This document describes how to create views in BigQuery.
You can create a view in BigQuery in the following ways:
- Using the Cloud Console.
- Using the
bq
command-line tool'sbq mk
command. - Calling the
tables.insert
API method. - Using the client libraries.
- Submitting a
CREATE VIEW
Data Definition Language (DDL) statement.
View naming
When you create a view in BigQuery, the view name must be unique per dataset. The view name can contain the following:
- Up to 1,024 characters
- Letters (uppercase or lowercase), numbers, and underscores
View limitations
BigQuery views are subject to the following limitations:
- Views are read-only. You cannot run DML (insert, update, delete) queries against a view.
- The dataset that contains your view and the dataset that contains the tables referenced by the view must be in the same location.
- You cannot run a BigQuery job that exports data from a view.
- You cannot use the
TableDataList
JSON API method to retrieve data from a view. For more information, see Tabledata: list. - You cannot mix standard SQL and legacy SQL queries when using views. A standard SQL query cannot reference a view defined using legacy SQL syntax.
- You cannot reference query parameters in views.
- The schemas of the underlying tables are stored with the view when the view is created. If columns are added, deleted, and so on after the view is created, the reported schema will be inaccurate until the view is updated. Even though the reported schema may be inaccurate, all submitted queries produce accurate results.
- You cannot automatically update a legacy SQL view to standard SQL syntax. To
modify the query used to define a view, use the Edit query option in the
Cloud Console, use the
bq update --view
command in thebq
command-line tool, use the client libraries, or use the update or patch API methods. - You cannot include a temporary user-defined function or a temporary table in the SQL query that defines a view.
- You cannot reference a view in a wildcard table query.
For information about quotas and limits that apply to views, see View limits.
Required permissions
Views are treated as table resources in BigQuery so creating a
view requires the same permissions as creating a table. At a minimum, to create
a view, you must be granted bigquery.tables.create
permissions. The following
predefined IAM roles include bigquery.tables.create
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 create views in the
dataset.
For more information on IAM roles and permissions in BigQuery, see Predefined roles and permissions.
Creating a view
You can create a view by composing a SQL query that is used to define the data accessible to the view.
In the standard SQL query used to create a view, you must include the
project ID in table and view references in the form
`PROJECT_ID.DATASET.TABLE`
.
Standard SQL requires explicit project IDs to avoid ambiguity when views are
queried from different projects.
To create a view:
Console
After running a query, click the Save view button above the query results window to save the query as a view.
In the Save view dialog:
- For Project name, select the project that will store the view.
- For Dataset name, choose the dataset that will contain the view. The dataset that contains your view and the dataset that contains the tables referenced by the view must be in the same location.
- For Table name, enter the name of the view.
- Click Save.
bq
Use the bq mk
command
with the --view
flag. For standard SQL queries,
add the --use_legacy_sql
flag and set it to false
. Optional
parameters include --expiration
, --description
, and --label
.
If your query references external user-defined function (UDF) 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 about
using UDFs, see
UDFs and the bq
command-line tool.
If you are creating a view in a project other than your default project,
specify the project ID using the --project_id
flag.
bq mk \ --use_legacy_sql=false \ --view_udf_resource=PATH_TO_FILE \ --expiration INTEGER \ --description "DESCRIPTION" \ --label KEY:VALUE \ --view 'QUERY' \ --project_id PROJECT_ID \ DATASET.VIEW
Replace the following:
PATH_TO_FILE
is the URI or local file system path to a code file to be loaded and evaluated immediately as a UDF resource used by the view. Repeat the flag to specify multiple files.INTEGER
sets the lifetime (in seconds) for the view. IfINTEGER
is0
, the view doesn't expire. If you don't include the--expiration
flag, BigQuery creates the view with the dataset's default table lifetime.DESCRIPTION
is a description of the view in quotes.KEY:VALUE
is the key-value pair that represents a label. Repeat the--label
flag to specify multiple labels.QUERY
is a valid query. For standard SQL views, the query must include the project ID in table and view references in the form`PROJECT_ID.DATASET.TABLE`
.PROJECT_ID
is your project ID (if you do not have a default project configured).DATASET
is a dataset in your project.VIEW
is the name of the view that you want to create.
Examples:
Enter the following command to create a view named myview
in
mydataset
in your default project. The expiration time is set to
3600 seconds (1 hour), the description is set to This is my view
, and the
label is set to organization:development
. The query used to create the view
queries data from the USA Name Data public dataset.
bq mk \
--use_legacy_sql=false \
--expiration 3600 \
--description "This is my view" \
--label organization:development \
--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 create a view named myview
in
mydataset
in myotherproject
. The description is set to
This is my view
, the label is set to organization:development
,
and the view's expiration is set to the dataset's default table
expiration.
The query used to create the view
queries data from the USA Name Data public dataset.
bq mk \
--use_legacy_sql=false \
--description "This is my view" \
--label organization:development \
--project_id myotherproject \
--view \
'SELECT
name,
number
FROM
`bigquery-public-data.usa_names.usa_1910_current`
WHERE
gender = "M"
ORDER BY
number DESC' \
mydataset.myview
After the view is created, you can update the view's expiration, description, and labels. For more information, see Updating views.
API
Call the tables.insert
method
with a table resource that
contains a view
property.
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.
After you create the view, you query it like you query a table.
Next steps
- For information about creating an authorized view, see Creating authorized views.
- For information about listing views, see Listing views.
- For information about getting view metadata, see Getting information about views.
- For information about updating views, see Updating views.
- For more information about managing views, see Managing views.