Snowflake环境下编写接收动态参数创建表的Stored procedure方法

实现逻辑 #

• 采用ARRAY类型作为动态入参承载扩展列定义，不需要每次新增列就修改存储过程的入参声明，兼容任意数量的扩展列需求
• 内置固定7个基础列的定义，后续基础列调整只需修改存储过程内部的基础列配置即可
• 自动拼接生成完整的建表DDL，内置幂等建表逻辑避免重复创建报错

完整存储过程代码 #

CREATE OR REPLACE PROCEDURE CREATE_DYNAMIC_TABLE(table_name STRING, extra_columns ARRAY)
RETURNS STRING
LANGUAGE JAVASCRIPT
AS
$$
    // 固定7个基础列定义，后续调整直接修改此处
    const baseColumns = [
        'ID NUMBER(38,0) NOT NULL PRIMARY KEY',
        'RECORD_STATUS VARCHAR(10) DEFAULT \'ACTIVE\'',
        'CREATED_BY VARCHAR(100) NOT NULL',
        'CREATED_AT TIMESTAMP_LTZ DEFAULT CURRENT_TIMESTAMP()',
        'UPDATED_BY VARCHAR(100)',
        'UPDATED_AT TIMESTAMP_LTZ',
        'IS_DELETED BOOLEAN DEFAULT FALSE'
    ];
    // 合并基础列和传入的扩展列
    const allColumns = baseColumns.concat(EXTRA_COLUMNS);
    // 组装建表语句
    const createTableSql = `CREATE OR REPLACE TABLE ${TABLE_NAME} (
        ${allColumns.join(',\n        ')}
    )`;
    // 执行建表操作
    snowflake.execute({sqlText: createTableSql});
    return `执行成功，表${TABLE_NAME}共创建${allColumns.length}个列`;
$$;

调用示例 #

• 仅创建包含固定7列的表

CALL CREATE_DYNAMIC_TABLE('MY_DB.MY_SCHEMA.USER_BASE_INFO', []);

• 创建包含固定列+2个扩展列的表

CALL CREATE_DYNAMIC_TABLE('MY_DB.MY_SCHEMA.USER_EXT_INFO', 
    ['USER_PHONE VARCHAR(20) NOT NULL', 'USER_DEPT_ID NUMBER(38,0) DEFAULT 0']
);

扩展说明 #

• 扩展列支持完整的列定义语法，包括非空约束、默认值、字段注释等，直接将完整定义作为数组元素传入即可，无需修改存储过程逻辑
• 如果需要调整固定列的数量、类型、约束，直接修改存储过程中baseColumns数组的配置即可，不影响原有调用逻辑

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