Managing tables

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

Update a table's:
- Expiration time
- Description
- Schema definition
- Labels
Rename (copy) a table
Copy a table
Delete a table
Restore a deleted table

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.

Updating table properties

You can update the following elements of a table:

Required permissions

At a minimum, to update a table, you must be granted bigquery.tables.update and bigquery.tables.get permissions. The following predefined IAM roles include bigquery.tables.update and bigquery.tables.get permissions:

bigquery.dataEditor
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 users the ability to update table properties in datasets they create.

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

Updating a table's description

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

Using the Cloud Console.
Using a 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 cannot add a description when you create a table using the Cloud Console. After the table is created, you can add a description on the Details page.

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

Note: The default experience is the Preview Cloud Console. If you clicked Hide preview features to go to the Generally Available Cloud Console, then perform the following step instead: In the navigation panel, in the Resources section, expand your project and dataset, and then click the table name.
In the details panel, click Details.
In the Description section, click the pencil icon to edit the description.
Enter a description in the box, and click Update to save.

SQL

Data definition language (DDL) statements allow you to create and modify tables and views using standard SQL query syntax.

See more on Using data definition language statements.

To update a table's description by using a DDL statement in the Cloud Console:

Click Compose new query.

Type your DDL statement into the Query editor text area.

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

Click Run.

bq

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:

Enter the following command to change the description of mytable in mydatasetto "Description of mytable." mydataset is in your default project.

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

Enter the following command to change the description of mytable in mydataset to "Description of mytable." mydataset is in myotherproject, 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.

View on GitHub Feedback

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.

View on GitHub Feedback

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.

Configure the Table.description property and call Client.update_table() to send the update to the API.

View on GitHub Feedback

# 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."

Updating 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.

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 Cloud Console.
Using a 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 cannot add an expiration time when you create a table using the Cloud Console. After a table is created, you can add or update a table expiration on the Table Details page.

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

Note: The default experience is the Preview Cloud Console. If you clicked Hide preview features to go to the Generally Available Cloud Console, then perform the following step instead: In the navigation panel, in the Resources section, expand your project and dataset, and then click the table name.
In the detalis panel, click Details.
Click the pencil icon next to Table info
For Table expiration, select Specify date. Then select the expiration date using the calendar widget.
Click Update to save. The updated expiration time appears in the Table info section.

SQL

Data definition language (DDL) statements allow you to create and modify tables and views using standard SQL query syntax.

See more on Using data definition language statements.

To update the expiration time by using a DDL statement in the Cloud Console:

Click Compose new query.

Type your DDL statement into the Query editor text area.

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

Click Run.

bq

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:

Enter the following command to update the expiration time of mytable in mydataset to 5 days (432000 seconds). mydataset is in your default project.

bq update --expiration 432000 mydataset.mytable

Enter the following command to update the expiration time of mytable in mydataset to 5 days (432000 seconds). mydataset is in myotherproject, 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.

View on GitHub Feedback

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.

View on GitHub Feedback

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.

View on GitHub Feedback

// 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.

Configure Table.expires property and call Client.update_table() to send the update to the API.

View on GitHub Feedback

import datetime
import pytz

# 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.expires is None

# set table to expire 5 days from now
expiration = datetime.datetime.now(pytz.utc) + datetime.timedelta(days=5)
table.expires = expiration
table = client.update_table(table, ["expires"])  # API request

# expiration is stored in milliseconds
margin = datetime.timedelta(microseconds=1000)
assert expiration - margin <= table.expires <= expiration + margin

Updating a table's schema definition

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

Renaming a table

Currently, you cannot change the name of an existing table. If you need to change the table name, follow the steps to copy the table. When you specify the destination table in the copy operation, use the new table name.

Copying a table

You can copy a table in the following ways:

Using the Cloud Console.
Using the bq command-line tool's bq cp command.
Calling the jobs.insert API method and configuring a copy job.
Using the client libraries.

Required permissions

At a minimum, to copy tables and partitions, you must be granted the following permissions.

On the source dataset:

bigquery.tables.get
bigquery.tables.getData

On the destination dataset:

bigquery.tables.create to create the copy of the table or partition in the destination dataset

The following predefined IAM roles include bigquery.tables.create, bigquery.tables.get, and bigquery.tables.getData permissions:

bigquery.dataEditor
bigquery.dataOwner
bigquery.admin

Also, to run the copy job, you must be granted bigquery.jobs.create permissions.

The following predefined IAM roles include bigquery.jobs.create permissions:

bigquery.user
bigquery.jobUser
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 copy tables and partitions in the dataset, but access to the destination dataset is required unless the user also created the destination dataset.

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

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's quota policies on copy jobs.
When you use the Cloud Console to copy a table, you cannot overwrite an existing table in the destination dataset. The table must have a unique name in the destination dataset.
When copying tables, the destination dataset must reside in the same location as the dataset containing the table being copied. For example, you cannot copy a table from an EU-based dataset and write it to a US-based dataset.
Copying multiple source tables into a destination table is not supported by the Cloud Console.
When copying multiple source tables to a destination table using the API or bq command-line tool, all source tables must have identical schemas.
The time that BigQuery takes to copy tables might vary significantly across different runs because the underlying storage is managed dynamically.

Copying a single source table

You can copy a single table in the following ways:

Using the Cloud Console.
Using the bq command-line tool's bq cp command.
Calling the jobs.insert API method, configuring a copy job, and specifying the sourceTable property.
Using the client libraries.

The Cloud Console supports 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

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

Note: The default experience is the Preview Cloud Console. If you clicked Hide preview features to go to the Generally Available Cloud Console, then perform the following step instead: In the navigation panel, in the Resources section, expand your project and dataset, and then click the table name.
In the details panel, click Copy table.
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 cannot overwrite an existing table in the destination dataset using the Cloud Console. For more information about table name requirements, see Table naming.
Click Copy to start the copy job.

bq

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:

Enter the following command to copy mydataset.mytable to mydataset2.mytable2. Both datasets are in your default project.

bq cp mydataset.mytable mydataset2.mytable2

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

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

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

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

Enter the following command to copy a mydataset.mytable and to append the data to a destination table with the same name. The source dataset is in your default project. The destination dataset is in myotherproject. 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.

View on GitHub Feedback


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.
        // 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.

View on GitHub Feedback

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.

View on GitHub Feedback

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.

View on GitHub Feedback

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

async function copyTable() {
  // Copies src_dataset:src_table to dest_dataset:dest_table.

  /**
   * TODO(developer): Uncomment the following lines before running the sample
   */
  // const srcDatasetId = "my_src_dataset";
  // const srcTableId = "my_src_table";
  // const destDatasetId = "my_dest_dataset";
  // const destTableId = "my_dest_table";

  // Copy the table contents into another table
  const [job] = await bigquery
    .dataset(srcDatasetId)
    .table(srcTableId)
    .copy(bigquery.dataset(destDatasetId).table(destTableId));

  console.log(`Job ${job.id} completed.`);

  // Check the job's status for errors
  const errors = job.status.errors;
  if (errors && errors.length > 0) {
    throw errors;
  }
}

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;
use Google\Cloud\Core\ExponentialBackoff;

/** Uncomment and populate these variables in your code */
// $projectId = 'The Google project ID';
// $datasetId = 'The BigQuery dataset ID';
// $sourceTableId   = 'The BigQuery table ID to copy from';
// $destinationTableId = 'The BigQuery table ID to copy to';

$bigQuery = new BigQueryClient([
    'projectId' => $projectId,
]);
$dataset = $bigQuery->dataset($datasetId);
$sourceTable = $dataset->table($sourceTableId);
$destinationTable = $dataset->table($destinationTableId);
$copyConfig = $sourceTable->copy($destinationTable);
$job = $sourceTable->runJob($copyConfig);

// poll the job until it is complete
$backoff = new ExponentialBackoff(10);
$backoff->execute(function () use ($job) {
    print('Waiting for job to complete' . PHP_EOL);
    $job->reload();
    if (!$job->isComplete()) {
        throw new Exception('Job has not yet completed', 500);
    }
});
// check if the job has errors
if (isset($job->info()['status']['errorResult'])) {
    $error = $job->info()['status']['errorResult']['message'];
    printf('Error running job: %s' . PHP_EOL, $error);
} else {
    print('Table copied successfully' . PHP_EOL);
}

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 source_table_id to the ID of the original table.
# source_table_id = "your-project.source_dataset.source_table"

# TODO(developer): Set destination_table_id to the ID of the destination table.
# destination_table_id = "your-project.destination_dataset.destination_table"

job = client.copy_table(source_table_id, destination_table_id)
job.result()  # Wait for the job to complete.

print("A copy of the table created.")

Copying multiple source tables

You can copy multiple source tables to a destination table in the following ways:

Using the bq command-line tool's bq cp command.
Calling the jobs.insert method, configuring a copy job, and specifying the sourceTables property.
Using the client libraries.

All source tables must have identical schemas, and only one destination table is allowed.

Source tables must be specified as a comma-separated list. You cannot use wildcards when you copy multiple source tables.

To copy multiple source tables:

Console

Copying multiple tables is not currently supported by the Cloud Console.

bq

Issue the bq cp command and include multiple source tables as a comma-separated list. Optional flags can be used to control the write disposition of the destination table:

-a or --append_table appends the data from the source tables to an existing table in the destination dataset.
-f or --force overwrites an existing destination 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 Key Management Service 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.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 that you're copying.
destination_table: the name of the table in the destination dataset.

Examples:

Enter the following command to copy mydataset.mytable and mydataset.mytable2 to mydataset2.tablecopy. All datasets are in your default project.

bq cp \
mydataset.mytable,mydataset.mytable2 \
mydataset2.tablecopy

Enter the following command to copy mydataset.mytable and mydataset.mytable2 to myotherdataset.mytableand to overwrite a destination table with the same name. The destination dataset is in myotherproject, not your default project. The -f shortcut is used to overwrite the destination table without a prompt.

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

Enter the following command to copy myproject:mydataset.mytable and myproject:mydataset.mytable2 and to return an error if the destination dataset contains a table with the same name. The destination dataset is in myotherproject. The -n shortcut is used to prevent overwriting a table with the same name.

bq cp -n \
myproject:mydataset.mytable,myproject:mydataset.mytable2 \
myotherproject:myotherdataset.mytable

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

bq cp -a \
mydataset.mytable,mydataset.mytable2 \
myotherproject:myotherdataset.mytable

API

To copy multiple tables using the API, call the jobs.insert method, configure a table copy job, and specify the sourceTables property.

Specify your region in the location property in the jobReference section of the job resource.

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

// copyMultiTable demonstrates using a copy job to copy multiple source tables into a single destination table.
func copyMultiTable(projectID, srcDatasetID string, srcTableIDs []string, dstDatasetID, dstTableID string) error {
	// projectID := "my-project-id"
	// srcDatasetID := "sourcedataset"
	// srcTableIDs := []string{"table1","table2"}
	// dstDatasetID = "destinationdataset"
	// dstTableID = "destinationtable"
	ctx := context.Background()
	client, err := bigquery.NewClient(ctx, projectID)
	if err != nil {
		return fmt.Errorf("bigquery.NewClient: %v", err)
	}
	defer client.Close()

	srcDataset := client.Dataset(srcDatasetID)
	dstDataset := client.Dataset(dstDatasetID)
	var tableRefs []*bigquery.Table
	for _, v := range srcTableIDs {
		tableRefs = append(tableRefs, srcDataset.Table(v))
	}
	copier := dstDataset.Table(dstTableID).CopierFrom(tableRefs...)
	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.

View on GitHub Feedback

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;
import java.util.Arrays;

public class CopyMultipleTables {

  public static void runCopyMultipleTables() {
    // TODO(developer): Replace these variables before running the sample.
    String destinationDatasetName = "MY_DATASET_NAME";
    String destinationTableId = "MY_TABLE_NAME";
    copyMultipleTables(destinationDatasetName, destinationTableId);
  }

  public static void copyMultipleTables(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 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,
                  Arrays.asList(
                      TableId.of(destinationDatasetName, "table1"),
                      TableId.of(destinationDatasetName, "table2")))
              .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 tables 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.

View on GitHub Feedback

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

async function copyTableMultipleSource() {
  // Copy multiple source tables to a given destination.

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

  // Create a client
  const dataset = bigquery.dataset(datasetId);

  const metadata = {
    createDisposition: 'CREATE_NEVER',
    writeDisposition: 'WRITE_TRUNCATE',
  };

  // Create table references
  const table = dataset.table(sourceTable);
  const yourTable = dataset.table(destinationTable);

  // Copy table
  const [apiResponse] = await table.copy(yourTable, metadata);
  console.log(apiResponse.configuration.copy);
}

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 dest_table_id to the ID of the destination table.
# dest_table_id = "your-project.your_dataset.your_table_name"

# TODO(developer): Set table_ids to the list of the IDs of the original tables.
# table_ids = ["your-project.your_dataset.your_table_name", ...]

job = client.copy_table(table_ids, dest_table_id)  # Make an API request.
job.result()  # Wait for the job to complete.

print("The tables {} have been appended to {}".format(table_ids, dest_table_id))

Deleting tables

You can delete a table in the following ways:

Using the Cloud Console.
Using the bq command-line tool bq rm command.
Calling the tables.delete API method.
Using the client libraries.

Currently, you can only delete one table at a time.

When you delete a table, any data in the table is also deleted. To automatically delete tables after a specified period of time, set the default table expiration for the dataset or set the expiration time when you create the table.

Required permissions

At a minimum, to delete a table, you must be granted bigquery.tables.delete and bigquery.tables.get permissions. The following predefined IAM roles include bigquery.tables.delete and bigquery.tables.get permissions:

bigquery.dataOwner
bigquery.dataEditor
bigquery.admin

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

Deleting a table

To delete a table:

Console

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

Note: The default experience is the Preview Cloud Console. If you clicked Hide preview features to go to the Generally Available Cloud Console, then perform the following step instead: In the navigation panel, in the Resources section, expand your project and dataset, and then click the table name.
In the details panel*, click Delete table.
Type the name of the table in the dialog, then click Delete to confirm.

SQL

Data definition language (DDL) statements let you delete tables using standard SQL query syntax.

See more on Using data definition language statements.

To delete an existing table using a DDL statement in the Cloud Console, follow these steps:

Click Compose new query.
Type your DDL statement into the Query editor field.
```
 DROP TABLE mydataset.mytable
 
```
Click Run.

bq

Use the bq rm command with the --table flag (or -t shortcut) to delete a table. When you use the bq command-line tool to remove a table, you must confirm the action. You can use the --force flag (or -f shortcut) to skip confirmation.

If the table is in a dataset 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 \
-f \
-t \
project_id:dataset.table

Replace the following:

project_id: your project ID
dataset: the name of the dataset that contains the table
table: the name of the table that you're deleting

Examples:

Enter the following command to delete mytable from mydataset. mydataset is in your default project.

bq rm -t mydataset.mytable

Enter the following command to delete mytable from mydataset. mydataset is in myotherproject, not your default project.

bq rm -t myotherproject:mydataset.mytable

Enter the following command to delete mytable from mydataset. mydataset is in your default project. The command uses the -f shortcut to bypass confirmation.

bq rm -f -t mydataset.mytable

API

Call the tables.delete API method and specify the table to delete using the tableId parameter.

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.

View on GitHub Feedback


using Google.Cloud.BigQuery.V2;
using System;

public class BigQueryDeleteTable
{
    public void DeleteTable(
        string projectId = "your-project-id",
        string datasetId = "your_dataset_id",
        string tableId = "your_table_id"
    )
    {
        BigQueryClient client = BigQueryClient.Create(projectId);
        client.DeleteTable(datasetId, tableId);
        Console.WriteLine($"Table {tableId} deleted.");
    }
}

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

// deleteTable demonstrates deletion of a BigQuery table.
func deleteTable(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()

	table := client.Dataset(datasetID).Table(tableID)
	if err := table.Delete(ctx); 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.

View on GitHub Feedback

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

public class DeleteTable {

  public static void runDeleteTable() {
    // TODO(developer): Replace these variables before running the sample.
    String datasetName = "MY_DATASET_NAME";
    String tableName = "MY_TABLE_NAME";
    deleteTable(datasetName, tableName);
  }

  public static void deleteTable(String datasetName, String tableName) {
    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();
      boolean success = bigquery.delete(TableId.of(datasetName, tableName));
      if (success) {
        System.out.println("Table deleted successfully");
      } else {
        System.out.println("Table was not found");
      }
    } catch (BigQueryException e) {
      System.out.println("Table was not deleted. \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.

View on GitHub Feedback

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

async function deleteTable() {
  // Deletes "my_table" from "my_dataset".

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

  // Delete the table
  await bigquery
    .dataset(datasetId)
    .table(tableId)
    .delete();

  console.log(`Table ${tableId} 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';
// $tableId = 'The BigQuery table ID';

$bigQuery = new BigQueryClient([
    'projectId' => $projectId,
]);
$dataset = $bigQuery->dataset($datasetId);
$table = $dataset->table($tableId);
$table->delete();
printf('Deleted table %s.%s' . PHP_EOL, $datasetId, $tableId);

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 table_id to the ID of the table to fetch.
# table_id = 'your-project.your_dataset.your_table'

# If the table does not exist, delete_table raises
# google.api_core.exceptions.NotFound unless not_found_ok is True.
client.delete_table(table_id, not_found_ok=True)  # Make an API request.
print("Deleted table '{}'.".format(table_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_table dataset_id = "my_dataset_id", table_id = "my_table_id"
  bigquery = Google::Cloud::Bigquery.new
  dataset  = bigquery.dataset dataset_id
  table    = dataset.table table_id

  table.delete

  puts "Table #{table_id} deleted."
end

Restoring deleted tables

You can undelete a table within seven days of deletion, including explicit deletions and implicit deletions due to table expiration. After seven days, it is not possible to undelete a table using any method, including opening a support ticket.

You can restore a deleted table by:

Using the @<time> snapshot decorator in the bq command-line tool
Using the client libraries

Console

You cannot undelete a table by using the Cloud Console.

bq

To restore a table, use a table copy operation with the @<time> snapshot decorator. First, determine a UNIX timestamp of when the table existed (in milliseconds). Then, use the bq copy command with the snapshot decorator.

For example, enter the following command to copy mydataset.mytable at the time 1418864998000 into a new table mydataset.newtable.

bq cp mydataset.mytable@1418864998000 mydataset.newtable

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

You can also specify a relative offset. The following example copies the version of a table from one hour ago:

bq cp mydataset.mytable@-3600000 mydataset.newtable

For more information, see Restore a table from a point in time.

Go

View on GitHub

import (
	"context"
	"fmt"
	"time"

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

// deleteAndUndeleteTable demonstrates how to recover a deleted table by copying it from a point in time
// that predates the deletion event.
func deleteAndUndeleteTable(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()

	ds := client.Dataset(datasetID)
	if _, err := ds.Table(tableID).Metadata(ctx); err != nil {
		return err
	}
	// Record the current time.  We'll use this as the snapshot time
	// for recovering the table.
	snapTime := time.Now()

	// "Accidentally" delete the table.
	if err := client.Dataset(datasetID).Table(tableID).Delete(ctx); err != nil {
		return err
	}

	// Construct the restore-from tableID using a snapshot decorator.
	snapshotTableID := fmt.Sprintf("%s@%d", tableID, snapTime.UnixNano()/1e6)
	// Choose a new table ID for the recovered table data.
	recoverTableID := fmt.Sprintf("%s_recovered", tableID)

	// Construct and run a copy job.
	copier := ds.Table(recoverTableID).CopierFrom(ds.Table(snapshotTableID))
	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
	}

	ds.Table(recoverTableID).Delete(ctx)
	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

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;

// Sample to undeleting a table
public class UndeleteTable {

  public static void runUndeleteTable() {
    // TODO(developer): Replace these variables before running the sample.
    String datasetName = "MY_DATASET_NAME";
    String tableName = "MY_TABLE_TABLE";
    String recoverTableName = "MY_RECOVER_TABLE_TABLE";
    undeleteTable(datasetName, tableName, recoverTableName);
  }

  public static void undeleteTable(String datasetName, String tableName, String recoverTableName) {
    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();

      // "Accidentally" delete the table.
      bigquery.delete(TableId.of(datasetName, tableName));

      // Record the current time.  We'll use this as the snapshot time
      // for recovering the table.
      long snapTime = System.currentTimeMillis();

      // Construct the restore-from tableID using a snapshot decorator.
      String snapshotTableId = String.format("%s@%d", tableName, snapTime);

      // Construct and run a copy job.
      CopyJobConfiguration configuration =
          CopyJobConfiguration.newBuilder(
                  // Choose a new table ID for the recovered table data.
                  TableId.of(datasetName, recoverTableName),
                  TableId.of(datasetName, snapshotTableId))
              .build();

      Job job = bigquery.create(JobInfo.of(configuration));
      job = job.waitFor();
      if (job.isDone() && job.getStatus().getError() == null) {
        System.out.println("Undelete table recovered successfully.");
      } else {
        System.out.println(
            "BigQuery was unable to copy the table due to an error: \n"
                + job.getStatus().getError());
        return;
      }
    } catch (BigQueryException | InterruptedException e) {
      System.out.println("Table not found. \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.

View on GitHub Feedback

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

async function undeleteTable() {
  // Undeletes "my_table_to_undelete" from "my_dataset".

  /**
   * TODO(developer): Uncomment the following lines before running the sample.
   */
  // const datasetId = "my_dataset";
  // const tableId = "my_table_to_undelete";
  // const recoveredTableId = "my_recovered_table";

  /**
   * TODO(developer): Choose an appropriate snapshot point as epoch milliseconds.
   * For this example, we choose the current time as we're about to delete the
   * table immediately afterwards.
   */
  const snapshotEpoch = Date.now();

  // Delete the table
  await bigquery
    .dataset(datasetId)
    .table(tableId)
    .delete();

  console.log(`Table ${tableId} deleted.`);

  // Construct the restore-from table ID using a snapshot decorator.
  const snapshotTableId = `${tableId}@${snapshotEpoch}`;

  // Construct and run a copy job.
  await bigquery
    .dataset(datasetId)
    .table(snapshotTableId)
    .copy(bigquery.dataset(datasetId).table(recoveredTableId));

  console.log(
    `Copied data from deleted table ${tableId} to ${recoveredTableId}`
  );
}

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

import time

from google.cloud import bigquery

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

# TODO(developer): Choose a table to recover.
# table_id = "your-project.your_dataset.your_table"

# TODO(developer): Choose a new table ID for the recovered table data.
# recovery_table_id = "your-project.your_dataset.your_table_recovered"

# TODO(developer): Choose an appropriate snapshot point as epoch
# milliseconds. For this example, we choose the current time as we're about
# to delete the table immediately afterwards.
snapshot_epoch = int(time.time() * 1000)

# ...

# "Accidentally" delete the table.
client.delete_table(table_id)  # Make an API request.

# Construct the restore-from table ID using a snapshot decorator.
snapshot_table_id = "{}@{}".format(table_id, snapshot_epoch)

# Construct and run a copy job.
job = client.copy_table(
    snapshot_table_id,
    recovered_table_id,
    # Must match the source and destination tables location.
    location="US",
)  # Make an API request.

job.result()  # Wait for the job to complete.

print(
    "Copied data from deleted table {} to {}".format(table_id, recovered_table_id)
)

Next steps

For more information about creating and using tables, see Creating and using tables.
For more information about handling data, see Working With table data.
For more information about specifying table schemas, see Specifying a schema.
For more information about modifying table schemas, see Modifying table schemas.
For more information about datasets, see Introduction to datasets.
For more information about views, see Introduction to views.