Snowflake/DBT：插入语句中动态生成Checksum列的实现方法

直接用SHOW COLUMNS做内联子查询确实无法生效，得通过查询系统信息表动态获取列名，再拼接成哈希语句。下面分两种主流数据库场景给出实用方案：

MySQL/MariaDB 实现方式 #

1. 先获取需要哈希的列列表 #

通过INFORMATION_SCHEMA.COLUMNS表筛选目标列，排除不需要的字段：

SELECT GROUP_CONCAT(column_name SEPARATOR ', ') AS hash_columns
FROM INFORMATION_SCHEMA.COLUMNS
WHERE table_schema = 'my'  -- 替换为你的数据库名
  AND table_name = 'example'  -- 替换为你的表名
  AND column_name NOT LIKE 'example%';  -- 排除指定前缀的列

2. 拼接INSERT语句 #

把上面查询得到的列列表，直接插入到INSERT语句的哈希部分：

INSERT INTO my.example.table (col1, col2, ..., checksum_col)
SELECT col1, col2, ..., SHA2(CONCAT_WS('|', 这里替换为上面得到的列列表), 256) AS checksum_col
FROM source_table;  -- 替换为你的数据源表

3. 自动生成并执行（可选） #

如果不想手动复制列名，可写存储过程一键完成：

DELIMITER //
CREATE PROCEDURE AutoInsertWithChecksum()
BEGIN
  DECLARE hash_cols TEXT;
  -- 获取目标列
  SELECT GROUP_CONCAT(column_name SEPARATOR ', ') INTO hash_cols
  FROM INFORMATION_SCHEMA.COLUMNS
  WHERE table_schema = 'my'
    AND table_name = 'example'
    AND column_name NOT LIKE 'example%';
  
  -- 拼接完整INSERT语句
  SET @insert_sql = CONCAT(
    'INSERT INTO my.example.table (', hash_cols, ', checksum_col) ',
    'SELECT ', hash_cols, ', SHA2(CONCAT_WS(''|'', ', hash_cols, '), 256) AS checksum_col ',
    'FROM source_table;'
  );
  
  -- 执行语句
  PREPARE stmt FROM @insert_sql;
  EXECUTE stmt;
  DEALLOCATE PREPARE stmt;
END //
DELIMITER ;

-- 调用存储过程
CALL AutoInsertWithChecksum();

PostgreSQL 实现方式 #

1. 获取目标列列表 #

用information_schema.columns配合string_agg拼接列名：

SELECT string_agg(column_name, ', ') AS hash_columns
FROM information_schema.columns
WHERE table_schema = 'my'
  AND table_name = 'example'
  AND column_name NOT LIKE 'example%';

2. 拼接INSERT语句 #

INSERT INTO my.example.table (col1, col2, ..., checksum_col)
SELECT col1, col2, ..., sha256(CONCAT_WS('|', 列列表))::text AS checksum_col
FROM source_table;

3. 自动执行函数（可选） #

CREATE OR REPLACE FUNCTION AutoInsertWithChecksum()
RETURNS void AS $$
DECLARE
  hash_cols text;
  insert_sql text;
BEGIN
  SELECT string_agg(column_name, ', ') INTO hash_cols
  FROM information_schema.columns
  WHERE table_schema = 'my'
    AND table_name = 'example'
    AND column_name NOT LIKE 'example%';
  
  insert_sql := format(
    'INSERT INTO my.example.table (%s, checksum_col) SELECT %s, sha256(CONCAT_WS(''|'', %s))::text AS checksum_col FROM source_table;',
    hash_cols, hash_cols, hash_cols
  );
  
  EXECUTE insert_sql;
END;
$$ LANGUAGE plpgsql;

-- 调用函数
SELECT AutoInsertWithChecksum();

关键注意点 #

• 必须用CONCAT_WS指定分隔符（比如|），避免不同列的数值拼接后产生歧义（例如列A=12、列B=3，直接拼接是123，和列A=1、列B=23结果一致，加分隔符后会变成12|3和1|23，不会冲突）。
• CONCAT_WS会自动跳过NULL值，而普通CONCAT只要有一个NULL结果就是NULL，根据业务需求选择。
• 哈希函数可按需选择：MD5性能快但安全性低，SHA256安全性高但性能稍弱。

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