Export data to google cloud storage from BigQuery select statement without temp table

Question

Is there any way I can export the data to google cloud storage from select statement (BigQuery) without creating a table.

I have a requirement to pull data based on select statement (BigQuery) every day and put the file in google cloud storage.

I know we can do export from BigQuery following ways.

Command Line -- In this option I need to create a temporary table. I couldnt see any example or syntax without temp table.

bq query --destination_table=mydataset.happyhalloween "SELECT name,count FROM mydataset.babynames WHERE gender = 'M' ORDER BY count DESC LIMIT 6" bq extract 'bigquery-public-data:samples.shakespeare' gs://example-bucket/shakespeare.csv

WebConsole -- This option is out since need to schedule
API -- With API also I couldn't see from SQL Query. I see the examples with entire table.

Let me know is there any way to export the data to google cloud storage from Bigquery select statement without creating a temporary table.

Thanks,

I don't have write access to BigQuery, have only select permission and full permissions for bucket. So looking for the options to put the file directly into Storage instead of temp table. — user374374

Tim Swast Tim Swast · Accepted Answer · 2017-06-29T19:33:41

Query results are always saved to either a temporary or permanent table.

When you specify a destination table, that is a permanent table, but if you don't BigQuery creates a short-lived temporary table to store the results. If you look at the Query history section, you can see the names of these temporary tables.

I successfully was able to run an export job to Cloud Storage when specifying a temporary table as the source.

bq extract my-project:_63cfa3...c5d1ef7 gs://my-bucket/my_table.csv

Regarding using the API, you can see the destination table if you get the job after you insert it.

https://cloud.google.com/bigquery/docs/reference/rest/v2/jobs/get

In my example, the response for getting the job ID associated with the query job shows

{
 "kind": "bigquery#job",
 "id": "my-project:bquijob_ca7e64e_15cf54562fb",
 "jobReference": {
  "projectId": "my-project",
  "jobId": "bquijob_ca7e64e_15cf54562fb"
 },
 "configuration": {
  "query": {
   "query": "SELECT \"ABC\"",
   "destinationTable": {
    "projectId": "my-project",
    "datasetId": "_63cfa399614a54153cc386c27d6c0c6fdb249f9e",
    "tableId": "anondca79e1ad06d03be556f3fc3fdbaaf2b9c5d1ef7"
   },
   "createDisposition": "CREATE_IF_NEEDED",
   "writeDisposition": "WRITE_TRUNCATE",
   "useLegacySql": false
  }
 },
 "status": {
  "state": "DONE"
 },
...
}

Export data to google cloud storage from BigQuery select statement without temp table

2 Answers