Manage logical views

This document describes how to manage views in BigQuery. You can manage your BigQuery views in the following ways:

Update a view
Copy a view
Rename a view
Delete a view

Before you begin

Grant Identity and Access Management (IAM) roles that give users the necessary permissions to perform each task in this document. The permissions required to perform a task (if any) are listed in the "Required permissions" section of the task.

Update a view

After creating a view, you can update the following view properties:

SQL query
Expiration time
Description
Labels

Required permissions

To update a view, you need the following IAM permissions:

bigquery.tables.update
bigquery.tables.get

Each of the following predefined IAM roles includes the permissions that you need in order to update a view:

roles/bigquery.dataEditor
roles/bigquery.dataOwner
roles/bigquery.admin

Additionally, if you have the bigquery.datasets.create permission, you can update tables and views in the datasets that you create.

To update the view's SQL query, you must also have permissions to query any tables referenced by the view's SQL query.

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

Updating a view's SQL query

You can update the SQL query used to define a view by:

Using the Google Cloud console
Using the bq command-line tool's bq update command
Calling the tables.patch API method
Using the client libraries

You can change the SQL dialect from legacy SQL to GoogleSQL in the API or bq command-line tool. You cannot update a legacy SQL view to GoogleSQL in the Google Cloud console.

To update a view's SQL query:

Console

In the Explorerpanel, expand your project and dataset, then select the view.
Click the Detailstab.
Above the Querybox, click the Edit querybutton. Click Openin the dialog that appears.
Edit the SQL query in the Query editorbox and then click Save view.
Make sure all the fields are correct in the Save viewdialog and then click Save.

bq

Issue the bq update command with the --view flag. To use GoogleSQL or to update the query dialect from legacy SQL to GoogleSQL, include the --use_legacy_sql flag and set it to false .

If your query references external user-defined function resources stored in Cloud Storage or in local files, use the --view_udf_resource flag to specify those resources. The --view_udf_resource flag is not demonstrated here. For more information on using UDFs, see GoogleSQL User-Defined Functions .

If you are updating a view in a project other than your default project, add the project ID to the dataset name in the following format: project_id:dataset .

bq  
update  
 \ 
  
--use_legacy_sql = 
 false 
  
 \ 
  
--view_udf_resource = 
 path_to_file 
  
 \ 
  
--view = 
 ' query 
' 
  
 \ 
  
 project_id:dataset.view

Replace the following:

path_to_file : the URI or local file system path to a code file to be loaded and evaluated immediately as a user-defined function resource used by the view. Repeat the flag to specify multiple files.
query : a valid GoogleSQL query
project_id : your project ID
dataset : the name of the dataset containing the view you're updating
view : the name of the view you're updating

Examples

Enter the following command to update the SQL query for a view named myview in mydataset . mydataset is in your default project. The example query used to update the view queries data from the USA Name Data public dataset.

 bq update \
    --use_legacy_sql=false \
    --view \
    'SELECT
      name,
      number
    FROM `bigquery-public-data.usa_names.usa_1910_current` 
WHERE
      gender = "M"
    ORDER BY
      number DESC;' \
    mydataset.myview

Enter the following command to update the SQL query for a view named myview in mydataset . mydataset is in myotherproject , not your default project. The example query used to update the view queries data from the USA Name Data public dataset.

 bq update \
    --use_legacy_sql=false \
    --view \
    'SELECT
      name,
      number
    FROM `bigquery-public-data.usa_names.usa_1910_current` 
WHERE
      gender = "M"
    ORDER BY
      number DESC;' \
    myotherproject:mydataset.myview

API

You can update a view by calling the tables.patch method with a table resource that contains an updated view property. Because the tables.update method replaces the entire table resource, the tables.patch method is preferred.

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" 
  
 "cloud.google.com/go/bigquery" 
 ) 
 // updateView demonstrates updating the query metadata that defines a logical view. 
 func 
  
 updateView 
 ( 
 projectID 
 , 
  
 datasetID 
 , 
  
 viewID 
  
 string 
 ) 
  
 error 
  
 { 
  
 // projectID := "my-project-id" 
  
 // datasetID := "mydataset" 
  
 // viewID := "myview" 
  
 ctx 
  
 := 
  
 context 
 . 
 Background 
 () 
  
 client 
 , 
  
 err 
  
 := 
  
 bigquery 
 . 
 NewClient 
 ( 
 ctx 
 , 
  
 projectID 
 ) 
  
 if 
  
 err 
  
 != 
  
 nil 
  
 { 
  
 return 
  
 fmt 
 . 
 Errorf 
 ( 
 "bigquery.NewClient: %v" 
 , 
  
 err 
 ) 
  
 } 
  
 defer 
  
 client 
 . 
 Close 
 () 
  
 view 
  
 := 
  
 client 
 . 
 Dataset 
 ( 
 datasetID 
 ). 
 Table 
 ( 
 viewID 
 ) 
  
 meta 
 , 
  
 err 
  
 := 
  
 view 
 . 
 Metadata 
 ( 
 ctx 
 ) 
  
 if 
  
 err 
  
 != 
  
 nil 
  
 { 
  
 return 
  
 err 
  
 } 
  
 newMeta 
  
 := 
  
 bigquery 
 . 
  TableMetadataToUpdate 
 
 { 
  
 // This example updates a view into the shakespeare dataset to exclude works named after kings. 
  
 ViewQuery 
 : 
  
 "SELECT word, word_count, corpus, corpus_date FROM `bigquery-public-data.samples.shakespeare` WHERE corpus NOT LIKE '%king%'" 
 , 
  
 } 
  
 if 
  
 _ 
 , 
  
 err 
  
 := 
  
 view 
 . 
 Update 
 ( 
 ctx 
 , 
  
 newMeta 
 , 
  
 meta 
 . 
 ETag 
 ); 
  
 err 
  
 != 
  
 nil 
  
 { 
  
 return 
  
 err 
  
 } 
  
 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 .

  import 
  
 com.google.cloud.bigquery. BigQuery 
 
 ; 
 import 
  
 com.google.cloud.bigquery. BigQueryException 
 
 ; 
 import 
  
 com.google.cloud.bigquery. BigQueryOptions 
 
 ; 
 import 
  
 com.google.cloud.bigquery. TableId 
 
 ; 
 import 
  
 com.google.cloud.bigquery. TableInfo 
 
 ; 
 import 
  
 com.google.cloud.bigquery. ViewDefinition 
 
 ; 
 // Sample to update query on a view 
 public 
  
 class 
 UpdateViewQuery 
  
 { 
  
 public 
  
 static 
  
 void 
  
 runUpdateViewQuery 
 () 
  
 { 
  
 // TODO(developer): Replace these variables before running the sample. 
  
 String 
  
 datasetName 
  
 = 
  
 "MY_DATASET_NAME" 
 ; 
  
 String 
  
 tableName 
  
 = 
  
 "MY_TABLE_NAME" 
 ; 
  
 String 
  
 viewName 
  
 = 
  
 "MY_VIEW_NAME" 
 ; 
  
 String 
  
 updateQuery 
  
 = 
  
 String 
 . 
 format 
 ( 
 "SELECT TimestampField, StringField FROM %s.%s" 
 , 
  
 datasetName 
 , 
  
 tableName 
 ); 
  
 updateViewQuery 
 ( 
 datasetName 
 , 
  
 viewName 
 , 
  
 updateQuery 
 ); 
  
 } 
  
 public 
  
 static 
  
 void 
  
 updateViewQuery 
 ( 
 String 
  
 datasetName 
 , 
  
 String 
  
 viewName 
 , 
  
 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 
 (); 
  
 // Retrieve existing view metadata 
  
  TableInfo 
 
  
 viewMetadata 
  
 = 
  
 bigquery 
 . 
  getTable 
 
 ( 
 TableId 
 . 
 of 
 ( 
 datasetName 
 , 
  
 viewName 
 )); 
  
 // Update view query 
  
  ViewDefinition 
 
  
 viewDefinition 
  
 = 
  
 viewMetadata 
 . 
  getDefinition 
 
 (); 
  
 viewDefinition 
 . 
  toBuilder 
 
 (). 
 setQuery 
 ( 
 query 
 ). 
 build 
 (); 
  
 // Set metadata 
  
 bigquery 
 . 
  update 
 
 ( 
 viewMetadata 
 . 
 toBuilder 
 (). 
 setDefinition 
 ( 
 viewDefinition 
 ). 
 build 
 ()); 
  
 System 
 . 
 out 
 . 
 println 
 ( 
 "View query updated successfully" 
 ); 
  
 } 
  
 catch 
  
 ( 
  BigQueryException 
 
  
 e 
 ) 
  
 { 
  
 System 
 . 
 out 
 . 
 println 
 ( 
 "View query was not updated. \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 and create a client 
 const 
  
 { 
 BigQuery 
 } 
  
 = 
  
 require 
 ( 
 ' @google-cloud/bigquery 
' 
 ); 
 const 
  
 bigquery 
  
 = 
  
 new 
  
  BigQuery 
 
 (); 
 async 
  
 function 
  
 updateViewQuery 
 () 
  
 { 
  
 // Updates a view named "my_existing_view" in "my_dataset". 
  
 /** 
 * TODO(developer): Uncomment the following lines before running the sample. 
 */ 
  
 // const datasetId = "my_existing_dataset" 
  
 // const tableId = "my_existing_table" 
  
 const 
  
 dataset 
  
 = 
  
 await 
  
 bigquery 
 . 
 dataset 
 ( 
 datasetId 
 ); 
  
 // This example updates a view into the USA names dataset to include state. 
  
 const 
  
 newViewQuery 
  
 = 
  
 `SELECT name, state 
 FROM \`bigquery-public-data.usa_names.usa_1910_current\` 
 LIMIT 10` 
 ; 
  
 // Retrieve existing view 
  
 const 
  
 [ 
 view 
 ] 
  
 = 
  
 await 
  
 dataset 
 . 
 table 
 ( 
 tableId 
 ). 
 get 
 (); 
  
 // Retrieve existing view metadata 
  
 const 
  
 [ 
 metadata 
 ] 
  
 = 
  
 await 
  
 view 
 . 
 getMetadata 
 (); 
  
 // Update view query 
  
 metadata 
 . 
 view 
  
 = 
  
 newViewQuery 
 ; 
  
 // Set metadata 
  
 await 
  
 view 
 . 
 setMetadata 
 ( 
 metadata 
 ); 
  
 console 
 . 
 log 
 ( 
 `View 
 ${ 
 tableId 
 } 
 updated.` 
 ); 
 }

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 .

  from 
  
 google.cloud 
  
 import 
  bigquery 
 
 client 
 = 
  bigquery 
 
 . 
  Client 
 
 () 
 view_id 
 = 
 "my-project.my_dataset.my_view" 
 source_id 
 = 
 "my-project.my_dataset.my_table" 
 view 
 = 
  bigquery 
 
 . 
  Table 
 
 ( 
 view_id 
 ) 
 # The source table in this example is created from a CSV file in Google 
 # Cloud Storage located at 
 # `gs://cloud-samples-data/bigquery/us-states/us-states.csv`. It contains 
 # 50 US states, while the view returns only those states with names 
 # starting with the letter 'M'. 
  view 
 
 . 
  view_query 
 
 = 
 f 
 "SELECT name, post_abbr FROM ` 
 { 
 source_id 
 } 
 ` WHERE name LIKE 'M%'" 
 # Make an API request to update the query property of the view. 
 view 
 = 
 client 
 . 
  update_table 
 
 ( 
 view 
 , 
 [ 
 "view_query" 
 ]) 
 print 
 ( 
 f 
 "Updated 
 { 
  view 
 
 . 
 table_type 
 } 
 : 
 { 
 str 
 ( 
  view 
 
 . 
 reference 
 ) 
 } 
 " 
 )

Updating a view's expiration time

You can set a default table expiration time at the dataset level (which affects both tables and views), or you can set a view's expiration time when the view is created. If you set the expiration when the view is created, the dataset's default table expiration is ignored. If you do not set a default table expiration at the dataset level, and you do not set an expiration when the view is created, the view never expires and you must delete the view manually.

At any point after the view is created, you can update the view's expiration time by:

Using the Google Cloud console
Using a Data definition language (DDL) statement written in GoogleSQL syntax
Using the bq command-line tool's bq update command
Calling the tables.patch API method
Using the client libraries

To update a view's expiration time:

Console

In the navigation pane, select your view.
On the view Details page, click the Detailstab.
To the right of View info, click the edit icon (pencil).
In the View infodialog, for View expiration, click Specify date.
In the date picker, enter the expiration date and time and then click Ok.
Click Update. The updated expiration time appears in the View infosection.

SQL

Use the ALTER VIEW SET OPTIONS DDL statement :

In the Google Cloud console, go to the BigQuerypage.

Go to BigQuery

In the query editor, enter the following statement:

  
 ALTER 
  
 VIEW 
  
  DATASET_ID 
 
 . 
  MY_VIEW 
 
  
 SET 
  
 OPTIONS 
  
 ( 
  
 expiration_timestamp 
  
 = 
  
 TIMESTAMP 
 ( 
 ' NEW_TIMESTAMP 
' 
 ));

Replace the following:

DATASET_ID : the ID of the dataset containing your view
MY_VIEW : the name of the view to be updated
NEW_TIMESTAMP : a TIMESTAMP value

Click Run.

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

bq

Issue the bq update command with the --expiration flag. If you are updating a view in a project other than your default project, add the project ID to the dataset name in the following format: project_id:dataset .

 bq 
  
 update 
  
 \ 
  
 -- 
 expiration 
  
  integer 
 
  
 \ 
  
  project_id 
 : 
 dataset 
 . 
 view

Replace the following::

integer : the default lifetime (in seconds) for the table. The minimum value is 3600 seconds (one hour). The expiration time evaluates to the current time plus the integer value.
project_id : your project ID
dataset : the name of the dataset containing the view you're updating
view : the name of the view you're updating

Examples

Enter the following command to update the expiration time of myview in mydataset to 5 days (432000 seconds). mydataset is in your default project.

 bq update \
    --expiration 432000 \
    mydataset.myview

Enter the following command to update the expiration time of myview in mydataset to 5 days (432000 seconds). mydataset is in myotherproject , not your default project.

 bq update \
    --expiration 432000 \
    myotherproject:mydataset.myview

API

Call the tables.patch method and use the expirationTime property in the table resource . Because the tables.update method replaces the entire table resource, the tables.patch method is preferred. When you use the REST API, the view's expiration is expressed in milliseconds.

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" 
  
 "time" 
  
 "cloud.google.com/go/bigquery" 
 ) 
 // updateTableExpiration demonstrates setting the table expiration of a table to a specific point in time 
 // in the future, at which time it will be deleted. 
 func 
  
 updateTableExpiration 
 ( 
 projectID 
 , 
  
 datasetID 
 , 
  
 tableID 
  
 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 
 () 
  
 tableRef 
  
 := 
  
 client 
 . 
 Dataset 
 ( 
 datasetID 
 ). 
 Table 
 ( 
 tableID 
 ) 
  
 meta 
 , 
  
 err 
  
 := 
  
 tableRef 
 . 
 Metadata 
 ( 
 ctx 
 ) 
  
 if 
  
 err 
  
 != 
  
 nil 
  
 { 
  
 return 
  
 err 
  
 } 
  
 update 
  
 := 
  
 bigquery 
 . 
  TableMetadataToUpdate 
 
 { 
  
 ExpirationTime 
 : 
  
 time 
 . 
 Now 
 (). 
 Add 
 ( 
 time 
 . 
 Duration 
 ( 
 5 
 * 
 24 
 ) 
  
 * 
  
 time 
 . 
 Hour 
 ), 
  
 // table expiration in 5 days. 
  
 } 
  
 if 
  
 _ 
 , 
  
 err 
  
 = 
  
 tableRef 
 . 
 Update 
 ( 
 ctx 
 , 
  
 update 
 , 
  
 meta 
 . 
 ETag 
 ); 
  
 err 
  
 != 
  
 nil 
  
 { 
  
 return 
  
 err 
  
 } 
  
 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 .

  Table 
  
 beforeTable 
  
 = 
  
 bigquery 
 . 
 getTable 
 ( 
 datasetName 
 , 
  
 tableName 
 ); 
 // Set table to expire 5 days from now. 
 long 
  
 expirationMillis 
  
 = 
  
 DateTime 
 . 
 now 
 (). 
 plusDays 
 ( 
 5 
 ). 
 getMillis 
 (); 
 TableInfo 
  
 tableInfo 
  
 = 
  
 beforeTable 
 . 
 toBuilder 
 () 
  
 . 
 setExpirationTime 
 ( 
 expirationMillis 
 ) 
  
 . 
 build 
 (); 
 Table 
  
 afterTable 
  
 = 
  
 bigquery 
 . 
 update 
 ( 
 tableInfo 
 );

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 
  
 updateTableExpiration 
 () 
  
 { 
  
 // Updates a table's expiration. 
  
 /** 
 * TODO(developer): Uncomment the following lines before running the sample. 
 */ 
  
 // const datasetId = 'my_dataset', // Existing dataset 
  
 // const tableId = 'my_table', // Existing table 
  
 // const expirationTime = Date.now() + 1000 * 60 * 60 * 24 * 5 // 5 days from current time in ms 
  
 // Retreive current table metadata 
  
 const 
  
 table 
  
 = 
  
 bigquery 
 . 
 dataset 
 ( 
 datasetId 
 ). 
 table 
 ( 
 tableId 
 ); 
  
 const 
  
 [ 
 metadata 
 ] 
  
 = 
  
 await 
  
 table 
 . 
 getMetadata 
 (); 
  
 // Set new table expiration to 5 days from current time 
  
 metadata 
 . 
 expirationTime 
  
 = 
  
 expirationTime 
 . 
 toString 
 (); 
  
 const 
  
 [ 
 apiResponse 
 ] 
  
 = 
  
 await 
  
 table 
 . 
 setMetadata 
 ( 
 metadata 
 ); 
  
 const 
  
 newExpirationTime 
  
 = 
  
 apiResponse 
 . 
 expirationTime 
 ; 
  
 console 
 . 
 log 
 ( 
 ` 
 ${ 
 tableId 
 } 
 expiration: 
 ${ 
 newExpirationTime 
 } 
 ` 
 ); 
 }

Python

Updating a view's expiration is the same process as updating a table's expiration.

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 .

  from 
  
 google.cloud 
  
 import 
  bigquery 
 
 client 
 = 
  bigquery 
 
 . 
  Client 
 
 () 
 # TODO(dev): Change table_id to the full name of the table you want to update. 
 table_id 
 = 
 "your-project.your_dataset.your_table_name" 
 # TODO(dev): Set table to expire for desired days days from now. 
 expiration 
 = 
 datetime 
 . 
 datetime 
 . 
 now 
 ( 
 datetime 
 . 
 timezone 
 . 
 utc 
 ) 
 + 
 datetime 
 . 
 timedelta 
 ( 
 days 
 = 
 5 
 ) 
 table 
 = 
 client 
 . 
  get_table 
 
 ( 
 table_id 
 ) 
 # Make an API request. 
 table 
 . 
 expires 
 = 
 expiration 
 table 
 = 
 client 
 . 
  update_table 
 
 ( 
 table 
 , 
 [ 
 "expires" 
 ]) 
 # API request 
 print 
 ( 
 f 
 "Updated 
 { 
 table_id 
 } 
 , expires 
 { 
 table 
 . 
 expires 
 } 
 ." 
 )

Manage logical views

Before you begin

Update a view

Required permissions

Updating a view's SQL query

Console

bq

API

Go

Java

Node.js

Python

Updating a view's expiration time

Console

SQL

bq

API

Go

Java

Node.js

Python

Updating a view's description

Console

SQL

bq

API

Go

Java

Node.js

Python

Copy views

Required permissions

Copy a view

Rename a view

Delete views

Required permissions

Delete a view

Console

SQL

bq

API

C#

Go

Java

Node.js

PHP

Python

Ruby

Restore a view

View security

What's next