如何通过BigQuery Python API筛选结构化数据数组

需求概述 #

从BigQuery表中提取以下特定组合的数据并导出到Pandas DataFrame：

• Contract A + Specification 1/2
• Contract B + Specification 1
• Contract C + Specification 3

原始表数据：

Report-Date Contract Specification  Value
2020-01-01  A        1              34.2
2020-01-01  A        2              37.2
2020-01-01  A        3              39.7
2020-01-02  A        1              34.1
2020-01-02  A        2              37.0
2020-01-02  A        3              38.8
2020-01-01  B        1              10.0
2020-01-02  B        2              10.1
2020-01-01  C        2              74.7
2020-01-01  C        2              75.2
2020-01-02  C        3              80.9
2020-01-02  C        3              80.9

期望结果：

Report-Date Contract Specification  Value
2020-01-01  A        1              34.2
2020-01-01  A        2              37.2
2020-01-02  A        1              34.1
2020-01-02  A        2              37.0
2020-01-01  B        1              10.0
2020-01-02  C        3              80.9
2020-01-02  C        3              80.9

原代码的问题点 #

1. 结构体类型定义错误：BigQuery的结构体参数需要明确指定字段名和类型，不能仅写STRUCT<STRING, INT64>
2. 字符串值未加引号：数组中的Contract字符串（如A、B、C）没有用引号包裹，会被识别为变量而非字符串字面量
3. 代码笔误：query_job2.to_dataframe()中的query_job2是未定义变量，应为query_job
4. 数组匹配语法缺失：BigQuery中数组类型参数需用UNNEST()展开后才能用IN匹配

正确实现代码 #

import time
from google.cloud import bigquery

# 初始化BigQuery客户端
client = bigquery.Client()

# 定义查询语句，替换为实际的项目、数据集、表路径
query = """
SELECT *
FROM `your-project.your-dataset.your-table`
WHERE STRUCT(Contract, Specification) IN UNNEST(@product);
"""

# 定义查询参数：结构体数组
query_parameters = [
    bigquery.ArrayQueryParameter(
        "product",
        "STRUCT<Contract STRING, Specification INT64>",
        [("A", 1), ("A", 2), ("B", 1), ("C", 3)]
    )
]

# 配置查询任务
job_config = bigquery.QueryJobConfig(query_parameters=query_parameters)

# 执行查询
query_job = client.query(query, job_config=job_config)

# 等待查询完成（可选，也可直接用query_job.result()自动等待）
while query_job.state != 'DONE':
    time.sleep(1)
    print("查询执行中...")

# 导出到Pandas DataFrame
df = query_job.to_dataframe()
print(df)

关键说明 #

• 结构体类型格式：必须写成STRUCT<字段名1 类型1, 字段名2 类型2>，确保和表中字段名、类型完全匹配
• UNNEST关键字：数组类型参数需要用UNNEST()展开，才能被IN关键字匹配
• 参数值格式：数组中的每个元素是元组，对应结构体的字段顺序，字符串值必须加引号
• 表路径：替换your-project.your-dataset.your-table为实际的项目ID、数据集ID和表名

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