hive 实战总结

为什么80%的码农都做不了架构师？>>>   

hive 体系架构

启动 hive 命令行

    进入hive安装目录，输入bin/hive的执行程序，或者输入 hive –service cli

hive脚本的执行方式大致有三种

1. hive可以直接敲hive命令进入interactive模式，直接使用hive交互式模式 也可以hive -e 执行简单命令或者hive -f 执行一个sql脚本文件
2. hive -e “SQL”执行; eg : hive -e 'select * from t1' hive -e 'select * from t1' > test.txt
3. hive -f SQL文件执行；eg（适合多条sql组成的文件）: hive –f /root/shell/hive-script.sql

启动thrift服务

bin/hive --service metastore
metastore服务实际上就是一种thrift服务，通过它我们可以获取到hive原数据，并且通过thrift获取原数据的方式，屏蔽了数据库访问需要驱动，url，用户名，密码等等细节metastore database metastore server 端连接应该就是以客户端的形式连接 hive的metastore服务的 MetaStore是用来访问元数据的 有了metastore服务，就可以有多个客户端同时连接，而且这些客户端不需要知道MySQL数据库的用户名和密码，只需要连接metastore 服务即可内嵌模式（Embedded）-- 本地模式（Local） 远程模式（Remote）是否远程指的是metastore和hive服务是否在同一进程内

启动 hiveserver2

bin/hive --service hiveserver2 &（&表示后台运行）

用java，python等程序实现通过jdbc等驱动的访问hive就用这种起动方式了，这个是程序员最需要的方式了 HiveServer2 (HS2) 以使用jdbc，odbc，或者thrift的方式连接。 用java编码jdbc或则beeline连接使用jdbc的方式 hiveserver2提供了一个新的命令行工具Beeline，他是基于SQLLine CLI的JDBC客户端 其中jdbc是基于thrift接口封装 HiveServer2支持多个客户端的并发请求和授权的 HiveServer或者HiveServer2都是基于Thrift的 HS2对于TCP 模式使用TThreadPoolServer，对于HTTP模式使用JettyServer.

hive 架构原理

Hive执行流程

• 编译器将一个Hive QL转换操作符
• 操作符是Hive的最小的处理单元
• 每个操作符代表HDFS的一个操作或者一道MapReduce作业

Operator

• Operator都是hive定义的一个处理过程
• Operator都定义有:
• protected List <Operator<? extends Serializable >> childOperators;
• protected List <Operator<? extends Serializable >> parentOperators;
• protected boolean done; // 初始化值为false
• 所有的操作构成了 Operator图，hive正是基于这些图关系来处理诸如limit, group by, join等操作

Hive执行流程

• 操作符 描述
• TableScanOperator 扫描hive表数据
• ReduceSinkOperator 创建将发送到Reducer端的<Key,Value>对
• JoinOperator Join两份数据
• SelectOperator 选择输出列
• FileSinkOperator 建立结果数据,输出至文件
• FilterOperator 过滤输入数据
• GroupByOperator GroupBy语句
• MapJoinOperator /*+mapjoin(t) */
• LimitOperator Limit语句
• UnionOperator Union语句

UDF： hive 功能扩展 UDF开发，建议使用Spark SQL代替，减少此类开发便于维护。

编译原理

Hive编译器

编译流程：

具体编译过程实例：

(此处参考:https://www.cnblogs.com/nashiyue/p/5751102.html)

MapReduce实现基本SQL操作的原理

1.1 Join的实现原理

select u.name, o.orderid from order o join user u on o.uid = u.uid; 在map的输出value中为不同表的数据打上tag标记，在reduce阶段根据tag判断数据来源。MapReduce的过程如下（这里只是说明最基本的Join的实现，还有其他的实现方式）

1.2 Group By的实现原理

select rank, isonline, count(*) from city group by rank, isonline;
将GroupBy的字段组合为map的输出key值，利用MapReduce的排序，在reduce阶段保存LastKey区分不同的key。MapReduce的过程如下（当然这里只是说明Reduce端的非Hash聚合过程）

1.3 Distinct的实现原理

select dealid, count(distinct uid) num from order group by dealid; 当 只有一个distinct字段时，如果不考虑Map阶段的Hash GroupBy，只需要将GroupBy字段和Distinct字段组合为map输出key，利用mapreduce的排序，同时将GroupBy字段作 为reduce的key，在reduce阶段保存LastKey即可完成去重

如果有多个distinct字段呢，如下面的SQL

select dealid, count(distinct uid), count(distinct date) from order group by dealid; 实现方式有两种：

（1）如果仍然按照上面一个distinct字段的方法，即下图这种实现方式，无法跟据uid和date分别排序，也就无法通过LastKey去重，仍然需要在reduce阶段在内存中通过Hash去重

（2）第二种实现方式，可以对所有的distinct字段编号，每行数据生成n行数据，那么相同字段就会分别排序，这时只需要在reduce阶段记录LastKey即可去重。

这种实现方式很好的利用了MapReduce的排序，节省了reduce阶段去重的内存消耗，但是缺点是增加了shuffle的数据量。

需要注意的是，在生成reduce value时，除第一个distinct字段所在行需要保留value值，其余distinct数据行value字段均可为空。

2、SQL转化为MapReduce的过程

了解了MapReduce实现SQL基本操作之后，我们来看看Hive是如何将SQL转化为MapReduce任务的，整个编译过程分为六个阶段：

1. Antlr定义SQL的语法规则，完成SQL词法，语法解析，将SQL转化为抽象语法树AST Tree
2. 遍历AST Tree，抽象出查询的基本组成单元QueryBlock
3. 遍历QueryBlock，翻译为执行操作树OperatorTree
4. 逻辑层优化器进行OperatorTree变换，合并不必要的ReduceSinkOperator，减少shuffle数据量
5. 遍历OperatorTree，翻译为MapReduce任务
6. 物理层优化器进行MapReduce任务的变换，生成最终的执行计划

Hive内部表和外部表的区别

未被external修饰的是内部表（managed table），被external修饰的为外部表（external table）；

• 内部表数据由Hive自身管理，外部表数据由HDFS管理；
• 内部表数据存储的位置是hive.metastore.warehouse.dir（默认：/user/hive/warehouse），外部表数据的存储位置由自己制定；
• 删除内部表会直接删除元数据（metadata）及存储数据；删除外部表仅仅会删除元数据，HDFS上的文件并不会被删除；

行式存储vs列式存储

行式数据库存储在hdfs上式按行进行存储的，一个block存储一或多行数据。而列式数据库在hdfs上则是按照列进行存储，一个block可能有一列或多列数据。

如果要将数据进行压缩：

• 对于行式数据库，必然按行压缩，当一行中有多个字段，各个字段对应的数据类型可能不一致，压缩性能压缩比就比较差。
• 对于列式数据库，必然按列压缩，每一列对应的是相同数据类型的数据，故列式数据库的压缩性能要强于行式数据库。

如果要进行数据的查询： 假设执行的查询操作是：select id,name from table_emp;

• 对于行式数据库，它要遍历一整张表将每一行中的id,name字段拼接再展现出来，这样需要查询的数据量就比较大，效率低。
• 对于列式数据库，它只需找到对应的id,name字段的列展现出来即可，需要查询的数据量小，效率高。
  假设执行的查询操作是：select * from table_emp;

对于这种查询整个表全部信息的操作，由于列式数据库需要将分散的行进行重新组合，行式数据库效率就高于列式数据库。但是，在大数据领域，进行全表查询的场景少之又少，进而我们使用较多的还是列式数据库及列式储存。

Hive静态分区动态分区

分区的概念
Hive的分区方式：由于Hive实际是存储在HDFS上的抽象，Hive的一个分区名对应HDFS上的一个目录名，子分区名就是子目录名，并不是一个实际字段。
分区的好处
产生背景：如果一个表中数据很多，我们查询时就很慢，耗费大量时间，如果要查询其中部分数据该怎么办呢，这是我们引入分区的概念。 Partition:分区，每张表中可以加入一个分区或者多个，方便查询，提高效率；并且HDFS上会有对应的分区目录：
语法：
Hive分区是在创建表的时候用Partitioned by 关键字定义的，但要注意，Partitioned by子句中定义的列是表中正式的列， 但是Hive下的数据文件中并不包含这些列，因为它们是目录名，真正的数据在分区目录下。
静态分区和 动态分区的区别
创建表的语法都一样

静态分区：加载数据的时候要指定分区的值（key=value），比较麻烦的是每次插入数据都要指定分区的值，创建多个分区多分区一样，以逗号分隔。
动态分区：
如果用上述的静态分区，插入的时候必须首先要知道有什么分区类型，而且每个分区写一个load data，太烦人。使用动态分区可解决以上问题，其可以根据查询得到的数据动态分配到分区里。其实动态分区与静态分区区别就是不指定分区目录，由系统自己选择。

Hive和数据库的异同：

由于Hive采用了SQL的查询语言HQL，因此很容易将Hive理解为数据库。其实从结构上来看，Hive和数据库除了拥有类似的查询语言，再无类似之处。数据库可以用在Online的应用中，但是Hive是为数据仓库而设计的，清楚这一点，有助于从应用角度理解Hive的特性。 Hive和数据库的比较如下表：

常用语句

Hive数据类型转换

隐式转换和显式转换
implicit conversions and explicitly conversion INT BIGINT FLOAT DOUBLE STRING
隐式转换 bigint 可以隐式转换为 String
double 可以隐式转换为 string
STRING 可以隐式地转换成DOUBLE
varchar 也可以隐式地转换成DOUBLE
显示转换
cast(salaryString AS bigint)
cast(salaryvarchar AS bigint)
其余转换
- 如果将浮点型的数据转换成int类型的，

•      内部操作是通过round()或者floor()函数来实现的，而不是通过cast实现！
  

•  对于Date类型的数据，只能在Date、Timestamp以及String之间进行转换
  

•   对于BINARY类型的数据，只能将BINARY类型的数据转换成STRING类型。
  

•    如果你确信BINARY类型数据是一个数字类型(a number)，
  

•    这时候你可以利用嵌套的cast操作，比如a是一个BINARY，且它是一个数字类型
         cast(cast(a as string) as double)
  

毫秒时间戳转日期时间 from_unixtime

from_unixtime(cast(substr(min(s1.createtime), 0, 10) as bigint), 'yyyy-MM-dd HH:mm:ss') as c_o_starttime

日期转时间戳

unix_timestamp('2018-12-07 13:01:03')

hive 列转行

列转行 (对某列拆分，一列拆多行)
使用函数：lateral view explode(split(column, ',')) num
如：
select id, tagid, createtime from xxx_dd_comment_main lateral view explode(split(tagid, ',')) num as tag_new where parttag >='2019-03-04'

hive行转列

(根据主键，进行多行合并一列)

使用函数：concat_ws(',',collect_set(column))

说明：collect_list 不去重，collect_set 去重。 column 的数据类型要求是 string

select id, concat_ws(',',collect_set(tag_new)) as tag_col from XXX_news_prop group by id;

Hive row_number() 等用法

（1） row_number() over()分组排序功能：

 在使用 row_number() over()函数时候，over()里头的分组以及排序的执行晚于 where group by  order by 的执行。

partition by 用于给结果集分组，如果没有指定那么它把整个结果集作为一个分组，它和聚合函数不同的地方在于它能够返回一个分组中的多条记录，而聚合函数一般只有一个反映统计值的记录。

如：获取系统中不同用户的最新一个手机号码
select temp.systemid, temp.userno, temp.phoneno from( select systemid, userno, phoneno, row_number() over (partition by userno order by createtime desc) as ordnum from xxx_da_userphoneinfo )temp where temp.ordnum = 1

SELECT empno,WORKDEPT,SALARY, Row_Number() OVER (partition by workdept ORDER BY salary desc) rank FROM employee

C01 98250 1
C01 73800 2

（2） rank() over()是跳跃排序，有两个第二名时接下来就是第四名（同样是在各个分组内）
select workdept,salary,rank() over(partition by workdept order by salary) as dense_rank_order from emp order by workdept;
C01 68420 1
C01 68420 1
C01 73800 3

（3） dense_rank() over()是连续排序，有两个第二名时仍然跟着第三名。相比之下row_number是没有重复值的

select workdept,salary,dense_rank() over(partition by workdept order by salary) as dense_rank_order from emp order by workdept

C01 68420 1
C01 68420 1
C01 73800 2
C01 98250 3

hive 存储格式

1、TestFile  TextFile文件不支持块压缩，默认格式，数据不做压缩，磁盘开销大，数据解析开销大。这边不做深入介绍。

2、RCFile  Record Columnar的缩写。是Hadoop中第一个列文件格式。能够很好的压缩和快速的查询性能，但是不支持模式演进。通常 写操作比较慢，比非列形式的文件格式需要更多的内存空间和计算量。  RCFile是一种行列存储相结合的存储方式。首先，其将数据按行分块，保证同一个record在一个块上，避免读一个记录需要读 取多个block。其次，块数据列式存储，有利于数据压缩和快速的列存取。

3、ORCFile  存储方式：数据按行分块 每块按照列存储 ，压缩快 快速列存取，效率比rcfile高,是rcfile的改良版本，相比RC能够更好的压 缩，能够更快的查询，但还是不支持模式演进。

4、Parquet  Parquet能够很好的压缩，有很好的查询性能，支持有限的模式演进。但是写速度通常比较慢。这中文件格式主要是用在Cloudera Impala上面的。支持Impala。

存储性能分析

hive调优

（https://blog.csdn.net/yu0_zhang0/article/details/81776459）

I、系统层面：

1.存储格式：  
	Hive使用 ORCfile 作为表结构不仅可以节省存储空间，而且能够快速提高Hive Query的速度。
	
2.执行引擎： Tez 和SaprkSQL
	1.配置mapreduce计算引擎 mr;、配置spark计算引擎 spark;、配置tez 计算引擎 tez;
	  set hive.execution.engine=mr;
	  
3.执行计划优化  --  负责制定SQL的执行计划 -是SQL分析和执行的优化工具
	 hive的cbo是通过 Apache Calcite -- Cost based Optimizer  基于代价的优化器
	   根据收集的统计信息来计算每种执行的代价，进而选择最优的执行方式 hive.cbo.enable
	   
4.数据的模型-创建表
Hive表的分区就是一个目录，分区字段不和表的字段重复
分区-对表进行合理的管理以及提高查询效率--创建有一个分区的分区表
hive partition是通过将数据拆分成不同的partition放入不同的文件，从而减少查询操作时数据处理规模的手段

5.数据的大小和数量
对小文件进行合并
set hive.merge.size.per.task = 256*1000*1000 #合并文件的大小

6.数据倾斜的一些配置
	设置数据负载均衡，其设置方法为设置hive.groupby.skewindata参数
	set hive.groupby.skewindata=ture;进行启用。
	  当启用时，能够解决数据倾斜的问题，但如果要在查询语句中对多个字段进行去重统计时会报错

II、应用层面--逻辑代码

01.列裁剪和分区裁剪：查询的过程中减少不必要的分区，字段
	去除查询中不需要的column Where条件判断等在TableScan阶段就进	行过滤使用中间表来完成复		杂的逻辑子查询--in 遇到大的数据，使用join来代替少用COUNT DISTINCT
02.合理控制map reduce数量
03.索引--从索引的角度优化：对那些常用的查询字段简历索引
04.避免倾斜：
	数据倾斜：由于数据的不均衡原因，导致数据分布不均匀，造成数据大量的集中到一点，造成数据热点
	造成的原因：
	01.数据分布不均衡
	02.操作：GROUP BY, COUNT DISTINCT, join
	解决的方式：
		使用COUNT DISTINCT和GROUP BY造成的数据倾斜：
		存在大量空值或NULL，或者某一个值的记录特别多，可以先把该值过滤掉，在最后单独处理:
		使用JOIN引起的数据倾斜：
			关联键存在大量空值或者某一特殊值，如”NULL”
			不同数据类型的字段关联
			空值单独处理，不参与关联；
05.表连接的过程中： Join
	多个表连接时，应是小表连大表hive.auto.convert.join=ture，小表放左边。
	join操作符左边的表的内容加载进内存，所以写语句时应将条目少的表/子查询放在join操作符的左边，
	这样可以有效减少内存溢出错误的几率

优化重点详解

1. 我们知道大数据场景下不害怕数据量大，害怕的是数据倾斜，怎样避免数据倾斜，找到可能产生数据倾斜的函数尤为关键，数据量较大的情况下，慎用count(distinct)，count(distinct) 容易产生倾斜问题。

2. 设置合理的map reduce 的task数量

map阶段优化

map个数影响因子：

1. input目录中文件总个数；
2. input目录中每个文件大小；
3. 集群设置的文件块大小(默认为128M, 可在hive中通过set dfs.block.size;命令查看，不能在hive中自定义修改)；

举例：

• input目录中有1个文件（300M），会产生3个块（2个128M，1个44M）即3个Map数。
• input目录中有3个文件（5M,10M,200M），会产生4个块（5M,10M,128M,72M）即4个Map数。

减少map数量
假设一个SQL任务： Select count(1) from popt_tbaccountcopy_mes where pt = ‘2012-07-04’; 该任务的inputdir /group/p_sdo_data/p_sdo_data_etl/pt/popt_tbaccountcopy_mes/pt=2012-07-04
共有194个文件，其中很多是远远小于128m的小文件，总大小9G，正常执行会用194个map任务。
Map总共消耗的计算资源： SLOTS_MILLIS_MAPS= 623,020

我通过以下方法来在map执行前合并小文件，减少map数：
set mapred.max.split.size=100000000;
set mapred.min.split.size.per.node=100000000;
set mapred.min.split.size.per.rack=100000000;
set hive.input.format =org.apache.hadoop.hive.ql.io.CombineHiveInputFormat;

再执行上面的语句，用了74个map任务，map消耗的计算资源：SLOTS_MILLIS_MAPS= 333,500
对于这个简单SQL任务，执行时间上可能差不多，但节省了一半的计算资源。

大概解释一下，100000000表示100M,
sethive.input.format=org.apache.hadoop.hive.ql.io.CombineHiveInputFormat;这个参数表示执行前进行小文件合并，前面三个参数确定合并文件块的大小，大于文件块大小128m的，按照128m来分隔，小于128m,大于100m的，按照100m来分隔，把那些小于100m的（包括小文件和分隔大文件剩下的），进行合并,最终生成了74个块。

增大map数量

如何适当的增加map数？
当input的文件都很大，任务逻辑复杂，map执行非常慢的时候，可以考虑增加Map数，
来使得每个map处理的数据量减少，从而提高任务的执行效率。
假设有这样一个任务：
Select data_desc,
count(1),
count(distinct id),
sum(case when …),
sum(case when ...),
sum(…)
from a group by data_desc
如果表a只有一个文件，大小为120M，但包含几千万的记录，如果用1个map去完成这个任务，
肯定是比较耗时的，这种情况下，我们要考虑将这一个文件合理的拆分成多个，
这样就可以用多个map任务去完成。
set mapred.reduce.tasks=10;
create table a_1 as
select * from a
distribute by rand(123);

这样会将a表的记录，随机的分散到包含10个文件的a_1表中，再用a_1代替上面sql中的a表，
则会用10个map任务去完成。
每个map任务处理大于12M（几百万记录）的数据，效率肯定会好很多。

看上去，貌似这两种有些矛盾，一个是要合并小文件，一个是要把大文件拆成小文件，
这点正是重点需要关注的地方，
使单个map任务处理合适的数据量。

reduce阶段优化

1. 	Reduce的个数对整个作业的运行性能有很大影响。如果Reduce设置的过大，那么将会产生很多小文件，
2. 	对NameNode会产生一定的影响，
3. 	而且整个作业的运行时间未必会减少；如果Reduce设置的过小，那么单个Reduce处理的数据将会加大，
4. 	很可能会引起OOM异常。
5. 	如果设置了mapred.reduce.tasks/mapreduce.job.reduces参数，那么Hive会直接使用它的值作为Reduce的个数；
6. 	如果mapred.reduce.tasks/mapreduce.job.reduces的值没有设置（也就是-1），那么Hive会
7. 	根据输入文件的大小估算出Reduce的个数。
8. 	根据输入文件估算Reduce的个数可能未必很准确，因为Reduce的输入是Map的输出，而Map的输出可能会比输入要小，
9. 	所以最准确的数根据Map的输出估算Reduce的个数。

1. Hive自己如何确定reduce数：

reduce个数的设定极大影响任务执行效率，不指定reduce个数的情况下，Hive会猜测确定一个reduce个数，基于以下两个设定： hive.exec.reducers.bytes.per.reducer（每个reduce任务处理的数据量，默认为1000^3=1G） hive.exec.reducers.max（每个任务最大的reduce数，默认为999） 计算reducer数的公式很简单N=min(参数2，总输入数据量/参数1) 即，如果reduce的输入（map的输出）总大小不超过1G,那么只会有一个reduce任务；

如：select pt,count(1) from popt_tbaccountcopy_mes where pt = '2012-07-04' group by pt; 
			/group/p_sdo_data/p_sdo_data_etl/pt/popt_tbaccountcopy_mes/pt=2012-07-04 总大小为9G多，
			因此这句有10个reduce

2. 调整reduce个数方法一：

调整hive.exec.reducers.bytes.per.reducer参数的值；
set hive.exec.reducers.bytes.per.reducer=500000000; （500M）
select pt,count(1) from popt_tbaccountcopy_mes where pt = '2012-07-04' group by pt; 这次有20个reduce

3. 调整reduce个数方法二

set mapred.reduce.tasks = 15;
select pt,count(1) from popt_tbaccountcopy_mes where pt = '2012-07-04' group by pt;这次有15个reduce

4. reduce个数并不是越多越好

同map一样，启动和初始化reduce也会消耗时间和资源；
另外，有多少个reduce,就会有多少个输出文件，如果生成了很多个小文件，那么如果这些小文件作为下一个任务的输入，
则也会出现小文件过多的问题；

5. 什么情况下只有一个reduce
很多时候你会发现任务中不管数据量多大，不管你有没有设置调整reduce个数的参数，任务中一直都只有一个reduce任务；
其实只有一个reduce任务的情况，除了数据量小于hive.exec.reducers.bytes.per.reducer参数值的情况外，还有以下原因：

• 没有group by的汇总，比如把select pt,count(1) from popt_tbaccountcopy_mes where pt = ‘2012-07-04’ group by pt; 写成 select count(1) from popt_tbaccountcopy_mes where pt = ‘2012-07-04’;这点非常常见，希望大家尽量改写。
• 用了Order by
• 有笛卡尔积
  通常这些情况下，除了找办法来变通和避免，我暂时没有什么好的办法，因为这些操作都是全局的，所以hadoop不得不用一个reduce去完成；同样的，在设置reduce个数的时候也需要考虑这两个原则：使大数据量利用合适的reduce数；使单个reduce任务处理合适的数据量；

合并小文件

  我们知道文件数目小，容易在文件存储端造成瓶颈，给 HDFS 带来压力，影响处理效率。
  对此，可以通过合并Map和Reduce的结果文件来消除这样的影响。
  用于设置合并属性的参数有：
		是否合并Map输出文件：hive.merge.mapfiles=true（默认值为真）
		是否合并Reduce 端输出文件：hive.merge.mapredfiles=false（默认值为假）
		合并文件的大小：hive.merge.size.per.task=256*1000*1000（默认值为 256000000）  

Hive优化之小文件问题及其解决方案
小文件是如何产生的

1. 动态分区插入数据，产生大量的小文件，从而导致map数量剧增。

2. reduce数量越多，小文件也越多(reduce的个数和输出文件是对应的)。

3. 数据源本身就包含大量的小文件。

小文件问题的影响

1. 从Hive的角度看，小文件会开很多map，一个map开一个JVM去执行，所以这些任务的初始化，启动，执行会浪费大量的资源，严重影响性能。

2. 在HDFS中，每个小文件对象约占150byte，如果小文件过多会占用大量内存。这样NameNode内存容量严重制约了集群的扩展。

小文件问题的解决方案
从小文件产生的途经就可以从源头上控制小文件数量，方法如下：

1.使用Sequencefile作为表存储格式，不要用textfile，在一定程度上可以减少小文件。

2.减少reduce的数量(可以使用参数进行控制)。

3.少用动态分区，用时记得按distribute by分区。  

对于已有的小文件，我们可以通过以下几种方案解决：
1.使用hadoop archive命令把小文件进行归档。

2.重建表，建表时减少reduce数量。

3.通过参数进行调节，设置map/reduce端的相关参数，如下：

设置map输入合并小文件的相关参数：

[java] view plain copy
//每个Map最大输入大小(这个值决定了合并后文件的数量)  
set mapred.max.split.size=256000000;    
//一个节点上split的至少的大小(这个值决定了多个DataNode上的文件是否需要合并)  
set mapred.min.split.size.per.node=100000000;  
//一个交换机下split的至少的大小(这个值决定了多个交换机上的文件是否需要合并)    
set mapred.min.split.size.per.rack=100000000;  
//执行Map前进行小文件合并  
set hive.input.format=org.apache.hadoop.hive.ql.io.CombineHiveInputFormat;   

设置map输出和reduce输出进行合并的相关参数：
[java] view plain copy
//设置map端输出进行合并，默认为true  
set hive.merge.mapfiles = true  
//设置reduce端输出进行合并，默认为false  
set hive.merge.mapredfiles = true  
//设置合并文件的大小  
set hive.merge.size.per.task = 256*1000*1000  
//当输出文件的平均大小小于该值时，启动一个独立的MapReduce任务进行文件merge。  
set hive.merge.smallfiles.avgsize=16000000  

cost based query optimization

Hive 自0.14.0开始，加入了一项”Cost based Optimizer”来对HQL执行计划进行优化，这个功能通  
过”hive.cbo.enable”来开启。在Hive 1.1.0之后，这个feature是默认开启的,它可以自动优化HQL中多个JOIN的顺序，并
选择合适的JOIN算法.
Hive在提交最终执行前,优化每个查询的执行逻辑和物理执行计划。这些优化工作是交给底层来完成。
根据查询成本执行进一步的优化，从而产生潜在的不同决策：如何排序连接，执行哪种类型的连接，并行度等等。
要使用基于成本的优化（也称为CBO），请在查询开始处设置以下参数：
设置hive.cbo.enable = true;

设置hive.compute.query.using.stats = true;

设置hive.stats.fetch.column.stats = true;

设置hive.stats.fetch.partition.stats = true;

数据倾斜

大表和大表Join

产生原因：业务数据本身的特性，导致两个表都是大表。
解决方式：业务削减。
案例：user 表有 500w+ 的记录，把 user 分发到所有的 map 上也是个不小的开销，而且 map join 不支持这么大的小表。如果用普通的 join，又会碰到数据倾斜的问题。

select * from log l left outer join user u
 on l.user_id = u.user_id;  

解决方法：当天登陆的用户其实很少,先只查询当天登录的用户，log里user_id有上百万个，这就又回到原来map join问题。所幸，每日的会员uv不会太多，有交易的会员不会太多，有点击的会员不会太多，有佣金的会员不会太多等等。所以这个方法能解决很多场景下的数据倾斜问题。

select /*+mapjoin(u2)*/* from log l2
left outer join
 (
select  /*+mapjoin(l1)*/u1.*
from ( select distinct user_id from log ) l1
join user u1 on l1.user_id = u1.user_id
) u2
on l2.user_id = u2.user_id;

count distinct 聚 合 时 存 在 大 量 特 殊 值

产生原因： 做count distinct时，该字段存在大量值为NULL或空的记录。
解决方式： 做count distinct时，将值为空的情况单独处理，如果是计算count distinct，可以不用处理，直接过滤，在最后结果中加1。如果还有其他计算，需要进行group by，可以先将值为空的记录单独处理，再和其他计算结果进行union。
案例：
1.只计算count distinct

select cast(count(distinct user_id)+1 as bigint) as user_cnt
from user
where user_id is not null and user_id <> '';

2.计算完count distinct 后面还有 group by。同一个reduce上进行distinct操作时压力很大,先将值为空的记录单独处理，再和其他计算结果进行union。 在Hive中，经常遇到count（distinct）操作，这样会导致最终只有一个reduce，我们可以先group 再在外面包一层count，就可以了。

select day,
count(case when type='session' then 1 else null end) as session_cnt,
count(case when type='user' then 1 else null end) as user_cnt
from (
  select day,type
  from (
	select day,session_id,'session' as type from log
	union all
	select day user_id,'user' as type from log
  )
  group by day,type
)t1 
group by day;

group by 产生倾斜的问题

set hive.map.aggr=true

开启map端combiner:在Map端做combine,若map各条数据基本上不一样, 聚合无意义，通过如下参数设置。

hive.groupby.mapaggr.checkinterval = 100000 (默认)
hive.map.aggr.hash.min.reduction=0.5(默认)

解释：预先取100000条数据聚合,如果聚合后的条数小于100000*0.5，则不再聚合。

set hive.groupby.skewindata=true；//决定 group by 操作是否支持倾斜数据。

注意：只能对单个字段聚合。
控制生成两个MR Job,第一个MR Job Map的输出结果随机分配到reduce中减少某些key值条数过多某些key条数过小造成的数据倾斜问题。 在第一个 MapReduce 中，map 的输出结果集合会随机分布到 reduce 中， 每个reduce 做部分聚合操作，并输出结果。这样处理的结果是，相同的 Group By Key 有可能分发到不同的reduce中，从而达到负载均衡的目的； 第二个 MapReduce 任务再根据预处理的数据结果按照 Group By Key 分布到 reduce 中（这个过程可以保证相同的 Group By Key 分布到同一个 reduce 中），最后完成最终的聚合操作。

hive explain

hive> explain select sum(amt_case) from t_orderinfo;
OK
STAGE DEPENDENCIES: --------------------显示各个阶段的依赖关系
  Stage-1 is a root stage ---------------------阶段一
  Stage-0 depends on stages: Stage-1 ---------------------结束阶段，依赖阶段一

STAGE PLANS:
  Stage: Stage-1
	Map Reduce
	  Map Operator Tree: ---------------------Map阶段
		  TableScan
			alias: t_orderinfo ---------------------扫描的表
			Statistics: Num rows: 2 Data size: 20 Basic stats: COMPLETE Column stats: NONE
			Select Operator
			  expressions: amt_case (type: bigint)
			  outputColumnNames: amt_case
			  Statistics: Num rows: 2 Data size: 20 Basic stats: COMPLETE Column stats: NONE
			  Group By Operator
				aggregations: sum(amt_case)
				mode: hash
				outputColumnNames: _col0 ---------------------临时字段
				Statistics: Num rows: 1 Data size: 8 Basic stats: COMPLETE Column stats: NONE
				Reduce Output Operator
				  sort order: 
				  Statistics: Num rows: 1 Data size: 8 Basic stats: COMPLETE Column stats: NONE
				  value expressions: _col0 (type: bigint)
	  Reduce Operator Tree: ---------------------Reduce阶段
		Group By Operator
		  aggregations: sum(VALUE._col0)
		  mode: mergepartial
		  outputColumnNames: _col0
		  Statistics: Num rows: 1 Data size: 8 Basic stats: COMPLETE Column stats: NONE
		  File Output Operator
			compressed: true
			Statistics: Num rows: 1 Data size: 8 Basic stats: COMPLETE Column stats: NONE
			table:
				input format: org.apache.hadoop.mapred.TextInputFormat
				output format: org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat
				serde: org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe

  Stage: Stage-0
	Fetch Operator
	  limit: -1 ---------------------job没有limit语句，stage-0是没有任何操作的阶段
	  Processor Tree:
		ListSink

转载于:https://my.oschina.net/sunmin/blog/3034494