Creating and Using Views

This document describes how to create and use views in BigQuery. After creating a view, you can:

  • Control access to your views
  • Get information about your views
  • List the views in a dataset
  • Get view metadata using meta-tables

For more information on managing views including updating view properties, copying a view, and deleting a view, see Managing Views.

Creating a view

You can create a view in BigQuery:

  • Manually by using the BigQuery web UI or the command line tool's bq mk command
  • Programmatically by calling the tables.insert API method
  • By submitting a CREATE VIEW Data Definition Language (DDL) statement

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

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:

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

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

      Save view dialog

    • Click OK.

CLI

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, add the project ID to the dataset in the following format: [PROJECT_ID]:[DATASET].

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].[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 standard SQL query.
  • [PROJECT_ID] is your project ID.
  • [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' 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.

Controlling access to views

You cannot assign access controls directly to views. You can control view access by configuring access controls at the dataset level or at the project level.

Dataset-level access controls specify the operations users, groups, and service accounts are allowed to perform on views in that specific dataset. If you assign only dataset-level permissions, you must also assign a primitive or predefined, project-level role that provides access to the project, for example, bigquery.user.

Instead of granting access to individual datasets, you can assign predefined, project-level IAM roles that grant permissions to all views in all datasets in a project.

You can also create IAM custom roles. If you create a custom role, the permissions you grant depend on the view operations you want the user, group, or service account to be able to perform.

For more information on roles and permissions, see:

Querying views

You query a view the same way you query a table. The permissions required to query a view are also the same as those for querying a table. For information on creating and submitting queries, see Introduction to Querying BigQuery Data.

Using views

Getting information about views

You can get information about views using the BigQuery web UI, using the bq show CLI command or by calling the tables.get API method.

Required permissions

To get information about views, you must be assigned the READER role on the dataset, or you must be assigned a project-level IAM role that includes bigquery.tables.get permissions. If you are granted bigquery.tables.get permissions at the project level, you can get information about all views in the project. The following predefined, project-level IAM roles include bigquery.tables.get permissions:

In addition, a user assigned the bigquery.user role has bigquery.datasets.create permissions. This allows a user assigned to the bigquery.user role to get information about 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 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.

Getting view information

Getting information about views is identical to the process for getting information about tables.

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. The --format flag can be used to control the output. If you are getting information about a view in a project other than your default project, add the project ID to the dataset in the following format: [PROJECT_ID]:[DATASET].

bq show --format=prettyjson [PROJECT_ID]:[DATASET].[VIEW]

Where:

  • [PROJECT_ID] is your project ID.
  • [DATASET] is the name of the dataset.
  • [VIEW] is the name of the view.

Examples:

Enter the following command to display information about myview in dataset mydataset in your default project.

bq show --format=prettyjson mydataset.myview

Enter the following command to display information about myview in dataset mydataset in myotherproject.

bq show --format=prettyjson myotherproject:mydataset.myview

API

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

Listing views in a dataset

You can list views in datasets using the BigQuery web UI, using the bq ls CLI command or by calling the tables.list API method.

Required permissions

To list views in a dataset, you must be assigned the READER role on the dataset, or you must be assigned a project-level IAM role that includes bigquery.tables.list permissions. If you are granted bigquery.tables.list permissions at the project level, you can list views in any dataset in the project. All predefined 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. For more information on dataset-level roles, see Primitive roles for datasets.

Listing views

Listing views is identical to the process for listing tables.

To list the views in a dataset:

Web UI

  1. In the web UI, in the navigation pane, click the blue arrow to the left of your dataset to expand it, or double-click the dataset name. This displays the tables and views in the dataset.

  2. Scroll through the list to see the tables in the dataset. Tables and views are identified by different icons.

    View tables

Command-line

Issue the bq ls command. The --format flag can be used to control the output. If you are listing views in a project other than your default project, add the project ID to the dataset in the following format: [PROJECT_ID]:[DATASET].

bq ls --format=pretty [PROJECT_ID]:[DATASET]

Where:

  • [PROJECT_ID] is your project ID.
  • [DATASET] is the name of the dataset.

When you run the command, the Type field displays either TABLE or VIEW. For example:

+-------------------------+-------+----------------------+-------------------+
|         tableId         | Type  |        Labels        | Time Partitioning |
+-------------------------+-------+----------------------+-------------------+
| mytable                 | TABLE | department:shipping  |                   |
| myview                  | VIEW  |                      |                   |
+-------------------------+-------+----------------------+-------------------+

Examples:

Enter the following command to list views in dataset mydataset in your default project.

bq ls --format=pretty mydataset

Enter the following command to list views in dataset mydataset in myotherproject.

bq ls --format=pretty myotherproject:mydataset

API

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

Getting view metadata using meta-tables

BigQuery offers some special tables whose contents represent metadata, such as the list of tables and views in a dataset. The "meta-tables" are read-only. To access metadata about the tables and views in a dataset, use the __TABLES_SUMMARY__ meta-table in a query's SELECT statement. You can run the query using the BigQuery web UI, using the command-line tool's bq query command, or by calling the jobs.insert API method and configuring a query job.

A query that uses the __TABLES_SUMMARY__ meta-table looks like the following:

    SELECT [FIELD] FROM [DATASET].__TABLES_SUMMARY__

Where:

  • DATASET is the name of your dataset
  • FIELD is one of the following:
Value Description
project_id Name of the project.
dataset_id Name of the dataset.
table_id Name of the table or view.
creation_time The time at which the table or view was created, in milliseconds since January 1, 1970 UTC.
type An integer representing the table type: a regular table (1) or a view (2).

Required permissions

To run a query job that uses the __TABLES_SUMMARY__ meta-table, you must have bigquery.jobs.create permissions. The following predefined, project-level IAM roles include bigquery.jobs.create permissions:

You must also be assigned the READER role at the dataset level, or you must be assigned a project-level IAM role that includes bigquery.tables.getData permissions. All predefined, project-level IAM roles except bigquery.user and bigquery.jobUser have bigquery.tables.getData permissions.

Meta-table limitations

Meta-tables are subject to the following limitations:

  • Generally, __TABLES_SUMMARY__ is reasonably fast for datasets with up to a few thousand tables. For larger datasets __TABLES_SUMMARY__ becomes increasingly slow, and may exceed available resources.
  • Meta-tables cannot be used with the tables.insert method.
  • Meta-tables cannot be used as a destination table.
  • Meta-tables do not support table decorators in legacy SQL.
  • Meta-tables do not appear when you list the tables in a dataset.

Meta-table examples

The following query retrieves all metadata for the publicdata.samples dataset.

Web UI

#standardSQL
SELECT
  *
FROM
  `publicdata.samples.__TABLES_SUMMARY__`

Command-line

bq --location=US query --use_legacy_sql=false '
SELECT
  *
FROM
  publicdata.samples.TABLES_SUMMARY'

The output looks like the following:

+------------+------------+-----------------+---------------+------+
| project_id | dataset_id |    table_id     | creation_time | type |
+------------+------------+-----------------+---------------+------+
| publicdata | samples    | github_nested   | 1348782587310 |    1 |
| publicdata | samples    | github_timeline | 1335915950690 |    1 |
| publicdata | samples    | gsod            | 1335916040125 |    1 |
| publicdata | samples    | natality        | 1335916045005 |    1 |
| publicdata | samples    | shakespeare     | 1335916045099 |    1 |
| publicdata | samples    | trigrams        | 1335916127449 |    1 |
| publicdata | samples    | wikipedia       | 1335916132870 |    1 |
+------------+------------+-----------------+---------------+------+

The following query lists all tables and views in the publicdata.samples dataset.

Web UI

#standardSQL
SELECT
  table_id
FROM
  `publicdata.samples.__TABLES_SUMMARY__`

Command-line

bq --location=US query --use_legacy_sql=false '
SELECT
  table_id
FROM
  publicdata.samples.TABLES_SUMMARY'

The output looks like the following:

+-----------------+
|    table_id     |
+-----------------+
| github_nested   |
| github_timeline |
| gsod            |
| natality        |
| shakespeare     |
| trigrams        |
| wikipedia       |
+-----------------+

The following query lists the type for each table in the publicdata.samples dataset.

Web UI

#standardSQL
SELECT
  table_id, type
FROM
  `publicdata.samples.__TABLES_SUMMARY__`

Command-line

bq --location=US query --use_legacy_sql=false '
SELECT
  table_id, type
FROM
  publicdata.samples.TABLES_SUMMARY'

The output looks like the following:

+-----------------+------+
|    table_id     | type |
+-----------------+------+
| github_nested   |   1  |
| github_timeline |   1  |
| gsod            |   1  |
| natality        |   1  |
| shakespeare     |   1  |
| trigrams        |   1  |
| wikipedia       |   1  |
+-----------------+------+

Granting a view access to a dataset

Giving a view access to a dataset is also known as creating an authorized view in BigQuery. An authorized view allows you to share query results with particular users and groups without giving them access to the underlying tables. You can also use the view's SQL query to restrict the columns (fields) the users are able to query.

When you create the view, it must be created in a dataset separate from the source data queried by the view. Because you can assign access controls only at the dataset level, if the view is created in the same dataset as the source data, your users would have access to both the view and the data.

For a tutorial on creating an authorized view, see: Creating an Authorized View in BigQuery.

Required permissions

To give a view access to a dataset, you must have OWNER access at the dataset level, or you must be assigned a project-level IAM role that includes bigquery.datasets.update permissions. The following predefined, project-level IAM roles include bigquery.datasets.update permissions:

In addition, because the bigquery.user role has bigquery.datasets.create permissions, a user assigned to the bigquery.user role can update 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.

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.

Granting view access

To grant a view access to a dataset:

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. Write the existing dataset information (including access controls) to a JSON file using the show command. If the dataset is in a project other than your default project, add the project ID to the dataset name in the following format: [PROJECT_ID]:[DATASET].

    bq show --format=prettyjson [PROJECT_ID]:[DATASET] > [PATH_TO_FILE]
    

    Where:

    • [PROJECT_ID] is your project ID.
    • [DATASET] is the name of your dataset.
    • [PATH_TO_FILE] is the path to the JSON file on your local machine.

    Examples:

    Enter the following command to write the access controls for mydataset to a JSON file. mydataset is in your default project.

    bq show --format=prettyjson mydataset > /tmp/mydataset.json
    

    Enter the following command to write the access controls for mydataset to a JSON file. mydataset is in myotherproject.

    bq show --format=prettyjson myotherproject:mydataset > /tmp/mydataset.json
    
  2. Add the authorized view to the "access" section of the JSON file.

    For example, the access section of a dataset's JSON file would look like the following:

    {
     "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]"
      },
      {
       "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. If the dataset is in a project other than your default project, add the project ID to the dataset name in the following format: [PROJECT_ID]:[DATASET].

    bq update --source [PATH_TO_FILE] [PROJECT_ID]:[DATASET]
    

    Where:

    • [PATH_TO_FILE] is the path to the JSON file on your local machine.
    • [PROJECT_ID] is your project ID.
    • [DATASET] is the name of your dataset.

    Examples:

    Enter the following command to update the access controls for mydataset. mydataset is in your default project.

        bq update --source /tmp/mydataset.json mydataset
    

    Enter the following command to update the access controls for mydataset. mydataset is in myotherproject.

        bq update --source /tmp/mydataset.json myotherproject:mydataset
    
  4. To verify your access control changes, enter the show command again without writing the information to a file.

    bq show --format=prettyjson [DATASET]
    

    or

    bq show --format=prettyjson [PROJECT_ID]:[DATASET]
    

API

Call the datasets.patch and use the access property to update your access controls. For more information, see Datasets.

Because the datasets.update method replaces the entire dataset resource, datasets.patch is the preferred method for updating access controls.

Enforcing row-level access using a view

Views can be used to restrict access to particular columns (fields). 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

Next steps

Was this page helpful? Let us know how we did:

Send feedback about...