hive杂记

#在beeline内部执行两个参数设置，即可格式化
!set showheader false
!set outputformat tsv2


#加载数据忽略行首或行尾
create table test
(
id int commet 'id',
name string commet '姓名'
) comment '测试表'
tblproperties(
"skip.header.line.count"="1",  --跳过文件行首1行
"skip.footer.line.count"="1"   --跳过文件行尾1行
);

hive优化：

1.列区裁剪

2.谓词下推：即where语句尽量提前执行,提前做筛选

**3.使用sortby 而不是order by,**order by 会将数据全局排序，这会导致独有map端数据都进入到一个reducer中,并且sort by可以配合distribute by 进行使用。这样就可以保证多个reducer中局部有序。

4.group by 代替 count(distinct) ，使用count(distinct)会产生很少的 reducer,如果遇到多个count(distinct)字段 可以使用下列语句

 select t.a,sum(t.b),count(t.c),count(t.d) from (
  select a,b,null c,null d from some_table
  union all
  select a,0 b,c,null d from some_table group by a,c
  union all
  select a,0 b,null c,d from some_table group by a,d
) t; 

5.group by 配置调整
（1）map端预聚合：
在map端聚合时，就会减少shuffle的数据量，此处的默认配置项为hive.map.aggr=true,对应的优化器为GroupByOptimizer，简单方便。
通过hive.groupby.mapaggr.checkinterval参数也可以设置map端预聚合的行数阈值，超过该值就会分拆job，默认值100000
（2）倾斜配置
hive.groupby.skewindata，默认值false，此配置将任务拆分为2个job，第一个job会将map端数据随机发送到reducer,做部分聚合，然后再启动第二个job再进行聚合
6.join阶段优化：
（1）build table(小表) 前置 --此处有疑问：当多张表关联时，build table和probe table 怎么区分？？？？
通常会将最后一个表当做probe table,即大表，将前面的表当做build table,这样就会将build table加载到内存中，如果将大小表顺序倒置，极可能会产生oom。
（2）多表join时key相同
如果多张表关联时，使用的key相同时，就会产生一个mr job
(3) map join
当其中有表数据量很小时，则会在map端进行关联，从而取消了reduce阶段，提高了效率，通过hive.auto.covert.join=true;进行配置，此值为默认项。
还有hive.mapjoin.smalltable.filesize

#hive合并小文件

1. Map输入合并小文件
   对应参数：
   set mapred.max.split.size=256000000; #每个Map最大输入大小
   set mapred.min.split.size.per.node=100000000; #一个节点上split的至少的大小
   set mapred.min.split.size.per.rack=100000000; #一个交换机下split的至少的大小
   set hive.input.format=org.apache.hadoop.hive.ql.io.CombineHiveInputFormat; #执行Map前进行小文件合并

在开启了org.apache.hadoop.hive.ql.io.CombineHiveInputFormat后，一个data node节点上多个小文件会进行合并，合并文件数由mapred.max.split.size限制的大小决定。
mapred.min.split.size.per.node决定了多个data node上的文件是否需要合并~
mapred.min.split.size.per.rack决定了多个交换机上的文件是否需要合并~

2.输出合并
set hive.merge.mapfiles = true #在Map-only的任务结束时合并小文件
set hive.merge.mapredfiles = true #在Map-Reduce的任务结束时合并小文件
set hive.merge.size.per.task = 25610001000 #合并文件的大小
set hive.merge.smallfiles.avgsize=16000000 #当输出文件的平均大小小于该值时，启动一个独立的map-reduce任务进行文件merge

3.hive写入到本地或者hdfs中

insert overwrite  directory 'hdfs://ns2/user/bdoc/8755/hive/iotroam/getdata4gnb_apn_tm_d6' row format delimited
fields terminated by ','   select prov_id,sum(card_cnt),sum(total_flux),sum(up_flux),sum(down_flux),day_time from 4GNB_APN_TM_D where day_time in (
'20210110') group by day_time, prov_id;