PolarDB:Archive partitioned tables in CSV format

Create a DLM policy

The following example creates a partitioned table named sales. This table uses the order_time column as the partition key and divides partitions by time interval. The table has two policies: INTERVAL and DLM.

• INTERVAL policy: When inserted data exceeds the partition range, a new partition is automatically created with a time interval of one year.

• DLM policy: The table is defined to have only three partitions. When the number of partitions exceeds three and the DLM policy is executed:

  • If the OSS external table sales_history does not exist, a new OSS external table named sales_history is created, and cold data is transferred to the sales_history external table.

  • If the sales_history external table exists and the sales_history table is in the built-in OSS space, cold data is directly transferred to the sales_history external table.

1. Create the sales table with a DLM policy.

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

   The DLM policy for this table is named test_policy. When the number of partitions exceeds three, cold data from the current table is transferred to OSS in CSV format. The archived table is named sales_history and is read-only. If the archived OSS table does not exist, an OSS table is automatically created, and an OSS_FILE_FILTER is created on the id and name columns.

2. The DLM policy for the current table is stored in the mysql.dlm_policies system table. You can view the details of the DLM policy in this table. For more information about the mysql.dlm_policies table, see Table schema description. View the schema information of the mysql.dlm_policies table.

   mysql> SELECT * FROM mysql.dlm_policies\G

   The following result is returned:

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

   Currently, the sales table has three partitions, so data archiving is not performed.

3. Insert 3,000 test records into the sales partitioned table to ensure the data exceeds the current partition definition. This triggers the INTERVAL policy to automatically create new partitions.

   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. At this point, the INTERVAL policy is triggered to automatically create new partitions. This increases the number of partitions in the sales table, and the table schema changes to:

   mysql> SHOW CREATE TABLE sales\G

   The following result is returned:

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

   New partitions have been created, and the number of partitions is greater than three. This meets the execution condition of the DLM policy, so data can be archived.

Create a DLM policy

The following example creates a partitioned table named sales. This table uses the order_time column as the partition key and divides partitions by time interval. The table has two policies: INTERVAL and DLM.

• INTERVAL policy: When inserted data exceeds the partition range, a new partition is automatically created with a time interval of one year.

• DLM policy: The table is defined to have only three partitions. When the number of partitions exceeds three, executing the DLM policy directly transfers the older partitions to OSS.

1. Create the sales 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(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);

   The DLM policy for this table is named policy_part2part. When the number of partitions exceeds three, the old partitions are transferred to OSS.

2. View the DLM policy in the mysql.dlm_policies table.

   SELECT * FROM mysql.dlm_policies\G

   The following result is returned:

   *************************** 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. Use the proc_batch_insert stored procedure to insert test data into the sales partitioned table. This triggers the INTERVAL policy to automatically create new partitions.

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

   The following result indicates that the data was inserted successfully:

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

4. Run the following command to view the schema information of the sales table.

   SHOW CREATE TABLE sales \G

   The following result is returned:

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

Execute the DLM policy

1. Execute the DLM policy with the following command.

   CALL dbms_dlm.execute_all_dlm_policies();

2. View the DLM execution record in the mysql.dlm_progress table.

   SELECT * FROM mysql.dlm_progress \G

   The following result is returned:

   *************************** 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. Run the following command to view the schema information of the sales table.

   SHOW CREATE TABLE sales \G

   The following result is returned:

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

   The queried table schema information shows that the p20200101000000, p20210101000000, p20220101000000, _p20230101000000, _p20240101000000, and _p20250101000000 partitions in the sales partitioned table have all been transferred to OSS. Only the three hot data partitions _p20260101000000, _p20270101000000, and _p20280101000000 remain in the InnoDB engine. The sales table has become a hybrid partitioned table. For information about how to query data in a hybrid partitioned table, see Query a hybrid partition.