Getting view metadata using INFORMATION_SCHEMA

Beta

This is a beta release of BigQuery INFORMATION_SCHEMA. This feature might be changed in backward-incompatible ways and is not subject to any SLA or deprecation policy.

INFORMATION_SCHEMA is a series of views that provide access to metadata about datasets, tables, and views.

You can query the INFORMATION_SCHEMA.VIEWS view to retrieve metadata about views in a dataset.

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. All predefined, project-level IAM roles include bigquery.tables.get permissions except for bigquery.jobUser and bigquery.user.

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.

The VIEWS view

When you query the INFORMATION_SCHEMA.VIEWS view, the query results contain one row for each view in a dataset.

Queries against the INFORMATION_SCHEMA.VIEWS view must have a dataset qualifier. The user submitting the query must have access to the dataset that contains the views.

The INFORMATION_SCHEMA.VIEWS view has the following schema:

Column name	Data type	Value
TABLE_CATALOG	STRING	The name of the project that contains the dataset
TABLE_SCHEMA	STRING	The name of the dataset that contains the view (also referred to as the datasetId)
TABLE_NAME	STRING	The name of the view (also referred to as the tableId)
VIEW_DEFINITION	STRING	The SQL query that defines the view
CHECK_OPTION	STRING	The value returned is always NULL
USE_STANDARD_SQL	STRING	YES if the view was created by using a standard SQL query; NO if useLegacySql is set to true

Examples

Example 1:

The following example retrieves all columns from the INFORMATION_SCHEMA.VIEWS view except for check_option which is reserved for future use. The metadata returned is for all views in mydataset in your default project — myproject.

Queries against the INFORMATION_SCHEMA.VIEWS view must have a dataset qualifier. The user submitting the query must have access to the dataset that contains the views.

To run the query against a project other than your default project, add the project ID to the dataset in the following format: `[PROJECT_ID]`.[DATASET].INFORMATION_SCHEMA.[VIEW] for example, `myproject`.mydataset.INFORMATION_SCHEMA.VIEWS.

To run the query:

Console

1. Open the BigQuery web UI in the GCP Console.

   Go to the BigQuery web UI

2. Enter the following standard SQL query in the Query editor box. INFORMATION_SCHEMA requires standard SQL syntax. Standard SQL is the default syntax in the GCP Console.

   SELECT
    * EXCEPT(check_option)
   FROM
    mydataset.INFORMATION_SCHEMA.VIEWS
   

   Note: INFORMATION_SCHEMA view names are case-sensitive.

3. Click Run.

Command-line

Use the query command and specify standard SQL syntax by using the --nouse_legacy_sql or --use_legacy_sql=false flag. Standard SQL syntax is required for INFORMATION_SCHEMA queries.

To run the query, enter:

bq query --nouse_legacy_sql \
'SELECT * EXCEPT(check_option) FROM mydataset.INFORMATION_SCHEMA.VIEWS'

The results should look like the following:

  +----------------+---------------+---------------+---------------------------------------------------------------------+------------------+
  | table_catalog  | table_schema  |  table_name   |                        view_definition                              | use_standard_sql |
  +----------------+---------------+---------------+---------------------------------------------------------------------+------------------+
  | myproject      | mydataset     | myview        | SELECT column1, column2 FROM [myproject:mydataset.mytable] LIMIT 10 | NO               |
  +----------------+---------------+---------------+---------------------------------------------------------------------+------------------+
  

Note that the results show that this view was created by using a legacy SQL query.

Example 2:

The following example retrieves the SQL query and query syntax used to define myview in mydataset in your default project — myproject.

Queries against the INFORMATION_SCHEMA.VIEWS view must have a dataset qualifier. The user submitting the query must have access to the dataset that contains the views.

To run the query against a project other than your default project, add the project ID to the dataset in the following format: `[PROJECT_ID]`.[DATASET].INFORMATION_SCHEMA.[VIEW] for example, `myproject`.mydataset.INFORMATION_SCHEMA.VIEWS.

To run the query:

Console

1. Open the BigQuery web UI in the GCP Console.

   Go to the BigQuery web UI

2. Enter the following standard SQL query in the Query editor box. INFORMATION_SCHEMA requires standard SQL syntax. Standard SQL is the default syntax in the GCP Console.

   SELECT
    table_name, view_definition, use_standard_sql
   FROM
    mydataset.INFORMATION_SCHEMA.VIEWS
   WHERE
    table_name="myview"
   

   Note: INFORMATION_SCHEMA view names are case-sensitive.

3. Click Run.

Command-line

Use the query command and specify standard SQL syntax by using the --nouse_legacy_sql or --use_legacy_sql=false flag. Standard SQL syntax is required for INFORMATION_SCHEMA queries.

To run the query, enter:

bq query --nouse_legacy_sql \
'SELECT table_name, view_definition, use_standard_sql FROM mydataset.INFORMATION_SCHEMA.VIEWS WHERE table_name="myview"'

The results should look like the following:

  +---------------+---------------------------------------------------------------+------------------+
  |  table_name   |                        view_definition                        | use_standard_sql |
  +---------------+---------------------------------------------------------------+------------------+
  | myview        | SELECT column1, column2, column3 FROM mydataset.mytable       | YES              |
  +---------------+---------------------------------------------------------------+------------------+
  

Note that the results show that this view was created by using a standard SQL query.

Next steps

• To see an overview of INFORMATION_SCHEMA, go to Introduction to BigQuery INFORMATION_SCHEMA.
• Learn how to use INFORMATION_SCHEMA to get dataset metadata.
• Learn how to use INFORMATION_SCHEMA to get table metadata.