Managing Views

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

  • Update a view's:
    • SQL query
    • Expiration time
    • Description
    • Labels
  • Copy (recreate) a view
  • Rename (recreate) a view
  • Delete a view

Updating view properties

You can update a view's:

Required permissions

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

In addition, because the bigquery.user role has bigquery.datasets.create permissions, a user assigned to the bigquery.user role can update any view that user creates in the dataset. 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 it and all the tables and views in it.

For more information on IAM roles and permissions in BigQuery, see: access control.

Updating a view's SQL query

You can update the SQL query used to define a view by using the BigQuery web UI, the command-line tool's bq update command, or by calling the tables.patch API method. This includes changing the SQL dialect from legacy SQL to standard SQL in the CLI or API. You cannot update a legacy SQL view to standard SQL in the BigQuery web UI.

To update a view's SQL query:

Web UI

  1. Select the view.

  2. In the View Details panel, click Details.

  3. Below the Query box, click Edit Query.

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

    Update view

Command-line

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

If your query references external user-defined function resources stored in Google 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 Standard SQL 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]

Where:

  • [PATH_TO_FILE] is the URI or local filesystem 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] is a valid standard SQL query.
  • [PROJECT_ID] is your project ID.
  • [DATASET] is a dataset that contains the view.
  • [VIEW] is the name of the view you want to update.

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.

Python

For more on installing and creating a BigQuery client, refer to BigQuery Client Libraries.

# from google.cloud import bigquery
# client = bigquery.Client()
# project = 'my-project'
# source_dataset_id = 'my_source_dataset'
# source_table_id = 'us_states'
# shared_dataset_ref = client.dataset('my_shared_dataset')

# This example shows how to update a shared view of a source table of
# US States. The view's query will be updated to contain only states with
# names starting with 'M'.
view_ref = shared_dataset_ref.table('my_shared_view')
view = bigquery.Table(view_ref)
sql_template = (
    'SELECT name, post_abbr FROM `{}.{}.{}` WHERE name LIKE "M%"')
view.view_query = sql_template.format(
    project, source_dataset_id, source_table_id)
view = client.update_table(view, ['view_query'])  # API request

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 using the BigQuery web UI, the CLI's bq update command, or the API's tables.patch method.

When you update a view's expiration time, you must calculate the expiration based on the view's creation time. For example, if your view was created on January 3, 2018, and today is January 5, if you set the expiration time at 1 day using the BigQuery web UI, the expiration time is considered "in the past" and the view is deleted immediately.

To update a view's expiration time:

Web UI

You cannot add an expiration time when you create a view using the web UI. After a view is created, you can add or update the expiration on the View Details page.

  1. In the navigation pane, select your view.

  2. On the View Details page, click Details.

  3. For Expiration Time, click Edit.

  4. In the Update Expiration dialog, click In and enter an expiration time in days.

  5. Click OK. The updated expiration time appears on the Details page.

    View expiration

CLI

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]

Where:

  • [INTEGER] is 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] is your project ID.
  • [DATASET] is the name of the dataset that contains the view you're updating.
  • [VIEW] is 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 (43200 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 (43200 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 to update the view's expiration in milliseconds. Because the tables.update method replaces the entire table resource, the tables.patch method is preferred.

Python

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

For more on installing and creating a BigQuery client, refer to BigQuery Client Libraries.

import datetime
import pytz

# from google.cloud import bigquery
# client = bigquery.Client()
# table_ref = client.dataset('my_dataset').table('my_table')
# table = client.get_table(table_ref)  # API request

assert table.expires is None

# set table to expire 5 days from now
expiration = datetime.datetime.now(pytz.utc) + datetime.timedelta(days=5)
table.expires = expiration
table = client.update_table(table, ['expires'])  # API request

# expiration is stored in milliseconds
margin = datetime.timedelta(microseconds=1000)
assert expiration - margin <= table.expires <= expiration + margin

Updating a view's description

To update a view's description, you can use the BigQuery web UI, the bq update CLI command, or the tables.patch API method.

To update a view's description:

Web UI

You cannot add a description when you create a view using the BigQuery web UI. After the view is created, you can add a description on the View Details page.

  1. In the navigation pane, select your view.

  2. On the View Details page, click Details.

  3. In the Description section, click Describe this view to open the description box.

  4. Enter a description in the box. When you click away from the box, the text is saved.

    Table description

CLI

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]

Where:

  • [DESCRIPTION] is the text describing the view in quotes.
  • [PROJECT_ID] is your project ID.
  • [DATASET] is the name of the dataset that contains the view you're updating.
  • [VIEW] is 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.

Java

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

For more on installing and creating a BigQuery client, refer to BigQuery 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);

Python

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

For more on installing and creating a BigQuery client, refer to BigQuery Client Libraries.

# from google.cloud import bigquery
# client = bigquery.Client()
# table_ref = client.dataset('my_dataset').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.'

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:

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

CLI

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 using the BigQuery web UI, the command-line tools' bq rm command, or by calling the tables.delete API method. Currently, you can only delete one view at a time.

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

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:

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:

Web UI

  1. Click the down arrow icon 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

API

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

C#

For more on installing and creating a BigQuery client, refer to BigQuery Client Libraries.

public void DeleteTable(string datasetId, string tableId, BigQueryClient client)
{
    client.DeleteTable(_projectId, datasetId, tableId);
}

Go

For more on installing and creating a BigQuery client, refer to BigQuery Client Libraries.

table := client.Dataset(datasetID).Table(tableID)
if err := table.Delete(ctx); err != nil {
	return err
}

Java

For more on installing and creating a BigQuery client, refer to BigQuery 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

For more on installing and creating a BigQuery client, refer to BigQuery Client Libraries.

// Imports the Google Cloud client library
const BigQuery = require('@google-cloud/bigquery');

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

// Creates a client
const bigquery = new BigQuery({
  projectId: projectId,
});

// Deletes the table
bigquery
  .dataset(datasetId)
  .table(tableId)
  .delete()
  .then(() => {
    console.log(`Table ${tableId} deleted.`);
  })
  .catch(err => {
    console.error('ERROR:', err);
  });

PHP

For more on installing and creating a BigQuery client, refer to BigQuery Client Libraries.

use Google\Cloud\BigQuery\BigQueryClient;

/**
 * @param string $projectId The Google project ID.
 * @param string $datasetId The BigQuery dataset ID.
 * @param string $tableId   The BigQuery table ID.
 */
function delete_table($projectId, $datasetId, $tableId)
{
    $bigQuery = new BigQueryClient([
        'projectId' => $projectId,
    ]);
    $dataset = $bigQuery->dataset($datasetId);
    $table = $dataset->table($tableId);
    $table->delete();
}

Python

For more on installing and creating a BigQuery client, refer to BigQuery Client Libraries.

# from google.cloud import bigquery
# client = bigquery.Client()
# dataset_id = 'my_dataset'
# table_id = 'my_table'

table_ref = client.dataset(dataset_id).table(table_id)
client.delete_table(table_ref)  # API request

print('Table {}:{} deleted.'.format(dataset_id, table_id))

Ruby

For more on installing and creating a BigQuery client, refer to BigQuery Client Libraries.

# project_id = "Your Google Cloud project ID"
# dataset_id = "ID of the dataset delete table from"
# table_id   = "ID of the table to delete"

require "google/cloud/bigquery"

bigquery = Google::Cloud::Bigquery.new project: project_id
dataset  = bigquery.dataset dataset_id
table    = dataset.table table_id

table.delete

puts "Deleted table: #{table_id}"

Next steps

Was this page helpful? Let us know how we did:

Send feedback about...