Manage logical views

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

• Update a view
• 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.

Update a view

After creating a view, you can update the following view properties:

• SQL query
• Expiration time
• Description
• Labels

Required permissions

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

• bigquery.tables.update
• bigquery.tables.get

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

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

Additionally, if you have the bigquery.datasets.create permission, you can update tables and views in the datasets that you create.

To update the view's SQL query, you must also have permissions to query any tables referenced by the view's SQL query.

Note: To update the SQL of an authorized view, or a view in an authorized dataset, you need additional permissions. For more information, see required permissions for authorized views and required permissions for views in authorized datasets.

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

Updating a view's SQL query

You can update the SQL query used to define a view by:

• Using the Google Cloud console
• Using the bq command-line tool's bq update command
• Calling the tables.patch API method
• Using the client libraries

You can change the SQL dialect from legacy SQL to GoogleSQL in the API or bq command-line tool. You cannot update a legacy SQL view to GoogleSQL in the Google Cloud console.

To update a view's SQL query:

Console

1. In the Explorer panel, expand your project and dataset, then select the view.

2. Click the Details tab.

3. Above the Query box, click the Edit query button. Click Open in the dialog that appears.

   

4. Edit the SQL query in the Query editor box and then click Save view.

   

5. Make sure all the fields are correct in the Save view dialog and then click Save.

bq

Issue the bq update command with the --view flag. To use GoogleSQL or to update the query dialect from legacy SQL to GoogleSQL, include the --use_legacy_sql flag and set it to false.

If your query references external user-defined function resources stored in Cloud Storage or in local files, use the --view_udf_resource flag to specify those resources. The --view_udf_resource flag is not demonstrated here. For more information on using UDFs, see GoogleSQL User-Defined Functions.

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

bq update \
    --use_legacy_sql=false \
    --view_udf_resource=path_to_file \
    --view='query' \
    project_id:dataset.view

Replace the following:

• path_to_file: the URI or local file system path to a code file to be loaded and evaluated immediately as a user-defined function resource used by the view. Repeat the flag to specify multiple files.
• query: a valid GoogleSQL query
• project_id: your project ID
• dataset: the name of the dataset containing the view you're updating
• view: the name of the view you're updating

Examples

Enter the following command to update the SQL query for a view named myview in mydataset. mydataset is in your default project. The example query used to update the view queries data from the USA Name Data public dataset.

bq update \
    --use_legacy_sql=false \
    --view \
    'SELECT
      name,
      number
    FROM
      `bigquery-public-data.usa_names.usa_1910_current`
    WHERE
      gender = "M"
    ORDER BY
      number DESC;' \
    mydataset.myview

Enter the following command to update the SQL query for a view named myview in mydataset. mydataset is in myotherproject, not your default project. The example query used to update the view queries data from the USA Name Data public dataset.

bq update \
    --use_legacy_sql=false \
    --view \
    'SELECT
      name,
      number
    FROM
      `bigquery-public-data.usa_names.usa_1910_current`
    WHERE
      gender = "M"
    ORDER BY
      number DESC;' \
    myotherproject:mydataset.myview

API

You can update a view by calling the tables.patch method with a table resource that contains an updated view property. Because the tables.update method replaces the entire table resource, the tables.patch method is preferred.

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"

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

// updateView demonstrates updating the query metadata that defines a logical view.
func updateView(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
	}

	newMeta := bigquery.TableMetadataToUpdate{
		// This example updates a view into the shakespeare dataset to exclude works named after kings.
		ViewQuery: "SELECT word, word_count, corpus, corpus_date FROM `bigquery-public-data.samples.shakespeare` WHERE corpus NOT LIKE '%king%'",
	}

	if _, err := view.Update(ctx, newMeta, meta.ETag); 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.

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.TableId;
import com.google.cloud.bigquery.TableInfo;
import com.google.cloud.bigquery.ViewDefinition;

// Sample to update query on a view
public class UpdateViewQuery {

  public static void runUpdateViewQuery() {
    // TODO(developer): Replace these variables before running the sample.
    String datasetName = "MY_DATASET_NAME";
    String tableName = "MY_TABLE_NAME";
    String viewName = "MY_VIEW_NAME";
    String updateQuery =
        String.format("SELECT TimestampField, StringField FROM %s.%s", datasetName, tableName);
    updateViewQuery(datasetName, viewName, updateQuery);
  }

  public static void updateViewQuery(String datasetName, String viewName, String query) {
    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();

      // Retrieve existing view metadata
      TableInfo viewMetadata = bigquery.getTable(TableId.of(datasetName, viewName));

      // Update view query
      ViewDefinition viewDefinition = viewMetadata.getDefinition();
      viewDefinition.toBuilder().setQuery(query).build();

      // Set metadata
      bigquery.update(viewMetadata.toBuilder().setDefinition(viewDefinition).build());

      System.out.println("View query updated successfully");
    } catch (BigQueryException e) {
      System.out.println("View query was not updated. \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 and create a client
const {BigQuery} = require('@google-cloud/bigquery');
const bigquery = new BigQuery();

async function updateViewQuery() {
  // Updates a view named "my_existing_view" in "my_dataset".

  /**
   * TODO(developer): Uncomment the following lines before running the sample.
   */
  // const datasetId = "my_existing_dataset"
  // const tableId = "my_existing_table"
  const dataset = await bigquery.dataset(datasetId);

  // This example updates a view into the USA names dataset to include state.
  const newViewQuery = `SELECT name, state 
  FROM \`bigquery-public-data.usa_names.usa_1910_current\`
  LIMIT 10`;

  // Retrieve existing view
  const [view] = await dataset.table(tableId).get();

  // Retrieve existing view metadata
  const [metadata] = await view.getMetadata();

  // Update view query
  metadata.view = newViewQuery;

  // Set metadata
  await view.setMetadata(metadata);

  console.log(`View ${tableId} updated.`);
}

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"
source_id = "my-project.my_dataset.my_table"
view = bigquery.Table(view_id)

# The source table in this example is created from a CSV file in Google
# Cloud Storage located at
# `gs://cloud-samples-data/bigquery/us-states/us-states.csv`. It contains
# 50 US states, while the view returns only those states with names
# starting with the letter 'M'.
view.view_query = f"SELECT name, post_abbr FROM `{source_id}` WHERE name LIKE 'M%'"

# Make an API request to update the query property of the view.
view = client.update_table(view, ["view_query"])
print(f"Updated {view.table_type}: {str(view.reference)}")

Note: If you update the datasets referenced by the query of an authorized view, you must authorize the view access to any new underlying datasets.

Updating a view's expiration time

You can set a default table expiration time at the dataset level (which affects both tables and views), or you can set a view's expiration time when the view is created. If you set the expiration when the view is created, the dataset's default table expiration is ignored. If you do not set a default table expiration at the dataset level, and you do not set an expiration when the view is created, the view never expires and you must delete the view manually.

At any point after the view is created, you can update the view's expiration time by:

• Using the Google Cloud console
• Using a Data definition language (DDL) statement written in GoogleSQL syntax
• Using the bq command-line tool's bq update command
• Calling the tables.patch API method
• Using the client libraries

Note: If you set an expiration time that has already passed, the view is deleted immediately.

To update a view's expiration time:

Console

1. In the navigation pane, select your view.

2. On the view Details page, click the Details tab.

3. To the right of View info, click the edit icon (pencil).

4. In the View info dialog, for View expiration, click Specify date.

5. In the date picker, enter the expiration date and time and then click Ok.

6. Click Update. The updated expiration time appears in the View info section.

SQL

Use the ALTER VIEW SET OPTIONS DDL statement:

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

   Go to BigQuery

2. In the query editor, enter the following statement:

    ALTER VIEW DATASET_ID.MY_VIEW
    SET OPTIONS (
     expiration_timestamp = TIMESTAMP('NEW_TIMESTAMP'));

   Replace the following:

   • DATASET_ID: the ID of the dataset containing your view
   • MY_VIEW: the name of the view to be updated
   • NEW_TIMESTAMP: a TIMESTAMP value

3. Click Run.

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

bq

Issue the bq update command with the --expiration flag. If you are updating a view in a project other than your default project, add the project ID to the dataset name in the following format: project_id:dataset.

bq update \
    --expiration integer \
    project_id:dataset.view

Replace the following::

• integer: the default lifetime (in seconds) for the table. The minimum value is 3600 seconds (one hour). The expiration time evaluates to the current time plus the integer value.
• project_id: your project ID
• dataset: the name of the dataset containing the view you're updating
• view: the name of the view you're updating

Examples

Enter the following command to update the expiration time of myview in mydataset to 5 days (432000 seconds). mydataset is in your default project.

bq update \
    --expiration 432000 \
    mydataset.myview

Enter the following command to update the expiration time of myview in mydataset to 5 days (432000 seconds). mydataset is in myotherproject, not your default project.

bq update \
    --expiration 432000 \
    myotherproject:mydataset.myview

API

Call the tables.patch method and use the expirationTime property in the table resource. Because the tables.update method replaces the entire table resource, the tables.patch method is preferred. When you use the REST API, the view's expiration is expressed in milliseconds.

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

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

// updateTableExpiration demonstrates setting the table expiration of a table to a specific point in time
// in the future, at which time it will be deleted.
func updateTableExpiration(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()

	tableRef := client.Dataset(datasetID).Table(tableID)
	meta, err := tableRef.Metadata(ctx)
	if err != nil {
		return err
	}
	update := bigquery.TableMetadataToUpdate{
		ExpirationTime: time.Now().Add(time.Duration(5*24) * time.Hour), // table expiration in 5 days.
	}
	if _, err = tableRef.Update(ctx, update, meta.ETag); 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.

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

Table beforeTable = bigquery.getTable(datasetName, tableName);

// Set table to expire 5 days from now.
long expirationMillis = DateTime.now().plusDays(5).getMillis();
TableInfo tableInfo = beforeTable.toBuilder()
        .setExpirationTime(expirationMillis)
        .build();
Table afterTable = bigquery.update(tableInfo);

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 updateTableExpiration() {
  // Updates a table's expiration.

  /**
   * TODO(developer): Uncomment the following lines before running the sample.
   */
  // const datasetId = 'my_dataset', // Existing dataset
  // const tableId = 'my_table', // Existing table
  // const expirationTime = Date.now() + 1000 * 60 * 60 * 24 * 5 // 5 days from current time in ms

  // Retreive current table metadata
  const table = bigquery.dataset(datasetId).table(tableId);
  const [metadata] = await table.getMetadata();

  // Set new table expiration to 5 days from current time
  metadata.expirationTime = expirationTime.toString();
  const [apiResponse] = await table.setMetadata(metadata);

  const newExpirationTime = apiResponse.expirationTime;
  console.log(`${tableId} expiration: ${newExpirationTime}`);
}

Python

Updating a view's expiration is the same process as updating a table's expiration.

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()

# TODO(dev): Change table_id to the full name of the table you want to update.
table_id = "your-project.your_dataset.your_table_name"

# TODO(dev): Set table to expire for desired days days from now.
expiration = datetime.datetime.now(datetime.timezone.utc) + datetime.timedelta(
    days=5
)
table = client.get_table(table_id)  # Make an API request.
table.expires = expiration
table = client.update_table(table, ["expires"])  # API request

print(f"Updated {table_id}, expires {table.expires}.")

Updating a view's description

You can update a view's description by:

• Using the Google Cloud console
• Using a Data definition language (DDL) statement written in GoogleSQL syntax
• Using the bq command-line tool's bq update command
• Calling the tables.patch API method
• Using the client libraries

To update a view's description:

Console

You cannot add a description when you create a view using the Google Cloud console. After the view is created, you can add a description on the Details page.

1. In the Explorer panel, expand your project and dataset, then select the view.

2. Click the Details tab.

3. Click the pencil icon next to Description.

   

4. Enter a description in the dialog box. Click Update to save the new description.

SQL

Use the ALTER VIEW SET OPTIONS DDL statement:

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

   Go to BigQuery

2. In the query editor, enter the following statement:

    ALTER VIEW DATASET_ID.MY_VIEW
    SET OPTIONS (
     description = 'NEW_DESCRIPTION');

   Replace the following:

   • DATASET_ID: the ID of the dataset containing your view
   • MY_VIEW: the name of the view to be updated
   • NEW_DESCRIPTION: the new view description

3. Click Run.

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

bq

Issue the bq update command with the --description flag. If you are updating a view in a project other than your default project, add the project ID to the dataset name in the following format: [PROJECT_ID]:[DATASET].

bq update \
    --description "description" \
    project_id:dataset.view

Replace the following:

• description: the text describing the view in quotes
• project_id: your project ID.
• dataset: the name of the dataset containing the view you're updating
• view: the name of the view you're updating

Examples

Enter the following command to change the description of myview in mydatasetto "Description of myview." mydataset is in your default project.

bq update \
    --description "Description of myview" \
    mydataset.myview

Enter the following command to change the description of myview in mydataset to "Description of myview." mydataset is in myotherproject, not your default project.

bq update \
    --description "Description of myview" \
    myotherproject:mydataset.myview

API

Call the tables.patch method and use the description property to update the view's description in the table resource. Because the tables.update method replaces the entire table resource, the tables.patch method is preferred.

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"

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

// updateTableDescription demonstrates how to fetch a table's metadata and updates the Description metadata.
func updateTableDescription(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()

	tableRef := client.Dataset(datasetID).Table(tableID)
	meta, err := tableRef.Metadata(ctx)
	if err != nil {
		return err
	}
	update := bigquery.TableMetadataToUpdate{
		Description: "Updated description.",
	}
	if _, err = tableRef.Update(ctx, update, meta.ETag); err != nil {
		return err
	}
	return nil
}

Java

Updating a view's description is the same process as updating a table's description.

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.

// String datasetName = "my_dataset_name";
// String tableName = "my_table_name";
// String newDescription = "new_description";

Table beforeTable = bigquery.getTable(datasetName, tableName);
TableInfo tableInfo = beforeTable.toBuilder()
    .setDescription(newDescription)
    .build();
Table afterTable = bigquery.update(tableInfo);

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 updateTableDescription() {
  // Updates a table's description.

  // Retreive current table metadata
  const table = bigquery.dataset(datasetId).table(tableId);
  const [metadata] = await table.getMetadata();

  // Set new table description
  const description = 'New table description.';
  metadata.description = description;
  const [apiResponse] = await table.setMetadata(metadata);
  const newDescription = apiResponse.description;

  console.log(`${tableId} description: ${newDescription}`);
}

Python

Updating a view's description is the same process as updating a table's description.

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()
# project = client.project
# dataset_ref = bigquery.DatasetReference(project, dataset_id)
# table_ref = dataset_ref.table('my_table')
# table = client.get_table(table_ref)  # API request

assert table.description == "Original description."
table.description = "Updated description."

table = client.update_table(table, ["description"])  # API request

assert table.description == "Updated description."

Copy views

You can copy a view using the Google Cloud console.

You cannot copy a view by using the bq command-line tool, the REST API, or the client libraries, but you can copy a view in the target dataset.

Required permissions

To copy a view in the Google Cloud 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.

Note: bigquery.jobs.create permissions are not required to copy a view. The Google Cloud console does not generate a copy job when you copy a view.

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

Copy a view

To copy a view:

1. In the Explorer panel, expand your project and dataset, then select the view.

2. In the details panel, click Copy view.

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

Limits for copy jobs apply. For more information, see Quotas and limits.

Rename a view

Currently, you can rename a view only when you use the Google Cloud 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 Google Cloud 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.

Caution: Deleting a view cannot be undone. If you recreate an authorized view with the same name as the deleted view, you must add the new view to the authorized views list of the source dataset.

Deleting a view also deletes any permissions associated with this view. When you recreate a deleted view, you must also manually reconfigure any access permissions previously associated with it.

Note: You cannot recover views directly, but you can recover the view creation statement by searching for the corresponding audit log activity.

• For information about using the log explorer to query the activity log by audit log name, see the audit logs overview.
• For information about using projects/PROJECT_ID/logs/cloudaudit.googleapis.com%2Factivity, see BigQuery Data Policy audit logging.

Required permissions

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

• bigquery.tables.delete

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

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

   Go to BigQuery

2. In the Explorer panel, expand your project and dataset, then select the view.

3. In the details panel, click Delete view.

4. Type "delete" in the dialog, and click Delete to confirm.

SQL

Use the DROP VIEW DDL statement:

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

   Go to BigQuery

2. In the query editor, enter the following statement:

   DROP VIEW mydataset.myview;

   Replace the following:

   • DATASET_ID: the ID of the dataset containing your view
   • MY_VIEW: the name of the view to be updated
   • NEW_DESCRIPTION: the new view description

3. Click Run.

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

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 Google Cloud 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

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.

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

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.

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

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.

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

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.

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

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

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.

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

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

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

Restore a view

You can't restore a deleted view directly, but there are workarounds for certain scenarios:

• If a view is deleted because the parent dataset was deleted, then you can undelete the dataset to retrieve the view.
• If a view is deleted explicitly, then you can recreate the view by using the last query that was used to create or update the view. You can find the query definition of the view creation or update operation in logs.

View security

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

What's next

• For information on creating views, see Create views.
• For information on creating an authorized view, see Creating authorized views.
• For information on getting view metadata, see Get information about views.