Documentation Data Sources Direct Connection

Connect to Google BigQuery

Check out our step-by-step video showing how to connect BigQuery to Chartio:

To connect your BigQuery account to Chartio, you’ll need to set up a Service Account and upload the generated key to Chartio.

1. Create a Service Account

  1. Log in to Google Cloud Platform and navigate to the project you want to use in Chartio.
  2. In the sidebar, select IAM & admin and choose Service accounts.

    From the Google Cloud Platform, click Service accounts

  3. Click Create Service Account.

    Click Create Service Account

  4. Enter a service account name —- you may want to name it “Chartio” so you can remember its purpose later.

    Under Role, select BigQuery Data Viewer and BigQuery User. Ensure both roles are selected; if either are missing, Chartio will be unable to connect to your BigQuery data. Once added, click Continue.

    Note: It is possible to use only the BigQuery User role but that service account would need to be added to each dataset individually.

    Name it and select BigQuery Viewer and BigQuery User

  5. Click + CREATE KEY, select JSON for the Key type, then click Create.
  6. Your key is generated and downloaded to your machine. Save the JSON file to a secure location, or delete it after uploading the key to Chartio.

    If you have multiple BigQuery projects you’d like to associate with a single service account, follow our instructions below for accessing multiple projects.

2. Connect Google BigQuery to Chartio

1. Upload key to Chartio

In Chartio, select Data > Add Data Source > Google BigQuery and upload the JSON file you downloaded in the previous step. Note: It can take a few minutes for Google to accept the JSON key. If you get an error uploading the JSON file to Chartio, wait a few minutes and try again.

Drag and drop or click to select your service account key

2. Finish connecting

Provide an Alias for your data source, verify the Project ID, and specify the Maximum Bytes Billed, which limits queries over the specified size for billing purposes.


Access multiple projects

To access additional BigQuery projects with a single Service Account, you’ll need to add the client ID to the additional projects from the Google Cloud Platform console.

  1. From Google Cloud Platform, select IAM & admin > IAM from the sidebar.
  2. Find the service account and copy the member name.

    Set the permissions

  3. Switch to another project you want to connect to Chartio, and click Add at the top of the page.
  4. In the Members field, paste the member name you copied earlier.
  5. Under Role, select BigQuery User and BigQuery Data Viewer.

    Click Add and paste the copied member name

  6. Once this is finished, you can use the same JSON file you downloaded earlier to connect your second BigQuery project to Chartio.

Google BigQuery SQL Syntax

Google BigQuery uses its own proprietary SQL, so you need to use that syntax when creating queries in SQL Mode. If you need help, you can refer to Google BigQuery’s Standard SQL Query Syntax documentation.


Google Sheets via BigQuery

If you’ve added Google Sheets tables to your BigQuery project, you can query them in Chartio from your BigQuery connection after a few settings updates. Your BigQuery account must use a service account connection to enable this feature.

Enable Google Drive API in the project

  1. Log in to your Google Cloud Console.
  2. Select your project from the top dropdown.
  3. From the left navigation, choose APIs & Services > Dashboard.
  4. At the top of the page, click Enable APIs and Services.
  5. Use the search bar to search for Google Drive API. Select it, and click Enable.

Add service account client ID to the Google Sheet

  1. From your Google Cloud Console, select IAM & admin > Service accounts from the left sidebar.
  2. Copy the value in the Service account ID column. If you don’t have a service account yet, follow the instructions above for creating a BigQuery service account.
  3. Open your Google Sheet and click on the Share button.
  4. Click Advanced at the bottom of the pop-up. In the Invite people text input, enter the Service account ID value you copied earlier.

BigQuery job labels

To provide more context to where queries to your data source are coming from, Chartio includes job labels to any queries we send. Unfortunately, you can’t view these labels in the BQ console, but you can use the command line tool to see them. Check out the BQ documentation for more info.

Note: We previously used query comments but found BigQuery’s job labels to be more cache-friendly and less costly for our BigQuery customers.

The job labels we send look similar to the following:

{
	"reason":"chart_editor",
	"user_email":"support@chartio.com",
	"datasource_id":123456,
	"dashboard_slug":"my-dashboard",
	"chart_id":1234567,
	"datasource_alias":"my data source",
	"organization_id":12345,
	"dataset_id":1231231,
	"is_manual":false,
	"dataset_name":"Dataset 1",
	"dashboard_id":456456
}