Clean up data

This topic describes how to clean up data by submitting a data cleanup job.

Background information

OceanBase Cloud supports deleting data from the source database after it is archived to the target database, improving database query performance and reducing online storage costs.

This topic uses creating a data cleanup job as an example. It describes how to clean up the employee table in the test1 database in OceanBase Cloud.

Prerequisites

• You have the database account and password of the current tenant to log in to the SQL console.

• The table to be cleaned up must contain a primary key.

Considerations

• Prerequisites:

  • CPU and memory burst protection is not supported for MySQL data sources.

• Supported data sources for data cleanup:

  • OceanBase MySQL data sources.

  • OceanBase Oracle data sources.

  • MySQL data sources.

• Data cleanup is not supported in the following cases:

  • For OceanBase MySQL and MySQL data sources, cleanup is not supported if a table does not contain a PRIMARY KEY or a unique non-null index.

  • For OceanBase Oracle data sources, cleanup is not supported if a table does not contain a PRIMARY KEY.

  • For OceanBase Oracle data sources, cleanup is not supported if a table contains JSON or XMLTYPE columns.

  • Cleanup is not supported if the cleanup condition contains a LIMIT clause.

  • Cleanup is not supported if a table contains foreign keys.

Create a data cleanup job

1. Click Data Services > Data Lifecycle > Data Cleanup. On the Data Lifecycle page, click Create Job > Data Cleanup.

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

   Parameter	Description
   Database Information	Select the data source and database. You can also verify the status of the target data source and database.
   Cleanup Scope	Partial Cleanup: Clean up selected tables in the database based on cleanup conditions. You can configure filter conditions by using constants or variables defined in custom variables. For example, in time<'${create_time}', create_time is a variable defined in custom variables, and time is a field in the table to be cleaned up. Click More Settings > Configure to clean up data by specified partition. Full Database Cleanup: Clean up all tables in the database.
   Custom Variable	Optional. Define variables and set time offsets to filter rows that meet the conditions for cleanup.
   Execution Mode	Select Immediate Execution, Scheduled Execution, or Periodic Execution to set how the job runs.
   Task Settings	Execution Timeout: The job expires after the timeout is reached. Data Retrieval Strategy: Specifies how target rows are retrieved. Full table scan offers stable performance and is suitable when the target data accounts for a large proportion of the table. Condition matching avoids empty scans, runs faster, and is suitable when the target data accounts for a small proportion of the table. Row Limit: Limits the total number of rows processed per second. Data Volume Limit: Limits the total data volume processed per second. Use Primary Key for Cleanup: Specifies whether to use the primary key for cleanup.
   Remarks	Optional. Enter a description of up to 200 characters in the Remarks field.

3. Click Preview SQL, preview the cleanup SQL statements, and click Submit to create the data cleanup job.

4. After the job is created, you can view it in the Data Cleanup list.

View a data cleanup job

1. Go to Data Services > Data Lifecycle > Data Cleanup to view the data cleanup job list and basic cleanup records.

2. In the data cleanup job list, click the name of a job to view its details. You can also click ··· in the Actions column for more operations.

   • Terminate: If the job runs periodically or on a schedule, you can terminate it.
   • Disable/Enable: If the job runs periodically or on a schedule, you can disable it to pause execution temporarily. After you enable it again, the job resumes.
   • Edit: If the job is disabled, you can edit it to update its configuration.
   • Delete: If the job is completed or terminated, you can delete it.
   • Details: View job details.
   • Initiate Again: Copy the job configuration to quickly open the create job page with the same settings.

3. On the Job Information tab of the job details panel, view the job type, database name, variable configuration, cleanup scope, and other information.

4. On the Execution Records tab of the job details panel, view the job status and execution details.

5. On the Operation Records tab of the job details panel, view the job status and records.

Import data cleanup jobs

You can import data cleanup jobs from other platforms into OceanBase Cloud. For example, after you migrate an instance from ApsaraDB for OceanBase to OceanBase Cloud, you can import the data cleanup jobs of the migrated instance into OceanBase Cloud.

Step 1: Export data cleanup jobs from another platform

1. In the left-side navigation pane of the ApsaraDB for OceanBase console, click Instances.

2. Under Actions for the target instance, click Cut to Cloud Market.

3. After the instance is switched, under Actions for the instance, click Process Data Development Tasks.

4. On the Process Data Development Tasks page, click View and Export All to export scheduled tasks to your local device.

Step 2: Import data cleanup jobs to OceanBase Cloud

1. Log in to the OceanBase Cloud console, click Data Services > Data Lifecycle, and on the Data Lifecycle page, click ... > Import Job.

2. Upload the data cleanup configuration file that you downloaded to your local device.

References

• Archive data