Google BigQuery Connectors > Part II: Data Integration with Google BigQuery V2 Connector > Google BigQuery pushdown optimization > Google BigQuery Pushdown through an ODBC Connection
  

Google BigQuery Pushdown through an ODBC Connection

Use an ODBC connection to enable full or source pushdown optimization when you want to read data from a Google BigQuery source, write to an Google BigQuery target, and if you want to run the mapping logic entirely within Google BigQuery.
When you run a task configured for pushdown optimization, the task converts the transformation logic to an SQL statement. The task sends the SQL statement to the database, and the database executes the SQL statement.
Example
You work for a rapidly growing data science organization. Your organization develops software products to analyze financials, building financial graphs connecting people profiles, companies, jobs, advertisers, and publishers. The organization uses infrastructure based on Google Cloud Platform and stores its data in Google BigQuery, a petabyte scale data warehouse. The organization plans to implement a business intelligence service to build visualization and perform real-time analysis. Therefore, you need to port the vast amount of data stored in Google BigQuery to the business intelligence service. You can use Google BigQuery V2 Connector to read data from Google BigQuery. To read this large amount of data, you can use source pushdown for the ODBC connection type. Using the ODBC connection type with pushdown optimization enhances the performance.

Prequisites

When you run a mapping task for full pushdown optimization, you must have the following permissions:
Note: When you configure the Create Temporary View in the mapping task, you must have the bigquery.tables.create and bigquery.tables.delete to permissions create and drop views.
The dataset configured to create and drop views is used as the default dataset in the Google BigQuery ODBC driver.

Configuring a Google BigQuery ODBC connection

You can set the pushdown optimization for the ODBC connection type that uses Google BigQuery ODBC driver to enhance the mapping performance. To use an ODBC connection to connect to Google BigQuery, you must configure the ODBC connection.
You cannot configure target-side pushdown optimization by using Google BigQuery ODBC driver. To verify that the pushdown optimization occurred successfully, you can check the session log for the job. In Monitor, view the log for jobs.
Google BigQuery supports Google BigQuery ODBC drivers on Windows and Linux systems. You can install one of the following 64-bit drivers based on your system requirement:

Configuring a Google BigQuery ODBC connection using Informatica ODBC Driver for Google BigQuery

Google BigQuery supports Informatica ODBC Driver for Google BigQuery on Windows and Linux systems. You can install the 2.3.3.1005 version or 2.2.5.1012 version of the Informatica ODBC Driver for Google BigQuery based on your system requirement.
For more information about downloading the Informatica ODBC Driver for Google BigQuery, contact Informatica Global Customer Support.

Configuring Google BigQuery ODBC connection on Windows

To establish an ODBC connection to connect to Google BigQuery on Windows using the Informatica ODBC Driver for Google BigQuery, install the Informatica ODBC driver for Google BigQuery, version 2.3.3.1005 or 2.2.5.1012, on the Windows machine where the Secure Agent runs and configure the ODBC connection.
    1. Download the InformaticaODBCDriverforGoogleBigQuery_<version>_Windows.zip file, Register.bat file, and the DriverInstallHelper.jar file.
    For more information about downloading the Informatica Google BigQuery ODBC (64-bit) driver, Register.bat file, and the DriverInstallHelper.jar file, contact Informatica Global Customer Support.
    Note: Download the Register.bat and DriverInstallHelper.jar files in the same location.
    2. After you download the Informatica Google BigQuery ODBC (64-bit) driver, extract the downloaded file.
    3. Navigate to the following directory where you extracted the InformaticaODBCDriverforGoogleBigQuery_<version>_Windows.zip file:
    <Informatica ODBC Driver for Google BigQuery installation directory>\InformaticaODBCDriverforGoogleBigQuery_<version>_Windows
    4. Extract the SimbaODBCDriverforGoogleBigQuery64_<version>.zip file to the following directory.
    SimbaODBCDriverforGoogleBigQuery64_<version>.zip file: <SimbaODBCDriverforGoogleBigQuery64_<version> installation directory>\SimbaODBCDriverforGoogleBigQuery64_<version>\
    5. Navigate to the directory where you downloaded the Register.bat and DriverInstallHelper.jar file.
    The Register.bat file prompts you to specify the Input Installation Directory. Specify the directory where you installed the Informatica ODBC Driver for Google BigQuery.
    6. Open the Command Prompt as an administrator and navigate to the directory where you downloaded the Register.bat and DriverInstallHelper.jar file.
    7. Run the following command in the command prompt: Register.bat
    Note: You must have administrator privileges to run the Register.bat file.
    The Register.bat file prompts you to specify the Input Directory Containing Registry File Template and Input Directory Containing Installation Files.
    8. Specify the following directory for the Input Directory Containing Registry File Template and press Enter:
    <Informatica ODBC Driver for Google BigQuery installation directory>\InformaticaODBCDriverforGoogleBigQuery_<version>_Windows\setup
    9. Specify the following directory for the Input Directory Containing Installation Files and press Enter:
    <SimbaODBCDriverforGoogleBigQuery64_<version> installation directory>\SimbaODBCDriverforGoogleBigQuery64_<version>\
    The Register.bat file creates the output.txt file and returns success or an error message.
    10. Click Start > Control Panel.
    11. Click Administrative Tools.
    12. Click Data Sources (ODBC).
    The ODBC Data Source Administrator dialog box appears.
    13. Click the System DSN tab.
    The System DSN tab appears.
    The following image shows the System DSN tab on the ODBC Data Source Administrator (64-bit) dialog box:
    The image shows the System DSN on the ODBC Data Source Administrator (64-bit) dialog box.
    14. Click Add.
    The Create New Data Source dialog appears.
    The following image shows the Create New Data Source dialog where you can select the Google BigQuery data source:
    The following image shows the Informatica ODBC driver for Google BigQuery available for your operating system
    15. Select the Informatica ODBC Driver for Google BigQuery to set up the data source.
    16. Click Finish.
    The Informatica ODBC Driver for Google BigQuery DSN Setup dialog box appears.
    The following image shows the connection properties in the Informatica ODBC Driver for Google BigQuery DSN Setup dialog box:The following image shows the connection configuration properties in the Informatica ODBC Driver for Google BigQuery DSN Setup dialog box
    17. Specify the following connection properties:
    Property
    Description
    Data Source Name
    Enter a name for the data source. The ODBC Driver for Google BigQuery uses the DSN to connect to the Google BigQuery.
    Description
    Enter a description.
    OAuth Mechanism
    The OAuth 2.0 authentication mechanism used to authenticate the driver. Select the Service Authentication option to authenticate the driver through a Google service account.
    If you select the User Authentication option, you will need to sign in to your Google service account. Click on Sign In and in the browser that opens, enter your credentials and then click Sign In. Click Accept to allow the Google BigQuery Client Tools to access Google BigQuery objects.
    Confirmation Code
    Code that Google provides when you click Accept. Copy and paste the code in the Confirmation Code field.
    Refresh Token
    The OAuth Mechanism populates the Refresh Token field when you paste the confirmation code.
    Email
    Specify the Google service account email ID. This field is needed to authenticate the service account.
    Key File Path
    Enter the path to the .p12 or JSON key file that is used to authenticate the Google service account.
    Request Google Drive scope access
    Allows the driver to access Google Drive so that the driver can support federated tables that combine Google BigQuery data with data from Google Drive.
    Trusted Certificates
    Path of the .pem file. Use the trusted CA certificates from a specific.pem file or use the trusted CA certificates .pem file that is installed with the driver.
    Note: If you specify the Trusted Certificates .pem file path, you do not need to select the Use System Trust Store option.
    Use System Trust Store
    If you select the Use System Trust Store option, you do not need to specify the Trusted Certificates .pem file path.
    Catalog (Project)
    Name of the Google BigQuery project associated with your billing account that the Simba ODBC Driver for Google BigQuery queries against.

Configuring existing data sources on Windows to use Informatica ODBC Driver for Google BigQuery

If you have created a data source using the 2.1.19.1024 version of the Simba ODBC Driver for Google BigQuery and you want the same data source to use the 2.2.5.1012 version of the Informatica ODBC Driver for Google BigQuery on a Windows machine, perform the following steps after you configure the Informatica ODBC Driver for Google BigQuery:
  1. 1. Open the Windows Registry Editor.
  2. 2. Navigate to following location on the Registry Editor:
  3. HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI\ODBC Data Sources
  4. 3. Select the existing DSN and modify the registry value from Simba ODBC Driver for Google BigQuery to Informatica ODBC Driver for Google BigQuery.
  5. 4. Navigate to following location on the Registry Editor:
  6. HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI\<OLD DSN KEY>
  7. 5. Modify the value of the Driver registry from Simba ODBC Driver for Google BigQuery to Informatica ODBC Driver for Google BigQuery.
  8. 6. Modify the value of the TrustedCerts registry to the following location:
  9. <SimbaODBCDriverforGoogleBigQuery64_2.2.5.1012 installation directory>/lib/cacerts.pem

Configuring Google BigQuery ODBC connection on Linux

To establish an ODBC connection to connect to Google BigQuery on Linux using the Informatica ODBC Driver for Google BigQuery, install the Informatica Google BigQuery ODBC (64-bit) driver, version 2.3.3.1005 or 2.2.5.1012, on the Linux machine where the Secure Agent runs and configure the ODBC connection.
    1. Download the InformaticaODBCDriverforGoogleBigQuery_<version>-Linux.tar.gz file.
    For more information about downloading the Informatica Google BigQuery ODBC (64-bit) driver, contact Informatica Global Customer Support.
    2. After you download the Informatica Google BigQuery ODBC (64-bit) driver, use the following command to extract the downloaded file:
    tar -xvf InformaticaODBCDriverforGoogleBigQuery_<version>-Linux.tar.gz
    3. Navigate to the following directory where you extracted the InformaticaODBCDriverforGoogleBigQuery_<version>-Linux.tar.gz file:
    <Informatica ODBC Driver for Google BigQuery installation directory>/InformaticaODBCDriverforGoogleBigQuery_<version>-Linux
    4. Use the following command to extract the SimbaODBCDriverforGoogleBigQuery64_<version>.tar.gz file to any directory:
    tar -xvf SimbaODBCDriverforGoogleBigQuery64_<version>.tar.gz
    Consider the following directory where you extracted the SimbaODBCDriverforGoogleBigQuery64_<version>.tar.gz file: <SimbaODBCDriverforGoogleBigQuery64_<version> installation directory>/SimbaODBCDriverforGoogleBigQuery64_<version>
    5. Move the GoogleBigQueryODBC.did file from the <Informatica ODBC Driver for Google BigQuery installation directory>/InformaticaODBCDriverforGoogleBigQuery_<version>-Linux folder to the <SimbaODBCDriverforGoogleBigQuery64_<version> installation directory>/SimbaODBCDriverforGoogleBigQuery64_<version>/lib folder.
    6. Move the simba.googlebigqueryodbc.ini file from the <Informatica ODBC Driver for Google BigQuery installation directory>/InformaticaODBCDriverforGoogleBigQuery_<version>-Linux/setup folder to the <SimbaODBCDriverforGoogleBigQuery64_<version> installation directory>/SimbaODBCDriverforGoogleBigQuery64_<version>/lib folder.
    7. Navigate to the <SimbaODBCDriverforGoogleBigQuery64_<version> installation directory>/SimbaODBCDriverforGoogleBigQuery64_<version>/lib folder and rename the simba.googlebigqueryodbc.ini file to informatica.googlebigqueryodbc.ini.
    8. Edit the informatica.googlebigqueryodbc.ini file with the following:
    9. Create the odbc.ini file and add the following properties:
    [ODBC Data Sources]
    GBQ_ODBC=Simba ODBC Driver for Google BigQuery 64-bit
    Description=<DSN Description>
    [Sample DSN]
    Driver=<SimbaODBCDriverforGoogleBigQuery64_<version> installation directory>/lib/libgooglebigqueryodbc_sb64.so
    Catalog=<project_id>
    DefaultDataset=<Dataset name>
    SQLDialect=<1>
    OAuthMechanism=0
    Email=<Client Email ID of the Google service account>
    KeyFilePath=<.p12 or JSON key file path>
    For example,
    [ODBC Data Sources]
    GBQ_ODBC=Simba ODBC Driver for Google BigQuery 64-bit
    [GBQ_ODBC_IICS]
    Driver=<SimbaODBCDriverforGoogleBigQuery64_<version> installation directory>/lib/libgooglebigqueryodbc_sb64.so
    Catalog=api-project-1243343
    DefaultDataset=QATEST
    SQLDialect=1
    OAuthMechanism=0
    Email=simbaaccount@api-project-1243343.iam.gserviceaccount.com
    KeyFilePath=/export/Simba_GBQ_ODBC/API Project-c993e990af5.json
    10. Specify the following properties in the odbc.ini file:
    Property
    Description
    ODBC Data Sources
    Name of the data source.
    Description
    Description of the data source.
    Driver
    Full path of the Simba ODBC Driver for Google BigQuery library file.
    Catalog
    Name of the Google BigQuery project associated with your billing account that the Simba ODBC Driver for Google BigQuery queries against.
    SQLDialect
    The SQL dialect used to run queries against the Google BigQuery tables using the DSN.
    To perform pushdown optimization, specify the value of SQLDialect property as 1.
    DefaultDataset
    Specify a dataset name in Google BigQuery that the Simba ODBC driver queries by default.
    OAuthMechanism
    The OAuth 2.0 authentication mechanism used to authenticate the driver.
    To authenticate the driver through a Google service account, specify the value of OAuthMechanism property as 0.
    Specify the value of the Email property to the Google service account email ID.
    To authenticate the driver through a Google user account, specify the value of OAuthMechanism property as 1.
    Obtain a Refresh Token based on your Google user account and set the following property in the DSN:
    Auth_RefreshToken=<Refresh token value>
    If you already have your refresh token, then you can set the following property in the DSN:
    RefreshToken=<Refresh token value>
    Email
    Applicable when you set the value of OAuthMechanism property as 0.
    Specify the value of the Email property to the Google service account email ID.
    KeyFilePath
    Applicable when you set the value of OAuthMechanism property as 0.
    Enter the path to the .p12 or JSON key file that is used to authenticate the Google service account.
    RefreshToken
    Applicable when you set the value of OAuthMechanism property as 1.
    Specify the refresh token associated with the Google user account.
    11. Run the following command to set the environment variable ODBCINI:
    Export ODBCINI=/<odbc.ini file path>/odbc.ini
    12. Restart the Secure Agent.
The Google BigQuery ODBC connection on Linux is configured successfully.
After you configure the Google BigQuery ODBC connection, you must create an ODBC connection to connect to Google BigQuery.
For more information about how to create an ODBC connection to connect to Google BigQuery, see Create an ODBC connection.

Configuring existing data sources on Linux to use Informatica ODBC Driver for Google BigQuery

If you have created a data source using the 2.1.19.1024 version of the Simba ODBC Driver for Google BigQuery and you want the same data source to use the 2.2.5.1012 version of the Informatica ODBC Driver for Google BigQuery on a Linux machine, perform the following steps after you configure the Informatica ODBC Driver for Google BigQuery:
  1. 1. Edit the odbc.ini file and update the Driver property for the DSN created with the 2.1.19.1024 version of the Simba ODBC Driver for Google BigQuery and change the value to the location of the 2.2.5.1012 version of the Informatica ODBC Driver for Google BigQuery.
  2. For example, set the following value for the Driver property:
    <SimbaODBCDriverforGoogleBigQuery64_2.2.5.1012 installation directory>/lib/libgooglebigqueryodbc_sb64.so
  3. 2. After you configure the odbc.ini file, you must create an ODBC connection using the same data source in Data Integration. You must ensure that the test connection is successful.

Configuring a Google BigQuery ODBC connection using Simba ODBC Driver for Google BigQuery

You can download the 2.1.19.1024 version of the Simba ODBC Driver with SQL Connector for Google BigQuery from your Simba account for Windows or Linux 64-bit operating system.
Google BigQuery supports ODBC Driver for Google BigQuery on Windows and Linux systems. You must install the 2.1.19.1024 version of the Simba ODBC Driver for Google BigQuery 64-bit driver based on your system requirement.

Configuring a Google BigQuery ODBC connection on Windows

To establish an ODBC connection to connect to Google BigQuery on Windows, you must download the 64-bit Google BigQuery ODBC driver on the machine where Secure Agent runs.
For more information about installing the Simba ODBC Driver for Google BigQuery on Windows, see the Simba ODBC Driver for Google BigQuery Install and Configuration Guide located in the following directory:
<SimbaODBCDriverforGoogleBigQuery64_2.1.20.1025 installation directory>\Simba ODBC Driver for Google BigQuery
Perform the following steps to configure an ODBC connection on Windows:
    1. Click Start > Control Panel.
    2. Click Administrative Tools.
    3. Click Data Sources (ODBC).
    The ODBC Data Source Administrator dialog box appears.
    4. Click the System DSN tab.
    The System DSN tab appears.
    The following image shows the System DSN tab on the ODBC Data Source Administrator (64-bit) dialog box:
    The image shows the System DSN on the ODBC Data Source Administrator (64-bit) dialog box.
    5. Click Add.
    The Create New Data Source dialog appears.
    The following image shows the Create New Data Source dialog where you can select the Google BigQuery data source:
    The following image shows the Simba ODBC driver for Google BigQuery available for your operating system
    6. Select the Simba ODBC Driver for Google BigQuery to set up the data source.
    7. Click Finish.
    The Simba ODBC Driver for Google BigQuery DSN Setup dialog box appears.
    The following image shows the connection properties in the Simba ODBC Driver for Google BigQuery DSN Setup dialog box:The following image shows the connection configuration properties in the Simba ODBC Driver for Google BigQuery DSN Setup dialog box
    8. Specify the following connection properties:
    Property
    Description
    Data Source Name
    Enter a name for the data source. The ODBC Driver for Google BigQuery uses the DSN to connect to the Google BigQuery.
    Description
    Enter a description.
    OAuth Mechanism
    The OAuth 2.0 authentication mechanism used to authenticate the driver. Select the Service Authentication option to authenticate the driver through a Google service account.
    If you select the User Authentication option, you will need to sign in to your Google service account. Click on Sign In and in the browser that opens, enter your credentials and then click Sign In. Click Accept to allow the Google BigQuery Client Tools to access Google BigQuery objects.
    Confirmation Code
    Code that Google provides when you click Accept. Copy and paste the code in the Confirmation Code field.
    Refresh Token
    The OAuth Mechanism populates the Refresh Token field when you paste the confirmation code.
    Email
    Specify the Google service account email ID. This field is needed to authenticate the service account.
    Key File Path
    Enter the path to the .p12 or JSON key file that is used to authenticate the Google service account.
    Request Google Drive scope access
    Allows the driver to access Google Drive so that the driver can support federated tables that combine Google BigQuery data with data from Google Drive.
    Trusted Certificates
    Path of the .pem file. Use the trusted CA certificates from a specific.pem file or use the trusted CA certificates .pem file that is installed with the driver.
    Note: If you specify the Trusted Certificates .pem file path, you do not need to select the Use System Trust Store option.
    Use System Trust Store
    If you select the Use System Trust Store option, you do not need to specify the Trusted Certificates .pem file path.
    Catalog (Project)
    Name of the Google BigQuery project associated with your billing account that the Simba ODBC Driver for Google BigQuery queries against.
    9. Click Proxy Options to configure the connection to a data source through a proxy server.
    10. Click Advanced Options to configure the Simba ODBC Driver for Google BigQuery advanced properties. To perform pushdown optimization, select Standard SQL from the Language Dialect option in the Advanced Options dialog box.
    11. To enable logging, click Logging Options.
    12. Click Test to verify the connection to Google BigQuery.
    13. Click OK to close the Simba ODBC Driver for Google BigQuery DSN Setup dialog box.
    14. Click OK to close the ODBC Data Source Administrator dialog box
The Google BigQuery ODBC connection is configured successfully on Windows.
After you configure the Google BigQuery ODBC connection, you must create an ODBC connection to connect to Google BigQuery. For more information about how to create an ODBC connection to connect to Google BigQuery, see Create an ODBC connection.

Configuring a Google BigQuery ODBC connection on Linux

To establish an ODBC connection to connect to Google BigQuery on Linux, install the Google BigQuery ODBC (64-bit) driver on the Linux machine where the Secure Agent runs and configure the ODBC connection.
For more information about installing the Simba ODBC Driver for Google BigQuery on Linux, see the Simba ODBC Driver for Google BigQuery Install and Configuration Guide located in the following directory:
<SimbaODBCDriverforGoogleBigQuery64_2.1.20.1025 installation directory>\simba\googlebigqueryodbc
Perform the following steps to configure an ODBC connection on Linux:
    1. Open the simba.googlebigqueryodbc.ini configuration file located in the following directory:
    <Driver installation directory>/simba/googlebigqueryodbc/lib/64/
    2. Add the following property
    DriverManagerEncoding=UTF-16
    3. Save the simba.googlebigqueryodbc.ini configuration file.
    4. Copy the simba.googlebigqueryodbc.ini configuration file to the following directory:
    <SimbaODBCDriverforGoogleBigQuery64_2.1.20.1025 installation directory>/simba/Setup
    5. Create the odbc.ini file and add the following properties:
    [ODBC Data Sources]
    Sample DSN=Simba ODBC Driver for Google BigQuery 64-bit
    Description=<DSN Description>
    [Sample DSN]
    Driver=<SimbaODBCDriverforGoogleBigQuery64_2.1.20.1025 installation directory>/simba/googlebigqueryodbc/lib/64/libgooglebigqueryodbc_sb64.so
    Catalog=<project_id>
    DefaultDataset=<Dataset name>
    SQLDialect=<1>
    OAuthMechanism=0
    Email=<Client Email ID of the Google service account>
    KeyFilePath=<.p12 or JSON key file path>
    For example,
    [ODBC Data Sources]
    GBQ_ODBC=Simba ODBC Driver for Google BigQuery 64-bit
    [GBQ_ODBC]
    Driver=/export/googlebigqueryodbc/lib/64/libgooglebigqueryodbc_sb64.so
    Catalog=api-project-1243343
    DefaultDataset=QATEST
    SQLDialect=1
    OAuthMechanism=0
    Email=simbaaccount@api-project-1243343.iam.gserviceaccount.com
    KeyFilePath=/export/Simba_GBQ_ODBC/API Project-c993e990af5.json
    6. Specify the following properties in the odbc.ini file:
    Property
    Description
    ODBC Data Sources
    Name of the data source.
    Description
    Description of the data source.
    Driver
    Full path of the Simba ODBC Driver for Google BigQuery library file.
    Catalog
    Name of the Google BigQuery project associated with your billing account that the Simba ODBC Driver for Google BigQuery queries against.
    SQLDialect
    The SQL dialect used to run queries against the Google BigQuery tables using the DSN.
    To perform pushdown optimization, specify the value of SQLDialect property as 1.
    DefaultDataset
    Specify a dataset name in Google BigQuery that the Simba ODBC driver queries by default.
    OAuthMechanism
    The OAuth 2.0 authentication mechanism used to authenticate the driver.
    To authenticate the driver through a Google service account, specify the value of OAuthMechanism property as 0.
    Specify the value of the Email property to the Google service account email ID.
    To authenticate the driver through a Google user account, specify the value of OAuthMechanism property as 1.
    Obtain a Refresh Token based on your Google user account and set the following property in the DSN:
    Auth_RefreshToken=<Refresh token value>
    If you already have your refresh token, then you can set the following property in the DSN:
    RefreshToken=<Refresh token value>
    Email
    Applicable when you set the value of OAuthMechanism property as 0.
    Specify the value of the Email property to the Google service account email ID.
    KeyFilePath
    Applicable when you set the value of OAuthMechanism property as 0.
    Enter the path to the .p12 or JSON key file that is used to authenticate the Google service account.
    RefreshToken
    Applicable when you set the value of OAuthMechanism property as 1.
    Specify the refresh token associated with the Google user account.
    7. Run the following command to set the environment variable ODBCINI:
    Export ODBCINI=/<odbc.ini file path>/odbc.ini
    8. Restart the Secure Agent.
The Google BigQuery ODBC connection on Linux is configured successfully.
After you configure the Google BigQuery ODBC connection, you must create an ODBC connection to connect to Google BigQuery.
For more information about how to create an ODBC connection to connect to Google BigQuery, see Create an ODBC connection.

Create an ODBC connection

You must create an ODBC connection to connect to Google BigQuery after you configure the ODBC connection.
Perform the following steps to create a Google BigQuery ODBC connection on the Connections page:
    1. In Administrator, click Connections.
    The Connections page appears.
    2. Click New Connection.
    The New Connection page appears. The following image shows the New Connection page:
    The image shows the New Connection page where you can create a ODBC connection.
    3. Configure the following connection details in the Connection Details section:
    Property
    Description
    Connection Name
    Name of the ODBC connection. For example, sf_odbc.
    Description
    Description of the connection.
    Type
    Type of the connection.
    Select the type of the connection as ODBC.
    4. Configure the following connection details in the ODBC Connection Properties section:
    Property
    Description
    Runtime Environment
    Runtime environment that contains the Secure Agent you can use to access the system.
    User Name
    Username to log in to the Google BigQuery database.
    Password
    Password to log in to the Google BigQuery database.
    Data Source Name
    Enter the name of the ODBC data source name that you created for the Google BigQuery database.
    Schema
    Mandatory. Name of the Google BigQuery schema.
    Code Page
    The code page of the database server or flat file defined in the connection.
    ODBC Subtype
    Select Google BigQuery.
    Driver Manager for Linux
    The driver that the Google BigQuery ODBC driver manager sends database calls to. Select unixODBC 2.3.4 to connect to Google BigQuery.
The Google BigQuery ODBC connection is created successfully.

Configuring optimization for a Google BigQuery ODBC mapping task

Perform the following steps to configure pushdown optimization in a Google BigQuery ODBC mapping task:
    1. Create a mapping to read from or write to Google BigQuery:
    1. a. Use the Google BigQuery ODBC connection in the Source transformation.
    2. b. Use the Google BigQuery ODBC connection in the Target transformation.
    2. Create a mapping task.
    1. a. Select the configured mapping.
    2. b. In the Pushdown Optimization section on the Schedule tab, set the pushdown optimization value to Full or To Source.
    3. c. Save the task and click Finish.
When you run the mapping task, the transformation logic is pushed to the Google BigQuery database. To verify that the pushdown optimization has taken place, you can check the session log for the job. In Monitor, view the log for jobs.

Configuring the Secure Agent for pushdown optimization

Before you configure pushdown optimization, you must add the AutoCommit flag in the Custom Configuration Details for the Secure Agent ans set the value to Yes.
To add the AutoCommit flag, perform the following steps:
  1. 1. In Administrator, select Runtime Environments.
  2. The Runtime Environments page appears.
  3. 2. Select the Secure Agent for which you want to set the AutoCommit property from the list of available Secure Agents.
  4. 3. In the upper-right corner, click Edit.
  5. 4. In the Custom Configuration Details section, select the Service as Data Integration Server and Type as DTM.
  6. 5. Add AutoCommit in the Name field and set the Value as Yes.
  7. The following image shows the Custom Configuration Details section:

Supported functions and operators for Google BigQuery ODBC mappings

The following table summarizes the availability of pushdown functions in a Google BigQuery database. Columns marked with an X indicate that the function can be pushed to the Google BigQuery database by using source-side or full pushdown optimization. Columns marked with a dash (-) symbol indicate that the function cannot be pushed to the database.
Function
Pushdown
Function
Pushdown
Function
Pushdown
ABORT()
-
INITCAP()
-
REG_MATCH()
-
ABS()
X
INSTR()
X
REG_REPLACE
-
ADD_TO_DATE()
X
IS_DATE()
X
REPLACECHR()
X
AES_DECRYPT()
-
IS_NUMBER()
-
REPLACESTR()
X
AES_ENCRYPT()
-
IS_SPACES()
-
REVERSE()
-
ASCII()
-
ISNULL()
X
ROUND(DATE)
X
AVG()
X
LAST()
-
ROUND(NUMBER)
X
CEIL()
X
LAST_DAY()
X
RPAD()
X
CHOOSE()
-
LEAST()
-
RTRIM()
X
CHR()
X
LENGTH()
X
SET_DATE_PART()
-
CHRCODE()
-
LN()
-
SIGN()
-
COMPRESS()
-
LOG()
-
SIN()
X
CONCAT()
X
LOOKUP
-
SINH()
-
COS()
X
LOWER()
X
SOUNDEX()
-
COSH()
-
LPAD()
X
SQRT()
X
COUNT()
X
LTRIM()
X
STDDEV()
-
CRC32()
-
MAKE_DATE_TIME()
-
SUBSTR()
X
CUME()
-
MAX()
X
SUM()
X
DATE_COMPARE()
-
MD5()
-
SYSDATE()
X
DATE_DIFF()
-
MEDIAN()
-
SYSTIMESTAMP()
X
DECODE()
X
METAPHONE()
-
TAN()
X
DECODE_BASE64()
-
MIN()
X
TANH()
-
DECOMPRESS()
-
MOD()
X
TO_BIGINT
X
ENCODE_BASE64()
-
MOVINGAVG()
-
TO_CHAR(DATE)
X
EXP()
X
MOVINGSUM()
-
TO_CHAR(NUMBER)
X
FIRST()
-
NPER()
-
TO_DATE()
X
FLOOR()
X
PERCENTILE()
-
TO_DECIMAL()
X
FV()
-
PMT()
-
TO_FLOAT()
X
GET_DATE_PART()
X
POWER()
X
TO_INTEGER()
X
GREATEST()
-
PV()
-
TRUNC(DATE)
X
IIF()
-
RAND()
-
TRUNC(NUMBER)
X
IN()
-
RATE()
-
UPPER()
X
INDEXOF()
-
REG_EXTRACT()
-
VARIANCE()
-
Note: When you push the SYSDATE(), SYSTIMESTAMP(), IS_DATE(), ROUND(DATE), GET_DATE_PART(), ADD_TO_DATE(), or TRUNC(DATE) function to Google BigQuery, you must add the Custom Properties property under Advanced Session Properties tab when you create a mapping task and specify EnableAdvancedPDOForBigQuery=Yes in the Session Property Value field.
The following list summarizes the availability of pushdown operators in a Google BigQuery database:

Supported transformations, variables, and data types for Google BigQuery ODBC mappings

When you use pushdown optimization, the Secure Agent converts the expression in the transformation by determining equivalent operators and functions in the database.
The following table lists the transformation logic that the Secure Agent can push to a Google BigQuery source or target:
Transformations Supported
Pushdown Type
Aggregator
Source, Full
Expression
Source, Full
Filter
Source, Full
Joiner
Source, Full
Lookup
Source, Full
Sorter
Source, Full
Union
Source, Full
Router
Full
When you use pushdown optimization, the Secure Agent converts the expression in the transformation by determining equivalent variables in the database. If there is no equivalent variable, the Secure Agent processes the transformation logic.
The following table lists the pushdown operators that can be used in an Google BigQuery database. Columns marked with an X indicate that the operator can be pushed to the Google BigQuery database by using full pushdown optimization.
Variable
Pushdown
SESSSTARTTIME
X
SYSDATE
-
WORKFLOWSTARTTIME
-
The following table lists the Google BigQuery data types that can be used for pushdown optimization:
Google BigQuery Data Type
Transformation Data Type
Boolean
Integer
Date
DateTime
DateTime
DateTime
Applicable only for full pushdown.
Float
Double
Integer
BigInt
Record
String
Applicable only for full pushdown.
Numeric
Decimal
Applicable only for full pushdown.
Note: Google BigQuery ODBC connection supports maximum precision of 28 and maximum scale of 9.
String
String
Byte
Byte
Time
DateTime
Timestamp
DateTime

Rules and guidelines for pushdown optimization

Consider the following rules and guidelines when you push functions to a Google BigQuery database: