Create logical views

This document describes how to create logical views in BigQuery.

You can create a logical view in the following ways:

Using the Google Cloud console.
Using the bq command-line tool's bq mk command.
Calling the tables.insert API method.
Using the client libraries.
Submitting a CREATE VIEW data definition language (DDL) statement.

View limitations

BigQuery views are subject to the following limitations:

Views are read-only. For example, you can't run queries that insert, update, or delete data.
The dataset that contains your view and the dataset that contains the tables referenced by the view must be in the same location.
A reference inside of a view must be qualified with a dataset. The default dataset doesn't affect a view body.
You cannot use the TableDataList JSON API method to retrieve data from a view. For more information, see Tabledata: list.
You cannot mix GoogleSQL and legacy SQL queries when using views. A GoogleSQL 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, or modified after the view is created, the view isn't automatically updated and the reported schema will remain inaccurate until the view SQL definition is changed or the view is recreated. Even though the reported schema may be inaccurate, all submitted queries produce accurate results.
You cannot automatically update a legacy SQL view to GoogleSQL syntax. To modify the query used to define a view, you can use the following:
- The Edit query option in the Google Cloud console
- The bq update --view command in the bq command-line tool
- The BigQuery Client libraries
- 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.

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

Views are treated as table resources in BigQuery, so creating a view requires the same permissions as creating a table. You must also have permissions to query any tables that are referenced by the view's SQL query.

To create a view, you need the bigquery.tables.create IAM permission. The roles/bigquery.dataEditor predefined IAM role includes the permissions that you need to create a view.

Additionally, if you have the bigquery.datasets.create permission, you can create views in the datasets that you create. To create a view for data that you don't own, you must have bigquery.tables.getData permission for that table.

For more information on IAM roles and permissions in BigQuery, see Predefined roles and permissions.

View naming

When you create a view in BigQuery, the view name must be unique per dataset. The view name can:

Contain characters with a total of up to 1,024 UTF-8 bytes.
Contain Unicode characters in category L (letter), M (mark), N (number), Pc (connector, including underscore), Pd (dash), Zs (space). For more information, see General Category.

The following are all examples of valid view names: view 01, ग्राहक, 00_お客様, étudiant-01.

Caveats:

Table names are case-sensitive by default. mytable and MyTable can coexist in the same dataset, unless they are part of a dataset with case-sensitivity turned off.
Some view names and view name prefixes are reserved. If you receive an error saying that your view name or prefix is reserved, then select a different name and try again.
If you include multiple dot operators (.) in a sequence, the duplicate operators are implicitly stripped.

For example, this: project_name....dataset_name..table_name

Becomes this: project_name.dataset_name.table_name

Create a view

You can create a view by composing a SQL query that is used to define the data accessible to the view. The SQL query must consist of a SELECT statement. Other statement types (such as DML statements) and multi-statement queries aren't allowed in view queries.

To create a view:

Console

In the Google Cloud console, go to the BigQuery page.

Go to BigQuery
Click SQL query.
In the query editor, enter a valid SQL query.

Alternatively, you can open a saved query.
Click Save > Save view.
In the Save view dialog:
- In the Project menu, select a project to store the view.
- In the Dataset menu, select a dataset or create a new dataset to store the view. The destination dataset for a saved view must be in the same region as the source.
- In the Table field, enter the name of the view.
- Click Save.

SQL

Use the CREATE VIEW statement. The following example creates a view named usa_male_names from the USA names public dataset:

In the Google Cloud console, go to the BigQuery page.

Go to BigQuery

In the query editor, enter the following statement:

CREATE VIEW mydataset.usa_male_names(name, number) AS (
  SELECT
    name,
    number
  FROM
    `bigquery-public-data.usa_names.usa_1910_current`
  WHERE
    gender = 'M'
  ORDER BY
    number DESC
);

Click Run.

For more information about how to run queries, see Run an interactive query.

bq

Use the bq mk command with the --view flag. For GoogleSQL queries, add the --use_legacy_sql flag and set it to false. Some optional parameters include --add_tags, --expiration, --description, and --label. For a full list of parameters, see the bq mk command reference.

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.

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_1:VALUE_1 \
--add_tags=KEY_2:VALUE_2[,...] \
--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. If INTEGER is 0, 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_1:VALUE_1 is the key-value pair that represents a label. Repeat the --label flag to specify multiple labels.
KEY_2:VALUE_2 is the key-value pair that represents a tag. Add multiple tags under the same flag with commas between key:value pairs.
QUERY is a valid query.
PROJECT_ID is your project ID (if you don't 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.

Terraform

Use the google_bigquery_table resource.

To authenticate to BigQuery, set up Application Default Credentials. For more information, see Set up authentication for client libraries.

The following example creates a view named myview:

resource "google_bigquery_dataset" "default" {
  dataset_id                      = "mydataset"
  default_partition_expiration_ms = 2592000000  # 30 days
  default_table_expiration_ms     = 31536000000 # 365 days
  description                     = "dataset description"
  location                        = "US"
  max_time_travel_hours           = 96 # 4 days

  labels = {
    billing_group = "accounting",
    pii           = "sensitive"
  }
}

resource "google_bigquery_table" "default" {
  dataset_id          = google_bigquery_dataset.default.dataset_id
  table_id            = "myview"
  deletion_protection = false # set to "true" in production

  view {
    query          = "SELECT global_id, faa_identifier, name, latitude, longitude FROM `bigquery-public-data.faa.us_airports`"
    use_legacy_sql = false
  }

}

To apply your Terraform configuration in a Google Cloud project, complete the steps in the following sections.

Prepare Cloud Shell

Launch Cloud Shell.
Set the default Google Cloud project where you want to apply your Terraform configurations.

You only need to run this command once per project, and you can run it in any directory.
```
export GOOGLE_CLOUD_PROJECT=PROJECT_ID
```
Environment variables are overridden if you set explicit values in the Terraform configuration file.

Prepare the directory

Each Terraform configuration file must have its own directory (also called a root module).

In Cloud Shell, create a directory and a new file within that directory. The filename must have the .tf extension—for example main.tf. In this tutorial, the file is referred to as main.tf.
```
mkdir DIRECTORY && cd DIRECTORY && touch main.tf
```
If you are following a tutorial, you can copy the sample code in each section or step.

Copy the sample code into the newly created main.tf.

Optionally, copy the code from GitHub. This is recommended when the Terraform snippet is part of an end-to-end solution.
Review and modify the sample parameters to apply to your environment.
Save your changes.
Initialize Terraform. You only need to do this once per directory.
```
terraform init
```
Optionally, to use the latest Google provider version, include the -upgrade option:
```
terraform init -upgrade
```

Apply the changes

Review the configuration and verify that the resources that Terraform is going to create or update match your expectations:
```
terraform plan
```
Make corrections to the configuration as necessary.
Apply the Terraform configuration by running the following command and entering yes at the prompt:
```
terraform apply
```
Wait until Terraform displays the "Apply complete!" message.
Open your Google Cloud project to view the results. In the Google Cloud console, navigate to your resources in the UI to make sure that Terraform has created or updated them.

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.

To authenticate to BigQuery, set up Application Default Credentials. For more information, see Set up authentication for client libraries.

import (
	"context"
	"fmt"

	"cloud.google.com/go/bigquery"
)

// createView demonstrates creation of a BigQuery logical view.
func createView(projectID, datasetID, tableID string) error {
	// projectID := "my-project-id"
	// datasetID := "mydatasetid"
	// tableID := "mytableid"
	ctx := context.Background()

	client, err := bigquery.NewClient(ctx, projectID)
	if err != nil {
		return fmt.Errorf("bigquery.NewClient: %v", err)
	}
	defer client.Close()

	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
	}
	return nil
}

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.

To authenticate to BigQuery, set up Application Default Credentials. For more information, see Set up authentication for client libraries.

import com.google.cloud.bigquery.BigQuery;
import com.google.cloud.bigquery.BigQueryException;
import com.google.cloud.bigquery.BigQueryOptions;
import com.google.cloud.bigquery.TableId;
import com.google.cloud.bigquery.TableInfo;
import com.google.cloud.bigquery.ViewDefinition;

// Sample to create a view
public class CreateView {

  public static void main(String[] args) {
    // TODO(developer): Replace these variables before running the sample.
    String datasetName = "MY_DATASET_NAME";
    String tableName = "MY_TABLE_NAME";
    String viewName = "MY_VIEW_NAME";
    String query =
        String.format(
            "SELECT TimestampField, StringField, BooleanField FROM %s.%s", datasetName, tableName);
    createView(datasetName, viewName, query);
  }

  public static void createView(String datasetName, String viewName, String query) {
    try {
      // Initialize client that will be used to send requests. This client only needs to be created
      // once, and can be reused for multiple requests.
      BigQuery bigquery = BigQueryOptions.getDefaultInstance().getService();

      TableId tableId = TableId.of(datasetName, viewName);

      ViewDefinition viewDefinition =
          ViewDefinition.newBuilder(query).setUseLegacySql(false).build();

      bigquery.create(TableInfo.of(tableId, viewDefinition));
      System.out.println("View created successfully");
    } catch (BigQueryException e) {
      System.out.println("View was not created. \n" + e.toString());
    }
  }
}

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.

To authenticate to BigQuery, set up Application Default Credentials. For more information, see Set up authentication for client libraries.

// Import the Google Cloud client library and create a client
const {BigQuery} = require('@google-cloud/bigquery');
const bigquery = new BigQuery();

async function createView() {
  // Creates a new view named "my_shared_view" in "my_dataset".

  /**
   * TODO(developer): Uncomment the following lines before running the sample.
   */
  // const myDatasetId = "my_table"
  // const myTableId = "my_table"
  // const projectId = "bigquery-public-data";
  // const sourceDatasetId = "usa_names"
  // const sourceTableId = "usa_1910_current";
  const myDataset = await bigquery.dataset(myDatasetId);

  // For all options, see https://cloud.google.com/bigquery/docs/reference/v2/tables#resource
  const options = {
    view: `SELECT name 
    FROM \`${projectId}.${sourceDatasetId}.${sourceTableId}\`
    LIMIT 10`,
  };

  // Create a new view in the dataset
  const [view] = await myDataset.createTable(myTableId, options);

  console.log(`View ${view.id} created.`);
}

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.

To authenticate to BigQuery, set up Application Default Credentials. For more information, see Set up authentication for client libraries.

from google.cloud import bigquery

client = bigquery.Client()

view_id = "my-project.my_dataset.my_view"
source_id = "my-project.my_dataset.my_table"
view = bigquery.Table(view_id)

# The source table in this example is created from a CSV file in Google
# Cloud Storage located at
# `gs://cloud-samples-data/bigquery/us-states/us-states.csv`. It contains
# 50 US states, while the view returns only those states with names
# starting with the letter 'W'.
view.view_query = f"SELECT name, post_abbr FROM `{source_id}` WHERE name LIKE 'W%'"

# Make an API request to create the view.
view = client.create_table(view)
print(f"Created {view.table_type}: {str(view.reference)}")

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

View security

To control access to views in BigQuery, see Authorized views.

What's next

For information about creating an authorized view, see Creating authorized views.
For information about getting view metadata, see Getting information about views.
For more information about managing views, see Managing views.