---

Hive

窗口函数

概述

1. 窗口函数的作用是用于限制要处理的数据量的大小
2. 语法

分析函数 over(partition by 字段 order by 字段 rows between 起始范围 and 结束范围)

案例一

1. 原始数据

jack,2017-01-01,10
tony,2017-01-02,15
jack,2017-02-03,23
tony,2017-01-04,29
jack,2017-01-05,46
jack,2017-04-06,42
tony,2017-01-07,50
jack,2017-01-08,55
mart,2017-04-08,62
mart,2017-04-09,68
neil,2017-05-10,12
mart,2017-04-11,75
neil,2017-06-12,80
mart,2017-04-13,94

2. 建表

create table orders (name string, orderdate string, cost double) row format delimited fields terminated by ',';

3. 加载数据

load data local inpath '/opt/hivedemo/orders.txt' into table orders;

4. 需求一：查询2017年4月份消费的顾客名单以及2017年4月份产生的总的消费人次

select *, count(*) over() from orders where year(orderdate) = 2017 and month(orderdate) = 4;

5. 需求二：获取每一个顾客的消费明细以及每一位顾客的月度消费总额

select *, sum(cost) over (partition by name, month(orderdate)) from orders;

6. 需求三：获取每一个顾客的消费明细以及到当前日期为止的累计消费

select *, sum(cost) over(partition by name order by orderdate rows between unbounded preceding and current row) from orders;

7. 需求四：查询顾客的消费明细以及每一次消费之后上一次的消费时间

select *, lag(orderdate, 1) over(partition by name order by orderdate) from orders;

8. 需求五：获取最早的20%的顾客的消费名单 - 首先要先对数据进行排序，然后将数据分成5个桶，获取第一个桶的数据

select name from (select *, ntile(5) over(order by orderdate) as o from orders)tmp where o = 1;

案例二

1. 原始数据

Charles    Chinese    87
Charles    Math    95
Charles    English    68
Lily    Chinese    94
Lily    Math    56
Lily    English    84
William    Chinese    64
William    Math    86
William    English    84
Vincent    Chinese    65
Vincent    Math    85
Vincent    English    78

2. 建表语句

create table scores(name string, subject string, score int) row format delimited fields terminated by '\t';

3. 加载数据

load data local inpath '/opt/hivedemo/scores' into table scores;

4. 需求一：按学科，对每一个学生的成绩进行降序排序
5. 顺次排序

select *, row_number() over(partition by subject order by score desc) from scores;

2. 空位排序

select *, rank() over(partition by subject order by score desc) from scores;

3. 非空位排序

select *, dense_rank() over(partition by subject order by score desc) from scores;

其他操作

join

1. 在Hive中，类似于MySQL，提供了left join/right join/inner join/full outer join方式。在使用的时候，如果不指定，那么默认使用的是inner join
2. 案例
3. 建立orders表

create table orders (orderid int, orderdate string, productid int, num int) row format delimited fields terminated by ' ' location '/orders';

2. 建立products表

create table products (productid int, name string, price double) row format delimited fields terminated by ' ' location '/products';

3. 连接查询

# 左连
select * from orders o left join products p on o.productid = p.productid;
# 右连
select * from orders o right join products p on o.productid = p.productid;
# 内连
select * from orders o inner join products p on o.productid = p.productid;
# 全外连接
select * from orders o full outer join products p on o.productid = p.productid;

4. 需求：每一天卖了多少钱

select o.orderdate, sum(o.num * p.price) from orders o join products p on o.productid = p.productid group by o.orderdate;

having

1. 如果需要对指定字段来进行查询，那么此时考虑使用where；如果需要对聚合结果进行查询，此时需要使用having
2. 案例
3. 原始数据

1 Apollo 4900
1 Billy 5100
1 Cary 4800
1 Dylan 5000
1 Ford 4700
2 Apollo 5300
2 Billy 4600
2 Cary 4700
2 Dylan 5100
2 Ford 4500
3 Apollo 5200
3 Billy 4300
3 Cary 4600
3 Dylan 5200
3 Ford 4800

2. 建表

create table salaries (id int, name string, salary double) row format delimited fields terminated by ' ';

3. 加载数据

load data local inpath '/opt/hivedemo/salaries' into table salaries;

4. 需求：获取平均工资达到5000的员工

# 方式一：子查询
select name, avgsalary from (select name, avg(salary) as avgsalary from salaries group by name)tmp where avgsalary >= 5000;
# 方式二：having
select name, avg(salary) as avgsalary from salaries group by name having avgsalary >= 5000;

beeline

1. beeline是Hive提供的一种用于进行远程连接的方式，底层利用了JDBC的方式来进行
2. 步骤
3. 退出Hive，关闭Hadoop
4. 编辑文件

vim /opt/hadoop-3.1.3/etc/hadoop/core-site.xml

在文件中添加

<property>
<name>hadoop.proxyuser.root.hosts</name>
<value>*</value>
</property>
<property>
<name>hadoop.proxyuser.root.groups</name>
<value>*</value>
</property>

3. 重启Hadoop

start-all.sh

4. 启动Hive的后台进程

hive --service hiveserver2 &
hive --service metastore &

5. 进行远程连接

beeline -u jdbc:hive2://192.168.233.133:10000/hivedemo -n root

SerDe

1. SerDe(Serializar - Deserializar)是Hive中提供的一套序列化反序列化机制，实际过程中经常使用这个机制来处理一些不规则的数据
2. SerDe在 使用的时候需要指定正则表达式，在正则表达式中需要给定捕获组，通过捕获组来一一对应表中的字段
3. 案例
4. 原始数据

192.168.120.23 -- [30/Apr/2018:20:25:32 +0800] "GET /asf.avi HTTP/1.1" 304 -
192.168.120.23 -- [30/Apr/2018:20:25:32 +0800] "GET /bupper.png HTTP/1.1" 304 -
192.168.120.23 -- [30/Apr/2018:20:25:32 +0800] "GET /bupper.css HTTP/1.1" 304 -
192.168.120.23 -- [30/Apr/2018:20:25:33 +0800] "GET /bg-button HTTP/1.1" 304 -
192.168.120.23 -- [30/Apr/2018:20:25:33 +0800] "GET /bbutton.css HTTP/1.1" 304 -
192.168.120.23 -- [30/Apr/2018:20:25:33 +0800] "GET /asf.jpg HTTP/1.1" 304 -
192.168.120.23 -- [30/Apr/2018:20:25:33 +0800] "GET /tomcat.css HTTP/1.1" 304 -
192.168.120.23 -- [30/Apr/2018:20:25:33 +0800] "GET /tomcat.png HTTP/1.1" 304 -
192.168.120.23 -- [30/Apr/2018:20:25:33 +0800] "GET /tbutton.png HTTP/1.1" 304 -
192.168.120.23 -- [30/Apr/2018:20:25:33 +0800] "GET /tinput.png HTTP/1.1" 304 -
192.168.120.23 -- [30/Apr/2018:20:25:33 +0800] "GET /tbg.css HTTP/1.1" 304 -
192.168.120.23 -- [30/Apr/2018:20:25:34 +0800] "GET /tomcat.css HTTP/1.1" 304 -
192.168.120.23 -- [30/Apr/2018:20:25:34 +0800] "GET /bg.css HTTP/1.1" 304 -
192.168.120.23 -- [30/Apr/2018:20:25:34 +0800] "GET /bg-button.css HTTP/1.1" 304 -
192.168.120.23 -- [30/Apr/2018:20:25:34 +0800] "GET /bg-input.css HTTP/1.1" 304 -
192.168.120.23 -- [30/Apr/2018:20:25:34 +0800] "GET /bd-input.png HTTP/1.1" 304 -
192.168.120.23 -- [30/Apr/2018:20:25:34 +0800] "GET /bg-input.png HTTP/1.1" 304 -
192.168.120.23 -- [30/Apr/2018:20:25:34 +0800] "GET /music.mp3 HTTP/1.1" 304 -

2. 方式一：不使用SerDe
   1. 需要先创建临时表用于存储数据

create table logs_tmp(log string);

2. 将数据加载到临时表中

load data local inpath '/opt/hivedemo/tomcat.log' into table logs_tmp;

3. 创建表用于存储解析之后的结果

create table logs(ip string, logdate string, timezone string, request_Way string, resource string, protocol string, stateid int) row format delimited fields terminated by ' ';

4. 解析数据

insert into table logs select arr[0], arr[1], arr[2],arr[3], arr[4], arr[5], cast(arr[6] as int) from (select split(regexp_replace(log, '(.*) \-\- \\[(.*) (.*)\\] \"(.*) (.*) (.*)\" (.*) \-', '$1 $2 $3 $4 $5 $6 $7'), ' ') as arr from logs_tmp)tmp;

3. 方式二：使用SerDe
   1. 建表用于管理数据

create table logs(ip string, logdate string, timezone string, request_way string, resource string, protocol string, stateid int) row format serde 'org.apache.hadoop.hive.serde2.RegexSerDe' with serdeproperties ("input.regex" = "(.*) \-\- \\[(.*) (.*)\\] \"(.*) (.*) (.*)\" (.*) \-") stored as textfile;

2. 加载数据

load data local inpath '/opt/hivedemo/tomcat.log' into table logs;

View

1. 视图只能读(select)不能写(insert)，需要从原来的表中来抽取字段构成视图 - 组建视图的目的是为了提高查询效率
2. 视图分为物化视图和虚拟视图：如果将抽取出来的视图存储到磁盘上，此时这种视图称之为物化视图；如果将抽取出来的视图维系在内存中，此时这种视图称之为虚拟视图
3. Hive只支持虚拟视图，不支持物化视图
4. 建立视图

create view logs_view as select ip, logdate, resource from logs;

5. 在建立视图的时候，并没有指定封装的select语句，当第一次使用视图的时候，才会触发这个封装的select语句
6. 删除视图

drop view logs_view;

注意问题

1. Hive将数据最终以文件的形式存储到HDFS上，在存储的时候可以采取不同的文件格式
2. 如果不指定，那么Hive默认会将数据以textfile(文本)来存储，在text格式下，不支持update和delete操作
3. Hive还支持orc和parquet格式，不同于textfile格式，orc和parquet格式采用的是物理列逻辑行的格式来存储数据 - 即意味着orc和parquet在存储数据的时候是以列为单位来进行存储。orc和parquet格式可以采取更好的压缩方式，同时orc和parquet支持update和delete，但是update和delete的效率非常低

排序

1. 不同于MySQL，Hive中提供了两种不同的排序方式：order by和sort by
2. 案例
3. 原始数据

3 Max 89
1 Eric 89
3 Paul 82
1 Hank 95
2 Larry 74
1 Henry 84
2 Justin 82
3 Tim 85
2 ken 84
1 Ivan 85
3 Nick 84
2 Leo 82
2 Mars 86
1 Jim 74
3 Reed 81

2. 建表管理数据

create table scores(class int, name string, score int) row format delimited fields terminated by ' ';

3. 加载数据

load data local inpath '/opt/hivedemo/scores' into table scores;

4. Hive会将SQL转化为MapReduce程序来执行 - 在MapReduce中，默认只有1个ReduceTask - 在只有1个ReduceTask的前提下，order by和sort by没有区别
   1. order by排序

insert overwrite directory '/result/orderby1' row format delimited fields terminated by '\t' select * from scores order by score desc;

2. sort by排序

insert overwrite directory '/result/sortby1' row format delimited fields terminated by '\t' select * from scores sort by score desc;

5. 设置多个ReduceTask，通过命令set mapred.reduce.tasks = 3;来设置 - order by忽略ReduceTask的数量直接对数据进行整体的排序，sort by则是将数据分配到3个ReduceTask中，然后再在每一个ReduceTask内部进行排序
   1. order by排序

insert overwrite directory '/result/orderby2' row format delimited fields terminated by '\t' select * from scores order by score desc;

2. sort by排序

insert overwrite directory '/result/sortby2' row format delimited fields terminated by '\t' select * from scores sort by score desc;

6. 需求：分班级，在每一个班级内将学生按照分数进行降序排序

# 方式一：可以使用窗口函数
insert overwrite directory '/result/over' row format delimited fields terminated by ' ' select *, rank() over(partition by class order by score desc) from scores;
# 方式二：使用distribute by
insert overwrite directory '/result/distributeby' row format delimited fields terminated by ' ' select * from scores distribute by class sort by score desc;

Hive和数据库的区别

1. 存储介质：Hive将数据以文本形式落地到HDFS的磁盘上，因为HDFS的副本特性，所以Hive天然支持数据的可靠性；MySQL会将数据自动的落地到本地磁盘上，也就意味着如果本地磁盘损坏，数据就可能会产生丢失
2. 应用场景：Hive适应于OLAP场景，强调对数据进行分析和管理；数据库是用于OLTP场景，强调对数据进行存储以及操作的事务问题 - Hive默认不支持事务
3. 数据量：Hive依靠于HDFS来存储数据，因此一个Hive集群能够存储和处理的数据量大小实际上是由HDFS集群的大小来决定，HDFS集群可以轻松的扩展到上千台数据，可以存储几十真至于上百PB的数据；数据库主要依靠本地磁盘来存储数据，并且数据库的扩展性相对而言是比较差的(Oracle能够大概最多扩展到100台服务器上)，因此导致数据库存储的数据量相对比较低
4. 索引机制：Hive3.x不支持索引，在Hive1.x和Hive2.x中支持索引机制，但是不会自动建立索引而是需要用户在使用的时候手动建立索引表并且需要手动更新索引；在数据库中，会自动的针对主键建立索引，并且会自动更新主键索引
5. 效率：Hive在执行的时候，需要将写的SQL转化为MapReduce程序，之后再交给YARN来执行，因此Hive的效率相对比较低，更适合于离线分析/离线批处理场景；MySQL在执行的时候，会将写的SQL转化为执行计划树，然后执行过程，此时MySQL的执行效率相对而言是比较高的

---

Program 实训

Hadoop Hive

本博客所有文章除特别声明外，均采用 CC BY-SA 4.0 协议 ，转载请注明出处！

项目实训第十天 上一篇

项目实训第八天 下一篇