sql - 在 Big Query 中通过一系列表从 Google Analytics 获取扁平化命中数据

Question

以下是一个查询，用于展平从 Google Analytics 传输到 BigQuery 的“命中”结果:

SELECT
*
FROM flatten(flatten(flatten(flatten(flatten(flatten(flatten(flatten(flatten([ga_sessions_20171116], hits), hits.product), hits.product.customDimensions), hits.product.customMetrics), hits.promotion), hits.experiment), hits.customDimensions), hits.customVariables), hits.customMetrics)
Limit 20

一个人如何在一系列表格中做同样的事情，或者这是否可能？我试过:

SELECT
*
FROM flatten(flatten(flatten(flatten(flatten(flatten(flatten(flatten(flatten([ga_sessions_2017111*], hits), hits.product), hits.product.customDimensions), hits.product.customMetrics), hits.promotion), hits.experiment), hits.customDimensions), hits.customVariables), hits.customMetrics)
WHERE _TABLE_SUFFIX BETWEEN '0' and '10'
Limit 20

但它没有用。有谁知道如何做到这一点？

Answer 1

使用 Standard SQL 中的嵌套数据比在 Legacy 中要容易得多(因为查询语法和行为的可预测性)。

话虽如此，请考虑使用它。您在标准中的查询变为:

SELECT
  fullvisitorid visitor_id,
  prods.productSku sku,
  custd.index index
FROM `project_id.dataset_id.ga_sessions_*`,
UNNEST(hits) hits,
UNNEST(hits.product) prods,
UNNEST(prods.customDimensions) custd
WHERE _TABLE_SUFFIX BETWEEN '20171110' and '20171111'
LIMIT 1000

这只是一个例子，但希望它足以理解这个概念。
hits是结构的重复字段，因此类似于:

hits = [{'hitNumber': 1, 'product': [{'productSku': 'sku0'}]}, {'hitNumber': 2}, ...]

当您申请 unnest(hits) AS unnested_hits操作它变成:

unnested_hits = {'hitNumber': 1, 'product': [{'productSku': 'sku0'}]},
                {'hitNumber': 2}
                ...

所以如果你叫它 "unnested_hits" ，当您引用此别名时，您将获得此扁平化数据。您可以继续诸如取消嵌套字段 product里面 unnested_hits )。

要更深入地了解这些概念，请务必通读 docs ，它们写得很好，您可以学习在 BigQuery 中有效工作所需的几乎所有内容。

最后一点，您正在从 GA 中选择所有字段。俗话说，每次有人运行 "SELECT * FROM TABLE" 类型的查询时世界某处的一只 Pandas 死了。

您必须非常小心地在 BQ 中运行此类查询，因为您将按处理的数据量计费；确保您只携带绝对必要的元素。