Manage tables (original) (raw)

Skip to main content

You can manage BigQuery tables to control their lifecycle, optimize storage costs, and ensure data governance. Table management includes tasks such as updating properties, renaming, copying, and deleting tables. When you update BigQuery table properties, you can modify their expiration time, description, schema definition, labels, and default rounding mode.

To restore a deleted table, see Restore deleted tables. To learn more about creating and using tables, including getting table information, listing tables, and controlling access to table data, seeCreating 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 theData Editor (roles/bigquery.dataEditor) IAM role on a table. For more information about granting roles, see Manage access to projects, folders, and organizations.

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:

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:

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 left pane, click Explorer:
    Highlighted button for the Explorer pane.
    If you don't see the left pane, click Expand left pane to open the pane.
  2. In the Explorer pane, expand your project, click Datasets, and then select a dataset.
  3. Click Overview > Tables, and then select a table.
  4. Click the Details tab, and then click Edit details.
  5. In the Description section, add a new description or edit an existing description.
  6. Click Save.

SQL

Use theALTER 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, aCloud 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 themyotherproject project, not your default project.
      bq update \
      --description "Description of mytable" \
      myotherproject:mydataset.mytable

API

Call the tables.patchmethod and use the description property in the table resourceto 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 theBigQuery quickstart using client libraries. For more information, see theBigQuery Go API reference documentation.

To authenticate to BigQuery, set up Application Default Credentials. For more information, seeSet up authentication for client libraries.

Java

Before trying this sample, follow the Java setup instructions in theBigQuery quickstart using client libraries. For more information, see theBigQuery Java API reference documentation.

To authenticate to BigQuery, set up Application Default Credentials. For more information, seeSet up authentication for client libraries.

Python

Before trying this sample, follow the Python setup instructions in theBigQuery quickstart using client libraries. For more information, see theBigQuery Python API reference documentation.

To authenticate to BigQuery, set up Application Default Credentials. For more information, seeSet up authentication for client libraries.

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

Gemini

You can generate a table description with Gemini in BigQuery by using data insights. Data insights is an automated way to explore, understand, and curate your data.

For more information about data insights, including setup steps, required IAM roles, and best practices to improve the accuracy of the generated insights, seeGenerate data insights in BigQuery.

  1. In the Google Cloud console, go to the BigQuery page.
    Go to BigQuery
  2. In the left pane, click Explorer:
    Highlighted button for the Explorer pane.
  3. In the Explorer pane, expand your project and dataset, then select the table.
  4. In the details panel, click the Schema tab.
  5. Click Generate.
    Gemini generates a table description and insights about the table. It takes a few minutes for the information to be populated. You can view the generated insights on the table'sInsights tab.
  6. To edit and save the generated table description, do the following:
    1. Click View column descriptions.
      The current table description and the generated description are displayed.
    2. In the Table description section, click Save to details.
    3. To replace the current description with the generated description, click Copy suggested description.
    4. Edit the table description as necessary, and then clickSave to details.
      The table description is updated immediately.
    5. To close the Preview descriptions panel, clickClose.

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, seeRestore 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:

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 left pane, click Explorer:
    Highlighted button for the Explorer pane.
  2. In the Explorer pane, expand your project, click Datasets, and then select a dataset.
  3. Click Overview > Tables, and then select a table.
  4. Click the Details tab and the click Edit details.
  5. For Expiration time, select Specify date. Then select the expiration date using the calendar widget.
  6. Click Save. The updated expiration time appears in theTable info section.

SQL

Use theALTER 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, aCloud 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 specify0, 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 themyotherproject project, not your default project.
      bq update --expiration 432000 myotherproject:mydataset.mytable

API

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

Go

Before trying this sample, follow the Go setup instructions in theBigQuery quickstart using client libraries. For more information, see theBigQuery Go API reference documentation.

To authenticate to BigQuery, set up Application Default Credentials. For more information, seeSet up authentication for client libraries.

Java

Before trying this sample, follow the Java setup instructions in theBigQuery quickstart using client libraries. For more information, see theBigQuery Java API reference documentation.

To authenticate to BigQuery, set up Application Default Credentials. For more information, seeSet up authentication for client libraries.

Node.js

Before trying this sample, follow the Node.js setup instructions in theBigQuery quickstart using client libraries. For more information, see theBigQuery Node.js API reference documentation.

To authenticate to BigQuery, set up Application Default Credentials. For more information, seeSet up authentication for client libraries.

Python

Before trying this sample, follow the Python setup instructions in theBigQuery quickstart using client libraries. For more information, see theBigQuery Python API reference documentation.

To authenticate to BigQuery, set up Application Default Credentials. For more information, seeSet up authentication for client libraries.

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

To update the default dataset partition expiration time:

Update a table's rounding mode

You can update a table'sdefault rounding modeby using theALTER TABLE SET OPTIONS DDL statement. The following example updates the default rounding mode for mytable toROUND_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, seeModifying table schemas.

Rename a table

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

ALTER TABLE mydataset.mytable RENAME TO mynewtable;

The ALTER TABLE RENAME TO statement recreates the table in the destination dataset with the creation timestamp of the original table. If you have configured dataset-level table expiration, the renamed table might be immediately deleted if its original creation timestamp falls outside of the expiration window.

Limitations on renaming 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 andtable snapshots.

You can copy a table in the following ways:

Limitations on copying tables

Table copy jobs are subject to the following limitations:

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 theData Editor (roles/bigquery.dataEditor) IAM role on the source and destination datasets. For more information about granting roles, see Manage access to projects, folders, and organizations.

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:

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 theJob User (roles/bigquery.jobUser) IAM role on the source and destination datasets. For more information about granting roles, see Manage access to projects, folders, and organizations.

This predefined role contains thebigquery.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:

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 filesto a destination table, you must use the bq command-line tool or the API.

To copy a single source table:

Console

  1. In the left pane, click Explorer:
    Highlighted button for the Explorer pane.
  2. In the Explorer pane, expand your project, click Datasets, and then select a dataset.
  3. Click Overview > Tables, and then select a table.
  4. In the details pane, click Copy.
  5. In the Copy table dialog, under Destination:
    • For Project, choose the project that will store the copied table.
    • For Dataset, select the dataset where you want to store the copied table. The source and destination datasets must be in the same location.
    • For Table, 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, seeTable naming.
  6. Click Copy to start the copy job.

SQL

Use theCREATE TABLE COPY statementto copy a table namedtable1 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, aCloud 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. SeeProtecting data with Cloud Key Management Service keysfor 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 yourlocation.
      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 myotherprojectproject. 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 themyotherproject 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 thebigquery.jobs.insertmethod, and configuring a copy job. Specify your location in the location property in the jobReference section of thejob 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 theBigQuery quickstart using client libraries. For more information, see theBigQuery C# API reference documentation.

To authenticate to BigQuery, set up Application Default Credentials. For more information, seeSet up authentication for client libraries.

Go

Before trying this sample, follow the Go setup instructions in theBigQuery quickstart using client libraries. For more information, see theBigQuery Go API reference documentation.

To authenticate to BigQuery, set up Application Default Credentials. For more information, seeSet up authentication for client libraries.

Java

Before trying this sample, follow the Java setup instructions in theBigQuery quickstart using client libraries. For more information, see theBigQuery Java API reference documentation.

To authenticate to BigQuery, set up Application Default Credentials. For more information, seeSet up authentication for client libraries.

Node.js

Before trying this sample, follow the Node.js setup instructions in theBigQuery quickstart using client libraries. For more information, see theBigQuery Node.js API reference documentation.

To authenticate to BigQuery, set up Application Default Credentials. For more information, seeSet up authentication for client libraries.

PHP

Before trying this sample, follow the PHP setup instructions in theBigQuery quickstart using client libraries. For more information, see theBigQuery PHP API reference documentation.

To authenticate to BigQuery, set up Application Default Credentials. For more information, seeSet up authentication for client libraries.

Python

Before trying this sample, follow the Python setup instructions in theBigQuery quickstart using client libraries. For more information, see theBigQuery Python API reference documentation.

To authenticate to BigQuery, set up Application Default Credentials. For more information, seeSet up authentication for client libraries.

Copy multiple source tables

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

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

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

To copy multiple source tables, select one of the following choices:

bq

  1. In the Google Cloud console, activate Cloud Shell.
    Activate Cloud Shell
    At the bottom of the Google Cloud console, aCloud 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 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. SeeProtecting data with Cloud Key Management Service keysfor 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 yourlocation.
      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:
      To copy the mydataset.mytable table and the mydataset.mytable2 table tomydataset2.tablecopy table, enter the following command . All datasets are in your default project.
      bq cp \
      mydataset.mytable,mydataset.mytable2 \
      mydataset2.tablecopy
      To copy the mydataset.mytable table and the mydataset.mytable2 table tomyotherdataset.mytable table and to overwrite a destination table with the same name, enter the following command. The destination dataset is in themyotherproject project, 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
      To copy the myproject:mydataset.mytable table and themyproject:mydataset.mytable2 table and to return an error if the destination dataset contains a table with the same name, enter the following command. 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 \
      myproject:mydataset.mytable,myproject:mydataset.mytable2 \
      myotherproject:myotherdataset.mytable
      To copy the mydataset.mytable table and the mydataset.mytable2 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,mydataset.mytable2 \
      myotherproject:myotherdataset.mytable

API

To copy multiple tables using the API, call thejobs.insertmethod, configure a table copy job, and specify the sourceTablesproperty.

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

Go

Before trying this sample, follow the Go setup instructions in theBigQuery quickstart using client libraries. For more information, see theBigQuery Go API reference documentation.

To authenticate to BigQuery, set up Application Default Credentials. For more information, seeSet up authentication for client libraries.

Java

Before trying this sample, follow the Java setup instructions in theBigQuery quickstart using client libraries. For more information, see theBigQuery Java API reference documentation.

To authenticate to BigQuery, set up Application Default Credentials. For more information, seeSet up authentication for client libraries.

Node.js

Before trying this sample, follow the Node.js setup instructions in theBigQuery quickstart using client libraries. For more information, see theBigQuery Node.js API reference documentation.

To authenticate to BigQuery, set up Application Default Credentials. For more information, seeSet up authentication for client libraries.

Python

Before trying this sample, follow the Python setup instructions in theBigQuery quickstart using client libraries. For more information, see theBigQuery Python API reference documentation.

To authenticate to BigQuery, set up Application Default Credentials. For more information, seeSet up authentication for client libraries.

Copy tables across regions

You can copy a table, table snapshot, ortable clone from one BigQuery region or multi-region to another. This includes any tables that have customer-managed Cloud KMS (CMEK) applied.

Copying a table across regions incurs additional data transfer charges according to BigQuery pricing. Additional charges are incurred even if you cancel the cross-region table copy job before it has been completed.

To copy a table across regions, select one of the following options:

bq

  1. In the Google Cloud console, activate Cloud Shell.
    Activate Cloud Shell
    At the bottom of the Google Cloud console, aCloud 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. Run the bq cp command:

    bq cp
    -f -n
    SOURCE_PROJECT:SOURCE_DATASET.SOURCE_TABLE
    DESTINATION_PROJECT:DESTINATION_DATASET.DESTINATION_TABLE

Replace the following:

The following example is a command that copies the mydataset_us.mytable table from the us multi-region to the mydataset_eu.mytable2 table in the eu multi-region. Both datasets are in the default project.

bq cp --sync=false mydataset_us.mytable mydataset_eu.mytable2

To copy a table across regions into a CMEK-enabled destination dataset, you must enable CMEK on the table with a key from the table's region. The CMEK on the table doesn't have to be the same CMEK in use by the destination dataset. The following example copies a CMEK-enabled table to a destination dataset using the bq cp command.

bq cp source-project-id:source-dataset-id.source-table-id destination-project-id:destination-dataset-id.destination-table-id

Conversely, to copy a CMEK-enabled table across regions into a destination dataset, you can enable CMEK on the destination dataset with a key from the destination dataset's region. You can also use the destination_kms_keys flag in the bq cp command, as shown in the following example:

bq cp --destination_kms_key=projects/project_id/locations/eu/keyRings/eu_key/cryptoKeys/eu_region mydataset_us.mytable mydataset_eu.mytable2

API

To copy a table across regions using the API, call thejobs.insertmethod and configure a table copy job.

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

C#

Before trying this sample, follow the C# setup instructions in theBigQuery quickstart using client libraries. For more information, see theBigQuery C# API reference documentation.

To authenticate to BigQuery, set up Application Default Credentials. For more information, seeSet up authentication for client libraries.

Go

Before trying this sample, follow the Go setup instructions in theBigQuery quickstart using client libraries. For more information, see theBigQuery Go API reference documentation.

To authenticate to BigQuery, set up Application Default Credentials. For more information, seeSet up authentication for client libraries.

Java

Before trying this sample, follow the Java setup instructions in theBigQuery quickstart using client libraries. For more information, see theBigQuery Java API reference documentation.

To authenticate to BigQuery, set up Application Default Credentials. For more information, seeSet up authentication for client libraries.

Node.js

Before trying this sample, follow the Node.js setup instructions in theBigQuery quickstart using client libraries. For more information, see theBigQuery Node.js API reference documentation.

To authenticate to BigQuery, set up Application Default Credentials. For more information, seeSet up authentication for client libraries.

PHP

Before trying this sample, follow the PHP setup instructions in theBigQuery quickstart using client libraries. For more information, see theBigQuery PHP API reference documentation.

To authenticate to BigQuery, set up Application Default Credentials. For more information, seeSet up authentication for client libraries.

Python

Before trying this sample, follow the Python setup instructions in theBigQuery quickstart using client libraries. For more information, see theBigQuery Python API reference documentation.

To authenticate to BigQuery, set up Application Default Credentials. For more information, seeSet up authentication for client libraries.

Limitations

Copying a table across regions is subject to the following limitations:

View current quota usage

You can view your current usage of query, load, extract, or copy jobs by running an INFORMATION_SCHEMA query to view metadata about the jobs ran over a specified time period. You can compare your current usage against the quota limit to determine your quota usage for a particular type of job. The following example query uses theINFORMATION_SCHEMA.JOBS view to list the number of query, load, extract, and copy jobs by project:

SELECT sum(case when job_type="QUERY" then 1 else 0 end) as QRY_CNT, sum(case when job_type="LOAD" then 1 else 0 end) as LOAD_CNT, sum(case when job_type="EXTRACT" then 1 else 0 end) as EXT_CNT, sum(case when job_type="COPY" then 1 else 0 end) as CPY_CNT FROM region-REGION_NAME.INFORMATION_SCHEMA.JOBS_BY_PROJECT WHERE date(creation_time)= CURRENT_DATE()

Maximum number of copy jobs per day per project quota errors

BigQuery returns this error when the number of copy jobs running in a project has exceeded the daily limit. To learn more about the limit for copy jobs per day, seeCopy jobs.

Error message

Your project exceeded quota for copies per project

Diagnosis

If you'd like to gather more data about where the copy jobs are coming from, you can try the following:

Resolution

Delete tables

You can delete a table in the following ways:

To delete all of the tables in the dataset,delete the dataset.

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

Deleting a table also deletes any permissions associated with this table. When you recreate a deleted table, you must also manually reconfigure any access permissionspreviously associated with it.

Required roles

To get the permissions that you need to delete a table, ask your administrator to grant you theData Editor (roles/bigquery.dataEditor) IAM role on the dataset. For more information about granting roles, see Manage access to projects, folders, and organizations.

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

Required permissions

The following permissions are required to delete a table:

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

Delete a table

To delete a table:

Console

  1. In the left pane, click Explorer:
    Highlighted button for the Explorer pane.
  2. In the Explorer pane, expand your project, click Datasets, and then select a dataset.
  3. Click Overview > Tables, and then select a table.
  4. In the details pane, click Delete.
  5. Type "delete" in the dialog, then click Delete to confirm.

SQL

Use theDROP TABLE statement. The following example deletes 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:
    DROP TABLE mydataset.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, aCloud 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. 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:
      To delete the mytable table from the mydataset dataset, enter the following command. The mydataset dataset is in your default project.
      bq rm -t mydataset.mytable
      To delete the mytable table from the mydataset dataset, enter the following command. The mydataset dataset is in the myotherprojectproject, not your default project.
      bq rm -t myotherproject:mydataset.mytable
      To delete the mytable table from the mydataset dataset, enter the following command. The mydataset dataset is in your default project. The command uses the -f shortcut to bypass confirmation.
      bq rm -f -t mydataset.mytable

API

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

C#

Before trying this sample, follow the C# setup instructions in theBigQuery quickstart using client libraries. For more information, see theBigQuery C# API reference documentation.

To authenticate to BigQuery, set up Application Default Credentials. For more information, seeSet up authentication for client libraries.

Go

Before trying this sample, follow the Go setup instructions in theBigQuery quickstart using client libraries. For more information, see theBigQuery Go API reference documentation.

To authenticate to BigQuery, set up Application Default Credentials. For more information, seeSet up authentication for client libraries.

Java

Before trying this sample, follow the Java setup instructions in theBigQuery quickstart using client libraries. For more information, see theBigQuery Java API reference documentation.

To authenticate to BigQuery, set up Application Default Credentials. For more information, seeSet up authentication for client libraries.

Node.js

Before trying this sample, follow the Node.js setup instructions in theBigQuery quickstart using client libraries. For more information, see theBigQuery Node.js API reference documentation.

To authenticate to BigQuery, set up Application Default Credentials. For more information, seeSet up authentication for client libraries.

PHP

Before trying this sample, follow the PHP setup instructions in theBigQuery quickstart using client libraries. For more information, see theBigQuery PHP API reference documentation.

To authenticate to BigQuery, set up Application Default Credentials. For more information, seeSet up authentication for client libraries.

Python

Before trying this sample, follow the Python setup instructions in theBigQuery quickstart using client libraries. For more information, see theBigQuery Python API reference documentation.

To authenticate to BigQuery, set up Application Default Credentials. For more information, seeSet up authentication for client libraries.

Ruby

Before trying this sample, follow the Ruby setup instructions in theBigQuery quickstart using client libraries. For more information, see theBigQuery Ruby API reference documentation.

To authenticate to BigQuery, set up Application Default Credentials. For more information, seeSet up authentication for client libraries.

Troubleshoot deleted tables

There are three main reasons for a deleted table: manual table deletion, expiration, and dataset deletion.

Table deletion

Check Cloud Audit Logs for a google.cloud.bigquery.v2.TableService.DeleteTable event. To view deleted tables, select one of the following options:

Console

  1. In the Google Cloud console, go to the Logs Explorer page.
    Go to Logs Explorer
  2. In the Filter section, use the following Cloud Logging Filter and Run Query,
resource.type="bigquery_dataset"  
protoPayload.methodName="google.cloud.bigquery.v2.TableService.DeleteTable"  
protoPayload.resourceName="projects/PROJECT_ID/datasets/DATASET_ID/tables/TABLE_ID"

bq

  1. In the Google Cloud console, activate Cloud Shell.
    Activate Cloud Shell
    At the bottom of the Google Cloud console, aCloud 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. Use the gcloud logging read command with the following filters:
    gcloud logging read '
    resource.type="bigquery_dataset"
    protoPayload.methodName="google.cloud.bigquery.v2.TableService.DeleteTable"
    protoPayload.resourceName=~"projects/PROJECT_ID/datasets/DATASET_ID/tables/TABLE_ID"
    '
    Replace the following:
    • PROJECT_ID: your project ID
    • DATASET_ID: the name of the dataset that contains the table
    • TABLE_ID: the name of the table that was deleted

Table expiration

Tables can be created with an expiration time. Once this time is reached, BigQuery automatically deletes the table. To view deleted tables, select one of the following options:

Console

  1. In the Google Cloud console, go to the Logs Explorer page.
    Go to Logs Explorer
  2. In the Filter section, use the following Cloud Logging Filter and click Run Query.
protoPayload.methodName="InternalTableExpired"  
  protoPayload.resourceName="projects/PROJECT_ID/datasets/DATASET_ID/tables/TABLE_ID"

bq

  1. In the Google Cloud console, activate Cloud Shell.
    Activate Cloud Shell
    At the bottom of the Google Cloud console, aCloud 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. Use the gcloud logging read command with the following filters:
    gcloud logging read '
    protoPayload.methodName="InternalTableExpired"
    protoPayload.resourceName=~"projects/PROJECT_ID/datasets/DATASET_ID/tables/TABLE_ID"
    '
    Replace the following:
    • PROJECT_ID: your project ID
    • DATASET_ID: the name of the dataset that contains the table
    • TABLE_ID: the name of the table that was deleted

You can query the INFORMATION_SCHEMA.TABLE_OPTIONS view to check the expiration_timestamp column for existing tables.

Dataset deletion

If the dataset containing the table was deleted, the table will also be deleted. To view deleted tables, select one of the following options:

Console

  1. In the Google Cloud console, go to the Logs Explorer page.
    Go to Logs Explorer
  2. In the Filter section, use the following Cloud Logging Filter and Run Query.
resource.type="bigquery_dataset"  
protoPayload.methodName="google.cloud.bigquery.v2.DatasetService.DeleteDataset"  
protoPayload.resourceName="projects/PROJECT_ID/datasets/DATASET_ID"

bq

  1. In the Google Cloud console, activate Cloud Shell.
    Activate Cloud Shell
    At the bottom of the Google Cloud console, aCloud 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. Use the gcloud logging read command with the following filters:
    gcloud logging read '
    resource.type="bigquery_dataset"
    protoPayload.methodName="google.cloud.bigquery.v2.DatasetService.DeleteDataset"
    protoPayload.resourceName=~"projects/PROJECT_ID/datasets/DATASET_ID"
    '
    Replace the following:
    • PROJECT_ID: your project ID
    • DATASET_ID: the name of the dataset that contains the table

Restore deleted tables

To learn how to restore or undelete deleted tables, seeRestore deleted tables.

Table security

To control access to tables in BigQuery, seeControl access to resources with IAM.

What's next

Except as otherwise noted, the content of this page is licensed under the Creative Commons Attribution 4.0 License, and code samples are licensed under the Apache 2.0 License. For details, see the Google Developers Site Policies. Java is a registered trademark of Oracle and/or its affiliates.

Last updated 2026-06-18 UTC.