Follow

Connecting to and querying Big Query

Domino can connect to and query any common database, including Big Query. In this article, we outline the steps to create a Google service account, authenticate to Google, and use the Big Query API to query a public table.

Obtaining Credentials

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



    New Project Screen:



  2. Create a Service account for your project. 



  3. Define the access that the Service account should have to Big Query. See Google's Access Control documentation for more information. 



  4. Confirm that your Service account has been created.



  5. On the Service Accounts page, create a new key. 



  6. Download the JSON key and keep it in a safe place. You will use this key later to programmatically authenticate to Google.                         



 

Enable the Big Query API

  1. Click on the Google APIs logo in the top left of the screen.



  2. In the Library page, select the Big Query API.                    



  3. If it is not enabled, click Enable.                                                    



 

Activating Your Credentials from Domino

Google Cloud activates your credentials using the Google Cloud SDK, which is already installed in the Domino Default environment. You will need to execute the following bash command:

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

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 may want to 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. Make sure to read more on how to store your credentials securely.

 

Authenticate and Query Using Python

You will require two Python packages: gcloud and oauth2client==1.4.12. You can install them using 

pip install --user gcloud oauth2client==1.4.12

in either your custom Domino compute environment or in your workspace session.

Use the following code snippet to authenticate your Google credentials and query a public Big Query 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']))

 

Authenticate and Query using R

(Coming soon)

Was this article helpful?
1 out of 1 found this helpful