Manage tables

This document describes how to manage tables in BigQuery. You can manage your BigQuery tables in the following ways:

For more information about creating and using tables including getting table information, listing tables, and controlling access to table data, see Creating and using tables.

Before you begin

Grant Identity and Access Management (IAM) roles that give users the necessary permissions to perform each task in this document. The permissions required to perform a task (if any) are listed in the "Required permissions" section of the task.

Update table properties

You can update the following elements of a table:

Required permissions

To get the permissions that you need to update table properties, ask your administrator to grant you the Data Editor (roles/bigquery.dataEditor) IAM role on a table. For more information about granting roles, see Manage access.

This predefined role contains the permissions required to update table properties. To see the exact permissions that are required, expand the Required permissions section:

Required permissions

The following permissions are required to update table properties:

  • bigquery.tables.update
  • bigquery.tables.get

You might also be able to get these permissions with custom roles or other predefined roles.

Additionally, if you have the bigquery.datasets.create permission, you can update the properties of the tables of the datasets that you create.

Update a table's description

You can update a table's description in the following ways:

  • Using the Google Cloud console.
  • Using a data definition language (DDL) ALTER TABLE statement.
  • Using the bq command-line tool's bq update command.
  • Calling the tables.patch API method.
  • Using the client libraries.

To update a table's description:

Console

You can't add a description when you create a table using the Google Cloud console. After the table is created, you can add a description on the Details page.

  1. In the Explorer panel, expand your project and dataset, then select the table.

  2. In the details panel, click Details.

  3. In the Description section, click the pencil icon to edit the description.

    Edit description.

  4. Enter a description in the box, and click Update to save.

SQL

Use the ALTER TABLE SET OPTIONS statement. The following example updates the description of a table named mytable:

  1. In the Google Cloud console, go to the BigQuery page.

    Go to BigQuery

  2. In the query editor, enter the following statement:

    ALTER TABLE mydataset.mytable
      SET OPTIONS (
        description = 'Description of mytable');
    

  3. Click Run.

For more information about how to run queries, see Run an interactive query.

bq

  1. In the Google Cloud console, activate Cloud Shell.

    Activate Cloud Shell

    At the bottom of the Google Cloud console, a Cloud Shell session starts and displays a command-line prompt. Cloud Shell is a shell environment with the Google Cloud CLI already installed and with values already set for your current project. It can take a few seconds for the session to initialize.

  2. Issue the bq update command with the --description flag. If you are updating 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 update \
    --description "description" \
    project_id:dataset.table
    

    Replace the following:

    • description: the text describing the table in quotes
    • project_id: your project ID
    • dataset: the name of the dataset that contains the table you're updating
    • table: the name of the table you're updating

    Examples:

    To change the description of the mytable table in the mydataset dataset to "Description of mytable", enter the following command. The mydataset dataset is in your default project.

    bq update --description "Description of mytable" mydataset.mytable
    

    To change the description of the mytable table in the mydataset dataset to "Description of mytable", enter the following command. The mydataset dataset is in the myotherproject project, not your default project.

    bq update \
    --description "Description of mytable" \
    myotherproject:mydataset.mytable
    

API

Call the tables.patch method and use the description property in the table resource to update the table's description. Because the tables.update method replaces the entire table resource, the tables.patch method is preferred.

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.

To authenticate to BigQuery, set up Application Default Credentials. For more information, see Set up authentication for a local development environment.

import (
	"context"
	"fmt"

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

// updateTableDescription demonstrates how to fetch a table's metadata and updates the Description metadata.
func updateTableDescription(projectID, datasetID, tableID string) error {
	// projectID := "my-project-id"
	// datasetID := "mydataset"
	// tableID := "mytable"
	ctx := context.Background()
	client, err := bigquery.NewClient(ctx, projectID)
	if err != nil {
		return fmt.Errorf("bigquery.NewClient: %v", err)
	}
	defer client.Close()

	tableRef := client.Dataset(datasetID).Table(tableID)
	meta, err := tableRef.Metadata(ctx)
	if err != nil {
		return err
	}
	update := bigquery.TableMetadataToUpdate{
		Description: "Updated description.",
	}
	if _, err = tableRef.Update(ctx, update, meta.ETag); err != nil {
		return 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.

To authenticate to BigQuery, set up Application Default Credentials. For more information, see Set up authentication for a local development environment.

import com.google.cloud.bigquery.BigQuery;
import com.google.cloud.bigquery.BigQueryException;
import com.google.cloud.bigquery.BigQueryOptions;
import com.google.cloud.bigquery.Table;

public class UpdateTableDescription {

  public static void runUpdateTableDescription() {
    // TODO(developer): Replace these variables before running the sample.
    String datasetName = "MY_DATASET_NAME";
    String tableName = "MY_TABLE_NAME";
    String newDescription = "this is the new table description";
    updateTableDescription(datasetName, tableName, newDescription);
  }

  public static void updateTableDescription(
      String datasetName, String tableName, String newDescription) {
    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.
      BigQuery bigquery = BigQueryOptions.getDefaultInstance().getService();

      Table table = bigquery.getTable(datasetName, tableName);
      bigquery.update(table.toBuilder().setDescription(newDescription).build());
      System.out.println("Table description updated successfully to " + newDescription);
    } catch (BigQueryException e) {
      System.out.println("Table description was not updated \n" + e.toString());
    }
  }
}

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.

To authenticate to BigQuery, set up Application Default Credentials. For more information, see Set up authentication for a local development environment.

Configure the Table.description property and call Client.update_table() to send the update to the API.
# from google.cloud import bigquery
# client = bigquery.Client()
# project = client.project
# dataset_ref = bigquery.DatasetReference(project, dataset_id)
# table_ref = dataset_ref.table('my_table')
# table = client.get_table(table_ref)  # API request

assert table.description == "Original description."
table.description = "Updated description."

table = client.update_table(table, ["description"])  # API request

assert table.description == "Updated description."

Update a table's expiration time

You can set a default table expiration time at the dataset level, or you can set a table's expiration time when the table is created. A table's expiration time is often referred to as "time to live" or TTL.

When a table expires, it is deleted along with all of the data it contains. If necessary, you can undelete the expired table within the time travel window specified for the dataset, see Restore deleted tables for more information.

If you set the expiration when the table is created, the dataset's default table expiration is ignored. If you do not set a default table expiration at the dataset level, and you do not set a table expiration when the table is created, the table never expires and you must delete the table manually.

At any point after the table is created, you can update the table's expiration time in the following ways:

  • Using the Google Cloud console.
  • Using a data definition language (DDL) ALTER TABLE statement.
  • Using the bq command-line tool's bq update command.
  • Calling the tables.patch API method.
  • Using the client libraries.

To update a table's expiration time:

Console

You can't add an expiration time when you create a table using the Google Cloud console. After a table is created, you can add or update a table expiration on the Table Details page.

  1. In the Explorer panel, expand your project and dataset, then select the table.

  2. In the details panel, click Details.

  3. Click the pencil icon next to Table info

  4. For Table expiration, select Specify date. Then select the expiration date using the calendar widget.

  5. Click Update to save. The updated expiration time appears in the Table info section.

SQL

Use the ALTER TABLE SET OPTIONS statement. The following example updates the expiration time of a table named mytable:

  1. In the Google Cloud console, go to the BigQuery page.

    Go to BigQuery

  2. In the query editor, enter the following statement:

    ALTER TABLE mydataset.mytable
      SET OPTIONS (
        -- Sets table expiration to timestamp 2025-02-03 12:34:56
        expiration_timestamp = TIMESTAMP '2025-02-03 12:34:56');
    

  3. Click Run.

For more information about how to run queries, see Run an interactive query.

bq

  1. In the Google Cloud console, activate Cloud Shell.

    Activate Cloud Shell

    At the bottom of the Google Cloud console, a Cloud Shell session starts and displays a command-line prompt. Cloud Shell is a shell environment with the Google Cloud CLI already installed and with values already set for your current project. It can take a few seconds for the session to initialize.

  2. Issue the bq update command with the --expiration flag. If you are updating 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 update \
    --expiration integer \
    project_id:dataset.table
    

    Replace the following:

    • integer: the default lifetime (in seconds) for the table. The minimum value is 3600 seconds (one hour). The expiration time evaluates to the current time plus the integer value. If you specify 0, the table expiration is removed, and the table never expires. Tables with no expiration must be manually deleted.
    • project_id: your project ID.
    • dataset: the name of the dataset that contains the table you're updating.
    • table: the name of the table you're updating.

    Examples:

    To update the expiration time of the mytable table in the mydataset dataset to 5 days (432000 seconds), enter the following command. The mydataset dataset is in your default project.

    bq update --expiration 432000 mydataset.mytable
    

    To update the expiration time of the mytable table in the mydataset dataset to 5 days (432000 seconds), enter the following command. The mydataset dataset is in the myotherproject project, not your default project.

    bq update --expiration 432000 myotherproject:mydataset.mytable
    

API

Call the tables.patch method and use the expirationTime property in the table resource to update the table expiration in milliseconds. Because the tables.update method replaces the entire table resource, the tables.patch method is preferred.

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.

To authenticate to BigQuery, set up Application Default Credentials. For more information, see Set up authentication for a local development environment.

import (
	"context"
	"fmt"
	"time"

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

// updateTableExpiration demonstrates setting the table expiration of a table to a specific point in time
// in the future, at which time it will be deleted.
func updateTableExpiration(projectID, datasetID, tableID string) error {
	// projectID := "my-project-id"
	// datasetID := "mydataset"
	// tableID := "mytable"
	ctx := context.Background()
	client, err := bigquery.NewClient(ctx, projectID)
	if err != nil {
		return fmt.Errorf("bigquery.NewClient: %v", err)
	}
	defer client.Close()

	tableRef := client.Dataset(datasetID).Table(tableID)
	meta, err := tableRef.Metadata(ctx)
	if err != nil {
		return err
	}
	update := bigquery.TableMetadataToUpdate{
		ExpirationTime: time.Now().Add(time.Duration(5*24) * time.Hour), // table expiration in 5 days.
	}
	if _, err = tableRef.Update(ctx, update, meta.ETag); err != nil {
		return 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.

To authenticate to BigQuery, set up Application Default Credentials. For more information, see Set up authentication for a local development environment.

import com.google.cloud.bigquery.BigQuery;
import com.google.cloud.bigquery.BigQueryException;
import com.google.cloud.bigquery.BigQueryOptions;
import com.google.cloud.bigquery.Table;
import java.util.concurrent.TimeUnit;

public class UpdateTableExpiration {

  public static void runUpdateTableExpiration() {
    // TODO(developer): Replace these variables before running the sample.
    String datasetName = "MY_DATASET_NAME";
    String tableName = "MY_TABLE_NAME";
    // Update table expiration to one day.
    Long newExpiration = TimeUnit.MILLISECONDS.convert(1, TimeUnit.DAYS);
    updateTableExpiration(datasetName, tableName, newExpiration);
  }

  public static void updateTableExpiration(
      String datasetName, String tableName, Long newExpiration) {
    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.
      BigQuery bigquery = BigQueryOptions.getDefaultInstance().getService();

      Table table = bigquery.getTable(datasetName, tableName);
      bigquery.update(table.toBuilder().setExpirationTime(newExpiration).build());

      System.out.println("Table expiration updated successfully to " + newExpiration);
    } catch (BigQueryException e) {
      System.out.println("Table expiration was not updated \n" + e.toString());
    }
  }
}

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.

To authenticate to BigQuery, set up Application Default Credentials. For more information, see Set up authentication for a local development environment.

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

async function updateTableExpiration() {
  // Updates a table's expiration.

  /**
   * TODO(developer): Uncomment the following lines before running the sample.
   */
  // const datasetId = 'my_dataset', // Existing dataset
  // const tableId = 'my_table', // Existing table
  // const expirationTime = Date.now() + 1000 * 60 * 60 * 24 * 5 // 5 days from current time in ms

  // Retreive current table metadata
  const table = bigquery.dataset(datasetId).table(tableId);
  const [metadata] = await table.getMetadata();

  // Set new table expiration to 5 days from current time
  metadata.expirationTime = expirationTime.toString();
  const [apiResponse] = await table.setMetadata(metadata);

  const newExpirationTime = apiResponse.expirationTime;
  console.log(`${tableId} expiration: ${newExpirationTime}`);
}

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.

To authenticate to BigQuery, set up Application Default Credentials. For more information, see Set up authentication for a local development environment.

Configure Table.expires property and call Client.update_table() to send the update to the API.
# Copyright 2022 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
#
#     https://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.

import datetime


def update_table_expiration(table_id, expiration):
    orig_table_id = table_id
    orig_expiration = expiration

    from google.cloud import bigquery

    client = bigquery.Client()

    # TODO(dev): Change table_id to the full name of the table you want to update.
    table_id = "your-project.your_dataset.your_table_name"

    # TODO(dev): Set table to expire for desired days days from now.
    expiration = datetime.datetime.now(datetime.timezone.utc) + datetime.timedelta(
        days=5
    )

    table_id = orig_table_id
    expiration = orig_expiration

    table = client.get_table(table_id)  # Make an API request.
    table.expires = expiration
    table = client.update_table(table, ["expires"])  # API request

    print(f"Updated {table_id}, expires {table.expires}.")

To update the default dataset partition expiration time:

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.

To authenticate to BigQuery, set up Application Default Credentials. For more information, see Set up authentication for a local development environment.

import com.google.cloud.bigquery.BigQuery;
import com.google.cloud.bigquery.BigQueryException;
import com.google.cloud.bigquery.BigQueryOptions;
import com.google.cloud.bigquery.Dataset;
import java.util.concurrent.TimeUnit;

// Sample to update partition expiration on a dataset.
public class UpdateDatasetPartitionExpiration {

  public static void main(String[] args) {
    // TODO(developer): Replace these variables before running the sample.
    String datasetName = "MY_DATASET_NAME";
    // Set the default partition expiration (applies to new tables, only) in
    // milliseconds. This example sets the default expiration to 90 days.
    Long newExpiration = TimeUnit.MILLISECONDS.convert(90, TimeUnit.DAYS);
    updateDatasetPartitionExpiration(datasetName, newExpiration);
  }

  public static void updateDatasetPartitionExpiration(String datasetName, Long newExpiration) {
    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.
      BigQuery bigquery = BigQueryOptions.getDefaultInstance().getService();

      Dataset dataset = bigquery.getDataset(datasetName);
      bigquery.update(dataset.toBuilder().setDefaultPartitionExpirationMs(newExpiration).build());
      System.out.println(
          "Dataset default partition expiration updated successfully to " + newExpiration);
    } catch (BigQueryException e) {
      System.out.println("Dataset partition expiration was not updated \n" + e.toString());
    }
  }
}

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.

To authenticate to BigQuery, set up Application Default Credentials. For more information, see Set up authentication for a local development environment.

# Copyright 2019 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
#
#     https://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.


def update_dataset_default_partition_expiration(dataset_id: str) -> None:

    from google.cloud import bigquery

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

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

    dataset = client.get_dataset(dataset_id)  # Make an API request.

    # Set the default partition expiration (applies to new tables, only) in
    # milliseconds. This example sets the default expiration to 90 days.
    dataset.default_partition_expiration_ms = 90 * 24 * 60 * 60 * 1000

    dataset = client.update_dataset(
        dataset, ["default_partition_expiration_ms"]
    )  # Make an API request.

    print(
        "Updated dataset {}.{} with new default partition expiration {}".format(
            dataset.project, dataset.dataset_id, dataset.default_partition_expiration_ms
        )
    )

Update a table's rounding mode

You can update a table's default rounding mode by using the ALTER TABLE SET OPTIONS DDL statement. The following example updates the default rounding mode for mytable to ROUND_HALF_EVEN:

ALTER TABLE mydataset.mytable
SET OPTIONS (
  default_rounding_mode = "ROUND_HALF_EVEN");

When you add a NUMERIC or BIGNUMERIC field to a table and do not specify a rounding mode, then the rounding mode is automatically set to the table's default rounding mode. Changing a table's default rounding mode doesn't alter the rounding mode of existing fields.

Update a table's schema definition

For more information about updating a table's schema definition, see Modifying table schemas.

Rename a table

You can rename a table after it has been created by using the ALTER TABLE RENAME TO statement. The following example renames mytable to mynewtable:

ALTER TABLE mydataset.mytable
RENAME TO mynewtable;

Limitations on renaming tables

  • If you want to rename a table that has data streaming into it, you must stop the streaming and wait for BigQuery to indicate that streaming is not in use.
  • A table can usually be renamed within 72 hours of the last streaming operation, but it might take longer.
  • Table ACLs and row access policies are not always preserved after a table is renamed.
  • You can't concurrently rename a table and run a DML statement on that table.
  • Renaming a table removes all Data Catalog tags on the table.
  • You can't rename external tables.

Copy a table

This section describes how to create a full copy of a table. For information about other types of table copies, see table clones and table snapshots.

You can copy a table in the following ways:

  • Use the Google Cloud console.
  • Use the bq cp command.
  • Use a data definition language (DDL) CREATE TABLE COPY statement.
  • Call the jobs.insert API method and configure a copy job.
  • Use the client libraries.

Limitations on copying tables

Table copy jobs are subject to the following limitations:

  • When you copy a table, the name of the destination table must adhere to the same naming conventions as when you create a table.
  • Table copies are subject to BigQuery limits on copy jobs.
  • The Google Cloud console supports copying only one table at a time. You can't overwrite an existing table in the destination dataset. The table must have a unique name in the destination dataset.
  • Copying multiple source tables into a destination table is not supported by the Google Cloud console.
  • When copying multiple source tables to a destination table using the API, bq command-line tool, or the client libraries, all source tables must have identical schemas, including any partitioning or clustering.
  • The time that BigQuery takes to copy tables might vary significantly across different runs because the underlying storage is managed dynamically.
  • You can't copy and append a source table to a destination table that has more columns than the source table, and the additional columns have default values. Instead, you can run INSERT destination_table SELECT * FROM source_table to copy over the data.
  • If the copy operation overwrites an existing table, then the table-level access for the existing table is maintained. Tags from the source table aren't copied to the overwritten table.
  • If the copy operation creates a new table, then the table-level access for the new table is determined by the access policies of the dataset in which the new table is created. Additionally, tags are copied from the source table to the new table.
  • When you copy multiple source tables to a destination table, all source tables must have identical tags.

Required roles

To perform the tasks in this document, you need the following permissions.

Roles to copy tables and partitions

To get the permissions that you need to copy tables and partitions, ask your administrator to grant you the Data Editor (roles/bigquery.dataEditor) IAM role on the source and destination datasets. For more information about granting roles, see Manage access.

This predefined role contains the permissions required to copy tables and partitions. To see the exact permissions that are required, expand the Required permissions section:

Required permissions

The following permissions are required to copy tables and partitions:

  • bigquery.tables.getData on the source and destination datasets
  • bigquery.tables.get on the source and destination datasets
  • bigquery.tables.create on the destination dataset
  • bigquery.tables.update on the destination dataset

You might also be able to get these permissions with custom roles or other predefined roles.

Permission to run a copy job

To get the permission that you need to run a copy job, ask your administrator to grant you the Job User (roles/bigquery.jobUser) IAM role on the source and destination datasets. For more information about granting roles, see Manage access.

This predefined role contains the bigquery.jobs.create permission, which is required to run a copy job.

You might also be able to get this permission with custom roles or other predefined roles.

Copy a single source table

You can copy a single table in the following ways:

  • Using the Google Cloud console.
  • Using the bq command-line tool's bq cp command.
  • Using a data definition language (DDL) CREATE TABLE COPY statement.
  • Calling the jobs.insert API method, configuring a copy job, and specifying the sourceTable property.
  • Using the client libraries.

The Google Cloud console and the CREATE TABLE COPY statement support only one source table and one destination table in a copy job. To copy multiple source files to a destination table, you must use the bq command-line tool or the API.

To copy a single source table:

Console

  1. In the Explorer panel, expand your project and dataset, then select the table.

  2. In the details panel, click Copy table.

  3. In the Copy table dialog, under Destination:

    • For Project name, choose the project that will store the copied table.
    • For Dataset name, select the dataset where you want to store the copied table. The source and destination datasets must be in the same location.
    • For Table name, enter a name for the new table. The name must be unique in the destination dataset. You can't overwrite an existing table in the destination dataset using the Google Cloud console. For more information about table name requirements, see Table naming.
  4. Click Copy to start the copy job.

SQL

Use the CREATE TABLE COPY statement to copy a table named table1 to a new table named table1copy:

  1. In the Google Cloud console, go to the BigQuery page.

    Go to BigQuery

  2. In the query editor, enter the following statement:

    CREATE TABLE myproject.mydataset.table1copy
    COPY myproject.mydataset.table1;
    

  3. Click Run.

For more information about how to run queries, see Run an interactive query.

bq

  1. In the Google Cloud console, activate Cloud Shell.

    Activate Cloud Shell

    At the bottom of the Google Cloud console, a Cloud Shell session starts and displays a command-line prompt. Cloud Shell is a shell environment with the Google Cloud CLI already installed and with values already set for your current project. It can take a few seconds for the session to initialize.

  2. Issue the bq cp command. Optional flags can be used to control the write disposition of the destination table:

    • -a or --append_table appends the data from the source table to an existing table in the destination dataset.
    • -f or --force overwrites an existing table in the destination dataset and doesn't prompt you for confirmation.
    • -n or --no_clobber returns the following error message if the table exists in the destination dataset: Table 'project_id:dataset.table' already exists, skipping. If -n is not specified, the default behavior is to prompt you to choose whether to replace the destination table.
    • --destination_kms_key is the customer-managed Cloud KMS key used to encrypt the destination table.

    --destination_kms_key is not demonstrated here. See Protecting data with Cloud Key Management Service keys for more information.

    If the source or destination dataset is in a project other than your default project, add the project ID to the dataset names in the following format: project_id:dataset.

    (Optional) Supply the --location flag and set the value to your location.

    bq --location=location cp \
    -a -f -n \
    project_id:dataset.source_table \
    project_id:dataset.destination_table
    

    Replace the following:

    • location: the name of your location. The --location flag is optional. For example, if you are using BigQuery in the Tokyo region, you can set the flag's value to asia-northeast1. You can set a default value for the location using the .bigqueryrc file.
    • project_id: your project ID.
    • dataset: the name of the source or destination dataset.
    • source_table: the table you're copying.
    • destination_table: the name of the table in the destination dataset.

    Examples:

    To copy the mydataset.mytable table to the mydataset2.mytable2 table, enter the following command. Both datasets are in your default project.

    bq cp mydataset.mytable mydataset2.mytable2
    

    To copy the mydataset.mytable table and to overwrite a destination table with the same name, enter the following command. The source dataset is in your default project. The destination dataset is in the myotherproject project. The -f shortcut is used to overwrite the destination table without a prompt.

    bq cp -f \
    mydataset.mytable \
    myotherproject:myotherdataset.mytable
    

    To copy the mydataset.mytable table and to return an error if the destination dataset contains a table with the same name, enter the following command. The source dataset is in your default project. The destination dataset is in the myotherproject project. The -n shortcut is used to prevent overwriting a table with the same name.

    bq cp -n \
    mydataset.mytable \
    myotherproject:myotherdataset.mytable
    

    To copy the mydataset.mytable table and to append the data to a destination table with the same name, enter the following command. The source dataset is in your default project. The destination dataset is in the myotherproject project. The - a shortcut is used to append to the destination table.

    bq cp -a mydataset.mytable myotherproject:myotherdataset.mytable
    

API

You can copy an existing table through the API by calling the bigquery.jobs.insert method, and configuring a copy job. Specify your location in the location property in the jobReference section of the job resource.

You must specify the following values in your job configuration:

"copy": {
      "sourceTable": {       // Required
        "projectId": string, // Required
        "datasetId": string, // Required
        "tableId": string    // Required
      },
      "destinationTable": {  // Required
        "projectId": string, // Required
        "datasetId": string, // Required
        "tableId": string    // Required
      },
      "createDisposition": string,  // Optional
      "writeDisposition": string,   // Optional
    },

Where sourceTable provides information about the table to be copied, destinationTable provides information about the new table, createDisposition specifies whether to create the table if it doesn't exist, and writeDisposition specifies whether to overwrite or append to an existing table.

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.

To authenticate to BigQuery, set up Application Default Credentials. For more information, see Set up authentication for a local development environment.


using Google.Apis.Bigquery.v2.Data;
using Google.Cloud.BigQuery.V2;
using System;

public class BigQueryCopyTable
{
    public void CopyTable(
        string projectId = "your-project-id",
        string destinationDatasetId = "your_dataset_id"
    )
    {
        BigQueryClient client = BigQueryClient.Create(projectId);
        TableReference sourceTableRef = new TableReference()
        {
            TableId = "shakespeare",
            DatasetId = "samples",
            ProjectId = "bigquery-public-data"
        };
        TableReference destinationTableRef = client.GetTableReference(
            destinationDatasetId, "destination_table");
        BigQueryJob job = client.CreateCopyJob(
            sourceTableRef, destinationTableRef)
            .PollUntilCompleted() // Wait for the job to complete.
            .ThrowOnAnyError();

        // Retrieve destination table
        BigQueryTable destinationTable = client.GetTable(destinationTableRef);
        Console.WriteLine(
            $"Copied {destinationTable.Resource.NumRows} rows from table "
            + $"{sourceTableRef.DatasetId}.{sourceTableRef.TableId} "
            + $"to {destinationTable.FullyQualifiedId}."
        );
    }
}

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.

To authenticate to BigQuery, set up Application Default Credentials. For more information, see Set up authentication for a local development environment.

import (
	"context"
	"fmt"

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

// copyTable demonstrates copying a table from a source to a destination, and
// allowing the copy to overwrite existing data by using truncation.
func copyTable(projectID, datasetID, srcID, dstID string) error {
	// projectID := "my-project-id"
	// datasetID := "mydataset"
	// srcID := "sourcetable"
	// dstID := "destinationtable"
	ctx := context.Background()
	client, err := bigquery.NewClient(ctx, projectID)
	if err != nil {
		return fmt.Errorf("bigquery.NewClient: %v", err)
	}
	defer client.Close()

	dataset := client.Dataset(datasetID)
	copier := dataset.Table(dstID).CopierFrom(dataset.Table(srcID))
	copier.WriteDisposition = bigquery.WriteTruncate
	job, err := copier.Run(ctx)
	if err != nil {
		return err
	}
	status, err := job.Wait(ctx)
	if err != nil {
		return err
	}
	if err := status.Err(); err != nil {
		return 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.

To authenticate to BigQuery, set up Application Default Credentials. For more information, see Set up authentication for a local development environment.

import com.google.cloud.bigquery.BigQuery;
import com.google.cloud.bigquery.BigQueryException;
import com.google.cloud.bigquery.BigQueryOptions;
import com.google.cloud.bigquery.CopyJobConfiguration;
import com.google.cloud.bigquery.Job;
import com.google.cloud.bigquery.JobInfo;
import com.google.cloud.bigquery.TableId;

public class CopyTable {

  public static void runCopyTable() {
    // TODO(developer): Replace these variables before running the sample.
    String destinationDatasetName = "MY_DESTINATION_DATASET_NAME";
    String destinationTableId = "MY_DESTINATION_TABLE_NAME";
    String sourceDatasetName = "MY_SOURCE_DATASET_NAME";
    String sourceTableId = "MY_SOURCE_TABLE_NAME";

    copyTable(sourceDatasetName, sourceTableId, destinationDatasetName, destinationTableId);
  }

  public static void copyTable(
      String sourceDatasetName,
      String sourceTableId,
      String destinationDatasetName,
      String destinationTableId) {
    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.
      BigQuery bigquery = BigQueryOptions.getDefaultInstance().getService();

      TableId sourceTable = TableId.of(sourceDatasetName, sourceTableId);
      TableId destinationTable = TableId.of(destinationDatasetName, destinationTableId);

      // For more information on CopyJobConfiguration see:
      // https://googleapis.dev/java/google-cloud-clients/latest/com/google/cloud/bigquery/JobConfiguration.html
      CopyJobConfiguration configuration =
          CopyJobConfiguration.newBuilder(destinationTable, sourceTable).build();

      // For more information on Job see:
      // https://googleapis.dev/java/google-cloud-clients/latest/index.html?com/google/cloud/bigquery/package-summary.html
      Job job = bigquery.create(JobInfo.of(configuration));

      // Blocks until this job completes its execution, either failing or succeeding.
      Job completedJob = job.waitFor();
      if (completedJob == null) {
        System.out.println("Job not executed since it no longer exists.");
        return;
      } else if (completedJob.getStatus().getError() != null) {
        System.out.println(
            "BigQuery was unable to copy table due to an error: \n" + job.getStatus().getError());
        return;
      }
      System.out.println("Table copied successfully.");
    } catch (BigQueryException | InterruptedException e) {
      System.out.println("Table copying job was interrupted. \n" + e.toString());
    }
  }
}

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.

To authenticate to BigQuery, set up Application Default Credentials. For more information, see