BigQuery TRUNCATE TABLE Command Made Easy

BigQuery is scalable, allowing organizations to scale their storage up and down based on their changing needs.

When using BigQuery, you will want to delete data from some of your tables, probably to free up storage.

There are different ways to do this- some of which are free while others where you will incur a cost.

In this article, we will be discussing how to delete data from a BigQuery table using the BigQuery TRUNCATE TABLE command. 

Prerequisites 

This is what you need for this article:

• A Google BigQuery account. 

What is Google BigQuery?

• Google BigQuery is a Highly Scalable & Serverless Data Warehouse with a built-in Query Engine. It was developed by Google, hence, it uses the processing power of Google’s infrastructure.
• The Query Engine can run SQL queries on terabytes of data within seconds, and petabytes within minutes. 

BigQuery TRUNCATE TABLE Command

• Initially, BigQuery did not support anything other than the SELECT statement.
• However, a lot of improvements have been made to this Data Warehousing Solution to make it support additional statements. 
• BigQuery now supports the BigQuery TRUNCATE TABLE command, which is good for those coming from an on-premise background.
• The statement is used to delete data from a BigQuery table. When used, the BigQuery TRUNCATE TABLE command removes all data from a table but leaves the table’s metadata intact, even the table schema, labels, and description. 
• When you use BigQuery’s DELETE DML statement to delete data from a table, you will incur a scan cost.
• The good news is that the BigQuery TRUNCATE TABLE statement falls under the category of BigQuery free operations, so you don’t incur any costs when using this statement. Thus, if you need to reduce scan costs, use the BigQuery TRUNCATE TABLE command over DELETE when you want to delete all table contents. 

Pro Tip : The BigQuery TRUNCATE TABLE statement removes all rows from a table but leaves the table metadata intact, including the table schema, description, and labels. Let’s try integration tips

BigQuery TRUNCATE TABLE Command Syntax

The BigQuery TRUNCATE TABLE command takes the following syntax:

TRUNCATE TABLE [[project_name.]dataset_name.]table_name

The parameters in the above syntax are described below:

• project_name: This is the name of the project that contains the table. The project that runs this DDL (Data Definition Language) query is used as the default. 
• dataset_name: This is the name of the dataset that contains the table. 
• table_name: This is the name of the table that is to be truncated. 

Note: TRUNCATE TABLE BigQuery command does not allow you to truncate models, views, materialized views, and external tables. However, the command allows you to apply Quotas and LIMITs to your statements. 

BigQuery TRUNCATE TABLE Example Queries

In this section, we will be giving practical examples of how to use the BigQuery TRUNCATE TABLE command. First, let’s create the table we will use to demonstrate this. 

We will create a table named customer that stores data about the details of customers. We will use the SQL `CREATE TABLE` command for this as shown below:

CREATE TABLE `project.dataset.customer` ( 
    id INT64,
    name STRING,
    country STRING,
    ts TIMESTAMP
);

The table will store details like customer id, name, country, and the time they made a purchase. 

Now that the table is ready, let us insert some data into it. We will use the SQL INSERT statement for this as shown below:

INSERT `project.dataset.customer` (id, name, country, ts)
VALUES(2347, 'John Doe', 'Scotland', TIMESTAMP("2021-12-01"));

The above statement will insert one row into the table. Let us insert 3 other rows into the table:

INSERT `project.dataset.customer` (customer_id, name, location, ts)
VALUES
      (23348, 'Mercy Michaels',  'Netherlands',  TIMESTAMP("2021-12-01")),
      (23349, 'Joel Pogba',  'USA',  TIMESTAMP("2021-12-03")),
      (23350, 'Kate Drogba',  'Nigeria',  TIMESTAMP("2021-12-05"));

We now have 4 rows in the table. 

To delete the table data, we can use the BigQuery TRUNCATE TABLE statement. This is demonstrated below:

TRUNCATE TABLE `project.dataset.customer`

The command will delete all rows from the table.

FAQ on BigQuery TRUNCATE TABLE Command

Conclusion

• The BigQuery TRUNCATE TABLE command is an essential operation in BigQuery to reduce query scan costs.
• We recommend using TRUNCATE over DELETE whenever you need to delete the entire contents of a table.