Google BigQuery

The recommended connector library for BigQuery is sqlalchemy-bigquery.

Install BigQuery Driver

Follow the steps here about how to install new database drivers when setting up Superset locally via docker compose.

  1. echo "sqlalchemy-bigquery" >> ./docker/requirements-local.txt

Connecting to BigQuery

When adding a new BigQuery connection in Superset, you’ll need to add the GCP Service Account credentials file (as a JSON).

  1. Create your Service Account via the Google Cloud Platform control panel, provide it access to the appropriate BigQuery datasets, and download the JSON configuration file for the service account.
  2. In Superset, you can either upload that JSON or add the JSON blob in the following format (this should be the content of your credential JSON file):
  1. {
  2.         "type": "service_account",
  3.         "project_id": "...",
  4.         "private_key_id": "...",
  5.         "private_key": "...",
  6.         "client_email": "...",
  7.         "client_id": "...",
  8.         "auth_uri": "...",
  9.         "token_uri": "...",
  10.         "auth_provider_x509_cert_url": "...",
  11.         "client_x509_cert_url": "..."
  12.     }

CleanShot 2021-10-22 at 04 18 11

  1. Additionally, can connect via SQLAlchemy URI instead

    The connection string for BigQuery looks like:

    1. bigquery://{project_id}

    Go to the Advanced tab, Add a JSON blob to the Secure Extra field in the database configuration form with the following format:

    1. {
    2. "credentials_info": <contents of credentials JSON file>
    3. }

    The resulting file should have this structure:

    1. {
    2.  "credentials_info": {
    3.      "type": "service_account",
    4.      "project_id": "...",
    5.      "private_key_id": "...",
    6.      "private_key": "...",
    7.      "client_email": "...",
    8.      "client_id": "...",
    9.      "auth_uri": "...",
    10.      "token_uri": "...",
    11.      "auth_provider_x509_cert_url": "...",
    12.      "client_x509_cert_url": "..."
    13.      }
    14.  }

You should then be able to connect to your BigQuery datasets.

CleanShot 2021-10-22 at 04 47 08

To be able to upload CSV or Excel files to BigQuery in Superset, you’ll need to also add the pandas_gbq library.

Currently, Google BigQuery python sdk is not compatible with gevent, due to some dynamic monkeypatching on python core library by gevent. So, when you deploy Superset with gunicorn server, you have to use worker type except gevent.