sql - 将数据从舞台发送到雪花中的多列表

Question

我有一个内部命名阶段，其中存储 json 文件，我想从那里将​​它们存储在雪花表中。目标表的结构如下，

file_name (string)
load_date (timestamp)
data      (variant)

我正在使用以下查询将数据从阶段移动到表

copy into tableName (data) from @stagename/filename.json;

但是上面的查询只是填充数据列，我想要的也是插入时间戳和文件名。知道我需要在查询中进行哪些更改吗？谢谢

标签： sqlsnowflake-cloud-data-platformsnowflake-schema

Answer 1

您需要在此处COPY使用带有转换文档的语句。当您使用该方法时，您可以查询文件的元数据以获取文件名、行号等 -此处的文档。

filename.json上传到名为的内部阶段的示例文件stagename：

[{"name": "simon"},{"name": "jason"}, {"name": "jessica"}]

Sql 加载创建和加载表：

-- Create example table first with 3 columns
create or replace transient table test_table
(
    file_name varchar,
    load_date timestamp,
    data      variant
);


-- Load with transformation: 
copy into test_table (file_name, load_date, data) from (
    select
        metadata$filename,
        current_timestamp,
        f.$1
    from @stagename/filename.json f
)
    file_format = (
        type = json
            strip_outer_array = true
        )
    force=true
;

结果：

+-------------+-----------------------------+-----------------------+
|FILE_NAME    |LOAD_DATE                    |DATA                   |
+-------------+-----------------------------+-----------------------+
|filename.json|2021-07-16 08:56:24.075000000|{"name": "simon"}      |
|filename.json|2021-07-16 08:56:24.075000000|{"name": "jason"}      |
|filename.json|2021-07-16 08:56:24.075000000|{"name": "jessica"}    |
+-------------+-----------------------------+-----------------------+