Creating views

This document describes how to create views in BigQuery.

You can create a view in BigQuery by:

• Using the GCP Console or classic BigQuery web UI
• Using the command line tool's bq mk command
• Calling the tables.insert API method
• 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 up to 1,024 characters
• Contain letters (upper or lower case), numbers, and underscores

View limitations

BigQuery views are subject to the following limitations:

• 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.
• 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 console or the classic BigQuery web UI, use the bq update --view CLI command or use the update or patch API methods.
• You cannot include a user-defined function in the SQL query that defines a view.
• You cannot reference a view in a wildcard table query.

For information on 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. To create a view, you you must have WRITER access at the dataset level, or you must be assigned a project-level IAM role that includes bigquery.tables.create permissions. The following predefined, project-level IAM roles include bigquery.tables.create permissions:

• bigquery.dataEditor
• bigquery.dataOwner
• bigquery.admin

In addition, because the bigquery.user role has bigquery.datasets.create permissions, a user assigned to the bigquery.user role can create views in any dataset that user creates. When a user assigned to the bigquery.user role creates a dataset, that user is given OWNER access to the dataset. OWNER access to a dataset gives the user full control over it and all the tables and views in it.

For more information on IAM roles and permissions in BigQuery, see Access Control. For more information on dataset-level roles, see Primitive roles for datasets.

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

1. After running a query, click the Save view button above the query results window to save the query as a view.

   

2. 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.

Note: When you create a view using the UI, you cannot add a label, description, or expiration time. You can add these optional properties when you create a view using the CLI or API. After you create a view using the BigQuery web UI, you can add an expiration, description, and labels.

Classic UI

1. After running a query, click the Save View button in the query results window to save the query as a view.

   

2. In the Save View dialog:

   • For Project, select the project that will store the view.
   • For Dataset, 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 ID, enter the name of the view.

     

   • Click OK.

Note: When you create a view using the UI, you cannot add a label, description, or expiration time. You can add these optional properties when you create a view using the CLI or API. After you create a view using the BigQuery web UI, you can add an expiration, description, and labels.

Command-line

Use the 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 resources stored in Google 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 creating a view in a project other than your default project, specify the project ID using the --project_id flag.

Note: The dataset that contains your view and the dataset that contains the tables referenced by the view must be in the same location.

bq mk --use_legacy_sql=false --view_udf_resource=[PATH_TO_FILE] --expiration [INTEGER] --description "[DESCRIPTION]" --label [KEY:VALUE, KEY:VALUE] --view '[QUERY]' --project_id [PROJECT_ID] [DATASET].[VIEW]

Where:

• [PATH_TO_FILE] is the URI or local filesystem 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.
• [INTEGER] is the default lifetime (in seconds) for the view. The minimum value is 3600 seconds (one hour). The expiration time evaluates to the current time plus the integer value. If you set the expiration time when you create a view, the dataset's default table expiration setting is ignored.
• [DESCRIPTION] is a description of the view in quotes.
• [KEY:VALUE] is the key:value pair that represents a label. You can enter multiple labels using a comma-separated list.
• [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 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 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' --project_id myotherproject mydataset.myview

After the view is created, you can update the view's expiration, description, and labels.

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 .

View on GitHub Feedback

// To run this sample, you will need to create (or reuse) a context and
// an instance of the bigquery client.  For example:
// import "cloud.google.com/go/bigquery"
// ctx := context.Background()
// client, err := bigquery.NewClient(ctx, "your-project-id")
meta := &bigquery.TableMetadata{
	// This example shows how to create a view of the shakespeare sample dataset, which
	// provides word frequency information.  This view restricts the results to only contain
	// results for works that contain the "king" in the title, e.g. King Lear, King Henry V, etc.
	ViewQuery: "SELECT word, word_count, corpus, corpus_date FROM `bigquery-public-data.samples.shakespeare` WHERE corpus LIKE '%king%'",
}
if err := client.Dataset(datasetID).Table(tableID).Create(ctx, meta); err != nil {
	return err
}

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 .

View on GitHub Feedback

# from google.cloud import bigquery
# client = bigquery.Client()
# project = 'my-project'
# source_dataset_id = 'my_source_dataset'
# source_table_id = 'us_states'
# shared_dataset_ref = client.dataset('my_shared_dataset')

# This example shows how to create a shared view of a source table of
# US States. The source table contains all 50 states, while the view will
# contain only states with names starting with 'W'.
view_ref = shared_dataset_ref.table("my_shared_view")
view = bigquery.Table(view_ref)
sql_template = 'SELECT name, post_abbr FROM `{}.{}.{}` WHERE name LIKE "W%"'
view.view_query = sql_template.format(project, source_dataset_id, source_table_id)
view = client.create_table(view)  # API request

print("Successfully created view at {}".format(view.full_table_id))

After creating the view, you query it like you query a table.

Next steps

• 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.
• For more information on managing views, see Managing views.