在BigQuery中使用最大值展平结果

你的 probabilities 字段是重复记录,即结构数组。可以使用子查询遍历数组并选择最大概率,如下所示:

SELECT 
    original,
    predicted, 
    (SELECT p 
         -- Iterate over the array
         FROM UNNEST(probabilities) as p 
         -- Order by probability and get the first result
         ORDER BY p.prob DESC
         LIMIT 1) AS probabilities
  FROM
      ML.PREDICT(MODEL `my_dataset.my_model`,
      (
      SELECT
        *
      FROM   
       `bigquery-public-data.ml_datasets.census_adult_income`

      ))

结果如下:

from google.cloud import bigquery

client = bigquery.Client()

# Perform a query.
sql = ''' SELECT ... ''' # Your query 

query_job = client.query(sql)

rows = query_job.result()  # Waits for query to finish

for row in rows:
    print(row.values())

输出:

(' >50K', ' >50K', {'label': ' >50K', 'prob': 0.5218586871072727})
(' >50K', ' >50K', {'label': ' >50K', 'prob': 0.5907989087876587})
(' >50K', ' >50K', {'label': ' >50K', 'prob': 0.734145221825564})

注意,概率是BigQuery SQL中的一种结构数据类型,因此它被映射为python dict。

BigQuery quickstart 有关客户端库的详细信息。