This document describes how to get information or metadata about views in BigQuery.
You can get view metadata by:
- Using the 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)
Required permissions
At a minimum, to get information about views, you must be granted
bigquery.tables.get
permissions. The following predefined IAM
roles include bigquery.tables.get
permissions:
bigquery.metadataViewer
bigquery.dataViewer
bigquery.dataOwner
bigquery.dataEditor
bigquery.admin
In addition, if a user has bigquery.datasets.create
permissions, when that
user creates a dataset, they are granted bigquery.dataOwner
access to it.
bigquery.dataOwner
access gives the user the ability to get information about
views in the dataset.
For more information on IAM roles and permissions in BigQuery, see Access control.
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
.
To run the query:
Console
Open the BigQuery page in the Cloud Console.
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 Cloud Console.SELECT * EXCEPT(check_option) FROM mydataset.INFORMATION_SCHEMA.VIEWS
Click Run.
bq
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
.
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 page in the Cloud Console.
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 Cloud Console.SELECT table_name, view_definition, use_standard_sql FROM mydataset.INFORMATION_SCHEMA.VIEWS WHERE table_name="myview"
Click Run.
bq
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 BigQueryINFORMATION_SCHEMA
.