PolarDB:Archive data in CSV or ORC format

Limitations

• Supported engines: InnoDB and X-Engine.

• InnoDB tables must have a primary key.

• Tables with an In-Memory Column Index (IMCI) can only be archived in ORC format, not CSV.

• DDL and DML statements cannot run on the table during archiving.

• Archiving to a user-created OSS server is not supported.

• After archiving, the table is read-only and query performance may decrease. Test query performance after archiving to confirm it meets your requirements.

Parameters

Example

Archive table t in the oss_test database to OSS in CSV or ORC format.

1. Create an InnoDB table with a primary key.

   CREATE TABLE t(a int, b int, c int, primary key(a)) ENGINE = INNODB;

2. Insert data.

   INSERT INTO t VALUES (1,2,3);

3. Archive the table. CSV format:

   ALTER TABLE t ENGINE = CSV CONNECTION = 'default_oss_server';

   ORC format:

   ALTER TABLE t ENGINE = ORC STORAGE OSS;

4. After archiving completes, verify the result:

   • In the console: Log in to the PolarDB console. On the target cluster page, go to Settings and Management > Data Lifecycle > Data Archive (Cold Data).

   • With SQL: Query the archived table directly — access patterns remain unchanged. ``sql SELECT * FROM t; ``

Prerequisites

• Cluster version: PolarDB for MySQL 8.0.2 revision 8.0.2.2.25 or later.

• Set the cluster parameter partition_level_mdl_enabled to ON to enable partition-level metadata lock (MDL). For instructions, see Set cluster and node parameters.

• Set the cluster parameter loose_use_oss_meta to ON. For instructions, see Set cluster and node parameters.

Limitations

• The destination OSS table supports CSV format only.

• After archiving a partition, the source table must retain at least one InnoDB partition.

• Archived data loses its partition information and cannot be directly restored. Use INSERT INTO ... SELECT to restore the data if needed.

• Subpartitions cannot be archived individually. Archive the entire first-level partition instead.

• Supported first-level partition types:

  First-level partition type	Second-level partition type	Supported
  LIST	Any	Yes
  RANGE	Any	Yes
  KEY	Any	Yes
  HASH	Any	No
  LIST DEFAULT	Any	Yes, except the DEFAULT partition

Syntax

CALL dbms_dlm.archive_partition2table(
  'source_db',
  'source_tb',
  'source_part',
  'archive_db',
  'archive_table',
  'oss_file_filter'
);

Parameters

Behavior based on destination table state:

• Destination table does not exist: The table is created automatically. An OSS_FILE_FILTER is created on the specified columns, with the primary key and partition key added automatically.

• Destination table exists: Column names and types must match the source table. If they differ, align the schemas using DDL statements before archiving. See DDL for cold data. If the destination table already has an OSS_FILE_FILTER with a different definition, the existing one is used.

• Destination table does not exist but a same-name file exists in OSS: The operation fails with:

  ERROR 8181 (HY000): [Data Lifecycle Management] errmsg: Target file for archived table exists on oss, please remove it first, or use flag 'FORCE' to overwrite on existing files.

  If the residual OSS files are no longer needed, delete them and retry:

  -- Delete the residual OSS files.
  CALL dbms_oss.delete_table_file('test', 'sales_history');
  -- Query OK, 0 rows affected (0.76 sec)
  
  -- Retry the archiving operation.
  CALL dbms_dlm.archive_partition2table('test', 'sales', 'p0', 'test', 'sales_history', 'id');
  -- Query OK, 0 rows affected (4.24 sec)

Example

Archive the p0 partition of a partitioned sales table to a new OSS table named sales_history.

1. Create an InnoDB partitioned table and insert data.

   DROP TABLE IF EXISTS `sales`;
   
   -- Create a RANGE-partitioned table.
   CREATE TABLE `sales` (
     `id` int DEFAULT NULL,
     `name` varchar(20) DEFAULT NULL,
     `order_time` datetime NOT NULL,
     primary key (order_time)
   ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
   PARTITION BY RANGE COLUMNS(order_time) INTERVAL(month, 1)
   (PARTITION p0 VALUES LESS THAN ('2022-01-01') ENGINE = InnoDB,
    PARTITION p1 VALUES LESS THAN ('2023-01-01 00:00:00') ENGINE = InnoDB,
    PARTITION p2 VALUES LESS THAN ('2024-01-01 00:00:00') ENGINE = InnoDB);
   
   -- Insert 1,000 rows using a batch insert procedure.
   DROP PROCEDURE IF EXISTS proc_batch_insert;
   delimiter $$
   CREATE PROCEDURE proc_batch_insert(IN begin INT, IN end INT, IN name VARCHAR(20))
     BEGIN
     SET @insert_stmt = concat('INSERT INTO ', name, ' VALUES(? , ?, ?);');
     PREPARE stmt from @insert_stmt;
     WHILE begin <= end DO
       SET @ID1 = begin;
       SET @NAME = CONCAT(begin+begin*281313, '@stiven');
       SET @TIME = from_days(begin + 738368);
       EXECUTE stmt using @ID1, @NAME, @TIME;
       SET begin = begin + 1;
       END WHILE;
     END;
   $$
   delimiter ;
   
   CALL proc_batch_insert(1, 1000, 'sales');

2. Check the current schema of the sales table.

   SHOW CREATE TABLE sales;

   Expected output:

   *************************** 1. row ***************************
          Table: sales
   Create Table: CREATE TABLE `sales` (
     `id` int(11) DEFAULT NULL,
     `name` varchar(20) DEFAULT NULL,
     `order_time` datetime NOT NULL,
     PRIMARY KEY (`order_time`)
   ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
   /*!50500 PARTITION BY RANGE  COLUMNS(order_time) */ /*!99990 800020200 INTERVAL(MONTH, 1) */
   /*!50500 (PARTITION p0 VALUES LESS THAN ('2022-01-01') ENGINE = InnoDB,
    PARTITION p1 VALUES LESS THAN ('2023-01-01 00:00:00') ENGINE = InnoDB,
    PARTITION p2 VALUES LESS THAN ('2024-01-01 00:00:00') ENGINE = InnoDB,
    ...) */
   1 row in set (0.03 sec)

3. Archive the p0 partition to the OSS table sales_history, with an OSS_FILE_FILTER on the id column.

   CALL dbms_dlm.archive_partition2table('test', 'sales', 'p0', 'test', 'sales_history', 'id');
   -- Query OK, 0 rows affected (1.86 sec)

4. Verify the schema of the created sales_history table.

   SHOW CREATE TABLE sales_history;

   Expected output:

   *************************** 1. row ***************************
          Table: sales_history
   Create Table: CREATE TABLE `sales_history` (
     `id` int(11) DEFAULT NULL,
     `name` varchar(20) DEFAULT NULL,
     `order_time` datetime DEFAULT NULL,
     PRIMARY KEY (`order_time`)
   ) /*!99990 800020213 STORAGE OSS */ ENGINE=CSV DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
   /*!99990 800020204 NULL_MARKER='NULL' */ /*!99990 800020223 OSS META=1 */
   /*!99990 800020224 OSS_FILE_FILTER='id,order_time' */
   1 row in set (0.00 sec)

5. Query the archived data.

   Enable OSS_FILE_FILTER query acceleration to improve query performance on the OSS table.

   SELECT * FROM sales_history WHERE id = 100;

   Expected output:

   +------+-----------------+---------------------+
   | id   | name            | order_time          |
   +------+-----------------+---------------------+
   |  100 | 28131400@stiven | 2021-11-09 00:00:00 |
   +------+-----------------+---------------------+
   1 row in set (0.24 sec)

Prerequisites

• Cluster version: PolarDB for MySQL 8.0.2 revision 8.0.2.2.14 or later.

Limitations

• Only InnoDB engine partitioned tables are supported.

• At least one InnoDB partition must remain in the table. The last InnoDB partition cannot be archived.

• DDL operations cannot be performed on a hybrid partitioned table. See Create a hybrid partitioned table for full details.

• Archived partition data is read-only. Write operations are not supported on archived partitions.

• Subpartitions cannot be archived individually.

• The DEFAULT partition of a LIST DEFAULT HASH partitioned table cannot be archived.

• HASH and KEY partitioned tables cannot be archived.

• An entire partitioned table cannot be archived manually. Archive partitions individually.

Syntax

-- Archive as CSV:
ALTER TABLE table_name CHANGE PARTITION part_name ENGINE = CSV;

-- Archive as ORC:
ALTER TABLE table_name CHANGE PARTITION part_name ENGINE = ORC;

If a file with the same name already exists in OSS, the operation fails with: Target file for archived table exists on oss. To overwrite the existing OSS file, add FORCE to the statement: On clusters running revision 8.0.2.2.29 or later, FORCE STORAGE OSS is also supported:

ALTER TABLE table_name CHANGE PARTITION part_name ENGINE = CSV/ORC FORCE;

ALTER TABLE table_name CHANGE PARTITION part_name ENGINE = CSV FORCE STORAGE OSS;

Parameters

Example

Archive the p1 and p2 partitions of table t to OSS in CSV format.

1. Create an InnoDB RANGE-partitioned table.

   CREATE TABLE t(a int, b int, c int, primary key(a))
   PARTITION BY RANGE(a)
   (PARTITION p1 VALUES LESS THAN (100),
    PARTITION p2 VALUES LESS THAN (200),
    PARTITION p3 VALUES LESS THAN MAXVALUE
   );

2. Insert data.

   INSERT INTO t VALUES (1,1,1), (10,10,10), (100,100,100),
                        (150,150,150), (200,200,200), (1000,1000,1000);

3. Archive the p1 and p2 partitions. CSV format:

   ALTER TABLE t CHANGE PARTITION p1 ENGINE = CSV;
   ALTER TABLE t CHANGE PARTITION p2 ENGINE = CSV;

   ORC format:

   ALTER TABLE t CHANGE PARTITION p1 ENGINE = ORC;
   ALTER TABLE t CHANGE PARTITION p2 ENGINE = ORC;

4. After archiving completes, verify the result:

   • In the console: Log in to the PolarDB console. On the target cluster page, go to Settings and Management > Data Lifecycle > Data Archive (Cold Data).

   • With SQL: Query the hybrid partitioned table. See Querying hybrid partitions for details.

Prerequisites

• Manual archiving in CSV or ORC format only.

• Cluster version: PolarDB for MySQL 8.0.1 revision 8.0.1.1.47 or later, or 8.0.2 revision 8.0.2.2.27 or later. If your cluster does not meet this requirement, upgrade the minor version.

Syntax

Add ENCRYPTION="Y" to the archiving statement:

ALTER TABLE t1 ENGINE = CSV ENCRYPTION="Y" STORAGE OSS;

Restore a standard archived table

Restoring converts the OSS table back to a read-write InnoDB table in PolarStore. The archived data in OSS is deleted after restoration.

An archived OSS table is read-only. INSERT, UPDATE, and DELETE operations fail with: 1036 - Table 't1' is read only. Restore the table to PolarStore before making any modifications.

ALTER TABLE table_name ENGINE = engine_name;

ALTER TABLE `oss_test`.`t` ENGINE = InnoDB;

-- Format 1:
ALTER TABLE t ENGINE = CSV CONNECTION = 'default_oss_server';

-- Format 2:
ALTER TABLE t ENGINE = CSV STORAGE OSS;

Restore an archived partition

Restoring an archived partition moves its data from OSS back to PolarStore. The cold data in OSS is deleted after restoration.

ALTER TABLE table_name REORGANIZE PARTITION part_name INTO (partition_definition);

ALTER TABLE t REORGANIZE PARTITION p1 INTO (PARTITION p1 VALUES LESS THAN (100));