Connect Google BigQuery to Mammoth with a service account

What you can bring in from BigQuery

• Any table the service account can read. BigQuery groups tables into datasets, which work like schemas, and the Tables list groups tables under their BigQuery dataset.
• The result of your own SELECT query.

The Mammoth dataset can refresh daily or only when you ask.

Before you connect BigQuery

You need:

• A Google Cloud project with BigQuery turned on.
• A service account with the BigQuery Data Viewer role (or equivalent) and the BigQuery Job User role.
• The service account's JSON key file, downloaded to your computer.

Create a BigQuery service account and key

1. In the Google Cloud Console, go to IAM & Admin → Service Accounts.
2. Click Create Service Account.
3. Give it a name, for example mammoth-bigquery.
4. Grant the roles BigQuery Data Viewer (read access) and BigQuery Job User (to run queries).
5. Click Done.
6. Open the service account, go to Keys, click Add Key → Create new key, and select JSON.
7. Save the downloaded JSON file. You upload it to Mammoth in the next step.

The key file grants access to your BigQuery project. Store it securely and don't share it. To revoke access, delete the key in Google Cloud Console under IAM & Admin → Service Accounts, then your account, then Keys.

Connect Google BigQuery to Mammoth

1. Go to the Data Library, click New dataset, and choose Connectors.
2. Search for BigQuery and click the Google BigQuery tile. The connection form opens.
3. Click Upload service account file and select the JSON key file.
4. Click Connect. Once Mammoth has connected, the connector workspace opens on your tables.

If you already have a BigQuery connection, pick it under Your connections in the launcher instead. See Connect a database or app for the shared steps.

Query a BigQuery table in the SQL view

1. In Tables, click a table. Mammoth opens its rows with the SELECT * query that read them, shown above the rows.
2. To change the query, click Edit SQL, edit it, and click Run.

BigQuery puts table names in backticks:

SELECT order_id, customer_id, amount, created_at
FROM `my-project.analytics.orders`
WHERE DATE(created_at) >= DATE_SUB(CURRENT_DATE(), INTERVAL 90 DAY)
  AND status = 'completed'

Connect a database or app covers the full SQL view.

Save BigQuery data as a dataset

1. Click Save as dataset.
2. Enter a Name and pick a folder.
3. Under Keep it up to date, choose Daily at 06:00 or Only when I ask.
4. Next to the sentence that says what each refresh does, click Change and pick an option under On each refresh (see the table below).
5. Click Save as dataset.

A key column is a column whose values only ever go up, such as an ID or a date.

Refresh BigQuery data on a schedule

Choosing Daily at 06:00 creates a Dataset Refresh automation that you can reschedule later.

After the dataset is saved, you can change what a refresh does in its Update method: Replace all data, Add new rows only or Keep only the newest rows.

Troubleshoot BigQuery permission errors

Related

• Connect a database or app: the shared connector workspace steps and the full SQL view.
• Manage saved connections: edit, reuse or delete this connection.
• Refresh a dataset: change when the dataset refreshes.
• Google Cloud Storage: import files from a bucket with a service account.
• Connector reference: every source Mammoth connects to.