Fetching data from large BigQuery table in python

Question

What I have is a BigQuery table(>5mil rows).

I need to fetch this data in batches and process it inside AppEngine, python.

The only way to fetch from a table that I know is to run SELECT query on this table and then iterate the result using tokens fetch_data returns.

It looks like this:

query = u"""\
    SELECT url FROM %s
    """ % (query_table)

query_job = client.run_async_query(str(uuid.uuid4()), query)

query_job.begin()

wait_for_job(query_job, 1)

query_results = query_job.results()

rows, total_rows, next_token = query_results.fetch_data(max_results=per_page, page_token=page_token)

This works on smaller tables, but on larger ones like mine it asks to allow large requests and specify target table. But this makes no sense to me. For to simply fetch data from a table I have to copy it to another table?

Answer 1

What you are running into is described in this documentation. In summary, apart from the limit on how much data can be fetched at a time, there is a point where your results become "large results." This is when your results are more than 128MB compressed as described here. When your results are classified as large, you can only store the result of a query in a table in Big Query.

Unfortunately I'm not sure there's a nice way to do what you want without reducing how many rows you are retrieving at once. What you'll likely need to do is explore the exporting data documentation for big query.