Getting information about views

This document describes how to list, get information about, and see metadata for views in BigQuery.

You can list views in datasets by:

Using the Google Cloud console
Using the bq ls command in the bq command-line tool
Calling the tables.list API method
Using the client libraries

Before you begin

Grant Identity and Access Management (IAM) roles that give users the necessary permissions to perform each task in this document.

List views

Listing views is identical to the process for listing tables.

Required permissions

To list views in a dataset, you need the bigquery.tables.list IAM permission.

Each of the following predefined IAM roles includes the permissions that you need in order to list views in a dataset:

roles/bigquery.user
roles/bigquery.metadataViewer
roles/bigquery.dataViewer
roles/bigquery.dataOwner
roles/bigquery.dataEditor
roles/bigquery.admin

For more information on IAM roles and permissions in BigQuery, see Predefined roles and permissions.

List views in a dataset

To list the views in a dataset:

Console

In the Explorer panel, expand your project and select a dataset.
Scroll through the list to see the tables in the dataset. Tables and views are identified by different icons.

SQL

Use the INFORMATION_SCHEMA.VIEWS view:

In the Google Cloud console, go to the BigQuery page.

Go to BigQuery
In the query editor, enter the following statement:
```
SELECT table_name
FROM DATASET_ID.INFORMATION_SCHEMA.VIEWS;
```
Replace DATASET_ID with the name of the dataset.
Click Run.

For more information about how to run queries, see Run an interactive query.

bq

Issue the bq ls command. The --format flag can be used to control the output. If you are listing views in a project other than your default project, add the project ID to the dataset in the following format: project_id:dataset.

bq ls --format=pretty project_id:dataset

Where:

project_id is your project ID.
dataset is the name of the dataset.

When you run the command, the Type field displays either TABLE or VIEW. For example:

+-------------------------+-------+----------------------+-------------------+
|         tableId         | Type  |        Labels        | Time Partitioning |
+-------------------------+-------+----------------------+-------------------+
| mytable                 | TABLE | department:shipping  |                   |
| myview                  | VIEW  |                      |                   |
+-------------------------+-------+----------------------+-------------------+

Examples:

Enter the following command to list views in dataset mydataset in your default project.

bq ls --format=pretty mydataset

Enter the following command to list views in dataset mydataset in myotherproject.

bq ls --format=pretty myotherproject:mydataset

API

To list views using the API, call the tables.list method.

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 authenticate to BigQuery, set up Application Default Credentials. For more information, see Set up authentication for client libraries.

import (
	"context"
	"fmt"
	"io"

	"cloud.google.com/go/bigquery"
	"google.golang.org/api/iterator"
)

// listTables demonstrates iterating through the collection of tables in a given dataset.
func listTables(w io.Writer, projectID, datasetID string) error {
	// projectID := "my-project-id"
	// datasetID := "mydataset"
	// tableID := "mytable"
	ctx := context.Background()
	client, err := bigquery.NewClient(ctx, projectID)
	if err != nil {
		return fmt.Errorf("bigquery.NewClient: %v", err)
	}
	defer client.Close()

	ts := client.Dataset(datasetID).Tables(ctx)
	for {
		t, err := ts.Next()
		if err == iterator.Done {
			break
		}
		if err != nil {
			return err
		}
		fmt.Fprintf(w, "Table: %q\n", t.TableID)
	}
	return nil
}

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.

To authenticate to BigQuery, set up Application Default Credentials. For more information, see Set up authentication for client libraries.


from google.cloud import bigquery

# Construct a BigQuery client object.
client = bigquery.Client()

# TODO(developer): Set dataset_id to the ID of the dataset that contains
#                  the tables you are listing.
# dataset_id = 'your-project.your_dataset'

tables = client.list_tables(dataset_id)  # Make an API request.

print("Tables contained in '{}':".format(dataset_id))
for table in tables:
    print("{}.{}.{}".format(table.project, table.dataset_id, table.table_id))

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

Get information about views

Getting information about views is identical to the process for getting information about tables.

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.

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.

SQL

Query the INFORMATION_SCHEMA.VIEWS view. The following example retrieves all columns except for check_option, which is reserved for future use. The metadata returned is for all views in DATASET_ID in your default project:

In the Google Cloud console, go to the BigQuery page.

Go to BigQuery

In the query editor, enter the following statement:

  SELECT
    * EXCEPT (check_option)
  FROM
    DATASET_ID.INFORMATION_SCHEMA.VIEWS;

Replace DATASET_ID with the name of the dataset.

Click Run.

For more information about how to run queries, see Run an interactive query.

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.

To authenticate to BigQuery, set up Application Default Credentials. For more information, see Set up authentication for client libraries.

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.

To authenticate to BigQuery, set up Application Default Credentials. For more information, see Set up authentication for client libraries.

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.

To authenticate to BigQuery, set up Application Default Credentials. For more information, see Set up authentication for client libraries.

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

To authenticate to BigQuery, set up Application Default Credentials. For more information, see Set up authentication for client libraries.

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}")

View security

To control access to views in BigQuery, see Authorized views.

What's next

For information on creating views, see Creating views.
For information on creating an authorized view, see Creating authorized views.
For more information on managing views, see Managing views.
To see an overview of INFORMATION_SCHEMA, go to Introduction to BigQuery INFORMATION_SCHEMA.