Table of Contents

Search

  1. Preface
  2. Part 1: Introduction to Google BigQuery connectors
  3. Part 2: Data Integration with Google BigQuery V2 Connector
  4. Part 3: Data Integration with Google BigQuery Connector

Google BigQuery Connectors

Google BigQuery Connectors

Rules and guidelines for SQL ELT optimization

Rules and guidelines for
SQL ELT optimization

Certain rules and guidelines apply when you enable a mapping for
SQL ELT optimization
to a
Google BigQuery
database.
When you configure a
Google BigQuery
source, Google Cloud Storage source, or
Google BigQuery
target, adhere to the following guidelines:
  • The Service Account ID associated with the
    Google BigQuery V2
    connection must have permissions to access Google Cloud Storage buckets and files.
  • You cannot enable full
    SQL ELT optimization
    for a mapping task when the target table contains columns of record data type or repeated columns.
  • You cannot enable full
    SQL ELT optimization
    for a mapping task when the task contains a mapping with a single transformation connected to multiple transformations downstream or multiple transformations connected to a single transformation.
  • You must ensure that the column header names in the Google Cloud Storage source file does not contain unicode characters. Otherwise, the mapping fails.
  • When you enable
    SQL ELT optimization
    for a mapping with multiple pipelines to write to the same
    Google BigQuery
    target and the Truncate target table option is enabled in each pipeline, the target table is truncated for each pipeline when data is inserted into the target.
    For example, if there are two pipelines, in pipeline 1 the target table is truncated and then the data is inserted. Similarly, in Pipeline 2 the target table is truncated and the data is inserted into the target table. Hence, the target table contains only the data from pipeline 2.
    When you run a mapping without
    SQL ELT optimization
    and the mapping contains multiple pipelines, the target tables are truncated at once for all pipelines and then the data is inserted.
  • When you configure a Filter transformation or specify a filter condition, you must ensure that you do not specify special characters. Use the ASCII value for the special character in the filter condition.
  • When you parameterize the
    Google BigQuery V2
    connection, source, target and provide values in the mapping task using a parameter file, the default values for the parameter are not overridden with the values in the parameter file.
  • If the Google Cloud Storage source file contains a column of Boolean data type,
    SQL ELT optimization
    query fails.
  • You must ensure that the Google BigQuery source object does not contain any partitions.
  • When you read from a
    Google BigQuery
    source and edit the metadata for the source fields, the Secure Agent ignores the changes to the metadata.
  • If the
    Google BigQuery
    source and target object resides in the same region other than US, do not specify the
    Region ID
    explicitly in the connection.
  • You must not specify a legacy SQL query in the
    pre SQL
    and
    post SQL
    advanced source or target properties.
  • A mapping run without
    SQL ELT optimization
    fails if any of the Pre-SQL and Post-SQL commands fail in a multi-statement query. Previously mappings were successful.
  • When you specify custom query as a source object and specify a dataset name in the
    Source Dataset ID
    source advanced property, the mapping runs without full
    SQL ELT optimization
    .
  • When you specify custom query as a source object and specify an SQL override query, you must specify a dataset name in the
    Source Dataset ID
    source advanced property.
  • When you specify custom query as a source object and specify an SQL override query with different column names, ensure that the data types and the order of the columns that appear in the SQL override query matches the data types and order in which they appear in the custom query.
  • When you select a view as a source object that contain columns of the Record data type or repeated columns and create a new target at runtime, a validation error appears in the session logs and the mapping runs without full
    SQL ELT optimization
    .
  • To load data into columns of date, time, datetime, or timestamp in a
    Google BigQuery
    target, you must pass the data through the TO_DATE() function as an expression and map the results to the target column.
  • When you set SCD Type 2 merge optimization context for a mapping, you cannot use filter, joiner, and custom SQL query.
  • If the mapping contains a Router transformation output connected to a Sequence Generator transformation, the mapping does not push down the mapping logic to the point where the transformation is supported and runs without
    SQL ELT optimization
    .
  • When you push down a Router transformation with IIF and IS_SPACE() functions in mapping that reads from and writes to
    Google BigQuery
    , and the Boolean values are 0 and 1, the mapping fails. When the Boolean values are true and false, the mapping runs successfully.
  • When you use multiple functions within a transformation and one of the functions cannot be pushed to
    Google BigQuery
    , the mapping runs without
    SQL ELT optimization
    .
  • When the mapping contains multiple pipelines and a function within one of transformations cannot be pushed to
    Google BigQuery
    , the mapping does not push down the mapping logic to the point where the transformation is supported and the mapping runs without
    SQL ELT optimization
    .
  • When you read from or write data to
    Google BigQuery
    objects associated with different projects in different Google service accounts that resides in different regions, the mapping runs without
    SQL ELT optimization
    .
  • When you use the data driven operation to write data to a
    Google BigQuery
    target and enable the
    Disable Duplicate Update Rows
    target advanced property, the Secure Agent ignores the
    Disable Duplicate Update Rows
    property.
  • When you read data from a
    Google BigQuery
    source that contains duplicate update keys and enable the
    Disable Duplicate Update Rows
    target advanced property, the Secure Agent ignores the
    Disable Duplicate Update Rows
    property.
  • When you configure a mapping that includes any of the following datetime scenarios, the mapping runs without
    SQL ELT optimization
    :
    • Map data from the TIME data type to any other date/time data type
    • Map data from the DATE data type to the TIME data type
    • Compare data of the TIME and TIMESTAMP data types with the DATE or DATETIME data types
  • When you use
    $$$SESSSTARTTIME
    variable in a custom query, the variable returns the session start time as a string value. Use the following syntax to convert the string values to timestamp or datetime:
    • SELECT PARSE_DATETIME('%m/%d/%Y %H:%M:%E6S', '$$$SESSSTARTTIME' ) as t1;
    • SELECT cast(substr(cast('$$$SESSSTARTTIME' as string),0,19) as datetime FORMAT 'MM/DD/YYYY HH24:MI:SS') as datetime;
    Ensure that the time zones of the
    Google BigQuery
    project and the agent machine are the same.
  • When you set the
    OptimizeCastsInPDO:true
    custom property in the advanced settings for a
    Google BigQuery V2
    connection, the SESSSTARTTIME, SYSDATE, SYSTIMESTAMP, and TO_DATE functions return data of DATETIME data type.
  • When you configure a native filter in the Source transformation, ensure that you do not prefix the field name with the table name and dataset name in the filter expression. Otherwise, the mapping fails.
  • When you configure an insert operation and set the
    Write Disposition
    property as Write Truncate in the target transformation properties, the mapping appends the records to the target table instead of truncating the target table before loading data. To configure a truncation when you insert records, you need to select the
    Truncate target table
    option in the target advanced properties.
  • When you create a target table at runtime and perform DML operations, the mapping might fail if the expression port returns a null value of a non-integer data type.

0 COMMENTS

We’d like to hear from you!