如何通过Snowpipe同时加载XML与JSON两种文件格式？

要同时加载XML和JSON格式文件到Snowflake，核心是利用Snowpipe的文件匹配规则+条件解析逻辑，以下是具体实现步骤：

1. 创建对应文件格式对象 #

首先分别定义适配JSON和XML的文件格式，确保解析规则符合你的数据结构：

-- JSON文件格式（根据实际需求调整参数）
CREATE OR REPLACE FILE_FORMAT json_ff
  TYPE = JSON
  STRIP_OUTER_ARRAY = TRUE
  TRIM_SPACE = TRUE;

-- XML文件格式（根据实际需求调整参数）
CREATE OR REPLACE FILE_FORMAT xml_ff
  TYPE = XML
  STRIP_OUTER_ELEMENT = TRUE
  PRESERVE_SPACE = FALSE;

2. 创建存储阶段 #

指向存放两类文件的云存储路径（以S3为例，其他存储适配对应 credential 配置）：

CREATE OR REPLACE STAGE data_stage
  URL = 's3://your-storage-bucket/data-folder/'
  CREDENTIALS = (AWS_KEY_ID = 'your-access-key' AWS_SECRET_KEY = 'your-secret-key');

3. 创建Snowpipe并配置多格式加载逻辑 #

在Snowpipe的COPY语句中，通过METADATA$FILENAME识别文件后缀，分别解析JSON和XML数据，同时用PATTERN过滤仅处理这两类文件：

CREATE OR REPLACE PIPE dual_format_pipe
AUTO_INGEST = TRUE
AS
COPY INTO target_table (raw_data, source_file, file_type)
FROM (
  SELECT
    -- 按文件类型解析原始数据，用VARIANT存储适配不同格式
    CASE
      WHEN REGEXP_LIKE(METADATA$FILENAME, '\.json$', 'i') THEN $1::VARIANT
      WHEN REGEXP_LIKE(METADATA$FILENAME, '\.xml$', 'i') THEN $1::VARIANT
      ELSE NULL
    END AS raw_data,
    METADATA$FILENAME AS source_file,
    -- 标记文件类型
    CASE
      WHEN REGEXP_LIKE(METADATA$FILENAME, '\.json$', 'i') THEN 'JSON'
      WHEN REGEXP_LIKE(METADATA$FILENAME, '\.xml$', 'i') THEN 'XML'
      ELSE 'UNKNOWN'
    END AS file_type
  FROM @data_stage
)
-- 根据文件类型选择对应的文件格式
FILE_FORMAT = (
  CASE
    WHEN REGEXP_LIKE(METADATA$FILENAME, '\.json$', 'i') THEN 'json_ff'
    WHEN REGEXP_LIKE(METADATA$FILENAME, '\.xml$', 'i') THEN 'xml_ff'
    ELSE NULL
  END
)
-- 仅匹配JSON/XML文件
PATTERN = '.*\.(json|xml)$'
-- 可选：跳过已加载文件
FORCE = FALSE;

4. 触发文件加载 #

如果配置了云存储事件通知（如S3的SQS），Snowpipe会自动检测新上传的文件并加载；若无需自动触发，可手动刷新管道：

ALTER PIPE dual_format_pipe REFRESH;

关键注意事项 #

• 目标表建议包含VARIANT类型字段存储原始解析数据，方便后续按需提取结构化内容；
• 正则匹配时添加'i'参数忽略大小写，兼容.JSON/.XML这类大写后缀的文件；
• 若两类文件的结构化字段差异较大，可考虑先加载到不同临时表，再通过ETL逻辑合并到最终表。

内容的提问来源于stack exchange，提问作者Sheethal K S