Managing views

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

• Copy (recreate) a view
• Rename (recreate) a view
• Delete a view

Copying a view

Currently, there is no supported method for copying a view from one dataset to another. You must recreate the view in the target dataset.

To assist you in recreating the view, you can copy the SQL query from the old view. To copy the SQL query that defines a view:

Console

1. In the UI navigation pane, select the view.

2. Click Details below the Query editor.

   

3. Copy the text in the Query box in the Details tab.

   

Classic UI

1. In the UI navigation pane, select the view.

2. In the View Details panel, click Details.

3. Copy the text in the Query box.

Command-line

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

Renaming a view

Currently, you cannot change the name of an existing view. If you need to change the view name, recreate the view with the new name.

Deleting views

You can delete a view by:

• Using the GCP Console or the classic BigQuery web UI
• Using the 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.

Caution: Deleting a view cannot be undone.

Required permissions

Deleting a view requires the same permissions as deleting a table — you must have OWNER access at the dataset level, or you must be assigned a project-level IAM role that includes bigquery.tables.delete permissions. The following predefined, project-level IAM roles include bigquery.tables.delete permissions:

• bigquery.dataOwner
• bigquery.dataEditor
• bigquery.admin

Users assigned a predefined, project-level role can delete views in any dataset in the project. Users assigned OWNER permissions at the dataset level can delete views only in that dataset.

In addition, because the bigquery.user role has bigquery.datasets.create permissions, a user assigned to the bigquery.user role can delete views in any dataset that user creates. When a user assigned to the bigquery.user role creates a dataset, that user is given OWNER access to the dataset. OWNER access to a dataset gives the user full control over the dataset and all the tables and views in it.

For more information on IAM roles and permissions in BigQuery, see Access control. For more information on dataset-level roles, see Primitive roles for datasets.

Deleting a view

To delete a view:

Classic UI

1. Select your view in the Resources panel. Click Delete view on the right side of the window..

2. Type the name of the view in the dialog, and click Delete to confirm.

Classic UI

1. Click the down arrow icon next to your view name in the navigation bar and click Delete view.

2. When prompted, click OK to confirm.

Command-line

Use the bq rm command with the --table flag (or -t shortcut) to delete a view. When you use the CLI 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:

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

Note: You can enter the bq ls [DATASET] command to confirm that a view was removed from a dataset.

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

// To run this sample, you will need to create (or reuse) a context and
// an instance of the bigquery client.  For example:
// import "cloud.google.com/go/bigquery"
// ctx := context.Background()
// client, err := bigquery.NewClient(ctx, "your-project-id")
table := client.Dataset(datasetID).Table(tableID)
if err := table.Delete(ctx); err != nil {
	return err
}

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');

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";

  // Create a client
  const bigqueryClient = new BigQuery();

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

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

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

# TODO(developer): 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)
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

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