Update dataset properties

This document describes how to update dataset properties in BigQuery. After you create a dataset, you can update the following dataset properties:

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 dataset properties, you need the following IAM permissions:

  • bigquery.datasets.update
  • bigquery.datasets.setIamPolicy (only required when updating dataset access controls in the Google Cloud console)

The roles/bigquery.dataOwner predefined IAM role includes the permissions that you need to update dataset properties.

Additionally, if you have the bigquery.datasets.create permission, you can update properties of the datasets that you create.

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

Update dataset descriptions

You can update a dataset's description in the following ways:

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

To update a dataset's description:

Console

  1. In the left pane, click Explorer:

    Highlighted button for the Explorer pane.

    If you don't see the left pane, click Expand left pane to open the pane.

  2. In the Explorer pane, expand your project, click Datasets, and then click a dataset.

  3. In the Details pane, click Edit details to edit the description text.

    In the Edit detail dialog that appears, do the following:

    1. In the Description field, enter a description or edit the existing description.
    2. To save the new description text, click Save.

SQL

To update a dataset's description, use the ALTER SCHEMA SET OPTIONS statement to set the description option.

The following example sets the description on a dataset named mydataset:

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

    Go to BigQuery

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

     ALTER SCHEMA mydataset
 SET OPTIONS (
     description = 'Description of mydataset');

  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 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 update \
--description "string" \
project_id:dataset

Replace the following:

  • string: the text that describes the dataset, in quotes
  • project_id: your project ID
  • dataset: the name of the dataset that you're updating

Examples:

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

bq update --description "Description of mydataset" mydataset

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

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

API

Call datasets.patch and update the description property in the dataset resource. Because the datasets.update method replaces the entire dataset resource, the datasets.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"
)

// updateDatasetDescription demonstrates how the Description metadata of a dataset can
// be read and modified.
func updateDatasetDescription(projectID, datasetID string) error {
	// projectID := "my-project-id"
	// datasetID := "mydataset"
	ctx := context.Background()
	client, err := bigquery.NewClient(ctx, projectID)
	if err != nil {
		return fmt.Errorf("bigquery.NewClient: %v", err)
	}
	defer client.Close()

	ds := client.Dataset(datasetID)
	meta, err := ds.Metadata(ctx)
	if err != nil {
		return err
	}
	update := bigquery.DatasetMetadataToUpdate{
		Description: "Updated Description.",
	}
	if _, err = ds.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.

Create a Dataset.Builder instance from an existing Dataset instance with the Dataset.toBuilder() method. Configure the dataset builder object. Build the updated dataset with the Dataset.Builder.build() method, and call the Dataset.update() method to send the update to the API. 
import com.google.cloud.bigquery.BigQuery;
import com.google.cloud.bigquery.BigQueryException;
import com.google.cloud.bigquery.BigQueryOptions;
import com.google.cloud.bigquery.Dataset;

public class UpdateDatasetDescription {

  public static void runUpdateDatasetDescription() {
    // TODO(developer): Replace these variables before running the sample.
    String datasetName = "MY_DATASET_NAME";
    String newDescription = "this is the new dataset description";
    updateDatasetDescription(datasetName, newDescription);
  }

  public static void updateDatasetDescription(String datasetName, String newDescription) {
    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();

      Dataset dataset = bigquery.getDataset(datasetName);
      bigquery.update(dataset.toBuilder().setDescription(newDescription).build());
      System.out.println("Dataset description updated successfully to " + newDescription);
    } catch (BigQueryException e) {
      System.out.println("Dataset description 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
const {BigQuery} = require('@google-cloud/bigquery');
const bigquery = new BigQuery();

async function updateDatasetDescription() {
  // Updates a dataset's description.

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

  // Set new dataset description
  const description = 'New dataset description.';
  metadata.description = description;

  const [apiResponse] = await dataset.setMetadata(metadata);
  const newDescription = apiResponse.description;

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

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.

Configure the Dataset.description property and call Client.update_dataset() to send the update to the API. 

from google.cloud import bigquery

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

# TODO(developer): Set dataset_id to the ID of the dataset to fetch.
# dataset_id = 'your-project.your_dataset'

dataset = client.get_dataset(dataset_id)  # Make an API request.
dataset.description = "Updated description."
dataset = client.update_dataset(dataset, ["description"])  # Make an API request.

full_dataset_id = "{}.{}".format(dataset.project, dataset.dataset_id)
print(
    "Updated dataset '{}' with description '{}'.".format(
        full_dataset_id, dataset.description
    )
)

Update default table expiration times

You can update a dataset's default table expiration time in the following ways:

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

You can set a default table expiration time at the dataset level, or you can set a table's expiration time when the table is created. If you set the expiration when the table is created, the dataset's default table expiration is ignored. If you don't set a default table expiration at the dataset level, and you don't set a table expiration when the table is created, the table never expires and you must delete the table manually. When a table expires, it's deleted along with all of the data it contains.

When you update a dataset's default table expiration setting:

  • If you change the value from Never to a defined expiration time, any tables that already exist in the dataset won't expire unless the expiration time was set on the table when it was created.
  • If you are changing the value for the default table expiration, any tables that already exist expire according to the original table expiration setting. Any new tables created in the dataset have the new table expiration setting applied unless you specify a different table expiration on the table when it is created.

The value for default table expiration is expressed differently depending on where the value is set. Use the method that gives you the appropriate level of granularity:

  • In the Google Cloud console, expiration is expressed in days.
  • In the bq command-line tool, expiration is expressed in seconds.
  • In the API, expiration is expressed in milliseconds.

To update the default expiration time for a dataset:

Console

  1. In the left pane, click Explorer:

    Highlighted button for the Explorer pane.

  2. In the Explorer pane, expand your project, click Datasets, and then click a dataset.

  3. In the Details tab, click Edit details to edit the expiration time.

  4. In the Edit detail dialog, in the Default table expiration section, select Enable table expiration and enter a value for Default maximum table age.

  5. Click Save.

SQL

To update the default table expiration time, use the ALTER SCHEMA SET OPTIONS statement to set the default_table_expiration_days option.

The following example updates the default table expiration for a dataset named mydataset.

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

    Go to BigQuery

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

     ALTER SCHEMA mydataset
 SET OPTIONS(
     default_table_expiration_days = 3.75);

  3. Click Run.

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

bq

To update the default expiration time for newly created tables in a dataset, enter the bq update command with the --default_table_expiration flag. If you are updating 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 update \
--default_table_expiration integer \
project_id:dataset

Replace the following:

  • integer: the default lifetime, in seconds, for newly created tables. The minimum value is 3600 seconds (one hour). The expiration time evaluates to the current UTC time plus the integer value. Specify 0 to remove the existing expiration time. Any table created in the dataset is deleted integer seconds after its creation time. This value is applied if you do not set a table expiration when the table is created.
  • project_id: your project ID.
  • dataset: the name of the dataset that you're updating.

Examples:

Enter the following command to set the default table expiration for new tables created in mydataset to two hours (7200 seconds) from the current time. The dataset is in your default project.

bq update --default_table_expiration 7200 mydataset

Enter the following command to set the default table expiration for new tables created in mydataset to two hours (7200 seconds) from the current time. The dataset is in myotherproject, not your default project.

bq update --default_table_expiration 7200 myotherproject:mydataset

API

Call datasets.patch and update the defaultTableExpirationMs property in the dataset resource. The expiration is expressed in milliseconds in the API. Because the datasets.update method replaces the entire dataset resource, the datasets.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"
	"time"

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

// updateDatasetDefaultExpiration demonstrats setting the default expiration of a dataset
// to a specific retention period.
func updateDatasetDefaultExpiration(projectID, datasetID string) error {
	// projectID := "my-project-id"
	// datasetID := "mydataset"
	ctx := context.Background()
	client, err := bigquery.NewClient(ctx, projectID)
	if err != nil {
		return fmt.Errorf("bigquery.NewClient: %v", err)
	}
	defer client.Close()

	ds := client.Dataset(datasetID)
	meta, err := ds.Metadata(ctx)
	if err != nil {
		return err
	}
	update := bigquery.DatasetMetadataToUpdate{
		DefaultTableExpiration: 24 * time.Hour,
	}
	if _, err := client.Dataset(datasetID).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.

Create a Dataset.Builder instance from an existing Dataset instance with the Dataset.toBuilder() method. Configure the dataset builder object. Build the updated dataset with the Dataset.Builder.build() method, and call the Dataset.update() method to send the update to the API.

Configure the default expiration time with the Dataset.Builder.setDefaultTableLifetime() method. 

import com.google.cloud.bigquery.BigQuery;
import com.google.cloud.bigquery.BigQueryException;
import com.google.cloud.bigquery.BigQueryOptions;
import com.google.cloud.bigquery.Dataset;
import java.util.concurrent.TimeUnit;

public class UpdateDatasetExpiration {

  public static void runUpdateDatasetExpiration() {
    // TODO(developer): Replace these variables before running the sample.
    String datasetName = "MY_DATASET_NAME";
    updateDatasetExpiration(datasetName);
  }

  public static void updateDatasetExpiration(String datasetName) {
    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();

      // Update dataset expiration to one day
      Long newExpiration = TimeUnit.MILLISECONDS.convert(1, TimeUnit.DAYS);

      Dataset dataset = bigquery.getDataset(datasetName);
      bigquery.update(dataset.toBuilder().setDefaultTableLifetime(newExpiration).build());
      System.out.println("Dataset description updated successfully to " + newExpiration);
    } catch (BigQueryException e) {
      System.out.println("Dataset expiration was not updated \n" + e.toString());
    }
  }
}