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 Google Cloud console
- Using the
bq
command-line tool'sbq show
command - Calling the
tables.get
API method - Using the client libraries
- Querying the
INFORMATION_SCHEMA
views (beta)
Before you begin
Grant Identity and Access Management (IAM) roles that give users the necessary permissions to perform each task in this document.
Required permissions
To get information about a view, you need the bigquery.tables.get
IAM permission.
Each of the following predefined IAM roles includes the permissions that you need in order to get information about a view:
roles/bigquery.metadataViewer
roles/bigquery.dataViewer
roles/bigquery.dataOwner
roles/bigquery.dataEditor
roles/bigquery.admin
Additionally, if you have the bigquery.datasets.create
permission, you can get information about views in the datasets that you create.
For more information on IAM roles and permissions in BigQuery, see Predefined roles and permissions.
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.
bq
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.
Java
Before trying this sample, follow the Java setup instructions in the BigQuery quickstart using client libraries. For more information, see the BigQuery Java API reference documentation.
Node.js
Before trying this sample, follow the Node.js setup instructions in the BigQuery quickstart using client libraries. For more information, see the BigQuery Node.js API reference documentation.
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.
INFORMATION_SCHEMA
(beta)
INFORMATION_SCHEMA
is a series of views that provide access to metadata
about datasets, routines, tables, views, jobs, reservations, and streaming data.
VIEWS
view
When you query the INFORMATION_SCHEMA.VIEWS
view, the query results contain
one row for each view in a dataset.
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 |
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
.
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
.
SELECT * EXCEPT (check_option) FROM mydataset.INFORMATION_SCHEMA.VIEWS;
The result is similar to 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
.
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
.
SELECT table_name, view_definition, use_standard_sql FROM mydataset.INFORMATION_SCHEMA.VIEWS WHERE table_name = 'myview';
The result is similar to 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.
View security
To control access to views in BigQuery, see Controlling access to views.
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 BigQueryINFORMATION_SCHEMA
.