如何在BigQuery中通过INFORMATION_SCHEMA.TABLES获表名后直接取数据？

单个目标表的情况 #

如果只需要查询单个日期对应的表，可以用变量存储表名，再通过EXECUTE IMMEDIATE动态执行查询：

DECLARE target_table STRING;

-- 从INFORMATION_SCHEMA获取目标表的完整路径
SET target_table = (
  SELECT CONCAT('bigquery-public-data.google_analytics_sample.', table_name)
  FROM bigquery-public-data.google_analytics_sample.INFORMATION_SCHEMA.TABLES
  WHERE table_name = CONCAT('ga_sessions_', '20170801')
);

-- 动态执行查询，获取表内数据
EXECUTE IMMEDIATE FORMAT("""
  SELECT * FROM `%s`
""", target_table);

说明 #

1. 用DECLARE定义变量存储表的完整路径；
2. 通过SET语句从INFORMATION_SCHEMA中查询并赋值目标表名；
3. 用EXECUTE IMMEDIATE配合FORMAT函数拼接SQL语句，反引号``包裹表名是为了避免路径中的点号引发语法错误。

多个目标表的情况 #

如果需要批量查询符合日期规则的多个表（比如某月份的所有会话表），可以用STRING_AGG拼接UNION ALL语句：

DECLARE batch_query STRING;

-- 构造包含所有目标表的联合查询语句
SET batch_query = (
  SELECT STRING_AGG(
    FORMAT("SELECT * FROM `bigquery-public-data.google_analytics_sample.%s`", table_name),
    " UNION ALL "
  )
  FROM bigquery-public-data.google_analytics_sample.INFORMATION_SCHEMA.TABLES
  WHERE table_name LIKE 'ga_sessions_201708%' -- 筛选2017年8月的所有表
);

-- 执行批量查询
EXECUTE IMMEDIATE batch_query;

说明 #

STRING_AGG会把每个表的查询语句用UNION ALL连接起来，最终生成一个包含所有目标表数据的联合查询，再通过EXECUTE IMMEDIATE执行。

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