PolarDB：Arsipkan tabel partisi dalam format CSV

Buat kebijakan DLM

Contoh berikut membuat tabel partisi bernama sales. Tabel ini menggunakan kolom order_time sebagai kunci partisi dan membagi partisi berdasarkan interval waktu. Tabel ini memiliki dua kebijakan: INTERVAL dan DLM.

• Kebijakan INTERVAL: Saat data yang dimasukkan melebihi rentang partisi, partisi baru dibuat secara otomatis dengan interval waktu satu tahun.

• Kebijakan DLM: Tabel ini didefinisikan hanya memiliki tiga partisi. Ketika jumlah partisi melebihi tiga dan kebijakan DLM dijalankan:

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

  • Jika tabel eksternal sales_history sudah ada dan tabel sales_history berada di ruang OSS bawaan, data dingin langsung dipindahkan 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. Saat jumlah partisi melebihi tiga, data dingin dari tabel saat ini dipindahkan ke OSS dalam format CSV. Tabel arsip bernama sales_history dan bersifat read-only. Jika tabel OSS arsip belum ada, tabel OSS dibuat secara otomatis, dan OSS_FILE_FILTER dibuat pada kolom id dan name.

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

   mysql> SELECT * FROM mysql.dlm_policies\G

   Hasil berikut dikembalikan:

   *************************** 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 pengarsipan data belum dilakukan.

3. Masukkan 3.000 catatan uji ke tabel partisi sales untuk memastikan data melebihi definisi partisi saat ini. Hal ini 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. Pada titik ini, kebijakan INTERVAL dipicu untuk membuat partisi baru secara otomatis. Hal ini meningkatkan jumlah partisi di tabel sales, dan skema tabel berubah menjadi:

   mysql> SHOW CREATE TABLE sales\G

   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(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 telah dibuat, dan jumlah partisi lebih dari tiga. Hal ini memenuhi kondisi eksekusi kebijakan DLM, sehingga data dapat diarsipkan.

Buat kebijakan DLM

Contoh berikut membuat tabel partisi bernama sales. Tabel ini menggunakan kolom order_time sebagai kunci partisi dan membagi partisi berdasarkan interval waktu. Tabel ini memiliki dua kebijakan: INTERVAL dan DLM.

• Kebijakan INTERVAL: Saat data yang dimasukkan melebihi rentang partisi, partisi baru dibuat secara otomatis dengan interval waktu satu tahun.

• Kebijakan DLM: Tabel ini didefinisikan hanya memiliki tiga partisi. Saat jumlah partisi melebihi tiga, menjalankan kebijakan DLM langsung memindahkan partisi yang lebih lama 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. Saat jumlah partisi melebihi tiga, partisi lama dipindahkan ke OSS.

2. Lihat kebijakan DLM di tabel mysql.dlm_policies.

   SELECT * FROM mysql.dlm_policies\G

   Hasil berikut dikembalikan:

   *************************** 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. Hal ini memicu kebijakan INTERVAL untuk membuat partisi baru secara otomatis.

   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 informasi skema tabel sales.

   SHOW CREATE TABLE sales \G

   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 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 kebijakan DLM dengan perintah berikut.

   CALL dbms_dlm.execute_all_dlm_policies();

2. Lihat catatan eksekusi DLM di tabel mysql.dlm_progress.

   SELECT * FROM mysql.dlm_progress \G

   Hasil berikut dikembalikan:

   *************************** 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 informasi skema tabel sales.

   SHOW CREATE TABLE sales \G

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

   Informasi skema tabel yang dikueri menunjukkan bahwa partisi p20200101000000, p20210101000000, p20220101000000, _p20230101000000, _p20240101000000, dan _p20250101000000 di tabel partisi sales semuanya telah dipindahkan ke OSS. Hanya tiga partisi data panas _p20260101000000, _p20270101000000, dan _p20280101000000 yang tetap berada di engine InnoDB. Tabel sales telah menjadi tabel partisi hibrida. Untuk informasi tentang cara mengkueri data di tabel partisi hibrida, lihat Kueri partisi hibrida.