PolarDB:CSV 形式でのパーティションテーブルのアーカイブ

DLM ポリシーの作成

次の例では、sales という名前のパーティションテーブルを作成します。このテーブルは order_time 列をパーティションキーとして使用します。テーブルには INTERVAL ポリシーと DLM ポリシーが設定されています。

• INTERVAL ポリシー：挿入されたデータが既存のパーティション範囲外にある場合、1 年間隔で新しいパーティションが自動的に作成されます。

• DLM ポリシー：テーブルは 3 つのパーティションのみを保持するように定義されています。パーティション数が 3 を超えると、DLM ポリシーがトリガーされ、次のいずれかの操作が実行されます。

  • OSS 外部テーブル sales_history が存在しない場合、sales_history という名前の新しい OSS 外部テーブルが作成され、コールドデータが sales_history テーブルにアーカイブされます。

  • sales_history 外部テーブルが存在し、かつ sales_history テーブルが内蔵 OSS 上にある場合、コールドデータは直接 sales_history 外部テーブルにアーカイブされます。

1. DLM ポリシーを含む 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(YEAR, 1)
   (PARTITION p20200101000000 VALUES LESS THAN ('2020-01-01 00:00:00') ENGINE = InnoDB,
    PARTITION p20210101000000 VALUES LESS THAN ('2021-01-01 00:00:00') ENGINE = InnoDB,
    PARTITION p20220101000000 VALUES LESS THAN ('2022-01-01 00:00:00') ENGINE = InnoDB)
   DLM ADD POLICY test_policy TIER TO TABLE ENGINE=CSV STORAGE=OSS READ ONLY
   STORAGE TABLE_NAME = 'sales_history' EXTRA_INFO '{"oss_file_filter":"id,name:bloom"}' ON (PARTITIONS OVER 3);

   このテーブルの DLM ポリシー名は test_policy です。パーティション数が 3 を超えると、ポリシーによりソーステーブルのコールドデータが CSV 形式で OSS にアーカイブされます。結果として生成されるアーカイブテーブル名は sales_history で、読み取り専用です。OSS アーカイブテーブルが存在しない場合、システムが自動的に作成し、id 列および name 列に OSS_FILE_FILTER を追加します。

2. 現在のテーブルの DLM ポリシーは、システムテーブル mysql.dlm_policies に保存されています。このテーブルを照会することで、DLM ポリシーの詳細を確認できます。mysql.dlm_policies テーブルの詳細については、「テーブル構造の説明」をご参照ください。mysql.dlm_policies テーブルの構造を表示します。

   mysql> SELECT * FROM mysql.dlm_policies\G

   出力例：

   *************************** 1. row ***************************
                      Id: 3
            Table_schema: test
              Table_name: sales
             Policy_name: test_policy
             Policy_type: TABLE
            Archive_type: PARTITION COUNT
            Storage_mode: READ ONLY
          Storage_engine: CSV
           Storage_media: OSS
     Storage_schema_name: test
      Storage_table_name: sales_history
         Data_compressed: OFF
    Compressed_algorithm: NULL
                 Enabled: ENABLED
         Priority_number: 10300
   Tier_partition_number: 3
          Tier_condition: NULL
              Extra_info: {"oss_file_filter": "id,name:bloom,order_time"}
                 Comment: NULL
   1 row in set (0.03 sec)      

   現在、sales テーブルには 3 つのパーティションがあるため、データはアーカイブされていません。

3. sales パーティションテーブルにテストデータ 3,000 行を挿入します。これにより、データが定義されたパーティション範囲を超え、INTERVAL ポリシーがトリガーされて新しいパーティションが自動的に作成されます。

   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 + 737600);
   EXECUTE stmt using @ID1, @NAME, @TIME;
   SET begin = begin + 1;
   END WHILE;
   END;
   $$
   delimiter ;
   CALL proc_batch_insert(1, 3000, 'sales');

4. INTERVAL ポリシーがトリガーされ、sales テーブルに新しいパーティションが追加されます。テーブル構造は次のようになります。

   mysql> SHOW CREATE TABLE sales\G

   出力例：

   *************************** 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(YEAR, 1) */
   /*!50500 (PARTITION p20200101000000 VALUES LESS THAN ('2020-01-01 00:00:00') ENGINE = InnoDB, 
   PARTITION p20210101000000 VALUES LESS THAN ('2021-01-01 00:00:00') ENGINE = InnoDB, 
   PARTITION p20220101000000 VALUES LESS THAN ('2022-01-01 00:00:00') ENGINE = InnoDB, 
   PARTITION _p20230101000000 VALUES LESS THAN ('2023-01-01 00:00:00') ENGINE = InnoDB, 
   PARTITION _p20240101000000 VALUES LESS THAN ('2024-01-01 00:00:00') ENGINE = InnoDB, 
   PARTITION _p20250101000000 VALUES LESS THAN ('2025-01-01 00:00:00') ENGINE = InnoDB, 
   PARTITION _p20260101000000 VALUES LESS THAN ('2026-01-01 00:00:00') ENGINE = InnoDB, 
   PARTITION _p20270101000000 VALUES LESS THAN ('2027-01-01 00:00:00') ENGINE = InnoDB, 
   PARTITION _p20280101000000 VALUES LESS THAN ('2028-01-01 00:00:00') ENGINE = InnoDB) */
   1 row in set (0.03 sec)

   新しいパーティションにより、パーティション総数が 3 を超えました。これにより DLM ポリシーの条件が満たされ、データがアーカイブされる準備が整いました。

DLM ポリシーの作成

次の例では、sales という名前のパーティションテーブルを作成します。このテーブルは order_time 列をパーティションキーとして使用します。テーブルには INTERVAL ポリシーと DLM ポリシーが設定されています。

• INTERVAL ポリシー：挿入されたデータが既存のパーティション範囲外にある場合、1 年間隔で新しいパーティションが自動的に作成されます。

• DLM ポリシー：テーブルは 3 つのパーティションのみを保持するように定義されています。パーティション数が 3 を超えると、DLM ポリシーがトリガーされ、古いパーティションが直接 OSS にアーカイブされます。

1. 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(YEAR, 1)
   (PARTITION p20200101000000 VALUES LESS THAN ('2020-01-01 00:00:00') ENGINE = InnoDB,
    PARTITION p20210101000000 VALUES LESS THAN ('2021-01-01 00:00:00') ENGINE = InnoDB,
    PARTITION p20220101000000 VALUES LESS THAN ('2022-01-01 00:00:00') ENGINE = InnoDB)
   DLM ADD POLICY policy_part2part TIER TO PARTITION ENGINE=CSV STORAGE=OSS READ ONLY ON (PARTITIONS OVER 3);

   このテーブルの DLM ポリシー名は policy_part2part です。パーティション数が 3 を超えると、古いパーティションが OSS にアーカイブされます。

2. mysql.dlm_policies テーブルで DLM ポリシーを表示します。

   SELECT * FROM mysql.dlm_policies\G

   出力例：

   *************************** 1. row ***************************
                      Id: 2
            Table_schema: test
              Table_name: sales
             Policy_name: policy_part2part
             Policy_type: PARTITION
            Archive_type: PARTITION COUNT
            Storage_mode: READ ONLY
          Storage_engine: CSV
           Storage_media: OSS
     Storage_schema_name: NULL
      Storage_table_name: NULL
         Data_compressed: OFF
    Compressed_algorithm: NULL
                 Enabled: ENABLED
         Priority_number: 10300
   Tier_partition_number: 3
          Tier_condition: NULL
              Extra_info: null
                 Comment: NULL
   1 row in set (0.03 sec)

3. proc_batch_insert ストアドプロシージャを使用して、sales パーティションテーブルにテストデータを挿入します。この操作により INTERVAL ポリシーがトリガーされ、新しいパーティションが自動的に作成されます。

   CALL proc_batch_insert(1, 3000, 'sales');

   次の結果は、データが正常に挿入されたことを示しています。

   Query OK, 1 row affected, 1 warning (0.99 sec)

4. sales テーブル構造を表示するには、次のコマンドを実行します。

   SHOW CREATE TABLE sales \G

   出力例：

   *************************** 1. row ***************************
          Table: sales
   Create Table: CREATE TABLE `sales` (
     `id` int(11) DEFAULT NULL,
     `name` varchar(20) DEFAULT NULL,
     `order_time` datetime DEFAULT 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(YEAR, 1) */
   /*!50500 (PARTITION p20200101000000 VALUES LESS THAN ('2020-01-01 00:00:00') ENGINE = InnoDB,
    PARTITION p20210101000000 VALUES LESS THAN ('2021-01-01 00:00:00') ENGINE = InnoDB,
    PARTITION p20220101000000 VALUES LESS THAN ('2022-01-01 00:00:00') ENGINE = InnoDB,
    PARTITION _p20230101000000 VALUES LESS THAN ('2023-01-01 00:00:00') ENGINE = InnoDB,
    PARTITION _p20240101000000 VALUES LESS THAN ('2024-01-01 00:00:00') ENGINE = InnoDB,
    PARTITION _p20250101000000 VALUES LESS THAN ('2025-01-01 00:00:00') ENGINE = InnoDB,
    PARTITION _p20260101000000 VALUES LESS THAN ('2026-01-01 00:00:00') ENGINE = InnoDB,
    PARTITION _p20270101000000 VALUES LESS THAN ('2027-01-01 00:00:00') ENGINE = InnoDB,
    PARTITION _p20280101000000 VALUES LESS THAN ('2028-01-01 00:00:00') ENGINE = InnoDB) */
   1 row in set (0.03 sec)

DLM ポリシーの実行

1. DLM ポリシーを実行するには、次のコマンドを実行します。

   CALL dbms_dlm.execute_all_dlm_policies();

2. mysql.dlm_progress テーブルを照会して、DLM 実行履歴を確認します。

   SELECT * FROM mysql.dlm_progress \G

   出力例：

   *************************** 1. row ***************************
                     Id: 4
           Table_schema: test
             Table_name: sales
            Policy_name: policy_part2part
            Policy_type: PARTITION
         Archive_option: PARTITIONS OVER 3
         Storage_engine: CSV
          Storage_media: OSS
        Data_compressed: OFF
   Compressed_algorithm: NULL
     Archive_partitions: p20200101000000, p20210101000000, p20220101000000, _p20230101000000, _p20240101000000, _p20250101000000
          Archive_stage: ARCHIVE_COMPLETE
     Archive_percentage: 100
     Archived_file_info: null
             Start_time: 2023-09-11 18:04:39
               End_time: 2023-09-11 18:04:40
             Extra_info: null
   1 row in set (0.02 sec)

3. sales テーブル構造を表示するには、次のコマンドを実行します。

   SHOW CREATE TABLE sales \G

   出力例：

   *************************** 1. row ***************************
          Table: sales
   Create Table: CREATE TABLE `sales` (
     `id` int(11) DEFAULT NULL,
     `name` varchar(20) DEFAULT NULL,
     `order_time` datetime DEFAULT NULL,
      PRIMARY KEY (`order_time`)
   ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci CONNECTION='default_oss_server'
   /*!99990 800020205 PARTITION BY RANGE  COLUMNS(order_time) */ /*!99990 800020200 INTERVAL(YEAR, 1) */
   /*!99990 800020205 (PARTITION p20200101000000 VALUES LESS THAN ('2020-01-01 00:00:00') ENGINE = CSV,
    PARTITION p20210101000000 VALUES LESS THAN ('2021-01-01 00:00:00') ENGINE = CSV,
    PARTITION p20220101000000 VALUES LESS THAN ('2022-01-01 00:00:00') ENGINE = CSV,
    PARTITION _p20230101000000 VALUES LESS THAN ('2023-01-01 00:00:00') ENGINE = CSV,
    PARTITION _p20240101000000 VALUES LESS THAN ('2024-01-01 00:00:00') ENGINE = CSV,
    PARTITION _p20250101000000 VALUES LESS THAN ('2025-01-01 00:00:00') ENGINE = CSV,
    PARTITION _p20260101000000 VALUES LESS THAN ('2026-01-01 00:00:00') ENGINE = InnoDB,
    PARTITION _p20270101000000 VALUES LESS THAN ('2027-01-01 00:00:00') ENGINE = InnoDB,
    PARTITION _p20280101000000 VALUES LESS THAN ('2028-01-01 00:00:00') ENGINE = InnoDB) */
   1 row in set (0.03 sec)

   出力から、p20200101000000、p20210101000000、p20220101000000、_p20230101000000、_p20240101000000、および _p20250101000000 の sales パーティションテーブルのパーティションが OSS にアーカイブされたことがわかります。InnoDB ストレージエンジンには、ホットデータのパーティション _p20260101000000、_p20270101000000、および _p20280101000000 の 3 つだけが保持されています。sales テーブルは現在、ハイブリッドパーティションテーブルです。ハイブリッドパーティションテーブルのデータのクエリ方法については、「ハイブリッドパーティションテーブルのクエリ」をご参照ください。