Google BigQuery
This is a step-by-step tutorial on how to connect, read and write to your BigQuery data warehouse using Deepnote

General information

Step by step tutorial

    1.
    Follow this guide to create a service account and download its key
Make sure to enable BigQuery API for your GCP project.
The service account needs sufficient permissions for the resources you want to use. If the authorisation process fails, we recommend visiting BigQuery's access control page.
Grant sufficient permissions to your service account
The minimum required permissions are:
    BigQuery Job User
    BigQuery Read Session User
    BigQuery Data Viewer
      The best practice is to follow the principle of least privilege, and only grant this role on specific datasets or tables you wish to explore in Deepnote. If you'd like to work with all data, grant this role for the entire project.
2. Go to a project where you wish to use your BigQuery integration, then go to left sidebar, click integrations and add a BiqQuery integration.
3. Paste the content of your service account key to the specified fields to create the integration.
Your service account key will be encrypted and stored in our database. The JSON file will be mounted to every project the integration is connected to, so the python library can easily access it.
4. After the integration is created, you can connect it to any of your projects. After connecting, simply add a SQL cell to query your BigQuery.
For easier query composition, you can also explore the schema by clicking the View button.

Advanced usage

To go beyond querying (like listing tables, creating datasets, etc.), you may need to use the official Python client library (docs).
Use this code snippet to authenticate the python client using the integration's service account:
1
import json
2
import os
3
from google.oauth2 import service_account
4
from google.cloud import bigquery
5
​
6
bq_credentials = service_account.Credentials.from_service_account_info(
7
json.loads(os.environ['INTEGRATION_NAME_SERVICE_ACCOUNT']))
8
client = bigquery.Client(credentials=bq_credentials,
9
project=bq_credentials.project_id)
Copied!
Just replace the INTEGRATION_NAME with an uppercased, underscore-connected name of your integration. If you have trouble finding it, run this one-liner to list environment variables that contain service accounts:
1
[var for var in os.environ if '_SERVICE_ACCOUNT' in var]
Copied!
Last modified 19d ago