BigQuery查询无限运行问题求助及已尝试方案说明

涉及SQL语句 #

WITH A AS (
  SELECT id FROM db1.X AS d
  WHERE DATE(d.date) BETWEEN DATE_SUB(current_date(), INTERVAL 7 DAY) AND current_date()
),
B AS (
  SELECT id
  FROM db2.Y as t
  WHERE
    t.start <= TIMESTAMP(DATE_SUB(current_date(), INTERVAL 7 DAY))
    AND t.end >= TIMESTAMP(current_date())
)

SELECT * FROM A as d JOIN B as t on d.id = t.id;

表信息 #

• db1.X：基于大表的物化视图，包含16亿行数据
• db2.Y：数据源为Google Sheet的表，包含1.5万行数据

问题描述 #

该查询当前会无限运行，曾手动终止运行1小时的进程，另有一次运行6小时后超时且无错误提示。1月2日前查询可正常执行，1月9日起出现异常。两张表均为自动填充，推测可能触发了未明确的阈值，另有3个使用相同表的查询也出现相同问题。

已尝试方案 #

• 将关联逻辑改为WHERE IN语句，问题依旧
• 单独操作B表（如SELECT count(*) from B）可正常完成，但只要涉及A表的操作均无法结束，除非移除B表的定义
• 不使用子查询直接关联，问题仍存在
• 确认A的结果集为1060万行，B的结果集仅31行，关联后问题依旧
• 原查询未使用子查询，直接在关联时做日期过滤，改为先过滤再关联的子查询结构后仍无改善
• 尝试使用JOIN EACH，始终报语法错误，调整后仍提示Expected end of input but got "."

解决建议 #

1. 检查物化视图状态
   物化视图可能未自动刷新，导致查询时需扫描底层16亿行的原始表。手动触发刷新：

   REFRESH MATERIALIZED VIEW db1.X;
   

   刷新完成后重新执行查询，确认是否恢复正常。

2. 优化日期过滤条件，避免字段上的函数调用
   原查询中DATE(d.date)会阻止BigQuery使用date字段的分区或索引，根据date字段的实际类型调整过滤逻辑：

   • 若d.date为DATE类型：

     WHERE d.date BETWEEN DATE_SUB(current_date(), INTERVAL 7 DAY) AND current_date()
     

   • 若d.date为TIMESTAMP类型：

     WHERE d.date >= TIMESTAMP(DATE_SUB(current_date(), INTERVAL 7 DAY)) 
       AND d.date < TIMESTAMP(DATE_ADD(current_date(), INTERVAL 1 DAY))
     

3. 调整关联顺序，用小表驱动大表
   B表结果仅31行，将关联顺序改为B JOIN A，让BigQuery把小表广播到所有节点，再过滤大表数据，大幅减少扫描量：

   WITH A AS (
     SELECT id FROM db1.X AS d
     WHERE d.date BETWEEN DATE_SUB(current_date(), INTERVAL 7 DAY) AND current_date()
   ),
   B AS (
     SELECT id
     FROM db2.Y as t
     WHERE
       t.start <= TIMESTAMP(DATE_SUB(current_date(), INTERVAL 7 DAY))
       AND t.end >= TIMESTAMP(current_date())
   )
   
   SELECT * FROM B as t JOIN A as d on t.id = d.id;
   

4. 确保关联字段类型一致
   检查A表和B表的id字段数据类型是否匹配（如INT64 vs STRING），类型不匹配会触发隐式转换，导致性能骤降。若类型不同，在B表查询中显式转换：

   SELECT CAST(id AS INT64) id FROM db2.Y as t ...
   

5. 将小表结果存入临时表
   先把B表的过滤结果存入临时表，避免每次关联都重新读取Google Sheet数据：

   CREATE TEMP TABLE B_temp AS
   SELECT id
   FROM db2.Y as t
   WHERE
     t.start <= TIMESTAMP(DATE_SUB(current_date(), INTERVAL 7 DAY))
     AND t.end >= TIMESTAMP(current_date());
   
   SELECT * FROM db1.X d
   JOIN B_temp t ON d.id = t.id
   WHERE d.date BETWEEN DATE_SUB(current_date(), INTERVAL 7 DAY) AND current_date();
   

6. 查看执行计划定位瓶颈
   在BigQuery控制台打开查询的执行计划，检查是否存在全表扫描、数据倾斜等问题，针对性优化。

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