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's bq 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.

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.

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.

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 BigQuery INFORMATION_SCHEMA.

Getting information about views

Before you begin

Required permissions

Getting view information

Console

bq

API

Go

Java

Node.js

Python

INFORMATION_SCHEMA (beta)

VIEWS view

Examples

Example 1:

Example 2:

View security

Next steps

`INFORMATION_SCHEMA` (beta)

`VIEWS` view