HDFS小文件治理总结，你学会了吗？

set hive.input.format = org.apache.hadoop.hive.ql.io.CombineHiveInputFormat;
set hive.merge.mapfiles = true;
set hive.merge.mapredfiles = true;
set hive.merge.smallfiles.avgsize=256000000;
set hive.merge.size.per.task=12800000;
set mapred.max.split.size=256000000;
set mapred.min.split.size=64000000;
set mapred.min.split.size.per.node=64000000;
set mapred.min.split.size.per.rack=64000000;

hdfs oiv -i  fsimage_0000000192578352133 -o /data2/data/fsimage/$day/fsimage.txt -p Delimited -t /data2/data/fsimage/$day/tmp

INODE_ID：文件或目录的唯一标识符；
NAME：文件或目录的名称；
PARENT_ID：父目录的INODE_ID；
MODIFICATION_TIME：最后修改时间；
ACCESS_TIME：最后访问时间；
BLOCK_IDS：文件的数据块ID列表；
BLOCK_SIZE：数据块大小；
NUM_BLOCKS：数据块数量；
PERMISSIONS：文件或目录的权限信息；
USER_NAME：文件或目录所属用户；
GROUP_NAME：文件或目录所属用户组；
SYMLINK：如果是符号链接，则包含符号链接的目标路径；
UNDER_CONSTRUCTION：如果文件正在写入中，则为true；
UNDER_RECOVERY：如果文件正在恢复中，则为true；
FILE_LENGTH：文件长度；
NS_QUOTA：命名空间配额；
DS_QUOTA：磁盘配额；
STORAGE_POLICY：存储策略。

CREATE TABLE `tmp_fsimage_info_log_clean`(
  `path` string, 
  `replication` int, 
  `db` string, 
  `table_name` string, 
  `parttition_nm1` string, 
  `parttition_nm2` string, 
  `parttition_nm3` string, 
  `file_name` string, 
  `modificationtime` string, 
  `accesstime` string, 
  `preferredblocksize_mb` decimal(20,5), 
  `blockscount` int, 
  `filesize_mb` decimal(20,5), 
  `filesize_gb` decimal(20,5), 
  `username` string, 
  `groupname` string)
PARTITIONED BY ( 
  `stat_day` string COMMENT '分区时间:yyyy-mm-dd')
ROW FORMAT SERDE 
  'org.apache.hadoop.hive.ql.io.orc.OrcSerde' 
STORED AS INPUTFORMAT 
  'org.apache.hadoop.hive.ql.io.orc.OrcInputFormat' 
OUTPUTFORMAT 
  'org.apache.hadoop.hive.ql.io.orc.OrcOutputFormat'

env {
  spark.app.name = "hive_to_clickhouse"
  spark.executor.instances = 10
  spark.executor.cores = 4
  spark.executor.memory = "12g"
}


source {
  file {
    path = "hdfs://ds01:8020/user/hive/warehouse/paas_test.db/db_table_file_all/stat_day="${day}""   
    result_table_name = "db_table_file_all"
    format = "orc"
  }
}


transform {
 sql {
    sql = "SELECT  '"${dayStr}"' as stat_day, _col0 as db, _col1 as table_name,_col2 as sum_filesize,_col3 as count_filesize,_col4 as count_filesize_less_10,_col5 as count_filesize_less_50,_col6 as count_filesize_less_150,_col7 as count_filesize_less_250,_col8 as count_filesize_less_300,_col9 as count_filesize_less_10_diff,_col10 as count_filesize_less_50_diff,_col11  as count_filesize_less_150_diff,_col12 as count_filesize_less_250_diff,_col13 as count_filesize_less_300_diff,_col14 as table_storage_increase,_col15 as count_filesize_diff,_col16 as latest_accesstime  from db_table_file_all"
 }
}




sink {
  clickhouse {
    host = "ds03:8123"
    clickhouse.socket_timeout = 50000
    database = "fsimage_info"
    table = "db_table_file_all"
    fields = ["db","table_name","sum_filesize","count_filesize","count_filesize_less_10","count_filesize_less_50","count_filesize_less_150","count_filesize_less_250","count_filesize_less_300","count_filesize_less_10_diff","count_filesize_less_50_diff","count_filesize_less_150_diff","count_filesize_less_250_diff","count_filesize_less_300_diff","table_storage_increase","count_filesize_diff","latest_accesstime","stat_day"]
    username = ""
    password = ""
  }


}