hive按日期分区保留三年内月末数据sql怎么写

在Hive中，如果你想要保留三年内的每月末数据，并按照日期进行分区，你可以使用`PARTITIONED BY`和` retention`特性结合`ADD PARTITION`命令来实现。假设你的表名为`sales_data`，日期字段为`date`，并且已经有一个月份分区`month`，那么可以尝试以下SQL：

-- 创建一个新的分区目录，例如`monthly_archive`，用于保存每个月末的数据
CREATE TABLE sales_data_monthly_archive (
   ... -- 表结构
)
PARTITIONED BY (month STRING, year INT)
CLUSTERED BY (date) INTO 1 DAYSPECIFIC PARTITIONS
LOCATION 'path/to/monthly_archive';

-- 获取当前年份
SET @current_year = YEAR(CURRENT_DATE());

-- 获取三年前的年份
SET @three_years_ago = DATE_SUB(@current_year, INTERVAL 3 YEAR);

-- 删除超过三年的旧数据
MSCK REPAIR TABLE sales_data;

-- 逐月备份并添加新的分区，只保留最近三年的每月末数据
FOR d IN ('01', '02', ..., '12') LOOP
    SET @year = IF(@current_year <= @three_years_ago, @three_years_ago, @current_year);
    SET @month = CONCAT('0', d); -- 如果需要两位数的月份

    -- 示例：将本月末数据移动到新分区
    INSERT INTO TABLE sales_data_monthly_archive PARTITION (month = '@month', year = @year)
    SELECT * FROM sales_data WHERE month = CONCAT('0', d) AND date = CONCAT(year, '-', month, '-30');
END LOOP;

-- 清理原始表，只保留最新分区的数据
ALTER TABLE sales_data ADD PARTITION (month='12', year=@current_year) location 'path/to/original';
ANALYZE TABLE sales_data COMPUTE STATISTICS;
TRUNCATE TABLE sales_data PARTITION(month, year);

-- 将新分区设置为默认分区
ALTER TABLE sales_data_monthly_archive SET DEFAULT partitions (month='12', year=@current_year);