深入理解Hive

背景

做数据分析和数据仓库的童鞋平常工作中用到的最多的语言就是Hive SQL。那么我们在熟悉编写Hive SQL之后，是不是可以再深入地去了解多一些它的底层实现呢。这篇文章总结了一下我对理解Hive sql的一些知识点总结。

Hive组成模块

提交一个hive sql到返回结果数据，整个过程是怎样的？

1.(执行查询操作)Execute Query
命令行或Web UI之类的Hive接口将查询发送给Driver(任何数据库驱动程序，如JDBC、ODBC等)以执行。
2.(获取计划任务)Get Plan
Driver借助查询编译器解析查询，检查语法和查询计划或查询需求
3.(获取元数据信息)Get Metadata
编译器将元数据请求发送到Metastore(任何数据库)。
4.(发送元数据)Send Metadata
Metastore将元数据作为对编译器的响应发送出去。
5.(发送计划任务)Send Plan
编译器检查需求并将计划重新发送给Driver。到目前为止，查询的解析和编译已经完成
6.(执行计划任务)Execute Plan
Driver将执行计划发送到执行引擎。
7.(执行Job任务)Execute Job
在内部，执行任务的过程是MapReduce Job。执行引擎将Job发送到ResourceManager,
ResourceManager位于Name节点中，并将job分配给datanode中的NodeManager。在这里，查询执行MapReduce任务.
7.1.(元数据操作)Metadata Ops
在执行的同时，执行引擎可以使用Metastore执行元数据操作。
8.(拉取结果集)Fetch Result
执行引擎将从datanode上获取结果集；
9.(发送结果集至driver)Send Results
执行引擎将这些结果值发送给Driver。
10.(driver将result发送至interface)Send Results
Driver将结果发送到Hive接口

Hive sql转Map reduce

Hive sql是如何翻译成Map reduce的呢？
参考：https://blog.csdn.net/u011535508/article/details/88083427

EXPLAIN PLAN

查看Hive语句的执行计划能够帮助我们更好地理解转换成MR任务的原理，以及如何编写性能更好的HIve SQL。

• 语法

EXPLAIN [EXTENDED|CBO|AST|DEPENDENCY|AUTHORIZATION|LOCKS|VECTORIZATION|ANALYZE] query

• 输出
  1.query的抽象语法树（AST)
  2.执行计划的各个stage的依赖
  3.每个stage的描述，可以是一个MR任务，一个合并阶段，一个抽样阶段等。默认一次只执行一个stage，可以设置并行执行。
• 示例

explain EXTENDED select sum(total_fee) from dwd.dwd_order_trade_wide

解释：

该SQL生成两个stage,stage-1是根结点。stage-0依赖stage-1

Map阶段，Select operator会产生一个只有total_fee的字段输出，Group by operator会应用sum(total_fee)然后产生一个_col0输出。Reduce阶段，Group by operator再次进行sum(_col0）操作，输出结果是文本格式。

由于没有limit操作，stage-0没有任何操作。

JOIN优化

• 小表放左边，大表放右边。小表将会加载进内存，进行Map -side join。设置参数：hive.auto.convert.join=true

控制mapper和reducer数量的方法

参考：https://blog.csdn.net/zhong_han_jun/article/details/50814246

分区还是分桶

MR会将一个人物转换成多个task,每个task都是一个jvm实例，都需要开启和销毁的开销。对于小文件，每一个文件都对应一个Task,jvm开启和消耗的时间将会很长。如果分区过多产生很多小文件，可以考虑分桶存储。

排序

• order by:全局排序，在一个reducer执行排序
• sort by:局部排序，全局不一定有序
• cluster by = sort by + 全局排序，但是会失去sort by的并行性。

join的过滤条件放在on和where里面有什么区别

对外连接（outter join）来说，过滤条件放在on后面不起作用。
对于内连接（inner join）来说,过滤条件放在on后面会起作用。
一种通用的方式是使用嵌套SELECT语句，嵌套在里面的SELECT语句使用过滤条件。

distinct优化

参考：https://blog.csdn.net/qq_36743482/article/details/76507091
参考：https://blog.csdn.net/qq_36330643/article/details/80775020

Hive开窗函数的使用

https://blog.csdn.net/student__software/article/details/81636566

常见统计指标的SQL计算

数据量大的时候的优化。

• 留存

SELECT
    first_day,
    sum(case when by_day = 0 then 1 else 0 end) day_0,
    sum(case when by_day = 1 then 1 else 0 end) day_1,
    sum(case when by_day = 2 then 1 else 0 end) day_2,
    sum(case when by_day = 3 then 1 else 0 end) day_3,
    sum(case when by_day = 4 then 1 else 0 end) day_4,
    sum(case when by_day = 5 then 1 else 0 end) day_5,
    sum(case when by_day = 6 then 1 else 0 end) day_6,
    sum(case when by_day >= 7 then 1 else 0 end) day_7plus
FROM
   (SELECT 
      user_id,
      login_time,
      first_day,
      DATEDIFF(login_time,first_day) as by_day
   FROM
     (SELECT
        b.user_id,
        b.login_time,
        c.first_day
      FROM 
        (SELECT
            user_id,
            str_to_date(login_time,'%Y/%m/%d') login_time
         FROM user_info
         GROUP BY 1,2) b
    LEFT JOIN
      (SELECT
          user_id,
          min(login_time) first_day
       FROM
           (select 
                user_id,
                str_to_date(login_time,'%Y/%m/%d') login_time
            FROM 
                user_info
            group by 1,2) a
       group by 1) c
     on b.user_id = c.user_id
     order by 1,2) e
  order by 1,2) f
group by 1
order by 1

• 新老用户
• 活跃用户

Hive实现拉链表

https://www.jianshu.com/p/799252156379

数据倾斜处理

https://blog.csdn.net/u010670689/article/details/42920917

hive sql去重a,b和b,a

https://blog.51cto.com/darrenmemos/2175665