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 a dataset
• Copy a dataset
• Manually move a dataset
• Delete a dataset

Renaming datasets

Currently, you cannot change the name of an existing dataset, but you can copy a dataset. See Copying datasets.

Copying datasets

To see steps for copying a dataset, including across regions, see Copying datasets.

Copying datasets is currently in Beta

Moving a dataset

To manually move a dataset from one location to another, follow this process:

1. Export the data from your BigQuery tables to a regional or multi-region Cloud Storage bucket in the same location as your dataset. For example, if your dataset is in the EU multi-region location, export your data into a regional or multi-region bucket in the EU.

   There are no charges for exporting data from BigQuery, but you do incur charges for storing the exported data in Cloud Storage. BigQuery exports are subject to the limits on export jobs.

2. Copy or move the data from your Cloud Storage bucket to a regional or multi-region bucket in the new location. For example, if you are moving your data from the US multi-region location to the Tokyo regional location, you would transfer the data to a regional bucket in Tokyo. For information on transferring Cloud Storage objects, see Renaming, copying, and moving objects in the Cloud Storage documentation.

   Note that transferring data between regions incurs network egress charges in Cloud Storage.

3. After you transfer the data to a Cloud Storage bucket in the new location, create a new BigQuery dataset (in the new location). Then, load your data from the Cloud Storage bucket into BigQuery.

   You are not charged for loading the data into BigQuery, but you will incur charges for storing the data in Cloud Storage until you delete the data or the bucket. You are also charged for storing the data in BigQuery after it is loaded. Loading data into BigQuery is subject to the limits on load jobs.

For more information on using Cloud Storage to store and move large datasets, see Using Cloud Storage with big data.

Deleting datasets

You can delete a dataset by:

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

Required permissions

At a minimum, to delete a dataset, you must be granted bigquery.datasets.delete permissions. If the dataset contains tables or views, bigquery.tables.delete is also required. The following predefined Cloud IAM roles include both bigquery.datasets.delete and bigquery.tables.delete permissions:

• bigquery.dataOwner
• bigquery.admin

In addition, if a user has bigquery.datasets.create permissions, when that user creates a dataset, they are granted bigquery.dataOwner access to it. bigquery.dataOwner access gives the user the ability to delete datasets and tables they create.

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

Deleting a dataset

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

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

To delete a dataset:

Console

Caution: Deleting a dataset cannot be undone.

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

   

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

Note: When you delete a dataset using the Cloud Console, the tables are automatically removed.

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.

CLI

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 <var>project_id:dataset</var>

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 CLI 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

import (
	"context"
	"fmt"

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

// deleteDataset demonstrates the deletion of an empty dataset.
func deleteDataset(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)
	}

	// To recursively delete a dataset and contents, use DeleteWithContents.
	if err := client.Dataset(datasetID).Delete(ctx); err != nil {
		return fmt.Errorf("Delete: %v", 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

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

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

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

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

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

  // Delete the dataset and its contents
  await dataset.delete({force: true});
  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

# 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
)  # Make an API request.

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 .

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.
• For more information on copying datasets, including across regions, see Copying datasets.