You can execute the TRUNCATE TABLE statement to clear the data of a table or specified partitions in a table.

Syntax

  • Clear the data of a table.
    TRUNCATE TABLE db_name.table_name;
  • Clear the data of specified partitions in a table.
    TRUNCATE TABLE db_name.table_name PARTITION partition_name;

    The data type of partition names is BIGINT. You can execute the following SQL statement to obtain the names of all partitions in a table: 

    select partition_name from information_schema.partitions where table_name = 'your_table_name' order by partition_name desc limit 100;

Precautions

Note
  • When the database is backed up, TRUNCATE TABLE cannot be executed. An error is returned if you execute the TRUNCATE TABLE statement.
  • When you execute the TRUNCATE TABLE statement to clear the data of a table, the table schema is not deleted.

Examples

  • Clear the data of the customer table.
    TRUNCATE TABLE adb_demo.customer;
  • Clear the data of specified partitions in the customer table.
    TRUNCATE TABLE adb_demo.customer partition 20170103,20170104,20170108;