PolarDB:コールドデータをCSVまたはORC形式で手動でアーカイブする

共通テーブルのアーカイブ

コールドデータアーカイブは、テーブル内のコールドデータを保存するプロセスです。 コールドデータを格納するこれらのテーブルは、アーカイブテーブル (読み取り専用) と呼ばれ、OSSエンジンを利用します。 これらのアーカイブテーブルに対応するデータファイルはOSSに保存されます。 コールドデータがアーカイブされると、元のテーブルが占有していたPolarFileSystemスペースが解放されます。

構文

• コールドデータをCSV形式でアーカイブする:

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

  クラスターがPolarDB for MySQL 8.0.1.1.33以降、PolarDB for MySQL 8.0.2.2.13以降、またはマルチマスタークラスター (データベース /テーブル) エディションPolarDB for MySQL 8.0.1.1.15以降の場合、次のステートメントを実行してコールドデータを手動でアーカイブすることもできます。

  ALTER TABLE table_name ENGINE = CSV STORAGE OSS;

• コールドデータをORC形式でアーカイブする:

  ALTER TABLE table_name ENGINE = ORC STORAGE OSS;

Target file for archived table exists on oss.

ALTER TABLE table_name ENGINE = CSV CONNECTION = 'default_oss_server' , FORCE;

Parameters

注意事項

• InnoDBストレージエンジンとX-engineを使用するテーブルのデータのみをアーカイブできます。

• コールドデータアーカイブ中にDDLまたはDMLステートメントを実行してテーブルを変更することはできません。

• データファイルをカスタムOSSサーバーにアーカイブすることはできません。

• InnoDBストレージエンジンを使用するテーブルにコールドデータをアーカイブするには、テーブルにプライマリキーがあることを確認します。

• コールドデータがアーカイブされると、OSSのテーブルは読み取り専用になり、このテーブルのクエリ速度は遅くなります。 データアーカイブを実行する前に、テストデータを使用して、アーカイブテーブルのクエリパフォーマンスがビジネス要件を満たしているかどうかを評価することをお勧めします。

例

tテーブルのデータをCSVまたはORC形式でOSSにアーカイブします。

1. oss_testデータベースにtという名前のテーブルを作成します。 このテーブルはInnoDBストレージエンジンを使用します。

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';

     クラスターがPolarDB for MySQL 8.0.1.1.33以降、PolarDB for MySQL 8.0.2.2.13以降、またはマルチマスタークラスター (データベース /テーブル) エディションPolarDB for MySQL 8.0.1.1.15以降の場合は、次のステートメントを実行します。

     ALTER TABLE t ENGINE = CSV STORAGE OSS; 

   • ORC形式でデータをアーカイブする:

     ALTER TABLE t ENGINE = ORC STORAGE OSS;

4. データがアーカイブされた後、PolarDBコンソールにログインしてOSSのアーカイブされたデータベースとテーブルを表示するか、SQL文を実行してアーカイブされたテーブルのデータを表示します。

   • OSSでアーカイブされたデータベースとテーブルを表示するには、にログインします。PolarDBコンソール. 管理するクラスターを見つけ、[設定と管理] > [コールドデータアーカイブ] を選択して、OSSのアーカイブされたデータベースとテーブルに関する情報を表示します。

   • アーカイブされたテーブルのデータを表示するには、アーカイブ操作の前にSQL文を実行するのと同じ方法でSQL文を実行します。 例：

     SELECT * FROM t;

OSS外部テーブルへのテーブルパーティションのアーカイブ

構文

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

Parameters

注意事項

• ターゲットOSSテーブルが存在しない場合、システムは自動的にOSSテーブルを作成し、archive_table_file_filterパラメーターで指定された列にOSSファイルフィルターを作成します。 ファイルフィルターにより、クエリのパフォーマンスが向上します。 プライマリキーとパーティションキーは、クエリを高速化するためにarchive_table_file_filterパラメーターに自動的に追加されます。

• ターゲットOSSテーブルが存在する場合、ソーステーブルとターゲットテーブルの列名とタイプが同じかどうかがチェックされます。 列名と型が同じ場合、指定されたパーティションは宛先テーブルにアーカイブされます。 そうでない場合、アーカイブ操作は失敗します。 DDLステートメントを使用して、2つのテーブル間の定義の一貫性を確保できます。 詳細については、「コールドデータに対するDDL操作の実行」をご参照ください。 宛先テーブルに作成されたOSSファイルフィルターの定義がcall dbms_dlm.archive_partition2tableテーブルと異なる場合、宛先テーブルのOSSファイルフィルターが優先されます。

• ターゲットOSSテーブルが存在しないが、ターゲット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からファイルを削除し、データアーカイブを実行できます。

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

• ターゲットOSSテーブルは、CSV形式でのみコールドデータをアーカイブできます。

• アーカイブ済みパーティションテーブルには、少なくとも1つのInnoDBパーティションが含まれている必要があります。

• アーカイブされたパーティションデータは1つのテーブルにまとめられます。 パーティションテーブルにデータを復元する場合は、INSERT SELECTステートメントを実行します。

• 個々のレベル2パーティションをアーカイブすることはできません。 ただし、レベル1パーティションの下にあるすべてのレベル2パーティションをアーカイブできます。

• 次の表に、OSS外部テーブルにアーカイブできるパーティションタイプを示します。

  レベル1パーティション	レベル2パーティション	OSS外部テーブルにアーカイブ可能
  HASH	すべてのタイプ	不可
  リスト	すべてのタイプ	可
  RANGE	すべてのタイプ	可
  キー	すべてのタイプ	可
  リストデフォルト	すべてのタイプ	リストデフォルトハッシュなし

例

1. InnoDBパーティションテーブルを作成し、テーブルにデータを挿入します。

   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. パーティションテーブルのp0パーティションをOSSテーブルにアーカイブします。

   1. salesテーブルのスキーマを表示するには、次の文を実行します。

      -- View the status of the InnoDB table.
      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パーティションをsales_historyという名前のOSSテーブルにアーカイブします。

      -- Archive the p0 partition to the sales_history OSS table and create an OSS file filter on the id column of the table.
      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ファイルフィルター機能を有効にして、クエリを高速化できます。 詳細については、「OSSファイルフィルター機能を使用したコールドデータクエリの高速化」をご参照ください。

   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;

Target file for archived table exists on oss.

ALTER TABLE table_name CHANGE PARTITION part_name ENGINE = CSV FORCE;

Parameters

注意事項

• コールドデータアーカイブ機能は、InnoDBストレージエンジンを使用するパーティションテーブルでのみサポートされます。

• パーティションテーブルのデータをアーカイブするときは、InnoDBストレージエンジンに少なくとも1つのパーティションが残っていることを確認してください。 つまり、InnoDBストレージエンジンの最後のパーティションにデータをアーカイブすることはできません。

• アーカイブテーブルは、ハイブリッド分割テーブルです。 ハイブリッドパーティションテーブルでDDLステートメントを実行することはできません。 詳細については、「ハイブリッドパーティションテーブルの作成」をご参照ください。

• アーカイブされたパーティションのデータは変更できません。

• コールドデータアーカイブは、パーティションテーブルのサブパーティションではサポートされていません。

• リストの既定のハッシュパーティションテーブルの既定のパーティションにコールドデータをアーカイブすることはできません。

• ハッシュまたはキーパーティションテーブルにコールドデータをアーカイブすることはできません。

• パーティションテーブル全体のコールドデータをアーカイブすることはできません。

• アーカイブするパーティション内のファイルと同じ名前のファイルが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. データベースにtという名前のテーブルを作成します。 このテーブルはInnoDBストレージエンジンを使用します。

   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のアーカイブされたデータベースとテーブルに関する情報を表示します。

• ハイブリッドパーティションテーブルのデータを表示する方法については、「ハイブリッドパーティションテーブルのデータの照会」をご参照ください。

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からテーブルを削除するか、OSSからPolarStoreにテーブルをインポートすると、OSS内の対応するファイルが保持されます。 データが不要になったことを確認した場合は、次のステートメントを使用して、OSSから対応するファイルを削除できます。

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

OSSファイルは非同期で削除されます。 したがって、ファイルを削除する前に、クラスター内のすべてのノードがOSSファイルに依存しなくなるまで待つ必要があります。 トラフィックが多いときに遅延が発生することに注意してください。 上記のステートメントが失敗し、「OSSファイルはまだ使用中です」というエラーメッセージが表示された場合は、しばらく待ってからステートメントを再実行してください。