BLOCKS Reference

Block Reference

BigQuery

Execute query

This BLOCK executes a BigQuery SQL query and stores the results into a BigQuery table and/or variable.

Property Explanation
BLOCK name Configure the name displayed on this BLOCK.
GCP service account

Select the GCP service account to use with this BLOCK.

SQL syntax

Select whether your query uses Legacy SQL or Standard SQL syntax.

Query

Designate a query to execute.

To send % format characters (ex. %Y or %m) directly to BigQuery, use %%. For example, enter %%Y and BigQuery will change the %% into %, thus receiving the intended %Y.

[% format character addressable] [variable expansion addressable]

Dataset for storing results

Designate the ID of the dataset containing the table that will store the query results.

If this and "Table for storing results" are left blank, the results will be sent to the "Variable for storing results" (set to _ by default).

[% format character addressable] [variable expansion addressable]

Table for storing results

Designate the ID of the table that will store the query results.

If this and "Dataset for storing results" are left blank, the results will be sent to the "Variable for storing results" (set to _ by default).

[% format character addressable] [variable expansion addressable]

In cases of non-empty tables

Select which action to perform when the "table for storing results" already contains data.

  • Append: Appends new data to the table.
  • Overwrite: Overwrites the table with the new data.
  • Error: An error occurs if the table is not empty.
BLOCK memos Make notes about this BLOCK.
Variable for storing results

Designate a variable that will store the results of the query. The variable can store query results with up to 1,000 records.

If this, the "Dataset for storing result", and "Table for storing results" properties are all left blank, the results will be sent to the variable _ (underscore) by default.

Reattempts in case of errors Configure the number of attempts to try in case of a request error.
Minimum timeout interval Set the number of seconds to wait for results. If results are not returned during this interval, the time will be doubled for each reattempt until the time set in the “Maximum timeout interval” property is reached.
Maximum timeout interval Indicate the maximum number of seconds to wait for results. The timeout interval will start with the value set in the "Minimum timeout interval" property and double with each reattempt until reaching the value set here.
Allow results with 1,000+ records Select whether or not to allow query results with over 1,000 records. This cannot be enabled if the “Dataset for storing results” and “Table for storing results” properties are blank.
Allow flattened results Select whether or not to flatten nested fields. If you do not allow flattened results, you will not be able to enable the "Allow results with 1,000+ records" property.
Query priority level

Select the query priority level from the following:

  • Interactive (executes immediately, limited simultaneous queries)
  • Batch (executes when resources are available, unlimited simultaneous queries)
Use query cache Select whether or not to use the query cache.
User-defined functions

If using user-defined functions (UDF), enter them as strings into this property. Multiple UDF can be entered.

Strings starting with "gs://" are interpreted as a URI and user-defined functions will be read from the corresponding GCS objects.

This property can only be used when “SQL syntax” is set to Legacy SQL.

Maximum billing tier

Limit the execution of High-Compute queries. Queries that have resource usage beyond this billing tier will fail (without incurring a charge).

Reference: Basic Guide > Hints > BigQuery pricing tiers and High-Compute queries

Maximum bytes billed Set a limit for query pricing. Queries that will have bytes billed beyond this limit will fail (without incurring a charge).
Tables required for execution Designate a table or tables that the BLOCK will check for before executing. Enter tables as strings (ex. ProjectID:DatasetID:TableID), or objects (ex. {"dataset":"DatasetID","table":"TableID"}). You can enter multiple tables as an array.
Times to check for table(s) Set the maximum number of times to check for the table(s) specified in the "Tables required for execution" property.
Time between checks Set the number of seconds to wait between checking for the table(s) specified in the "Tables required for execution" property.