...

...

...

• A minimum of data (recipe steps and associated metadata) is transferred between systems. Everything else remains in BigQuery.
• Recipe steps are converted into SQL that is understandable and native to BigQuery. Execution times are much faster.
• Depending on your environment, total cost of executing the job may be lower in BigQuery.

...

• This feature must be enabled by the project owner. See Configure Running Environments.
• The permission to execute jobs in BigQuery must be enabled. In most environments, it is enabled by default. For more information, see Required Dataprep User Permissions.
• In your flow, you must enable all general and BigQuery-specific flow optimizations. When all of these optimizations are enabled, the job can be pushed down to BigQuery for execution. For more information, see Flow Optimization Settings Dialog.

If the requirements and limitations are met, the 

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.

• All datasources and all outputs specified in a job must be located within BigQuery.
• For any job to be executed in BigQuery:
  • All datasources must be located in BigQuery or

    D s storage

    .
  • If the output is a file published to

    D s storage

    , then inputs must be in BigQuery.
• BigQuery does not publish CSV files with quotes. For example, when a column value is empty in transformer, BigQuery publishes it as an empty string.
• If a column string value has quotes or a delimiter, BigQuery encloses that string value with double quotes for CSV and JSON files.
• BigQuery does not write column values that are empty or null while publishing to JSON format.

• D s dataflow

   must be selected as running environment.
• Custom SQL datasets are not supported.

• All recipe steps, including all all 

  D s lang

  functions  functions in the recipe, must be translatable to SQL. 

  Info
  NOTE: When attempting to execute a job in BigQuery, D s webapp executes each recipe in BigQuery, until it reaches a step that cannot be executed there. At that point, data is transferred to D s dataflow , 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, 

  D s product

   falls back to submitting the job through 

  D s dataflow

  .

• If the schemas have changed for your datasets, pushdown execution on BigQuery is not supported. 

  D s product

   falls back to submitting the job through 

  D s dataflow

  .

• Some transformations and functions are not currently supported for execution in BigQuery. See below.
• Upserts, merges, and deletes are not supported for full execution in BigQuery.
• Sampling jobs are not supported for execution in BigQuery.
• If your recipe includes data quality rules, the job cannot be fully executed in BigQuery.
• If your recipe includes data quality rules, the job cannot be fully executed in BigQuery.
• BigQuery does not permit partitioned tables to be replaced. As a result, the Drop and Load publishing action is not supported when writing to a partitioned table during BigQuery execution. For more information, see https://cloud.google.com/bigquery/docs/reference/standard-sql/data-definition-language#create_table_statement. 
• In BigQuery, escaped whitespace characters (\s) match a broader set of Unicode space characters than 

  D s dataflow

  , 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.
• Some uncommon date formats are not supported for pushdown.
• Publication of complex arrays to BigQuery is not supported for jobs executed in the BigQuery running environment. To publish these arrays as non-String values, you must disable all flow optimizations and run the job in 

  D s dataflow

  .

D s storage

 File Support

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.

• For more information, see Google Cloud Storage Access.

Requirements:

• Publishing actions must be defined to be a target in BigQuery. 
• External views must be enabled in BigQuery. External views are used to query GCS files.  For more information, see Required Dataprep User Permissions.
• In the 

  D s webapp

  , the following flow optimization settings must be enabled at the flow level. 
  • BigQuery optimization
  • Full execution for GCS file
• In the Run Job page, the 

  D s dataflow

   + BigQuery running environment must be selected. For more information, see Run Job Page.

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

  D s product

  . 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 ("").

  • Each row must have the same number of columns.

  • For more information, see  https://cloud.google.com/bigquery/docs/loading-data-cloud-storage-csv .

• TSV
• JSON (newline-delimited)
• TXT
• LOG

• Compressed Files (gz and bz)
  

  Info
  NOTE:  Snappy and bz2 file formats are not supported for pushdown execution in BigQuery. When these file formats are encountered as datasources, the job automatically reverts to run on  D s dataflow .

  
  

Supported file encodings:

• UTF-8
• ISO-8859-1

Supported delimiters:

• Comma
• Tab
• Pipe

Supported quote characters:

• No quotes
• double quotes

Unsupported 

D s lang

 for BigQuery Execution

...

General limitations

• Regex patterns used must be valid RE2. Operations on non-RE2 regex patterns are not pushed down.
• Source metadata references such as $rownumber  and $filepath  are not supported for pushdown.
  
• For more information on limitations on specific push-downs, see Flow Optimization Settings Dialog.

Unsupported

...

The following data types are not supported for execution in BigQuery.

• Arrays
• Objects (Maps)

Unsupported transformations

The following transformations are not supported for execution in BigQuery.

Legend:

• Search term: the value you enter in the Transform Builder
• Transform: name of the underlying transform

...

For more information, see Transformation Reference.

transformations

None.

Unsupported functions

The following 

...

KTHLARGEST
KTHLARGESTIF
KTHLARGESTUNIQUE
KTHLARGESTUNIQUEIF
LIST
LISTIF
MODE
MODEIF
UNIQUE
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
CONVERTFROMUTC
CONVERTTOUTC
CONVERTTIMEZONE
MODEDATEIF
KTHLARGESTDATE
KTHLARGESTUNIQUEDATE
KTHLARGESTUNIQUEDATEIF
KTHLARGESTDATEIF
EOMONTH
SERIALNUMBER

Partially supported:DATEDIF

: Only day, hour, minute, second and millisecond are supported as unitsDATEFORMAT: Some uncommon formatting options are not supported for pushdown.

For more information, see Date Functions.

String functions

SUBSTITUTE
PROPER
REMOVESYMBOLS
RIGHTFIND
EXACT
STRINGGREATERTHAN
STRINGGREATERTHANEQUAL
STRINGLESSTHAN
STRINGLESSTHANEQUAL
DOUBLEMETAPHONE
DOUBLEMETAPHONEEQUALS
TRANSLITERATE

For more information, see String Functions.

...

Window functions

ARRAYCONCAT
ARRAYCROSS
ARRAYINTERSECT
ARRAYLEN
ARRAYSTOMAP
ARRAYUNIQUE
ARRAYZIP
FILTEROBJECT
KEYS
ARRAYELEMENTAT
LISTAVERAGE
LISTMAX
LISTMIN
LISTMODE
LISTSTDEV
LISTSUM
LISTVAR
ARRAYSORT
ARRAYINDEXOF
ARRAYMERGEELEMENTS
ARRAYRIGHTINDEXOF
ARRAYSLICE

SESSION

For more information, see Nested Window Functions.

Window functions

SESSION

For more information, see Window Functions.

Other functions

IPTOINT
IPFROMINT

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 Job History 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 Other Functions.Job Details Page.