[Snowflake] tDBOutputBulk - how to read files moved to internal stage
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:
(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.
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.
