一、什么是Hive？
基于hdfs，结合类SQL引擎，底层执行MR任务，用于OLAP分析查询的数据仓库。
hive的运行原理（简单版理解）
driver:解析器，将语句生成抽象的表达式树
编译器：词法分析、语法分析、编译(需要联系元数据)，编译完成后会生成执行计划
优化器：将执行计划进行优化，减少不必要的列、使用分区等等
执行器：将优化后的执行计划交给执行引擎运行
二、Hive分区、分桶的意义
1）分区的意义：
当表的数据量越来越大时，每次查询都需要花费很多时间，分区，避免全盘扫描，提高查询效率
2）分桶的意义：
A）join操作时可以提高MR的查询效率。
B）分桶表适合进行数据抽样。
三、Hive分区和分桶的区别
1）建表方式
分区：建表是通过关键字partition by和分区字段（表外字段），根据分区字段划分分区
分桶：建表是通过关键字distribute by sort by或clustered by sort by，根据分区字段的hash值模上分桶数量划分分区
2）执行方式
分桶前需要执行命令 hive.enforce.bucketing = true
3）粒度
分区：对应HDFS的目录结构，是粗粒度
分桶：对应HDFS目录下的文件，是细粒度
4）优缺点
分区优点：提供了一种整理数据和优化查询的便利方式，可以避免全盘扫描，T提供查询效率
分区缺点：容易造成数据倾斜
分桶的优点：相当于对分区的一种优化，缓解了分区带来的数据倾斜问题。使数据分配更加均匀。一般适用于做大数据抽样，以及map端join操作查询，提高查询效率
四、内部表和外部表的区别
1）关键字
外部表用关键字：external表示
内部表不需要加关键字
2）表的存储位置
内部表数据存储的位置是hive.metastore.warehouse.dir（默认user/hive/warehouse）
外部表的数据存储位置由用户自定义（一般存在HDFS的自定义目录）
3）删除的内容
删除内部表会直接删除元数据（metadata）以及存储数据
删除外部表仅仅会删除元数据，HDFS上的数据文件并不会被删除
五、静态分区和动态分区
静态分区 SP（static partition）
1）静态分区是在编译期间指定的指定分区名
2）支持load和insert两种插入方式
2.1 load方式
A）会将分区字段的值全部修改为指定的内容
B）一般是确定该分区内容是一致的时候才会使用
2.2 insert方式
A）必须先将数据放在一个没有设置分区的普通表中
B）该方式可以在一个分区内存储一个范围的内容
C）从普通表中选出的字段不能包含分区字段
3）适用于分区数少，分区名可以明确的数据
动态分区 DP（dynamic partition）
1）根据分区字段的实际值，动态进行分区
2）是在sql执行的时候进行分区
3）需要先将动态分区设置打开（set
hive.exec.dynamic.partition.mode=nonstrict ）
4）只能用insert方式
5）通过普通表选出的字段包含分区字段，分区字段放置在最后，多个分区字段按照分区顺序放置
总结：静态分区与动态分区的主要区别在于静态分区是手动指定，而动态分区是通过数据来进行判断。
六、Hive和传统关系型数据库的比较
可能有人会问，有那么多的数据库为什么用Hive呢？
这里从以下几个角度比较：
Hive 和数据库除了拥有类似的查询语言，再无类似之处。
1）数据存储位置
Hive存储在HDFS
数据库将数据保存在块设备或者本地文件系统中
而hadoop生态里提供的HDFS更适合大数据存储
2）数据更新
Hive中不建议对数据进行修改。而数据库中的数据进程需要实时修改
3）执行延迟
当数据规模比较大时，Hive执行延迟较低，体现出Hive并行计算的优势
当数据规模比较小时，数据库执行延迟较低
4）数据规模
Hive支持很大规模的数据计算
而数据库支持的数据规模比较小
具体概括一下Hive和Mysql的区别
1）存储：hive依赖于HDFS，理论上可以无限扩展;mysql 存在存储上限，一旦数据量增大，计算速度就很低
2）执行引擎：hive依赖于MR、TEZ、SPARK，mysql有自己的执行引擎
3）灵活性：hive元数据存储独立于数据存储之外，解耦数据和元数据，mysql灵活度低。
4）分析速度：hive在大规模数据执行速度要快，mysql在数据量较小时执行快
5）执行延迟：hive 执行延迟高，mysql执行延迟低
6）数据加载模式：hive是读时模式，mysql是写时模式
7）数据操作：hive是数据覆盖追加，mysql行级更新删除
8）索引：hive支持索引（0.7之后），mysql索引会更高效
9）应用场景：海量数据分析查询工作（OLAP），mysql实时查询(OLTP)
什么是读时模式？写时模式？
读时模式
1）读时模式可以使得数据加载非常迅速。这是因为它不需要读取数据，进行“解析”，再进行序列化以数据库内部格式存入磁盘。数据加载仅仅是文件的复制和移动
2）数据是在写入数据库是对照模式进行检查。有利于提升查询性能
写时模式
与读时模式相反
总结：hive是读时模式，所以导入和加载数据的时候很快
mysql是写时模式，因为每次加载数据都需要进行一系列的处理和检查，所以在导入和加载数据的时候很慢
注意：这里的数据规模一般都是指数据量比较大的情况下
OLAP和OLTP的区别
OLTP：联机事务处理
主要是执行基本日常的事务处理,比如数据库记录的增删查改,操作相对频繁，每次数量的数据量相对较小
OLAP：联机分析处理
主要是进行数据分析、探索并挖掘数据价值，作为企业高层进行决策的参考，处理相对OLTP没那么频繁，但是每次处理的数据量要比OLTP大得多
七、Hive和非关系型数据库的比较
这里拿Hbase作为nosql数据库的代表，因为hadoop生态里也会用到。
Hive和Hbase的区别
1）执行方式
hive底层执行MR任务
hbase不执行MR，是在数据库上实时运行
2）类型不同
两者都是基于HDFS的技术，但
Hive是一种类SQL的引擎，并且运行MapReduce任务
Hbase是一种在Hadoop之上的NoSQL的Key/value数据库
3）应用场景
Hive适合用来对一段时间内的数据进行分析查询，例如，用来计算趋势或者网站的日志。
Hbase非常适合用来进行大数据的实时查询。Facebook用Hbase进行消息和实时的分析。它也可以用来统计Facebook的连接数。
4）存储方式
hive是行式存储，通常是全盘扫描，维护索引在时间和空间上成本都很高，不推荐删除和更新操作
hbase是列式存储，通常是随机读写，增删查改速度很快
5）执行延迟
hive高延迟
hbase低延迟
八、hive解析json数据
解析json字段
两种解析方法
1）get_json_object(参数1，参数2)
参数1：要解析的json对象名
参数2：使用$表示json变量标识，然后用 . 或 [] 读取对象或数组
适用于只需要解析单个字段，因为get_json_object()一次只接受两个参数适用于解析较复杂的json数据，多层json
2）json_tupel(参数1，参数2)
参数1：要解析的对象名
参数2：一个或多个要解析的字段
适用于解析较为简单的json数据，单层json
解析json文件
一种JsonSede（导jar包）
九、Hive窗口函数
对实际业务中类似的思路及使用到的函数方法分类归纳
函数方法
1）explode函数
功能：一行拆为多行，常用于拆分array，map类型，行转列
explode函数如果是只查询一个字段即拆分字段不需要结合lateral view使 用。否则需要结合lateral view使用。
2）concat函数
语法1：concat(str1, str2,...)
返回结果为连续参数产生的字符串。一般用于处理两个及以上的字符的直接拼接。
语法2：concat(str1, seperator,str2,seperator,...)
返回结果为连接参数产生的字符串并且有分隔符，如果有任何一个参数为null，则返回值为null。
一般用于取单独列，但是由于语法原因，常加上字符“\t”进行拼接，结果还是显示单个字符的效果
3）concat_ws函数
功能：和concat()一样，将多个字符串连接成一个字符串，但是可以一次性指定分隔符
语法：concat_ws(separator, str1, str2, ...)
说明：第一个参数指定分隔符。需要注意的是分隔符不能为null，如果为null，则返回结果为null。
4）group_concat函数
功能：将group by产生的同一个分组中的值连接起来，返回一个字符串结果。
语法：group_concat( [distinct] 要连接的字段 [order by 排序字段 asc/desc ] [separator '分隔符'] )
说明：通过使用distinct可以排除重复值；如果希望对结果中的值进行排序，可以使用order by子句；separator是一个字符串值，缺省为一个逗号。
5）union和union all的功能和区别
功能：都是对多个结果集进行合并
效率区别：
（a）对重复结果的处理：union all包含重复记录。union不包含重复记录。
（b）对排序的处理：union会按照字段的顺序进行排序。union all只是简单的将两个结果集合并就返回
总结：union all效率上比union快
6）collect_list函数和colelct_set函数区别
功能：都是将分组中的某列转换为一个数组返回
区别：不同的是collect_list不去重而collect_set去重
7） date_sub(string,int)日期减少函数
功能：返回开始日期startdate减少days天后的日期
8）行列互换
行转列 case when
列转行 lateral view explode() 一列转多行，被转列多为array，map类型
9）count
A）用法：
count(col)：只包含一列，在统计的时候的会忽略null值
count(1)：1代表代码行，在统计结果的时候不会忽略null值
count(*):包含了所有的列，相当于函数，统计的时候不会忽略null值
B）执行效率上：
如果列名为主键，count(col)会比count(1)快
列名不为主键count(1)会比count(col)快
如果表中多个列并且没有主键，count(1)效率最高
如果表中只有一个字段count(*)效率最高
10）自定义函数
使用UDF的方式
第一种：
将编写好的UDF打包上传到服务器，并将jar包添加到hive的class path中
add jar /home/hadoop/oyrx_data/sz1901-1.0.jar;
创建一个自定义临时函数名
create temporary function parseAge as 'udf.birthday2Age';
第二种：
java API操作
自定义 UDF：继承 UDF，重写 evaluate 方法
11）join和多个逗号连接的区别
1）join会先过滤掉数据，多个逗号连接先笛卡尔积再过滤
2）效率上join较高
窗口函数
1）应用场景
分区排序
动态group by
Top N
累计计算
层次查询
2）常用窗口函数
排名函数
row_number():没有并列，相同名字顺序排
rank():有并列，相同名次空位
dense_rank():有并列，相同名次不空位
OVER()：指定分析函数工作的数据窗口大小，这个数据窗口大小可能会随着行的 变而变化
sum(col) over() : 分组对col累计求和，over()
count(col) over() : 分组对col累计，over()
min(col) over() : 分组对col求小
max(col) over() : 分组求col的大值
avg(col) over() : 分组求col列的平均值
first_value(col) over() : 某分区排序后的第一个col值
last_value(col) over() : 某分区排序后的后一个col值
lag(col,n,DEFAULT) : 统计往前n行的col值，n可选，默认为1，DEFAULT当往上第n行为NULL时候， 取默认值，如不指定，则为NULL
lead(col,n,DEFAULT) : 统计往后n行的col值，n可选，默认为1，DEFAULT当往下第n行为NULL时候， 取默认值，如不指定，则为NULL
分析函数
常用于OLAP中，不能累加，而且需要根据不同维度上钻和下钻的指标统计，比如，分小时、天、月的UV天数
1）grouping sets
可以实现对同一个数据集的多重group by操作。事实上grouping sets是多个group by进行union alll操作的结合，它仅使用一个stage完成这些操作。grouping sets的子句中如果包换() 数据集，则表示整体聚合。多用于指定的组合查询。
2）cube俗称是数据立方，它可以时限hive任意维度的组合查询。即使用with cube语句时，可对group by后的维度做任意组合查询。
3）rollup是cube的子集，以最左侧的维度为主。俗称层级聚合。
4）Grouping__ID。如果这列 被聚合过则返回0，否则返回1。
5）grouping sets/cube/rollup三者的区别：
注:
5.1）grouping sets是指定具体的组合来查询。
5.2）with cube 是group by后列的所有的维度的任意组合查询。
5.3）with rollup 是group by后列的从左往右逐级递减的层级组合查询。
5.4）cube/rollup 后不能加()来选择列，hive是要求这样。
十、案例分析（举例部分）
1）排名函数
1）查询班里成绩，显示各班成绩顺序和排名
select
id,cls,sco,
row_number() over(partition by cls order by sco desc) rm,
dense_rank() over(partition by cls order by sco desc) rk
from score;
2）查询各班成绩前三名的信息
select
*
from
(select
id,cls,sco,
row_number() over(partition by cls order by sco desc) rm
from
score) tmp
where tmp.rm<4;
2）行转列
concat字符串拼接+union all合并结果集
现有一张成绩score表：
Id Username Math Computer English
1 Huangbo 34 58 58
2 Xuzheng 45 87 45
3 Wangbaoqiang 76 34 89
请编写一个SQL语句把以上的这张表转换成下面这张表
Id Username Course Score
1 Huangbo Math 34
1 Huangbo Computer 58
1 Huangbo English 58
2 Xuzheng Math 45
2 Xuzheng Computer 87
2 Xuzheng English 45
sql：
select
*
from
(select
Id,
Username,
concat('Math','\t') Course,
Math Score
from score2
union all
select
Id,
Username,
concat('Computer','\t') Course,
Computer Score
from score2
union all
select
Id,
Username,
concat('English','\t') Course,
English Score
from score2
) tmp
;
3）explode拆分（行转列）+切割字符串split
表t3
id type_flag tags
10001 3 11_20_30,11_22_34,12_23_30,13_24_36
10002 2 11_20,11_22,12_23,13_24
10003 1 11,12
变为：
id type_flag tag1 tag2 tag3
10001 3 11 20 30
10001 3 11 22 34
10001 3 12 23 30
10001 3 13 24 36
10002 2 11 20
10002 2 11 22
10002 2 12 23
10002 2 13 24
10003 1 11
10003 1 12
思路：
1.先对tags字段拆分即行转列
id type_flag tags
10001 3 11_20_30
10001 3 11_22_34
10001 3 12_23_30
10001 3 13_24_36
...
2.然后再对tags字段按'_'切割后得到数组结果集的字段tags
3.分别查询tags[0]为tag1，tags[1]为tag2,tags[2]为tag3
sql：
select
tmp1.id id,
tmp1.type_flag type_flag,
tmp1.tags[0] tag1,
tmp1.tags[1] tag2,
tmp1.tags[2] tag3
from
(select
id,
type_flag,
split(tag,'_') tags
from t3
lateral view
explode(tags)tags as tag
)tmp1
;
4）字符串拼接+多行转单行（组合）+日期函数转换及计算时间差值
表phone_time
calling called times
zhangsan lisi 01:00:12
lisi zhangsan 00:10:25
计算通话时长
思路：
1.先将通话人和接听人的字段按统一格式进行字符串拼接，这里需要进行判断排个序
lisi-zhangsan 01:00:12
lisi-zhangsan 00:10:25
2.按照calling_called字段分组，按照时间倒序
3.将时间日期转成时间戳，然后计算通话终止时间减去通话开始时间
sql：
select
tmp2.calling_called calling_called,
date_diff(unix_timestamp('tmp2.times[0]','HH:mm:ss'),unix_timestamp('tmp2.times[1]','HH:mm:ss')) diff
from
(select
tmp1.calling_called calling_called,
collect_set(times) times
from
(select
case when calling>called then concat('calling','-','called') else concat('called','-','calling') end as calling_called,
times
from phone_time
)tmp1
group by times desc
)tmp2
5）日期时间的转换+时间计算+lead函数
表t6
id uv_time
1 2018-05-01 01:00:34
2 2018-05-01 02:12:41
3 2018-05-02 15:31:26
4 2018-06-08 12:40:39
计算上下相邻两次时间的时间间隔
思路：
1.将日期时间转成时间戳计算
2.用当前时间的后一条时间减去当前时间，如果当前数据的后一条时间为null返回当前时间
sql：
方法一
select
id,
uv_time,
from_unixtime(tmp1.diff,'HH:mm:ss') diff
from
(select
id,
uv_time,
case when lead(unix_timestamp(uv_time),1) is null then nvl(lead(unix_timestamp(uv_time),1),uv_time) else lead(unix_timestamp(uv_time),1) over(sort by uv_time)-unix_timestamp(uv_time) end diff
from t6
)tmp1
;
方法二
select
id,
uv_time,
nvl(round((lead(unix_timestamp(uv_time)) over(sort by uv_time)-unix_timestamp(uv_time))/(60*60),0),0) minute_diff
from t6
;
6）分组group by+窗口函数类
1）级联求和accumulate
需求：
有如下访客访问次数统计表 t_access_times
id ds counts
A 2015-01 5
A 2015-01 15
B 2015-01 5
A 2015-01 8
B 2015-01 25
A 2015-01 5
A 2015-02 4
A 2015-02 6
B 2015-02 10
B 2015-02 5
A 2015-03 16
A 2015-03 22
B 2015-03 23
B 2015-03 10
B 2015-03 1
需要输出报表：t_access_times_accumulate
id ds 月访问总计 月最大访问次数 累计访问总计
A 2015-01 33 33 33
A 2015-02 10 33 43
A 2015-03 38 38 81
B 2015-01 30 30 30
B 2015-02 15 30 45
B 2015-03 34 34 79
思路：
能group by先group by，先将group by统计的结果作为子查询，然后在这之上使用一系列窗口函数
sql：
select
user_id,
ds,
counts as `月总`,
max(counts) over(distribute by user_id sort by ds) as `月最大`,
sum(counts) over(distribute by user_id sort by ds) as `月累计`
from
(
select
user_id,
ds,
sum(counts) counts
from t_access_times
group by user_id,ds
)t1;
2）求出每个栏目的被观看次数及累计观看时长
数据:
vedio表
Uid channl min
1,1,23
2,1,12
3,1,12
4,1,32
5,1,342
6,2,13
7,2,34
8,2,13
9,2,134
sql：
select
channel,
count(1) channel_cnt,
sum(min) total_time
from t1
group by channel
;
3）每个店铺的当月销售额和累计到当月的总销售额
数据：
店铺,月份,金额
a,01,150
a,01,200
b,01,1000
b,01,800
c,01,250
c,01,220
b,01,6000
a,02,2000
a,02,3000
b,02,100
c,02,350
c,02,280
a,03,350
a,03,25
sql：
select
shop_name,
month,
sum_amount,
sum(sum_amount) over(distribute by shop_name sort by month) total
from
(select
shop_name,
month,
sum(amount) sum_amount
from t3
group by shop_name,month
)tmp1;
7）连续登陆时间，连续访问时间天数这类一般用排名函数标记，然后求天数就用使用日期函数结合标记处理，然后分组统计天数
1）求每个用户的连续登录最长天数
id day
1 2019-05-01
1 2019-05-02
1 2019-05-03
1 2019-05-04
2 2019-05-01
2 2019-05-02
2 2019-05-03
2 2019-05-04
2 2019-05-06
2 2019-05-07
2 2019-05-08
2 2019-05-10
2 2019-05-12
sql：
select
id,
max(counts)
from
(select
id,
count(1) counts
from
(select
id,
date_sub(day,rm) new_day
from
(select
id,
day,
row_number() over(distribute by id sort by day) rm
from user_login
)t1
)t2
group by id,new_day
)t3
group by id
;
2）编写连续7天登录的总人数
数据:
t2表
Uid dt login_status(1登录成功,0异常)
1,2019-07-11,1
1,2019-07-12,1
1,2019-07-13,1
1,2019-07-14,1
1,2019-07-15,1
1,2019-07-16,1
1,2019-07-17,1
1,2019-07-18,1
2,2019-07-11,1
2,2019-07-12,1
2,2019-07-13,0
2,2019-07-14,1
2,2019-07-15,1
2,2019-07-16,0
2,2019-07-17,1
2,2019-07-18,0
3,2019-07-11,1
3,2019-07-12,1
3,2019-07-13,1
3,2019-07-14,1
3,2019-07-15,1
3,2019-07-16,1
3,2019-07-17,1
3,2019-07-18,1
sql：
select
count(1) `连续7天登录的总人数`
from
(select
tmp2.uid
from
(select
uid,
date_sub(dt,rn) new_dt
from
(select
uid,
dt,
login_status,
row_number() over(distribute by uid sort by dt asc) rn
from t2
where login_status=1
)tmp1
)tmp2
group by tmp2.new_dt,tmp2.uid
having count(1)>=7
)tmp3;
8）排名函数+lag（）+lead（）+nvl()求分差
1）实现每班前三名，分数一样并列，同时求出前三名 按名次排序的一次的分差
数据：
stu表
Stu_no class score
1,1901,90
2,1901,90
3,1901,83
4,1901,60
5,1902,66
6,1902,23
7,1902,99
8,1902,67
9,1902,87
结果数据：
class stu_no score rn rn1 rn_diff
1901 1 90 1 1 90
1901 2 90 2 1 0
1901 3 83 3 1 -7
1902 7 99 1 1 99
1902 9 87 2 2 -12
1902 8 67 3 3 -20
sql：
select
class,
stu_no,
score,
rn,
rn1,
score-nvl(lag(score,1) over(distribute by class sort by score desc),0) rn_diff
from
(select
class,
stu_no,
score,
row_number() over(distribute by class sort by score desc) rn,
dense_rank() over(distribute by class sort by score desc) rn1
from t1_stu
)t1
where rn<4
;
9）计算最近一次，第一次这类的，先排名row_number(asc或desc)+取第一个或者first_value(col) over()或last_value(col) over()
1）使用hql语句，,找到每一个用户在表中的最后一次行为?
t4:
uid time action
1,2019-09-07 12:22:23,read
1,2019-09-07 12:23:23,write
1,2019-09-07 12:26:23,like
1,2019-09-07 12:20:23,share
3,2019-09-07 12:28:23,like
3,2019-09-07 12:29:00,read
3,2019-09-07 12:32:33,comment
4,2019-09-07 12:22:23,read
4,2019-09-07 12:16:18,like
写法一
select
uid,
dt,
action
from
(select
uid,
dt,
action,
row_number() over(distribute by uid sort by dt desc) rn
from t4
)tmp1
where rn=1;
写法二
select
uid,
dt,
action
from
(select
uid,
dt,
action,
first_value(dt) over(distribute by uid sort by dt desc) last_dt
from t4
)tmp1
where dt=last_dt
;
10）分析函数grouping sets和cube使用
t4:
uid dt action
1,2019-09-07 12:22:23,read
1,2019-09-07 12:23:23,write
1,2019-09-07 12:26:23,like
1,2019-09-07 12:20:23,share
3,2019-09-07 12:28:23,like
3,2019-09-07 12:29:00,read
3,2019-09-07 12:32:33,comment
4,2019-09-07 12:22:23,read
4,2019-09-07 12:16:18,like
从使用分析函数，分别从日期，行为，日期+行为分析对应的用户数量
结果：
dt action cnt grouping__id
NULL write 1 1
NULL share 1 1
NULL read 3 1
NULL like 3 1
NULL comment 1 1
2019-09-07 NULL 9 2
2019-09-07 write 1 3
2019-09-07 share 1 3
2019-09-07 read 3 3
2019-09-07 like 3 3
2019-09-07 comment 1 3
1）grouping sets实现
select
dt,
action,
count(uid) cnt,
GROUPING__ID
from
(select
uid,
date_format(dt,'yyyy-MM-dd') dt,
action
from t4
)tmp1
group by action,dt
grouping sets(dt,action,(dt,action))
order by GROUPING__ID
;
2）cube实现
select
dt,
action,
count(uid) cnt,
GROUPING__ID
from
(select
uid,
date_format(dt,'yyyy-MM-dd') dt,
action
from t4
)tmp1
group by action,dt
with cube
order by GROUPING__ID
;
3）rollup实现（rollup是cube的子集，以最左侧的维度为主）可以观察出是一个上钻的过程
天和行为分析的用户总量->天分析的用户总量->用户总量
select
dt,
action,
count(uid) cnt,
GROUPING__ID
from
(select
uid,
date_format(dt,'yyyy-MM-dd') dt,
action
from t4
)tmp1
group by dt,action
with rollup
order by GROUPING__ID
;
扫一扫
1942
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
