如何在Snowflake中获取指定事务包含的全部查询及DML操作元数据

有成熟的实现方案，可以通过Snowflake内置的查询历史视图结合事务ID筛选完成需求，具体操作步骤如下：

• 步骤1：事务执行前获取当前事务唯一标识
  开启事务后先执行语句获取本次事务ID，后续用于筛选该事务下所有执行的语句：

SELECT CURRENT_TRANSACTION_ID();

将返回的事务ID值记为TXN_ID备用。

• 步骤2：正常执行事务内的DML操作
  按业务逻辑执行你需要的3条INSERT语句，完成后正常提交事务即可，所有执行记录会自动同步到Snowflake的查询历史中。
• 步骤3：查询该事务下所有DML元数据
  执行以下查询即可得到3条INSERT分别对应的元数据结果：

SELECT 
  QUERY_ID,
  QUERY_TEXT,
  START_TIME,
  END_TIME,
  ROWS_INSERTED,
  ROWS_UPDATED,
  ROWS_DELETED,
  EXECUTION_STATUS
FROM TABLE(INFORMATION_SCHEMA.QUERY_HISTORY())
WHERE TRANSACTION_ID = '你之前获取的TXN_ID'
AND QUERY_TYPE IN ('INSERT','UPDATE','DELETE','MERGE');

• 步骤4：将DML元数据持久化到自定义表
  如果需要长期留存记录，可以先创建自定义日志表，再把查询结果写入表中：

-- 建表示例
CREATE OR REPLACE TABLE TXN_DML_RECORDS (
  TRANSACTION_ID STRING,
  QUERY_ID STRING,
  DML_TEXT STRING,
  EXECUTE_START TIMESTAMP_LTZ,
  EXECUTE_END TIMESTAMP_LTZ,
  INSERT_ROWS NUMBER,
  UPDATE_ROWS NUMBER,
  DELETE_ROWS NUMBER,
  RUN_STATUS STRING
);

-- 写入对应事务的DML记录
INSERT INTO TXN_DML_RECORDS
SELECT 
  TRANSACTION_ID,
  QUERY_ID,
  QUERY_TEXT,
  START_TIME,
  END_TIME,
  ROWS_INSERTED,
  ROWS_UPDATED,
  ROWS_DELETED,
  EXECUTION_STATUS
FROM TABLE(INFORMATION_SCHEMA.QUERY_HISTORY())
WHERE TRANSACTION_ID = '你之前获取的TXN_ID'
AND QUERY_TYPE IN ('INSERT','UPDATE','DELETE','MERGE');

• INFORMATION_SCHEMA.QUERY_HISTORY默认仅保留最近7天的查询记录，企业版及以上版本可调整保留时长最长到1年，需要更长时间存档的场景请提前把数据同步到自定义表。
• 执行查询的账号需要拥有对应库schema的INFORMATION_SCHEMA访问权限，以及查询历史的查看权限。

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