This topic describes how to submit a data cleanup ticket to clean up data.
Background information
In ODC, you can archive data from the source database to the target database and then delete the data from the source database to improve query performance and reduce costs of online storage.
The example in this topic shows how to clean up data in the employee table in the test2 database in the ODC console.
Note
All data used in this example is for reference only. You can replace them with actual data.
Considerations
Prerequisites:
The table to be cleaned up must have a primary key. If the data source type is MySQL or OceanBase MySQL compatible mode, a unique index can also be used.
The database user used for cleanup must have the read/write permissions and the access permissions on the internal views. For more information, see View user privileges, Overview of users and privileges, and Modify user privileges.
Data sources that support data cleanup include:
OceanBase MySQL compatible mode data sources
OceanBase Oracle compatible mode data sources
MySQL data sources
Oracle data sources
PostgreSQL data sources
Data cleanup is not supported in the following scenarios:
For an OceanBase MySQL compatible mode data source, if the table contains the XMLTYPE field type, data cleanup is not supported.
For OceanBase Oracle compatible mode, Oracle, and PostgreSQL data sources, if the table does not have a primary key (PRIMARY KEY), data cleanup is not supported.
For an OceanBase Oracle compatible mode data source, if the table contains the JSON or XMLTYPE field type, data cleanup is not supported.
If the cleanup condition contains the LIMIT statement, data cleanup is not supported.
Data cleanup is not supported for tables that contain foreign keys.
Create a data cleanup task
In the left-side navigation pane, choose Ticket > Data Cleanup > Create Data Cleanup Ticket.
On the Create Data Cleanup Ticket page, specify the following parameters.
Parameter Description Source Database The database to which the table belongs. Cleanup Scope - Partial Cleanup: Specifies the tables to be cleaned up in the database based on the cleanup conditions.
- You can set filter conditions by using constants or variables defined in the variable configuration. For example,
time<'${create_time}'. In this example,create_timeis the name of a variable in the variable configuration, andtimeis a field in the table to be cleaned up.Note
You can configure join table conditions in filter conditions.
- After you select Specify Partition, you can specify partitions to clean up data.
- You can set filter conditions by using constants or variables defined in the variable configuration. For example,
- Database Cleanup: Specifies to clean up all tables in the database.
Custom Variables Optional. You can define variables and set a time offset to filter the rows that meet the specified conditions for cleanup. Execution Mode You can choose Execute Immediately, Execute On Schedule, or Cycle Execution. Task Settings - Need Verification Before Cleanup: If you select the check box, the system will check whether the data of the table to be cleaned up is consistent with that of the target table before the cleanup. If the data is consistent, the cleanup operation is performed. Otherwise, it is not performed.
- Specify Task Duration: After the task starts, it will be suspended if it is not completed within the specified duration.
- Data Retrieval through Full Table Scan: If you use full-table scan for data search, the process will be more stable but the performance may be affected.
- Set Limit: Row throttling: limits the total number of rows operated per second; Data size throttling: limits the total size of data operated per second.
Description You can enter a description of up to 200 characters. - Partial Cleanup: Specifies the tables to be cleaned up in the database based on the cleanup conditions.
Click Create. On the page that appears, click OK.
You can view or delete the task in Ticket > Data Cleanup.
View a data cleanup task
Task information
In the data cleanup list on the Ticket page, click the View icon in the Actions column.
In the task details panel that appears, click the Basic Information tab to view information such as the task type, source and destination databases, variable configurations, cleanup scope, and execution mode.
Click Initiate Again in the lower-right corner. The task information is prepopulated in the Create Data Cleanup Ticket panel, allowing you to quickly create a data cleanup task.
Execution records
In the task details panel, click the Execution Records tab to view the task status and execution details.
Operation records
In the task details panel, click the Operation Records tab to view the approval status and change history of the task.
