如何用SQL查询BigQuery字典类型字段中数值键对应的属性值

报错原因 #

• 语法错误直接触发点：FROM子句末尾多了多余的逗号,，导致SQL解析器无法识别后续的WHERE关键字，直接抛出语法错误。
• 逻辑用法错误：Google Analytics的BigQuery数据集里hits是重复数组类型，不是键值对字典，你直接用hits.0.productName只能取每个会话第一条命中记录的产品名，无法获取所有数值索引对应的值。

正确SQL写法 #

如果要提取所有hits下的productName，需要用UNNEST函数打散hits数组，再关联取值：

SELECT hit.productName
FROM `bigquery-public-data.google_analytics_sample.ga_sessions_*`
,UNNEST(hits) AS hit
WHERE date BETWEEN '20160101' AND '20161231'

如果需要保留对应的会话标识，可以加上fullVisitorId、visitId这类维度，同时可以过滤掉空的产品名记录：

SELECT 
  fullVisitorId,
  visitId,
  hit.productName
FROM `bigquery-public-data.google_analytics_sample.ga_sessions_*`
,UNNEST(hits) AS hit
WHERE date BETWEEN '20160101' AND '20161231'
AND hit.productName IS NOT NULL

完整可运行Python代码 #

from google.cloud import bigquery
import os

# 初始化认证
os.environ['GOOGLE_APPLICATION_CREDENTIALS'] ='/Users/<UserName>/Desktop/folder/key/<Key_name>.json'
bigquery_client = bigquery.Client()

# 执行查询
df = bigquery_client.query("""
SELECT 
  fullVisitorId,
  visitId,
  hit.productName
FROM `bigquery-public-data.google_analytics_sample.ga_sessions_*`
,UNNEST(hits) AS hit
WHERE date BETWEEN '20160101' AND '20161231'
AND hit.productName IS NOT NULL
""").to_dataframe()

print(df)

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