Snowflake从外部阶段加载数据时乘法运算报错的解决方法

错误原因 #

Snowflake的COPY INTO语句对查询子句有语法限制，不允许直接在SELECT中使用算术运算符（如*）进行计算，这是其预编译逻辑的限制导致的。

解决方案一：通过临时表中转处理 #

这是最直接的解决方式，先将原始数据加载到临时表，再完成计算后写入目标表：

1. 创建临时表匹配源数据结构

CREATE OR REPLACE TEMPORARY TABLE temp_account (
    raw_accountid VARCHAR,
    accounttype VARCHAR
);

2. 加载原始数据到临时表

COPY INTO temp_account(raw_accountid, accounttype)
FROM (SELECT t.$1, t.$4 FROM @azure_ext_stg/snowflake/account.csv t)
FILE_FORMAT = my_csv_format
FORCE = TRUE;

3. 计算后插入目标表

INSERT INTO account(accountid, accounttype)
SELECT TO_NUMBER(raw_accountid, 10, 1)*10, accounttype
FROM temp_account;

解决方案二：使用自定义函数封装运算 #

通过创建自定义UDF封装乘法逻辑，绕过COPY INTO的运算符限制：

1. 创建自定义函数

CREATE OR REPLACE FUNCTION multiply_by_ten(input_num NUMBER)
RETURNS NUMBER
AS $$
    input_num * 10
$$;

2. 在COPY INTO中调用函数完成计算

COPY INTO account(accountid, accounttype)
FROM (SELECT multiply_by_ten(TO_NUMBER(t.$1, 10, 1)), t.$4 FROM @azure_ext_stg/snowflake/account.csv t)
FILE_FORMAT = my_csv_format
FORCE = TRUE;

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