Managing datasets

This document describes how to manage datasets in BigQuery. After creating a dataset, you can manage the dataset in the following ways:

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

Renaming datasets

Currently, you cannot change the name of an existing dataset, and you cannot copy a dataset and give it a new name. If you need to change the dataset name, follow these steps to recreate the dataset:

1. Create a new dataset and specify the new name.

2. Copy the tables from the old dataset to the new one.

3. Recreate the views in the new dataset.

4. Delete the old dataset to avoid additional storage costs.

Copying datasets

Currently, you cannot copy a dataset. Instead follow these steps to recreate the dataset:

1. Create a new dataset. Because dataset names must be unique per project, you must assign a new name to the dataset if you are recreating it in the same project.

2. Copy the tables from the old dataset to the new one.

3. Recreate the views in the new dataset.

4. Delete the old dataset to avoid additional storage costs.

Deleting datasets

You can delete a dataset by:

• Using the GCP Console or the classic BigQuery web UI
• Using the bq rm CLI command
• Calling the datasets.delete API method.

Required permissions

To delete a dataset, you must have OWNER access at the dataset level, or you must be assigned a project-level IAM role that includes bigquery.datasets.delete permissions. If the dataset contains tables, bigquery.tables.delete is also required. The following predefined, project-level IAM roles include both bigquery.datasets.delete and bigquery.tables.delete permissions:

• bigquery.dataOwner
• bigquery.admin

In addition, because the bigquery.user role has bigquery.datasets.create permissions, a user assigned to the bigquery.user role can delete 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 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 dataset

When you delete a dataset using the web UI, tables in the dataset (and the data they contain) are deleted. When you delete a dataset using the CLI, you must use the -r flag to delete the dataset's tables.

After you delete a dataset, it cannot be recovered, restored, or undeleted. Deleting a dataset is permanent.

To delete a dataset:

Classic UI

Caution: Deleting a dataset cannot be undone.

1. Click the down arrow icon next to your dataset name in the navigation, and then click Delete dataset.

2. In the Delete Dataset dialog:

   • For Dataset ID, enter the name of the dataset to delete.

   • Click OK.

     

Note: When you delete a dataset using the web UI, the tables are automatically removed.

Command-line

Caution: Deleting a dataset cannot be undone.

Use the bq rm command with the (optional) --dataset or -d shortcut flag to delete a dataset. When you use the CLI to remove a dataset, you must confirm the command. You can use the -f flag to skip confirmation.

In addition, if the dataset contains tables, you must use the -r flag to remove all tables in the dataset. If you are deleting a table 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 -r -f -d [PROJECT_ID]:[DATASET]

Where:

• [PROJECT_ID] is your project ID.
• [DATASET] is the name of the dataset you're deleting.

Examples:

Enter the following command to remove mydataset and all the tables in it from your default project. The command uses the optional -d shortcut.

bq rm -r -d mydataset

When prompted, type y and press enter.

Enter the following command to remove mydataset and all the tables in it from myotherproject. The command does not use the optional -d shortcut. The -f flag is used to skip confirmation.

bq rm -r -f myotherproject:mydataset

Note: You can enter the bq ls command to confirm that the dataset was deleted.

API

Caution: Deleting a dataset cannot be undone.

Call the datasets.delete method to delete the dataset and set the deleteContents parameter to true to delete the tables in it.

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

if err := client.Dataset(datasetID).Delete(ctx); err != nil {
	return fmt.Errorf("Failed to delete dataset: %v", err)
}

Java

Before trying this sample, follow the Java setup instructions in the BigQuery Quickstart Using Client Libraries . For more information, see the BigQuery Java API reference documentation .

View on GitHub Feedback

DatasetId datasetId = DatasetId.of(projectId, datasetName);
boolean deleted = bigquery.delete(datasetId, DatasetDeleteOption.deleteContents());
if (deleted) {
  // the dataset was deleted
} else {
  // the dataset was not found
}

Node.js

Before trying this sample, follow the Node.js setup instructions in the BigQuery Quickstart Using Client Libraries . For more information, see the BigQuery Node.js API reference documentation .

View on GitHub Feedback

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

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

// Creates a reference to the existing dataset
const dataset = bigquery.dataset(datasetId);

// Deletes the dataset
await dataset.delete();
console.log(`Dataset ${dataset.id} 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 .

View on GitHub Feedback

use Google\Cloud\BigQuery\BigQueryClient;

/** Uncomment and populate these variables in your code */
// $projectId = 'The Google project ID';
// $datasetId = 'The BigQuery dataset ID';

$bigQuery = new BigQueryClient([
    'projectId' => $projectId,
]);
$dataset = $bigQuery->dataset($datasetId);
$table = $dataset->delete();
printf('Deleted dataset %s' . PHP_EOL, $datasetId);

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

# Delete a dataset that does not contain any tables
# dataset1_id = 'my_empty_dataset'
dataset1_ref = client.dataset(dataset1_id)
client.delete_dataset(dataset1_ref)  # API request

print('Dataset {} deleted.'.format(dataset1_id))

# Use the delete_contents parameter to delete a dataset and its contents
# dataset2_id = 'my_dataset_with_tables'
dataset2_ref = client.dataset(dataset2_id)
client.delete_dataset(dataset2_ref, delete_contents=True)  # API request

print('Dataset {} deleted.'.format(dataset2_id))

Ruby

Before trying this sample, follow the Ruby setup instructions in the BigQuery Quickstart Using Client Libraries . For more information, see the BigQuery Ruby API reference documentation .

View on GitHub Feedback

require "google/cloud/bigquery"

def delete_dataset dataset_id = "my_empty_dataset"
  bigquery = Google::Cloud::Bigquery.new

  # Delete a dataset that does not contain any tables
  dataset = bigquery.dataset dataset_id
  dataset.delete
  puts "Dataset #{dataset_id} deleted."
end

Next steps

• For more information on creating datasets, see Creating datasets.
• For more information on updating datasets, see Updating datasets.