求Hive按年、月、日分区表的动态获取最新分区数据SQL

场景说明 #

我有一张按year、month、day三列分区的Hive表，数据存储路径示例如下：

/table1/year=2024/month=1/day=20/file1
/table1/year=2024/month=1/day=16/file1
/table1/year=2024/month=1/day=9/file1

原本使用硬编码查询最新分区数据：

SELECT * FROM table1 WHERE year=2024 AND month=1 AND day=20;

需要将其改为动态SQL，自动识别并获取最新年、月、日分区的所有记录。

解决方案 #

方法1：纯Hive SQL动态筛选（推荐） #

通过拼接日期字符串并取最大值的方式定位最新分区，无需依赖外部工具：

SELECT *
FROM table1
WHERE concat(year, '-', lpad(month, 2, '0'), '-', lpad(day, 2, '0')) = (
    SELECT max(concat(year, '-', lpad(month, 2, '0'), '-', lpad(day, 2, '0')))
    FROM table1
);

• 说明：lpad函数用来给月份、日期补零（如month=1转为01），确保拼接后的日期字符串格式统一，能正确按字典序比较大小，从而获取最新的分区日期。

方法2：通过分组排序获取最新分区 #

先分组所有分区并按时间倒序取第一条，再关联主表获取数据：

WITH latest_partition AS (
    SELECT 
        year, 
        month, 
        day
    FROM table1
    GROUP BY year, month, day
    ORDER BY cast(year AS int) DESC, cast(month AS int) DESC, cast(day AS int) DESC
    LIMIT 1
)
SELECT t.* 
FROM table1 t
JOIN latest_partition lp 
ON t.year = lp.year 
AND t.month = lp.month 
AND t.day = lp.day;

• 说明：将分区字段转为整数后排序，避免字符串排序可能出现的异常（如day=9和day=20的字符串排序问题）。

方法3：Shell脚本结合Hive SQL（适合自动化场景） #

如果需要在脚本中自动执行，可通过HDFS命令获取最新分区路径，提取分区值后拼接SQL：

# 获取最新分区的HDFS路径
latest_partition_path=$(hdfs dfs -ls /table1 | grep -v "^Found" | sort -r | head -1 | awk '{print $NF}')

# 从路径中提取year、month、day的值
year=$(echo $latest_partition_path | awk -F'/' '{print $3}' | cut -d'=' -f2)
month=$(echo $latest_partition_path | awk -F'/' '{print $4}' | cut -d'=' -f2)
day=$(echo $latest_partition_path | awk -F'/' '{print $5}' | cut -d'=' -f2)

# 执行Hive查询
hive -e "SELECT * FROM table1 WHERE year='$year' AND month='$month' AND day='$day';"

• 说明：该方式直接读取HDFS目录排序后的第一条结果，适合分区目录严格按时间创建的场景。

内容的提问来源于stack exchange，提问作者Santhoshhadoop