PolarDB:Archive a partitioned table in CSV format

Create a DLM policy

The following example creates a partitioned table named sales that uses the order_time column as the partition key. The table has an INTERVAL policy and a DLM policy:

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

• DLM policy: The table is defined to retain only three partitions. When the number of partitions exceeds three, the DLM policy is triggered and performs one of the following actions:

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

  • If the sales_history external table exists, and the sales_history table is on the built-in OSS, the cold data is directly archived 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 the table is named test_policy. When the number of partitions exceeds three, the policy archives the cold data from the source table in CSV format to OSS. The resulting archive table is named sales_history and is read-only. If the OSS archive table does not exist, the system automatically creates it and adds an OSS_FILE_FILTER to the id and name columns.

2. The DLM policies for the current table are stored in the system table mysql.dlm_policies. You can query this table to view the details of the DLM policies. For more information about the mysql.dlm_policies table, see Table structure description. View the structure of the mysql.dlm_policies table.

   mysql> SELECT * FROM mysql.dlm_policies\G

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

   Currently, the sales table has three partitions, so no data is archived.

3. Insert 3,000 rows of test data into the sales partitioned table. This ensures that the data exceeds the defined partition range and 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. The INTERVAL policy is triggered, which adds new partitions to the sales table. The table structure is now as follows:

   mysql> SHOW CREATE TABLE sales\G

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

   The new partitions increase the total partition count to more than three. This meets the condition for the DLM policy, and the data is now ready for archiving.

Create a DLM policy

The following example creates a partitioned table named sales that uses the order_time column as the partition key. The table has an INTERVAL policy and a DLM policy:

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

• DLM policy: The table is defined to retain only three partitions. When the number of partitions exceeds three, the DLM policy is triggered and archives the older partitions directly 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 the table is named policy_part2part. When the number of partitions exceeds three, the older partitions are archived to OSS.

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

   SELECT * FROM mysql.dlm_policies\G

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

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

   The following result indicates that the data is inserted successfully:

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

4. Run the following command to view the sales table structure:

   SHOW CREATE TABLE sales \G

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

Execute the DLM policy

1. Run the following command to execute the DLM policy:

   CALL dbms_dlm.execute_all_dlm_policies();

2. Query the mysql.dlm_progress table to view the DLM execution history.

   SELECT * FROM mysql.dlm_progress \G

   Sample 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. Run the following command to view the sales table structure:

   SHOW CREATE TABLE sales \G

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

   The output shows that the partitions p20200101000000, p20210101000000, p20220101000000, _p20230101000000, _p20240101000000, and _p20250101000000 of the sales partitioned table are archived to OSS. Only the three hot data partitions, _p20260101000000, _p20270101000000, and _p20280101000000, are retained in the InnoDB engine. The sales table is now a hybrid partitioned table. For information about how to query data in a hybrid partitioned table, see Query a hybrid partitioned table.