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 odc_test_data_2 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 job
Click Job > Data Cleanup > Create New in the SQL window.
Specify the following parameters.
ParameterDescriptionSource Database The database to which the table belongs. 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. 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 Cycle Execution, Execute Immediately, or Execute On Schedule. Job Settings - Data Retrieval through Full Table Scan: Supports full-table scan and conditional matching.
- Specify Task Duration: After the task starts, it will be suspended if it is not completed within the specified duration.
- 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.
- Partial Cleanup: Specifies the tables to be cleaned up in the database based on the cleanup conditions.
Click Pre-check in the lower part of the page. The system will pre-check the job and remind you of any errors in the job configuration.
Example:
After the pre-check is completed, click Next: Preview SQL in the lower part of the page.
On the preview SQL page, check the execution SQL statements, set the job name, and click OK.
You can view or delete the job in Job > Data Cleanup.
View a data cleanup job
Job information
In the data cleanup list on the Job page, click the name of the target job to view the job details. You can also click ··· in the Actions column to perform more operations on the job.
- Terminate: When the execution mode of a job is set to cycle execution or scheduled execution, you can terminate the job.
- Disable/Enable: When the execution mode of a job is set to cycle execution or scheduled execution, you can disable the job to temporarily stop its execution. After you enable the job again, it will be executed.
- Edit: When the status of a job is disabled, you can edit the job to update its content.
- Delete: When the status of a job is completed or terminated, you can delete the job.
- View: View the job details.
- Clone: Clone the job to quickly generate a new job with the same configuration.
- Share: Copy the job access address.
In the job details panel, the Basic Information tab is displayed by default. You can view information such as the job type, source and target databases, variable configurations, cleanup scope, and execution mode.
In the job details panel, click the Execution Records tab to view the job status and execution details.
In the job details panel, click the Operation Records tab to view the approval status and change history of the job.