Snowflake递归CTE调用CONCAT出现字符串过长截断报错如何解决？

报错原因 #

Snowflake对递归CTE的字段类型有严格的自动推断规则：会以UNION ALL前的锚点查询返回的字段类型、长度作为整个递归CTE对应字段的约束。你的锚点查询中val字段直接返回trim(child_col)，Snowflake会自动将val的长度限定为child_col字段的原有长度。
前2层级拼接后的字符串长度还未超出该初始限定长度，所以运行正常；到第3层级拼接后字符串长度超过了初始推断的最大长度，就会触发截断报错。

修复方案 #

核心解决思路是显式指定锚点中val字段的长度，避免Snowflake默认的短长度推断，有两种可选实现方式：

• 方案1：预估层级路径的最大长度，显式转换为对应长度的VARCHAR类型，比如预估最长路径不超过2000字符，可将锚点val字段转换为VARCHAR(2000)
• 方案2：不确定路径长度时直接转换为VARCHAR(MAX)，Snowflake中该类型最大支持16MB的字符串存储，完全满足绝大多数层级路径拼接场景

修改后完整可运行代码 #

with recursive plant 
(child_col,parent_col,val )
as 
(
-- 锚点查询显式指定val的长度为VARCHAR(MAX)
select  child_col, '' parent_col , trim(child_col)::VARCHAR(MAX)  from My_view 
where condition1 = 'AAA' 
union all
select  A.child_col,A.parent_col,                 
concat(trim(A.child_col),'>')||trim(val)
from My_view A 
JOIN plant as B ON trim(B.child_col) = trim(A.parent_col) 
  )
select distinct * from plant 

附加优化建议 #

可以预先在My_view中对child_col、parent_col做trim处理，避免递归过程中重复调用trim函数，提升查询执行效率。

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