snowflake-cloud-data-platform - 雪花 table 阶段

Question

有人可以帮助理解根据雪花文档的以下含义 -“表阶段不支持在加载数据时转换数据(即使用查询作为 COPY 命令的源)。” - 文档链接:- https://docs.snowflake.com/en/user-guide/data-load-local-file-system-create-stage.html .COPY 命令支持以下转换，它们似乎也适用于表级阶段，因此不确定文档中的含义是什么？

1. 列重新排序
2. 遗漏
3. 使用 SELECT 语句进行转换

我尝试了所有三个并且它们工作正常，所以无法理解该声明根据文档的含义:案例一(CAST)

snowflake1#COMPUTE_WH@TEST_DB.PUBLIC>copy into test_tab from (select s.$1,s.$2,s.$3,s.$4,s.$5,reverse(s.$6::String) from @%test_tab s) file_format=(skip_header=1);
+------------------+--------+-------------+-------------+-------------+-------------+-------------+------------------+-----------------------+-------------------------+
| file             | status | rows_parsed | rows_loaded | error_limit | errors_seen | first_error | first_error_line | first_error_character | first_error_column_name |
|------------------+--------+-------------+-------------+-------------+-------------+-------------+------------------+-----------------------+-------------------------|
| MOCK_DATA.csv.gz | LOADED |        1000 |        1000 |           1 |           0 | NULL        |             NULL |                  NULL | NULL                    |
+------------------+--------+-------------+-------------+-------------+-------------+-------------+------------------+-----------------------+-------------------------+
1 Row(s) produced. Time Elapsed: 0.856s

案例 2 [列重新排序]此外，尝试对列重新排序 [重新排序列号 2 和 3]，效果也很好 -

snowflake1#COMPUTE_WH@TEST_DB.PUBLIC>copy into test_tab from (select cast(s.$1 as String),s.$3,s.$2,s.$4,s.$5,reverse(s.$6::String) from @%test_tab s) file_format=(skip_header=1);
    +------------------+--------+-------------+-------------+-------------+-------------+-------------+------------------+-----------------------+-------------------------+
    | file             | status | rows_parsed | rows_loaded | error_limit | errors_seen | first_error | first_error_line | first_error_character | first_error_column_name |
    |------------------+--------+-------------+-------------+-------------+-------------+-------------+------------------+-----------------------+-------------------------|
    | MOCK_DATA.csv.gz | LOADED |        1000 |        1000 |           1 |           0 | NULL        |             NULL |                  NULL | NULL                    |
    +------------------+--------+-------------+-------------+-------------+-------------+-------------+------------------+-----------------------+-------------------------+
    1 Row(s) produced. Time Elapsed: 0.947s

Case-3【列遗漏，遗漏列IP_address】-

snowflake1#COMPUTE_WH@TEST_DB.PUBLIC>copy into test_tab(id,first_name,last_name,email,gender) from (select cast(s.$1 as String),s.$3,s.$2,s.$4,s.$5 from @%test_tab s) file_format=(skip_header=1);
    +------------------+--------+-------------+-------------+-------------+-------------+-------------+------------------+-----------------------+-------------------------+
    | file             | status | rows_parsed | rows_loaded | error_limit | errors_seen | first_error | first_error_line | first_error_character | first_error_column_name |
    |------------------+--------+-------------+-------------+-------------+-------------+-------------+------------------+-----------------------+-------------------------|
    | MOCK_DATA.csv.gz | LOADED |        1000 |        1000 |           1 |           0 | NULL        |             NULL |                  NULL | NULL                    |
    +------------------+--------+-------------+-------------+-------------+-------------+-------------+------------------+-----------------------+-------------------------+
    1 Row(s) produced. Time Elapsed: 0.831s
    
    snowflake1#COMPUTE_WH@TEST_DB.PUBLIC>select * from test_tab limit 1;
    +----+------------+-----------+-------------------+--------+------------+
    | ID | FIRST_NAME | LAST_NAME | EMAIL             | GENDER | IP_ADDRESS |
    |----+------------+-----------+-------------------+--------+------------|
    |  1 | Menicomb   | Hedwiga   | hmenicomb0@un.org | Male   | NULL       |
    +----+------------+-----------+-------------------+--------+------------+
    1 Row(s) produced. Time Elapsed: 0.293s

复制到表阶段 - 卸载:案例 1) 正常复制到表级阶段 - 工作正常。

snowflake1#COMPUTE_WH@TEST_DB.PUBLIC>create table test_tab_copy as 
select * from TEST_TAB where 1=2;
+-------------------------------------------+
| status                                    |
|-------------------------------------------|
| Table TEST_TAB_COPY successfully created. |
+-------------------------------------------+
1 Row(s) produced. Time Elapsed: 1.336s
snowflake1#COMPUTE_WH@TEST_DB.PUBLIC>select * from TEST_TAB_COPY;
+----+------------+-----------+-------+--------+------------+
| ID | FIRST_NAME | LAST_NAME | EMAIL | GENDER | IP_ADDRESS |
|----+------------+-----------+-------+--------+------------|
+----+------------+-----------+-------+--------+------------+
0 Row(s) produced. Time Elapsed: 0.185s
snowflake1#COMPUTE_WH@TEST_DB.PUBLIC>copy into @%test_tab_copy from (select * from test_tab);
+---------------+-------------+--------------+
| rows_unloaded | input_bytes | output_bytes |
|---------------+-------------+--------------|
|          1000 |       52413 |        21265 |
+---------------+-------------+--------------+
1 Row(s) produced. Time Elapsed: 1.085s
snowflake1#COMPUTE_WH@TEST_DB.PUBLIC>list @%test_tab_copy;
+-------------------+-------+----------------------------------+-------------------------------+
| name              |  size | md5                              | last_modified                 |
|-------------------+-------+----------------------------------+-------------------------------|
| data_0_0_0.csv.gz | 21280 | 71a630eef7eaba5d5f84e1afe39db66a | Tue, 15 Mar 2022 17:23:10 GMT |
+-------------------+-------+----------------------------------+-------------------------------+
1 Row(s) produced. Time Elapsed: 0.284s

案例 2)复制到表级阶段并重新排序列(first_name，last_name)- 工作正常。

snowflake1#COMPUTE_WH@TEST_DB.PUBLIC>create table test_tab_copy as select * from TEST_TAB where 1=2;
+-------------------------------------------+
| status                                    |
|-------------------------------------------|
| Table TEST_TAB_COPY successfully created. |
+-------------------------------------------+
1 Row(s) produced. Time Elapsed: 0.622s
snowflake1#COMPUTE_WH@TEST_DB.PUBLIC>copy into @%test_tab_copy from (select id,last_name,first_name,email,gender,ip_add
                                     ress from test_tab);
+---------------+-------------+--------------+
| rows_unloaded | input_bytes | output_bytes |
|---------------+-------------+--------------|
|          1000 |       52413 |        21221 |
+---------------+-------------+--------------+
1 Row(s) produced. Time Elapsed: 0.947s

Case-3) 使用 cast (string) 复制到表级阶段 - 工作正常。

snowflake1#COMPUTE_WH@TEST_DB.PUBLIC>create table test_tab_copy as select * from TEST_TAB where 1=2;
+-------------------------------------------+
| status                                    |
|-------------------------------------------|
| Table TEST_TAB_COPY successfully created. |
+-------------------------------------------+
1 Row(s) produced. Time Elapsed: 0.638s
snowflake1#COMPUTE_WH@TEST_DB.PUBLIC>copy into @%test_tab_copy from (select id,last_name,first_name,email,gender,ip_add
                                     ress::String from test_tab);
+---------------+-------------+--------------+
| rows_unloaded | input_bytes | output_bytes |
|---------------+-------------+--------------|
|          1000 |       52413 |        21221 |
+---------------+-------------+--------------+
1 Row(s) produced. Time Elapsed: 0.384s

案例 4)复制到表级阶段并省略列(省略列 IP_ADDRESS)- 工作正常。

snowflake1#COMPUTE_WH@TEST_DB.PUBLIC>create table test_tab_copy as select * from TEST_TAB where 1=2;
+-------------------------------------------+
| status                                    |
|-------------------------------------------|
| Table TEST_TAB_COPY successfully created. |
+-------------------------------------------+
1 Row(s) produced. Time Elapsed: 0.610s
snowflake1#COMPUTE_WH@TEST_DB.PUBLIC>select * from test_tab_copy;
+----+------------+-----------+-------+--------+------------+
| ID | FIRST_NAME | LAST_NAME | EMAIL | GENDER | IP_ADDRESS |
|----+------------+-----------+-------+--------+------------|
+----+------------+-----------+-------+--------+------------+
0 Row(s) produced. Time Elapsed: 0.179s
snowflake1#COMPUTE_WH@TEST_DB.PUBLIC>copy into @%test_tab_copy from (select id,last_name,first_name,email,gender from t
                                     est_tab);
+---------------+-------------+--------------+
| rows_unloaded | input_bytes | output_bytes |
|---------------+-------------+--------------|
|          1000 |       48413 |        20997 |
+---------------+-------------+--------------+
1 Row(s) produced. Time Elapsed: 0.527s
snowflake1#COMPUTE_WH@TEST_DB.PUBLIC>

Answer 1

强制转换、列重新排序和省略都是 COPY 命令中支持的转换。

一些不受支持的转换是:

• 过滤(WHERE 子句、LIMIT、FETCH、TOP)
• FLATTEN 函数

有关更多详细信息，请参阅文档: https://docs.snowflake.com/en/user-guide/data-load-transform.html