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
  • 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 Cloud 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 Cloud 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

  1. Expand your dataset.

  2. Click the view name.

  3. Click Details. The Details tab displays the view's description, view information, and the SQL query that defines the view.

    View details

Classic UI

  1. Expand your dataset.

  2. Click the view name.

  3. Click Details. The View Details page displays the view's description, view information, and the SQL query that defines the view.

    View details

CLI

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 .

// 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 .

# 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 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 GCP Console

  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 GCP Console

  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

Czy ta strona była pomocna? Podziel się z nami swoją opinią:

Wyślij opinię na temat...

Potrzebujesz pomocy? Odwiedź naszą stronę wsparcia.