Snowflake存储过程实现：Raw层到Transformed层列类型转换与数据迁移

以下是一个可复用的存储过程，支持手动指定各列的目标数据类型，完成从Raw层到Transformed层的数据迁移与类型转换，包含基础错误处理：

CREATE OR REPLACE PROCEDURE RAW_TO_TRANSFORMED(
    p_raw_table VARCHAR,
    p_transformed_table VARCHAR,
    p_column_mappings VARCHAR
)
RETURNS VARCHAR
LANGUAGE SQL
EXECUTE AS CALLER
AS
$$
DECLARE
    v_create_sql VARCHAR;
    v_insert_sql VARCHAR;
    v_result VARCHAR;
BEGIN
    -- 构建目标表创建SQL（若不存在则创建）
    v_create_sql := 'CREATE OR REPLACE TABLE ' || p_transformed_table || ' (' || p_column_mappings || ')';
    EXECUTE IMMEDIATE v_create_sql;

    -- 构建插入SQL，自动映射列并转换类型
    v_insert_sql := 'INSERT INTO ' || p_transformed_table || ' (' || 
                    (SELECT LISTAGG(SPLIT_PART(value, ' ', 1), ', ') FROM TABLE(SPLIT_TO_TABLE(p_column_mappings, ','))) || 
                    ') SELECT ' || 
                    (SELECT LISTAGG('TRY_TO_' || SPLIT_PART(value, ' ', 2) || '(' || SPLIT_PART(value, ' ', 1) || ')', ', ') FROM TABLE(SPLIT_TO_TABLE(p_column_mappings, ','))) || 
                    ' FROM ' || p_raw_table;

    EXECUTE IMMEDIATE v_insert_sql;
    v_result := '数据迁移完成：共插入 ' || SQLROWCOUNT || ' 条记录';
    RETURN v_result;

EXCEPTION
    WHEN OTHERS THEN
        RETURN '迁移失败：' || SQLERRM;
END;
$$;

调用示例 #

假设Raw层表RAW_DB.RAW_SCHEMA.ORDERS_RAW包含ID、ORDER_DATE、AMOUNT、STATUS四个VARCHAR列，要转换为Transformed层表TRANSFORM_DB.TRANSFORM_SCHEMA.ORDERS，对应类型为INT、DATE、DECIMAL(10,2)、VARCHAR(50)：

CALL RAW_TO_TRANSFORMED(
    'RAW_DB.RAW_SCHEMA.ORDERS_RAW',
    'TRANSFORM_DB.TRANSFORM_SCHEMA.ORDERS',
    'ID INT, ORDER_DATE DATE, AMOUNT DECIMAL(10,2), STATUS VARCHAR(50)'
);

关键说明 #

1. 类型转换容错：存储过程使用TRY_TO_*系列函数（如TRY_TO_DATE、TRY_TO_INT），转换失败时会返回NULL而非中断整个任务，若需要严格校验可替换为TO_*函数
2. 列映射规则：p_column_mappings参数需严格遵循列名 目标数据类型的格式，多列用逗号分隔
3. 权限要求：执行存储过程的角色需拥有Raw层表的SELECT权限，以及Transformed层的CREATE TABLE和INSERT权限
4. 增量加载扩展：若需实现增量迁移，可添加p_last_sync_timestamp参数，在插入SQL中增加WHERE条件过滤新增数据

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