Updating view properties
This document describes how to update view properties or metadata. After creating a view, you can update the following view properties:
Before you begin
Grant Identity and Access Management (IAM) roles that give users the necessary permissions to perform each task in this document.
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.
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.
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 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
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
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 standard SQL 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.
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 console
- Using a Data definition language (DDL) statement written in standard SQL syntax
- 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.
Use a DDL statement to update a view's expiration time:
In the console, go to the BigQuery page.
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 Running interactive queries.
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.
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 console
- Using a Data definition language (DDL) statement written in standard SQL syntax
- 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 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.
Use a DDL statement to update a view's description:
In the console, go to the BigQuery page.
In the query editor, enter the following statement:
ALTER VIEW DATASET_ID.MY_VIEW SET OPTIONS ( description = 'NEW_DESCRIPTION');
Replace the following:
- DATASET_ID: the ID of the dataset containing your view
- MY_VIEW: the name of the view to be updated
- NEW_DESCRIPTION: the new view description
Click
Run.
For more information about how to run queries, see Running interactive queries.
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
Replace the following:
- description: the text describing the view in quotes
- 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 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.
View security
To control access to views in BigQuery, see Controlling access to views.
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.