Skip to main content
Version: 2.50.0-LTS

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

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.
BQ create

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.
BQ query
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

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