PolarDB:CSV または ORC フォーマットでのデータアーカイブ

DLM ポリシーの作成

次の例では、order_time 列をパーティションキーとして時間間隔でパーティション化され、INTERVAL ポリシーと DLM ポリシーの両方が設定された、sales という名前のパーティションテーブルを作成します。

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

   スキーマ情報では、sales パーティションテーブルのパーティション p20200101000000、p20210101000000、p20220101000000、_p20230101000000、_p20240101000000、および _p20250101000000 が OSS にアーカイブされていることが示されています。InnoDB エンジンには、3つのホットデータパーティション _p20260101000000、_p20270101000000、および _p20280101000000 のみが残っています。sales テーブルは、現在ハイブリッドパーティションテーブルです。ハイブリッドパーティションテーブルのデータをクエリする方法については、「ハイブリッドパーティションテーブルをクエリする」をご参照ください。

DLM ポリシーの作成

次の例では、order_time 列をパーティションキーとして使用し、時間間隔でデータをパーティション分割する sales_orc パーティションテーブルを作成します。このテーブルには、INTERVAL ポリシーと DLM ポリシーの両方があります。

• INTERVAL ポリシー： 挿入されたデータがパーティション範囲を超えると、1 年間隔で新しいパーティションが自動的に作成されます。

• DLM ポリシー： テーブルに 3 つを超えるパーティションがある場合、DLM ポリシーは古いパーティションを ORC フォーマットで OSS にアーカイブします。テーブルはハイブリッドパーティションテーブルになります。

TIER TO PARTITION ENGINE=ORC を使用して古いパーティションを OSS にアーカイブする DLM ポリシーを持つ sales_orc テーブルを作成します。

CREATE TABLE `sales_orc` (
  `id` int DEFAULT NULL,
  `name` varchar(20) DEFAULT NULL,
  `order_time` datetime NOT NULL,
   PRIMARY KEY (`order_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
-- order_time に対して 1 年間隔の RANGE COLUMN パーティションを作成 (自動パーティション作成)
PARTITION BY RANGE  COLUMNS(order_time) INTERVAL(YEAR, 1)
-- 2020 / 2021 / 2022 より前のデータを保持する 3 つの初期パーティションを事前作成
(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 ポリシー: パーティション数が 3 を超えると、古いパーティションを ORC フォーマットで OSS にアーカイブ
-- TIER TO PARTITION: パーティションレベルのアーカイブ。テーブルはハイブリッドパーティションテーブルになります
-- (ホット InnoDB パーティションとコールド ORC パーティションが共存)
DLM ADD POLICY policy_orc TIER TO PARTITION ENGINE=ORC STORAGE=OSS READ ONLY
ON (PARTITIONS OVER 3);

DLM ポリシー policy_orc では、パーティション数が 3 を超えると、古いパーティションが ORC 形式で OSS にアーカイブされます。これにより、テーブルはホット InnoDB パーティションとコールド ORC パーティションの両方を含むハイブリッドパーティションテーブルになります。

DLM ポリシーの実行

INTERVAL をトリガーして新しいパーティションを自動的に作成し、パーティション数が 3 を超えて DLM ポリシー条件が満たされるように、sales_orc パーティションテーブルにテストデータを挿入します。

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

DLM ポリシーを実行します。

CALL dbms_dlm.execute_all_dlm_policies();

アーカイブ結果の表示

mysql.dlm_progress テーブルで DLM ポリシー実行レコードを表示するには、次のコマンドを実行します：

SELECT * FROM mysql.dlm_progress\G

アクセス頻度の低いコールドデータを格納するパーティションは OSS にアーカイブされています。次のコマンドを実行して、sales_orc テーブルのスキーマを表示します:

SHOW CREATE TABLE sales_orc\G

結果は次のとおりです。

*************************** 1. row ***************************
       Table: sales_orc
CREATE TABLE `sales_orc` (
  `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 /*!99990 800020223 OSS META=1 */ 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 = ORC,
 PARTITION p20210101000000 VALUES LESS THAN ('2021-01-01 00:00:00') ENGINE = ORC,
 PARTITION p20220101000000 VALUES LESS THAN ('2022-01-01 00:00:00') ENGINE = ORC,
 PARTITION _p20230101000000 VALUES LESS THAN ('2023-01-01 00:00:00') ENGINE = ORC,
 PARTITION _p20240101000000 VALUES LESS THAN ('2024-01-01 00:00:00') ENGINE = ORC,
 PARTITION _p20250101000000 VALUES LESS THAN ('2025-01-01 00:00:00') ENGINE = ORC,
 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)

クエリ結果に示すように、sales_orc パーティションテーブルの p20210101000000、p20220101000000、_p20230101000000、_p20240101000000、および _p20250101000000 パーティションは OSS にアーカイブされています。InnoDB エンジンには、3つのホットデータパーティション _p20260101000000、_p20270101000000、および _p20280101000000 のみが残っています。これにより、sales_orc テーブルはハイブリッドパーティションテーブルになります。ハイブリッドパーティションテーブルでデータをクエリする方法については、「ハイブリッドパーティションテーブルのクエリ」をご参照ください。