使用UNION ALL在BigQuery SQL创建合并表报错，寻求解决方案

问题描述 #

我正在开展谷歌分析课程的案例研究，尝试通过BigQuery SQL合并12个数据集创建名为bikeshare_total的新表，但一直出现报错：

Syntax error: Unexpected string literal "bikeshare_total" at [1:14]

原查询语句：

CREATE TABLE "bikeshare_total" AS (
SELECT *
FROM `cyclisticjan21-dec21.Bike_share.Jan2021`
UNION ALL
SELECT * 
FROM `cyclisticjan21-dec21.Bike_share.Feb2021`
UNION ALL
SELECT *
FROM `cyclisticjan21-dec21.Bike_share.March2021`
UNION ALL
SELECT *
FROM `cyclisticjan21-dec21.Bike_share.April2021`
UNION ALL
SELECT * 
FROM `cyclisticjan21-dec21.Bike_share.May2021`
UNION ALL
SELECT *
FROM `cyclisticjan21-dec21.Bike_share.June2021`
UNION ALL
SELECT * 
FROM `cyclisticjan21-dec21.Bike_share.July2021`
UNION ALL
SELECT *
FROM `cyclisticjan21-dec21.Bike_share.August2021`
UNION ALL
SELECT * 
FROM `cyclisticjan21-dec21.Bike_share.Sep2021`
UNION ALL
SELECT *
FROM `cyclisticjan21-dec21.Bike_share.Oct2021`
UNION ALL
SELECT * 
FROM `cyclisticjan21-dec21.Bike_share.Nov2021`
UNION ALL
SELECT *
FROM `cyclisticjan21-dec21.Bike_share.Dec2021`)

错误原因 #

BigQuery中表名不能用双引号包裹，双引号的语法不符合BigQuery的表名定义规范。正确的做法是用反引号包裹表名，若表名无特殊字符、空格，直接写表名也可。

修正后的SQL语句 #

CREATE TABLE `bikeshare_total` AS (
SELECT *
FROM `cyclisticjan21-dec21.Bike_share.Jan2021`
UNION ALL
SELECT * 
FROM `cyclisticjan21-dec21.Bike_share.Feb2021`
UNION ALL
SELECT *
FROM `cyclisticjan21-dec21.Bike_share.March2021`
UNION ALL
SELECT *
FROM `cyclisticjan21-dec21.Bike_share.April2021`
UNION ALL
SELECT * 
FROM `cyclisticjan21-dec21.Bike_share.May2021`
UNION ALL
SELECT *
FROM `cyclisticjan21-dec21.Bike_share.June2021`
UNION ALL
SELECT * 
FROM `cyclisticjan21-dec21.Bike_share.July2021`
UNION ALL
SELECT *
FROM `cyclisticjan21-dec21.Bike_share.August2021`
UNION ALL
SELECT * 
FROM `cyclisticjan21-dec21.Bike_share.Sep2021`
UNION ALL
SELECT *
FROM `cyclisticjan21-dec21.Bike_share.Oct2021`
UNION ALL
SELECT * 
FROM `cyclisticjan21-dec21.Bike_share.Nov2021`
UNION ALL
SELECT *
FROM `cyclisticjan21-dec21.Bike_share.Dec2021`)

若要指定目标数据集（比如将新表存入cyclisticjan21-dec21.Bike_share），可写成：

CREATE TABLE `cyclisticjan21-dec21.Bike_share.bikeshare_total` AS (
-- 后续SELECT语句同上
)

优化建议 #

如果12个表结构完全一致，可用通配符简化查询，避免重复写UNION ALL：

CREATE TABLE `bikeshare_total` AS (
SELECT *
FROM `cyclisticjan21-dec21.Bike_share.*2021`
)

此写法要求所有目标表命名符合*2021匹配规则，且表结构完全相同。

内容的提问来源于stack exchange，提问作者Yasmin Fakhruddin