Data archiving

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

Background information

ODC allows you to archive table data from the source database to another database on a scheduled basis. This way, the increase of online data does not affect the query performance and business operations.

The example in this topic shows you how to archive the employee and order tables from the odc_test_data_1 database to the odc_test_data_2 database in the same project.

Principles

Considerations

• Prerequisites:

  • The table to be archived must have a primary key. If the data source type is OceanBase MySQL compatible mode or MySQL, a unique index can also be archived.

  • The database user for archiving data must have the read/write permissions and access permissions to the internal views. For more information, see View user privileges, Overview of users and privileges, and Modify user privileges.

  • The source table columns must be compatible with the target database. The data archiving feature does not handle compatibility issues.

  • Table structure synchronization is not supported for homogeneous archiving links that involve subpartitioning. It is also not supported for heterogeneous databases, which means that you cannot synchronize schemas or create tables automatically.

• Supported archiving links:

  • Links from OceanBase MySQL compatible mode to OceanBase MySQL compatible mode databases

  • Links from Oracle databases to OceanBase Oracle compatible mode databases

  • Links from MySQL databases to MySQL databases

  • Links from Oracle databases to Oracle databases

  • Links from PostgreSQL databases to OceanBase MySQL compatible mode databases

  • Links from OceanBase MySQL compatible mode, OceanBase Oracle compatible mode, MySQL, Oracle, and PostgreSQL databases to object storage services (OSS, COS, OBS, and S3)

• Unsupported scenarios:

  • If the OceanBase MySQL compatible mode data source contains the XMLTYPE field type, archiving is not supported.

  • If the OceanBase Oracle compatible mode data source contains the JSON or XMLTYPE field type, archiving is not supported.

  • If the PostgreSQL data source defines tables that contain the array, composite, enumeration, geometric, XML, HSTORE, or full-text search type, archiving is not supported.

  • If the archiving condition contains the LIMIT clause, archiving is not supported.

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

• Supported automatic table creation links:

  • Links from OceanBase Oracle compatible mode to OceanBase Oracle compatible mode databases
  • Links from OceanBase MySQL compatible mode to OceanBase MySQL compatible mode databases
  • Links from MySQL databases to MySQL databases, except for MySQL 5.7 and earlier

• Supported schema synchronization links:

  • Links from OceanBase MySQL compatible mode to OceanBase MySQL compatible mode

Create a data archiving job

1. In the SQL window, execute the following SQL statements to create an employee table and an order 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')
   );
   
   CREATE TABLE `order` (
   `time` date NOT NULL,
    `parti_key` int(11),
    `name` varchar(120) DEFAULT NULL,
    PRIMARY KEY (`time`, parti_key)
   ) partition by range columns(time, parti_key)
   (partition p2023_01 values less than ('2023-01-01', 20230101),
   partition p2023_02 values less than ('2023-02-01', 20230201));
   

2. Right-click the name of the source database and select Job Scheduling > Data Archiving. You can also select Job > Data Archiving > Create New.

3. Configure the following parameters.

   Parameter	Description
   Source Database	The database to which the source table belongs. Note In ODC V4.2.2 and later, you can select a MySQL database to archive data from the MySQL database to the OceanBase database.
   Target Database	The database to which the source table is archived. Note In ODC V4.2.2 and later, you can select a MySQL database to archive data from the OceanBase database to the MySQL database.
   Archiving Scope	Partial archiving: Archive data from specific tables in the source database based on filter 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 defined in the variable configuration, and time is a column in the table to be archived. Note You can set filter conditions for associated tables. If you want to archive data from specific partitions, select the Specify Partition check box. Click +Add to add a table to be archived and archive the table to the target database. Click +Batch Add to add multiple tables to be archived and archive the tables to the target database. Archive all tables in the source database.
   Custom Variables	Optional. Define variables and set time offsets to archive rows that meet the conditions.
   Execution Mode	Select Cycle Execution, Execute Immediately, or Execute On Schedule to specify the task execution mode.
   Job Settings	Auto-create Missing Target Table Schema: If the target database does not contain the table to be archived, the table is automatically created based on the schema in the source database. Data Retrieval through Full Table Scan: Supports full-table scan and conditional matching. Clean Up Archived Data in Source: If you select this option, data in the source is cleaned up based on the settings. By default, the data is immediately cleaned up and not backed up. You can roll back the cleanup task. Data Insertion Strategy: When data archiving is executed, duplicate data is ignored or updated. Specify Task Duration: If the task is not completed after running for the specified duration, the scheduling is paused and resumed in the next schedule. Set Limit: Configure row throttling and data size throttling.

4. Click Pre-check at the bottom of the page. The system will pre-check the job and remind you of any errors in the job configuration.

5. After the pre-check is completed, click Next: Preview SQL at the bottom of the page.

6. In the Preview SQL interface, check the execution SQL statements, set the job name, and then click OK to confirm the archiving SQL statements.

7. After the job is generated, you can view or delete the job in the Job > Data Archiving list.

View a data archiving job

1. Access Job > Data Archiving. In the data archiving job list, 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: If the job execution mode is set to cycle execution or scheduled execution, you can terminate the job.
   • Disable/Enable: If the job execution mode is set to cycle 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 the job type, source database, target database, variable settings, archiving scope, and execution mode.

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

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

References

• Data cleanup