TRUNCATE TABLE (SQL Analytics)TRUNCATE TABLE (SQL Analytics)

从表或分区中删除所有行。Removes all the rows from a table or partition(s). 表不能是视图,也不能是外部表或临时表。The table must not be a view or an external or temporary table. 若要同时截断多个分区,请在 partition_spec 中指定分区。In order to truncate multiple partitions at once, specify the partitions in partition_spec. 如果未指定 partition_spec,则删除表中的所有分区。If no partition_spec is specified, removes all partitions in the table.

语法Syntax

TRUNCATE TABLE table_identifier [ partition_spec ]

参数Parameters

  • table_identifiertable_identifier

    • [database_name.] table_name:表名,可选择使用数据库名称进行限定。[database_name.] table_name: A table name, optionally qualified with a database name.
    • delta.`<path-to-table>`:现有 Delta 表的位置。delta.`<path-to-table>`: The location of an existing Delta table.
  • partition_specpartition_spec

    分区键值对的可选逗号分隔列表。Optional comma-separated list of key-value pairs for partitions.

    语法: PARTITION ( partition_col_name = partition_col_val [ , ... ] )Syntax: PARTITION ( partition_col_name = partition_col_val [ , ... ] )

示例Examples

-- Create table Student with partition
CREATE TABLE Student (name STRING, rollno INT) PARTITIONED BY (age INT);

SELECT * FROM Student;
+----+------+---+
|name|rollno|age|
+----+------+---+
| ABC|     1| 10|
| DEF|     2| 10|
| XYZ|     3| 12|
+----+------+---+

-- Remove all rows from the table in the specified partition
TRUNCATE TABLE Student partition(age=10);

-- After truncate execution, records belonging to partition age=10 are removed
SELECT * FROM Student;
+----+------+---+
|name|rollno|age|
+----+------+---+
| XYZ|     3| 12|
+----+------+---+

-- Remove all rows from the table from all partitions
TRUNCATE TABLE Student;

SELECT * FROM Student;
+----+------+---+
|name|rollno|age|
+----+------+---+
+----+------+---+