Import & export
Security and privacy
This is a step-by-step tutorial on how to connect, read and write to your BigQuery data warehouse using Deepnote
Available on Team and Enterprise plans
- Deepnote BigQuery integration uses the official python BQ client library
- Authentication in the integration works through a generated JSON service account key
- 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, and BigQuery Data Viewer. The best practice is to only grant this last role for specific datasets or tables you wish to explore in Deepnote.
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 (shown below).
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, connect it to any of your projects. After connecting, simply add an SQL cell to query tables in GBQ.
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:
import json import os from google.oauth2 import service_account from google.cloud import bigquery bq_credentials = service_account.Credentials.from_service_account_info( json.loads(os.environ['INTEGRATION_NAME_SERVICE_ACCOUNT'])) client = bigquery.Client(credentials=bq_credentials, project=bq_credentials.project_id)
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:
[var for var in os.environ if '_SERVICE_ACCOUNT' in var]
Once the BigQuery
client is initialized, you can use it to run queries and materialize the results as dataframes like this:
sql = """ SELECT * FROM `bigquery-public-data.usa_names.usa_1910_current` LIMIT 1000 """ df = client.query(sql).to_dataframe()