Writing query results

This document describes how to write query results to temporary or permanent tables.

Temporary and permanent tables

BigQuery saves all query results to a table, which can be either permanent or temporary.

BigQuery uses temporary tables to cache query results that aren't written to a permanent table. The tables are created in a special dataset and named randomly. You can also create temporary tables for your own use within multi-statement queries and sessions . You aren't charged for temporary cached query result tables . You are charged for temporary tables that aren't cached query results.
After a query finishes, the temporary table exists for up to 24 hours. To view table structure and data, go to the BigQuery console , click Personal history, and choose the query that created the temporary table. Then, in the Destination tablerow, click Temporary table.
Access to the temporary table data is restricted to the user or service account that created the query job.
You cannot share temporary tables, and they are not visible using any of the standard list or other table manipulation methods. If you need to share your query results, write the results to a permanent table, download them, or share them though Google Sheets or Google Drive.
Temporary tables are created in the same region as the table or tables being queried.
A permanent table can be a new or existing table in any dataset to which you have access. If you write query results to a new table, you are charged for storing the data. When you write query results to a permanent table, the tables you're querying must be in the same location as the dataset that contains the destination table.
You can't save query results in a temporary table when the domain-restricted organization policy is enabled. As a workaround, temporarily disable the domain-restricted organization policy, run the query, and then again enable the policy. Alternatively, you can save query results in a destination table.

Required permissions

At a minimum, to write query results to a table, you must be granted the following permissions:

bigquery.tables.create permissions to create a new table
bigquery.tables.updateData to write data to a new table, overwrite a table, or append data to a table
bigquery.jobs.create to run a query job

Additional permissions such as bigquery.tables.getData may be required to access the data you're querying.

The following predefined IAM roles include both bigquery.tables.create and bigquery.tables.updateData permissions:

bigquery.dataEditor
bigquery.dataOwner
bigquery.admin

The following predefined IAM roles include bigquery.jobs.create permissions:

bigquery.user
bigquery.jobUser
bigquery.admin

In addition, if a user has bigquery.datasets.create permissions, when that user creates a dataset, they are granted bigquery.dataOwner access to it. bigquery.dataOwner access gives the user the ability to create and update tables in the dataset.

For more information on IAM roles and permissions in BigQuery, see Predefined roles and permissions .

Write query results to a permanent table

When you write query results to a permanent table, you can create a new table, append the results to an existing table, or overwrite an existing table.

Writing query results

Use the following procedure to write your query results to a permanent table. To help control costs, you can preview data before running the query.

Console

Open the BigQuery page in the Google Cloud console.

Go to the BigQuery page
In the Explorerpanel, expand your project and select a dataset.
Enter a valid SQL query.
Click Moreand then select Query settings.
Select the Set a destination table for query resultsoption.
In the Destinationsection, select the Datasetin which you want to create the table, and then choose a Table Id.
In the Destination table write preferencesection, choose one of the following:
- Write if empty— Writes the query results to the table only if the table is empty.
- Append to table— Appends the query results to an existing table.
- Overwrite table— Overwrites an existing table with the same name using the query results.
Optional: For Data location, choose your location .
To update the query settings, click Save.
Click Run. This creates a query job that writes the query results to the table you specified.

Alternatively, if you forget to specify a destination table before running your query, you can copy the cached results table to a permanent table by clicking the Save Results button above the editor.

SQL

The following example uses the CREATE TABLE statement to create the trips table from data in the public bikeshare_trips table:

In the Google Cloud console, go to the BigQuerypage.

Go to BigQuery

In the query editor, enter the following statement:

 CREATE 
  
 TABLE 
  
  mydataset 
 
 . 
 trips 
  
 AS 
  
 ( 
  
 SELECT 
  
 bike_id 
 , 
  
 start_time 
 , 
  
 duration_minutes 
  
 FROM 
  
 bigquery 
 - 
 public 
 - 
 data 
 . 
 austin_bikeshare 
 . 
 bikeshare_trips 
 );

Click Run.

For more information about how to run queries, see Run an interactive query .

For more information, see Creating a new table from an existing table .

bq

In the Google Cloud console, activate Cloud Shell.

Activate Cloud Shell

At the bottom of the Google Cloud console, a Cloud Shell session starts and displays a command-line prompt. Cloud Shell is a shell environment with the Google Cloud CLI already installed and with values already set for your current project. It can take a few seconds for the session to initialize.
Enter the bq query command and specify the --destination_table flag to create a permanent table based on the query results. Specify the use_legacy_sql=false flag to use GoogleSQL syntax. To write the query results to a table that is not in your default project, add the project ID to the dataset name in the following format: project_id : dataset .

Optional: Supply the --location flag and set the value to your location .

To control the write disposition for an existing destination table, specify one of the following optional flags:
- --append_table : If the destination table exists, the query results are appended to it.
- --replace : If the destination table exists, it is overwritten with the query results.
```
bq  
--location = 
 location 
  
query  
 \ 
--destination_table  
 project_id 
: dataset 
. table 
  
 \ 
--use_legacy_sql = 
 false 
  
 ' query 
' 
```
  Replace the following:
- location is the name of the location used to process the query. The --location flag is optional. For example, if you are using BigQuery in the Tokyo region, you can set the flag's value to asia-northeast1 . You can set a default value for the location by using the .bigqueryrc file .
- project_id is your project ID.
- dataset is the name of the dataset that contains the table to which you are writing the query results.
- table is the name of the table to which you're writing the query results.
- query is a query in GoogleSQL syntax.
  
  If no write disposition flag is specified, the default behavior is to write the results to the table only if it is empty. If the table exists and it is not empty, the following error is returned: BigQuery error in query operation: Error processing job project_id :bqjob_123abc456789_00000e1234f_1: Already Exists: Table project_id:dataset.table .
  
  Examples:
  
  Note: These examples query a US-based public dataset. Because the public dataset is stored in the US multi-region location, the dataset that contains your destination table must also be in the US. You cannot query a dataset in one location and write the results to a destination table in another location.
  
  Enter the following command to write query results to a destination table named mytable in mydataset . The dataset is in your default project. Since no write disposition flag is specified in the command, the table must be new or empty. Otherwise, an Already exists error is returned. The query retrieves data from the USA Name Data public dataset .
```
 bq  
query 
  
 \ 
--destination_table  
mydataset.mytable  
 \ 
--use_legacy_sql = 
 false 
  
 \ 
 'SELECT 
 name, 
 number 
 FROM 
 `bigquery-public-data`.usa_names.usa_1910_current 
 WHERE 
 gender = "M" 
 ORDER BY 
 number DESC' 
```
  Enter the following command to use query results to overwrite a destination table named mytable in mydataset . The dataset is in your default project. The command uses the --replace flag to overwrite the destination table.
```
 bq  
query 
  
 \ 
--destination_table  
mydataset.mytable  
 \ 
--replace  
 \ 
--use_legacy_sql = 
 false 
  
 \ 
 'SELECT 
 name, 
 number 
 FROM 
 `bigquery-public-data`.usa_names.usa_1910_current 
 WHERE 
 gender = "M" 
 ORDER BY 
 number DESC' 
```
  Enter the following command to append query results to a destination table named mytable in mydataset . The dataset is in my-other-project , not your default project. The command uses the --append_table flag to append the query results to the destination table.
```
 bq  
query 
  
 \ 
--append_table  
 \ 
--use_legacy_sql = 
 false 
  
 \ 
--destination_table  
my-other-project:mydataset.mytable  
 \ 
 'SELECT 
 name, 
 number 
 FROM 
 `bigquery-public-data`.usa_names.usa_1910_current 
 WHERE 
 gender = "M" 
 ORDER BY 
 number DESC' 
```
  The output for each of these examples looks like the following. For readability, some output is truncated.
```
Waiting on bqjob_r123abc456_000001234567_1 ... (2s) Current status: DONE
+---------+--------+
|  name   | number |
+---------+--------+
| Robert  |  10021 |
| John    |   9636 |
| Robert  |   9297 |
| ...              |
+---------+--------+
```

API

To save query results to a permanent table, call the jobs.insert method, configure a query job, and include a value for the destinationTable property. To control the write disposition for an existing destination table, configure the writeDisposition property.

To control the processing location for the query job, specify the location property in the jobReference section of the job resource .

Go

Before trying this sample, follow the Go setup instructions in the BigQuery quickstart using client libraries . For more information, see the BigQuery Go API reference documentation .

To authenticate to BigQuery, set up Application Default Credentials. For more information, see Set up authentication for client libraries .

  import 
  
 ( 
  
 "context" 
  
 "fmt" 
  
 "io" 
  
 "cloud.google.com/go/bigquery" 
  
 "google.golang.org/api/iterator" 
 ) 
 // queryWithDestination demonstrates saving the results of a query to a specific table by setting the destination 
 // via the API properties. 
 func 
  
 queryWithDestination 
 ( 
 w 
  
 io 
 . 
 Writer 
 , 
  
 projectID 
 , 
  
 destDatasetID 
 , 
  
 destTableID 
  
 string 
 ) 
  
 error 
  
 { 
  
 // projectID := "my-project-id" 
  
 // datasetID := "mydataset" 
  
 // tableID := "mytable" 
  
 ctx 
  
 := 
  
 context 
 . 
 Background 
 () 
  
 client 
 , 
  
 err 
  
 := 
  
 bigquery 
 . 
 NewClient 
 ( 
 ctx 
 , 
  
 projectID 
 ) 
  
 if 
  
 err 
  
 != 
  
 nil 
  
 { 
  
 return 
  
 fmt 
 . 
 Errorf 
 ( 
 "bigquery.NewClient: %v" 
 , 
  
 err 
 ) 
  
 } 
  
 defer 
  
 client 
 . 
 Close 
 () 
  
 q 
  
 := 
  
 client 
 . 
 Query 
 ( 
 "SELECT 17 as my_col" 
 ) 
  
 q 
 . 
  Location 
 
  
 = 
  
 "US" 
  
 // Location must match the dataset(s) referenced in query. 
  
 q 
 . 
  QueryConfig 
 
 . 
 Dst 
  
 = 
  
 client 
 . 
 Dataset 
 ( 
 destDatasetID 
 ). 
 Table 
 ( 
 destTableID 
 ) 
  
 // Run the query and print results when the query job is completed. 
  
 job 
 , 
  
 err 
  
 := 
  
 q 
 . 
 Run 
 ( 
 ctx 
 ) 
  
 if 
  
 err 
  
 != 
  
 nil 
  
 { 
  
 return 
  
 err 
  
 } 
  
 status 
 , 
  
 err 
  
 := 
  
 job 
 . 
 Wait 
 ( 
 ctx 
 ) 
  
 if 
  
 err 
  
 != 
  
 nil 
  
 { 
  
 return 
  
 err 
  
 } 
  
 if 
  
 err 
  
 := 
  
 status 
 . 
  Err 
 
 (); 
  
 err 
  
 != 
  
 nil 
  
 { 
  
 return 
  
 err 
  
 } 
  
 it 
 , 
  
 err 
  
 := 
  
 job 
 . 
 Read 
 ( 
 ctx 
 ) 
  
 for 
  
 { 
  
 var 
  
 row 
  
 [] 
 bigquery 
 . 
  Value 
 
  
 err 
  
 := 
  
 it 
 . 
 Next 
 ( 
& row 
 ) 
  
 if 
  
 err 
  
 == 
  
 iterator 
 . 
 Done 
  
 { 
  
 break 
  
 } 
  
 if 
  
 err 
  
 != 
  
 nil 
  
 { 
  
 return 
  
 err 
  
 } 
  
 fmt 
 . 
 Fprintln 
 ( 
 w 
 , 
  
 row 
 ) 
  
 } 
  
 return 
  
 nil 
 }

Java

Before trying this sample, follow the Java setup instructions in the BigQuery quickstart using client libraries . For more information, see the BigQuery Java API reference documentation .

To authenticate to BigQuery, set up Application Default Credentials. For more information, see Set up authentication for client libraries .

To save query results to a permanent table, set the destination table to the desired TableId in a QueryJobConfiguration .

  import 
  
 com.google.cloud.bigquery. BigQuery 
 
 ; 
 import 
  
 com.google.cloud.bigquery. BigQueryException 
 
 ; 
 import 
  
 com.google.cloud.bigquery. BigQueryOptions 
 
 ; 
 import 
  
 com.google.cloud.bigquery. QueryJobConfiguration 
 
 ; 
 import 
  
 com.google.cloud.bigquery. TableId 
 
 ; 
 public 
  
 class 
 SaveQueryToTable 
  
 { 
  
 public 
  
 static 
  
 void 
  
 runSaveQueryToTable 
 () 
  
 { 
  
 // TODO(developer): Replace these variables before running the sample. 
  
 String 
  
 query 
  
 = 
  
 "SELECT corpus FROM `bigquery-public-data.samples.shakespeare` GROUP BY corpus;" 
 ; 
  
 String 
  
 destinationTable 
  
 = 
  
 "MY_TABLE" 
 ; 
  
 String 
  
 destinationDataset 
  
 = 
  
 "MY_DATASET" 
 ; 
  
 saveQueryToTable 
 ( 
 destinationDataset 
 , 
  
 destinationTable 
 , 
  
 query 
 ); 
  
 } 
  
 public 
  
 static 
  
 void 
  
 saveQueryToTable 
 ( 
  
 String 
  
 destinationDataset 
 , 
  
 String 
  
 destinationTableId 
 , 
  
 String 
  
 query 
 ) 
  
 { 
  
 try 
  
 { 
  
 // Initialize client that will be used to send requests. This client only needs to be created 
  
 // once, and can be reused for multiple requests. 
  
  BigQuery 
 
  
 bigquery 
  
 = 
  
  BigQueryOptions 
 
 . 
 getDefaultInstance 
 (). 
 getService 
 (); 
  
 // Identify the destination table 
  
  TableId 
 
  
 destinationTable 
  
 = 
  
  TableId 
 
 . 
 of 
 ( 
 destinationDataset 
 , 
  
 destinationTableId 
 ); 
  
 // Build the query job 
  
  QueryJobConfiguration 
 
  
 queryConfig 
  
 = 
  
  QueryJobConfiguration 
 
 . 
 newBuilder 
 ( 
 query 
 ). 
 setDestinationTable 
 ( 
 destinationTable 
 ). 
 build 
 (); 
  
 // Execute the query. 
  
 bigquery 
 . 
  query 
 
 ( 
 queryConfig 
 ); 
  
 // The results are now saved in the destination table. 
  
 System 
 . 
 out 
 . 
 println 
 ( 
 "Saved query ran successfully" 
 ); 
  
 } 
  
 catch 
  
 ( 
  BigQueryException 
 
  
 | 
  
 InterruptedException 
  
 e 
 ) 
  
 { 
  
 System 
 . 
 out 
 . 
 println 
 ( 
 "Saved query did not run \n" 
  
 + 
  
 e 
 . 
 toString 
 ()); 
  
 } 
  
 } 
 }

Node.js

Before trying this sample, follow the Node.js setup instructions in the BigQuery quickstart using client libraries . For more information, see the BigQuery Node.js API reference documentation .

To authenticate to BigQuery, set up Application Default Credentials. For more information, see Set up authentication for client libraries .

  // Import the Google Cloud client library 
 const 
  
 { 
 BigQuery 
 } 
  
 = 
  
 require 
 ( 
 ' @google-cloud/bigquery 
' 
 ); 
 const 
  
 bigquery 
  
 = 
  
 new 
  
  BigQuery 
 
 (); 
 async 
  
 function 
  
 queryDestinationTable 
 () 
  
 { 
  
 // Queries the U.S. given names dataset for the state of Texas 
  
 // and saves results to permanent table. 
  
 /** 
 * TODO(developer): Uncomment the following lines before running the sample. 
 */ 
  
 // const datasetId = 'my_dataset'; 
  
 // const tableId = 'my_table'; 
  
 // Create destination table reference 
  
 const 
  
 dataset 
  
 = 
  
 bigquery 
 . 
 dataset 
 ( 
 datasetId 
 ); 
  
 const 
  
 destinationTable 
  
 = 
  
 dataset 
 . 
 table 
 ( 
 tableId 
 ); 
  
 const 
  
 query 
  
 = 
  
 `SELECT name 
 FROM \`bigquery-public-data.usa_names.usa_1910_2013\` 
 WHERE state = 'TX' 
 LIMIT 100` 
 ; 
  
 // For all options, see https://cloud.google.com/bigquery/docs/reference/v2/tables#resource 
  
 const 
  
 options 
  
 = 
  
 { 
  
 query 
 : 
  
 query 
 , 
  
 // Location must match that of the dataset(s) referenced in the query. 
  
 location 
 : 
  
 'US' 
 , 
  
 destination 
 : 
  
 destinationTable 
 , 
  
 }; 
  
 // Run the query as a job 
  
 const 
  
 [ 
 job 
 ] 
  
 = 
  
 await 
  
 bigquery 
 . 
 createQueryJob 
 ( 
 options 
 ); 
  
 console 
 . 
 log 
 ( 
 `Job 
 ${ 
  job 
 
 . 
 id 
 } 
 started.` 
 ); 
  
 console 
 . 
 log 
 ( 
 `Query results loaded to table 
 ${ 
 destinationTable 
 . 
 id 
 } 
 ` 
 ); 
 }

Python

Before trying this sample, follow the Python setup instructions in the BigQuery quickstart using client libraries . For more information, see the BigQuery Python API reference documentation .

To authenticate to BigQuery, set up Application Default Credentials. For more information, see Set up authentication for client libraries .

To save query results to a permanent table, create a QueryJobConfig and set the destination to the desired TableReference . Pass the job configuration to the query method .

  from 
  
 google.cloud 
  
 import 
  bigquery 
 
 # Construct a BigQuery client object. 
 client 
 = 
  bigquery 
 
 . 
  Client 
 
 () 
 # TODO(developer): Set table_id to the ID of the destination table. 
 # table_id = "your-project.your_dataset.your_table_name" 
 job_config 
 = 
  bigquery 
 
 . 
  QueryJobConfig 
 
 ( 
 destination 
 = 
 table_id 
 ) 
 sql 
 = 
 """ 
 SELECT corpus 
 FROM `bigquery-public-data.samples.shakespeare` 
 GROUP BY corpus; 
 """ 
 # Start the query, passing in the extra configuration. 
 query_job 
 = 
 client 
 . 
  query 
 
 ( 
 sql 
 , 
 job_config 
 = 
 job_config 
 ) 
 # Make an API request. 
  query_job 
 
 . 
 result 
 () 
 # Wait for the job to complete. 
 print 
 ( 
 "Query results loaded to the table 
 {} 
 " 
 . 
 format 
 ( 
 table_id 
 ))

Writing query results

Temporary and permanent tables

Required permissions

Write query results to a permanent table

Writing query results

Console

SQL

bq

API

Go

Java

Node.js

Python

Write large query results

Limitations

Writing large results using legacy SQL

Console

bq

API

Go

Java

Node.js

Python

Downloading and saving query results from the Google Cloud console

Limitations

What's next