Archive data in CSV or ORC format

Create a DLM policy

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

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

• DLM policy: The table retains only three partitions. When the number of partitions exceeds three, the DLM policy directly archives 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, older partitions are archived 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 to trigger 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 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. Run the following command to execute the DLM policy:

   CALL dbms_dlm.execute_all_dlm_policies();

2. View the DLM execution records 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 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 schema information shows that the partitions p20200101000000, p20210101000000, p20220101000000, _p20230101000000, _p20240101000000, and _p20250101000000 of the sales partitioned table have been archived to OSS. Only the three hot data partitions _p20260101000000, _p20270101000000, and _p20280101000000 remain in the InnoDB engine. The sales table is now a hybrid partitioned table. For information on how to query data in a hybrid partitioned table, see Query a hybrid partitioned table.

Create a DLM policy

The following example creates a sales_orc partitioned table that uses the order_time column as the partition key and partitions data by time interval. The table has both INTERVAL and DLM policies:

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

• DLM policy: When the table has more than 3 partitions, the DLM policy archives old partitions in ORC format to OSS. The table becomes a hybrid partitioned table.

Create the sales_orc table with a DLM policy that uses TIER TO PARTITION ENGINE=ORC to archive old partitions to OSS.

CREATE TABLE `sales_orc` (
  `id` int DEFAULT NULL,
  `name` varchar(20) DEFAULT NULL,
  `order_time` datetime NOT NULL,
   PRIMARY KEY (`order_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
-- Create RANGE COLUMN partitions on order_time with a 1-year INTERVAL (automatic partition creation)
PARTITION BY RANGE  COLUMNS(order_time) INTERVAL(YEAR, 1)
-- Pre-create 3 initial partitions that hold data before 2020 / 2021 / 2022
(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 policy: when the partition count exceeds 3, archive old partitions in ORC format to OSS
-- TIER TO PARTITION: partition-level archiving. The table becomes a hybrid partitioned table
--                    (hot InnoDB partitions coexist with cold ORC partitions)
DLM ADD POLICY policy_orc TIER TO PARTITION ENGINE=ORC STORAGE=OSS READ ONLY
ON (PARTITIONS OVER 3);

The DLM policy is named policy_orc. When the number of partitions exceeds 3, old partitions are archived in ORC format to OSS. The table becomes a hybrid partitioned table that contains both hot InnoDB partitions and cold ORC partitions.

Run the DLM policy

Insert test data into the sales_orc partitioned table to trigger INTERVAL to automatically create new partitions so that the number of partitions exceeds 3 and the DLM policy condition is met.

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

Run the DLM policy:

CALL dbms_dlm.execute_all_dlm_policies();

View the archiving result

Run the following command to view DLM policy execution records in the mysql.dlm_progress table:

SELECT * FROM mysql.dlm_progress\G

Partitions that store infrequently accessed cold data have been archived to OSS. Run the following command to view the schema of the sales_orc table:

SHOW CREATE TABLE sales_orc\G

The result is as follows:

*************************** 1. row ***************************
       Table: sales_orc
CREATE TABLE `sales_orc` (
  `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 /*!99990 800020223 OSS META=1 */ 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 = ORC,
 PARTITION p20210101000000 VALUES LESS THAN ('2021-01-01 00:00:00') ENGINE = ORC,
 PARTITION p20220101000000 VALUES LESS THAN ('2022-01-01 00:00:00') ENGINE = ORC,
 PARTITION _p20230101000000 VALUES LESS THAN ('2023-01-01 00:00:00') ENGINE = ORC,
 PARTITION _p20240101000000 VALUES LESS THAN ('2024-01-01 00:00:00') ENGINE = ORC,
 PARTITION _p20250101000000 VALUES LESS THAN ('2025-01-01 00:00:00') ENGINE = ORC,
 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)

As shown in the query result, the p20210101000000, p20220101000000, _p20230101000000, _p20240101000000, and _p20250101000000 partitions of the sales_orc partitioned table have been archived to OSS. Only the three hot data partitions _p20260101000000, _p20270101000000, and _p20280101000000 remain in the InnoDB engine. The sales_orc table is now a hybrid partitioned table. For information on how to query data in a hybrid partitioned table, see Query a hybrid partitioned table.