如何用Hive SQL计算Hadoop FS的各类健康指标？

嘿，我来帮你搞定这些Hive SQL分析的需求！首先得先确认下你用来存Hadoop FS元数据的Hive表结构，我假设你的表名叫hadoop_fs_metadata，包含这些核心字段：table_name（对应Hive表名，若为原始路径可替换为file_path）、file_size（文件大小，单位字节）、modification_time（文件/文件夹最后修改时间戳）、is_directory（布尔值，标记是否为文件夹）。如果你的字段名不一样，稍微调整下就行。下面是每个指标的实现方案：

最快增长的表通常看单位时间内的新增数据量，比如按天/小时统计增量，再计算平均增速。这里以最近7天的日增速为例：

-- 按天统计每个表的日新增数据量，计算日均增速
WITH daily_table_growth AS (
    SELECT
        table_name,
        date(modification_time) AS stat_date,
        SUM(file_size) AS daily_total_size
    FROM hadoop_fs_metadata
    WHERE is_directory = FALSE  -- 只统计文件，排除文件夹
      AND modification_time >= date_sub(current_date(), 7)  -- 取最近7天数据
    GROUP BY table_name, date(modification_time)
),
daily_growth_rate AS (
    SELECT
        table_name,
        AVG(daily_total_size - LAG(daily_total_size) OVER (PARTITION BY table_name ORDER BY stat_date)) AS avg_daily_growth
    FROM daily_table_growth
    GROUP BY table_name
)
SELECT
    table_name,
    avg_daily_growth,
    concat(round(avg_daily_growth / 1024 / 1024, 2), ' MB/天') AS readable_growth_rate
FROM daily_growth_rate
WHERE avg_daily_growth IS NOT NULL  -- 排除只有一天数据的表
ORDER BY avg_daily_growth DESC
LIMIT 10;  -- 取增速最快的前10个表

要是想按小时统计，把date(modification_time)改成date_format(modification_time, 'yyyy-MM-dd HH')就行。

增长幅度指的是**（当前总大小 - 历史某时间点总大小）/ 历史总大小 × 100%**，这里对比最近7天和之前7天的表大小：

-- 计算最近7天内每个表的增长幅度（当前总大小 vs 7天前总大小）
WITH current_table_size AS (
    SELECT
        table_name,
        SUM(file_size) AS current_total
    FROM hadoop_fs_metadata
    WHERE is_directory = FALSE
      AND modification_time >= date_sub(current_date(), 7)
    GROUP BY table_name
),
past_table_size AS (
    SELECT
        table_name,
        SUM(file_size) AS past_total
    FROM hadoop_fs_metadata
    WHERE is_directory = FALSE
      AND modification_time BETWEEN date_sub(current_date(), 14) AND date_sub(current_date(), 7)
    GROUP BY table_name
)
SELECT
    c.table_name,
    concat(round((c.current_total - p.past_total) / p.past_total * 100, 2), '%') AS growth_percentage,
    concat(round((c.current_total - p.past_total)/1024/1024/1024,2), ' GB') AS absolute_growth
FROM current_table_size c
JOIN past_table_size p ON c.table_name = p.table_name
WHERE p.past_total > 0  -- 排除历史大小为0的表
ORDER BY (c.current_total - p.past_total)/p.past_total DESC
LIMIT 10;

需要对比更早的时间？直接调整date_sub的参数就行。

我们可以把文件按大小区间划分，统计每个区间的文件数、总大小和占比，帮你快速掌握小文件/大文件的分布：

-- 统计文件大小分布情况
SELECT
    CASE
        WHEN file_size = 0 THEN '0字节（空文件）'
        WHEN file_size < 1024*1024 THEN '0-1MB'
        WHEN file_size < 1024*1024*10 THEN '1MB-10MB'
        WHEN file_size < 1024*1024*100 THEN '10MB-100MB'
        ELSE '100MB+'
    END AS file_size_range,
    COUNT(*) AS file_count,
    SUM(file_size) AS total_size,
    concat(round(COUNT(*) / (SELECT COUNT(*) FROM hadoop_fs_metadata WHERE is_directory = FALSE) * 100, 2), '%') AS file_count_ratio,
    concat(round(SUM(file_size) / (SELECT SUM(file_size) FROM hadoop_fs_metadata WHERE is_directory = FALSE) * 100, 2), '%') AS size_ratio
FROM hadoop_fs_metadata
WHERE is_directory = FALSE
GROUP BY
    CASE
        WHEN file_size = 0 THEN '0字节（空文件）'
        WHEN file_size < 1024*1024 THEN '0-1MB'
        WHEN file_size < 1024*1024*10 THEN '1MB-10MB'
        WHEN file_size < 1024*1024*100 THEN '10MB-100MB'
        ELSE '100MB+'
    END
ORDER BY
    CASE
        WHEN file_size_range = '0字节（空文件）' THEN 1
        WHEN file_size_range = '0-1MB' THEN 2
        WHEN file_size_range = '1MB-10MB' THEN 3
        WHEN file_size_range = '10MB-100MB' THEN 4
        ELSE 5
    END;

要是需要更细的区间，直接修改CASE里的阈值就行。

空文件夹就是没有任何子文件或子文件夹的目录，我们可以通过关联表自身来判断：

-- 找出所有空文件夹
SELECT
    d.file_path AS empty_folder_path
FROM hadoop_fs_metadata d
WHERE d.is_directory = TRUE
  AND NOT EXISTS (
      SELECT 1
      FROM hadoop_fs_metadata c
      WHERE c.file_path LIKE concat(d.file_path, '/%')  -- 匹配所有子路径
        AND (c.is_directory = FALSE OR c.is_directory = TRUE)  -- 只要有子内容就不算空
  );

如果你的元数据里文件夹路径不带结尾斜杠，把concat(d.file_path, '/%')改成concat(d.file_path, '%')即可。

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