snowflake-cloud-data-platform - s3下.csv文件的雪花外部表

Question

假设我有这样的 .csv 文件:

event,user
1,123
2,321

此 .csv 文件位于 s3 下。

运行以下 sql 以创建外部表(创建了 @TEST_STAGE 并具有正确的 s3 路径):

CREATE OR REPLACE EXTERNAL TABLE TEST_CSV_TABLE1(
  event_id VARCHAR AS (value:$1::varchar),
  user_id VARCHAR AS (value:$2::varchar)
)
WITH LOCATION = @TEST_STAGE
FILE_FORMAT = (TYPE = CSV FIELD_DELIMITER = ',' SKIP_HEADER = 1);

查询下表会产生以下输出:

|-----|----------------------------|----------|---------|
| Row |           VALUE            | EVENT_ID | USER_ID |
|-----|----------------------------|----------|---------|
|  1  | { "c1": "1", "c2": "123" } |   NULL   |   NULL  |
|-----|----------------------------|----------|---------|
|  2  | { "c1": "2", "c2": "321" } |   NULL   |   NULL  |
|-----|----------------------------|----------|---------|

但是，如果我只是创建一个表作为

CREATE OR REPLACE TABLE TEST_CSV_TABLE2(
  event_id VARCHAR,
  user_id VARCHAR
);

并像这样加载相同的文件:

COPY INTO TEST_CSV_TABLE2 FROM @TEST_STAGE
FILES = ('test.csv')
FILE_FORMAT = (TYPE = CSV FIELD_DELIMITER = ',' SKIP_HEADER = 1);

甚至喜欢:

COPY INTO TEST_CSV_TABLE2
FROM (
SELECT 
  t.$1,
  t.$2
FROM @ TEST_STAGE t)
FILES = ('test.csv')
FILE_FORMAT = (TYPE = CSV FIELD_DELIMITER = ',' SKIP_HEADER = 1);

这会导致正确分配的列:

|-----|----------|---------|
| Row | EVENT_ID | USER_ID |
|-----|----------|---------|
|  1  |     1    |   123   |
|-----|----------|---------|
|  2  |     2    |   321   |
|-----|----------|---------|

为什么在外部表的情况下不能正确选择列？非常感谢。

Answer 1

从 JSON 中提取列时，您需要使用该列的名称。您所拥有的是创建 JSON 列，然后解析它以获取 JSON 中称为“$1”和“$2”的属性。当它找不到这样的属性时，它会向该列返回 NULL。

CREATE OR REPLACE EXTERNAL TABLE TEST_CSV_TABLE1(
  event_id VARCHAR AS (value:c1::varchar),
  user_id VARCHAR AS (value:c2::varchar)
)
WITH LOCATION = @TEST_STAGE
FILE_FORMAT = (TYPE = CSV FIELD_DELIMITER = ',' SKIP_HEADER = 1);

使用 copy into 和 $1 和 $2 并不是像上面那样使用它们来解析 JSON，它是特定于复制到查询以引用文件中的列。