# Google BigQuery
Google BigQuery (opens new window) is a serverless, highly-scalable, and cost-effective cloud data warehouse with an in-memory BI Engine and machine learning built in. The Workato connector to Google BigQuery allows you to automate various actions on datasets in your BigQuery instance such as inserting rows or performing queries on existing datasets. You are also able to trigger recipes off new rows in datasets.
# How to connect to BigQuery on Workato
The BigQuery connector allows two types of authentication : OAuth 2.0 and Service Account.
|Connection name||Give this BigQuery connection a unique name that identifies which BigQuery instance it is connected to.|
|Authentication Type||Choose OAuth 2.0 when using a user account and Service Account when using a service account to connect to BigQuery.|
|GCP Project service account email (Applicable only to Service Account Auth)||The email of your service account|
|Private key (Applicable only to Service Account Auth)||The private of your service account|
|Advanced settings (Applicable only to Service Account Auth)||Adjust the scopes of your connection.|
# Service Account
You can also authenticate to BigQuery using a Google Cloud service account. A service account is a special type of Google account that is associated with your Google Cloud Project that can be used to run API requests on your behalf. Service accounts can be used in BigQuery to ensure that the solution will continue running even if individual users' permissions change. Read more about service accounts here (opens new window).
To create a service account, you need to log into your Google Cloud Platform (GCP)console. Follow the guide here (opens new window) to create a new service account in your GCP project. Follow this guide (opens new window) to add a new private key and download the key in JSON format. Note that after you download the key file, you cannot download it again.
Copy the private key from
-----BEGIN PRIVATE KEY----- to
-----END PRIVATE KEY-----\n both inclusive and paste it as input on Workato.
# Setting permissions
For the service account to use all actions and triggers in the BigQuery connector, the service account should be given the
BigQuery Admin IAM role or custom role with the following 11 permissions:
If you would like to only insert data with this connection on Workato, you may:
- Use the
BigQuery Data Editorrole.
- Add the bigquery.jobs.create permission.
If you would like to only select data with this connection on Workato, you may:
- Use the
BigQuery Data Viewerrole.
- Add the bigquery.jobs.create permission.
Read this guide (opens new window) to create custom roles for the service account on Google Cloud Platform. More information on each of the required permissions above can be found in the list of BigQuery permissions (opens new window).
Note that when the service account with a custom role is used, Project ID must be supplied directly on Workato as the
Select project dropdown in
Setup will not load and you will need to provide the Project ID manually.
# Rate limits on BigQuery
BigQuery's rate limits (opens new window) on tables indicates that
bulk insert operations on tables can only be performed 1000 times a day. To bypass these limits, use the
Insert row and
Insert rows actions which are not affected by such limits.
# Working with the BigQuery connector
After establishing a connection with the BigQuery connector, most actions will require some additional parameter inputs.
|Project||The project available in the connection to be billed for the query.|
|Dataset||The dataset which the action or trigger will pull the possible tables from.|
|Table||The table inside the dataset.|
|Location||The geographical location of where the job should be run.|
# Single row vs batch of rows
The BigQuery connector can read or write to your database either as a single row or in batches. When using batch read actions/triggers, you have to provide the batch size you wish to work with. The batch size can be any number between 1 and 50000, with 50000 being the maximum batch size.
Batch trigger inputs
Besides the difference in input fields, there is also a difference between the outputs of these 2 types of operations. A trigger that processes rows one at a time will have an output datatree that allows you to map data from that single row.
Single row output
However, a trigger that processes rows in batches will output them as an array of rows. The Rows datapill indicates that the output is a list containing data for each row in that batch.
Batch trigger output
As a result, the output of batch triggers/actions needs to be handled differently. In cases where there are downstream batch actions which accept Rows source list input fields, you'll be able to map the rows array pill to it to tell Workato you want to map the entire array.
Using batch trigger output
# WHERE condition
This input field is used to filter and identify rows to perform an action on. It is used in multiple triggers and actions in the following ways:
- filter rows to be picked up in triggers
- filter rows in Select rows action
- filter rows to be deleted in Delete rows action
This clause will be used as a
WHERE statement in each request. This should follow basic SQL syntax. Refer to this BigQuery documentation (opens new window) for a full list of rules for writing SQL statements compatible with BigQuery.
# Simple statements
Knowing the data types of the column in BigQuery are important to build working queries. When comparing string values, values must be enclosed in single quotes (
'') and columns used must exist in the table. When comparing integer values, the supplied value should not be enclosed in single quotes.
WHERE condition to filter rows based on values in a single column looks like this.
string_column = 'USD' and integer_column = 1111
If used in a Select rows action, this
WHERE condition will return all rows that have the value 'USD' in the
currency column. Just remember to wrap datapills with single quotes in your inputs.
Using datapills in
# Complex statements
WHERE condition can also contain subqueries. The following query can be used on the
id in (select distinct(user_id) from zendesk.tickets where priority = 2)
When used in a Delete rows action, this will delete all rows in the
users table where at least one associated row in the
tickets table has a value of 2 in the
Using datapills in
WHERE condition with subquery
# Defining your output fields
Sometimes, actions, and triggers in BigQuery will allow you to define the expected output columns a query. This input field shows up in the following triggers and actions:
- Scheduled query trigger
- Get query job output
- Select rows using custom SQL
Define your output fields easily using the output schema designer by using our CSV uploader. In these cases, simply run a sample query in the BigQuery console like above and export a CSV.
Upload this CSV into the schema wizard and automatically generate all fields for you.