Getting view metadata using INFORMATION_SCHEMA

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 dataset id
TABLE_NAME STRING The name of the view also referred to as the table id
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

For more information on table and view properties, see the table resource page in the REST API documentation.

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
    
  3. Click Run.

CLI

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"
    
  3. Click Run.

CLI

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

¿Te ha resultado útil esta página? Enviar comentarios:

Enviar comentarios sobre...

Si necesitas ayuda, visita nuestra página de asistencia.