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.
If the requirements and limitations are met, the automatically executes the job in BigQuery.
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 .
All recipe steps, including all functions in the recipe, must be translatable to SQL.
NOTE: When attempting to execute a job in BigQuery, executes each recipe in BigQuery, until it reaches a step that cannot be executed there. At that point, data is transferred to , where the remainder of the job is executed.
BigQuery imposes a limit of 1 MB for all submitted SQL queries. If this limit is exceeded during job execution, falls back to submitting the job through .
If the schemas have changed for your datasets, pushdown execution on BigQuery is not supported. falls back to submitting the job through .
\s) match a broader set of Unicode space characters than , due to differences in implementation of regular expressions between the two running environments. Depending on your dataset, this difference may result in mismatches between rows in your results when running the same job across different running environments.
In addition to BigQuery sources, you can execute jobs in BigQuery on source files from .
By default, the Full execution for GCS file option is enabled for new flows. For more information, see Flow Optimization Settings Dialog .
Tip: The BigQuery running environment also supports hybrid sources, so you can use as sources files and BigQuery tables in the same flow.
Supported file formats from :
CSV. CSV files that fail to meet the following requirements may cause job failures when executed in BigQuery, even though they can be imported into . Requirements:
For job execution of CSV files in BigQuery, source CSV files must be well-formatted.
Newlines must be inserted.
Fields must be demarcated with quotes and commas.
Quotes in field value must be escaped with quotes when needed (
For more information, see https://cloud.google.com/bigquery/docs/loading-data-cloud-storage-csv .
Supported file encodings:
Supported quote characters:
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.
$filepathare not supported for pushdown.
The following functions are not currently supported for execution in BigQuery.
For more information, see Aggregate Functions.
NUMFORMAT: Only supported when used for rounding.
For more information, see Math Functions.
DATEFORMAT: Some uncommon formatting options are not supported for pushdown.
For more information, see Date Functions.
For more information, see String Functions.
For more information, see Window Functions.
To verify execution in BigQuery, please do the following:
For more information, see Job Details Page.