Snowflake CSV加载报错：未知自定义函数T.TRY_TO_DECIMAL

加载CSV文件至临时表后，复制数据到最终表时出现小数类型被截断的情况。为解决该问题，编写了新的复制流程，尝试用TRY_TO_DECIMAL转换部分字段数据类型，却触发错误：Unknown user-defined function T.TRY_TO_DECIMAL

错误代码如下：

use warehouse P_TEMP_WH;
alter warehouse P_TEMP_WH resume;

-- 查询暂存数据文件
select t.$1,t.$2,t.$3,t.$4,t.$5,t.$6,t.$7,t.$8,t.$9,t.$10,t.$11,t.$12,t.$13,t.$14,t.$15,t.$16,t.$17,t.$18,t.$19,t.$20,t.$21,t.$22,t.$23,t.$24,t.$25,t.$26,t.$27,t.$28,t.$29,t.$30  from @%players t;

-- 将暂存CSV列数据转换为指定数据类型后加载至目标表
copy into PLAYERS(rk,
player,
pos,
age,
team,
games,
games_started,
minutes_played,
field_goals,
field_goals_attempted,
field_goal_percentage,
threes,
three_points_attempted,
three_point_percentage,
two_pointers,
two_pointers_attempted,
two_point_percentage,
effective_field_goal_percentage,
free_throws,
free_throws_attempted,
free_throw_percentage,
offensive_rebounds,
defensive_rebounds,
total_rebound_percentage,
assists,
steals,
blocks,
turnovers,
fouls,
points)
from (
select t.$1,t.$2,t.$3,t.$4,t.$5,t.$6 ,t.$7,t.$8,t.$9,TRY_TO_DECIMAL(t.$10, '99.9', 9, 5),TRY_TO_DECIMAL(t.$11, '99.9',9,5),TRY_TO_DECIMAL(t.$12, '99.9', 9, 5),TRY_TO_DECIMAL(t.$13, '99.9', 9, 5),TRY_TO_DECIMAL(t.$14, '99.9', 9, 5),TRY_TO_DECIMAL(t.$15, '99.9', 9, 5),TRY_TO_DECIMAL(t.$16, '99.9', 9, 5),TRY_TO_DECIMAL(t.$17, '99.9', 9, 5),TRY_TO_DECIMAL(t.$18, '99.9', 9, 5),TRY_TO_DECIMAL(t.$19, '99.9', 9, 5),TRY_TO_DECIMAL(t.$20, '99.9', 9, 5),TRY_TO_DECIMAL(t.$21, '99.9', 9, 5),t.TRY_TO_DECIMAL($22, '99.9', 9, 5),TRY_TO_DECIMAL(t.$23, '99.9', 9, 5),TRY_TO_DECIMAL(t.$24, '99.9', 9, 5),TRY_TO_DECIMAL(t.$25, '99.9', 9, 5),TRY_TO_DECIMAL(t.$26, '99.9', 9, 5),TRY_TO_DECIMAL(t.$27, '99.9', 9, 5),TRY_TO_DECIMAL(t.$28, '99.9', 9, 5),TRY_TO_DECIMAL(t.$29, '99.9', 9, 5),TRY_TO_DECIMAL(t.$30, '99.9', 9, 5)
from @%players t
)

错误原因 #

TRY_TO_DECIMAL是Snowflake系统内置函数，不属于表别名t的成员函数。代码中错误地写成了t.TRY_TO_DECIMAL($22, ...)，正确写法应为TRY_TO_DECIMAL(t.$22, ...)。

修正后的代码 #

use warehouse P_TEMP_WH;
alter warehouse P_TEMP_WH resume;

-- 查询暂存数据文件
select t.$1,t.$2,t.$3,t.$4,t.$5,t.$6,t.$7,t.$8,t.$9,t.$10,t.$11,t.$12,t.$13,t.$14,t.$15,t.$16,t.$17,t.$18,t.$19,t.$20,t.$21,t.$22,t.$23,t.$24,t.$25,t.$26,t.$27,t.$28,t.$29,t.$30  from @%players t;

-- 将暂存CSV列数据转换为指定数据类型后加载至目标表
copy into PLAYERS(rk,
player,
pos,
age,
team,
games,
games_started,
minutes_played,
field_goals,
field_goals_attempted,
field_goal_percentage,
threes,
three_points_attempted,
three_point_percentage,
two_pointers,
two_pointers_attempted,
two_point_percentage,
effective_field_goal_percentage,
free_throws,
free_throws_attempted,
free_throw_percentage,
offensive_rebounds,
defensive_rebounds,
total_rebound_percentage,
assists,
steals,
blocks,
turnovers,
fouls,
points)
from (
select t.$1,t.$2,t.$3,t.$4,t.$5,t.$6 ,t.$7,t.$8,t.$9,TRY_TO_DECIMAL(t.$10, '99.9', 9, 5),TRY_TO_DECIMAL(t.$11, '99.9',9,5),TRY_TO_DECIMAL(t.$12, '99.9', 9, 5),TRY_TO_DECIMAL(t.$13, '99.9', 9, 5),TRY_TO_DECIMAL(t.$14, '99.9', 9, 5),TRY_TO_DECIMAL(t.$15, '99.9', 9, 5),TRY_TO_DECIMAL(t.$16, '99.9', 9, 5),TRY_TO_DECIMAL(t.$17, '99.9', 9, 5),TRY_TO_DECIMAL(t.$18, '99.9', 9, 5),TRY_TO_DECIMAL(t.$19, '99.9', 9, 5),TRY_TO_DECIMAL(t.$20, '99.9', 9, 5),TRY_TO_DECIMAL(t.$21, '99.9', 9, 5),TRY_TO_DECIMAL(t.$22, '99.9', 9, 5),TRY_TO_DECIMAL(t.$23, '99.9', 9, 5),TRY_TO_DECIMAL(t.$24, '99.9', 9, 5),TRY_TO_DECIMAL(t.$25, '99.9', 9, 5),TRY_TO_DECIMAL(t.$26, '99.9', 9, 5),TRY_TO_DECIMAL(t.$27, '99.9', 9, 5),TRY_TO_DECIMAL(t.$28, '99.9', 9, 5),TRY_TO_DECIMAL(t.$29, '99.9', 9, 5),TRY_TO_DECIMAL(t.$30, '99.9', 9, 5)
from @%players t
)

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