Rules and guidelines for mappings that read from and write to Google BigQuery
Rules and guidelines for mappings that read from and write to Google BigQuery
When you configure
SQL ELT optimization
in a mapping that reads from and writes to Google BigQuery, consider the following guidelines:
When you perform an upsert operation, you must select the Enable Merge option in the target advanced properties.
You cannot use system variables in filters.
If a mapping contains a Filter transformation and also a filter in the Source transformation, the mapping consolidates the filter conditions from both these transformations to filter the records. However, it is recommended that you use only one of these filters at a time in a mapping.
You cannot apply a filter for query and multiple source objects.
A native filter cannot contain a sub-query.
When you select the source type as
Query
, ensure that you do not select the
Retain existing fields at runtime
option on the
Fields
tab. Otherwise, the mapping fails with the following error:
Error: The Secure Agent failed to run the full SQL ELT query due to the following error: [Field not found inside table]
When you configure a Target transformation in a mapping with a delete operation and the source type uses a query that contains Union ALL, the mapping fails. To avoid this error, before you run the mapping, you need to select the
Enable Merge
property in the target advanced properties. The mapping issues a merge query and runs successfully.
If the source data that the mapping read contains the Binary data types, data preview for