Managing views

This document describes how to manage views in BigQuery. You can manage your BigQuery views in the following ways:

Copy a view
Rename a view
Delete a view

Before you begin

Grant Identity and Access Management (IAM) roles that give users the necessary permissions to perform each task in this document. The permissions required to perform a task (if any) are listed in the "Required permissions" section of the task.

Copy views

Currently, you can copy a view only by using the console.

You cannot copy a view by using the bq command-line tool, the REST API, or the client libraries. Instead, you must recreate the view in the target dataset. To assist you in recreating the view, you can use the bq command-line tool to copy the view's SQL query.

Required permissions

To copy a view in the console, you need IAM permissions on the source and destination datasets.

On the source dataset, you need the following:
- bigquery.tables.get
- bigquery.tables.getData (required to access the tables referenced by the view's SQL query)
On the destination dataset, you need the following:
- bigquery.tables.create (lets you create a copy of the view in the destination dataset)

Each of the following predefined IAM roles includes the permissions that you need in order to copy a view:

roles/bigquery.dataEditor
roles/bigquery.dataOwner
roles/bigquery.admin

Additionally, if you have the bigquery.datasets.create permission, you can copy views in the datasets that you create. You also need access to the destination dataset unless you created it.

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

Copy a view

To copy a view:

Console

In the Explorer panel, expand your project and dataset, then select the view.
In the details panel, click Copy view.
In the Copy view dialog:
- In the Source section, verify that your Project name, Dataset name, and Table name are correct.
- In the Destination section:
  - For Project name, choose the project to which you are copying the view.
  - For Dataset name, choose the dataset that will contain the copied view.
  - For Table name, enter the name of the view. You can rename the view by entering a new view name in the box. If you enter a new name, it must follow the view naming rules.
- Click Copy.

bq

The bq command-line tool does not support copying a view. Instead, you must recreate the view in the target dataset. To assist you in recreating the view, you can copy the SQL query used to define the view.

To copy the SQL query that defines a view, 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. To write the view properties to a file, add > path_to_file to the command.

bq show \
--format=prettyjson \
project_id:dataset.view > path_to_file

Where:

project_id is your project ID.
dataset is the name of the dataset.
view is the name of the view.
path_to_file is the path to the output file on your local machine.

Examples:

Enter the following command to display information about myview in mydataset. mydataset is in your default project.

bq show --format=prettyjson mydataset.myview

Enter the following command to display information about myview in mydataset. mydataset is in myotherproject, not your default project. The view properties are written to a local file — /tmp/myview.json.

bq show \
--format=prettyjson \
myotherproject:mydataset.myview > /tmp/myview.json

API

The REST API does not support copying a view. Instead, you must recreate the view in the target dataset. For information on creating a view by using the REST API, see Creating a view.

Rename a view

Currently, you can rename a view only when you use the console to copy the view. For instructions on renaming a view when you copy it, see Copying a view.

You cannot change the name of an existing view by using the bq command-line tool, the API, or the client libraries. Instead, you must recreate the view with the new name.

Delete views

You can delete a view by:

Using the console
Using the bq command-line tool's bq rm command
Calling the tables.delete API method

Currently, using any available method, you can only delete one view at a time.

To automatically delete views after a specified period of time, set the default expiration time at the dataset level or set the expiration time when you create the view.

When you delete an authorized view, it might take up to 24 hours to remove the deleted view from the authorized views list of the source dataset.

Required permissions

To delete a view, you need the following IAM permissions:

bigquery.tables.delete
bigquery.tables.get

Each of the following predefined IAM roles includes the permissions that you need in order to delete a view:

roles/bigquery.dataOwner
roles/bigquery.dataEditor
roles/bigquery.admin

Additionally, if you have the bigquery.datasets.create permission, you can delete views in the datasets that you create.

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

Delete a view

To delete a view:

Console

In the console, go to the BigQuery page.

Go to BigQuery
In the Explorer panel, expand your project and dataset, then select the view.
In the details panel, click Delete view.
Type "delete" in the dialog, and click Delete to confirm.

SQL

Use the DROP VIEW statement:

In the console, go to the BigQuery page.

Go to BigQuery
In the query editor, enter the following statement:
```
DROP VIEW mydataset.myview;
```
Click Run.

For more information about how to run queries, see Running interactive queries.

bq

Use the bq rm command with the --table flag (or -t shortcut) to delete a view. When you use the bq command-line tool to remove a view, you must confirm the action. You can use the --force flag (or -f shortcut) to skip confirmation.

If the view is in a dataset in a project other than your default project, add the project ID to the dataset name in the following format: project_id:dataset.

bq rm \
-f \
-t \
project_id:dataset.view

Where:

project_id is your project ID.
dataset is the name of the dataset that contains the table.
view is the name of the view you're deleting.

Examples:

You can use the bq command-line tool to run bq commands.

In the console, activate Cloud Shell.

Activate Cloud Shell

Enter the following command to delete myview from mydataset. mydataset is in your default project.

bq rm -t mydataset.myview

Enter the following command to delete myview from mydataset. mydataset is in myotherproject, not your default project.

bq rm -t myotherproject:mydataset.myview

Enter the following command to delete myview from mydataset. mydataset is in your default project. The command uses the -f shortcut to bypass confirmation.

bq rm -f -t mydataset.myview

API

Call the tables.delete API method and specify the view to delete using the tableId parameter.

C#

Before trying this sample, follow the C# setup instructions in the BigQuery quickstart using client libraries. For more information, see the BigQuery C# API reference documentation.

View on GitHub Feedback


using Google.Cloud.BigQuery.V2;
using System;

public class BigQueryDeleteTable
{
    public void DeleteTable(
        string projectId = "your-project-id",
        string datasetId = "your_dataset_id",
        string tableId = "your_table_id"
    )
    {
        BigQueryClient client = BigQueryClient.Create(projectId);
        client.DeleteTable(datasetId, tableId);
        Console.WriteLine($"Table {tableId} deleted.");
    }
}

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"

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

// deleteTable demonstrates deletion of a BigQuery table.
func deleteTable(projectID, datasetID, tableID 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()

	table := client.Dataset(datasetID).Table(tableID)
	if err := table.Delete(ctx); err != nil {
		return err
	}
	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

TableId tableId = TableId.of(projectId, datasetName, tableName);
boolean deleted = bigquery.delete(tableId);
if (deleted) {
  // the table was deleted
} else {
  // the table was not found
}

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 deleteTable() {
  // Deletes "my_table" from "my_dataset".

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

  // Delete the table
  await bigquery
    .dataset(datasetId)
    .table(tableId)
    .delete();

  console.log(`Table ${tableId} deleted.`);
}

PHP

Before trying this sample, follow the PHP setup instructions in the BigQuery quickstart using client libraries. For more information, see the BigQuery PHP API reference documentation.

View on GitHub Feedback

use Google\Cloud\BigQuery\BigQueryClient;

/** Uncomment and populate these variables in your code */
// $projectId = 'The Google project ID';
// $datasetId = 'The BigQuery dataset ID';
// $tableId = 'The BigQuery table ID';

$bigQuery = new BigQueryClient([
    'projectId' => $projectId,
]);
$dataset = $bigQuery->dataset($datasetId);
$table = $dataset->table($tableId);
$table->delete();
printf('Deleted table %s.%s' . PHP_EOL, $datasetId, $tableId);

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

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

# TODO(developer): Set table_id to the ID of the table to fetch.
# table_id = 'your-project.your_dataset.your_table'

# If the table does not exist, delete_table raises
# google.api_core.exceptions.NotFound unless not_found_ok is True.
client.delete_table(table_id, not_found_ok=True)  # Make an API request.
print("Deleted table '{}'.".format(table_id))

Ruby

Before trying this sample, follow the Ruby setup instructions in the BigQuery quickstart using client libraries. For more information, see the BigQuery Ruby API reference documentation.

View on GitHub Feedback

require "google/cloud/bigquery"

def delete_table dataset_id = "my_dataset_id", table_id = "my_table_id"
  bigquery = Google::Cloud::Bigquery.new
  dataset  = bigquery.dataset dataset_id
  table    = dataset.table table_id

  table.delete

  puts "Table #{table_id} deleted."
end

View security

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

What's next

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 getting view metadata, see Getting information about views.
For information on updating views, see Updating views.