Skip to main content

BigQuery

ToolJet can connect to BigQuery databases to run BigQuery queries.

Connection​

To establish a connection with the BigQuery data source, you can either click on the + Add new Data source button located on the query panel or navigate to the Data Sources page from the ToolJet dashboard and choose BigQuery as the data source.

ToolJet requires the following to connect to your BigQuery:

  • Private key
  • Scope
warning

When entering multiple scopes, separate them using spaces. Using any other character may cause errors.

Example: https://www.googleapis.com/auth/cloud-platform https://www.googleapis.com/auth/drive

How to get a Private key?

  1. You need to enable BigQuery API in your Google Cloud Console. You can follow the steps to enable BigQuery API from Google Cloud.
  2. You need to create a service account and generate a key for the same. You can follow the steps to create a service account from Google Cloud.
  3. Once you have created the service account after following the steps mentioned in the Google Cloud guide, create a new Key and download it in a JSON file.
  4. Now, copy and paste the data from the downloaded JSON file into the Private key field in the BigQuery data source form.
Adding Bigquery Connection

The JSON file should look like this:

{
  "type": "service_account",
  "project_id": "long-sonar-324407",
  "private_key_id": "63f4415e600bd7879bc14fd1157a4aabe227c204",
  "private_key": "-----BEGIN PRIVATE KEY-----\nMIIEvgIBADANBgkqhkiG9w0BAQEFAASCBKgwggSkAgEAAoIBAQDRGgDmfwYcKp4q\n3ce4DkrKv0vTn/Jn2Z2vEHp+oOz5ebZqmE3v56c6YIvtVRblANILPrOsB5ZvkF5f\nEzZBXn7ZI3+dqKBrpxbJqF6bKTLENdgFZRTbXHtGDpmwX4A+ufir9QNoezRw0i5L\nnVZiVC54f/Qt/cKT8794qSnrxNX1TneZLGxJWou9VAl3xT9h2HdL56gYIuleWXDK\nnXkb3Leh9AMZCdFPMyC24MWefWrUbNkqJ7V8FHo7bMrAcFNuSoF2NfK1v6IPLkEs\nwAU0CJ9VSg6rrahQOqIJ04cdYs2OUh4lRvRB6pqlVvtl6EdJB6dHln1nDzpgHbnb\n+acfwEDnAgMBAAECggEAGs/mSKgGDQuL73wztU6j2X6RBwhN6XIWjZGj22PgLxcj\nxGRWLgp6v3oMxzhvcJrb1BRMrqTkbdbJuxA4F0a6JjaukPVD6Lnqqp37z5KHT3CG\nDB8LfxtLNU7+9wYv6Bspn0cSEk4mCcdxp0F8B6y6rrndgh41WopZRWwPk4tQUh1r\nor67AAYd3rtzGMLoghs+8UE+UYa8wbpsbmHEYgqvXQAkNsl8WdNwqmI0G4lf+pgx\n7Rm27LJrtdBBHc48RUhg2eiN05HLCsnwkrnSj0rLL/L7T1yoSfCSUuv1mTUesxQ1\nXUEsPQQTTsNsqKOxT71CzQLElrPfwZkN4Y/IOJqX3QKBgQD6u0idi2r54hMjBSuk\npLgXygH5AWfHc4QqMCui7HZrFOJ4U4AreI/zZrM3Gemgs+1l27wsUjoxADW2Egyq\nX5AVe94RKSV3cCIIty38VOUBVsgyxj38d8yWkpJKJ2FcAgqEzPDDo0TCaOEq01oA\nYqjkgBz7Sh4XhQ5xwzfnOPRPtQKBgQDVfsly/k03wAJo1xlUZeq9mAnba5Hz07x9\nJ3REAwrtOaD891rKbkqDZKdGHTMweFGeEW2Hx7Q5iRS4WDKFO14wgSHFTkkVoSKR\n2W7XMomUQPFojQwgkDhrxsGE8O1DqfQ0+A5AJn2ASv/cyVGE3V2xg2rGr/HWi6Wq\nUp4FxebXqwKBgQDNIcCNNG03N6EUe7xzHViIDfuDL4UqhvXQVky9JNzVSubmLtqj\ntiV/q7xgDlE36z0EorvXPwbg5B0NcsLt+PU2vnq2a4V9rD4MB2IWGZaqe8ea0toP\n3iuB3TTWelWLIxhcAhfQ15j/vTLLCNOPkShAmhgb902bTH6+0ErCX7RyKQKBgQCe\nDOeLpvF5VT8zaBILZgva4eRiOQdqz5RZvsyW0P3U0vX4cBIZjH7DOM+Q22sa9efO\nMi6490HX2kCpnDmCYon/NInQrHz0cz7JZINm8rXhOBa/hLO2o63xM8nt5gJwNjBg\nykaafSQpxtwWEj+0McD7+kMg5f4OC4HQTqtHsNONUwKBgAoWGGRPja068BPIiUMB\nezsdYPP5TdASiBeAEPaQXQHlJxPDu9KoKqM5xvWIdR8eH1z7cuQ3RP89hYT03/UT\nBvWXHk2MJQZK7BZDw9KMZAKexK9/qxwHS6i7HhErD+Au3UaRX8dfjJzX8WAwuAwp\nVDwHncN3n4mPFQl7eijnQZ/F\n-----END PRIVATE KEY-----\n",
  "client_email": "[email protected]",
  "client_id": "103664451567222591066",
  "auth_uri": "https://accounts.google.com/o/oauth2/auth",
  "token_uri": "https://oauth2.googleapis.com/token",
  "auth_provider_x509_cert_url": "https://www.googleapis.com/oauth2/v1/certs",
  "client_x509_cert_url": "https://www.googleapis.com/robot/v1/metadata/x509/tooljettest%40long-sonar-324407.iam.gserviceaccount.com",
  "universe_domain": "googleapis.com"
}

Querying BigQuery​

  1. Click on + Add button of the query manager at the bottom panel of the editor.
  2. Select the BigQuery datasource added in previous step.
  3. Select the desired operation from the dropdown and enter the required parameters.
  4. Click on the Preview button to preview the output or Click on the Run button to trigger the query.
Bigquery list all operations
tip

Query results can be transformed using transformations. Read our transformations documentation to see how: link

Supported Operations​

Query​

This operation returns the data based on the Query.

Note: Follow the reference given in Google Cloud about the operations: Query options and Query results options.

Required Parameters​

  • Query
  • Query options
  • Query results options
BQ query

List Datasets​

This operation returns the list of datasets.

BQ list datasets

List Tables​

This operation returns the list of tables within a dataset.

Required Parameter​

  • Dataset Id
BQ list tables

Get Dataset Info​

This operation allows the users to retrieve the metadata for a specific dataset.

Required Parameters​

  • Dataset Id
BQ get dataset info

Create Table​

This operation is used to create a table.

Required Parameters​

  • Table Id
  • Dataset Id
  • Options
BQ create tables

NOTE: Visit https://github.com/googleapis/nodejs-bigquery/blob/main/samples/createTable.js for more info on schema.

Delete Table​

This operation is used to delete a table.

Required Parameters​

  • Table Id
  • Dataset Id
BQ delete tables

Create View​

This operation is used to create a view.

Required Parameters​

  • Table Id
  • Dataset Id
  • View name
  • View columns
  • Condition
  • Query options
  • Query results options
BQ create view

Insert Record​

This operation is used to insert a record.

Required parameters:​

  • Table Id
  • Dataset Id
  • Rows
BQ insert

Delete Record​

Use this operation to delete a record.

Required parameters:​

  • Table Id
  • Dataset Id
  • Condition
  • Query options
  • Query results options
BQ delete
warning

NOTE: Be careful when deleting records in a table. If you omit the WHERE clause, all records in the table will be deleted!

Update Record​

Use this operation to update a record.

Required parameters:​

  • Table Id
  • Dataset Id
  • Columns
  • Condition
  • Query results options
BQ update