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

  • When you execute the TRUNCATE TABLE statement to clear the data of a table, the table schema is not deleted.
  • When database backup is in progress, the TRUNCATE TABLE statement cannot be executed. An error is returned if you execute this statement.
  • If you execute the TRUNCATE TABLE statement when your cluster has a BUILD task in the running state, an error is returned. You must wait for the BUILD task to complete and execute the statement again. For more information about how to query the state of a BUILD task, see View the state of a BUILD task.

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;