如何为Snowflake Pipe添加数据摄入时间戳并正确解析JSON？

问题背景 #

现有Snowflake管道配置如下，通过AWS SNS自动将S3中的GZIP压缩JSON数据摄入到raw.table表的json列，使用raw.json_gz文件格式解析：

CREATE OR REPLACE PIPE stage.table_pipe
AUTO_INGEST = TRUE
AWS_SNS_TOPIC = 'arn:::'
AS COPY INTO raw.table (json) FROM @raw.stage/ FILE_FORMAT = (FORMAT_NAME = raw.json_gz);

需要为目标表新增TIMESTAMP_MODIFIED列，记录每次数据摄入的时间戳，但之前的改写尝试均失败：

• 第一种写法去掉文件格式后可运行，但无法正确解析JSON：

COPY INTO raw.table
FROM (
    SELECT $1, CURRENT_TIMESTAMP() AS TIMESTAMP_MODIFIED
    FROM @raw.stage
    )
FILE_FORMAT = (FORMAT_NAME = raw.json_gz);

• 第二种写法存在语法错误：

AS COPY INTO raw.table(json), CURRENT_TIMESTAMP() AS TIMESTAMP_MODIFIED FROM @raw.stage FILE_FORMAT = (FORMAT_NAME = raw.json_gz);

正确解决方案 #

核心问题是文件格式的位置错误，当使用子查询作为COPY INTO的数据源时，需将文件格式指定在阶段引用的位置，而非COPY INTO语句末尾。正确的管道创建语句如下：

CREATE OR REPLACE PIPE stage.table_pipe
AUTO_INGEST = TRUE
AWS_SNS_TOPIC = 'arn:::'
AS COPY INTO raw.table (json, TIMESTAMP_MODIFIED)
FROM (
    SELECT $1, CURRENT_TIMESTAMP()
    FROM @raw.stage (FILE_FORMAT => raw.json_gz)
);

关键说明 #

1. 文件格式关联阶段：在子查询的@raw.stage后通过(FILE_FORMAT => raw.json_gz)指定解析格式，确保Snowflake正确读取并解析S3中的GZIP压缩JSON文件，将每行内容映射为$1对应的JSON对象。
2. 明确目标列映射：COPY INTO语句中需显式指定目标表的列(json, TIMESTAMP_MODIFIED)，保证子查询返回的两列（JSON数据、时间戳）与目标列一一对应。
3. 摄入时间戳生成：CURRENT_TIMESTAMP()会在数据实际被摄入Snowflake的时刻生成时间戳，准确记录数据的入库时间。

错误原因解析 #

• 第一种写法将文件格式放在COPY INTO末尾，子查询读取阶段数据时未应用格式规则，导致无法解析JSON结构；去掉文件格式后仅能读取原始文本，因此能运行但解析错误。
• 第二种写法违反Snowflake COPY INTO语法规范，不能直接在COPY INTO的目标列后附加表达式，必须通过子查询构造包含额外列的数据集。

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