Manage datasets

This document describes how to copy datasets, recreate datasets in anotherlocation, secure datasets, delete datasets, and restore tables from deleteddatasets in BigQuery. For information about how to restore(orundelete) a deleted dataset, seeRestore deleted datasets.

As a BigQuery administrator, you can organize and control accesstotables andviews thatanalysts use. For more information about datasets,seeIntroduction to datasets.

You cannot change the name of an existing dataset or relocate a dataset afterit's created.As a workaround for changing the dataset name, you cancopy a datasetand change the destination dataset's name. To relocate a dataset, you can followone of the following methods:

• Recreate a dataset in another location.
• Make a copy of the dataset.

Required roles

This section describes the roles and permissions that you need to managedatasets. If your source or destination dataset is in thesame project as the one you are using to copy, then you don't need extrapermissions or roles on that dataset.

Copy a dataset

Grant these roles to copy a dataset. Copying datasets is in(Beta).

To get the permissions that you need to copy datasets, ask your administrator to grant you the following IAM roles:

• BigQuery Admin (roles/bigquery.admin) - the destination project
• BigQuery Data Viewer (roles/bigquery.dataViewer) - the source dataset
• BigQuery Data Editor (roles/bigquery.dataEditor) - the destination dataset

For more information about granting roles, seeManage access to projects, folders, and organizations.

These predefined roles contain the permissions required to copy datasets. To see the exact permissions that are required, expand theRequired permissions section:

You might also be able to get these permissions withcustom roles or otherpredefined roles.

Delete a dataset

Grant these roles to delete a dataset.

To get the permissions that you need to delete datasets, ask your administrator to grant you theBigQuery Data Owner (roles/bigquery.dataOwner) IAM role on the project. For more information about granting roles, seeManage access to projects, folders, and organizations.

This predefined role contains the permissions required to delete datasets. To see the exact permissions that are required, expand theRequired permissions section:

You might also be able to get these permissions withcustom roles or otherpredefined roles.

Copy datasets

This product is subject to the "Pre-GA Offerings Terms" in the General Service Terms section of theService Specific Terms. Pre-GA products are available "as is" and might have limited support. For more information, see thelaunch stage descriptions.

You can copy a dataset, including partitioned data within a region or acrossregions, without extracting, moving, or reloading data into BigQuery.BigQuery uses theBigQuery Data Transfer Servicein the backend to copy datasets. For location considerations when you transferdata, seeData location and transfers.

For each dataset copy configuration, you can have one transfer run active at atime. Additional transfer runs are queued. If you are using the Google Cloud console,you can schedule recurring copies, and configure an email or Pub/Subnotifications with the BigQuery Data Transfer Service.

Limitations

The following limitations apply when you copy datasets:

• You can't copy the following resources from a source dataset:

  • Views.
  • Routines, including UDFs.
  • External tables.
  • Change data capture (CDC) tables ifthe copy job is across regions. Copying CDC tables within the same regionis supported.

  • Cross-region table copy job is not supported for tables encrypted withcustomer-managed encrypted keys (CMEK)when the destination dataset is not encrypted with CMEK and there is noCMEK provided. Copying tables with default encryption across regions issupported.

    You can copy all encrypted tables within the same region, includingtables encrypted with CMEK.

• You can't use the following resources as destination datasets for copy jobs:

  • Write-optimized storage.

  • Dataset encrypted with CMEK if the copy job is across regions and thesource table is not encrypted with CMEK.

    However, tables encrypted with CMEK are allowed as destination tableswhen copying within the same region.

• The minimum frequency between copy jobs is 12 hours.

• Appending data to a partitioned or non-partitioned table in the destinationdataset isn't supported. If there are no changes in the source table, thetable is skipped. If the source table is updated, the destination table iscompletely truncated and replaced.

• If a table exists in the source dataset and the destination dataset, and thesource table has not changed since the last successful copy, it's skipped.The source table is skipped even if theOverwrite destination tablescheckbox is selected.

• When truncating tables in the destination dataset, the dataset copy jobdoesn't detect any changes made to resources inthe destination dataset before it begins the copy job. The dataset copy joboverwrites all of the data in the destination dataset, including both thetables and schema.

• The destination table might not reflect changes made to the source tablesafter a copy job starts.

• Copying a dataset is not supported inBigQuery Omni regions.

• To copy a dataset to a project in anotherVPC Service Controls service perimeter,you need to set the following egress rules:

  • In the destination project's VPC Service Controls service perimeter configuration,the IAM principal must have the following methods:

    • bigquery.datasets.get
    • bigquery.tables.list
    • bigquery.tables.get,
    • bigquery.tables.getData

  • In the source project's VPC Service Controls service perimeterconfiguration, the IAM principal being used must have the method set toAll Methods.

• If you try to update a dataset copy transfer configuration you don't own,the update might fail with the following error message:

  Cannot modify restricted parameters without taking ownership of the transfer configuration.

  The owner of the dataset copy is the user associated with the dataset copyor the user who has access to the service account associated with the dataset copy. The associated user can be seen in theconfiguration details of thedataset copy. For information on how to update the dataset copy to takeownership, seeUpdate credentials.To grant users access to a service account, you must have theService Account user role.

  The owner restricted parameters for dataset copies are:

  • Source project
  • Source dataset
  • Destination dataset
  • Overwrite destination table setting

• Allcross-region table copy limitations apply.

Copy a dataset

Select one of the following options:

importcom.google.api.gax.rpc.ApiException;importcom.google.cloud.bigquery.datatransfer.v1.CreateTransferConfigRequest;importcom.google.cloud.bigquery.datatransfer.v1.DataTransferServiceClient;importcom.google.cloud.bigquery.datatransfer.v1.ProjectName;importcom.google.cloud.bigquery.datatransfer.v1.TransferConfig;importcom.google.protobuf.Struct;importcom.google.protobuf.Value;importjava.io.IOException;importjava.util.HashMap;importjava.util.Map;// Sample to copy dataset from another gcp projectpublicclassCopyDataset{publicstaticvoidmain(String[]args)throwsIOException{// TODO(developer): Replace these variables before running the sample.finalStringdestinationProjectId="MY_DESTINATION_PROJECT_ID";finalStringdestinationDatasetId="MY_DESTINATION_DATASET_ID";finalStringsourceProjectId="MY_SOURCE_PROJECT_ID";finalStringsourceDatasetId="MY_SOURCE_DATASET_ID";Map<String,Value>params=newHashMap<>();params.put("source_project_id",Value.newBuilder().setStringValue(sourceProjectId).build());params.put("source_dataset_id",Value.newBuilder().setStringValue(sourceDatasetId).build());TransferConfigtransferConfig=TransferConfig.newBuilder().setDestinationDatasetId(destinationDatasetId).setDisplayName("Your Dataset Copy Name").setDataSourceId("cross_region_copy").setParams(Struct.newBuilder().putAllFields(params).build()).setSchedule("every 24 hours").build();copyDataset(destinationProjectId,transferConfig);}publicstaticvoidcopyDataset(StringprojectId,TransferConfigtransferConfig)throwsIOException{try(DataTransferServiceClientdataTransferServiceClient=DataTransferServiceClient.create()){ProjectNameparent=ProjectName.of(projectId);CreateTransferConfigRequestrequest=CreateTransferConfigRequest.newBuilder().setParent(parent.toString()).setTransferConfig(transferConfig).build();TransferConfigconfig=dataTransferServiceClient.createTransferConfig(request);System.out.println("Copy dataset created successfully :"+config.getName());}catch(ApiExceptionex){System.out.print("Copy dataset was not created."+ex.toString());}}}

fromgoogle.cloudimportbigquery_datatransfertransfer_client=bigquery_datatransfer.DataTransferServiceClient()destination_project_id="my-destination-project"destination_dataset_id="my_destination_dataset"source_project_id="my-source-project"source_dataset_id="my_source_dataset"transfer_config=bigquery_datatransfer.TransferConfig(destination_dataset_id=destination_dataset_id,display_name="Your Dataset Copy Name",data_source_id="cross_region_copy",params={"source_project_id":source_project_id,"source_dataset_id":source_dataset_id,},schedule="every 24 hours",)transfer_config=transfer_client.create_transfer_config(parent=transfer_client.common_project_path(destination_project_id),transfer_config=transfer_config,)print(f"Created transfer config:{transfer_config.name}")

To avoid additional storage costs, considerdeleting the prior dataset.

View dataset copy jobs

To see the status and view details of a dataset copy job in theGoogle Cloud console, do the following:

1. In the Google Cloud console, go to theData transfers page.

   Go to Data transfers

2. Select a transfer for which you want to view the transfer details, and then dothe following:

   1. On theTransfer details page, select a transfer run.

   2. To refresh, clickrefreshRefresh.

Recreate datasets in another location

To manually move a dataset from one location to another, follow these steps:

1. Export the data from your BigQuery tables to a Cloud Storage bucket.

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

2. Copy or move the data from your export Cloud Storage bucket to a new bucket you created in the destination location. For example, if you are moving your data from theUS multi-region to theasia-northeast1 Tokyo region, you would transfer the data to a bucket that you created in Tokyo. For information about transferring Cloud Storage objects, seeCopy, rename, and move objects in the Cloud Storage documentation.

   Transferring data between regions incursnetwork egress charges in Cloud Storage.

3. Create a new BigQuery dataset in the new location, and then load your data from the Cloud Storage bucket into the new dataset.

   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 theload jobs limits.

You can also use Cloud Composer to move and copy large datasets programmatically.

For more information about using Cloud Storage to store and move large datasets, seeUse Cloud Storage with big data.

Secure datasets

To control access to datasets in BigQuery, seeControlling access to datasets.For information about data encryption, seeEncryption at rest.

Delete datasets

When you delete a dataset by using the Google Cloud console, tables and viewsin the dataset, including their data, are deleted. When you delete adataset by using the bq command-line tool, you must use the-r flag to delete thetables and views.

Deleting a dataset creates oneaudit log entry forthe dataset deletion. It doesn't create separate log entries for each deletedtable within the dataset.

To delete a dataset, select one of the following options:

bqrm-r-f-dPROJECT_ID:DATASET

bq rm -r -d mydataset

bq rm -r -f myotherproject:mydataset

usingGoogle.Cloud.BigQuery.V2;usingSystem;publicclassBigQueryDeleteDataset{publicvoidDeleteDataset(stringprojectId="your-project-id",stringdatasetId="your_empty_dataset"){BigQueryClientclient=BigQueryClient.Create(projectId);// Delete a dataset that does not contain any tablesclient.DeleteDataset(datasetId:datasetId);Console.WriteLine($"Dataset {datasetId} deleted.");}}

// Copyright(c) 2018 Google LLC//// Licensed under the Apache License, Version 2.0 (the "License"); you may not// use this file except in compliance with the License. You may obtain a copy of// the License at//// http://www.apache.org/licenses/LICENSE-2.0//// Unless required by applicable law or agreed to in writing, software// distributed under the License is distributed on an "AS IS" BASIS, WITHOUT// WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. See the// License for the specific language governing permissions and limitations under// the License.//usingGoogle.Cloud.BigQuery.V2;usingSystem;publicclassBigQueryDeleteDatasetAndContents{publicvoidDeleteDatasetAndContents(stringprojectId="your-project-id",stringdatasetId="your_dataset_with_tables"){BigQueryClientclient=BigQueryClient.Create(projectId);// Use the DeleteDatasetOptions to delete a dataset and its contentsclient.DeleteDataset(datasetId:datasetId,options:newDeleteDatasetOptions(){DeleteContents=true});Console.WriteLine($"Dataset {datasetId} and contents deleted.");}}

import("context""fmt""cloud.google.com/go/bigquery")// deleteDataset demonstrates the deletion of an empty dataset.funcdeleteDataset(projectID,datasetIDstring)error{// projectID := "my-project-id"// datasetID := "mydataset"ctx:=context.Background()client,err:=bigquery.NewClient(ctx,projectID)iferr!=nil{returnfmt.Errorf("bigquery.NewClient: %v",err)}deferclient.Close()// To recursively delete a dataset and contents, use DeleteWithContents.iferr:=client.Dataset(datasetID).Delete(ctx);err!=nil{returnfmt.Errorf("Delete: %v",err)}returnnil}

importcom.google.cloud.bigquery.BigQuery;importcom.google.cloud.bigquery.BigQuery.DatasetDeleteOption;importcom.google.cloud.bigquery.BigQueryException;importcom.google.cloud.bigquery.BigQueryOptions;importcom.google.cloud.bigquery.DatasetId;publicclassDeleteDataset{publicstaticvoidrunDeleteDataset(){// TODO(developer): Replace these variables before running the sample.StringprojectId="MY_PROJECT_ID";StringdatasetName="MY_DATASET_NAME";deleteDataset(projectId,datasetName);}publicstaticvoiddeleteDataset(StringprojectId,StringdatasetName){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.BigQuerybigquery=BigQueryOptions.getDefaultInstance().getService();DatasetIddatasetId=DatasetId.of(projectId,datasetName);booleansuccess=bigquery.delete(datasetId,DatasetDeleteOption.deleteContents());if(success){System.out.println("Dataset deleted successfully");}else{System.out.println("Dataset was not found");}}catch(BigQueryExceptione){System.out.println("Dataset was not deleted. \n"+e.toString());}}}

/* * Copyright 2020 Google LLC * * Licensed under the Apache License, Version 2.0 (the "License"); * you may not use this file except in compliance with the License. * You may obtain a copy of the License at * * http://www.apache.org/licenses/LICENSE-2.0 * * Unless required by applicable law or agreed to in writing, software * distributed under the License is distributed on an "AS IS" BASIS, * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. * See the License for the specific language governing permissions and * limitations under the License. */packagecom.example.bigquery;importcom.google.cloud.bigquery.BigQuery;importcom.google.cloud.bigquery.BigQueryException;importcom.google.cloud.bigquery.BigQueryOptions;importcom.google.cloud.bigquery.DatasetId;// Sample to delete dataset with contents.publicclassDeleteDatasetAndContents{publicstaticvoidmain(String[]args){// TODO(developer): Replace these variables before running the sample.StringprojectId="MY_PROJECT_ID";StringdatasetName="MY_DATASET_NAME";deleteDatasetAndContents(projectId,datasetName);}publicstaticvoiddeleteDatasetAndContents(StringprojectId,StringdatasetName){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.BigQuerybigquery=BigQueryOptions.getDefaultInstance().getService();DatasetIddatasetId=DatasetId.of(projectId,datasetName);// Use the force parameter to delete a dataset and its contentsbooleansuccess=bigquery.delete(datasetId,BigQuery.DatasetDeleteOption.deleteContents());if(success){System.out.println("Dataset deleted with contents successfully");}else{System.out.println("Dataset was not found");}}catch(BigQueryExceptione){System.out.println("Dataset was not deleted with contents. \n"+e.toString());}}}

// Import the Google Cloud client libraryconst{BigQuery}=require('@google-cloud/bigquery');constbigquery=newBigQuery();asyncfunctiondeleteDataset(){// 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 datasetconstdataset=bigquery.dataset(datasetId);// Delete the dataset and its contentsawaitdataset.delete({force:true});console.log(`Dataset${dataset.id} deleted.`);}

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

fromgoogle.cloudimportbigquery# 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))

require"google/cloud/bigquery"defdelete_datasetdataset_id="my_empty_dataset"bigquery=Google::Cloud::Bigquery.new# Delete a dataset that does not contain any tablesdataset=bigquery.datasetdataset_iddataset.deleteputs"Dataset#{dataset_id} deleted."end

# Copyright 2020 Google LLC## Licensed under the Apache License, Version 2.0 (the "License");# you may not use this file except in compliance with the License.# You may obtain a copy of the License at##     http://www.apache.org/licenses/LICENSE-2.0## Unless required by applicable law or agreed to in writing, software# distributed under the License is distributed on an "AS IS" BASIS,# WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.# See the License for the specific language governing permissions and# limitations under the License.require"google/cloud/bigquery"defdelete_dataset_and_contentsdataset_id="my_dataset_with_tables"bigquery=Google::Cloud::Bigquery.new# Use the force parameter to delete a dataset and its contentsdataset=bigquery.datasetdataset_iddataset.deleteforce:trueputs"Dataset#{dataset_id} and contents deleted."end

Restore tables from deleted datasets

You can restore tables from a deleted dataset that are within the dataset'stime travel window. To restore the entire dataset,seeRestore deleted datasets.

1. Create a dataset with the same name and in the same location as the original.
2. Choose a timestamp from before the original dataset was deleted by using aformat of milliseconds since the epoch–for example,1418864998000.

3. Copy theoriginaldataset.table1 table at the time1418864998000 intothe new dataset:

   bq cp originaldataset.table1@1418864998000 mydataset.mytable

   To find the names of the nonempty tables that were in the deleted dataset,query the dataset'sINFORMATION_SCHEMA.TABLE_STORAGE viewwithin the time travel window.

Restore deleted datasets

To learn how to restore (orundelete) a deleted dataset, seeRestore deleted datasets.

Quotas

For information about copy quotas, seeCopy jobs.Usage for copy jobs are available in theINFORMATION_SCHEMA. To learn how toquery theINFORMATION_SCHEMA.JOBS view, seeJOBS view.

Pricing

For pricing information for copying datasets, seeData replication pricing.

BigQuery sends compressed data for copying across regions so thedata that is billed might be less than the actual size of your dataset.For more information, seeBigQuery pricing.

What's next

• Learn how tocreate datasets.
• Learn how toupdate datasets.