Bigquery in Python: How to put the results of a query in a table?

1
votes

I recently started using BigQuery in Python2.7 and I'm having issues putting the results of a query in a table.

My query: 

query_data = {
    'configuration': {
        'query': {
            'query': QUERY
            'destinationTable': {
                'projectId': project_id,
                'datasetId': dataset_id,
                'tableId': 'table_id'
            },
            'createDisposition': 'CREATE_IF_NEEDED',
            'writeDisposition': 'WRITE_TRUNCATE',
            'allowLargeResults': True
        },
    }
}

query_request.query(projectId=PROJECT_NUMBER,body=query_data).execute()

According to what I read in the Google BigQuery documentation, destinationTable, createDisposition and writeDisposition should ensure that the result of my query ends up in the chosen BigQuery table.

But it doesn't and I get this error:

HttpError: https://www.googleapis.com/bigquery/v2/projects/project_id/queries?alt=json returned "Required parameter is missing">

Does somebody know how to fix this bug?

PS: 'QUERY' works when I use it directly on the Google BigQuery website, so I highly doubt that the problem is there.

PPS: Thanks to @Pentium10 I was able to solve the issue.

1
pythonpython-2.7google-bigquery

1 Answers

1
votes

You can do this by specifying a destination table in the query. You would need to use the Jobs.insert api rather than the Jobs.query call, and you should specify writeDisposition=WRITE_APPEND and fill out the destination table.

Here is what the configuration would look like, if you were using the raw api. If you're using Python, the python client should give accessors to these same fields:

"configuration": {
  "query": {
    "query": "select count(*) from foo.bar",
    "destinationTable": {
      "projectId": "my_project",
      "datasetId": "my_dataset",
      "tableId": "my_table"
    },
    "createDisposition": "CREATE_IF_NEEDED",
    "writeDisposition": "WRITE_APPEND",
  }
}