hive参数调整及优化

1.hive表查空间及其占用空间大小命令

1.1.Hive下查看数据表信息的方法

方法1：查看表的字段信息

desc table_name;

方法2：查看表的字段信息及元数据存储路径

desc extended table_name;

方法3：查看表的字段信息及元数据存储路径

desc formatted table_name;

备注：查看表元数据存储路径是，推荐方法3，信息比较清晰。

1.2.查看表容量大小

方法1：查看一个hive表文件总大小时(单位为Byte)，我们可以通过一行脚本快速实现，其命令如下：
–#查看普通表的容量

$ hadoop fs -ls  /user/hive/warehouse/table_name | awk -F ' ' '{print $5}'|awk '{a+=$1}END {print a}'
48

这样可以省去自己相加，下面命令是列出该表的详细文件列表

$ hadoop fs -ls  /user/hive/warehouse/table_name

查看分区表的容量

$ hadoop fs -ls  /user/hive/warehouse/table_name/yyyymm=201601 | awk -F ' ' '{print $5}'|awk '{a+=$1}END {print a/(1024*1024*1024)}'
39.709

这样可以省去自己相加，下面命令是列出该表的详细文件列表

$ hadoop fs -ls  /user/hive/warehouse/table_name/yyyymm=201601

方法2：查看该表总容量大小，单位为G

$ hadoop fs -du /user/hive/warehouse/table_name|awk '{ SUM += $1 } END { print SUM/(1024*1024*1024)}'

方法3：

$ hadoop fs -du /user/hive/warehouse/table_name/ | awk '{ sum=$1 ;dir2=$2 ; hum[1024**3]="Gb";hum[1024**2]="Mb";hum[1024]="Kb"; for (x=1024**3; x>=1024; x/=1024){ if (sum>=x) { printf "%.2f %s \t %s\n",sum/x,hum[x],dir2;break } }}'

1.3.查询hive库下所有表的大小

hadoop fs -count -q -h /user/hive/warehouse/xxxxxx.db/\*

1.4.查询指定目录下存储量最大的top20，即前20条记录

hadoop fs -du /user/hive/warehouse/xxxxxx.db/ | sort -nr | head -20

2.数据倾斜调试

2.1.自动mapjoin开关参数调整

set hive.groupby.skewindata=true; #倾斜开关
set hive.exec.reducers.bytes.per.reducer= 800000000; #提高并行度
-- 队列充足情况下适当增加
set mapreduce.reduce.memory.mb=6144; #适当增加（默认4096）
set mapreduce.reduce.java.opts=-Xmx4916m;

2.2.输入文件合并参数

set mapreduce.input.fileinputformat.split.maxsize=256000000;（默认256M）
set mapreduce.input.fileinputformat.split.minsize.per.node=256000000;
set mapreduce.input.fileinputformat.split.minsize.per.rack=256000000;

2.3.输出文件合并参数

-- 在Map-only的任务结束时合并小文件，建议开启
set hive.merge.mapfiles=true;
-- 在Map-Reduce任务结束时合并小文件，建议开启
set hive.merge.mapredfiles=true;

2.4.hive关联主键数据倾斜的一种处理方法

使用拼接随机数+取模的方法，即concat(a.col,‘-’,ceil(rand()*100)%20),将记录数特别多的关联字段取模20，得到新字段a.col2；另外维护一张有20条记录的小表，小表的数据从0到19，用b表的关联字段关联这张小表得到新的字段b.col2，然后再用这个新字段b.col2关联a表的新字段a.col2.
这样，相当于a表的值特别多的记录，分散为了20份，这个分为多少份可以根据实际情况调整。

2.5.hive关联主键字段类型不一致导致数据倾斜

在实际开发过程中还遇到过主键类型不一致导致数据倾斜，刚开始以为是关联键有重复导致的，但查询数据发现关联键未出现重复数据，后来无意间听到同事说会不会是关联键字段类型不一致导致的，然后我查了关联的两张表的表结构发现，a表的字段为bigint类型，b表的字段类型为string类型，然后我抱着试一试的态度，在关联的时候字段类型进行强转一下cast(a.col1 as string)=b.col，然后执行脚本，结果居然跑通过了，中间未出现数据倾斜。后来查阅资料得知，关联键类型不一致确实是也会导致数据倾斜，这个hive自带的隐式转换有关，所以在关联的时候要保证字段类型一致，否则就有可能出现数据倾斜，特此记录一下。

3.hive时间戳不支持13位

注：
1.值得注意的是，时间戳有可能是毫秒级的，
然后这时候直接使用from_unixtime(1441565203,‘yyyy/MM/dd HH:mm:ss’)的话
就会得到很奇怪的日期了，这时候要这样from_unixtime(cast(151331629920/1000 as int))，
同样的，时间转成毫秒级的时间戳也要乘以1000，如：unix_timestamp(‘2018-12-18 00:38:50’)*1000

2.如何区分时间戳是秒级还是毫秒级呢？
一般来说，常见的时间戳是10位数的，13位数的时间戳就是毫秒级的

hive是不支持13位的时间戳的（毫秒级）,只支持10位的时间戳（常规）
解决方式:
1,除以1000后,转化为bigint

select from_unixtime(cast(1483675004884/1000 as bigint),"yyyy-MM-dd HH:mm:ss") as dt;

4.sql中取当前记录的上一条记录或下一条记录：

lag() over() ：取出前n行数据，partition by 可选，order by 必选
lead() over() ：取出后n行数据，partition by 可选，order by 必选

eg:

select ename,eage,lag(eage,1,0) over(order by salary), 
lead(eage,1,0) over(order by salary) from emp;

select ename,eage,lag(eage,1) over(partition by esex order by salary),
lead(eage,1) over(partition by esex order by salary) from emp;

5.分区修复：

使用msck repair table new_table命令，修复新表的分区元数据（分区表的必备），new_table是分区表。

msck repair table tmp_db.new_table;

6.hive时间格式转换：

在解析埋点数据时会遇到两种不同的日期格式：yyyyMMdd和yyyy-MM-dd，此类型之间的转换主要有两种思路：
yyyyMMdd -> yyyy-MM-dd

6.1.第一种方法：

from_unixtime+unix_timestamp:

select from_unixtime(unix_timestamp('20180905','yyyyMMdd'),'yyyy-MM-dd') as dt;
select from_unixtime(unix_timestamp('20180905','yyyyMMdd'),'yyyy-MM-dd HH:mm:ss') as dt;

6.2.第二种方法：

substr + concat:

select concat(substr('20180905',1,4),'-',substr('20180905',5,2),'-',substr('20180905',7,2)) as d ;
select concat(substr(date_sub(from_unixtime(unix_timestamp(),'yyyy-MM-dd'),1),1,4),substr(date_sub(from_unixtime(unix_timestamp(),'yyyy-MM-dd'),1),6,2),substr(date_sub(from_unixtime(unix_timestamp(),'yyyy-MM-dd'),1),9,2)) as dt;
select from_unixtime(unix_timestamp(date_sub(from_unixtime(unix_timestamp(),'yyyy-MM-dd'),1),'yyyy-MM-dd'),'yyyyMMdd') as dt;

7.hive获取当前日期及当前时间戳:

select cast(current_date() as string) as dt;  --当前日期格式：yyyy-MM-dd，返回类型：string
select cast(current_timestamp() as string) as dt; --当前时间戳格式：yyyy-MM-dd HH:mm:ss.SSS，返回类型：string

8.hive表加字段语法：

alter table table_name add columns (c_time string comment '当前时间'); -- 正确，添加在最后
alter table tmp_db.test1_zwp change op_day op_day string comment '操作日期' after address ; --正确，移动到指定位置,address字段的后面

ALTER TABLE table_name CHANGE [CLOUMN] col_old_name col_new_name column_type [CONMMENT col_conmment] [FIRST|AFTER column_name];

9.null参与排序时的位置：

desc时排序，null排最后;
asc时排序，null排最前。

10.获取hive表的字段名

10.1.获取hive表的字段名,并使用逗号分隔，去掉表名脚本：

columns=$(hive -e "set hive.cli.print.header=true;
                  set mapred.job.queue.name=queue_0401.02;--设置队列，可选
                  select * from tmp_db.table_name limit 0;
                   " 2>/dev/null | sed -e "s/\t/,/g;s/data\.//g" | grep -v "WARN")
db_column=${columns//table_name./}
db_columns=${db_column/op_day,/}

或

columns=$(hive -e "set hive.cli.print.header=true;
                   set mapred.job.queue.name=queue_0401.02;
                   select * from tmp_db.table_name limit 0;
                   " 2>/dev/null | sed -e "s/\t/,/g;s/data\.//g;s/table_name\.//g" | grep -v "WARN")
echo ${columns}
db_col=${columns/op_day,/}
echo  $db_col

10.2.替换掉不需要的字符串：

${变量/查找/替换值} 一个“/”表示替换第一个，”//”表示替换所有,当查找中出现了：”/”请加转义符”/”表示。
demo：

db_column=${columns//table_name./}
db_columns=${columns/op_day,/}