BigQuery results too large response

1
votes

I'm trying to list the data in a table using the python api:

tableDataList = tableDataCollection.list(**params).execute(http=http)

Allowed params:

 list(projectId=*, datasetId=*, tableId=*, pageToken=None, maxResults=None, startIndex=None)

The error I receive is:

apiclient.errors.HttpError: <HttpError 403 when requesting https://www.googleapis.com/bigquery/v2/projects/myproject/queries?alt=json returned "Response too large to return.">

Currently I create a query job and receive the destinationTable back, and on that destinationTable I list the data (for performance among other). I've read that I can use allowLargeResults but I can't find it on the tabledata.list or jobs.query endpoints. Where do I set that or how do I overcome issue? Data is only about 90mb for 97k rows.

UPDATE:

I've changed the query to use a destination table (with jobs.insert) on which I can then set the allowLargeResults to True.

body = {
    'configuration': {
        'query': {
            'createDisposition': 'CREATE_IF_NEEDED',
            'writeDisposition': 'WRITE_TRUNCATE',
            'useQueryCache': False,
            'allowLargeResults': True,
            'destinationTable': {
                'projectId': PROJECT_ID,
                'datasetId': DATASET_ID,
                'tableId': 'tmp_{0}'.format(TABLE_ID),
            },
            'query': query,
        }
    }
}

However, I still receive the same error:

ResponseTooLarge: Response too large to return.

2
google-bigquery

2 Answers

1
votes

From your error message, it looks like the query failed, not the the table data list call.

allowLargeResults allows queries to produce arbitrarily large output results. You can read more about it here: https://cloud.google.com/bigquery/docs/reference/v2/jobs You can set it in configuration.query.allowLargeResults. It shouldn't be necessary for 90MB of results, but a result of > 128MB would need it. Perhaps your results are larger than you expect?. You'll also need to set an explicit destination table for allowLargeResults to work.

Once your query completes, it should be possible to list the results just as you have described. If you still have problems, we can look at individual details if you provide your job_id.

0
votes

It seems to work if I remove the ordering in my query:

query = """
    SELECT *
    FROM [mydataset.{0}]
    WHERE delta > {1}
    --ORDER BY delta ASC
""".format(TABLE_ID, int(delta))

Uhm, this is weird... BigQuery not big enough?