如何在Snowflake COPY INTO语句中添加空列与固定值列

问题描述 #

需将仅包含UUID、PHONE_NUMBER两列的myfile.csv文件上传至Snowflake，同时为所有行添加第三列空值列CUSTOMER_ID，以及第四列固定值为'N'的REGISTERED列。编写的执行脚本报错：syntax error line 3 at position 1 unexpected 'NULL'，询问是否需要修改null_if参数。

原脚本如下：

create or replace table my_database.my_table (

    CUSTOMER_ID NUMBER(38,0)
    ,UUID VARCHAR(50)
    ,PHONE_NUMBER VARCHAR(10)
    ,REGISTERED CHAR(1)
    
);

copy into dtable my_database.my_table (

     NULL as CUSTOMER_ID
    ,UUID
    ,PHONE_NUMBER
    ,’N’ REGISTERED
    
)           

from @fl_template_misc                                                                                                        
file_format = (                                                                                                        
  type = CSV                                                                                                         
  encoding = 'UTF8'                                                                                                    
  field_delimiter = '|'                                                                                                
  null_if = ('NULL','null','')                                                                                   
  field_optionally_enclosed_by = '"'                                                                                   
  trim_space = true                                                                                                    
  skip_header = 1                                                                                                    
  date_format = 'MM/DD/YYYY'                                                                                           
  time_format = 'HH24:MI:SS')                                                                                          
  on_error = 'ABORT_STATEMENT'                                                                                         
;      

错误原因 #

脚本存在3处语法错误：

1. copy into语句中多了无效关键字dtable，直接指定表名即可
2. 列映射中固定值写法错误：缺少as关键字，且使用了中文单引号’N’，需改为英文单引号'N' as REGISTERED
3. 空值列构造位置错误：不能直接在copy into的列列表里写NULL as CUSTOMER_ID，需在FROM子句的数据源中构造额外列

修正后的脚本 #

create or replace table my_database.my_table (
    CUSTOMER_ID NUMBER(38,0)
    ,UUID VARCHAR(50)
    ,PHONE_NUMBER VARCHAR(10)
    ,REGISTERED CHAR(1)
);

copy into my_database.my_table (CUSTOMER_ID, UUID, PHONE_NUMBER, REGISTERED)
from (
    select 
        NULL as CUSTOMER_ID,
        $1 as UUID,
        $2 as PHONE_NUMBER,
        'N' as REGISTERED
    from @fl_template_misc
)                                                                                                       
file_format = (                                                                                                        
  type = CSV                                                                                                         
  encoding = 'UTF8'                                                                                                    
  field_delimiter = '|'                                                                                                
  null_if = ('NULL','null','')                                                                                   
  field_optionally_enclosed_by = '"'                                                                                   
  trim_space = true                                                                                                    
  skip_header = 1                                                                                                    
  date_format = 'MM/DD/YYYY'                                                                                           
  time_format = 'HH24:MI:SS')                                                                                          
on_error = 'ABORT_STATEMENT'                                                                                         
;      

关于null_if参数的说明 #

不需要修改null_if参数。该参数用于定义CSV文件中被视为空值的字符串标识，和本次手动添加空值列CUSTOMER_ID的需求无关，当前配置符合常规空值处理逻辑。

内容的提问来源于stack exchange，提问作者Speedy