'Copy JSON data from Snowflake into S3

I am trying to COPY data (Variant data type - JSON) from Snowflake to S3. I am using below command:

copy into @STG_SF_S3_DEV_JJC/sample_file1.json
from (select distinct RECORD_CONTENT from MYTABLE where LOAD_DT_TS >= '2022-02-09 00:00:00')
FILE_FORMAT = (TYPE=JSON,COMPRESSION = NONE)
SINGLE=true
header=true
OVERWRITE = TRUE;

The data is getting copied. I am using STORAGE_INTEGRATION process & creating STAGE table. My question is:

  1. For above query, I am getting 23 rows and I want to load these 23 rows as 23 separate files (JSON files) in S3. How to achieve this?
  2. Each record has different LOAD_DT_TS and so for each 23 files, I need to give different names. I mean, the file name should end with LOAD_DT_TS. How to achieve this?
  3. MYTABLE in above query (COPY command), I have 4 columns. Is there any possible way to load all the 4 columns into a single JSON file?

Please share your thoughts.

snowflake-cloud-data-platformdata-warehousefile-copyingphpajaxwordpressformsplugins


Solution 1:[1]

Please refer below (it covers point #1 and 2).

SNOWFLAKE1#COMPUTE_WH@TEST_DB.PUBLIC>select * from test_pivot;
+---------+-----------+-----------+-----------+
| COMPANY | BU        | EVAL_DESC | MEMBER_ID |
|---------+-----------+-----------+-----------|
| C1      | FINANCIAL | L1        | ID1       |
| C1      | FINANCIAL | L2        | ID2       |
| C1      | FINANCIAL | L3        | ID3       |
| C1      | HR        | L1        | ID4       |
| C1      | HR        | L2        | ID5       |
| C2      | FINANCIAL | L1        | ID6       |
| C2      | BUSINESS  | L1        | ID7       |
+---------+-----------+-----------+-----------+
7 Row(s) produced. Time Elapsed: 0.187s
SNOWFLAKE1#COMPUTE_WH@TEST_DB.PUBLIC>list @test_row_stage;
+------+------+-----+---------------+
| name | size | md5 | last_modified |
|------+------+-----+---------------|
+------+------+-----+---------------+
0 Row(s) produced. Time Elapsed: 0.177s
SNOWFLAKE1#COMPUTE_WH@TEST_DB.PUBLIC>EXECUTE IMMEDIATE $$
                                     DECLARE
                                       company varchar2(30);
                                       BU varchar2(30);
                                       eval_desc varchar2(30);
                                       member_id varchar2(30);
                                       file_name varchar2(30);
                                       c1 CURSOR FOR SELECT * FROM test_pivot;
                                     BEGIN
                                      // OPEN c1;
                                       for record in c1 do
                                       company:=record.company;
                                       BU:=record.BU;
                                       eval_desc:=record.eval_desc;
                                       member_id:=record.member_id;
                                       file_name:='load'||'_'||member_id||'.csv';
                                       create or replace temporary table temp_test_pvt(company varchar2(30),BU varchar2
                                     (30),eval_desc varchar2(30),member_id varchar2(30));
                                       insert into temp_test_pvt values (:company,:bu,:eval_desc,:member_id);
                                       execute immediate 'copy into @test_row_stage/'||:file_name||' from (select * fro
                                     m temp_test_pvt) overwrite=false';
                                       end for;
                                       RETURN 0;
                                     END;
                                     $$
                                     ;
+-----------------+
| anonymous block |
|-----------------|
|               0 |
+-----------------+
1 Row(s) produced. Time Elapsed: 9.803s
SNOWFLAKE1#COMPUTE_WH@TEST_DB.PUBLIC>list @test_row_stage;
+------------------------------------------+------+----------------------------------+-------------------------------+
| name                                     | size | md5                              | last_modified                 |
|------------------------------------------+------+----------------------------------+-------------------------------|
| test_row_stage/load_ID1.csv_0_0_0.csv.gz |   48 | df314a0d95a771d5d81fa9b1cfb3a28e | Thu, 17 Mar 2022 16:47:42 GMT |
| test_row_stage/load_ID2.csv_0_0_0.csv.gz |   48 | 6be39868046f583b1c63d616faa9e7f6 | Thu, 17 Mar 2022 16:47:43 GMT |
| test_row_stage/load_ID3.csv_0_0_0.csv.gz |   48 | ecf9dbcb4e45fa29d6bcfe268c6ccae3 | Thu, 17 Mar 2022 16:47:44 GMT |
| test_row_stage/load_ID4.csv_0_0_0.csv.gz |   48 | 0fd3ff7e0a453e04be3aca22147a7d32 | Thu, 17 Mar 2022 16:47:45 GMT |
| test_row_stage/load_ID5.csv_0_0_0.csv.gz |   48 | c77985f8312a540816d82b4bf4ec5249 | Thu, 17 Mar 2022 16:47:46 GMT |
| test_row_stage/load_ID6.csv_0_0_0.csv.gz |   48 | c9e9d6fd613a8bdb76413dd3e9464cc4 | Thu, 17 Mar 2022 16:47:47 GMT |
| test_row_stage/load_ID7.csv_0_0_0.csv.gz |   48 | 4e4b999ed56059b44ee6bd15f28cafb8 | Thu, 17 Mar 2022 16:47:48 GMT |
+------------------------------------------+------+----------------------------------+-------------------------------+
7 Row(s) produced. Time Elapsed: 0.176s
SNOWFLAKE1#COMPUTE_WH@TEST_DB.PUBLIC>select $1,$2,$3,$4 from @test_row_stage/load_ID1.csv_0_0_0.csv.gz;
+----+-----------+----+-----+
| $1 | $2        | $3 | $4  |
|----+-----------+----+-----|
| C1 | FINANCIAL | L1 | ID1 |
+----+-----------+----+-----+
1 Row(s) produced. Time Elapsed: 0.429s
SNOWFLAKE1#COMPUTE_WH@TEST_DB.PUBLIC>select $1,$2,$3,$4 from @test_row_stage/load_ID2.csv_0_0_0.csv.gz;
+----+-----------+----+-----+
| $1 | $2        | $3 | $4  |
|----+-----------+----+-----|
| C1 | FINANCIAL | L2 | ID2 |
+----+-----------+----+-----+
1 Row(s) produced. Time Elapsed: 0.374s
SNOWFLAKE1#COMPUTE_WH@TEST_DB.PUBLIC>select $1,$2,$3,$4 from @test_row_stage/load_ID3.csv_0_0_0.csv.gz;
+----+-----------+----+-----+
| $1 | $2        | $3 | $4  |
|----+-----------+----+-----|
| C1 | FINANCIAL | L3 | ID3 |
+----+-----------+----+-----+
1 Row(s) produced. Time Elapsed: 0.506s
SNOWFLAKE1#COMPUTE_WH@TEST_DB.PUBLIC>select $1,$2,$3,$4 from @test_row_stage/load_ID4.csv_0_0_0.csv.gz;
+----+----+----+-----+
| $1 | $2 | $3 | $4  |
|----+----+----+-----|
| C1 | HR | L1 | ID4 |
+----+----+----+-----+
1 Row(s) produced. Time Elapsed: 0.281s
SNOWFLAKE1#COMPUTE_WH@TEST_DB.PUBLIC>

Solution 2:[2]

This works for input a predicate value with quotes placement -

SNOWFLAKE1#COMPUTE_WH@TEST_DB.PUBLIC>EXECUTE IMMEDIATE $$
                                     DECLARE
                                      load_dt_tss timestamp;
                                      file_name varchar2(30);
                                     BEGIN
                                      file_name:='somefile'||'.csv';
                                      execute immediate 'copy into @test_row_stage/'||:file_name||' from (select LOAD_D
                                     T_TS from TEST_TS where LOAD_DT_TS >=' || '''2022-02-09 00:00:00''' || ')' || ' FI
                                     LE_FORMAT = (TYPE=CSV,COMPRESSION = NONE) overwrite=FALSE';
                                      RETURN 0;
                                     END;
                                     $$
                                     ;
+-----------------+
| anonymous block |
|-----------------|
|               0 |
+-----------------+
1 Row(s) produced. Time Elapsed: 0.584s

enter image description here

Solution 3:[3]

SINGLE=true - This option send the full SQL output to single file, if you remove this it will send the output to separate files.

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Pankaj
Solution 2
Solution 3 Deependra

Related Questions