Archive data

This topic describes how to archive data by submitting a data archiving job.

Background information

OceanBase Cloud supports archiving table data from a source database to a target database on a schedule, helping you address performance and operational issues caused by growing online data.

This topic uses creating a data archiving job as an example. It describes how to archive the employee table from the test1 database to the load_test 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 archived must contain a primary key.

Considerations

• Prerequisites:

  • Ensure that fields in the source table are compatible with the target. Data archiving does not handle field compatibility issues.

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

  • Second-level partitions are not supported for table schema synchronization in homogeneous archiving links. Heterogeneous databases do not support schema synchronization or automatic table creation.

• Supported archiving links:

  • OceanBase MySQL to OceanBase MySQL.

  • OceanBase Oracle to OceanBase Oracle.

  • MySQL to OceanBase MySQL.

• Archiving is not supported in the following cases:

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

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

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

  • Archiving is not supported if the archiving condition contains a LIMIT clause.

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

• Automatic table creation and table schema synchronization are not supported for the following archiving links:

  • MySQL to OceanBase MySQL.
  • OceanBase MySQL to MySQL.

Create a data archiving job

1. Log in to the SQL console, then open the SQL window.

2. In the SQL window, edit the following SQL statement to create the employee table.

   CREATE TABLE `employee` (
   `emp_no` int(120) NOT NULL COMMENT 'employee number',
    `birthday` date DEFAULT NULL COMMENT 'employee birthday',
    `name` varchar(120) DEFAULT NULL COMMENT 'employee name',
    `time` date NOT NULL COMMENT 'time' COMMENT 'create time',
    PRIMARY KEY (`time`)
   ) partition by range columns(time) (
    partition p2023_01
    values
     less than ('2023-01-01'),
     partition p2023_02
   values
     less than ('2023-02-01')
   )
   

3. Click Data Services > Data Lifecycle > Data Archiving. On the Data Lifecycle page, click Create Job > Data Archiving.

4. On the Create Data Archiving Job page, configure the following parameters.

   Parameter	Description
   Database Information	Source data source: Select the data source that contains the data to be archived. Source database: Select the database that contains the data to be archived. Target data source: Select the target data source for archived data. Target database: Select the target database for archived data.
   Archiving Scope	Partial Archiving: Archive selected tables in the source database based on filter 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 archived. Click More Settings > Configure to archive data by specified partition. Full Database Archiving: Archive all tables in the source database.
   Custom Variable	Optional. Define variables and set time offsets to filter rows that meet the conditions for archiving.
   Execution Mode	Select Immediate Execution, Scheduled Execution, or Periodic Execution to set how the job runs.
   Task Settings	Clean Up Archived Data in Source: Specifies whether to delete archived data from the source. Execution Timeout: If the job is not completed within the specified duration after it starts, scheduling is paused until the next run. Row Limit: Limits the total number of rows processed per second. Data Volume Limit: Limits the total data volume processed per second. Insert Strategy: Specifies whether to ignore or update duplicate data during archiving. 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. Target Table Schema Synchronization: Compares source and target table schemas before scheduling. Tables with inconsistent schemas are skipped if this option is not selected.
   Remarks	Optional. Enter a description of up to 200 characters in the Remarks field.

5. Click Preview SQL, preview the archiving SQL statements, specify a job name, and click Submit to create the data archiving job.

6. After the job is created, you can view it in the Data Archiving list.

View a data archiving job

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

2. In the data archiving 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. Click Details. On the Job Information tab of the job details panel, view the job type, source database, target database, variable configuration, archiving scope, execution mode, and other information.

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

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

Import data archiving jobs

You can import data archiving 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 archiving jobs of the migrated instance into OceanBase Cloud.

Step 1: Export data archiving 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 archiving 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 archiving configuration file that you downloaded to your local device.

References

• Clean up data