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 Explorerpanel, 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 BigQuerypage.

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 Detailstab 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 BigQuerypage.

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 .