Getting information about views

This document describes how to get information or metadata about views in BigQuery.

You can get view metadata by:

Using the GCP Console or the classic BigQuery web UI
Using the bq show CLI command
Calling the tables.get API method
Querying the INFORMATION_SCHEMA views (beta)

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.

Getting view information

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

To get information about views:

Console

Expand your dataset.
Click the view name.
Click Details. The Details tab displays the view's description, view information, and the SQL query that defines the view.

Classic UI

Expand your dataset.
Click the view name.
Click Details. The View Details page displays the view's description, view information, and the SQL query that defines the view.

Command-line

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.

Go

Before trying this sample, follow the Go setup instructions in the BigQuery Quickstart Using Client Libraries . For more information, see the BigQuery Go API reference documentation .

View on GitHub Feedback

// To run this sample, you will need to create (or reuse) a context and
// an instance of the bigquery client.  For example:
// import "cloud.google.com/go/bigquery"
// ctx := context.Background()
// client, err := bigquery.NewClient(ctx, "your-project-id")
view := client.Dataset(datasetID).Table(viewID)
meta, err := view.Metadata(ctx)
if err != nil {
	return err
}
fmt.Printf("View %s, query: %s\n", view.FullyQualifiedName(), meta.ViewQuery)

Python

Before trying this sample, follow the Python setup instructions in the BigQuery Quickstart Using Client Libraries . For more information, see the BigQuery Python API reference documentation .

View on GitHub Feedback

# from google.cloud import bigquery
# client = bigquery.Client()
# shared_dataset_id = 'my_shared_dataset'

view_ref = client.dataset(shared_dataset_id).table("my_shared_view")
view = client.get_table(view_ref)  # API Request

# Display view properties
print("View at {}".format(view.full_table_id))
print("View Query:\n{}".format(view.view_query))

`INFORMATION_SCHEMA` (beta)

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

`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

Open the BigQuery web UI in the GCP Console.

Go to the BigQuery web UI
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.
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:

Console

Open the BigQuery web UI in the GCP Console.

Go to the BigQuery web UI
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.
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

For information on creating views, see Creating views.
For information on creating an authorized view, see Creating authorized views.
For information on listing views, see Listing views.
For information on updating views, see Updating views.
For more information on managing views, see Managing views.
To see an overview of INFORMATION_SCHEMA, go to Introduction to BigQuery INFORMATION_SCHEMA.

Cette page vous a-t-elle été utile ? Évaluez-la :

Except as otherwise noted, the content of this page is licensed under the Creative Commons Attribution 3.0 License, and code samples are licensed under the Apache 2.0 License. For details, see our Site Policies. Java is a registered trademark of Oracle and/or its affiliates.

Dernière mise à jour : avril 10, 2019

Getting information about views

Required permissions

Getting view information

Console

Classic UI

Command-line

API

Go

Python

INFORMATION_SCHEMA (beta)

VIEWS view

Examples

Example 1:

Console

Command-line

Example 2:

Console

Command-line

Next steps

Envoyer des commentaires concernant…

`INFORMATION_SCHEMA` (beta)

`VIEWS` view