Hive入门（面向产品的Hive教程）

Hive入门（面向产品的Hive教程）

0.正经的前言

这是一篇面向产品经理的Hive分享。目的是使受众更好的使用Hive client或其他封装Hive的产品。用HQL查询使用数据及应注意的问题和技巧。

0.1.主要内容：

• Hive的基本原理
• Hive的使用教程
• Hive使用注意事项
• HQL的使用技巧

0.2.建议读者：

• 大数据相关产品经理
• 初级数据分析师

0..不涉及：

• Hive的安装与配置
• Hive的开发与调优
• Hive的管理员相关知识

1.Hive的基本原理

要理解Hive绕不开Hadoop。我在知乎上有个关于Hadoop1.0诞生的比喻 回答。看完记得点赞

1.1 Hadoop的尴尬

Hadoop是一个开源框架来存储和处理大型数据在分布式环境中。但是，它“不好用”

• MapReduce“不好用”，它可以处理大型数据，但是需要用Java写很多的Map、Reduce和调度类，专业性很强，门槛高，一般人写不了。
• HDFS“不好用”，它提供了分布式的存储，解决了大数据存储的问题，但是很多原有的数据架构都是基于传统型关系型数据库（比如Mysql），迁移到HDFS困难。

简单的说，以前我们把数据放到一个数据库里的某张表里，而不是HDFS这样的目录上；我们使用SQL处理、转换数据而不是MapReduce这样的Java程序。

于是，duang～duang～duang～
Hive 诞生了

1.2 Hive的作用

Hive是什么？

• Hive是一个基于HDFS的数据仓库工具
• Hive是可以将HQL转化为MapReduce进行数据处理查询的工具

Hive不是什么？

• Hive不是一个关系型数据库
• Hive不能实时查询和行级更新。

1. Hive的原理

Hive的执行原理

你需要知道的Hive的执行原理

Hive将SQL(Hive SQL)转化成MapReduce并执行。

Hive的存储原理

你需要知道的Hive的存储原理

Hive按分区存储，所以查询时最好在where加上分区条件。

知道Hive是做什么的了，我们接下来了解如何使用Hive

2.Hive的使用教程

不论是Hive client或者Hue或者其他的封装工具比如我司的Puck和数据工坊。其实都是学习HQL的使用。

2.1 Select Where

select 表示我想要表的那些列；where 表示我想要表的那些行。这实际是个对于大表过滤出小表的过程。

示例： 有表employee如下（节选）,按部门Dept分区。查询研发部薪水高于5000的选手的ID、ame、Des。

ID	ame	Salary	Des	Dept
777	乔峰	50000	研发总监	研发部
778	柳梦璃	27000	商务经理	商务部
779	宁缺	18000	开发工程师	研发部
780	魏璎珞	5000	实习生	研发部

hive> SELECT ID,ame,Des FROM employee WHERE salary>5000 and Dept = 	研发部	;

ID	ame	Des
777	乔峰	研发总监
779	宁缺	开发工程师

在查询前，我不太确定结果数据有多少条，我只想看一条例子数据，这时候可以用limit限制，limit通常在select语句的最后

hive> SELECT ID,ame,Des FROM employee WHERE salary>5000 and Dept = 	研发部	 limit 1;

ID	ame	Des
777	乔峰	研发总监

2.2 Select Order By

Order by 可以使Select Where查询出来的结果表按某一列升序或者降序排列

示例： 上表employee表，查询研发部的选手的ID、ame、Des，按薪水升序排列。

hive> SELECT ID,ame,Des FROM employee WHERE Dept = 	研发部	 ORDER By Salary;

ID	ame	Des
780	魏璎珞	实习生
779	宁缺	开发工程师
777	乔峰	研发总监

2. Select Group By

包含group by的查询就不再是简单的“过滤”。它会把结果数据多行聚合为一行（多行按某种逻辑合并为一行），通常搭配聚合函数（count、sum）等一起使用。
示例： 上表employee表，统计各部门人数并按人数多少降序排序。

hive> SELECT Dept,count(*) from employee group by Dept ORDER By count(*) desc;

Dept	count(*)
研发部
商务部	1

这个列名不太好看，我们可以利用列别名的方式重命名列名

hive> SELECT Dept as Deptment,count(*) as umber from employee group by Dept ORDER By umber desc;

Deptment	umber
研发部
商务部	1

2.4 Select Join

对于sql的初学者来讲，第一个比较难的点可能就是join了。简单来说，join就是把两(多)个表连接在一起，通常搭配on来说明按什么条件连接在一起。

举个例子
employee表

ID	ame	Salary	Des	Dept
777	乔峰	50000	研发总监	研发部
778	柳梦璃	27000	前端工程师	研发部
779	宁缺	18000	开发工程师	研发部
780	魏璎珞	5000	实习生	研发部

layoffs表

ID	EID	fre
101	2761	1
102	779	2.5
10	780	0
104	4120	1

柳梦璃也转到我们部门了，我想看看我们部门谁上了优化名单

hive>select * from employee a join layoffs b on a.id = ;

由下表可以看出 join（或者是inner join）会按条件a表的id等于b表的eid这个条件连接，只保留连接上的数据。

ID	ame	Salary	Des	Dept	ID	EID	fre
779	宁缺	18000	开发工程师	研发部	102	779	2.5
780	魏璎珞	5000	实习生	研发部	10	780	0

hive>select * from employee a left join layoffs b on a.id = ;

由下表可以看出 left join（或者是left outer join）会按条件a表的id等于b表的eid这个条件连接，不论是否连接上都保留左边表的所有数据。

ID	ame	Salary	Des	Dept	ID	EID	fre
779	宁缺	18000	开发工程师	研发部	102	779	2.5
780	魏璎珞	5000	实习生	研发部	10	780	0
777	乔峰	50000	研发总监	研发部	ULL	ULL	ULL
778	柳梦璃	27000	前端工程师	研发部	ULL	ULL	ULL

hive>select * from employee a right join layoffs b on a.id = ;

由下表可以看出 right join（或者是right outer join）会按条件a表的id等于b表的eid这个条件连接，不论是否连接上都保留右边表的所有数据。（其实这个不常用）

ID	ame	Salary	Des	Dept	ID	EID	fre
779	宁缺	18000	开发工程师	研发部	102	779	2.5
780	魏璎珞	5000	实习生	研发部	10	780	0
ULL	ULL	ULL	ULL	ULL	101	2761	1
ULL	ULL	ULL	ULL	ULL	104	4120	1

hive>select * from employee a full join layoffs b on a.id = ;

由下表可以看出 full join（或者是full outer join）会按条件a表的id等于b表的eid这个条件连接，不论是否连接上都保留左右右边表的所有数据。（其实这个也不常用）

ID	ame	Salary	Des	Dept	ID	EID	fre
779	宁缺	18000	开发工程师	研发部	102	779	2.5
780	魏璎珞	5000	实习生	研发部	10	780	0
777	乔峰	50000	研发总监	研发部	ULL	ULL	ULL
778	柳梦璃	27000	前端工程师	研发部	ULL	ULL	ULL
ULL	ULL	ULL	ULL	ULL	101	2761	1
ULL	ULL	ULL	ULL	ULL	104	4120	1

几种join的关系如图：

2.5 其他技巧

2.5.1 正则表达式

很多时候我们为了懒省事节约时间，我们可以用“*”来表达：我全都要

hive> select * from meitu //所有的列我全要
hive> select symbol,`price.*` from meitu //price字段（map）所有的value我全要
hive> select distinct uid from meitu where date like 	*	 //所有日期我全要，注意搭配like
hive> select * from meitu where street RLIKE 	.*(Chicago|Ontario).*	; //RLIKE是hive的拓展功能可以搭配java的正则使用（如果你会写正则表达式的话）

2.5.2 嵌套Select

我们可以把查询结果也看作一个表，用括号括起来，作为一个表嵌套在sql中

select ame from (select a.id,ame,Salary,b.id as lid,b.Fre from employee a join layoffs b on a.id = ) as m 

2.5. case when

case when 用来处理单个列的结果，是数据分析人员使用的最频繁的一个功能之一。这个东西玩的6了是相当好用的功能

hive> select ame,salary,case when salary < 10000 then 	low	 when salary >= 10000 and salary < 20000 then 	mid	 else 	hight	 end as bracket from employee

ame	Salary	bracket
乔峰	50000	hight
柳梦璃	27000	hight
宁缺	18000	mid
魏璎珞	5000	low

2.5.4 其他常用函数

hive 提供了丰富的函数可以使用，包括数学函数、聚合函数、表生成函数等等，数据工坊上也由很多的udf可以使用，这里暂时不细讲。

.Hive使用注意事项

.1注意or的使用注意加括号

hive> select count(*) from meipai where controller_p = 	gifts	 and action_p = 	live_cume	 and logdate > 2016060100 and logdate < 2018121200 and is_najia!=1 or is_majia is null and coins is not null

注意！这个地方一定要加括号！（is_najia!=1 or is_majia is null），不然的话hive会认为or和前面的条件是两个对立的情况，所以会扫描所有的分区，直接崩掉！

//hive会认为这个逻辑where (controller_p) = 	gifts	 and action_p = 	live_cume	 and logdate > 2016060100 and logdate < 2018121200 and is_najia!=1) or (is_majia is null)

.2 more

更多的学习
1.可以看这个cf：数据分析参考手册
2.《Hive编程指南》第1、4、5、6、7、10章