将 JSON 数据从 Snowflake 复制到 S3

Question

我正在尝试将数据（变体数据类型 - JSON）从 Snowflake 复制到 S3。我正在使用以下命令：

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;

正在复制数据。我正在使用 STORAGE_INTEGRATION 过程 &创建 STAGE 表。 我的问题是：

1. 对于上面的查询，我得到 23 行，我想将这 23 行作为 23 个单独的文件（JSON 文件）加载到 S3 中。如何实现这一目标？
2. 每条记录都有不同的 LOAD_DT_TS，因此对于每 23 个文件，我需要给出不同的名称。我的意思是，文件名应该以 LOAD_DT_TS 结尾。如何实现这一目标？
3. MYTABLE 在上面的查询（COPY 命令）中，我有 4 列。有没有可能将所有 4 列加载到单个 JSON 文件中？

请分享您的想法。

Answer 1

请参阅下文（涵盖第 1 点和第 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>

Answer 2

这适用于输入带引号放置的谓词值 -

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

Answer 3

SINGLE=true - 此选项将完整的 SQL 输出发送到单个文件，如果删除此选项，则会将输出发送到单独的文件。