Connect to BigQuery

Domino can connect to and query any common database, including Google BigQuery. This topic describes how to create a Google service account, authenticate to Google, and use the BigQuery API to query a public table.

Warning

Connect to Google BigQuery

Go to Google’s Service Accounts page. Select a previous project or create a new project.

If you selected Create, the New Project page opens:
Create a Service account for your project.
Define the access that the Service account must have to BigQuery. See Google’s Access Control documentation for more information.
Confirm that your Service account has been created.
On the Service Accounts page, create a new key.
Download the JSON key and keep it in a safe place. You will use this key later to programmatically authenticate to Google.
To enable the BigQuery API, click the Google APIs logo.
In the Library page, select the Big Query API.
If it is not enabled, click Enable.

Activate your credentials from Domino

Google Cloud uses the Google Cloud SDK to activate your credentials. This is already installed in the Domino Default environment.

Execute the following bash command:

/home/ubuntu/google-cloud-sdk/bin/gcloud auth activate-service-account <service account name> --key-file <key file path>

For example:

/home/ubuntu/google-cloud-sdk/bin/gcloud auth activate-service-account big-query-example@example-big-query-170823.iam.gserviceaccount.com --key-file key.json

You can use a custom Domino compute environment and enter this command in Domino pre-setup script to activate the credentials before each run. Otherwise, you can execute them in workspace sessions. See how to store your credentials securely.

Authenticate and query using Python

You need the gcloud and oauth2client==1.4.12 Python packages. Use the following package to install them in your custom Domino compute environment or in your workspace session.

pip install --user gcloud oauth2client==1.4.12

Use the following code to authenticate your Google credentials and query a public BigQuery table:

from oauth2client.client import GoogleCredentials
from googleapiclient.discovery import build

# Grab the application's default credentials from the environment.
credentials = GoogleCredentials.get_application_default()

# Construct the service object for interacting with the BigQuery API.
bigquery_service = build('bigquery', 'v2', credentials=credentials)

query_request = bigquery_service.jobs()
query_data = {
    'query': (
    'SELECT TOP(corpus, 10) as title, '
    'COUNT(*) as unique_words '
    'FROM [publicdata:samples.shakespeare];')
}

query_response = query_request.query(
    projectId='example-big-query-170823', # Substitute your ProjectId
    body=query_data).execute()

print('Query Results:')
for row in query_response['rows']:
    print('\t'.join(field['v'] for field in row['f']))