Clears data from a specified table.

Clear data from a non-partitioned table

Clears data from a specified non-partitioned table. For more information about how to clear data from one or more partitions in a partitioned table, see Clear data from a partition.

  • Syntax
    truncate table <table_name>;
  • Parameters

    table_name: required. The name of the non-partitioned table whose data you want to clear.

Clear data from a partition

Clears data from a specified partition in a partitioned table.

MaxCompute allows you to clear the data in partitions that meet a specified filter condition. If you want to delete one or more partitions that meet a filter condition at a time, you can use an expression to specify the condition, use the condition to match partitions, and then clear data from the matched partitions.

  • Syntax
    • The filter condition is not specified.
      truncate table <table_name> partition <pt_spec>[, partition <pt_spec>....];
    • The filter condition is specified.
      truncate table <table_name> partition <partition_filtercondition>;
  • Parameters
    • table_name: required. The name of the partitioned table of which you want to clear partition data.
    • pt_spec: required. The partition from which you want to clear data. The value of this parameter is in the (partition_col1 = partition_col_value1, partition_col2 = partition_col_value2, ...) format. partition_col indicates the column name, and partition_col_value indicates the column value. The names of partition key columns are not case sensitive, but their values are case sensitive.
    • partition_filtercondition: the filter condition. This parameter is required when you specify the filter condition. This parameter is not case-sensitive. The parameter is in the following format:
      partition_filtercondition
    : partition (<partition_col> <relational_operators> <partition_col_value>)
    | partition (scalar(<partition_col>) <relational_operators> <partition_col_value>)
    | partition (<partition_filtercondition1> AND|OR <partition_filtercondition2>)
    | partition (NOT <partition_filtercondition>)
    | partition (<partition_filtercondition1>)[,partition (<partition_filtercondition2>), ...]
      • partition_col: the name of a partition key column.
      • relational_operators: the relational operator. For more information, see Operators.
      • partition_col_value: a value in the partition key column. The value may be a comparison value or regular expression. The data type of this value must be the same as the data type of the partition key column.
      • scalar(): the scalar function. The scalar function generates a scalar based on the input value, processes the values in the column specified by partition_col, and uses relational_operators to compare the processed values with partition_col_value.
      • The filter conditions that are used to delete partitions support the logical operators NOT, AND, and OR. You can use PARTITION (<NOT partition_filtercondition>) to obtain the complementary set of the filter conditions that you specified. You can use PARTITION (<partition_filtercondition1 ANDOR partition_filtercondition2>) to obtain the condition that is used to match the partitions you want to delete.
      • Multiple PARTITION (<partition_filtercondition>) clauses are supported. If these clauses are separated by commas (,), the logical relationship between the clauses is OR. The filter condition is obtained based on the OR logical relationship and used to match the partitions that you want to delete.
  • Examples
    • The filter condition is not specified.
      -- Clear the data from a partition of the sale_detail table. The partition stores the sales record in the China (Hangzhou) region in December 2013. 
truncate table sale_detail partition(sale_date='201312',region='hangzhou'); 
-- Clear the data from two partitions of the sale_detail table. The partitions store the sales records in the China (Hangzhou) and China (Shanghai) regions in December 2013. 
truncate table sale_detail partition(sale_date='201312',region='hangzhou'),  partition(sale_date='201312',region='shanghai');
    • The filter condition is specified.
      -- Clear the data from multiple partitions of the sale_detail table. The partitions store the sales records whose values in the sale_date column start with 2013 in the China (Hangzhou) region. 
truncate table sale_detail partition(sale_date like '2013%' and region='hangzhou');

Related statements

  • CREATE TABLE: Creates a non-partitioned table, a partitioned table, an external table, or a clustered table.
  • ALTER TABLE: Alters information about a table.
  • DROP TABLE: Drops a partitioned table or a non-partitioned table.
  • DESC TABLE/VIEW: Views the information about MaxCompute internal tables, views, materialized views, external tables, clustered tables, or transactional tables.
  • SHOW: views SQL DDL statements that are used to create tables, or queries information of all tables and views in a project or information of all partitions in a table.