BigQuery: объедините две разные таблицы, основанные на объединенной таблице Google.

У меня есть две разные таблицы Google:

Один с 4 столбцами

+------+------+------+------+
| Col1 | Col2 | Col5 | Col6 |
+------+------+------+------+
| ID1  | A    | B    | C    |
| ID2  | D    | E    | F    |
+------+------+------+------+

Один с 4 столбцами предыдущего файла и еще 2 столбца

+------+------+------+------+------+------+
| Col1 | Col2 | Col3 | Col4 | Col5 | Col6 |
+------+------+------+------+------+------+
| ID3  | G    | H    | J    | K    | L    |
| ID4  | M    | N    | O    | P    | Q    |
+------+------+------+------+------+------+

Я настроил их как федеративный источник в Google BigQuery, теперь мне нужно создать представление, которое объединит данные обеих таблиц.

Обе таблицы имеют Col1 столбец, который содержит идентификатор, этот идентификатор уникален для всех таблиц, не содержит реплицированных данных.

В результате я ищу следующую таблицу:

+------+------+------+------+------+------+
| Col1 | Col2 | Col3 | Col4 | Col5 | Col6 |
+------+------+------+------+------+------+
| ID1  | A    | NULL | NULL | B    | C    |
| ID2  | D    | NULL | NULL | E    | F    |
| ID3  | G    | H    | J    | K    | L    |
| ID4  | M    | N    | O    | P    | Q    |
+------+------+------+------+------+------+

Для столбцов, которых нет в первом файле, я ожидаю значение NULL.

Я использую стандартный SQL, вот оператор, который вы можете использовать для генерации образца данных:

#standardsQL

WITH table1 AS (
  SELECT "A" as Col1, "B" as Col2, "C" AS Col3
  UNION ALL
  SELECT "D" as Col1, "E" as Col2, "F" AS Col3
),

table2 AS (
  SELECT "G" as Col1, "H" as Col2, "J" AS Col3, "K" AS Col4, "L" AS Col5
  UNION ALL
  SELECT "M" as Col1, "N" as Col2, "O" AS Col3, "P" AS Col4, "Q" AS Col5
)

Простой UNION ALL не работает, потому что в таблицах разные столбцы

SELECT * FROM table1
UNION ALL
SELECT * FROM table2

Error: Queries in UNION ALL have mismatched column count; query 1 has 3 columns, query 2 has 5 columns at [17:1]

И оператор с подстановочными знаками не подходит, потому что федеративные источники не поддерживают это.

SELECT * FROM `table*`

Error: External tables cannot be queried through prefix

Конечно, это образец данных, всего 3-5 столбцов, в реальных таблицах 20-40 столбцов. Итак, пример, в котором мне нужно явно SELECT поле за полем, это несущественный способ.

Есть ли рабочий способ объединить эти две таблицы?


google-sheets sql google-bigquery bigquery-standard-sql
person Deviling Master    schedule 10.01.2018    source источник

Ответы (2)


arrow_upward
4
arrow_downward

Есть ли рабочий способ объединить эти две таблицы?

#standardsQL
SELECT *, NULL AS Col5, NULL AS Col6 FROM table1
UNION ALL
SELECT * FROM table2

Вы можете проверить это на своем примере

#standardsQL
WITH table1 AS (
  SELECT "ID1" AS Col1, "A" AS Col2, "B" AS Col3, "C" AS Col4 
  UNION ALL
  SELECT "ID2", "D", "E", "F"
),
table2 AS (
  SELECT "ID3" Col1, "G" AS Col2, "H" AS Col3, "J" AS Col4, "K" AS Col5, "L" AS Col6 
  UNION ALL
  SELECT "ID4", "M", "N", "O", "P", "Q" 
)
SELECT *, NULL AS Col5, NULL AS Col6 FROM table1
UNION ALL
SELECT * FROM table2
person Mikhail Berlyant    schedule 10.01.2018
comment
Я ищу способ, который не требует настройки имен столбцов один за другим, потому что в реальных таблицах дополнительные столбцы имеют размер до 20 // 30, поэтому поддерживать эту схему может быть сложно. Есть ли еще автоматический способ? Если таблицы являются настоящими таблицами, а не внешними, может ли подстановочный знак быть подходящим способом? - person Deviling Master; 10.01.2018
comment
У меня такая же проблема, и у меня слишком много столбцов, чтобы вручную настроить все имена. Вариант внешнего объединения в BQ был бы действительно полезен. - person ZaxR; 14.08.2019

arrow_upward
5
arrow_downward

Вы можете передавать строки через UDF для обработки случая, когда имена столбцов не выровнены по положению или их количество в таблицах разное. Вот пример:

CREATE TEMP FUNCTION CoerceRow(json_row STRING)
RETURNS STRUCT<Col1 STRING, Col2 STRING, Col3 STRING, Col4 STRING, Col5 STRING>
LANGUAGE js AS """
return JSON.parse(json_row);
""";

WITH table1 AS (
  SELECT "A" as Col5, "B" as Col3, "C" AS Col2
  UNION ALL
  SELECT "D" as Col5, "E" as Col3, "F" AS Col2
),

table2 AS (
  SELECT "G" as Col1, "H" as Col2, "J" AS Col3, "K" AS Col4, "L" AS Col5
  UNION ALL
  SELECT "M" as Col1, "N" as Col2, "O" AS Col3, "P" AS Col4, "Q" AS Col5
)
SELECT CoerceRow(json_row).*
FROM (
  SELECT TO_JSON_STRING(t1) AS json_row
  FROM table1 AS t1
  UNION ALL
  SELECT TO_JSON_STRING(t2) AS json_row
  FROM table2 AS t2
);
+------+------+------+------+------+
| Col1 | Col2 | Col3 | Col4 | Col5 |
+------+------+------+------+------+
| NULL | C    | B    | NULL | A    |
| NULL | F    | E    | NULL | D    |
| G    | H    | J    | K    | L    |
| M    | N    | O    | P    | Q    |
+------+------+------+------+------+

Обратите внимание, что функция CoerceRow должна объявить явный тип строки, который вы хотите в выводе. В остальном столбцы в объединяемых таблицах сопоставляются только по имени.

person Elliott Brossard    schedule 10.01.2018
comment
Я ищу способ, который не требует настройки имен столбцов один за другим, потому что в реальных таблицах дополнительные столбцы имеют размер до 20 // 30, поэтому поддерживать эту схему может быть сложно. Есть ли еще автоматический способ? Если таблицы являются настоящими таблицами, а не внешними, может ли подстановочный знак быть подходящим способом? - person Deviling Master; 10.01.2018
comment
Подход с подстановочными знаками будет работать для реальных таблиц. Однако, учитывая ограничения, накладываемые вашей формулировкой проблемы, лучшим вариантом будет однократное изложение схемы. Вы можете подумать об запросе функции для естественного объединения хотя имя столбца. - person Elliott Brossard; 10.01.2018
comment
Браво!!! Определение гения - взять сложное и сделать его простым (Альберт Эйнштейн). Иногда это настолько очевидно, но я все еще многому учусь из сообщений Эллиотта! Спасибо! - person Mikhail Berlyant; 10.01.2018