归档为CSV或ORC格式

归档普通表

冷数据归档执行的是表归档操作，执行冷数据归档操作后的表称为归档表（只读），归档表的引擎为OSS引擎，归档表的数据文件存储在OSS上。执行冷数据归档操作后，原来本地表在PolarStore上占用的空间将被释放。

语法

ALTER TABLE table_name ENGINE = ORC STORAGE OSS;

参数说明

注意事项

• 支持对InnoDB引擎和X-Engine引擎上的表使用冷数据归档功能。

• 冷数据归档过程中不支持对表进行修改（DDL和DML）。

• 冷数据归档功能不支持将数据文件归档到用户自建的OSS Server中。

• 对InnoDB引擎中的表使用冷数据归档功能时，执行冷数据归档操作的表中必须有主键。

• 冷数据归档完成后，OSS上的归档表只读，且查询性能较差。您需要提前测试数据归档后是否能满足您的查询性能要求。

• 若表中存在列存索引（IMCI），则不支持归档为CSV格式，仅支持归档为ORC格式。

示例

将表t中的数据以CSV或ORC格式归档至OSS。

1. 在数据库oss_test中创建InnoDB表t。

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

2. 在表t中插入数据。

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

3. 通过ALTER命令归档冷数据。

   • 归档为CSV格式：

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

   • 归档为ORC格式：

     ALTER TABLE t ENGINE = ORC STORAGE OSS;

4. 归档完成后，您可以登录PolarDB控制台查看归档在OSS上的库表信息，或通过SQL语句查看归档表上的数据：

   • 查看归档在OSS上的库表信息：登录PolarDB控制台。在目标集群的配置与管理 > 冷数据归档路径下，查看归档在OSS上的库表信息。

   • 查看归档表上的数据：通过SQL语句查看归档表上的数据，而不需要修改表的访问方式。例如：

     SELECT * FROM t;

分区表归档至OSS外表

语法

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

参数说明

注意事项

• 如果归档目标OSS表不存在，会自动创建目标OSS表，且目标表上会自动在 oss_file_filter指定的列上创建oss_file_filter，以提供查询加速功能。同时 oss_file_filter中会自动加入主键和分区键，帮助提升查询速度。

• 如果归档目标OSS表存在，则需要比较两个表中列名或者列类型定义是否一致。如果一致，则可以进行归档；如果不一致，则会出错。您可以使用DDL语句让这两个表定义保持一致，具体请参考相关文档冷数据DDL。同时，如果目标表上有oss_file_filter，且定义与call dbms_dlm.archive_partition2table中定义的不一致，则以目标表上的oss_file_filter为准。

• 如果目标表OSS不存在，但OSS表上有同名文件，则进行归档操作会出现报错信息。报错信息如下：

  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.

  此时如果确认OSS上的残留文件不需要，可以通过存储过程删除OSS上的数据，再执行归档操作。

  -- 删除OSS数据
  mysql> CALL dbms_oss.delete_table_file('test', 'sales_history');
  
  Query OK, 0 rows affected (0.76 sec)
  
  -- 执行归档操作
  mysql> CALL dbms_dlm.archive_partition2table('test', 'sales', 'p0', 'test', 'sales_history', 'id');
  
  Query OK, 0 rows affected (4.24 sec)

• 目标表OSS仅支持CSV格式冷数据。

• 分区表归档后至少还需要包含一个InnoDB分区。

• 归档后的数据丢失了分区信息，无法直接取回。但是可以通过insert select取回。

• 不支持单独归档二级分区，可以把整个一级分区下的二级分区全部归档。

• 下表列出了PolarDB MySQL版分区函数支持的分区类型。

  一级分区	二级分区	是否支持归档至OSS外表
  HASH	任意类型	不支持一级分区HASH类型归档至OSS外表。
  LIST	任意类型	支持。
  RANGE	任意类型	支持。
  KEY	任意类型	支持。
  LIST DEFAULT	任意类型	不支持归档DEFAULT分区。

示例

1. 创建InnoDB分区表并插入数据。

   DROP TABLE IF EXISTS `sales`;
   -- 创建分区表;
   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. 把分区表的p0分区归档至新的执行OSS表。

   1. 执行如下命令，查看sales表结构信息。

      -- 查看当前InnoDB表的状态;
      mysql> SHOW CREATE TABLE sales;

      执行结果如下：

      *************************** 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. 执行如下命令，将p0分区归档至OSS表sales_history。

      -- 把p0分区归档至OSS表 sales_history，并在id 列上建立OSS_FILE_FILTER;
      mysql> CALL dbms_dlm.archive_partition2table('test', 'sales', 'p0', 'test', 'sales_history', 'id');
      Query OK, 0 rows affected (1.86 sec)

   3. 执行如下命令，查看sales_history表结构信息。

      SHOW CREATE TABLE sales_history;

      执行结果如下：

      *************************** 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. 在新的OSS表上进行查询。

   说明

   您可以启用OSS_FILE_FILTER查询加速功能，以完成加速查询。

   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)

归档分区表

归档分区表是对表的分区进行归档，归档后的表为混合分区表，归档后分区的数据文件保存在OSS上。执行完归档操作后，分区在PolarStore上占用的空间会自动释放。

语法

• 归档为CSV格式的文件：

  ALTER TABLE table_name CHANGE PARTITION part_name ENGINE = CSV;

• 归档为ORC格式的文件：

  ALTER TABLE table_name CHANGE PARTITION part_name ENGINE = ORC;

参数说明

注意事项

• 仅支持对InnoDB引擎上的分区表使用冷数据归档功能。

• 对分区表执行分区归档操作时，至少保留一个分区在InnoDB引擎上，即无法对最后一个InnoDB引擎上的分区执行冷数据归档操作。

• 归档后的表为混合分区表，不支持对混合分区表执行DDL操作。使用时的注意事项详情请参见创建混合分区。

• 暂不支持对归档后的分区数据进行修改。

• 暂不支持对分区表中的二级分区执行冷数据归档操作。

• 暂不支持对LIST DEFAULT HASH分区表的DEFAULT分区执行冷数据归档操作。

• 暂不支持对HASH或KEY类型的分区表执行冷数据归档操作。

• 暂不支持对整张分区表执行手动归档冷数据操作。

• 归档分区中的数据时，若OSS上存在同名文件，则会执行失败并报类似如下错误信息：

  Target file for archived table exists on oss.

  您需要执行以下命令来覆盖OSS上已存在的文件。执行命令时请根据实际情况替换table_name和part_name。

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

示例

将表t中p1和p2分区上的数据以CSV格式归档至OSS。

1. 在数据库中创建InnoDB表t。

   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. 在表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. 执行以下命令，将p1和p2分区上的数据归档至OSS引擎。

   • 归档为CSV格式：

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

   • 归档为ORC格式：

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

4. 归档完成后，您可以登录PolarDB控制台查看归档在OSS上的库表信息，或通过SQL语句查询混合分区表中的数据：

   • 查看归档在OSS上的库表信息：登录PolarDB控制台。在目标集群的配置与管理 > 冷数据归档路径下，查看归档在OSS上的库表信息。

   • 查询混合分区表中的数据：操作详情请参见查询混合分区。

归档数据支持TDE加密

为满足您对数据安全性的需求，冷数据归档到OSS的文件支持指定TDE加密。基本原理是在归档操作中选择需要进行TDE加密的文件，由OSS服务器负责执行加密操作，详细加密操作请参见数据加密。经过加密处理后，您可以直接通过PolarDB MySQL版使用SQL语句查询归档数据。整个加密和解密过程均由后台完成，对您来说是透明的，无需额外操作。

语法

手动归档操作中，可以通过增加ENCRYPTION="Y"语法来指定开启TDE加密。

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

将OSS数据导回至PolarStore

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;

ALTER TABLE table_name REORGANIZE PARTITION part_name INTO (partition_definition);

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

删除OSS上对应的文件

当您将OSS上的表删除或导回至PolarStore后，OSS上的文件不会同步删除。确定数据不再使用后，您可以使用如下语法删除OSS上对应的文件：

CALL dbms_oss.delete_table_file('database_name', 'table_name');

由于删除OSS上对应文件的操作是异步执行的，故需要等待集群中的所有节点都不再依赖OSS文件后才可完全删除，且流量较大时存在一定时延。因此，如果上述命令执行失败并返回错误信息OSS files are still in use时，您可以等待一段时间后再重新执行该命令。