使用Snowflake MERGE查询偶发‘Left argument of string is not an array’错误求助

错误原因 #

这个错误是因为ARRAY_TO_STRING函数要求第一个参数必须是数组类型，但你的product表（target）或STAGE_PRODUCT_VIEW视图（source）中的items字段，存在非数组类型的值（比如字符串、数字，或者字段定义为非数组类型却存储了非数组数据）。当MERGE查询处理到这些异常数据时，函数执行失败抛出错误。

部分场景能正常运行，是因为那些场景下items字段的值全是合法数组，没有触发类型校验错误。

排查步骤 #

1. 检查字段类型：确认product表和STAGE_PRODUCT_VIEW中items字段的定义是否为数组类型（比如ARRAY或存储数组的VARIANT）。
2. 定位异常数据：执行以下查询找出非数组的items值：

   -- 检查目标表
   SELECT product_id, items, TYPEOF(items)
   FROM product
   WHERE TYPEOF(items) != 'ARRAY';
   
   -- 检查源视图
   SELECT product_id, items, TYPEOF(items)
   FROM STAGE_PRODUCT_VIEW
   WHERE TYPEOF(items) != 'ARRAY';
   

修复方案 #

根据排查结果选择对应方案：

方案1：清理脏数据 #

如果是数据质量问题，先将非数组的items转为合法数组（比如单值转成单元素数组），或删除/修正异常数据：

-- 示例：将字符串类型的items转为单元素数组
UPDATE product
SET items = ARRAY_CONSTRUCT(items)
WHERE TYPEOF(items) = 'STRING';

方案2：修改MERGE逻辑兼容非数组数据 #

如果无法清理数据，修改ON子句中的判断逻辑，确保ARRAY_TO_STRING只接收数组参数，或使用容错函数：

方式A：用CASE判断类型后处理

MERGE INTO product AS target
USING (SELECT product_id, items, product_hash FROM STAGE_PRODUCT_VIEW) as source
ON 
 COALESCE(target.product_id, 'NA') = COALESCE(source.product_id, 'NA')
 AND COALESCE(
     CASE WHEN TYPEOF(target.items) = 'ARRAY' THEN ARRAY_TO_STRING(target.items, 'NA') ELSE 'NA' END,
     'NA'
 ) = COALESCE(
     CASE WHEN TYPEOF(source.items) = 'ARRAY' THEN ARRAY_TO_STRING(source.items, 'NA') ELSE 'NA' END,
     'NA'
 )
WHEN MATCHED AND (target.product_hash <> source.product_hash) THEN
    UPDATE SET
        target.product_id = source.product_id,
        target.items = source.items,
        target.product_hash = source.product_hash
WHEN NOT MATCHED THEN 
    INSERT (product_id, items, product_hash) VALUES(source.product_id, source.items, source.product_hash);

方式B：使用TRY_ARRAY_TO_STRING容错

Snowflake的TRY_*系列函数会在参数错误时返回NULL，配合COALESCE处理：

MERGE INTO product AS target
USING (SELECT product_id, items, product_hash FROM STAGE_PRODUCT_VIEW) as source
ON 
 COALESCE(target.product_id, 'NA') = COALESCE(source.product_id, 'NA')
 AND COALESCE(TRY_ARRAY_TO_STRING(target.items, 'NA'), 'NA') = COALESCE(TRY_ARRAY_TO_STRING(source.items, 'NA'), 'NA')
WHEN MATCHED AND (target.product_hash <> source.product_hash) THEN
    UPDATE SET
        target.product_id = source.product_id,
        target.items = source.items,
        target.product_hash = source.product_hash
WHEN NOT MATCHED THEN 
    INSERT (product_id, items, product_hash) VALUES(source.product_id, source.items, source.product_hash);

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