Managing views
This document describes how to manage views in BigQuery. You can
manage your BigQuery views in the following ways:
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:
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
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 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.
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
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
Use the
ALTER VIEW SET OPTIONS
DDL statement
:
In the Google Cloud console, go to the
BigQuery
page.
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
play_circle
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.
Python
Updating a view's expiration is the same process as updating a table's
expiration.
Updating a view's description
You can update a view's description 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 description:
Console
You cannot add a description when you create a view using the Google 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
Use the
ALTER VIEW SET OPTIONS
DDL statement
:
In the Google Cloud console, go to the
BigQuery
page.
Go to BigQuery
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
play_circle
Run
.
For more information about how to run queries, see
Run an interactive 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
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.
Java
Updating a view's description is the same process as updating a table's
description.
Python
Updating a view's description is the same process as updating a table's
description.
Copy views
You can copy a view using the Google Cloud console.
You cannot copy a view by using the bq command-line tool, the REST API, or the client
libraries, but you can
copy a view in the target dataset
.
Required permissions
To copy a view in the Google Cloud console, you need IAM permissions on the
source and destination datasets.
On the source dataset, you need the following:
bigquery.tables.get
bigquery.tables.getData
(required to access the tables referenced by the view's SQL query)
On the destination dataset, you need the following:
bigquery.tables.create
(lets you create a copy of the view in the destination dataset)
Each of the following predefined IAM roles includes the
permissions that you need in order to copy a view:
roles/bigquery.dataEditor
roles/bigquery.dataOwner
roles/bigquery.admin
Additionally, if you have the
bigquery.datasets.create
permission, you can copy views in the datasets that you create. You also need access to the destination dataset unless you created it.
For more information on IAM roles and permissions in
BigQuery, see
Predefined roles and permissions
.
Copy a view
To copy a view:
In the
Explorer
panel, expand your project and dataset, then select
the view.
In the details panel, click
Copy view
.
In the
Copy view
dialog:
Limits for copy jobs apply. For more information, see
Quotas and limits
.
Rename a view
Currently, you can rename a view only when you use the Google Cloud console to
copy the view. For instructions on renaming a view when you copy it, see
Copying a view
.
You cannot change the name of an existing view by using the bq command-line tool, the API,
or the client libraries. Instead, you must
recreate the view
with the new name.
Delete views
You can delete a view by:
- Using the Google Cloud console
- Using the bq command-line tool's
bq rm
command
- Calling the
tables.delete
API
method
Currently, using any available method, you can only delete one view at a time.
To automatically delete views after a specified period of time, set the default
expiration time
at the dataset level or set the expiration time when you
create the view
.
When you delete an
authorized view
, it
might take up to 24 hours to remove the deleted view from the
authorized views
list of the source dataset.
Deleting a view also deletes any permissions associated with this view. When
you recreate a deleted view, you must also manually
reconfigure any access permissions
previously associated with it.
Required permissions
To delete a view, you need the following IAM permissions:
Each of the following predefined IAM roles includes the permissions that you need in order to delete a view:
roles/bigquery.dataOwner
roles/bigquery.dataEditor
roles/bigquery.admin
Additionally, if you have the
bigquery.datasets.create
permission, you can delete views in the datasets that you create.
For more information on IAM roles and permissions in
BigQuery, see
Predefined roles and permissions
.
Delete a view
To delete a view:
Console
In the Google Cloud console, go to the BigQuery page.
Go to BigQuery
In the
Explorer
panel, expand your project and dataset, then select
the view.
In the details panel, click
Delete view
.
Type
"delete"
in the dialog, and click
Delete
to confirm.
SQL
Use the
DROP VIEW
DDL statement
:
In the Google Cloud console, go to the
BigQuery
page.
Go to BigQuery
In the query editor, enter the following statement:
DROP VIEW mydataset.myview;
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
play_circle
Run
.
For more information about how to run queries, see
Run an interactive query
.
bq
Use the
bq rm
command with the
--table
flag (or
-t
shortcut) to delete
a view. When you use the bq command-line tool to remove a view, you must confirm the action.
You can use the
--force
flag (or
-f
shortcut) to skip confirmation.
If the view is in a dataset in a project other than your default
project, add the project ID to the dataset name in the following format:
project_id:dataset
.
bq rm \
-f \
-t \
project_id:dataset.view
Where:
- project_id
is your project ID.
- dataset
is the name of the dataset that contains the table.
- view
is the name of the view you're deleting.
Examples:
You can use the bq command-line tool to run
bq
commands.
In the Google Cloud console, activate
Cloud Shell
.
Activate Cloud Shell
Enter the following command to delete
myview
from
mydataset
.
mydataset
is in your default project.
bq rm -t mydataset.myview
Enter the following command to delete
myview
from
mydataset
.
mydataset
is in
myotherproject
, not your default project.
bq rm -t myotherproject:mydataset.myview
Enter the following command to delete
myview
from
mydataset
.
mydataset
is in your default project. The command uses the
-f
shortcut to bypass
confirmation.
bq rm -f -t mydataset.myview
API
Call the
tables.delete
API method and specify the view to delete using the
tableId
parameter.
View security
To control access to views in BigQuery, see
Authorized views
.
What's next