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.

Answer 2

You should use tabledata.list API for fetching data from table.
Using parameters (startIndex or pageToken) and maxResults you can control size of page you fetch.

Answer 3

I think this is exactly what you need link, as far as I understood you can't get a large result of a query but you can get the entire table data to your app no mater how big it is, thats why you need to put the large result in a table and then get this table data to your app and do whatever you want with it

good luck :)