FLATTEN results using MAX value in BigQuery

Question

I need to flatten probabilities column in my results with the max number:

original predicted probabilities
 <=50K   >50K      >50K 0.5377828170971353
                   <=50K 0.46221718290286473
 <=50K   <=50K     >50K 0.05434716579642335
                   <=50K 0.9456528342035766

I would like to flatten my result, but now using this query I just get the table above and using bigQuery Python client get an: [object Object],[object Object]

    SELECT
      original,
      predicted,
      probabilities
    FROM
      ML.PREDICT(MODEL `my_dataset.my_model`,
      (
      SELECT
        *
      FROM   
       `bigquery-public-data.ml_datasets.census_adult_income`

      ))

Tlaquetzal Tlaquetzal · Accepted Answer · 2019-08-16T21:57:15

Your probabilities field is a REPEATED RECORD, i.e., an array of structs. You can use a subquery to iterate over the array and select the max probability, like this:

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`

      ))

The result will look like this:

The python result you got looks more like a javascript representation of an object. Here's how I did it in python:

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())

Output:

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

Note that probabilities is a struct data type in BigQuery SQL, so its mapped as a python dict.

Check the BigQuery quickstart for more information on client libraries.

FLATTEN results using MAX value in BigQuery

1 Answers