Simple way to use bigquery result in python where the query return only one result

Question

I'm simply trying to get one data from bigquery from python. Below is the query

select max(date) from `x.x.table_name`

all the requirements to access bigquery in python is done, and I run the query as below

query_job = client.query('select max(date) from `x.x.table_name`')

and it returns google.cloud.bigquery.job.query.QueryJob object. Is there a quick way to get the result rather than going through the RowIterator, etc.?

Answer 1

You need to actually fetch the data

query_job = client.query('select max(date) from `x.x.table_name`').result()

Then, you should iterate over rows, or, if you know that there is a single entry, try

one_row = client.query('select max(date) from `x.x.table_name`').result()[0]

Answer 2

You can convert the google.cloud.bigquery.job.query.QueryJob object to a pandas dataframe using to_dataframe(). When converted you can use pandas to manipulate your data.

See code below:

from google.cloud import bigquery

client = bigquery.Client()

sql = """
    SELECT *
    FROM `my-project-id.my-dataset.myTable`
"""

query_job = client.query(sql)
df = query_job.to_dataframe()

print("Query results:")
print(df)
print("\nGet 1st row values")
print(df.iloc[0])

Testing:

You can also use to_arrow() and to_geodataframe() if you are more comfortable at manipulating these data types.

Answer 3

result() on a QueryJob will wait for the result of the job and return iterator so then you can use next():

    result = query_job.result()
    row = next(result)