Query return limited to 87500 rows

[mikesjqubit]

Hey guys, having a problem with the following code:

client = bigquery.Client.from_service_account_json(google_secrets_file)
query = client.run_sync_query(query_sql_string)
query.allow_large_results = True
query.use_query_cache = False
query.run()
response = query.fetch_data()

all large queries seem to return exactly 87500 rows (even if I export to an interim table first and select * on them). I can't see this number anywhere in the library or the documentation, so have no idea where this limit comes from or how to proceed!

[tseaver]

@mikesjqubit Thanks for the report. gcloud.bigquery.query.Query.fetch_data actually returns data in "pages", with a page token. In the second example under the Querying data (synchronous) usage docs, note the loop over pages:

query.run()
do_something_with(query.schema, query.rows)
token = query.page_token  # for initial request
while True:
    do_something_with(query.schema, rows)
    if token is None:
        break
    rows, total_count, token = query.fetch_data(page_token=token)

[jgeewax]

IMO this is a problem that needs fixing (at further discussion). It's somewhat different from typical "listing" of things (ie list_topics()) but similar in that we're exposing implementation details when people don't care all that much about them.

(related to #895)

@mikesjqubit : What would you expect to be the right way to handle this? I don't think that making you write that while loop is the right answer.

[mikesjqubit]

I was fully expecting to be able to download everything in one go, maybe there would be max_rows = None flag to set. I'm very surprised that the row limit is this small. Honestly, the ugliness doesn't concern me as much as the slowness of it - downloading 3m rows takes about 5 minutes using the loop method. Am considering exporting to GCS then downloading from there, it just feels so roundabout though!

[dhermes]

@fhoffa Any thoughts?

[tseaver]

@fhoffa bump.

[tswast]

This was fixed when the fetch_data method was changed to return a core iterator. #2840