Блог

У меня есть тестовая таблица, которую я пытаюсь загрузить из хранилища 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 для хранения типов МАССИВА:

 "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}]
  

Затем я хочу использовать СЛИЯНИЕ для обновления данных в целевой таблице. Когда я запускаю это:

 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]
  

Если я удалю весь последний раздел для того, чтобы ПРИ СОПОСТАВЛЕНИИ он вставлял только временные функции, они будут работать нормально. Итак, проблема, похоже, в том, что в заключительном THEN UPDATE SET разделе я не могу использовать временные функции.

Как я могу заставить такие типы данных, как ARRAY<STRING> и ARRAY<STRUCT<STRING,STRING>> , загружаться из внешнего хранилища в идеале с помощью одной инструкции MERGE?

Обновление: Я попытался использовать общее табличное выражение для предварительной обработки данных с помощью:

 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 в качестве формата, чтобы вам не нужно было явно анализировать вложенные или повторяющиеся столбцы.

Оказывается, 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