The ability to store data on Object Storage and retrieve it dynamically when necessary is a notable advantage of Lakehouse when managing MySQL historical data we would like to archive.
Let’s illustrate this with the following table:
CREATE TABLE `temperature_history` (
`id` bigint unsigned NOT NULL AUTO_INCREMENT,
`time_stamp` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
`device_id` varchar(30) DEFAULT NULL,
`value` decimal(5,2) NOT NULL DEFAULT '0.00',
`day_date` date GENERATED ALWAYS AS (cast(`time_stamp` as date)) STORED NOT NULL,
PRIMARY KEY (`id`,`day_date`),
KEY `device_id_idx` (`device_id`)
) ENGINE=InnoDB AUTO_INCREMENT=129428417 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci SECONDARY_ENGINE=RAPID
/*!50500 PARTITION BY RANGE COLUMNS(day_date)
(PARTITION p0_before2023_11 VALUES LESS THAN ('2023-11-01') ENGINE = InnoDB,
PARTITION p2023_12 VALUES LESS THAN ('2023-12-01') ENGINE = InnoDB,
PARTITION p2024_01 VALUES LESS THAN …
[Read more]