Help Center/ Data Lake Insight/ SQL Syntax Reference (ME-Abu Dhabi Region)/ Spark SQL Syntax Reference/ Backing Up and Restoring Data of Multiple Versions/ Deleting Multiversion Backup Data
Updated on 2023-03-06 GMT+08:00
View PDF
Share

Deleting Multiversion Backup Data

Function

The retention period of multiversion backup data takes effect each time the insert overwrite or truncate statement is executed. If neither statement is executed for the table, multiversion backup data out of the retention period will not be automatically deleted. You can run the SQL commands described in this section to manually delete multiversion backup data.

Syntax

Delete multiversion backup data out of the retention period. 
clear history for table [db_name.]table_name older_than 'timestamp';

Keyword

  • clear history for table: Used to delete multiversion backup data
  • older_than: Used to specify the time range for deleting multiversion backup data

Parameter

Table 1 Parameter description

Parameter

Description

db_name

Database name, which consists of letters, digits, and underscores (_). The value cannot contain only digits or start with a digit or underscore (_).

table_name

Table name

Timestamp

Multiversion backup data generated before the timestamp will be deleted. Timestamp format: yyyy-MM-dd HH:mm:ss

Precautions

  • Currently, the multiversion function supports only OBS tables created using the Hive syntax. For details about the syntax for creating a table, see Creating an OBS Table Using the Hive Syntax.
  • This statement does not delete the backup data of the current version.

Example

Delete the multiversion backup data generated before 2021-09-25 23:59:59 in the dliTable table. When the multiversion backup data is generated, a timestamp is generated. 
clear history for table dliTable older_than '2021-09-25 23:59:59';