DSXchange: DataStage and IBM Websphere Data Integration Forum
View next topic
View previous topic
Add To Favorites
Author Message
hsahay



Group memberships:
Premium Members

Joined: 21 Mar 2007
Posts: 175

Points: 2378

Post Posted: Thu Jul 23, 2020 3:20 pm Reply with quote    Back to top    

DataStage® Release: 11x
Job Type: Parallel
OS: Unix
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
Display posts from previous:       

Add To Favorites
View next topic
View previous topic
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum



Powered by phpBB © 2001, 2002 phpBB Group
Theme & Graphics by Daz :: Portal by Smartor
All times are GMT - 6 Hours