BigQuery：将两个基于联合Google Spreadsheet的不同表合并

Question

我有两个不同的Google Spreadsheet：

一列四列

+------+------+------+------+
| 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列，其中包含一个ID，此ID在所有表中都是唯一的，不包含复制的数据。

我要查找的结果表如下：

+------+------+------+------+------+------+
| 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值。

我正在使用standardSQL，这是可以用来生成示例数据的语句：

#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逐个字段明确地显示的示例中，这并不是一个相当好的方法。

有没有一种有效的方法可以连接这两个表？

Answer 1

有没有一种有效的方法可以连接这两个表？

#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