BigQuery Delete Table & Drop Table Commands

In this article, you will gain information about undergoing BigQuery Delete Table.

You will also gain a holistic understanding of Google BigQuery, its key features, managing BigQuery Tables and the different methods of deleting BigQuery Tables.

Read along to find out in-depth information about undergoing GBQ Delete Table.

What is Google BigQuery?

Google BigQuery is a Cloud-based Data Warehouse that provides a Big Data Analytic Web Service for processing petabytes of data. It is intended for analyzing data on a large scale. It consists of two distinct components: Storage and Query Processing.

Managing Tables in BigQuery

One can manage your BigQuery tables in the following ways:

• Update table properties:
  • Expiration time
  • Description
  • Schema definition
  • Labels
• Rename a table
• Copy a table
• Delete a table
• Restore a deleted table

Methods to undergo BigQuery Delete Table

• Method 1 : Using the Cloud Console.
• Method 2 : Using a Data Definition Language (DDL) DROP TABLE statement.
• Method 3 : Using the bq command-line tool bq rm command.
• Method 4 : Calling the tables.delete API method.
• Method 5 : Using the client libraries.

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

When you remove a table, all of its data is likewise deleted. Define the default table expiration for the dataset or set the expiration time when you create the table to automatically destroy tables after a specific period of time.

Required Permissions

To delete a table, you need the following IAM permissions:

• bigquery.tables.delete
• bigquery.tables.get

Each of the predefined IAM roles listed below includes the permissions required to delete a BigQuery table:

• roles/bigquery.dataEditor
• roles/bigquery.dataOwner
• roles/bigquery.admin

You can also delete tables from datasets that you create if you have the bigquery.datasets.create permission.

The following ways to showcase BigQuery Delete Table methods in this article are as follows:

A) Cloud Console

The steps followed to undergo BigQuery delete table are as follows:

• Step 1: Go to the Explorer panel. Expand your project and dataset. Now, choose the table.
• Step 2: Click the “Delete table” option in the details panel.
• Step 3: In the popup, type “delete“.
• Step 4: Click the “Delete” button to confirm.

B) SQL

Using standard SQL query syntax, DDL statements enable you to delete tables.

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

• Step 1: Click the “Compose new query” button.
• Step 2: Fill in the Query editor field with your DDL statement. 

 DROP TABLE mydataset.mytable

• Step 3: Click the “Run” button.

C) bq rm Command

To delete a table, you can use the bq rm command with the –table flag (or the -t shortcut). When deleting a table using the bq command-line tool, you must confirm the action. To skip confirmation, you can use the –force flag (or -f shortcut).

If the table is in a dataset in a project other than your default project, you can add the project ID to the dataset name, as shown below:

project id:dataset

bq rm 
-f 
-t 
<project_id value>:<dataset name>.<table name>

In the above syntax given, you can 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 the mytable table from the mydataset dataset. mydataset is a dataset in your default project.

bq rm -t mydataset.mytable

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

bq rm -t myotherproject:mydataset.mytable

Enter the following command to delete the mytable table from the mydataset dataset. mydataset is a dataset in your default project. To avoid confirmation, the command use the -f shortcut.

bq rm -f -t mydataset.mytable

D) API Method

You can call the tables.delete API method. And even specify the table name to delete using the tableId parameter.

E) Client Library: C#

To undergo BigQuery delete table in C# you can refer the following code snippet.

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.");
    }
}

F) Client Library: Go

To undergo BigQuery delete table in Go you can refer the following code snippet.

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
}

G) Client Library: Java

To undergo BigQuery delete table in Java you can refer the following code snippet.

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

H) Client Lirary: Node.js

To undergo BigQuery delete table in Node.js you can refer the following code snippet.

// 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.`);
}

I) Client Library: PHP

To undergo BigQuery delete table in PHP you can refer the following code snippet.

 use GoogleCloudBigQueryBigQueryClient;

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

J) Client Library: Python

To undergo BigQuery delete table in Python you can refer the following code snippet.

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

K) Client Library: Ruby

To undergo BigQuery delete table in Ruby you can refer the following code snippet.

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

Conclusion

1. This article also provided information on Google BigQuery, its key features, managing BigQuery Tables, and the different methods of deleting BigQuery Tables in detail.