Using Views

This document describes how to create, update, and manage views in Google BigQuery.

Overview

A view is a virtual table defined by a SQL query. You can query views in BigQuery using the web UI, the command-line tool, or the API. You can also use a view as a data source for a visualization tool such as Google Data Studio.

BigQuery's views are logical views, not materialized views. Because views are not materialized, the query that defines the view is run each time the view is queried. Queries are billed according to the total amount of data in all table fields referenced directly or indirectly by the top-level query. For more information, see query pricing.

SQL queries used to define views are subject to the standard query quotas.

Limitations

BigQuery views are subject to the following limitations:

  • 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 update a legacy SQL view to standard SQL in the BigQuery web UI. You can change the SQL language using the command-line tool bq update --view command or by using 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.
  • BigQuery supports up to 16 levels of nested views. If there are more than 16 levels, an INVALID_INPUT error returns.
  • You are limited to 1,000 authorized views per dataset.

Creating views

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

Creating a view requires the same permissions as creating a table: bigquery.tables.create. The bigquery.dataEditor and bigquery.dataOwner predefined IAM roles include bigquery.tables.create permissions. For more information on IAM roles in BigQuery, see access control.

You can create a view that accesses tables in another dataset in the same project, or you can create a view that accesses tables in a dataset in another project. When you create a view that accesses data in another project, use the fully qualified format:

  • Standard SQL: `PROJECT_NAME.DATASET.VIEW`
  • Legacy SQL: [PROJECT_NAME:DATASET.VIEW]

To create a view:

Web UI

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

    Save 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.
    • For Table ID, enter the name of the view.

    Save view dialog

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.

bq mk --use_legacy_sql=false --view='[SQL_QUERY]' [DATASET.VIEW]

Where:

  • [SQL_QUERY] is a valid BigQuery SQL query
  • [DATASET] is a dataset in your project
  • [VIEW] is the name of the view you want to create

If your query references external user-defined function resources stored in Google Cloud Storage or local files, use the --view_udf_resource flag to specify those resources. For example:

bq mk --view='[SQL_QUERY]' [DATASET.VIEW] --view_udf_resource=[PATH_TO_FILE]

API


Call tables.insert() with a table resource that contains a view property.

Getting information about views

You can get information about views to which you have access using the web UI, the CLI, and the API.

Getting information about views requires bigquery.tables.get permissions. The following BigQuery IAM roles include bigquery.tables.get permissions:

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

To get information about views:

Web UI

  1. Expand your dataset.

  2. Click the view name.

  3. Click Details. The View Details page displays the view's description, view information, and the SQL query that defines the view.

    View details

CLI

Issue the bq show command:

bq show [DATASET].[VIEW]

Where:

  • [DATASET] is the name of the dataset containing the view.
  • [VIEW] is the name of the view.

For example, the following command displays information about myview in dataset mydataset.

bq show mydataset.myview

API

Call the bigquery.tables.get API method and provide any relevant parameters.

Listing views

You can list views to which you have access using the web UI, the CLI, and the API.

Listing views requires bigquery.tables.list permissions. All BigQuery IAM roles include bigquery.tables.list permissions except for bigquery.jobUser.

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

To list the views in a dataset:

Web UI

  1. In the web UI, expand a dataset.

  2. Views and tables are listed in the navigation pane.

Command-line

Issue the bq ls command to list the tables and views in a dataset:

bq ls [DATASET]

Where:

  • [DATASET] is the name of the dataset containing the view.

API

To list views using the API, call the bigquery.tables.list API method.

Updating views

You can modify view properties using the web UI, CLI, or API. You can change:

  • The SQL query that defines the view
  • The view labels

Updating a view requires the same permissions as editing a table - bigquery.tables.update. The bigquery.dataEditor and bigquery.dataOwner predefined IAM roles include bigquery.tables.update permissions. For more information on IAM roles in BigQuery, see Access Control.

Updating a view's SQL query

You can update the SQL query used to define a view using the web UI, CLI, or API. If you are changing the SQL dialect from legacy SQL to standard SQL, you can use the command-line tool bq update --view command or the update or patch API methods. You cannot update a legacy SQL view to standard SQL in the BigQuery web UI.

To update a view's SQL query:

Web UI

  1. Select the view.

  2. In the View Details panel, click Details.

  3. Below the Query box, click Edit Query.

  4. Edit the SQL query in the Query box and then click Save View.

    Update view

Command-line


Use the update command with the --view flag. To use standard SQL or to update the query dialect from legacy SQL to standard SQL, include the --use_legacy_sql flag and set it to false.

bq update --use_legacy_sql=false --view='[SQL_QUERY]' [DATASET.VIEW]

Where:

  • [SQL_QUERY] is a valid BigQuery SQL query
  • [DATASET] is a dataset in your project
  • [VIEW] is the name of the view you want to create

If your query references external user-defined function resources stored in Google Cloud Storage or local files, use the --view_udf_resource flag to specify those resources. For example:

bq update --view='[SQL_QUERY]' [DATASET.VIEW] --view_udf_resource=[PATH_TO_FILE]

API

You can update a view by calling the tables.update or tables.patch methods in the BigQuery API.

Assigning access controls to views

You cannot assign access controls directly to views. When you create a view in a dataset, the dataset's access controls determine the users and groups allowed to access the view.

For example, if you assign the bigquery.dataOwner role to a user at the project level, that user can create, update, get, and delete views in all of the project's datasets. If you assign the bigquery.dataOwner role at the dataset level, the user can create, update, get, and delete views only in that dataset. For more information about project-level IAM roles and dataset access controls, see access control.

For more information on configuring dataset access controls, see Assigning access controls to datasets.

Creating authorized views

An authorized view allows you to share query results with particular users and groups without giving them read access to the underlying tables. You cannot create an authorized view in the dataset that contains the tables queried by the view.

When you create an authorized view, you use the view's SQL query to restrict access to only the fields (columns) you want the users to see.

When you create an authorized view, you:

  1. Create a separate dataset to store the view.
  2. Create the view in the new dataset.
  3. Assign access controls to the project.
  4. Assign access controls to the dataset containing the view.
  5. Authorize the view to access the source dataset.

For a tutorial on creating an authorized view, see: Sharing Access to BigQuery Views.

To create an authorized view:

Step 1: Create a separate dataset to store the view

Create a new dataset in your project.

Step 2: Create a view in the new dataset

In the new dataset, create the view you intend to authorize. When you save the view, be sure to save it in your newly created dataset.

Step 3: Assign access controls to the project

To query the view, your users need permission to run query jobs. The bigquery.user role includes permissions to run jobs, including queries, within the project. The bigquery.user role does not assign permissions to view data in the tables queried by the view or table schema details. Most individuals (data scientists, business intelligence analysts) in an enterprise should be granted the bigquery.user role. For more information on assigning IAM roles at the project level, see Granting, Changing, and Revoking Access to Project Members.

Step 4: Assign access controls to the dataset that contains the view

For users to query the view, they need READER access to the dataset that contains the view. For more information about assigning access controls to a dataset, see Assigning access controls to datasets.

Step 5: Authorize the view to access the source dataset

After you create access controls for the dataset that contains the view, you add the view as an authorized view in the source dataset. Adding an authorized view gives the view access to the source data. It does not give access to your users and groups.

To create an authorized view:

Web UI

  1. Click the drop-down arrow to the right of the dataset that contains the source tables and choose Share Dataset.

  2. In the Share Dataset dialog, for Add People, click the drop-down to the left of the field, and choose Authorized View.

  3. Click Select View.

  4. In the Select View dialog:

    • For Project, verify the project name. If the view is in a different project, be sure to select it.
    • For Dataset, choose the dataset that contains the view.
    • For Table ID, type the name of the view you are authorizing.
    • Click OK.

    Select authorized view

  5. Click Add and then click Save changes.

Command-line

  1. Export the existing access controls to a JSON file using the show command. Because the existing access controls are overwritten when you use the CLI, you must first export the existing controls.

    bq --format=json show [DATASET] >[DATASET].json
    

    Where:

    • [DATASET] is the name of your dataset
  2. Add the authorized view to the "access" section of the JSON file.

    For example:

    {
     "access": [
      {
       "role": "READER",
       "specialGroup": "projectReaders"
      },
      {
       "view":{
       "datasetId": "[DATASET_NAME]",
       "projectId": "[PROJECT_NAME]",
       "tableId": "[VIEW_NAME]"
      },
     ],
    }
    

  3. When your edits are complete, use the update command and include the JSON file using the --source flag.

    bq update --source=[DATASET].json [DATASET]

    Where:

    • [DATASET] is the name of your dataset
  4. To verify your access control changes, use the show command.

    bq show [DATASET]

    Where:

    • [DATASET] is the name of your dataset

API


Call datasets.insert or datasets.update and use the access[] property to authorize the view. For more information, see Datasets.

Assigning row-level permissions to different users or groups using a single view

Views are used to restrict access to particular fields (columns). If you want to restrict access to individual rows in your table, you do not need to create separate views for each user or group. Instead, you can use the SESSION_USER() function to return the email address of the current user.

To display different rows to different users, add another field to your table containing the user who is allowed to see the row. Then, create a view that uses the SESSION_USER() function. In the following example, the user names are stored in the allowed_viewer field:

#standardSQL
SELECT [COLUMN_1, COLUMN_2]
FROM `[DATASET.VIEW]`
WHERE allowed_viewer = SESSION_USER()

The limitation of this approach is that you can grant access to only one user at a time. You can work around this limitation by making allowed_viewer a repeated field. This approach allows you provide a list of users for each row, but, even if you use a repeated field, storing user names in the table still requires you to manually track the individual users that have access to each row.

Instead, populate the allowed_viewer field with group names, and create a separate table that maps groups to users. The table that maps groups to users would have a schema that stores group names and user names. For example: {group:string, user_name:string}. This approach allows you to manage the user and group information separately from the table that contains the data.

If the mapping table is named private.access_control, the SQL query used to create the authorized view would be:

#standardSQL
SELECT c.customer, c.id
FROM `private.customers` c
INNER JOIN (
    SELECT group
    FROM `private.access_control`
    WHERE SESSION_USER() = user_name) g
ON c.allowed_group = g.group

Send feedback about...