BigQuery

The BigQuery modules enable you to monitor tables and completed query jobs, and create, retrieve, update, or delete datasets and tables in your BigQuery account.

Getting Started with BigQuery

Prerequisites

In order to use BigQuery with Integromat, it is necessary to have a Google account. If you do not have one, you can create a Google account at google.com.

The module dialog fields that are displayed in bold (in the Integromat scenario, not in this documentation article) are mandatory!

Connecting BigQuery to Integromat

1. Go to Integromat, and open the BigQuery module's Create a connection dialog.

2. Enter a name for the connection in the Connection name field, and click Continue.

After you click the Continue button, Integromat will redirect you to the Google website, where you will be prompted to grant Integromat access to your account.

2020-10-30_11_28_20-Sign_in_-_Google_Accounts.png

Confirm the dialog by clicking the Allow button.

Dataset

List Datasets

Retrieves all datasets in the specified project.

ConnectionEstablish a connection to your BigQuery.
Project IDSelect or map the ID of the Google project (created via Google Cloud Platform) you want to list the BigQuery datasets from.
Show AllEnable this option to retrieve hidden datasets also.
LimitSet the maximum number of datasets Integromat will return during one execution cycle.

Get a Dataset

Retrieves dataset details.

ConnectionEstablish a connection to your BigQuery.
Project IDSelect or map the ID of the Google project (created via Google Cloud Platform) you want to retrieve dataset details from.
Dataset IDSelect or map the ID of the dataset you want to retrieve details for.

Create a Dataset

Creates a new dataset.

ConnectionEstablish a connection to your BigQuery.
Project IDSelect or map the ID of the Google project (created via Google Cloud Platform) you want to create a dataset for.
Create Dataset IDEnter the dataset reference, without the project name. The ID must contain only letters (a-z, A-Z), numbers (0-9), or underscores (_). The maximum length is 1,024 characters.
Default Table Expiration DayThe default lifetime of all tables in the dataset, in days. Once this property is set, all newly created tables in the dataset will have an expirationTime property set to the creation time plus the value in this property, and changing the value will only affect new tables, not existing ones. When the expirationTime for a given table is reached, that table will be deleted automatically. If a table's expirationTime is modified or removed before the table expires, or if you provide an explicit expirationTime when creating a table, that value takes precedence over the default expiration time indicated by this property.
LocationSelect the geographic location where the dataset should reside. 
LabelsAdd labels to the dataset if needed. You can use these to organize and group your datasets.
DescriptionEnter a user-friendly description of the dataset.

Update a Dataset

Updates dataset details.

ConnectionEstablish a connection to your BigQuery.
Project IDSelect or map the ID of the Google project (created via Google Cloud Platform) that contains the dataset you want to update.
Dataset IDEnter (map) or select the dataset ID of the dataset you want to update.
LabelsSpecify labels. You can use these to organize and group your datasets.
Default Table Expiration DayThe default lifetime of all tables in the dataset, in days. Once this property is set, all newly created tables in the dataset will have an expirationTime property set to the creation time plus the value in this property, and changing the value will only affect new tables, not existing ones. When the expirationTime for a given table is reached, that table will be deleted automatically. If a table's expirationTime is modified or removed before the table expires, or if you provide an explicit expirationTime when creating a table, that value takes precedence over the default expiration time indicated by this property.
DescriptionEnter a user-friendly description of the dataset.

Delete Dataset

Deletes the specified dataset.

ConnectionEstablish a connection to your BigQuery.
Project IDSelect or map the ID of the Google project (created via Google Cloud Platform) that contains the dataset you want to delete.
Dataset IDEnter (map) or select the dataset ID of the dataset you want to delete.

Table

Watch Tables

Retrieves table details when a new table is created in the specified dataset.

ConnectionEstablish a connection to your BigQuery.
Project IDSelect or map the ID of the Google project (created via Google Cloud Platform) that contains the dataset you want to watch for new tables.
Dataset IDSelect or map the ID of the dataset you want to watch for new tables.
LimitSet the maximum number of tables Integromat will return during one execution cycle.

List Tables

Retrieves all tables in the specified dataset.

ConnectionEstablish a connection to your BigQuery.
Project IDSelect or map the ID of the Google project (created via Google Cloud Platform) that contains the dataset you want to retrieve tables from.
Dataset IDSelect or map the ID of the dataset you want to retrieve tables from.
LimitSet the maximum number of tables Integromat will return during one execution cycle.

Get a Table

Retrieves table details.

ConnectionEstablish a connection to your BigQuery.
Project IDSelect or map the ID of the Google project (created via Google Cloud Platform) that contains the dataset you want to retrieve table details from.
Dataset IDSelect or map the ID of the dataset you want to retrieve table details from.
Table IDSelect or map the ID of the table you want to retrieve details for.

Create a Table

Creates a new, empty table in the specified dataset.

ConnectionEstablish a connection to your BigQuery.
Project IDSelect or map the ID of the Google project (created via Google Cloud Platform) that contains the dataset where you want to create a table.
Dataset IDSelect or map the ID of the dataset where you want to create a table.
Create Table IDEnter the ID of the table. The ID must contain only letters (a-z, A-Z), numbers (0-9), or underscores (_). The maximum length is 1,024 characters.
FieldsSpecify the fields of the table.
Require Partition FilterIf enabled, queries over this table require a partition filter that can be used for partition elimination to be specified.
Expiration DaysEnter the number of days for which to keep the storage for a partition.
LabelsThe labels associated with this table. You can use these to organize and group your tables. Label keys and values can be no longer than 63 characters, and can only contain lowercase letters, numeric characters, underscores, and dashes. International characters are allowed.
DescriptionEnter a user-friendly description of this table.

Update a Table

Updates an existing table.

ConnectionEstablish a connection to your BigQuery.
Project IDSelect or map the ID of the Google project (created via Google Cloud Platform) that contains the dataset where you want to update a table.
Dataset IDSelect or map the ID of the dataset where you want to update a table.
Table IDEnter the ID of the table you want to update.
FieldsSpecify the fields of the table.
Require Partition FilterIf enabled, queries over this table require a partition filter that can be used for partition elimination to be specified.
Expiration DaysEnter the number of days for which to keep the storage for a partition.
LabelsThe labels associated with this table. You can use these to organize and group your tables. Label keys and values can be no longer than 63 characters, and can only contain lowercase letters, numeric characters, underscores, and dashes. International characters are allowed.
DescriptionEnter a user-friendly description of this table.

Delete a Table

Delete a table.

ConnectionEstablish a connection to your BigQuery.
Project IDSelect or map the ID of the Google project (created via Google Cloud Platform) that contains the dataset you want to delete a table from.
Dataset IDSelect or map the ID of the dataset you want to delete a table from.
Table IDEnter the ID of the table you want to delete.

Table Data

List Table Data

Retrieves the content of a table in rows.

ConnectionEstablish a connection to your BigQuery.
Project IDSelect or map the ID of the Google project (created via Google Cloud Platform) that contains the dataset you want to list table data from.
Dataset IDSelect or map the ID of the dataset you want to list table data from.
Table IDEnter the ID of the table you want to retrieve rows from.
LimitSet the maximum number of rows Integromat will return during one execution cycle.

Create a Row

To use this module, billing must be enabled. Enable billing at console.cloud.google.com/billing.

Creates a new row in the table.

ConnectionEstablish a connection to your BigQuery.
Project IDSelect or map the ID of the Google project (created via Google Cloud Platform) that contains the dataset where you want to create a table row.
Dataset IDSelect or map the ID of the dataset where you want to create a table row.
Table IDEnter the ID of the table you want to add a row to.
Values of RowEnter desired values.

Query Job

Watch Query Job Completed

Returns query job details when the query job has been processed.

ConnectionEstablish a connection to your BigQuery.
Project IDSelect or map the ID of the Google project (created via Google Cloud Platform) you want to watch for jobs.
FilterSelect whether to return successful (done) jobs, unsuccessful (pending, running), or both types of jobs.
ProjectionRestricts information returned to a set of selected fields.
LimitSet the maximum number of jobs Integromat will return during one execution cycle.

Write Query

Runs a BigQuery SQL query synchronously and returns query results if the query completes within a specified timeout.

ConnectionEstablish a connection to your BigQuery.
Project IDSelect or map the ID of the Google project (created via Google Cloud Platform) that contains the dataset you want to execute a query for.
Query

A query string, following the BigQuery query syntax, of the query to execute.

For example:

SELECT count(f1) FROM [myProjectId:myDatasetId.myTableId]

Other

Make an API Call

Allows you to perform a custom API call.

ConnectionEstablish a connection to your BigQuery.
URL

Enter a path relative to https://bigquery.googleapis.com/bigquery.
For example: /v2/projects/{projectId}/datasets.

For the list of available endpoints, refer to the BigQuery API Documentation.
Method

Select the HTTP method you want to use:

GET
to retrieve information for an entry.

POST
to create a new entry.

PUT
to update/replace an existing entry.

PATCH
to make a partial entry update.

DELETE
to delete an entry.

HeadersEnter the desired request headers. You don't have to add authorization headers; we have already done that for you.
Query StringEnter the request query string.
BodyEnter the body content for your API call.

Example of Use - List Tables

The following API call returns all tables in the specified project's dataset in your BigQuery:

URL:
/v2/projects/{project_id}/datasets/{dataset_id}/tables/

Method:
GET

2020-11-03_18_18_52-Integration_Meetup___Integromat.png

The result can be found in the module's Output under BundleBody > tables
In our example, four tables were returned:

2020-11-03_18_20_06-Integration_Meetup___Integromat.png


Popular use cases from our blog

slack-automation-illustration

Slack Automation: 20 Free Templates to Get You Inspired

google-sheets-automation-alt

Google Sheets Automation for Ecommerce: 23 Free Templates

tweet-automation-illustration

How to Get Someone's Tweets Before Anyone Else Does

onboarding-automation-with-integromat-alt

How to Automate Employee Onboarding in Minutes with Integromat

shopify-order-to-manufacturer-illustration-alt

How to Auto Send Shopify Orders to Dropshipping Suppliers [Guide]

strava-exercise-to-google-sheets-illustration

Like a Champ: How to Save Strava Activities to Google Sheets

Didn’t find what you were looking for?

Ticket

Create a ticket

Contact our world-class support team and tell us what’s happening.

Create a ticket
Expert

Find an expert

We feature a network of 450+ certified partners across the globe who are ready to help

Find an expert

Automate any workflow in your business

Sign up for a free account today. No credit card required, no time limits on free plan.