ApsaraDB for OceanBase:Clean up data

Background

After you archive data in the source database to the target database, OceanBase Developer Center (ODC) allows you to delete the data from the source database to improve the query performance and reduce the online storage costs.

The example in this topic describes how to create a data cleanup task in ODC to clean up the employee table in the test2 database.

Considerations

• Pay attention to the following rules:

  • The table to be cleaned up must have a primary key. If the table belongs to an OceanBase MySQL-compatible data source, it can be cleaned up if it has a unique index.

  • The database user used for cleanup must have the read/write permissions and the permission to access internal views. For information about permission configuration, see View user privileges, Overview, and Modify user privileges.

• Data sources supported for data cleanup are as follows:

  • OceanBase MySQL-compatible data sources

  • OceanBase Oracle-compatible data sources

• Data cleanup is not supported in the following cases:

  • The source table in the OceanBase MySQL-compatible data source contains columns of the XMLType data type.

  • The source table in the OceanBase Oracle-compatible data source does not have a primary key.

  • The source table in the OceanBase Oracle-compatible data source contains columns of the JSON or XMLType data type.

  • The cleanup condition contains a LIMIT clause.

  • The table contains a foreign key.

Create a data cleanup task

1. In the left-side navigation pane of the SQL development window, choose Data Cleanup > Create New on the Tickets tab.

   

2. On the Create Data Cleanup Ticket page, configure the following parameters.

   

   Parameter	Description
   Source Database	The database to which the table belongs.
   Cleanup Scope	Partially Cleaned: specifies to clean up only tables that meet filtering conditions in the database. You can configure filtering conditions by using constants or referencing variables defined in Custom Variable. For example, in time<'${create_time}', create_time is the name of a variable configured in Custom Variable and time is a column in the table to be cleaned up. Note You can set filtering conditions for associated tables. You can select Specify Partition and specify the partitions to be cleaned up. Whole Library Cleaning: specifies to clean up all tables in the database.
   Custom Variable	Optional. You can define variables and set time offsets to filter rows to be cleaned up.
   Execution Mode	The execution mode of the task. Valid values: Execute Now, Scheduled Execution, and Periodic Execution.
   Task Settings	Need Verification Before Cleanup: If you select this option, the system checks whether the data of the table to be cleaned up is consistent with that of the target table before the cleanup. The system cleans up the table if yes, and does not proceed if otherwise. Specify Task Duration: You can select this option and specify a duration for the task. If the task is not completed within the specified duration, it will be suspended and wait for the next scheduling. Search strategy: Full-table scan and conditional matching are supported. Set Limit: You can specify Row Limit and Data Size Limit. Row Limit defines the maximum number of rows operated per second. Data Size Limit defines the maximum size of data operated per second. Use Primary Key for Cleanup: specifies whether to use the primary key for cleanup.
   Description	Optional. Additional information about the task, which cannot exceed 200 characters in length.

3. Click Create, preview the SQL statement for data cleanup, and click OK.

   

4. After the task is generated, choose Tickets > Data Cleanup to view the task.

   

View a data cleanup task

Task information

1. In the data cleanup task list on the Tickets tab, click View in the Actions column of a task.

   

2. In the ticket details panel, click the Basic Information tab and view information such as the task type, source database, target database, variable configurations, cleanup scope, and execution mode.

   

3. 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 ticket details panel, click the Execution Records tab and view the task status and execution details.

Operating records

In the ticket details panel, click the Operating Records tab and view the approval status and change history of the task.

Import data cleanup tasks

You can migrate instances as well as their data cleanup tasks from ApsaraDB for OceanBase to OceanBase Cloud.

Step 1: Export data cleanup tasks from ApsaraDB for OceanBase

1. Log on to the ApsaraDB for OceanBase console. Click Instances in the left-side navigation pane.

2. Click Cut to Cloud Market in the Actions column of the target instance.

   

3. After that, click Processing data research and development tasks in the Actions column of the instance.

   

4. On the Processing data research and development tasks page, click View and Export All to export scheduled tasks to your local computer.

   

Step 2: Import data cleanup tasks to OceanBase Cloud

1. Log on the OceanBase Cloud console and choose Data Services > Data Lifecycle. On the Data Lifecycle page, click ... and select Import Job.

   

2. Upload the data cleanup configuration file downloaded to your local computer earlier to the import job.

   

References

• Archive data