Creating views

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's bq 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 the bq 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

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

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

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

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. 

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. 

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

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.

Next steps