This document describes how to update view properties or metadata. After creating a view, you can update the following view properties:
Required permissions
At a minimum, to update a view, you must be granted bigquery.tables.update
and
bigquery.tables.get
permissions. If you are updating the view's SQL query, you
must also have permissions to query any tables referenced by the view's SQL
query.
The following predefined IAM roles include
bigquery.tables.update
and bigquery.tables.get
permissions:
bigquery.dataEditor
bigquery.dataOwner
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 update tables and
views in the dataset.
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 Cloud Console
- Using the
bq
command-line tool'sbq update
command - Calling the
tables.patch
API method - Using the client libraries
You can change the SQL dialect from legacy SQL to standard SQL in the API or
bq
command-line tool. You cannot update a legacy SQL view to standard SQL in the
Cloud Console.
To update a view's SQL query:
Console
In the Explorer panel, expand your project and dataset, then select the view.
Click the Details tab.
Above the Query box, click the Edit query button. Click Open in the dialog that appears.
Edit the SQL query in the Query editor box and then click Save view.
Make sure all the fields are correct in the Save view dialog and then click Save.
bq
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 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 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 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.
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.
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.
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.
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
bq
command-line tool'sbq 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 Details tab.
To the right of View info, click the edit icon (pencil).
In the View info dialog, 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 info section.
SQL
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.
To use a DDL statement in the Cloud Console:
Click Compose new query.
Type your DDL statement into the Query editor text area.
ALTER VIEW mydataset.myview SET OPTIONS ( -- Sets view expiration to timestamp 2025-02-03 12:34:56 in the America/Los Angeles time zone expiration_timestamp=TIMESTAMP("2025-02-03 12:34:56", "America/Los Angeles") );
Click Run 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
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 (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.
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.
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.
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.
Updating a view's description
You can update a view's description by:
- Using the Cloud Console
- Using the
bq
command-line tool'sbq update
command - Calling the
tables.patch
API method - Using the client libraries
To update a view's description:
Console
You cannot add a description when you create a view using the Cloud Console. After the view is created, you can add a description on the Details page.
In the Explorer panel, expand your project and dataset, then select the view.
Click the Details tab.
Click the pencil icon next to Description.
Enter a description in the dialog box. Click Update to save the new description.
SQL
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.
To use a DDL statement in the Cloud Console:
Click Compose new query.
Type your DDL statement into the Query editor text area.
ALTER VIEW mydataset.myview SET OPTIONS ( description="Description of myview" );
Click Run query.
bq
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
mydataset
to "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.
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.
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.
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.
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.