BigQuery能否单次读取GCS外部表写入多表降低查询成本？

完全可以避免重复读取GCS外部表产生的额外成本，通过BigQuery原生能力即可实现单次源表扫描完成8张Native表的写入，不需要额外引入第三方组件。

原示例SQL存在语法错误：GROUP BY sumagg、GROUP BY meancol5写法不符合SQL规范，GROUP BY后必须指定维度字段，不能写聚合结果的别名，直接执行会报错，下方示例代码已经修正该问题。

BigQuery支持会话级临时表，表数据存储在BigQuery原生存储中，会话结束后自动删除，无长期存储成本。实现逻辑是先一次性完成公共过滤逻辑，将需要用到的源数据从GCS读到临时表，这个步骤仅触发一次GCS外部表扫描；后续8个聚合写入操作全部基于本地临时表计算，不再访问GCS源数据，且原生表的扫描单价远低于GCS外部表扫描单价，整体成本和性能都远优于原写法。

示例代码如下：

-- 仅扫描一次GCS外部表，完成公共过滤逻辑写入临时表
CREATE TEMP TABLE temp_filtered_source
AS
SELECT * FROM ds.External_Table
WHERE CAST(SUBSTR(_FILE_NAME,43,12) AS INT64) > 123456;

-- 后续所有写入操作均基于临时表计算，不再重复读取GCS数据
INSERT INTO ds.Native_Cube1 (col1,col2, sumagg) 
SELECT col1,col2,sum(col25) as sumagg 
FROM temp_filtered_source 
GROUP BY col1,col2;

INSERT INTO ds.Native_Cube2 (col1,col2, col3, meancol5) 
SELECT col1,col2,col3,avg(col5) as meancol5 
FROM temp_filtered_source 
WHERE col3='http' 
GROUP BY col1,col2,col3;

-- 剩余6条INSERT语句逻辑同上，替换为对应业务的过滤、聚合、字段映射规则即可

• 方案优势：逻辑简单易维护，不受不同Cube聚合粒度差异影响，稳定性最高。如果公共过滤后的数据量远小于原始300GB，后续聚合计算速度会比直接查询外部表快3~10倍。

如果8张目标表的聚合维度重合度较高，可以使用BigQuery原生的多表插入语法，将公共过滤逻辑封装为CTE，BigQuery查询优化器会自动识别公共数据源，仅触发一次外部表扫描，通过行级路由将计算结果写入不同目标表，全程没有临时表存储开销。

示例代码如下：

INSERT
  -- 路由规则：所有符合基础过滤条件的数据都参与Cube1计算
  WHEN TRUE THEN INTO ds.Native_Cube1 (col1,col2, sumagg)
  -- 路由规则：col3=http的数据参与Cube2计算
  WHEN col3 = 'http' THEN INTO ds.Native_Cube2 (col1,col2, col3, meancol5)
  -- 其余6张表按业务过滤条件追加WHEN路由规则即可
WITH filtered_source AS (
  SELECT 
    col1,
    col2,
    col3,
    col5,
    col25,
    -- 用窗口函数提前计算所有Cube需要的聚合值
    sum(col25) OVER (PARTITION BY col1,col2) as sumagg,
    avg(col5) OVER (PARTITION BY col1,col2,col3) as meancol5
  FROM ds.External_Table
  WHERE CAST(SUBSTR(_FILE_NAME,43,12) AS INT64) > 123456
)
SELECT * FROM filtered_source;

• 注意事项：该方案需要通过窗口函数提前计算所有表需要的聚合指标，如果8张表的聚合维度完全不相关，窗口函数的计算开销会高于临时表方案，这类场景优先选择方案1。

原8条独立INSERT的写法会触发8次独立的GCS外部表扫描，总扫描量为8*300GB=2.4TB；使用上述两种方案后，GCS源表扫描量最高为300GB，如果公共过滤条件能过滤掉部分数据，实际扫描量会更低，至少能降低75%的源数据扫描成本。

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