[Snowflake] tDBOutputBulk - how to read files moved to internal stage

JackStrong — Thu, 29 Feb 2024 15:50:51 GMT

Hi.

I am working on Talend job to load data to Snowflake but without using tDBOutput component. I would like to use bulk load (copy into).

I am able to send the json file to internal named stage using tDBOutputBulk. But problem is that I would like to read this file using tDBRow (copy into statement). I don't want to use tDBBulkExec because as far as I understand there is no option to use transformation (select statement) loading the data by tDBBulkExec. Unfortunately I need to add some columns (transformation) before loading to Snowflake, this is why I am trying to utilize tDBRow + copy into TABLE from (select ... from @Stage).

The issue is that json file before sending it to the stage (in my local laptop) looks good

e.g

[{"data":{"name":"AAA","surname":"BBBB"}},{...]

but after moving json file to stage the file name is different, the file is compressed(?) (the file name looks like internal-8576818063155977688.gz) but the worst thing is that content of the file is different, meaning:

"[{""data"":{""name"":""AAA"",""surname"":""BBBB""}},{ ...]"

(double quotes added at the beginning and at the end of the json array and additionally each occurrence of double quote is duplicated).

I am guessing that it causes that I am not able to load dat to Snowflake correctly.

This is my copy into command defined in tDBRow:

"copy into MY_DATABASE.MY_SCHEMA.TABLE from (select " + ((Long)globalMap.get("load_id")) + ", '" + context.environment + "', $1 from @tmp_stage) file_format = (type = 'JSON') on_error = 'skip_file'".

After loading data into Snowflake table the data looks strange:

1,DEV,"[{"

1,DEV,"data"

1,DEV,":{"

1,DEV,"name"

1,DEV,":"

1,DEV,"AAA"

1,DEV,","

1,DEV,"surname"

1,DEV,":"

1,DEV,"BBBB"

1,DEV,"}},{"

...

1,DEV,"}}]"

Can anyone share what I am doing not correclty?

I would like to load all json documents from my json array, each document should be loaded to the new row in snowflake table. Unfortunately, currently I have many rows but there are only some parts of the document in the last column.

Re: [Snowflake] tDBOutputBulk - how to read files moved to internal stage

Shicong_Hong — Fri, 08 Mar 2024 03:01:04 GMT

I think there should be other attributes that need to be set for the file_format[type=JSON], I suggest you to ask for help on Snowflake community about the Copy into command.

Regards

Shicong

topic Re: [Snowflake] tDBOutputBulk - how to read files moved to internal stage in Talend Studio

[Snowflake] tDBOutputBulk - how to read files moved to internal stage

Re: [Snowflake] tDBOutputBulk - how to read files moved to internal stage