Python – Create a table from query results in Google BigQuery

google-app-enginegoogle-bigquerypython

We're using Google BigQuery via the Python API. How would I create a table (new one or overwrite old one) from query results? I reviewed the query documentation, but I didn't find it useful.

We want to simulate:

"SELEC … INTO …" from ANSI SQL.

Best 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",
  }
}
Related Topic