Snowflake存储过程批量传递多行数据的方案咨询

问题1：Variant vs Text类型处理嵌套列表格式 #

• Variant类型完全可以直接处理[[row1],[row2],...]这种嵌套数组格式，它原生支持JSON/半结构化数据。只要传入的内容是合法的JSON数组（比如字符串值带双引号、数值/布尔值直接书写），Snowflake会自动解析成Variant类型的数组结构，后续可直接用FLATTEN、ARRAY_GET等函数操作。
• 如果用Text类型，需要手动通过PARSE_JSON函数转换为Variant，多了一步冗余操作，所以优先选择Variant类型。

问题2：替代嵌套列表的方案 #

最推荐的替代方案是JSON对象数组格式，即[{"列名1": 值1, "列名2": 值2}, {"列名1": 值3, "列名2": 值4}, ...]。相比纯嵌套数组，它的优势是：

• 无需依赖列的顺序，后续表结构调整列顺序时，只要列名匹配就不会出错
• 可读性更强，每个元素明确对应一行的各列数据，调试和维护更便捷

其他可选方案比如CSV格式字符串（用分隔符分隔行和列），但这种格式容易遇到分隔符冲突（比如字段值包含逗号），需要额外转义，可靠性远不如JSON。

最优解决方案示例 #

1. 基于JSON对象数组的存储过程（推荐） #

CREATE OR REPLACE PROCEDURE BULK_INSERT_TO_TABLE(p_data VARIANT)
RETURNS VARCHAR
LANGUAGE SQL
AS
$$
BEGIN
  -- 展开JSON数组，将每个对象映射为表的行数据
  INSERT INTO YOUR_TARGET_TABLE(col1, col2, col3)
  SELECT 
    value:col1::VARCHAR,
    value:col2::NUMBER,
    value:col3::DATE
  FROM TABLE(FLATTEN(input => p_data));
  
  RETURN '成功插入 ' || SQLROWCOUNT || ' 行数据';
END;
$$;

调用示例：

CALL BULK_INSERT_TO_TABLE(
  [
    {"col1": "A", "col2": 100, "col3": "2024-01-01"},
    {"col1": "B", "col2": 200, "col3": "2024-01-02"}
  ]
);

2. 兼容原始嵌套数组格式的存储过程 #

如果必须使用[[值1,值2,值3], [值4,值5,值6]]的格式，可使用以下存储过程：

CREATE OR REPLACE PROCEDURE BULK_INSERT_FROM_ARRAY(p_data VARIANT)
RETURNS VARCHAR
LANGUAGE SQL
AS
$$
BEGIN
  INSERT INTO YOUR_TARGET_TABLE(col1, col2, col3)
  SELECT 
    ARRAY_GET(value, 0)::VARCHAR,
    ARRAY_GET(value, 1)::NUMBER,
    ARRAY_GET(value, 2)::DATE
  FROM TABLE(FLATTEN(input => p_data));
  
  RETURN '成功插入 ' || SQLROWCOUNT || ' 行数据';
END;
$$;

调用示例：

CALL BULK_INSERT_FROM_ARRAY([["A",100,"2024-01-01"],["B",200,"2024-01-02"]]);

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