BigQuery is a scalable cloud data warehouse integrated with the Google Cloud Platform for storage of a wide range of datasets. In some use cases, your transformation jobs can be executed completely in BigQuery. If all of your source datasets and outputs are in BigQuery locations, then transferring the execution steps from the  to BigQuery yields the following benefits:

In this scenario, the recipe steps are converted to SQL, which is sequentially executed on the Datasets and Tables of your source data into temporary tables, from which the results that you have defined for your output are written.

Tip: When running a job in BigQuery, your data never leaves BigQuery.

Tip: For jobs that are executed in BigQuery, you can optionally enable the execution of the visual profile in BigQuery, too. This option is enabled for individual flows. For more information, see Flow Optimization Settings Dialog.

Requirements

If the requirements and limitations are met, the  automatically executes the job in BigQuery.

Limitations

BigQuery as a running environment requires that pushdowns be enabled for the project and for the specific flow for which the job is executed. If the flow and the project are properly configured, the job is automatically executed in BigQuery.

NOTE: BigQuery is not a running environment that you explicitly select or specify as part of a job. If all of the requirements are met, then the job is executed in BigQuery when you select .


 File Support

In addition to BigQuery sources, you can execute jobs in BigQuery on source files from  . 

Tip: The BigQuery running environment also supports hybrid sources, so you can use as sources files and BigQuery tables in the same flow.

Requirements:

Supported file formats from  :

Supported file encodings:

Supported delimiters:

Supported quote characters:

Unsupported  for BigQuery Execution

The following transformations and functions are not currently supported for execution in BigQuery. 

NOTE: If your recipe contains any of the following transformations or functions, full job execution in BigQuery is not possible at this time. These transformations are expected to be supported and removed from this list in future releases.

General limitations

Unsupported transformations

None.

Unsupported functions

The following  functions are not currently supported for execution in BigQuery.

Aggregate functions

KTHLARGEST
KTHLARGESTIF
KTHLARGESTUNIQUE
KTHLARGESTUNIQUEIF
MODE
MODEIF
QUARTILE
APPROXIMATEMEDIAN
APPROXIMATEPERCENTILE
APPROXIMATEQUARTILE

For more information, see Aggregate Functions.


Math functions

LCM
NUMVALUE

Partially supported:

NUMFORMAT: Only supported when used for rounding.

For more information, see Math Functions.

Date functions

NETWORKDAYS
NETWORKDAYSINTL
MODEDATE
WORKDAY
WORKDAYINTL
MODEDATEIF
KTHLARGESTDATE
KTHLARGESTUNIQUEDATE
KTHLARGESTUNIQUEDATEIF
KTHLARGESTDATEIF
EOMONTH
SERIALNUMBER

String functions

RIGHTFIND
EXACT
STRINGGREATERTHAN
STRINGGREATERTHANEQUAL
STRINGLESSTHAN
STRINGLESSTHANEQUAL
DOUBLEMETAPHONEEQUALS
TRANSLITERATE

For more information, see String Functions.

Window functions

SESSION

For more information, see Window Functions.

Verify Execution

To verify execution in BigQuery, please do the following:

Steps:

  1. In the left nav bar, click the Jobs link. 
  2. In the Jobs page, select the job that you executed. 
  3. In the Overview tab, the value for Environment under the Execution summary should be: BigQuery.

For more information, see Job Details Page.