BLOCKS Reference

Block Reference

BigQuery

Split table by column values

This BLOCK creates tables from BigQuery tables, creating individual columns based upon content specifications.

As an example, suppose the following: "SELECT Split criteria column name FROM source table for split GROUP BY col LIMIT 1000;". The split criteria column name (col) of the source table for the split acquires classes for the values in the table. For each item contained there (val), "SELECT*FROM source table for split WHERE col=val;" will export the results into appropriate rows of tables containing val in the table name.

  • The "Split criteria column name" should be set accordingly as a character string. Characters that may not be used with table IDs are excluded and will appear in the destination table ID suffix. Numbers already in use in results will result in numbers being appended.
  • The "Split criteria column name" value class will result in error if it exceeds 999 (1,000 or greater results in error).
  • If pre-existing tables are found they will be overwritten.
Property name Explanation
BLOCK name Designate a name for BLOCKS. The names will be displayed on the BLOCKS.
GCP service account Select the GCP service account for use with this BLOCK.
Source data set for split

Designate an ID for the source data set to split.

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

Source table for split

Designate an ID for the source table to split.

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

Split criteria column name Designate the name (STRING format) of the column to be used as the basis for splitting the table.
Split table data set

Designate an ID for the location to which results of the table split will be written.

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

Table produced from table split

Designate a prefix for the ID of the split destination table recieivng data. Provide a value for the "Split criteria column name" (excluding characters that are not permitted). The table ID will be determined in the end.

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

BLOCK memos Make any comments regarding BLOCKS.
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)
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.