PolarDB:Arsipkan data dalam format CSV atau ORC

Sintaks

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

Parameter

Pertimbangan

• Jika tabel OSS tujuan belum ada, tabel tersebut akan dibuat secara otomatis. Untuk mempercepat kueri, oss_file_filter juga dibuat secara otomatis pada kolom yang ditentukan oleh parameter oss_file_filter. Primary key dan partition key juga ditambahkan secara otomatis ke oss_file_filter.

• Jika tabel OSS tujuan sudah ada, Anda harus membandingkan nama kolom atau definisi tipe kolom kedua tabel tersebut. Data hanya dapat diarsipkan jika definisinya konsisten. Jika tidak, akan muncul error. Anda dapat menggunakan pernyataan DDL untuk menyamakan definisi kedua tabel. Untuk informasi lebih lanjut, lihat DDL Data Dingin. Selain itu, jika oss_file_filter telah didefinisikan pada tabel tujuan dan definisinya tidak konsisten dengan yang ada di perintah call dbms_dlm.archive_partition2table, maka definisi oss_file_filter pada tabel tujuan yang akan digunakan.

• Jika tabel OSS tujuan belum ada tetapi file dengan nama yang sama sudah ada di OSS, pesan error akan muncul saat Anda melakukan operasi arsip. Pesan error tersebut sebagai berikut:

  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.

  Jika Anda memastikan bahwa file sisa di OSS tidak diperlukan, Anda dapat menggunakan prosedur tersimpan untuk menghapus data di OSS lalu melakukan operasi arsip.

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

• Tabel OSS tujuan hanya mendukung data dingin dalam format CSV.

• Setelah partisi diarsipkan, tabel harus tetap berisi setidaknya satu partisi InnoDB.

• Data yang diarsipkan kehilangan informasi partisinya dan tidak dapat langsung dipulihkan ke partisi. Namun, Anda dapat memulihkan data tersebut ke dalam tabel menggunakan pernyataan INSERT ... SELECT.

• Anda tidak dapat mengarsipkan partisi tingkat kedua secara individual. Anda hanya dapat mengarsipkan seluruh partisi tingkat pertama, termasuk semua partisi tingkat keduanya.

• Tabel berikut mencantumkan jenis partisi yang didukung oleh fungsi partisi PolarDB for MySQL.

  Partisi tingkat pertama	Partisi tingkat kedua	Dukungan arsip OSS
  HASH	Segala jenis	Mengarsipkan partisi tingkat pertama HASH ke tabel eksternal OSS tidak didukung.
  LIST	Segala jenis	Didukung.
  RANGE	Segala jenis	Didukung.
  KEY	Segala jenis	Didukung.
  LIST DEFAULT	Segala jenis	Mengarsipkan partisi DEFAULT tidak didukung.

Contoh

1. Buat tabel partisi InnoDB dan masukkan data.

   DROP TABLE IF EXISTS `sales`;
   -- Buat tabel partisi.
   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. Arsipkan partisi p0 dari tabel partisi ke tabel OSS baru.

   1. Jalankan perintah berikut untuk melihat skema tabel sales.

      -- Lihat status tabel InnoDB saat ini.
      mysql> SHOW CREATE TABLE sales;

      Hasil berikut dikembalikan:

      *************************** 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. Jalankan perintah berikut untuk mengarsipkan partisi p0 ke tabel OSS sales_history.

      -- Arsipkan partisi p0 ke tabel OSS sales_history dan buat OSS_FILE_FILTER pada kolom id.
      mysql> CALL dbms_dlm.archive_partition2table('test', 'sales', 'p0', 'test', 'sales_history', 'id');
      Query OK, 0 rows affected (1.86 sec)

   3. Jalankan perintah berikut untuk melihat skema tabel sales_history.

      SHOW CREATE TABLE sales_history;

      Hasil berikut dikembalikan:

      *************************** 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. Lakukan kueri pada tabel OSS baru.

   Catatan

   Anda dapat mengaktifkan fitur percepatan kueri OSS_FILE_FILTER untuk mempercepat kueri.

   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)

Sintaks

• Arsipkan sebagai file dalam format CSV:

  ALTER TABLE table_name CHANGE PARTITION part_name ENGINE = CSV;

• Arsipkan sebagai file dalam format ORC:

  ALTER TABLE table_name CHANGE PARTITION part_name ENGINE = ORC;

Parameter

Pertimbangan

• Anda hanya dapat menggunakan fitur arsip data dingin untuk tabel partisi InnoDB.

• Tabel menjadi tabel partisi hibrida setelah diarsipkan. Tabel partisi hibrida dapat berisi partisi ORC atau CSV. Anda tidak dapat melakukan operasi DDL Online pada tabel partisi hibrida, kecuali untuk menambah atau menghapus kolom dan indeks. Untuk pertimbangan penggunaan, lihat Buat partisi hibrida.

• Anda tidak dapat memodifikasi data partisi yang diarsipkan.

• Anda tidak dapat menggunakan arsip data dingin untuk partisi tingkat kedua dalam tabel partisi.

• Anda tidak dapat menggunakan arsip data dingin untuk partisi DEFAULT dari tabel partisi LIST DEFAULT HASH.

• Anda tidak dapat menggunakan arsip data dingin untuk tabel partisi HASH atau KEY.

• Anda tidak dapat mengarsipkan seluruh tabel partisi secara manual.

• Saat Anda mengarsipkan data dalam partisi, jika file dengan nama yang sama sudah ada di OSS, operasi akan gagal dan pesan error berikut akan dilaporkan:

  Target file for archived table exists on oss.

  Anda harus menjalankan perintah berikut untuk menimpa file yang sudah ada di OSS. Ganti table_name dan part_name dengan nilai aktual Anda.

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

Contoh

Arsipkan data di partisi p1 dan p2 dari tabel t ke OSS dalam format CSV.

1. Buat tabel InnoDB bernama t di 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. Masukkan data ke tabel 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. Jalankan perintah berikut untuk mengarsipkan data di partisi p1 dan p2 ke engine OSS.

   • Arsipkan dalam format CSV:

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

   • Arsipkan dalam format ORC:

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

4. Setelah arsip selesai, Anda dapat login ke PolarDB console untuk melihat informasi database dan tabel yang diarsipkan di OSS, atau gunakan pernyataan SQL untuk melakukan kueri data di tabel partisi hibrida:

   • Untuk melihat informasi database dan tabel yang diarsipkan di OSS: Login ke Konsol PolarDB. Di panel navigasi sisi kiri kluster target, pilih Settings and Management > Data Lifecycle > Data Archive (Cold Data) untuk melihat informasinya.

   • Untuk melakukan kueri data di tabel partisi hibrida, lihat Melakukan kueri partisi hibrida.

Pulihkan tabel arsip standar

Untuk memodifikasi data dingin yang diarsipkan di OSS, gunakan sintaks ALTER ENGINE untuk memulihkan data ke PolarStore. Memulihkan data ke PolarStore juga menghapus data yang sesuai dari OSS. Setelah Anda memodifikasi data, Anda dapat mengarsipkan kembali tabel yang telah dimodifikasi ke OSS.

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;

Pulihkan partisi yang diarsipkan

Jika Anda perlu memulihkan data dari tabel partisi yang diarsipkan ke PolarStore, Anda dapat menggunakan pernyataan ALTER untuk memulihkan data dari OSS ke PolarStore. Setelah data dipulihkan, data dingin di OSS juga dihapus.

ALTER TABLE table_name REORGANIZE PARTITION part_name INTO (partition_definition);

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