Managing datasets

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

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. Copy the views to the new dataset or 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. Copy the views to the new dataset or 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:

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:

Console

  1. Select your dataset from the Resources pane, and then click Delete dataset on the right side of the window.

    Delete dataset

  2. In the Delete dataset dialog, type the name of the dataset into the text box, and then click Delete.

Classic UI

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

Command-line

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

API

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 . 

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 . 

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 . 

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

async function deleteDataset() {
  // Deletes a dataset named "my_dataset".

  /**
   * TODO(developer): Uncomment the following lines before running the sample.
   */
  // const datasetId = "my_new_dataset";

  // Create a client
  const bigqueryClient = new BigQuery();

  // Create a reference to the existing dataset
  const dataset = bigqueryClient.dataset(datasetId);

  // Delete the dataset and its contents
  await dataset.delete({force: true});
  console.log(`Dataset ${dataset.id} deleted.`);
}
deleteDataset();

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 . 

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 . 

from google.cloud import bigquery

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

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

# Use the delete_contents parameter to delete a dataset and its contents
# Use the not_found_ok parameter to not receive an error if the dataset has already been deleted.
client.delete_dataset(dataset_id, delete_contents=True, not_found_ok=True)

print("Deleted dataset '{}'.".format(dataset_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 . 

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

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

Send feedback about...

This page
Documentation feedback
BigQuery
Product feedback
Need help? Visit our support page.