时间戳

首页 > 解决方案 > 在 WHERE 子句中使用子查询进行 Google BigQuery 优化

google-bigquery - 在 WHERE 子句中使用子查询进行 Google BigQuery 优化

问题描述

我正在尝试设置一个查询，该查询从 Google Analytics 会话数据的一系列日常分区中选择数据子集，并将数据写入 Google BigQuery 临时表。我面临的挑战是在 WHERE 子句中使用子查询时降低处理成本。

来自查询的 Google Analytics（分析）数据将在处理并加载到目标数据表（my-data-table）之前附加到临时表。主要查询以以下两种形式给出。第一个是硬编码。第二个反映了首选形式。_TABLE_SUFFIX 的上限是硬编码的，以简化查询。目标是使用来自 my-data-table 的 MAX(date)，其中 date 的格式为 YYYYMMDD，作为 ga_sessions_* 每日分区的下限。该查询已在此处进行了简化，但据信包含所有必要的元素。

聚合查询 (SELECT MAX(date) FROM my-project-12345.dataset.my-data-table) 返回值“20201015”并处理 202 KB。根据我是在主查询的 WHERE 子句中显式使用返回值（如“20201015”）还是在 WHERE 子句中使用 SELECT MAX() 查询，两个查询之间处理的数据存在显着差异（2.3 GB 用于显式值，而 138.1 GB 用于 SELECT MAX() 表达式）。

是否有可以应用于主查询的首选形式的优化、计划或指令，以降低数据处理成本？感谢您提供的任何帮助。

主查询（硬编码版本，处理 2.3 GB）

SELECT
  GA.date, 
  GA.field1, 
  hits.field2, 
  hits.field3
FROM 
  `my-project-12345.dataset.ga_sessions_*` AS GA, UNNEST(GA.hits) AS hits
WHERE 
  hits.type IN ('PAGE', 'EVENT')
  AND hits.field0 = 'some value'
  AND _TABLE_SUFFIX > '20201015'
  AND _TABLE_SUFFIX < '20201025'

主查询（首选形式，处理 138.1 GB 未优化）

SELECT
  GA.date, 
  GA.field1, 
  hits.field2, 
  hits.field3
FROM 
  `my-project-12345.dataset.ga_sessions_*` AS GA, UNNEST(GA.hits) AS hits
WHERE 
  hits.type IN ('PAGE', 'EVENT')
  AND hits.field0 = 'some value'
  AND _TABLE_SUFFIX > (SELECT MAX(date) FROM `my-project-12345.dataset.my-data-table`)
  AND _TABLE_SUFFIX < '20201025'

标签： google-bigquery

解决方案

您可以为此使用脚本

“诀窍”在于预计算

DECLARE start_date STRING;
SET start_date = (SELECT MAX(date) FROM `my-project-12345.dataset.my-data-table`);

并分配给变量，然后在主查询的 where 子句中使用此变量 - 在这种情况下，它将使用具有成本效益的版本

AND _TABLE_SUFFIX > start_date
AND _TABLE_SUFFIX < '20201025'

推荐阅读