Hubert Dufourt Hubert Dufourt - 6 months ago 33
Python Question

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

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()


And according to what I read in the google-bigquery documentation, the lines 'destinationTable', 'createDisposition' and 'writeDisposition' should ensure that the results of my query ends in the chosen bigquery table.

But it doesn't and I just 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 I could fix this bug.

Thanks in advance.

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

PS2: Thanks to Pentium10 I was able to solve the issue.

Answer

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",
  }
}
Comments