如何基于带日期层级的S3路径创建Snowflake外部表

问题描述 #

我的S3存储桶结构如下：

s3://rawzone/toyoto/2020-11-04/

每个日期目录下固定包含4个JSON文件：

s3://rawzone/toyoto/2020-11-04/Company.json
s3://rawzone/toyoto/2020-11-04/sales.json
s3://rawzone/toyoto/2020-11-04/transport.json
s3://rawzone/toyoto/2020-11-04/preaquisitions.json

这类日期目录数量众多（比如2020-11-05/下也有完全相同的4个文件）。我需要分别为Company、sales、transport、preaquisitions这四类数据创建覆盖所有日期的外部表，该如何配置才能正确从S3获取对应数据？

我目前尝试的代码如下（未明确部分用???标记）：

create or replace external table toyoto.PB_compnay
(
   columns mapping......
)
partition by ???
with location = @raw_zone/pitchbook/??????
file_format = json_format
aws_sns_topic='arn:aws:sns:us-west-1:5438:dev-autore'
auto_refresh = true 

解决方案 #

针对你的场景，需要利用Snowflake的外部表分区自动识别和路径过滤能力，分别抓取每个类型的文件，具体配置如下：

核心配置逻辑 #

• 分区字段：S3路径中的2020-11-04这类日期是天然分区键，可通过文件元数据自动提取并转换为日期类型。
• 路径过滤：通过正则表达式匹配文件名，确保每个表只加载对应类型的JSON文件，同时将location指向根目录以覆盖所有日期目录。

各表创建示例 #

Company表

create or replace external table toyoto.PB_company
(
    -- 替换为Company.json对应的实际列映射，示例：
    company_id string,
    company_name string,
    industry string,
    -- 从文件路径自动提取日期分区
    load_date date as to_date(split_part(metadata$filename, '/', 4), 'YYYY-MM-DD')
)
partition by (load_date)
with location = @raw_zone/toyoto/  -- 指向toyoto根目录，覆盖所有日期子目录
file_format = json_format
pattern = '.*Company\\.json$'  -- 仅匹配所有日期下的Company.json文件
aws_sns_topic='arn:aws:sns:us-west-1:5438:dev-autore'
auto_refresh = true;

sales表

create or replace external table toyoto.PB_sales
(
    -- 替换为sales.json对应的实际列映射，示例：
    sale_id string,
    transaction_amount number(18,2),
    customer_id string,
    load_date date as to_date(split_part(metadata$filename, '/', 4), 'YYYY-MM-DD')
)
partition by (load_date)
with location = @raw_zone/toyoto/
file_format = json_format
pattern = '.*sales\\.json$'
aws_sns_topic='arn:aws:sns:us-west-1:5438:dev-autore'
auto_refresh = true;

transport表

create or replace external table toyoto.PB_transport
(
    -- 替换为transport.json对应的实际列映射，示例：
    transport_id string,
    carrier_name string,
    route_info string,
    load_date date as to_date(split_part(metadata$filename, '/', 4), 'YYYY-MM-DD')
)
partition by (load_date)
with location = @raw_zone/toyoto/
file_format = json_format
pattern = '.*transport\\.json$'
aws_sns_topic='arn:aws:sns:us-west-1:5438:dev-autore'
auto_refresh = true;

preaquisitions表

create or replace external table toyoto.PB_preaquisitions
(
    -- 替换为preaquisitions.json对应的实际列映射，示例：
    deal_id string,
    target_company string,
    deal_amount number(18,2),
    load_date date as to_date(split_part(metadata$filename, '/', 4), 'YYYY-MM-DD')
)
partition by (load_date)
with location = @raw_zone/toyoto/
file_format = json_format
pattern = '.*preaquisitions\\.json$'
aws_sns_topic='arn:aws:sns:us-west-1:5438:dev-autore'
auto_refresh = true;

关键细节说明 #

• 分区字段提取：通过metadata$filename获取文件完整路径，使用split_part函数提取路径中的第4段（即日期目录名），再转换为date类型作为分区键load_date。
• 正则过滤：pattern参数中的正则表达式确保每个表只加载对应类型的文件，避免跨类型数据混入。
• 自动刷新：location指向根目录后，auto_refresh会自动发现新增的日期目录，无需手动维护外部表范围。

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