如何在Snowflake中通过infer_schema动态建表时添加文件名列？

1. 修改动态建表语句 #

通过在infer_schema的结果中追加文件名列的结构定义，让动态生成的表包含FILE_NAME字段：

Create or replace table t1
using template (
    select array_agg(obj)
    from (
        -- 获取文件原有字段结构
        select object_construct(*) as obj
        from table(
            infer_schema(
                location => '@stage'
                , file_format => 'file_format'
            )
        )
        union all
        -- 手动添加文件名列的结构
        select object_construct(
            'NAME', 'FILE_NAME',
            'TYPE', 'VARCHAR',
            'NULLABLE', TRUE
        ) as obj
    )
);

2. 修改数据加载语句 #

在COPY INTO中通过子查询同时加载文件名和文件内容：

copy into t1
from (
    select metadata$filename, $1.*
    from @stage
)
file_format = 'file_format'
match_by_column_name = case_insensitive;

说明 #

• 建表阶段：用union all将自定义的FILE_NAME字段（VARCHAR类型、允许为空）合并到自动推断的字段列表中，确保表结构包含文件名存储列。
• 加载阶段：metadata$filename获取当前加载文件的名称，$1.*引用文件内的所有字段，通过match_by_column_name自动匹配列名完成数据写入。

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