Data cleanup

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.

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

1. Click Jobs > Data Cleanup > Create New in the SQL window.

2. Specify the following parameters.

   Parameter	Description
   Source 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_time is the name of a variable in the variable configuration, and time is 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. Database Cleanup: Specifies to clean up all tables in the database.
   Custom Variable	Optional. You can define variables and set a time offset to filter the rows that meet the specified conditions for cleanup.
   Execution Mode	Select Periodic Execution, Immediate Execution, or Scheduled Execution.
   Throttling Strategy	Configure throttling conditions. CPU Usage: When the CPU usage of OceanBase Database exceeds this value, the program will automatically pause. Memory Usage: The memory usage is calculated as MEMSTORE_USED * 100 / MEMSTORE_LIMIT. If this value is not specified, the program will automatically pause when the memory usage exceeds 1.05 times the dump memory limit. Row Limit: Limits the total number of rows operated per second to this value. Data Size Limit: Limits the total size of data operated per second to this value.
   Job Settings	Data Retrieval through Full Table Scan: Supports full-table scan and conditional matching. Modify Session-level Cluster ID Parameter during Cleanup: You can select this option if you have selected Clean Up Archived Data in Source. If the source is OceanBase Database, you can specify the value of the cluster_id parameter at the session level to help downstream tools identify the cleanup records. Make sure to avoid duplication with values already used by the tenant. Specify Task Duration: After the task starts, it will be suspended if it is not completed within the specified duration.

3. 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.

4. After the pre-check is completed, click Next: Preview SQL in the lower part of the page.

5. On the preview SQL page, check the execution SQL statements, set the job name, and click OK.

6. You can view or delete the job in Jobs > Data Cleanup.

View a data cleanup job

Job information

1. Go to Jobs > Data Cleanup, click the name of the target job in the list to view the job details. You can also click ··· in the Actions column to perform more operations on the job.

   • Terminate: If the job execution mode is set to periodic execution or scheduled execution, you can terminate the job. You can also select the target jobs and click Batch Terminate at the top to terminate multiple jobs.
   • Disable/Enable: If the job execution mode is set to periodic execution or scheduled execution, you can disable the job to temporarily stop its execution. The job will resume execution after it is enabled.
   • Edit: If the job status is disabled, you can edit the job to update its content.
   • Delete: If the job status 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.

2. 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.

3. In the job details panel, click the Execution Records tab to view the job status and execution records.

4. In the job details panel, click the Operation Records tab to view the approval status and change history of the job.

Batch import and export

Batch export

1. Go to Jobs > Data Cleanup.

2. Select the target tasks and click Batch Actions > Batch Export.

3. Confirm the export content and click Export to complete the batch export.

4. After the export is completed, the export result is displayed on the right side of the page. You can view the key and view the exported file locally.

   Notice

   Keep the key properly. It is required when you import the exported file.

Batch import

1. Go to Jobs > DataCleanup.

2. Click the ∨ icon next to Create New and select Import Data Cleanup.

3. Select the file to import, enter the file key, select the target project, and click Next: Import.

4. Select the tasks to import and the target database, and click Import after you select I acknowledge the relevant business risks..

5. After the tasks are imported, you can view them in the list.

References

• Data archiving