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
  • 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 Access control.

Deleting a dataset

When you delete a dataset by using the GCP 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.

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.

CLI

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

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');
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 .

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

Var denne siden nyttig? Si fra hva du synes:

Send tilbakemelding om ...

Trenger du hjelp? Gå til brukerstøttesiden vår.