hive 小文件分析

1、获取fsimage文件：
hdfs dfsadmin -fetchImage /data/xy/
2、从二进制文件解析：
hdfs oiv -i /data/xy/fsimage_0000000019891608958 -t /data/xy/tmpdir -o /data/xy/out -p Delimited -delimiter “,”
3、创建hive表
create database if not exists hdfsinfo;
use hdfsinfo;
CREATE TABLE fsimage_info_csv(
path string,
replication int,
modificationtime string,
accesstime string,
preferredblocksize bigint,
blockscount int,
filesize bigint,
nsquota string,
dsquota string,
permission string,
username string,
groupname string)
ROW FORMAT SERDE ‘org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe’
WITH SERDEPROPERTIES (‘field.delim’=‘,’, ‘serialization.format’=‘,’)
STORED AS INPUTFORMAT ‘org.apache.hadoop.mapred.TextInputFormat’;

4、存储HDFS元数据加载进hive中
hdfs dfs -put /data/xy/out /user/hive/warehouse/hdfsinfo.db/fsimage_info_csv/
hdfs dfs -ls /user/hive/warehouse/hdfsinfo.db/fsimage_info_csv/
Hive: MSCK REPAIR TABLE hdfsinfo.fsimage_info_csv;
select * from hdfsinfo.fsimage_info_csv limit 5;

5、统计叶子目录下小文件数据量（4194304 H字节,即<4M）
SELECT
dir_path ,
COUNT(*) AS small_file_num,
modificationtime,
accesstime
FROM
( SELECT
modificationtime,
accesstime,
relative_size,
dir_path
FROM
(
SELECT
(CASE filesize < 4194304 WHEN TRUE THEN ‘small’ ELSE ‘large’ END) AS relative_size,
modificationtime,
accesstime,
split(
substr(
concat_ws(‘/’, split(PATH, ‘/’)),
1,
length(concat_ws(‘/’, split(PATH, ‘/’))) - length(last_element) - 1
),
‘,’)[0] as dir_path
FROM (
SELECT
modificationtime,
accesstime,
filesize,
PATH,
split(PATH, ‘/’)[size(split(PATH, ‘/’)) - 1] as last_element
FROM hdfsinfo.fsimage_info_csv
) t0 ) t1
WHERE
relative_size=‘small’) t2
GROUP BY
dir_path,modificationtime,accesstime
ORDER BY
small_file_num desc
limit 500;

5、统计叶子目录下小文件数据量（4194304 H字节,即<4M）
SELECT
dir_path,
COUNT(*) AS small_file_num
FROM
( SELECT
relative_size,
dir_path
FROM
(
SELECT
(CASE filesize < 41943040 WHEN TRUE THEN ‘small’ ELSE ‘large’ END) AS relative_size,
split(
substr(
concat_ws(‘/’, split(PATH, ‘/’)),
1,
length(concat_ws(‘/’, split(PATH, ‘/’))) - length(last_element) - 1
),
‘,’)[0] as dir_path
FROM (
SELECT
filesize,
PATH,
split(PATH, ‘/’)[size(split(PATH, ‘/’)) - 1] as last_element
FROM hdfsinfo.fsimage_info_csv
WHERE
permission not LIKE ‘d%’
) t0 ) t1
WHERE
relative_size=‘small’) t2
GROUP BY
dir_path
ORDER BY
small_file_num desc
limit 50000;