Loading data from Oracle to Snowflake

A forum for discussing DataStage<sup>®</sup> basics. If you're not sure where your question goes, start here.

Moderators: chulett, rschirm, roy

Post Reply
hsahay
Premium Member
Premium Member
Posts: 175
Joined: Wed Mar 21, 2007 9:35 am

Loading data from Oracle to Snowflake

Post by hsahay »

I am able to load data from oracle to snowflake using the "Snowflake internal location" option in the snowflake connector but not when using the External location(S3) option.


job design is simple

oracle stage >>>>>transformer>>>>>snowflake connector stage



When using the internal location option of the snowflake connector, datastage does the following -

1. Data is extracted from the source table by the odbc connector and sent to the snowflake connector stage.

2. The snowflake connector writes the data to a local folder pointed to by the "Directory Path" parameter in the connector. I confirmed this by looking at the folder on the server that datastage is running.

3. It then creates a stage object in Snowflake by the name specified by the parameter "Staging area name"

4. Then it moves the files written to Directory Path to the stage object. I confirmed this by running the ls @stagename command in snowflake.

5. Then it runs the copy command using the files staged in the internal stage.
This works !!


Now when i changed the location to External location S3, I provide the S3 bucket credentials and name and the connector creates an external stage in snowflake pointing to my S3 bucket using the following command (I got this from director log) -


CREATE OR REPLACE STAGE my_ds_load_external URL = 's3://dataeng-snowflake-prd-ascap/APM_WRK_PERF' CREDENTIALS = (AWS_KEY_ID = 'xxxxxx' AWS_SECRET_KEY='xxxxxxxxx') ENCRYPTION = ( TYPE = 'NONE' ) FILE_FORMAT=(TYPE=CSV COMPRESSION = NONE SKIP_BYTE_ORDER_MARK = TRUE ENCODING = "UTF-8" RECORD_DELIMITER = "<NL>" FIELD_DELIMITER = "^|^" ESCAPE_UNENCLOSED_FIELD=NONE ESCAPE=NONE )


But I don't see any files being written to this external stage - running the command ls @my_ds_load_external doesn't show any files.


I think that it's not able to write any file to this external stage on S3 because i am using encryption = none (or SSE-S3) while the S3 bucket is configured to use SSE-KMS. Currently the connector stage does not provide an option for using SSE-KMS.


Is my assumption correct ? If now what do i need to do to make this work ?

Also, i opened a case with IBM and the agent there was trying to convince me that while Datastage would extract the files from Oracle and write them to the internal stage before it runs the copy command, it won't write to external stage and can only read from it.

So their suggestion is to extract from oracle and write to S3 using S3 stage and then use the snowflake connector stage with the "external location(s3)" option to just READ from the S3 location that i had already written to.

Is this correct ? Does the snowflake connector behave differently when using internal location versus external location ?
vishal
Post Reply