PolarDB:Arsipkan tabel partisi dalam format CSV

Buat kebijakan DLM

Contoh berikut membuat tabel partisi bernama sales yang menggunakan kolom order_time sebagai kunci partisi. Tabel ini memiliki kebijakan INTERVAL dan kebijakan DLM:

• Kebijakan INTERVAL: Ketika data yang dimasukkan berada di luar rentang partisi yang ada, partisi baru akan dibuat secara otomatis dengan interval waktu satu tahun.

• Kebijakan DLM: Tabel ini didefinisikan untuk hanya menyimpan tiga partisi. Ketika jumlah partisi melebihi tiga, kebijakan DLM akan dipicu dan melakukan salah satu tindakan berikut:

  • Jika tabel eksternal OSS sales_history belum ada, tabel eksternal OSS baru bernama sales_history akan dibuat, dan data dingin diarsipkan ke tabel sales_history.

  • Jika tabel eksternal sales_history sudah ada, dan tabel sales_history berada di OSS bawaan, data dingin langsung diarsipkan ke tabel eksternal sales_history.

1. Buat tabel sales dengan kebijakan DLM.

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

   Kebijakan DLM untuk tabel ini bernama test_policy. Ketika jumlah partisi melebihi tiga, kebijakan ini mengarsipkan data dingin dari tabel sumber dalam format CSV ke OSS. Tabel arsip yang dihasilkan bernama sales_history dan bersifat read-only. Jika tabel arsip OSS belum ada, sistem akan membuatnya secara otomatis dan menambahkan OSS_FILE_FILTER ke kolom id dan name.

2. Kebijakan DLM untuk tabel saat ini disimpan di tabel sistem mysql.dlm_policies. Anda dapat mengkueri tabel ini untuk melihat detail kebijakan DLM. Untuk informasi selengkapnya tentang tabel mysql.dlm_policies, lihat Deskripsi struktur tabel. Lihat struktur tabel mysql.dlm_policies.

   mysql> SELECT * FROM mysql.dlm_policies\G

   Contoh output:

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

   Saat ini, tabel sales memiliki tiga partisi, sehingga belum ada data yang diarsipkan.

3. Masukkan 3.000 baris data uji ke tabel partisi sales. Hal ini memastikan bahwa data melebihi rentang partisi yang ditentukan dan memicu kebijakan INTERVAL untuk membuat partisi baru secara otomatis.

   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. Kebijakan INTERVAL dipicu, yang menambahkan partisi baru ke tabel sales. Struktur tabel sekarang sebagai berikut:

   mysql> SHOW CREATE TABLE sales\G

   Contoh output:

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

   Partisi baru meningkatkan jumlah total partisi menjadi lebih dari tiga. Hal ini memenuhi kondisi untuk kebijakan DLM, dan data kini siap untuk diarsipkan.

Buat kebijakan DLM

Contoh berikut membuat tabel partisi bernama sales yang menggunakan kolom order_time sebagai kunci partisi. Tabel ini memiliki kebijakan INTERVAL dan kebijakan DLM:

• Kebijakan INTERVAL: Ketika data yang dimasukkan berada di luar rentang partisi yang ada, partisi baru akan dibuat secara otomatis dengan interval waktu satu tahun.

• Kebijakan DLM: Tabel ini didefinisikan untuk hanya menyimpan tiga partisi. Ketika jumlah partisi melebihi tiga, kebijakan DLM akan dipicu dan mengarsipkan partisi lama langsung ke OSS.

1. Buat tabel 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);

   Kebijakan DLM untuk tabel ini bernama policy_part2part. Ketika jumlah partisi melebihi tiga, partisi lama diarsipkan ke OSS.

2. Lihat kebijakan DLM di tabel mysql.dlm_policies.

   SELECT * FROM mysql.dlm_policies\G

   Contoh output:

   *************************** 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. Gunakan prosedur tersimpan proc_batch_insert untuk memasukkan data uji ke tabel partisi sales. Tindakan ini memicu kebijakan INTERVAL, yang secara otomatis membuat partisi baru.

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

   Hasil berikut menunjukkan bahwa data berhasil dimasukkan:

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

4. Jalankan perintah berikut untuk melihat struktur tabel sales:

   SHOW CREATE TABLE sales \G

   Contoh output:

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

Jalankan kebijakan DLM

1. Jalankan perintah berikut untuk menjalankan kebijakan DLM:

   CALL dbms_dlm.execute_all_dlm_policies();

2. Kueri tabel mysql.dlm_progress untuk melihat riwayat eksekusi DLM.

   SELECT * FROM mysql.dlm_progress \G

   Contoh output:

   *************************** 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. Jalankan perintah berikut untuk melihat struktur tabel sales:

   SHOW CREATE TABLE sales \G

   Contoh output:

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

   Output menunjukkan bahwa partisi p20200101000000, p20210101000000, p20220101000000, _p20230101000000, _p20240101000000, dan _p20250101000000 dari tabel partisi sales telah diarsipkan ke OSS. Hanya tiga partisi data panas, yaitu _p20260101000000, _p20270101000000, dan _p20280101000000, yang tetap disimpan di engine InnoDB. Tabel sales kini menjadi tabel partisi hibrida. Untuk informasi tentang cara mengkueri data dalam tabel partisi hibrida, lihat Kueri tabel partisi hibrida.