Using Views

This document describes how to create, edit, and assign access controls to 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 360.

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.
  • BigQuery supports up to four levels of nested views in legacy SQL. If there are more than four levels, an INVALID_INPUT error returns. In standard SQL, you are limited to 100 levels of nested views.
  • You are limited to 1,000 authorized views per dataset.

Creating views

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


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

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.

Updating views

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.

To update a view:

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.

Command-line


Use the update command with the --view flag. For standard SQL queries, add 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 edit 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 dataset roles, see access control.

You can apply access controls to a dataset after the dataset is created using the command-line tool and the web UI. You can apply access controls during or after dataset creation using the API.

To assign permissions to views using the dataset access controls:

Web UI

  1. Click the drop-down arrow to the right of the dataset containing the view 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 the appropriate option:

    • User by e-mail - Gives an individual user access to the dataset
    • Group by e-mail - Gives all members of a group access to the dataset
    • Domain - Gives all users and groups in a domain access to the dataset
    • All Authenticated Users - Gives all Google account holders access to the dataset
    • Project Owners - Gives all project owners access to the dataset
    • Project Viewers - Gives all project viewers access to the dataset
    • Project Editors - Gives all project editors access to the dataset
    • Authorized View - Gives a view access to the dataset

  3. Type a value in the text box. For example, if you chose User by e-mail, type the user's email address.

  4. To the right of the Add People field, click Can view and choose the appropriate role from the list. "Can View" maps to the bigquery.dataViewer role, "Can Edit" maps to the bigquery.dataEditor role, and "Is owner" maps to the bigquery.dataOwner role.

  5. Click Add and then click Save changes.

Command-line

  1. When you apply access controls to a dataset using the command-line tool, the existing controls are overwritten. First, export the existing access controls to a JSON file using the show command.

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

    Where:

    • DATASET_NAME is the name of your dataset
  2. Make your changes to the "access" section of the JSON file. You can add or remove any of the special groups: Project Owner, Project Writer, Project Reader, and All Authenticated Users.

    And you can add any of the following: User by e-mail, Group by e-mail, and Domain.

    For example:

    {
     "access": [
      {
       "role": "READER",
       "specialGroup": "projectReaders"
      },
      {
       "role": "WRITER",
       "specialGroup": "projectWriters"
      },
      {
       "role": "OWNER",
       "specialGroup": "projectOwners"
      }
      {
       "role": "READER",
       "specialGroup": "allAuthenticatedUsers"
      }
      {
       "role": "READER",
       "domain": "[DOMAIN_NAME]"
      }
      {
       "role": "WRITER",
       "userByEmail": "[USER_EMAIL]"
      }
      {
       "role": "READER",
       "groupByEmail": "[GROUP_EMAIL]"
      }
     ],
    }
    

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

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

    Where:

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

    bq show [DATASET_NAME]

    Where:

    • [DATASET_NAME] is the name of your dataset

API


Call datasets.insert or datasets.update and use the access[] property to apply your access controls. For more information, see 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. Authorized views can only be created in a dataset that does not contain 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 rows and columns you want the users to see. Using a view to restrict access to rows and columns is also referred to as assigning row-level permissions to the data.

When you create an authorized view, you:

  • Create a separate dataset to store the view
  • Create the view in the new dataset
  • Assign access controls to the project
  • Assign access controls to the dataset containing the view
  • 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

In order 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 give users 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 users. 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 containing the view

In order for users to query the view, they need READER access to the dataset containing the view. For more information on assigning access controls to a dataset, see: Assigning access controls to views.

Step 5: Authorize the view to access the source dataset

Once you create access controls for the dataset containing the view, you add the view as an authorized view in the source dataset. This gives the view access to the source data, not your users and groups.

To create an authorized view:

Web UI

  1. Click the drop-down arrow to the right of the dataset containing 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.

  5. Click Add and then click Save changes.

Command-line

  1. When you apply access controls to a dataset using the CLI, the existing controls are overwritten. First, export the existing access controls to a JSON file using the show command.

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

    Where:

    • [DATASET_NAME] 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_NAME].json [DATASET_NAME]

    Where:

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

    bq show [DATASET_NAME]

    Where:

    • [DATASET_NAME] 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

If you want to give different users access to different rows in your table, you do not need to create separate views for each one. 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 column 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 column:

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

The limitation of this approach is that you can only grant access to a single user at a time. You can work around this limitation by making the user name column a repeated field. This 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 column 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 allows you to manage the user and group information separate from the table containing 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...

BigQuery Documentation