The following list summarizes the availability of transformations that you can push down to Google BigQuery:

Aggregator
Expression
Filter
Joiner
Lookup
Rank
Router
Sorter
SQL
Union

You can configure full

SQL ELT optimization

to push an Aggregator transformation to process in Google BigQuery.

You can perform the following aggregate calculations:

AVG
COUNT
MAX
MIN
SUM
STDDEV
VARIANCE

When you configure an Aggregator transformation, you must use each of the incoming ports either in an aggregate function or in a group by field to define how to group data for aggregate expressions.

You can configure full

SQL ELT optimization

to push a Lookup transformation to process in Google BigQuery. This applies to both connected and unconnected lookups.

You can add the following lookups:

Cached
Uncached
Unconnected with cached

When you look up data and the lookup condition finds multiple matches, the lookup returns all rows. In a mapping with Google BigQuery as target, you must set the

Multiple Matches

option for the lookup object to

Return all rows

. If you enabled

Multiple Matches

to any option other than

Return all rows

, the agent ignores it.

When you configure a Lookup transformation based on a Google BigQuery source, adhere to the following guidelines:

If there are null values in a lookup column, the mapping does not push the rows with null values to the Google BigQuery target. However, if you run the mapping without full
SQL ELT optimization
, the rows with null values are written to the target.
When you specify multiple lookup conditions, ensure at least one of the lookup condition uses the Equals operator.
Ensure that you specify the same Google BigQuery region ID for the source, lookup, and target connection.
When you use a Lookup transformation, ensure that you select the
Lookup caching enabled
property in the lookup advanced properties.
When you use an unconnected lookup and use an Expression transformation to assign the unconnected Lookup transformation output to a variable port, the mapping runs without
SQL ELT optimization
.
When you use a completely parameterized lookup condition where the input parameter holds the default value and you specify an override from the task using the parameter file, the task does not honor the override and runs with the default value.

When you configure an unconnected Lookup transformation, consider the following rules:

You must select the
Multiple Matches
property value as
Report error
in the unconnected lookup properties for
SQL ELT optimization
to work.
You can only configure an Expression transformation for an output received from an unconnected lookup.

You can use an SQL transformation to push supported scalar functions to Google BigQuery. When you configure

SQL ELT optimization

for a mapping, you can use Java or SQL user-defined functions (UDFs) in a SQL transformation and run queries with the Google BigQuery target endpoint.

You can use only the SELECT clause SQL statement to push down a function. The following snippet demonstrates the syntax of a simple SELECT SQL query:

SELECT <function_name1>(~Arg~), <function_name2> (~Arg~)...

You can push a SQL transformation with the following restrictions:

You can configure only a SQL query in the SQL transformation. You cannot enable a stored procedure when you push down to Google BigQuery.
The SQL query must be a simple SELECT statement without 'FROM' and 'WHERE' arguments. The SQL transformation only supports functions with simple SELECT statement.
You can only use a SQL transformation when the SELECT statement is present in the query property. Even if an entire query containing the SELECT statement comes from a parameterized input port, the
SQL ELT optimization
fails.
If any SQL error occurs, the error is added to the
SQLError
field by default. However, when you run a mapping enabled with
SQL ELT optimization
, the
SQLError
field remains as Null.
The
NumRowsAffected
field records the number of rows affected while computing the output buffer. However, for SQL transformation, the
NumRowsAffected
is 0, as the query runs for all the records at the same time.
Google BigQuery offers only passive behavior of SQL transformations where the support for dynamic queries are limited.
User defined functions containing special characters in its function name are supported. You need to enclose the Full UDF function name with backtick (`) character if it contains special characters.
You cannot specify the user defined functions in a legacy SQL query.
You cannot use sub-query and join condition in the SQL transformation.
You cannot use temporary UDF in the SQL transformation.
You cannot use the following parameterization scenarios:
Entire query as a parameter
Field names in a query as a parameter
In-out and input parameters in a query