Overview

Skill Level: Any Skill Level

This recipe helps you create, compile, and execute a DataStage job that can write data into Snowflake datawarehouse tables.

Ingredients

1. IBM Infosphere Information Server Datastage 11.7fp1 and above

2. Snowflake datawarehouse  account

3. Should be able to configure DB2 connector in source context.

Step-by-step

  1. Description

    IBM Information Server provides a native Snowflake Connector to write, read and load data into Snowflake datawarehouse and integrate the same into the ETL job design.

    This recipe demonstrates a simple use case of writing(inserting) enterprise data (DB2 database as source) into Snowflake tables using Snowflake Connector.

  2. Designing the datastage job

    To create a job that writes data into Snowflake datawarehouse the Snowflake connector should be on the target side , as we are reading data from DB2 using DB2 connector , DB2 Connector should be on the source side. The job design should look as below-

    sfcc_1

  3. Configure Snowflake connector properties and running the job

    1. Put in the values for Account name, Region, Username and Password that correspond to your Snowflake datawarehouse account details.

    2. Provide values for Database and Schema where the table, into which data is to be written is present.

    1

    3. Alternatively by selecting the property ‘Use connection URL‘ to ‘Yes‘ , the ‘URL‘ property would be enabled and other connection properties would be disabled and in the URL property the snowflake jdbc URL can be specified. The Snowflake jdbc URL should be like –

    jdbc:snowflake://ibmdatastage.us-east-1.snowflakecomputing.com/?db=CONDEV&warehouse=CONDEV&schema=CONDEV

    4. Select ‘Write mode’ = Insert and specify the table name in ‘Table name’ property.

    5. Select ‘Table action’ = Append to append data into an existing table.

    3-1

    6. Configure the columns page with the table schema.

    columns

    7. Compile and run the job.

    jobrun

     

  4. References

    Writing data using Snowflake Connector –

    https://www.ibm.com/support/knowledgecenter/SSZJPZ_11.7.0/com.ibm.swg.im.iis.conn.snow.usage.doc/topics/writing_data_parent_snow.html

    Configuring Snowflake Connector as target –

    https://www.ibm.com/support/knowledgecenter/SSZJPZ_11.7.0/com.ibm.swg.im.iis.conn.snow.usage.doc/topics/specifying_write_mode_snow.html

Join The Discussion