Hive疑难杂问

Hive优化count(distinct)

SELECT COUNT( DISTINCT id ) FROM TABLE_NAME WHERE ...;

由于引入了DISTINCT，因此在Map阶段无法利用combine对输出结果消重，必须将id作为Key输出，在Reduce阶段再对来自于不同Map Task、相同Key的结果进行消重，计入最终统计值。
  这个作业运行时的Reduce Task个数为1，对于统计大数据量时，这会导致最终Map的全部输出由单个的ReduceTask处理。这唯一的Reduce Task需要Shuffle大量的数据，并且进行排序聚合等处理，这使得它成为整个作业的IO和运算瓶颈。
  尝试通过显式地增大Reduce Task个数来提高Reduce阶段的并发。具体设置如下：

set mapred.reduce.tasks=100

但这一参数并没有影响实际Reduce Task个数，Hive运行时输出“Number of reduce tasks determined at compile time: 1”。因为Hive在处理COUNT这种“全聚合(full aggregates)”计算时，它会忽略用户指定的Reduce Task数，而强制使用1。
  解决方案：转换为子查询，转化为两个MapReduce任务，先select distinct的字段，然后在count（），这样去重就会分发到不同的reduce块，count依旧是一个reduce但是只需要计数即可。

SELECT COUNT(*) FROM (SELECT DISTINCT id FROM TABLE_NAME WHERE … ) t;

补充：count(distinct user_id)和count...group by user_id的区别？
  首先要清楚count(distinct) 的原理机制，它是将数据通过map端发往一个reduce，之后reduce接收到数据之后，会将数据放入到hashset中去重，之后cleanUp()方法再执行最后的逻辑，比如：计算hashset的size等。这里就出现了一些问题：
1）数据都发往一个reduce会造成数据倾斜
2）程序从分布式变成单机程序，影响效率
3）程序执行过程中，只产生一个job
  但也不是绝对的，当数据量很小的时候，此时我们并不需要采分布式执行，一个job运行足矣。但是，当数据量比较大的时候，这时count(distinct) 就暴露除了大大的弊端，所以，此时，不应该采用此法来实现去重。count()…group() by当数据量比较大的时候，采用此法，先分组，这时已经在map端实现了去重机制，之后数据发往reduce 数据量已经变得很小了，并且此法涉及到shuffle ，所以reduce的压力不会集中在某个上，并且会产生多个job 。

group() by count() 一定比count(distinct) 性能要好吗？
  不一定，当数据量比较大的时候采用group() by count() 会比count(distinct) 要好，但是在数据量比较小的时候，一个 job就可以处理，没必要用两个job ，也没必要shuffle，所以调优看情况而定。

Hive语句的运行机制包含where、having、group by、order by，执行过程顺序

1.where xx对全表数据做筛选
2.针对结果集使用group by分组
3.针对每个分组进行select查询，有几组就执行几次
4.再进行having筛选每组数据
5.最后整体进行order by排序

补充：Hive语句在MapReduce是怎么运行的，Map端输出的key-value值具体是什么？类似wordcount，MapReduce的输入输出对应什么？（把MR中的分组，排序说一下，要对MR中map(),reduce()和shuffle熟悉）

Hive的MetaStore的三种模式

1. 内嵌Derby方式
     这个是Hive默认的启动模式，一般用于单元测试，这种存储方式有一个缺点：在同一时间只能有一个进程连接使用数据库
2. Local方式
     例如本地MySQL：创建好用户：hive；database：hive。需要把MySQL的驱动包copy到目录<HIVE_HOME>/lib中。如果是第一次需要执行初始化命令：schematool -dbType mysql -initSchema配置完成后就可在shell中以CLI的方式访问Hive进行操作验证。
3. Remote方式
     以Mysql数据库(192.168.6.77)为例：创建好用户：hive；database：hive_meta。Remote方式需要分别配置服务端和客户端的配置文件。Hive metastore服务端启动命令：hive --service metastore -p <port_num>。如果不加端口默认启动：hive --service metastore，则默认监听端口是：9083。

Hive计算引擎

1、配置MapReduce计算引擎

set hive.execution.engine=mr;

2、配置Spark计算引擎

set hive.execution.engine=spark;

3、配置tez 计算引擎

set hive.execution.engine=tez;

  TEZ是基于Hadoop YARN之上的DAG（有向无环图，Directed Acyclic Graph）计算框架。核心思想是将Map和Reduce两个操作进一步拆分，即Map被拆分成Input、Processor、Sort、Merge和Output， Reduce被拆分成Input、Shuffle、Sort、Merge、Processor和Output等。这样，这些分解后的元操作可以任意灵活组合，产生新的操作，这些操作经过一些控制程序组装后，可形成一个大的DAG作业，从而可以减少Map/Reduce之间的文件存储，同时合理组合其子过程，也可以减少任务的运行时间。
  两者比较：MapReduce计算会对磁盘进行多次的读写操作，这样启动多轮job的代价略有些大，不仅占用资源，更耗费大量的时间。而采用TEZ计算框架，就会生成一个简洁的DAG作业，算子跑完不退出，下轮继续使用上一轮的算子，这样大大减少磁盘IO操作，从而计算速度更快。

Hive，Hive on Spark和SparkSQL区别

• Hive是一种基于HDFS的数据仓库，并且提供了基于SQL模型的，针对存储了大数据的数据仓库，进行分布式交互查询的查询引擎

  SparkSQL作为Spark生态的一员继续发展，而不再受限于Hive，只是兼容Hive；而Hive on Spark是一个Hive的发展计划，该计划将Spark作为Hive的底层引擎之一，即Hive将不再受限于一个引擎，可以采用Map-Reduce、Tez、Spark等引擎。

SparkSQL
• SparkSQL并不能完全替代Hive，它替代的是Hive的查询引擎，SparkSQL由于其底层基于Spark自身的基于内存的特点，因此速度是Hive查询引擎的数倍以上，Spark本身是不提供存储的，所以不可能替代Hive作为数据仓库的这个功能
• SparkSQL相较于Hive的另外一个优点，是支持大量不同的数据源，包括hive、json、parquet、jdbc等
• SparkSQL由于身处Spark技术堆栈内，基于RDD来工作，因此可以与Spark的其他组件无缝整合使用，配合起来实现许多复杂的功能

Hive on Spark
  Hive on Spark目的是把Spark作为Hive的一个计算引擎，将Hive的查询作为Spark的任务提交到Spark集群上进行计算。
Hive on Spark与SparkSql的结构类似，只是SQL引擎不同，但是计算引擎都是spark。

HQL与SQL语句的区别

• Hive不支持等值连接
SQL中对两表内联可以写成： select * from dual a,dual b where a.key = b.key; Hive中应为 select * from dual a join dual b on a.key = b.key
• Hive支持嵌入mapreduce程序，来处理复杂的逻辑
• Hive支持将转换后的数据直接写入不同的表，还能写入分区、hdfs和本地目录
• Hive不支持事务

Hive解决小文件过多的问题

Hive on MapReduce
哪里会产生小文件 ?
1、源数据本身有很多小文件
2、动态分区会产生大量小文件
3、reduce个数越多, 小文件越多
4、按分区插入数据的时候会产生大量的小文件, 文件个数 = maptask个数 * 分区数

小文件太多造成的影响 ?
1、从Hive的角度看，小文件会开很多map，一个map开一个JVM去执行，所以这些任务的初始化，启动，执行会浪费大量的资源，严重影响性能。
2、HDFS存储太多小文件, 会导致namenode元数据特别大, 占用太多内存, 制约了集群的扩展

小文件解决方法
方法一：通过调整参数进行合并

#每个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输出进行合并的相关参数：

#设置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

方法二: 针对按分区插入数据的时候产生大量的小文件的问题, 可以使用DISTRIBUTE BY rand() 将数据随机分配给Reduce，这样可以使得每个Reduce处理的数据大体一致.

# 设置每个reducer处理的大小为5个G
set hive.exec.reducers.bytes.per.reducer=5120000000;

# 使用distribute by rand()将数据随机分配给reduce, 避免出现有的文件特别大, 有的文件特别小
insert overwrite table test partition(dt)
select * from iteblog_tmp
DISTRIBUTE BY rand();

方法三: 使用Sequencefile作为表存储格式，不要用textfile，在一定程度上可以减少小文件

Hive on Spark
可以设置hive.merge.sparkfiles=true，可以设定启动参数中的executor和task来控制任务并行度从而防止太多小文件的情况。除此之外，对hiveContext计算出的要插入的数据进行repartition重分区，从而可以动态调整输出文件的个数，从而防止小文件的产生。

Hive事务 和 锁

Hive事务讲解
Hive官方文档事务
Hive的锁

Hive union与union all区别

  union和union all都是对两个子查询的结果合并，不过还是有区别的，union会对两个子查询的结果去重合并，而union all不会对子查询结果去重处理。

HQL

Hive-累计求和

计算累计和
统计1-12月的累积销量，即1月为1月份的值，2月为1.2月份值的和，3月为123月份的和，12月为1-12月份值的和。

SELECT  
month,SUM(amount) month_amount,  
SUM( SUM(amount)) OVER (ORDER BY month ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cumulative_amount  
FROM table_name  
GROUP BY month  
ORDER BY month; 

计算最大在线人数

计算最大在线人数

select max(max_index) from (
    select
        sum(index) over(order by `timestamp`) as max_index --排序后第一行到本行的和
    from(
        select
            order_id,
            unix_timestamp(login_time) as `timestamp`,
            1 as index
        from connection_detail where dt = '20190101' and is_td_finish = 1
        union all
        select
            order_id,
            unix_timestamp(logout_time) as `timestamp`,
            -1 as index
        from connection_detail where dt = '20190101'
    )a--将登录时间和登出时间多列成多行
)b