This document describes how to manage views in BigQuery. You can manage your BigQuery views in the following ways:
Copying views
Currently, you can copy a view only by using the Cloud Console.
You cannot copy a view by using the bq
command-line tool, the REST API, or the client
libraries. Instead, you must recreate the view in the
target dataset. To assist you in recreating the view, you can use the
bq
command-line tool to copy the view's SQL query.
Required permissions
At a minimum, to copy a view in the Cloud Console, you must be granted the following permissions.
On the source dataset:
bigquery.tables.get
to copy the view's metadatabigquery.tables.getData
to copy the view's SQL query
Additional permissions such as bigquery.tables.getData
may be required to
access the tables referenced by the view's SQL query.
On the destination dataset:
bigquery.tables.create
to create the copy of the view in the destination dataset
The following predefined IAM roles include
bigquery.tables.create
, bigquery.tables.get
, and bigquery.tables.getData
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 copy views from the
source dataset, but access to the destination dataset is still required unless
the user also created the destination dataset.
For more information on IAM roles and permissions in BigQuery, see Predefined roles and permissions.
Copying a view
To copy a view:
Console
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:
- In the Source section, verify that your Project name, Dataset name, and Table name are correct.
In the Destination section:
- For Project name, choose the project to which you are copying the view.
- For Dataset name, choose the dataset that will contain the copied view.
- For Table name, enter the name of the view. You can rename the view by entering a new view name in the box. If you enter a new name, it must follow the view naming rules.
Click Copy.
bq
The bq
command-line tool does not support copying a view. Instead, you
must recreate the view in the target dataset. To assist you in recreating
the view, you can copy the SQL query used to define the view.
To copy the SQL query that defines a view, issue the bq show
command. The
--format
flag can be used to control the output. If you are getting
information about a view in a project other than your default project, add
the project ID to the dataset in the following format:
project_id:dataset
. To write the view properties to
a file, add > path_to_file
to the command.
bq show \ --format=prettyjson \ project_id:dataset.view > path_to_file
Where:
- project_id is your project ID.
- dataset is the name of the dataset.
- view is the name of the view.
- path_to_file is the path to the output file on your local machine.
Examples:
Enter the following command to display information about myview
in
mydataset
. mydataset
is in your default project.
bq show --format=prettyjson mydataset.myview
Enter the following command to display information about myview
in
mydataset
. mydataset
is in myotherproject
, not your default project.
The view properties are written to a local file — /tmp/myview.json
.
bq show \
--format=prettyjson \
myotherproject:mydataset.myview > /tmp/myview.json
API
The REST API does not support copying a view. Instead, you must recreate the view in the target dataset. For information on creating a view by using the REST API, see Creating a view.
Renaming a view
Currently, you can rename a view only when you use the 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.
Deleting views
You can delete a view by:
- Using the Cloud Console
- Using the
bq
command-line tool'sbq 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.
Required permissions
At a minimum, to delete a view, you must be granted bigquery.tables.delete
and bigquery.tables.get
permissions. The following predefined
IAM roles include bigquery.tables.delete
and
bigquery.tables.get
permissions:
bigquery.dataOwner
bigquery.dataEditor
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 delete views in the
dataset.
For more information on IAM roles and permissions in BigQuery, see Predefined roles and permissions.
Deleting a view
To delete a view:
Console
In the Explorer panel, expand your project and dataset, then select the view.
In the details panel, click Delete view.
Type the name of the view in the dialog, and click Delete to confirm.
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:
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.
C#
Before trying this sample, follow the C# setup instructions in the BigQuery Quickstart Using Client Libraries. For more information, see the BigQuery C# API reference documentation.
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.
PHP
Before trying this sample, follow the PHP setup instructions in the BigQuery Quickstart Using Client Libraries. For more information, see the BigQuery PHP 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.
Ruby
Before trying this sample, follow the Ruby setup instructions in the BigQuery Quickstart Using Client Libraries. For more information, see the BigQuery Ruby API reference documentation.
Next steps
- 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.
- For information on updating views, see Updating views.