Google BigQuery

Google BigQuery is a fully-managed, serverless data warehouse that enables scalable analysis over petabytes of data. It is a Platform-as-a-Service (PaaS) that supports querying using ANSI SQL.

A View can be exported as a table to a BigQuery dataset on the Google Cloud Platform. For this export, you first need to configure Google Cloud. Here’s how to do this:

Enable BigQuery API

  1. Log in to Google Cloud Platform and select the project to use in Mammoth;

  2. Go to API and Services from the sidebar;

  3. Click on + Enable APIs and Services and look for BigQuery API;

  4. Open BigQuery API and click on Enable;

    Enabling BigQuery API

    Fig. 144 Enabling BigQuery API

Create a Service Account

  1. From the sidebar, select IAM & Admin and go to Service accounts;

  2. Click on Create service account;

  3. In Service Account Details, enter the service account name and description. Google Cloud will then choose an email for your service account randomly. Click on Create;

    Service account

    Fig. 145 Creating a service account

  4. Click into Grant this service account access to project, add two roles and select BigQuery Data Editor and BigQuery Job User to give the service account permission to complete specific actions on the resources in your project. Click Continue;

  5. In Grant users access to this service account, add the email address of the end user. By default, you will be the sole owner of the service account. This step is optional. Click Done;

    Adding roles

    Fig. 146 Adding roles to your service account.

  6. For the newly created service account, go to the action menu and select Manage keys;

  7. Select Add key and click on Create new key.

  8. Select the key type as JSON (Mammoth accepts JSON type keys). Click on Create and a JSON key will be downloaded into your system. This JSON key will be required while connecting Mammoth to BigQuery.

    Creating a key

    Fig. 147 Creating a JSON key

Note

A service account can be used for multiple projects.

  1. Switch to the project which needs this service account;

  2. From the sidebar, go to IAM & Admin and click on +ADD;

  3. Enter the email of the service account, create the roles and save the file.

    Adding service account

    Fig. 148 Adding a service account to a project

Connect Mammoth to BigQuery

  1. In Mammoth, Select Data Preparation > Export > Google BigQuery.

  2. Upload the JSON key which was downloaded in the previous step.

    Uploading key

    Fig. 149 Uploading the downloaded JSON key

    Note

    Make sure you have an existing Dataset in the Google Cloud project where the View can be exported as a table. To create a Dataset in BigQuery:

    1. Go to Google Cloud Console;

    2. Select Big Data > BigQuery from the sidebar;

    3. Select your project from Explorer tab, and click Create Dataset.

  3. Select the correct Dataset and enter the table name;

  4. Validate the connection and click Apply once it turns green after the Validation. The table is then exported to the selected Dataset.

    Export table

    Fig. 150 Exporting the table to BigQuery Dataset

On the Google Cloud Platform, select Google BigQuery from the sidebar. Select the same project with the key and open the Dataset you had selected in Mammoth. The table exported from Mammoth should now appear.