Updating view properties

This document describes how to update view properties in BigQuery. After creating a view, you can update the following view properties:

• SQL query
• Expiration time
• Description
• Labels

Required permissions

Updating a view requires the same permissions as updating a table — you must have WRITER access at the dataset level, or you must be assigned a project-level IAM role that includes requires bigquery.tables.update permissions. The following predefined, project-level IAM roles include bigquery.tables.update permissions:

• bigquery.dataEditor
• bigquery.dataOwner
• bigquery.admin

In addition, because the bigquery.user role has bigquery.datasets.create permissions, a user assigned to the bigquery.user role can update any view that user creates in the dataset. When a user assigned to the bigquery.user role creates a dataset, that user is given OWNER access to the dataset. OWNER access to a dataset gives the user full control over it and all the tables and views in it.

For more information on IAM roles and permissions in BigQuery, see: Access control.

Updating a view's SQL query

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

• Using the GCP Console or the classic BigQuery web UI
• Using the command-line tool's bq update command
• Calling the tables.patch API method.

You can change the SQL dialect from legacy SQL to standard SQL in the CLI or API. You cannot update a legacy SQL view to standard SQL in the GCP Console or the classic BigQuery web UI.

To update a view's SQL query:

Console

1. Select the view in the Resources panel.

2. Click over to the Details tab.

   

3. Above the Query box, click the Edit query button. Click Open in the dialog that appears.

   

4. Edit the SQL query in the Query editor box and then click Save view.

   

5. Make sure all the fields are correct in the Save view dialog before clicking Save.

Classic UI

1. Select the view.

2. In the View Details panel, click Details.

3. Below the Query box, click Edit Query.

4. Edit the SQL query in the Query box and then click Save View.

   

Command-line

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

If your query references external user-defined function resources stored in Google 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 Standard SQL 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]

Where:

• [PATH_TO_FILE] is the URI or local filesystem 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] is a valid standard SQL query.
• [PROJECT_ID] is your project ID.
• [DATASET] is a dataset that contains the view.
• [VIEW] is the name of the view you want to update.

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 .

View on GitHub Feedback

// To run this sample, you will need to create (or reuse) a context and
// an instance of the bigquery client.  For example:
// import "cloud.google.com/go/bigquery"
// ctx := context.Background()
// client, err := bigquery.NewClient(ctx, "your-project-id")
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
}

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 .

View on GitHub Feedback

# from google.cloud import bigquery
# client = bigquery.Client()
# project = 'my-project'
# source_dataset_id = 'my_source_dataset'
# source_table_id = 'us_states'
# shared_dataset_ref = client.dataset('my_shared_dataset')

# This example shows how to update a shared view of a source table of
# US States. The view's query will be updated to contain only states with
# names starting with 'M'.
view_ref = shared_dataset_ref.table("my_shared_view")
view = bigquery.Table(view_ref)
sql_template = 'SELECT name, post_abbr FROM `{}.{}.{}` WHERE name LIKE "M%"'
view.view_query = sql_template.format(project, source_dataset_id, source_table_id)
view = client.update_table(view, ["view_query"])  # API request

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 classic BigQuery web UI
  • Modifying view expiration times is not currently supported by the GCP Console
• Using the CLI's bq update command
• Calling the tables.patch API method.

When you update a view's expiration time, you must calculate the expiration based on the view's creation time. For example, if your view was created on January 3, 2018, and today is January 5, if you set the expiration time at 1 day using the BigQuery web UI, the expiration time is considered "in the past" and the view is deleted immediately.

To update a view's expiration time:

Console

You cannot add or update a view's expiration time when using the console web UI.

Classic UI

Option 1: Edit the expiration time manually

1. In the navigation pane, select your view.

2. On the View Details page, click Details.

3. For Expiration Time, click Edit.

4. In the Update Expiration dialog, click In and enter an expiration time in days.

5. Click OK. The updated expiration time appears on the Details page.

   

Option 2: Use a DDL statement

Data definition language (DDL) statements allow you to create and modify tables and views using standard SQL query syntax.

See more on Using Data Definition Language Statements.

Beta

This is a beta release of BigQuery support for the ALTER VIEW SET OPTIONS statement. This feature might be changed in backward-incompatible ways and is not subject to any SLA or deprecation policy.

1. Click Compose query.

2. Type your DDL statement into the New Query text area.

    #standardSQL
    ALTER VIEW mydataset.myview
    SET OPTIONS (
      -- Sets view expiration to timestamp 2025-02-03 12:34:56
      expiration_timestamp=TIMESTAMP "2025-02-03 12:34:56"
    );
    

Command-line

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]

Where:

• [INTEGER] is 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] is your project ID.
• [DATASET] is the name of the dataset that contains the view you're updating.
• [VIEW] is 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 (43200 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 (43200 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 to update the view's expiration in milliseconds. 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 .

View on GitHub Feedback

// To run this sample, you will need to create (or reuse) a context and
// an instance of the bigquery client.  For example:
// import "cloud.google.com/go/bigquery"
// ctx := context.Background()
// client, err := bigquery.NewClient(ctx, "your-project-id")
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
}

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 .

View on GitHub Feedback

import datetime
import pytz

# from google.cloud import bigquery
# client = bigquery.Client()
# table_ref = client.dataset('my_dataset').table('my_table')
# table = client.get_table(table_ref)  # API request

assert table.expires is None

# set table to expire 5 days from now
expiration = datetime.datetime.now(pytz.utc) + datetime.timedelta(days=5)
table.expires = expiration
table = client.update_table(table, ["expires"])  # API request

# expiration is stored in milliseconds
margin = datetime.timedelta(microseconds=1000)
assert expiration - margin <= table.expires <= expiration + margin

Updating a view's description

You can update a view's description by:

• Using the GCP Console or the classic BigQuery web UI
• Using the bq update CLI command
• Calling the tables.patch API method

To update a view's description:

Console

You cannot add a description when you create a view using the BigQuery web UI. After the view is created, you can add a description on the Details page.

1. In the Resources list, select your view.

2. Click over to the Details tab.

1. Click the pencil icon next to Description.

1. Enter a description in the dialog box. Click Update to save the new description.

Classic UI

Option 1: Change the description manually

1. In the navigation pane, select your view.

2. On the View Details page, click Details.

3. In the Description section, click Describe this view to open the description box.

4. Enter a description in the box. When you click away from the box, the text is saved.

   

Option 2: Use a DDL statement

Data definition language (DDL) statements allow you to create and modify tables and views using standard SQL query syntax.

See more on Using Data Definition Language Statements.

Beta

This is a beta release of BigQuery support for the ALTER VIEW SET OPTIONS statement. This feature might be changed in backward-incompatible ways and is not subject to any SLA or deprecation policy.

1. Click Compose query.

2. Type your DDL statement into the New Query text area.

    #standardSQL
    ALTER VIEW mydataset.myview
    SET OPTIONS (
      description="Description of myview"
    );
    

Command-line

Issue the bq update command with the --description 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 --description "[DESCRIPTION]" [PROJECT_ID]:[DATASET].[VIEW]

Where:

• [DESCRIPTION] is the text describing the view in quotes.
• [PROJECT_ID] is your project ID.
• [DATASET] is the name of the dataset that contains the view you're updating.
• [VIEW] is the name of the view you're updating.

Examples:

Enter the following command to change the description of myview in mydatasetto "Description of myview." mydataset is in your default project.

bq update --description "Description of myview" mydataset.myview

Enter the following command to change the description of myview in mydataset to "Description of myview." mydataset is in myotherproject, not your default project.

bq update --description "Description of myview" myotherproject:mydataset.myview

API

Call the tables.patch method and use the description property to update the view's description in the table resource. 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 .

View on GitHub Feedback

// To run this sample, you will need to create (or reuse) a context and
// an instance of the bigquery client.  For example:
// import "cloud.google.com/go/bigquery"
// ctx := context.Background()
// client, err := bigquery.NewClient(ctx, "your-project-id")
tableRef := client.Dataset(datasetID).Table(tableID)
meta, err := tableRef.Metadata(ctx)
if err != nil {
	return err
}
update := bigquery.TableMetadataToUpdate{
	Description: "Updated description.",
}
if _, err = tableRef.Update(ctx, update, meta.ETag); err != nil {
	return err
}

Java

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

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 .

View on GitHub Feedback

// String datasetName = "my_dataset_name";
// String tableName = "my_table_name";
// String newDescription = "new_description";

Table beforeTable = bigquery.getTable(datasetName, tableName);
TableInfo tableInfo = beforeTable.toBuilder()
    .setDescription(newDescription)
    .build();
Table afterTable = bigquery.update(tableInfo);

Python

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

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 .

View on GitHub Feedback

# from google.cloud import bigquery
# client = bigquery.Client()
# table_ref = client.dataset('my_dataset').table('my_table')
# table = client.get_table(table_ref)  # API request

assert table.description == "Original description."
table.description = "Updated description."

table = client.update_table(table, ["description"])  # API request

assert table.description == "Updated description."

Next steps

• For more information on managing views including copying a view and deleting a view, see Managing views.
• For information on creating views, see Creating views.
• For information on creating an authorized view, see Creating authorized views.
• For information on listing views, see Listing views.
• For information on getting view metadata, see Getting information about views.