
一、触发器内容
DELIMITER $$
create trigger set_partition after insert on `PARTITIONS` for each row
begin
DECLARE db VARCHAR(255);
DECLARE tbl VARCHAR(255);
DECLARE part VARCHAR(255);
DECLARE old_dt VARCHAR(255);
DECLARE new_dt VARCHAR(255);
SET part=SUBSTR(SUBSTRING_INDEX( NEW.PART_NAME, "/", 1 ), 15);
SET new_dt = RIGHT(NEW.PART_NAME, 10);
SELECT
c.`NAME` AS db_name,
b.`TBL_NAME` AS tbl_name
INTO db,tbl
FROM hive.`PARTITIONS` a
LEFT JOIN hive.TBLS b ON a.TBL_ID = b.TBL_ID
LEFT JOIN hive.DBS c ON b.DB_ID = c.DB_ID
WHERE a.`TBL_ID` = NEW.TBL_ID LIMIT 1;
SELECT dt INTO old_dt FROM hive_udf.partition_info WHERE `DB_NAME`=db AND `TBL_NAME`=tbl AND `PART_NAME`=part;
IF old_dt IS NULL OR old_dt='' THEN
INSERT INTO hive_udf.partition_info VALUES (db, tbl, part, new_dt, null);
ELSEIF old_dt < new_dt THEN
UPDATE hive_udf.partition_info SET dt=new_dt, old_dt=old_dt WHERE `DB_NAME`=db AND `TBL_NAME`=tbl AND `PART_NAME`=part;
END IF;
end
$$
DELIMITER ;
二、自定义分区表
CREATE TABLE hive_udf.`partition_info` (
`db_name` varchar(200) DEFAULT NULL,
`tbl_name` varchar(200) DEFAULT NULL,
`part_name` varchar(200) DEFAULT NULL,
`dt` varchar(200) DEFAULT NULL,
`old_dt` varchar(200) DEFAULT NULL,
UNIQUE KEY `pk_index` (`db_name`,`tbl_name`,`part_name`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;
三、初始化分区数据
SET @rank := 0;
SET @key := '';
INSERT INTO hive_udf.partition_info
SELECT
new.DB_NAME,
new.TBL_NAME,
new.PART_NAME,
new.dt,
old.dt AS old_dt
FROM (
SELECT
DB_NAME,
TBL_NAME,
PART_NAME,
MAX(dt) as dt
FROM (
SELECT
a. part_id AS `PART_ID`,
c.`NAME` AS `DB_NAME`,
b.`TBL_NAME` AS `TBL_NAME`,
SUBSTR(SUBSTRING_INDEX( a.PART_NAME, "/", 1 ), 15) AS `PART_NAME`,
RIGHT(a.PART_NAME, 10) AS dt
FROM hive.`PARTITIONS` a
LEFT JOIN hive.TBLS b ON a.TBL_ID = b.TBL_ID
LEFT JOIN hive.DBS c ON b.DB_ID = c.DB_ID
) s1 GROUP BY DB_NAME,TBL_NAME,PART_NAME
) new
LEFT JOIN (
SELECT * FROM
(
SELECT
DB_NAME,
TBL_NAME,
PART_NAME,
dt,
IF(@key = CONCAT(DB_NAME,TBL_NAME,PART_NAME), @rank := @rank + 1, @rank := 1) AS rank,
@key := CONCAT(DB_NAME,TBL_NAME,PART_NAME) AS `KEY`
FROM (
SELECT
c.`NAME` AS `DB_NAME`,
b.`TBL_NAME` AS `TBL_NAME`,
SUBSTR(SUBSTRING_INDEX( a.PART_NAME, "/", 1 ), 15) AS `PART_NAME`,
RIGHT(a.PART_NAME, 10) AS dt
FROM hive.`PARTITIONS` a
LEFT JOIN hive.TBLS b ON a.TBL_ID = b.TBL_ID
LEFT JOIN hive.DBS c ON b.DB_ID = c.DB_ID
) q1 ORDER BY DB_NAME,TBL_NAME,PART_NAME, dt DESC
) q2 WHERE rank=2
) old ON new.DB_NAME=old.DB_NAME AND new.TBL_NAME=old.TBL_NAME AND new.PART_NAME=old.PART_NAME;