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 Make, it is necessary to have a Google account. If you do not have one, you can create a Google account at google.com.

Notice

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

Connecting BigQuery to Make

  1. Go to Make, 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, Make will redirect you to the Google website, where you will be prompted to grant Make access to your account.

    61d5a944813ed.png

Confirm the dialog by clicking the Allow button.

Dataset

Retrieves all datasets in the specified project.

Connection

Establish a connection to your BigQuery account.

Project ID

Select or map the ID of the Google project (created via Google Cloud Platform) you want to list the BigQuery datasets from.

Show All

Enable this option to retrieve hidden datasets also.

Limit

Set the maximum number of datasets Make will return during one execution cycle.

Retrieves dataset details.

Connection

Establish a connection to your BigQuery account.

Project ID

Select or map the ID of the Google project (created via Google Cloud Platform) you want to retrieve dataset details from.

Dataset ID

Select or map the ID of the dataset you want to retrieve details for.

Creates a new dataset.

Connection

Establish a connection to your BigQuery account.

Project ID

Select or map the ID of the Google project (created via Google Cloud Platform) you want to create a dataset for.

Create Dataset ID

Enter 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 Day

The 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.

Location

Select the geographic location where the dataset should reside.

Labels

Add labels to the dataset if needed. You can use these to organize and group your datasets.

Description

Enter a user-friendly description of the dataset.

Updates dataset details.

Connection

Establish a connection to your BigQuery account.

Project ID

Select or map the ID of the Google project (created via Google Cloud Platform) that contains the dataset you want to update.

Dataset ID

Enter (map) or select the dataset ID of the dataset you want to update.

Labels

Specify labels. You can use these to organize and group your datasets.

Default Table Expiration Day

The 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.

Description

Enter a user-friendly description of the dataset.

Deletes the specified dataset.

Connection

Establish a connection to your BigQuery account.

Project ID

Select or map the ID of the Google project (created via Google Cloud Platform) that contains the dataset you want to delete.

Dataset ID

Enter (map) or select the dataset ID of the dataset you want to delete.

Table

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

Connection

Establish a connection to your BigQuery account.

Project ID

Select 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 ID

Select or map the ID of the dataset you want to watch for new tables.

Limit

Set the maximum number of tables Make will return during one execution cycle.

Retrieves all tables in the specified dataset.

Connection

Establish a connection to your BigQuery account.

Project ID

Select or map the ID of the Google project (created via Google Cloud Platform) that contains the dataset you want to retrieve tables from.

Dataset ID

Select or map the ID of the dataset you want to retrieve tables from.

Limit

Set the maximum number of tables Make will return during one execution cycle.

Retrieves table details.

Connection

Establish a connection to your BigQuery account.

Project ID

Select 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 ID

Select or map the ID of the dataset you want to retrieve table details from.

Table ID

Select or map the ID of the table you want to retrieve details for.

Creates a new, empty table in the specified dataset.

Connection

Establish a connection to your BigQuery account.

Project ID

Select 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 ID

Select or map the ID of the dataset where you want to create a table.

Create Table ID

Enter 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.

Fields

Specify the fields of the table.

Require Partition Filter

If enabled, queries over this table require a partition filter that can be used for partition elimination to be specified.

Expiration Days

Enter the number of days for which to keep the storage for a partition.

Labels

The 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.

Description

Enter a user-friendly description of this table.

Updates an existing table.

Connection

Establish a connection to your BigQuery account.

Project ID

Select 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 ID

Select or map the ID of the dataset where you want to update a table.

Table ID

Enter the ID of the table you want to update.

Fields

Specify the fields of the table.

Require Partition Filter

If enabled, queries over this table require a partition filter that can be used for partition elimination to be specified.

Expiration Days

Enter the number of days for which to keep the storage for a partition.

Labels

The 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.

Description

Enter a user-friendly description of this table.

Delete a table.

Connection

Establish a connection to your BigQuery account.

Project ID

Select 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 ID

Select or map the ID of the dataset you want to delete a table from.

Table ID

Enter the ID of the table you want to delete.

Query Job

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

Connection

Establish a connection to your BigQuery account.

Project ID

Select or map the ID of the Google project (created via Google Cloud Platform) you want to watch for jobs.

Filter

Select whether to return successful (done) jobs, unsuccessful (pending, running), or both types of jobs.

Projection

Restricts information returned to a set of selected fields.

Limit

Set the maximum number of jobs Make will return during one execution cycle.

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

Connection

Establish a connection to your BigQuery account.

Project ID

Select 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

Allows you to perform a custom API call.

Connection

Establish a connection to your BigQuery account.

URL

Enter a path relativeto 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.

Headers

Enter the desired request headers. You don't have to add authorization headers; we have already done that for you.

Query String

Enter the request query string.

Body

Enter the body content for your API call.

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

61d5a945a190a.png

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

61d5a946c8534.png