如何在BigQuery表后缀查询中排除视图实现多表联合查询

问题根源 #

使用my_dataset.*通配符查询时，BigQuery会先枚举数据集下所有匹配的对象（包括视图），即便后续用_TABLE_SUFFIX过滤，也会因视图无法通过前缀查询触发报错，因此必须先排除视图再执行联合查询。

方法一：动态生成SQL查询（推荐） #

利用BigQuery元数据视图INFORMATION_SCHEMA.TABLES筛选所有实体表，动态拼接UNION ALL语句执行，自动适配新增表：

DECLARE sql STRING;
SET sql = (
  SELECT STRING_AGG(
    CONCAT('SELECT a, b, c FROM `my_dataset.', table_name, '`'),
    ' UNION ALL '
  )
  FROM `my_dataset.INFORMATION_SCHEMA.TABLES`
  WHERE table_type = 'BASE TABLE' -- 仅选择实体表，排除视图
    AND table_name NOT LIKE '%_view' -- 匹配命名规则双重过滤
);
EXECUTE IMMEDIATE sql;

方法二：预定义统一视图 #

若需频繁查询，可先创建包含所有目标表的视图，后续直接查询该视图：

1. 运行以下SQL生成视图定义：

SELECT CONCAT(
  'CREATE OR REPLACE VIEW `my_dataset.all_events_union` AS ',
  STRING_AGG(
    CONCAT('SELECT a, b, c FROM `my_dataset.', table_name, '`'),
    ' UNION ALL '
  )
)
FROM `my_dataset.INFORMATION_SCHEMA.TABLES`
WHERE table_type = 'BASE TABLE'
  AND table_name NOT LIKE '%_view';

2. 复制输出的SQL语句执行，创建视图all_events_union
3. 后续直接查询：

SELECT a, b, c FROM `my_dataset.all_events_union`;

注意：新增表后需重新生成视图定义更新视图。

原SQL的语法修正（仅修正语法，无法解决根本问题） #

你原SQL存在引号未闭合的错误，正确写法为：

SELECT a,b,c
FROM `my_dataset`.*
WHERE _TABLE_SUFFIX NOT LIKE "%_view"

但该语句仍会触发视图查询报错，仅可作为辅助过滤规则，无法单独解决问题。

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