BigQuery中Inner Join执行超时问题求助

执行以下Inner Join查询时出现超时，运行6小时仍未完成：

SELECT
a.*,
b.total_video,
b.total_initial_video,
b.total_discovery,
b.total_sub_video,
b.total_sub_initial_video,
b.total_sub_discovery,
FROM  temp_table.source_stage_ a 
INNER JOIN temp_table.source_stage2_ b
ON (a.day = b.day
AND a.state_desc = b.state_desc
AND a.package_desc = b.package_desc
AND a.brand = b.v1_brand
))

临时表创建语句 #

temp_table.source_stage_的创建语句 #

create or replace table temp_table.source_stage_ as (

select *,
case
when cycle = 0
then 'Trial'
when cycle = 1
then '1 Month'
when cycle between 2 and 3
then '2 - 3 Months'
when cycle between 4 and 5
then '4 - 5 Months'
when cycle >= 6
then '6+ Months'
end as group,
from(
select
  *,
  case when active_days < 0 then 0 else cast( ceil( ( case when active_days < 1 then 1 else active_days end ) / 30 ) as int64 ) end cycle, from (
SELECT
t.activation_dt,
        t.end_dt,
        t.trial_start_dt,
        t.start_dt,
        date_diff(p.day_dt,coalesce(activation_dt,trial_start_dt,start_dt),DAY) as active_days,
       date_diff(p.day_dt,coalesce(start_dt),DAY) as active_days,
p.day_dt as day,
state_desc,
package_desc,
v1_brand as brand,
sc.scarp_group,
p.last_touch,
SUM(p.video_playback_ind) as video_start_cnt,
SUM(CASE WHEN cd_path_rank_nbr = 1 THEN p.video_playback_ind ELSE 0 END) as initial_video_start_cnt,
SUM(CASE WHEN p.video_playback_ind = 1 AND f.session_nbr IS NOT NULL THEN 1 ELSE 0 END) as discovery_start_cnt,
COUNT(DISTINCT(p.registration_id_nbr)) as sub_video_start_cnt,
COUNT(DISTINCT(CASE WHEN cd_path_rank_nbr = 1 THEN p.registration_id_nbr ELSE NULL END)) as sub_initial_video_start_cnt,
COUNT(DISTINCT(CASE WHEN f.session_nbr IS NOT NULL THEN p.registration_id_nbr ELSE NULL END)) as sub_discovery_start_cnt,
FROM table1 p
LEFT JOIN table2 f
ON p.day_dt = f.day_dt
AND p.session_nbr = f.session_nbr
AND f.day_dt = '2023-02-11'
LEFT JOIN table3 t
on  (p.registration_id_nbr = t.user_id_cd
and p.day_dt between t.activation_dt and t.end_dt)
left join table4 as sc
on  (p.registration_id_nbr is not null
      and sc.user_id_cd is not null
      and p.registration_id_nbr = sc.user_id_cd
      and p.day_dt between sc.period_start_dt and sc.period_end_dt)
WHERE p.day_dt  = '2023-02-11'
AND p.video_playback_ind = 1
AND p.report_suite_id NOT IN ('cb', 'ic', 'bs')
AND LOWER(p.state_desc) IN ("tr", "s", "di of")
AND lower(package_desc) IN ('cf', 'lc', 'lcp', 'cf-s', 'lc-s', 'lcp-s')
AND v1_brand LIKE 'pp_%'
GROUP BY 1,2,3,4,5,6,7,8,9,10,11,12
)))

temp_table.source_stage2_的创建语句 #

create or replace table temp_table.source_stage2 as (

select *,
case
when tenure_cycle = 0
then 'Trial'
when tenure_cycle = 1
then '1 Month'
when tenure_cycle between 2 and 3
then '2 - 3 Months'
when tenure_cycle between 4 and 5
then '4 - 5 Months'
when tenure_cycle >= 6
then '6+ Months'
end as tenure_group,
from(
select
  *,
  case when paid_active_days < 0 then 0 else cast( ceil( ( case when paid_active_days < 1 then 1 else paid_active_days end ) / 30 ) as int64 ) end tenure_cycle, from (
SELECT
t.activation_dt,
        t.end_dt,
        t.trial_start_dt,
        t.paid_start_dt,
        date_diff(p.day_dt,coalesce(activation_dt,trial_start_dt,paid_start_dt),DAY) as active_days,
       date_diff(p.day_dt,coalesce(paid_start_dt),DAY) as paid_active_days,
p.day_dt as day,
state_desc,
package_desc,
v1_brand_nm as v1_brand,
sc.scarp_group,
SUM(p.video_playback_ind) as total_video,
SUM(CASE WHEN cd_path_rank_nbr = 1 THEN p.video_playback_ind ELSE 0 END) as total_initial_video,
SUM(CASE WHEN p.video_playback_ind = 1 AND f.v88_player_session_nbr IS NOT NULL THEN 1 ELSE 0 END) as total_discovery,
COUNT(DISTINCT(p.v69_registration_id_nbr)) as total_sub_video,
COUNT(DISTINCT(CASE WHEN cd_path_rank_nbr = 1 THEN p.v69_registration_id_nbr ELSE NULL END)) as total_sub_initial_video,
COUNT(DISTINCT(CASE WHEN f.v88_player_session_nbr IS NOT NULL THEN p.v69_registration_id_nbr ELSE NULL END)) as total_sub_discovery,
FROM `table1` p
LEFT JOIN `table2` f
ON p.day_dt = f.day_dt
AND p.v88_player_session_nbr = f.v88_player_session_nbr
AND f.day_dt  = '2023-02-11' 
LEFT JOIN `table3` t
on  (p.registration_id_nbr = t.cbs_reg_user_id_cd
and p.day_dt between t.activation_dt and t.end_dt)
left join `table4` as sc
on  (p.registration_id_nbr is not null
      and reg_user_id_cd is not null
      and p.registration_id_nbr = reg_user_id_cd
      and p.day_dt between sc.period_start_dt and sc.period_end_dt)
WHERE p.day_dt  = '2023-02-11'
AND p.video_playback_ind = 1
AND p.report_suite_id NOT IN ('cb', 'ic', 'bs')
AND LOWER(p.state_desc) IN ("tr", "s", "di of")
AND lower(package_desc) IN ('cf', 'lc', 'lcp', 'cf-s', 'lc-s', 'lcp-s')
AND v1_brand_nm LIKE 'pp_%'
GROUP BY 1,2,3,4,5,6,7,8,9,10,11  
  )))

超时问题优化方案 #

• 为临时表添加聚簇键：在创建两个临时表时，通过CLUSTER BY指定Join字段作为聚簇键，让BigQuery按这些字段排序存储数据，减少Join时的数据扫描量。

  • 修改source_stage_的创建语句，在末尾添加：CLUSTER BY day, state_desc, package_desc, brand
  • 修改source_stage2_的创建语句，在末尾添加：CLUSTER BY day, state_desc, package_desc, v1_brand

• 消除临时表冗余计算：

  • source_stage_中重复定义了active_days字段，删除其中一条date_diff(p.day_dt,coalesce(start_dt),DAY) as active_days语句，避免重复计算
  • 简化cycle和tenure_cycle的计算逻辑，比如将嵌套case改写为：cast(ceil(greatest(active_days, 1)/30) as int64)（若active_days<0则用greatest(active_days, 0)），降低计算复杂度

• 合并计算逻辑，避免二次Join：两个临时表的基础过滤条件和关联逻辑高度一致，可以将两个表的指标计算合并到一个查询中，直接生成最终需要的结果，省去先创建两个临时表再做Join的步骤。

• 排查数据倾斜：检查Join键（如state_desc、package_desc）是否存在某类值的记录数远高于其他值的情况。如果存在数据倾斜，拆分查询：将倾斜键对应的记录单独提取，用小表驱动大表做Join，再与其他正常结果合并。

• 调整作业运行参数：

  • 启用查询结果缓存，若临时表数据不会频繁变更，后续查询可直接复用缓存结果
  • 使用批处理模式运行查询，BigQuery会为批处理作业分配更多计算资源，提升处理速度
  • 检查作业的最大字节数限制，若当前设置过低，适当调高以支持大数量级的Join操作

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