how to get all rows from a bigquery result in python?

user_1146785 0
Viewed 1500

I'm trying to get all results from a BQ query but I can only get 100 results.

Given the borked python BQ iterator API what's the best way to do this?

based on these docs: https://cloud.google.com/bigquery/docs/paging-results

I have all this code (to do something really basic) but still only get 100 results...

I must be missing something in the API, this should not be so complicated.


bqc = bigquery.Client(credentials=bq_creds, project=bq_creds.project_id)

def query(qstring, getall=True):
    '''run a time query string'''
    logger.info('query>> %s', qstring)
    try:
        job = bqc.query(qstring)
        result = job.result()
    except Exception as err:
        logging.error('failed BQ job %s', err)
        raise err

    if getall:
        result = get_all(result, job)
    return result


def get_all(cursor, job):
    '''unwind the bq data'''
    destination = job.destination
    destination = bqc.get_table(destination)

    # Download rows.
    # The client library automatically handles pagination.
    rows = bqc.list_rows(destination, max_results=5000)
    data = []
    for row in rows:
        data.append(row)
    logging.info('get_all rows: %s', len(data))
    return data

(Then a follow-on question is how to make this BQ data JSON serializable)

google-bigquery google-cloud-platform python
1 Answers

Is job.destination required? Try a simpler approach:

from google.cloud import bigquery

# Construct a BigQuery client object.
client = bigquery.Client()

query = """
    SELECT name, SUM(number) as total_people
    FROM `bigquery-public-data.usa_names.usa_1910_2013`
    WHERE state = 'TX'
    GROUP BY name, state
    ORDER BY total_people DESC
    LIMIT 20
"""
query_job = client.query(query)  # Make an API request.

print("The query data:")
for row in query_job:
    # Row values can be accessed by field name or index.
    print("name={}, count={}".format(row[0], row["total_people"]))
user_134735250
Related