Overview

Skill Level: Any Skill Level

This recipe helps you create, configure, compile, and execute a DataStage job that can read data from Google BigQuery and write it to DB2

Ingredients

1. IBM Infosphere Information Server Datastage 11.7.1 and above

2. DB2 database

3. Google BigQuery Account

Step-by-step

  1. Description

    BigQuery is Google’s fully managed, petabyte scale, low cost analytics data warehouse that enables users with super-fast SQL queries using the processing power of Google’s infrastructure.

    Information Server provides a native BigQuery Connector to read/write data from/to the tables on BigQuery and integrate it into the ETL job design.

    We demonstrate a sample use case here which performs a read operation on BigQuery table using BigQuery Connector. The datastage job includes a BigQuery Connector as source stage and DB2 Connector as target, where the data from BigQuery is written to a table on DB2, moving data from cloud on to OnPremise in a hybrid cloud scenario.

    Screen-Shot-2019-08-23-at-4.47.31-pm

    In this recipe, I will show you how we can configure BigQuery Connector properties to read data from Google BigQuery and move it to DB2 table.

  2. Configure BigQuery Connector Properties to read from Google BigQuery

    1. Download the Google service account credentials json file and copy it to any location on Engine tier.

    2. Provide the fully qualified path to the above json file under Credentials file in the Connection Properties as follows:

    Screen-Shot-2019-08-23-at-4.51.57-pm

    3. Provide the Schema name property on which the table resides.

    4. Provide the Table name property from which data has to be read.

    Screen-Shot-2019-08-23-at-4.56.58-pm

    5. Optionally, you can choose to provide a value for row limit property, where only those number of rows will be read by each node. For example, if the row limit has been set to 10 and a two node configuration is used to run a job, then only 20 rows will be read from BigQuery table.

    6. Under Output tab, provide the column name and type details of data, that needs to be read from Google BigQuery

    Screen-Shot-2019-08-23-at-4.48.01-pm

     

     

  3. Configure DB2 connector as target

    1. Provide Database, Username and Password details for DB2, in the connection properties of DB2 Connector.

    Screen-Shot-2019-08-23-at-5.02.41-pm-1

     

    2. Select Write Mode as Insert and Generate SQL option to Yes, to auto-generate the insert statement.

    3. Choose Table Action as Create and provide the DB2 Table name, where the data has to written.

    Screen-Shot-2019-08-23-at-5.06.27-pm

     

  4. Job Execution

    Compile and run the job. The data from BigQuery table is written to the DB2 table.

    When Datastage is configured to run on multiple nodes, each node reads a chunk of data in parallel from the BigQuery table.

    Screen-Shot-2019-08-23-at-5.11.07-pm

  5. References

    https://www.ibm.com/support/knowledgecenter/en/SSZJPZ_11.7.0/com.ibm.swg.im.iis.conn.bigquery.usage.doc/topics/bq_connector_top_of_nav.html

    https://cloud.google.com/iam/docs/creating-managing-service-account-keys

    https://cloud.google.com/bigquery/

Join The Discussion