Deepnote
IntegrationsPricing
Join usDocsSign in

Getting started

Deepnote documentation

Environment

Integrations

Google BigQuery

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

  • Deepnote BigQuery integration uses the official python BQ client library
  • Authentication in the integration works through a generated JSON service account key

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, 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).

spaces%2FtfH69m1V6bYYvquUay8O%2Fuploads%2FtICohHaQgF2i989dTaiw%2FScreen%20Shot%202022-04-01%20at%203.09.45%20PM.png

3. Paste the content of your service account key to the specified fields to create the integration.

spaces%2FtfH69m1V6bYYvquUay8O%2Fuploads%2FoE50O9Vh5L4PobmBRxw3%2FScreen%20Shot%202022-04-01%20at%203.12.43%20PM.png

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.

spaces%2FtfH69m1V6bYYvquUay8O%2Fuploads%2FGz3aiIlmIALb5Hp1dFHf%2FScreen%20Shot%202022-04-01%20at%203.21.36%20PM.png

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:

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]
Deepnote
Product
© 2022 Deepnote. All rights reserved.