Updating view properties

This document describes how to update view properties or metadata. After creating a view, you can update the following view properties:

• SQL query
• Expiration time
• Description
• Labels

Before you begin

Grant Identity and Access Management (IAM) roles that give users the necessary permissions to perform each task in this document.

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.

If you are updating 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 Google Standard SQL in the API or bq command-line tool. You cannot update a legacy SQL view to Google Standard SQL 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 Google Standard SQL or to update the query dialect from legacy SQL to Google Standard SQL, 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 Google 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

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 Google Standard SQL 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.

View on GitHub Feedback

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.

View on GitHub Feedback

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.

View on GitHub Feedback

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

View on GitHub Feedback

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)}")

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 Google Standard SQL 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

Data definition language (DDL) statements allow you to create and modify tables and views using Google Standard SQL query syntax.

See more on Using data definition language statements.

Use a DDL statement to update a view's expiration time:

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 Running interactive queries.

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.

View on GitHub Feedback

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.

View on GitHub Feedback

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.

View on GitHub Feedback

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

View on GitHub Feedback

import datetime
import pytz

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

You can update a view's description by:

• Using the Google Cloud console
• Using a Data definition language (DDL) statement written in Google Standard SQL 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

Data definition language (DDL) statements allow you to create and modify tables and views using Google Standard SQL query syntax.

See more on Using data definition language statements.

Use a DDL statement to update a view's description:

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 Running interactive queries.

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.

View on GitHub Feedback

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.

View on GitHub Feedback

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

View on GitHub Feedback

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

View on GitHub Feedback

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

View security

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

Next steps

• For more information on managing views including copying a view and deleting a view, see Managing views.
• 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.