基于触发器获取Hive最新分区和上一分区

一、触发器内容

-- 创建触发器
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);
	
    -- 将新插入行的PART_NAME字段值赋给变量
	SET part=SUBSTR(SUBSTRING_INDEX( NEW.PART_NAME, "/", 1 ), 15);
	SET new_dt = RIGHT(NEW.PART_NAME, 10);
	-- 获取数据库名、表名，并赋值给变量db,tbl
	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;
	
	-- 从查询结果中获取old_dt并赋给变量
	SELECT dt INTO old_dt FROM hive_udf.partition_info WHERE `DB_NAME`=db AND `TBL_NAME`=tbl AND `PART_NAME`=part;
	-- 条件判断：如果old_dt不存在则-插入，否则old_dt小于new_dt则-更新
	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;

三、初始化分区数据

-- 初始化 分区表 （Mysql 5.7的写法，8.x之上可以用row_number函数）
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;