时间戳

首页 > 解决方案 > 对 Google Bigquery 中的嵌套字段使用 OFFSET 而不是 UNNEST

google-bigquery - 对 Google Bigquery 中的嵌套字段使用 OFFSET 而不是 UNNEST

问题描述

对 GBQ 大师的快速提问。

这是两个目的相同的查询

first

SELECT
  fullVisitorId AS userid,
  CONCAT(fullVisitorId, visitStartTime) AS session,
  visitStartTime + (hits[
  OFFSET(0)].time / 1000) AS eventtime,
  date,
  trafficSource.campaign,
  trafficSource.source,
  trafficSource.medium,
  trafficSource.adContent,
  trafficSource.adwordsClickInfo.campaignId,
  geoNetwork.region,
  geoNetwork.city,
  trafficSource.keyword,
  totals.visits AS visits,
  device.deviceCategory AS deviceType,
  hits[OFFSET(0)].eventInfo.eventAction,
  hits[OFFSET(0)].TRANSACTION.transactionId,
  hits[OFFSET(0)].TRANSACTION.transactionRevenue,
  SUBSTR(channelGrouping,0,3) AS newchannelGrouping
FROM
  `some_site.ga_sessions_*`
WHERE
  ARRAY_LENGTH(hits) > 0
  AND _table_suffix BETWEEN '20200201'
  AND '20200201'
  AND fullVisitorId IN (
  SELECT
    DISTINCT(fullVisitorId)
  FROM
    `some_site.ga_sessions_*`,
    UNNEST(hits) AS hits
  WHERE
    _table_suffix BETWEEN '20200201'
    AND '20200201'
    AND (hits.TRANSACTION.transactionId != 'None')
)

second

SELECT
  fullVisitorId AS userid,
  CONCAT(fullVisitorId, visitStartTime) AS session,
  visitStartTime + (hits.time / 1000) AS eventtime,
  date,
  trafficSource.campaign,
  trafficSource.source,
  trafficSource.medium,
  trafficSource.adContent,
  trafficSource.adwordsClickInfo.campaignId,
  geoNetwork.region,
  geoNetwork.city,
  trafficSource.keyword,
  totals.visits AS visits,
  device.deviceCategory AS deviceType,
  hits.eventInfo.eventAction,
  hits.TRANSACTION.transactionId,
  hits.TRANSACTION.transactionRevenue,
  SUBSTR(channelGrouping,0,3) AS newchannelGrouping
FROM
  `some_site.ga_sessions_*`, UNNEST(hits) hits
WHERE
  _table_suffix BETWEEN '20200201' AND '20200201'
  AND fullVisitorId IN (
    SELECT
      DISTINCT(fullVisitorId)
    FROM
      `some_site.ga_sessions_*`,
      UNNEST(hits) AS hits
    WHERE
      _table_suffix BETWEEN '20200201'
      AND '20200201'
      AND (hits.TRANSACTION.transactionId != 'None')
  )

第一个用于OFFSET从嵌套字段中提取数据。根据执行详细信息报告，查询需要大约 1.5 MB 的 shuffle。

在此处输入图像描述

第二个查询用于UNNEST访问嵌套数据。并且混洗字节的数量约为（！）75 MB

在此处输入图像描述

在这两种情况下，处理的数据量是相同的。

现在，问题是：

这是否意味着根据这篇关于优化插槽之间通信的文章，我应该使用OFFSET而不是UNNEST获取存储在嵌套字段中的数据？

谢谢！

标签： google-bigqueryoffsetunnest

解决方案

让我们考虑使用 BigQuery 公共数据集的以下示例。

UNNEST - 返回 6 个结果：

WITH t AS (SELECT * FROM `bigquery-public-data.google_analytics_sample.ga_sessions_20170801` WHERE visitId = 1501571504 )
SELECT h FROM t, UNNEST(hits) h

OFFSET - 返回 1 个结果：

WITH t AS (SELECT * FROM `bigquery-public-data.google_analytics_sample.ga_sessions_20170801` WHERE visitId = 1501571504 )
SELECT hits[OFFSET(0)] FROM t

两个查询都引用 GA 公共表中的同一记录。他们表明，使用 UNNEST 连接将在数组中为每个元素带来一行，而使用 OFFSET(0) 将仅在数组的第一个元素中带来一行。

高数据shuffle不同的原因是因为UNNEST执行了JOIN操作，需要以特定的方式组织数据。OFFSET 方法仅采用数组的第一个元素。

推荐阅读