PolarDB：Arsipkan data dalam format CSV atau ORC

Arsipkan tabel standar

Pengarsipan data dingin merupakan operasi tingkat tabel. Saat Anda mengarsipkan sebuah tabel, tabel tersebut menjadi tabel arsip read-only. Engine tabel yang diarsipkan adalah engine OSS, dan file datanya disimpan di OSS. Setelah operasi pengarsipan selesai, ruang yang sebelumnya ditempati oleh tabel lokal asli di PolarStore akan dilepaskan.

Syntax

ALTER TABLE table_name ENGINE = ORC STORAGE OSS;

Parameters

Considerations

• Anda dapat menggunakan fitur pengarsipan data dingin untuk tabel yang menggunakan engine InnoDB atau engine X-Engine.

• Anda tidak dapat menggunakan pernyataan Data Definition Language (DDL) atau Data Manipulation Language (DML) untuk memodifikasi tabel selama proses pengarsipan data dingin.

• Fitur pengarsipan data dingin tidak mendukung pengarsipan file data ke server OSS yang dibuat pengguna.

• Saat Anda menggunakan fitur pengarsipan data dingin untuk tabel yang menggunakan engine InnoDB, tabel tersebut harus memiliki kunci primer.

• Setelah pengarsipan data dingin selesai, tabel yang diarsipkan di OSS bersifat read-only dan mungkin memiliki kinerja kueri yang buruk. Uji kinerja kueri setelah pengarsipan data untuk memastikan bahwa kinerjanya memenuhi kebutuhan Anda.

• Jika sebuah tabel memiliki Indeks Kolom dalam Memori (IMCI), tabel tersebut tidak dapat diarsipkan dalam format CSV dan hanya dapat diarsipkan dalam format ORC.

Example

Arsipkan data dalam tabel t ke OSS dalam format CSV atau ORC.

1. Buat tabel InnoDB bernama t di database oss_test.

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

2. Masukkan data ke dalam tabel t.

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

3. Gunakan perintah ALTER untuk mengarsipkan data dingin.

   • Arsipkan dalam format CSV:

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

   • Arsipkan dalam format ORC:

     ALTER TABLE t ENGINE = ORC STORAGE OSS;

4. Setelah pengarsipan selesai, Anda dapat masuk ke PolarDB console untuk melihat informasi tentang database dan tabel yang diarsipkan di OSS, atau gunakan pernyataan SQL untuk melihat data dalam tabel yang diarsipkan:

   • Untuk melihat informasi tentang database dan tabel yang diarsipkan di OSS, masuk ke Konsol PolarDB. Pada halaman kluster target, buka Configuration And Management > Cold Data Archiving untuk melihat informasi tersebut.

   • Untuk melihat data dalam tabel yang diarsipkan, gunakan pernyataan SQL. Anda tidak perlu mengubah cara mengakses tabel tersebut. Contohnya:

     SELECT * FROM t;

Arsipkan tabel partisi ke tabel eksternal OSS

Syntax

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

Parameters

Considerations

• Jika tabel OSS tujuan untuk pengarsipan belum ada, tabel tersebut akan dibuat secara otomatis. Untuk mempercepat kueri, oss_file_filter juga akan dibuat secara otomatis pada kolom yang ditentukan oleh oss_file_filter. Kunci primer dan kunci partisi juga akan ditambahkan secara otomatis ke oss_file_filter.

• Jika tabel OSS tujuan sudah ada, Anda harus memeriksa apakah nama kolom dan definisi tipe kolom konsisten antara kedua tabel. Jika konsisten, Anda dapat mengarsipkan data tersebut. Jika tidak konsisten, akan terjadi kesalahan. Anda dapat menggunakan pernyataan DDL untuk menyamakan definisi kedua tabel tersebut. Untuk informasi selengkapnya, lihat DDL untuk data dingin. Selain itu, jika tabel tujuan memiliki oss_file_filter, dan definisinya berbeda dari yang ada di call dbms_dlm.archive_partition2table, maka oss_file_filter dari tabel tujuan yang akan digunakan.

• Jika tabel OSS tujuan belum ada tetapi file dengan nama yang sama sudah ada di OSS, pesan kesalahan akan dilaporkan saat Anda melakukan operasi pengarsipan. Pesan kesalahannya 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 pengarsipan.

  -- Hapus data OSS.
  mysql> CALL dbms_oss.delete_table_file('test', 'sales_history');
  
  Query OK, 0 rows affected (0.76 sec)
  
  -- Lakukan operasi pengarsipan.
  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 sebuah partisi diarsipkan, tabel tersebut harus berisi setidaknya satu partisi InnoDB.

• Data yang diarsipkan kehilangan informasi partisinya dan tidak dapat dipulihkan secara langsung. Namun, Anda dapat memulihkannya menggunakan insert select.

• Anda tidak dapat mengarsipkan subpartisi secara individual. Anda hanya dapat mengarsipkan semua subpartisi di bawah seluruh partisi tingkat pertama.

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

  Partisi tingkat pertama	Partisi tingkat kedua	Pengarsipan ke tabel eksternal OSS
  HASH	Jenis apa pun	Pengarsipan partisi tingkat pertama HASH ke tabel eksternal OSS tidak didukung.
  LIST	Jenis apa pun	Didukung.
  RANGE	Jenis apa pun	Didukung.
  KEY	Jenis apa pun	Didukung.
  LIST DEFAULT	Jenis apa pun	Pengarsipan partisi DEFAULT tidak didukung.

Example

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

      Berikut ini yang 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. Jalankan 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)

Arsipkan tabel partisi

Mengarsipkan tabel partisi berarti mengarsipkan partisinya. Tabel tersebut menjadi tabel partisi hibrida setelah pengarsipan, dan file data dari partisi yang diarsipkan disimpan di OSS. Setelah operasi pengarsipan selesai, ruang yang ditempati oleh partisi di PolarStore akan dilepaskan secara otomatis.

Syntax

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

Parameters

Considerations

• Anda hanya dapat menggunakan fitur pengarsipan data dingin untuk tabel partisi yang menggunakan engine InnoDB.

• Saat Anda mengarsipkan partisi dari tabel partisi, Anda harus menyisakan setidaknya satu partisi yang menggunakan engine InnoDB. Anda tidak dapat mengarsipkan partisi terakhir yang menggunakan engine InnoDB.

• Tabel tersebut menjadi tabel partisi hibrida setelah pengarsipan. Anda tidak dapat melakukan operasi DDL pada tabel partisi hibrida. Untuk informasi selengkapnya tentang pertimbangan tersebut, lihat Buat partisi hibrida.

• Anda tidak dapat memodifikasi data dari partisi yang diarsipkan.

• Anda tidak dapat mengarsipkan subpartisi dalam tabel partisi.

• Anda tidak dapat mengarsipkan partisi DEFAULT dari tabel partisi hash default daftar.

• Anda tidak dapat mengarsipkan tabel partisi HASH atau KEY.

• Anda tidak dapat mengarsipkan seluruh tabel partisi secara manual.

• Saat Anda mengarsipkan data dalam sebuah partisi, jika file dengan nama yang sama sudah ada di OSS, operasi tersebut gagal dan pesan kesalahan serupa berikut 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;

Example

Arsipkan data dalam 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 dalam 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 dalam 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 pengarsipan selesai, Anda dapat masuk ke PolarDB console untuk melihat informasi tentang database dan tabel yang diarsipkan di OSS, atau gunakan pernyataan SQL untuk mengkueri data dalam tabel partisi hibrida:

   • Untuk melihat informasi tentang database dan tabel yang diarsipkan di OSS, masuk ke Konsol PolarDB. Pada halaman kluster target, buka Configuration And Management > Cold Data Archiving untuk melihat informasi tersebut.

   • Untuk informasi selengkapnya tentang mengkueri data dalam tabel partisi hibrida, lihat Mengkueri partisi hibrida.

Enkripsi TDE untuk data yang diarsipkan

Untuk memenuhi kebutuhan keamanan data Anda, Anda dapat menentukan enkripsi data transparan (TDE) untuk file saat mengarsipkan data dingin ke OSS. Prinsip dasarnya adalah Anda memilih file yang akan dienkripsi selama operasi pengarsipan, lalu server OSS melakukan enkripsi tersebut. Untuk informasi selengkapnya tentang enkripsi, lihat Enkripsi data. Setelah enkripsi, Anda dapat langsung mengkueri data yang diarsipkan menggunakan pernyataan SQL di PolarDB for MySQL. Seluruh proses enkripsi dan dekripsi diselesaikan di latar belakang dan transparan bagi Anda. Tidak diperlukan operasi tambahan.

Syntax

Selama operasi pengarsipan manual, Anda dapat menambahkan sintaks ENCRYPTION="Y" untuk mengaktifkan TDE.

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

Pulihkan data dari OSS ke 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));

Hapus file yang sesuai dari OSS

Saat Anda menghapus tabel dari OSS atau memulihkannya ke PolarStore, file di OSS tidak dihapus. Setelah Anda memastikan bahwa data tersebut tidak lagi diperlukan, Anda dapat menggunakan sintaks berikut untuk menghapus file yang sesuai dari OSS:

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

Karena penghapusan file dari OSS adalah operasi asinkron, file tersebut hanya dapat dihapus sepenuhnya setelah semua node di kluster tidak lagi bergantung padanya. Operasi ini mungkin tertunda saat lalu lintas sedang tinggi. Oleh karena itu, jika perintah di atas gagal dan mengembalikan pesan kesalahan OSS files are still in use, tunggu beberapa saat lalu jalankan kembali perintah tersebut.