Google BigQuery中3张一对多大表合并为单表的性能问题规避咨询

• 关联前先做数据裁剪
  不要直接全表关联，先筛掉所有不需要的字段和行：仅保留Tableau报表实际用到的字段；如果表有分区/分簇属性，先用WHERE子句命中分区键（比如常用的日期字段）过滤掉非必要的历史数据，减少扫描的数据量。
• 优化关联顺序与执行策略
  一对多关联时，将最小的1000万行事实表放在关联最左侧，作为左表。如果事实表大小低于10GB，可以加广播JoinHint，把小表广播到所有计算节点，避免大规模数据Shuffle，示例语法：

  SELECT /*+ BROADCAST(fact_table) */ 
    -- 只选需要的字段，不要写*
  FROM fact_table
  JOIN dim_table1 ON fact_table.key1 = dim_table1.key1
  JOIN dim_table2 ON fact_table.key2 = dim_table2.key2
  

• 提前处理维度表重复值，避免数据爆炸
  一对多关联最容易出现维度表同一关联键对应多条数据，导致关联后行数指数级上涨。关联前先给维度表做关联键去重，保留业务需要的唯一行，示例语法：

  -- 取每个关联键最新的一条维度数据
  WITH cleaned_dim AS (
    SELECT * FROM dim_table
    QUALIFY ROW_NUMBER() OVER(PARTITION BY join_key ORDER BY update_time DESC) = 1
  )
  SELECT * FROM fact_table JOIN cleaned_dim ON fact_table.join_key = cleaned_dim.join_key
  

  如果是缓慢变化维，关联时必须加上时间区间匹配条件，避免关联出冗余的历史版本数据。
• 结果表预优化
  最终生成的宽表按照Tableau高频查询的过滤字段设置分区（比如按交易日期日分区），按高频维度字段设置分簇（比如区域、用户ID），同时开启BigQuery查询缓存，定期调度刷新宽表，无需每次查询实时关联。

• 优先使用同区域实时连接：如果Tableau服务部署在和BigQuery相同的GCP区域，优先用实时连接，无需全量拉取数据到本地；如果必须做本地Extract，仅抽取需要的时间范围数据，同时隐藏所有未使用的字段，降低Extract文件大小。
• 增量刷新Extract：设置Extract按分区字段做增量刷新，每次仅同步最新分区的数据，不要每次全量重拉。
• 灵活使用物化视图：如果后续关联逻辑可能调整，可以把关联逻辑封装为BigQuery视图，开启物化视图定期自动刷新，Tableau直接对接物化视图即可，调整逻辑时仅需要修改视图定义，无需重新跑全量关联任务。

• 提前统一关联键数据类型：避免出现一个是INT64一个是STRING的情况，隐式类型转换不仅会拖慢查询速度，还可能出现关联匹配失败的问题。
• 资源调度优化：大表关联任务尽量选业务低峰期提交，避免和其他任务抢资源；如果这类任务频次高，可以申请预留槽位，比按需计费的资源更稳定，不会出现任务排队延迟。
• 关联后数据校验：关联完成后，统计事实表的可加指标（比如总金额、总订单数）和宽表的对应指标做对比，如果出现指标翻倍的情况，说明关联逻辑有问题，存在重复关联的情况，需要重新核对关联键和维度表去重逻辑。

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