PolarDB:Archive data in CSV or ORC format

Syntax

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

Parameters

Considerations

• If the destination OSS table does not exist, it is automatically created. To accelerate queries, an oss_file_filter is also automatically created on the columns specified by the oss_file_filter parameter. The primary key and partition key are also automatically added to the oss_file_filter.

• If the target OSS table exists, you must compare the column names or column type definitions of the two tables. The data can be archived only if the definitions are consistent. Otherwise, an error occurs. You can use DDL statements to make the two table definitions consistent. For more information, see Cold Data DDL. Additionally, if an oss_file_filter is defined on the target table and its definition is inconsistent with the one in the call dbms_dlm.archive_partition2table command, the oss_file_filter definition on the target table takes precedence.

• If the destination OSS table does not exist but a file with the same name exists in OSS, an error message is reported when you perform the archiving operation. The error message is as follows:

  mysql> CALL dbms_dlm.archive_partition2table('test', 'sales', 'p0', 'test', 'sales_history', 'id');
  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 you confirm that the residual files in OSS are not needed, you can use a stored procedure to delete the data in OSS and then perform the archiving operation.

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

• The destination OSS table supports only cold data in CSV format.

• After a partition is archived, the table must contain at least one InnoDB partition.

• Archived data loses its partition information and cannot be directly restored to a partition. However, you can restore the data into a table by using an INSERT ... SELECT statement.

• You cannot archive individual second-level partitions. You can only archive an entire first-level partition, which includes all of its second-level partitions.

• The following table lists the partition types supported by the partition function of PolarDB for MySQL.

  First-level partition	Second-level partition	OSS archiving support
  HASH	Any type	Archiving a HASH first-level partition to an OSS external table is not supported.
  LIST	Any type	Supported.
  RANGE	Any type	Supported.
  KEY	Any type	Supported.
  LIST DEFAULT	Any type	Archiving the DEFAULT partition is not supported.

Example

1. Create an InnoDB partitioned table and insert data.

   DROP TABLE IF EXISTS `sales`;
   -- Create a 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);
   
   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. Archive the p0 partition of the partitioned table to a new OSS table.

   1. Run the following command to view the schema of the sales table.

      -- View the status of the current InnoDB table.
      mysql> SHOW CREATE TABLE sales;

      The following result is returned:

      *************************** 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,
       PARTITION _p20240201000000 VALUES LESS THAN ('2024-02-01 00:00:00') ENGINE = InnoDB,
       PARTITION _p20240301000000 VALUES LESS THAN ('2024-03-01 00:00:00') ENGINE = InnoDB,
       PARTITION _p20240401000000 VALUES LESS THAN ('2024-04-01 00:00:00') ENGINE = InnoDB,
       PARTITION _p20240501000000 VALUES LESS THAN ('2024-05-01 00:00:00') ENGINE = InnoDB) */
      1 row in set (0.03 sec)

   2. Run the following command to archive the p0 partition to the OSS table sales_history.

      -- Archive the p0 partition to the OSS table sales_history and create an OSS_FILE_FILTER on the id column.
      mysql> CALL dbms_dlm.archive_partition2table('test', 'sales', 'p0', 'test', 'sales_history', 'id');
      Query OK, 0 rows affected (1.86 sec)

   3. Run the following command to view the schema of the sales_history table.

      SHOW CREATE TABLE sales_history;

      The following result is returned:

      *************************** 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)

3. Query the new OSS table.

   Note

   You can enable the OSS_FILE_FILTER query acceleration feature to accelerate queries.

   mysql> explain SELECT * FROM sales_history WHERE id = 100;
   +----+-------------+---------------+------------+------+---------------+------+---------+------+------+----------+-------------------------------------------------------------------------------+
   | id | select_type | table         | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                                                                         |
   +----+-------------+---------------+------------+------+---------------+------+---------+------+------+----------+-------------------------------------------------------------------------------+
   |  1 | SIMPLE      | sales_history | NULL       | ALL  | NULL          | NULL | NULL    | NULL |  152 |    10.00 | Using where; With pushed engine condition (`test`.`sales_history`.`id` = 100) |
   +----+-------------+---------------+------------+------+---------------+------+---------+------+------+----------+-------------------------------------------------------------------------------+
   1 row in set, 1 warning (0.00 sec)
   
   mysql> SELECT * FROM sales_history WHERE id = 100;
   +------+-----------------+---------------------+
   | id   | name            | order_time          |
   +------+-----------------+---------------------+
   |  100 | 28131400@stiven | 2021-11-09 00:00:00 |
   +------+-----------------+---------------------+
   1 row in set (0.24 sec)

Syntax

• Archive as a file in CSV format:

  ALTER TABLE table_name CHANGE PARTITION part_name ENGINE = CSV;

• Archive as a file in ORC format:

  ALTER TABLE table_name CHANGE PARTITION part_name ENGINE = ORC;

Parameters

Considerations

• You can use the cold data archiving feature only for InnoDB partitioned tables.

• The table becomes a hybrid partitioned table after archiving. A hybrid partitioned table can contain ORC or CSV partitions. You cannot perform online DDL operations on a hybrid partitioned table, except for adding or dropping columns and indexes. For usage considerations, see Create a hybrid partition.

• You cannot modify the data of an archived partition.

• You cannot use cold data archiving for second-level partitions in a partitioned table.

• You cannot use cold data archiving for the DEFAULT partition of a LIST DEFAULT HASH partitioned table.

• You cannot use cold data archiving for HASH or KEY partitioned tables.

• You cannot manually archive an entire partitioned table.

• When you archive data in a partition, if a file with the same name exists in OSS, the operation fails and an error message similar to the following is reported:

  Target file for archived table exists on oss.

  You must run the following command to overwrite the existing file in OSS. Replace table_name and part_name with your actual values.

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

Example

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

1. Create an InnoDB table named t in the database.

   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 into table t.

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

3. Run the following commands to archive the data in the p1 and p2 partitions to the OSS engine.

   • Archive in CSV format:

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

   • Archive in ORC format:

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

4. After the archiving is complete, you can log on to the PolarDB console to view information about the databases and tables archived in OSS, or use an SQL statement to query data in the hybrid partitioned table:

   • To view information about databases and tables archived in OSS: Log on to the PolarDB console. In the left-side navigation pane of the target cluster, choose Settings and Management > Data Lifecycle > Data Archive (Cold Data) to view the information.

   • To query data in a hybrid partitioned table, see Querying hybrid partitions.

Restore a standard archived table

To modify cold data archived in OSS, use the ALTER ENGINE syntax to restore the data to PolarStore. Restoring data to PolarStore also deletes the corresponding data from OSS. After you modify the data, you can archive the modified table to OSS again.

ALTER TABLE table_name ENGINE[=]engine_name;

1036 - Table 't1' is read only

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

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

ALTER TABLE t ENGINE = CSV STORAGE OSS;

Restore an archived partition

If you need to restore data from an archived partitioned table to PolarStore, you can use the ALTER statement to restore the data from OSS to PolarStore. After the data is restored, the cold data in OSS is also deleted.

ALTER TABLE table_name REORGANIZE PARTITION part_name INTO (partition_definition);

ALTER TABLE t REORGANIZE PARTITION p1 INTO(PARTITION p1 values less than(100));