BLOCKS Reference

Block Reference

BigQuery

Execute query

This BLOCK executes BigQuery queries.

Property name Explanation
BLOCK name Designate a name to be displayed on this BLOCK.
GCP service account

Select the GCP service account for use with this BLOCK.

SQL syntax

Select which SQL syntax will be used in the "Query" property.

Query

Designate a query to execute.

If you would like to deliver % format characters, such as %Y or %m, directly to BigQuery, please use %%. For example, %Y or %m would become %%Y and %%m. BigQuery changes %% into %, receiving the intended %Y and %m.

[% format character string addressable] [variable expansion addressable]

Result storage data set

Designate the ID of the query result storage data set.

If omitting the "Result storage data set", "Result storage table" and the "Result reference variable name", the query results will be exported as variable_ (underscore).

[% format character string addressable] [variable expansion addressable]

Result storage table

Designate the ID of the query result storage table.

If omitting the "Result storage data set", "Result storage table" and the "Result reference variable name", the query results will be exported as variable_ (underscore).

[% format character string addressable] [variable expansion addressable]

In cases of non-empty tables

Select an action to perform when destination tables are available for data.
Permissible actions are as follow.

Additions: Write additional data.
Overwrite: Empty tables and write.
Error: If tables are not empty, operations will fail.

BLOCK memos Make any comments regarding BLOCKS.
Variable names storing results

Designate a variable name for query result storage. Up to 1,000 queries can be stored.

If omitting the "Result storage data set", "Result storage table" and the "Result reference variable name", the query results will be exported as variable_ (underscore).

Repetition attempts Designate the number of repetitions to occur in the case of an error or time out in BigQuery.
Minimum duration for time out Indicate how many seconds to wait for a response (time out) in BigQuery. It will double with every repetition. The maximum value for the time increase between repetitions is set under 'Maximum duration for time out" in the properties.
Maximum duration for time out As time outs increase from the "Minimum duration for time out", they reach a maximum value. It is defined in units of seconds.
Allow 1,000+ query storage Allow execution of queries containing over 1,000 results. You may not allow it without the use of a "Result storage data set" and "Result storage table".
Permit flattened results Specify whether or not to expand nested table fields. If you will not, you will not be able to select "Allow 1,000+ query storage".
Query priority level

Select a level of priority for queries. Permitted levels are as follow.

  • Interactive (Immediate execution, number of simultaneous queries is suject to limitation)
  • Batch (Executed when resources are available, number of simultaneous queries is not subject to limitation)
Use query cache Specify whether or not you will use query cache.
User-Defined Functions

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

The BLOCK interprets strings starting with "gs://" as URI and reads the user-defined functions from the corresponding GCS objects.

This property is only 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 Before executing a query, specify a table for checks. You can do so using an object character string similar to "ProjectID:DatasetID:TableID" or "{dataset":"DatasetID","table":"TableID"}". When using several, use of an array is possible.
Check count This defines the maximum number of times to check for the table you specified in "Tables required for execution".
Time between checks This defines the time, in seconds, between checks for the table you specified in "Tables required for execution".