SQL中无需UNION，批量将12个月度表插入同一大表的方法？

问题背景 #

我有12个对应不同月份的数据表，需要合并到一个统一的大表中。已经创建好目标表：

CREATE TABLE `data_base.data_set.new_data_table`
(
    ride_id STRING, 
    rideable_type STRING, 
    started_at TIMESTAMP, 
    ended_at TIMESTAMP, 
    start_lat FLOAT64, 
    start_lng FLOAT64, 
    end_lat FLOAT64, 
    end_lng FLOAT64, 
    member_casual STRING, 
    start_station_name STRING, 
    end_station_name STRING
);

并且完成了首个月度表data_base.data_set.tab_2021-09的插入：

INSERT INTO `data_base.data_set.new_data_table` 
  (ride_id, rideable_type, started_at, ended_at, start_lat, start_lng, end_lat, end_lng, member_casual, start_station_name, end_station_name) 

(SELECT ride_id, rideable_type, started_at, ended_at, start_lat, start_lng, end_lat, end_lng, member_casual, start_station_name, end_station_name 
FROM 
  `data_base.data_set.tab_2021-09`)

现在需要处理tab_2021-10至tab_2022-07的11个表，不想重复写带UNION的SELECT语句，找批量遍历插入的方法。

解决方案 #

方法1：用BigQuery通配符表（最省事） #

BigQuery支持通配符表，直接用*匹配表名前缀，再通过_TABLE_SUFFIX筛选目标月份，一次性完成所有插入：

INSERT INTO `data_base.data_set.new_data_table` 
  (ride_id, rideable_type, started_at, ended_at, start_lat, start_lng, end_lat, end_lng, member_casual, start_station_name, end_station_name)
SELECT 
  ride_id, rideable_type, started_at, ended_at, start_lat, start_lng, end_lat, end_lng, member_casual, start_station_name, end_station_name
FROM 
  `data_base.data_set.tab_*`
WHERE 
  _TABLE_SUFFIX BETWEEN '2021-10' AND '2022-07'

这种方法不需要手动列举每个表，自动匹配符合后缀范围的所有表，适合表名规则统一的场景。

方法2：生成动态SQL脚本（灵活可控） #

如果需要更精细的控制（比如跳过某些表、添加额外过滤条件），可以用BigQuery的元数据生成动态INSERT语句：

1. 先查询获取目标表列表：

SELECT 
  CONCAT(
    'INSERT INTO `data_base.data_set.new_data_table` (ride_id, rideable_type, started_at, ended_at, start_lat, start_lng, end_lat, end_lng, member_casual, start_station_name, end_station_name) SELECT * FROM `data_base.data_set.', table_name, '`;'
  ) AS insert_query
FROM 
  `data_base.data_set.INFORMATION_SCHEMA.TABLES`
WHERE 
  table_name LIKE 'tab_2021-10' OR 
  table_name BETWEEN 'tab_2021-11' AND 'tab_2022-07'

2. 把查询结果里的insert_query列复制出来，批量执行这些SQL语句即可。

如果想直接在BigQuery里执行动态SQL，也可以用EXECUTE IMMEDIATE结合字符串拼接：

DECLARE sql STRING;
SET sql = (
  SELECT STRING_AGG(
    CONCAT('SELECT * FROM `data_base.data_set.', table_name, '`'),
    ' UNION ALL '
  )
  FROM `data_base.data_set.INFORMATION_SCHEMA.TABLES`
  WHERE table_name LIKE 'tab_2021-10' OR table_name BETWEEN 'tab_2021-11' AND 'tab_2022-07'
);

SET sql = CONCAT(
  'INSERT INTO `data_base.data_set.new_data_table` (ride_id, rideable_type, started_at, ended_at, start_lat, start_lng, end_lat, end_lng, member_casual, start_station_name, end_station_name) ',
  sql
);

EXECUTE IMMEDIATE sql;

方法3：用Python脚本批量执行 #

如果习惯用代码控制，可以写个简单的Python脚本遍历月份列表，逐个执行插入：

from google.cloud import bigquery

client = bigquery.Client()
project_id = "your-project-id"
dataset_id = "data_set"
target_table = f"{project_id}.{dataset_id}.new_data_table"

# 定义需要处理的月份列表
months = [
    "2021-10", "2021-11", "2021-12",
    "2022-01", "2022-02", "2022-03",
    "2022-04", "2022-05", "2022-06", "2022-07"
]

for month in months:
    source_table = f"{project_id}.{dataset_id}.tab_{month}"
    query = f"""
    INSERT INTO `{target_table}` 
      (ride_id, rideable_type, started_at, ended_at, start_lat, start_lng, end_lat, end_lng, member_casual, start_station_name, end_station_name)
    SELECT 
      ride_id, rideable_type, started_at, ended_at, start_lat, start_lng, end_lat, end_lng, member_casual, start_station_name, end_station_name
    FROM `{source_table}`
    """
    job = client.query(query)
    job.result()  # 等待执行完成
    print(f"完成插入表：{source_table}")

执行前需要安装google-cloud-bigquery库，并且配置好BigQuery认证。

内容的提问来源于stack exchange，提问作者Torin Harte