BigQuery如何使用MERGE加载数组列

Question

我有一个测试表，我正试图从GCS存储器加载：

CREATE OR REPLACE TABLE ta_producer_conformed.test
(
id NUMERIC,
array_string ARRAY<STRING>,
array_struct_string_string ARRAY<STRUCT<key STRING,  value STRING>>,
array_struct_string_numeric ARRAY<STRUCT<key STRING,  value NUMERIC>>,
array_struct_string_int64 ARRAY<STRUCT<key STRING,  value INT64>>
)

我已将外部存储表定义为：

{
"autodetect": true, 
"csvOptions": {
"encoding": "UTF-8", 
"quote": """,
"fieldDelimiter": "t"
}, 
"sourceFormat": "CSV", 
"sourceUris": [
"gs://my_bucket/test/input/*.tsv"
]
}

在它中，我使用JSON来保存ARRAY类型：

"id"    "array_string"  "struct_string_string"  "struct_string_numberic"    "struct_string_int64"
1   ["one", "two", "three"] [{"key":"one", "value":"1"},{"key":"two", "value":"2"},{"key":"three", "value":"3"}]    [{"key":"one", "value":1.1},{"key":"two", "value":2.2}] [{"key":"one", "value":11},{"key":"two", "value":22}]
2   ["four", "five", "six"] [{"key":"four", "value":"4"},{"key":"five", "value":"5"},{"key":"six", "value":"6"}]    [{"key":"three", "value":3.3},{"key":"four", "value":4.4}]  [{"key":"three", "value":33},{"key":"four", "value":44}]

然后我想使用MERGE来追加目标表中的数据。当我运行这个：

CREATE TEMPORARY FUNCTION ARRAY_OF(json STRING)
RETURNS ARRAY<STRING>
LANGUAGE js AS """
let parsed = JSON.parse(json);
return parsed;
""";
CREATE TEMPORARY FUNCTION ARRAY_STRUCT_STRING_STRING_OF(json STRING)
RETURNS ARRAY<STRUCT<key STRING, value STRING>>
LANGUAGE js AS """
let parsed = JSON.parse(json);
return parsed;
""";
CREATE TEMPORARY FUNCTION ARRAY_STRUCT_STRING_NUMERIC_OF(json STRING)
RETURNS ARRAY<STRUCT<key STRING, value NUMERIC>>
LANGUAGE js AS """
let parsed = JSON.parse(json);
return parsed;
""";
CREATE TEMPORARY FUNCTION ARRAY_STRUCT_STRING_INT64_OF(json STRING)
RETURNS ARRAY<STRUCT<key STRING, value INT64>>
LANGUAGE js AS """
let parsed = JSON.parse(json);
return parsed;
""";
MERGE ta_producer_conformed.test T
USING ta_producer_raw.test_raw S
ON
S.id = T.id
WHEN NOT MATCHED THEN
INSERT (id, array_string, array_struct_string_string, array_struct_string_numeric, array_struct_string_int64)
VALUES (
id, 
ARRAY_OF(array_string), 
ARRAY_STRUCT_STRING_STRING_OF(struct_string_string),
ARRAY_STRUCT_STRING_NUMERIC_OF(struct_string_numberic),
ARRAY_STRUCT_STRING_INT64_OF(struct_string_int64)
)
WHEN MATCHED THEN UPDATE SET
T.id = S.id, 
T.array_string = ARRAY_OF(S.array_string), 
T.struct_string_string = ARRAY_STRUCT_STRING_STRING_OF(S.struct_string_string),
T.ARRAY_STRUCT_STRING_NUMERIC_OF(S.struct_string_numberic),
T.ARRAY_STRUCT_STRING_INT64_OF(S.struct_string_int64)

我得到这个错误：

Error in query string: Error processing job 'xxxx-10843454-datamesh-
dev:bqjob_r4c426875_00000173fcfd2294_1': Syntax error: Expected "." or "=" or
"[" but got "(" at [1:1312]

如果我删除WHEN MATCHED的最后一节，这样它只插入临时函数就可以正常工作。因此，问题似乎是在THEN UPDATE SET的最后一节中，我无法使用临时函数。

理想情况下，我如何使用单个MERGE语句从外部存储桶加载ARRAY<STRING>和ARRAY<STRUCT<STRING,STRING>>等数据类型？

更新：我尝试使用Common Table Expression预处理数据，使用：

WITH cteConvertJason AS (
SELECT 
id, 
ARRAY_OF(array_string) AS array_string, 
ARRAY_STRUCT_STRING_STRING_OF(struct_string_string) AS struct_string_string,
ARRAY_STRUCT_STRING_NUMERIC_OF(struct_string_numberic) AS struct_string_numberic,
ARRAY_STRUCT_STRING_INT64_OF(struct_string_int64) AS struct_string_int64
FROM
ta_producer_raw.test_raw
)
MERGE ta_producer_conformed.test T
USING cteConvertJason S
...

这给出了一个错误，所以看起来像是将WITH和MERGE组合在一起。

更新：由于遗留问题，我们正在试用TSV。最好使用NEWLINE_DELIMITED_JSON作为格式，这样就不需要显式解析嵌套或重复的列。

Answer 1

事实证明MERGE target USING source可以使用查询作为源。该查询可以运行临时函数来预处理源数据。然后MERGE语句的其余部分可以是普通的并且有效：

CREATE TEMPORARY FUNCTION ARRAY_OF(json STRING)
RETURNS ARRAY<STRING>
LANGUAGE js AS """
let parsed = JSON.parse(json);
return parsed;
""";
CREATE TEMPORARY FUNCTION ARRAY_STRUCT_STRING_STRING_OF(json STRING)
RETURNS ARRAY<STRUCT<key STRING, value STRING>>
LANGUAGE js AS """
let parsed = JSON.parse(json);
return parsed;
""";
CREATE TEMPORARY FUNCTION ARRAY_STRUCT_STRING_NUMERIC_OF(json STRING)
RETURNS ARRAY<STRUCT<key STRING, value NUMERIC>>
LANGUAGE js AS """
let parsed = JSON.parse(json);
return parsed;
""";
CREATE TEMPORARY FUNCTION ARRAY_STRUCT_STRING_INT64_OF(json STRING)
RETURNS ARRAY<STRUCT<key STRING, value INT64>>
LANGUAGE js AS """
let parsed = JSON.parse(json);
return parsed;
""";
MERGE ta_producer_conformed.test T
USING 
(
SELECT 
id, 
ARRAY_OF(array_string) AS array_string, 
ARRAY_STRUCT_STRING_STRING_OF(struct_string_string) AS struct_string_string,
ARRAY_STRUCT_STRING_NUMERIC_OF(struct_string_numberic) AS struct_string_numberic,
ARRAY_STRUCT_STRING_INT64_OF(struct_string_int64) AS struct_string_int64
FROM
ta_producer_raw.test_raw
)
S
ON
S.id = T.id
WHEN NOT MATCHED THEN
INSERT (id, array_string, array_struct_string_string, array_struct_string_numeric, array_struct_string_int64)
VALUES (
id, 
array_string, 
struct_string_string,
struct_string_numberic,
struct_string_int64
)
WHEN MATCHED THEN UPDATE SET
T.array_string = S.array_string, 
T.array_struct_string_string = S.struct_string_string,
T.array_struct_string_numeric = S.struct_string_numberic,
T.array_struct_string_int64 = S.struct_string_int64