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 Cloud Console
Using the bq command-line tool's bq 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.

View on GitHub Feedback

import (
	"context"
	"fmt"
	"io"

	"cloud.google.com/go/bigquery"
)

// getView demonstrates fetching the metadata from a BigQuery logical view and printing it to an io.Writer.
func getView(w io.Writer, projectID, datasetID, viewID string) error {
	// projectID := "my-project-id"
	// datasetID := "mydataset"
	// viewID := "myview"
	ctx := context.Background()
	client, err := bigquery.NewClient(ctx, projectID)
	if err != nil {
		return fmt.Errorf("bigquery.NewClient: %v", err)
	}
	defer client.Close()

	view := client.Dataset(datasetID).Table(viewID)
	meta, err := view.Metadata(ctx)
	if err != nil {
		return err
	}
	fmt.Fprintf(w, "View %s, query: %s\n", view.FullyQualifiedName(), meta.ViewQuery)
	return nil
}

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.

View on GitHub Feedback

import com.google.cloud.bigquery.BigQuery;
import com.google.cloud.bigquery.BigQueryException;
import com.google.cloud.bigquery.BigQueryOptions;
import com.google.cloud.bigquery.Table;
import com.google.cloud.bigquery.TableId;

// Sample to get a view
public class GetView {

  public static void main(String[] args) {
    // TODO(developer): Replace these variables before running the sample.
    String datasetName = "MY_DATASET_NAME";
    String viewName = "MY_VIEW_NAME";
    getView(datasetName, viewName);
  }

  public static void getView(String datasetName, String viewName) {
    try {
      // Initialize client that will be used to send requests. This client only needs to be created
      // once, and can be reused for multiple requests.
      BigQuery bigquery = BigQueryOptions.getDefaultInstance().getService();

      TableId tableId = TableId.of(datasetName, viewName);
      Table view = bigquery.getTable(tableId);
      System.out.println("View retrieved successfully" + view.getDescription());
    } catch (BigQueryException e) {
      System.out.println("View not retrieved. \n" + e.toString());
    }
  }
}

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.

View on GitHub Feedback

// Import the Google Cloud client library
const {BigQuery} = require('@google-cloud/bigquery');
const bigquery = new BigQuery();

async function getView() {
  // Retrieves view properties.

  /**
   * TODO(developer): Uncomment the following lines before running the sample
   */
  // const datasetId = "my_dataset";
  // const tableId = "my_view";

  // Retrieve view
  const dataset = bigquery.dataset(datasetId);
  const [view] = await dataset.table(tableId).get();

  const fullTableId = view.metadata.id;
  const viewQuery = view.metadata.view.query;

  // Display view properties
  console.log(`View at ${fullTableId}`);
  console.log(`View query: ${viewQuery}`);
}
getView();

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()

view_id = "my-project.my_dataset.my_view"
# Make an API request to get the table resource.
view = client.get_table(view_id)

# Display view properties
print(f"Retrieved {view.table_type}: {str(view.reference)}")
print(f"View Query:\n{view.view_query}")

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

Go to the BigQuery page
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
```
Note: INFORMATION_SCHEMA view names are case-sensitive.
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:

Console

Open the BigQuery page in the Cloud Console.

Go to the BigQuery page
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"
```
Note: INFORMATION_SCHEMA view names are case-sensitive.
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 BigQuery INFORMATION_SCHEMA.

Getting information about views

Required permissions

Getting view information

Console

bq

API

Go

Java

Node.js

Python

INFORMATION_SCHEMA (beta)

VIEWS view

Examples

Example 1:

Console

bq

Example 2:

Console

bq

Next steps

`INFORMATION_SCHEMA` (beta)

`VIEWS` view