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, but you can copy a dataset. See Copying datasets.
Copying datasets
To see steps for copying a dataset, including across regions, see Copying datasets.
Moving a dataset
To manually move a dataset from one location to another, follow this process:
-
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.
-
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 Copying, renaming, and moving objects in the Cloud Storage documentation.
Note that transferring data between regions incurs network egress charges in Cloud Storage.
-
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.
You can also use Cloud Composer to move and copy large datasets programmatically.
For more information on using Cloud Storage to store and move large datasets, see Using Cloud Storage with big data.
Checking whether a dataset exists
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.
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.
Deleting datasets
You can delete a dataset in the following ways:
- Using the Cloud Console.
- Using the
bq rm
command in thebq
command-line tool. - 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
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 IAM roles and permissions in BigQuery, see Predefined roles and permissions.
Deleting a dataset
When you delete a dataset by using the Cloud Console, tables and views
in the dataset (and the data they contain) are deleted. When you delete a
dataset by using the bq
command-line tool, you must use the -r
flag to delete the
dataset's tables and views.
To delete a dataset:
Console
In the Explorer panel, expand your project and select a dataset.
Click Delete dataset.
In the Delete dataset dialog, type the name of the dataset into the text box, and then click Delete.
bq
Use the bq rm
command with the (optional) --dataset
or -d
shortcut
flag to delete a dataset. When you use the bq
command-line tool 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
Replace the following:
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.
C#
Before trying this sample, follow the C# setup instructions in the BigQuery Quickstart Using Client Libraries. For more information, see the BigQuery C# API reference documentation.
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.
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.
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.
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.
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.
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.
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.