背景
做数据分析和数据仓库的童鞋平常工作中用到的最多的语言就是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的呢?
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数量的方法
分区还是分桶
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语句使用过滤条件。
常见统计指标的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
hive sql去重a,b和b,a
https://blog.51cto.com/darrenmemos/2175665