6 Hive

最新推荐文章于 2025-01-13 17:04:57 发布

stable_阿甘

最新推荐文章于 2025-01-13 17:04:57 发布

阅读量321

点赞数

分类专栏： Hadoop

本文链接：https://blog.youkuaiyun.com/stable_zl/article/details/108065098

版权

Hadoop 专栏收录该内容

15 篇文章

订阅专栏

一、Hive 是什么

Hive 是构建在 Hadoop 之上的数据仓库平台；它通过 SQL 解析引擎把 SQL 语句转译成 MapReduce 作业，并在 Hadoop 上运行；Hive 表是 HDFS 的文件目录，一个表对应一个目录名，如果有分区，则分区值对应子目录。

二、Hive 架构

在这里插入图片描述

解释图中元素：

1、核心部分

1）解析器：将SQL字符串转换成抽象语法树AST，这一步一般用第三方工具完成；对AST进行语法分析，比如表是否存在、字段是否存在、SQL语义是否有误；

2）编译器：把AST翻译成逻辑执行计划，即将hql翻译成MapReduce任务；

3）优化器：对逻辑执行计划进行优化，优化方案随着模版的不断迭代而改进；

4）执行器：顺序执行所有的 job，如果 task 链不存在依赖关系，可以采用并发执行的方式执行 job。

2、元数据库

元数据用于存放 Hive 库的基础信息，它存在关系数据库中，如 mysql。元素据包括：数据库信息、表名、列表和列的分区及其属性，表的属性，表的数据所在的目录。

三、托管表与外部表

Hive 表分为两类，一个是托管表（内部表），一个是外部表。

托管表是只在 hive 中使用，外部表示 hive 之外也使用，它们下面两个方面有区别：

3.1 数据存储

托管表：数据存储在 hive 仓库目录下，是指定的。我指定为：/apps/hive/warehouse

外部表：外部表可存在 hdfs 的任意目录下。

3.2 数据删除

托管表：删除元数据和数据。

外部表：只删除元数据。

四、分区和桶

4.1 分区

分区其实就是在一个 hdfs 大的文件夹下面的子文件夹，它不是表的上面的结构，分区可以帮助我们来缩小查询的范围从而提高效率。在导入数据的时候，可以根据数据中的特定列把数据放到指定分区中，即指定文件夹中。

4.2 桶

桶是表上面的附加结构，可以提高查效率。桶对应于 MapReduce 的输出文件分区，一个作业产生的桶和 reduce 任务个数相等。

我个人理解是表对应的是文件夹，分区是文件夹下的子文件夹，而桶就是对应于子文件夹里的文件了，我们把具有相同特征的内容放到一个文件里，即一个桶中。分区针对的是数据的存储路径；分桶针对的是数据文件。

五、Hive和数据库的比较

5.1 查询语言

hive与数据库唯一相似的地方就是查询语言相似，sql和hql语法和类似

5.2 数据存储位置

hive是建立在Hadoop之上的，所以数据都是存储在hdfs中，而数据库则将数据保存在本地文件系统中。

5.3 数据更新

hive中不支持数据的更新，要想更新数据就必须先把之前的文件删除，然后再上传，而数据库则支持灵活更新。

5.4 索引

5.5 执行引擎

hive的引擎为mr、tez、spark，而数据库有自己的执行引擎。

5.6 执行延迟

hive的执行需要启动jvm和mapreduce作业，所以如果处理小规模的数据时，会明显慢于数据库；

5.7 可扩展新

因为hive构建于hadoop上，所以可以狠方面的扩展节点数量，而数据库由于ACID语义的严格限制，扩展性非常有限。

5.8 数据规模

hive支持的数据规模远远大于数据库

六、查询

6.1 排序

order by： 保证全局有序，在一个 reducer 中进行排序并输出。在 strict mode(hive.mapred.mode=strict)下，必须搭配 limit 使用，这是为了防止这个 reducer 任务花费的时间过长
sort by：sort by 在将数据发送给 reducer 前会将数据排序，sort by 只能保证同一个 reducer 中的数据有序
distribute by：对查询数据进行分区
cluster by：distribute by 和 sort by 的结合，默认这两个后面跟的列名相同，如果想用不同的列，则必须分开指定：

SELECT col1, col2 FROM t1 DISTRIBUTE BY col1 SORT BY col2 DESC

6.2 函数

1. nvl

NVL：给值为NULL的数据赋值，它的格式是NVL( string1, replace_with)。它的功能是如果string1为NULL，则NVL函数返回replace_with的值，否则返回string1的值，如果两个参数都为NULL ，则返回NULL

2. 行转列

concat(string A/col, string B/col···)：返回输入字符串连接后的结果，支持任意个输入字符串；
1. concat_ws(separator, str1, str2,…)：特殊形式的concat，第一个参数指定分隔符，如果分隔符为null，返回值也为null
2. collect_set(col)：将某字段的值进行去重汇总，产生array类型字段

例：把星座和血型一样的人归类到一起，

name	constellation	blood_type
孙悟空	白羊座	A
大海	射手座	A
宋宋	白羊座	B
猪八戒	白羊座	A
凤姐	射手座	A

结果如下：

射手座,A 大海|凤姐

白羊座,A 孙悟空|猪八戒

白羊座,B 宋宋

解题：

select bn, concat_ws("|", collect_set(name)) 
from 
	(select concat_ws(",", constellation, blood_type) bn, name from tb) t1
group by t1.bn;

3. 列转行

explode(col)：将hive一列中复杂的array或者map结构拆分成多行；

lateral view：

用法：lateral view udtf(expression) tableAlias as columnAlias

解释：用于和split，explode等udtf一起使用，它能将一列数据拆分成多行数据，在此基础上可以对拆分后的数据进行聚合

例：

元数据集：

movie	category
《疑犯追踪》	悬疑,动作,科幻,剧情
《Lie to me》	悬疑,警匪,动作,心理,剧情
《战狼2》	战争,动作,灾难

将电影分类中的数组数据展开。结果如下：

《疑犯追踪》悬疑

《疑犯追踪》动作

《疑犯追踪》科幻

《疑犯追踪》剧情

《Lie to me》悬疑

《Lie to me》警匪

《Lie to me》动作

《Lie to me》心理

《Lie to me》剧情

《战狼2》战争

《战狼2》动作

《战狼2》灾难

解题：

创建hive表并导入数据，把category导入成array类型

create table movie_info(
    movie string, 
    category array<string>) 
row format delimited fields terminated by "\t"
collection items terminated by ",";

load data local inpath "/opt/module/datas/movie.txt" into table movie_info;

查询语句

select 
	movie, category_name
from 
	movie_info lateral view explode(category) table_tmp as category_name;

4. 窗口函数

over：用 over 关键字就可以开一个窗口，其中可以指定窗口的范围，还可以指定分区和排序，如果指定了 order by，那么窗口范围是 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW，如果没有指定 order by，那么窗口的范围是 ROW BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING

CURRENT ROW：当前行

n PRECEDING：往前n行数据

n FOLLOWING：往后n行数据

UNBOUNDED：起点，UNBOUNDED PRECEDING 表示从前面的起点， UNBOUNDED FOLLOWING表示到后面的终点
lag(col, n)：往前第n行数据
lead(col, n)：往后第n行数据
ntile(n)：把有序分区中的行分发到指定数据的组中，每个组有编号，编号从1开始
rank()：

rank()：排名相同时会重复，总数不会变

dense_rand()：排名相同时会重复，总数会减少

row_number()：排名不重复

例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

需求：

（1）查询在2017年4月份购买过的顾客及总人数

（2）查询顾客的购买明细及月购买总额

（3）上述的场景,要将cost按照日期进行累加

（4）查询顾客上次的购买时间

（5）查询前20%时间的订单信息

创建hive表并导入数据

create table business(
name string, 
orderdate string,
cost int
) ROW FORMAT DELIMITED FIELDS TERMINATED BY ',';

load data local inpath "/opt/module/datas/business.txt" into table business;

（1）查询在2017年4月份购买过的顾客及总人数

select
	name,
	count(*) over()
from business
where substring(orderdate, 1, 7) = '2017-04'
group by name;

（2）查询顾客的购买明细及月购买总额

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

（3）上述的场景,要将cost按照日期进行累加

select name,orderdate,cost, 
	sum(cost) over() as sample1,--所有行相加 
	sum(cost) over(partition by name) as sample2,--按name分组，组内数据相加 
	sum(cost) over(partition by name order by orderdate) as sample3,--按name分组，组内数据累加 
	sum(cost) over(partition by name order by orderdate rows between UNBOUNDED PRECEDING and current row ) as sample4 ,--和sample3一样,由起点到当前行的聚合 
	sum(cost) over(partition by name order by orderdate rows between 1 PRECEDING and current row) as sample5, --当前行和前面一行做聚合 
	sum(cost) over(partition by name order by orderdate rows between 1 PRECEDING AND 1 FOLLOWING ) as sample6,--当前行和前边一行及后面一行 
	sum(cost) over(partition by name order by orderdate rows between current row and UNBOUNDED FOLLOWING ) as sample7 --当前行及后面所有行 
from business;

（4）查询顾客上次购买的时间

select 
	name,
	orderdate,
	lag(orderdate, 1, '1900-01-01') over(partition by name order by orderdate)
from business;

（5）查询前20%时间的订单信息

select 
	*
from 
	(select 
		name, 
		orderdate,
		ntile(5) over(order by orderdate) sorted
	from business) t1
where sorted = 1;

例2：

数据准备：

name	subject	score
孙悟空	语文	87
孙悟空	数学	95
孙悟空	英语	68
大海	语文	94
大海	数学	56
大海	英语	84
宋宋	语文	64
宋宋	数学	86
宋宋	英语	84
婷婷	语文	65
婷婷	数学	85
婷婷	英语	78

需求：

计算每门学科成绩排名。

创建hive表并导入数据

create table score(
name string,
subject string, 
score int) 
row format delimited fields terminated by "\t";
load data local inpath '/opt/module/datas/score.txt' into table score;

查询语句

select 
	name,
	subject,
	score,
	rank() over(partition by subject order by score desc),
	dense rank() over(partition by subject order by score desc) drp,
	row_number() over(partition by subject order by score desc) rmp
from score;

结果：

name subject score rp drp rmp

孙悟空数学 95 1 1 1

宋宋数学 86 2 2 2

婷婷数学 85 3 3 3

大海数学 56 4 4 4

宋宋英语 84 1 1 1

大海英语 84 1 1 2

婷婷英语 78 3 2 3

孙悟空英语 68 4 3 4

大海语文 94 1 1 1

孙悟空语文 87 2 2 2

婷婷语文 65 3 3 3

宋宋语文 64 4 4 4

七、存储和压缩

7.1 存储格式

1.行式存储和列式存储

在这里插入图片描述

（行式存储和列式存储）

行式存储：当查询满足条件的一整行数据时，行式存储的速度更快，因为列式存储需要去每个字段聚集的地方寻找对应的列，而行式存储只需要找到其中一个字段，其余字段都在附近的地方。

列式存储：因为每个字段都聚集存储，所以如果查询少数几个字段时，可以大大减少读取的数据量。

2. TextFile格式

TextFile每一行是一条记录，每行都以\n结尾。数据不做压缩，磁盘开销大，数据解析开销大。

3. SequenceFile格式

SequenceFile是Hadoop API提供的一种二进制文件支持，使用方便、可分割，可压缩。

4. Orc格式

Orc提供了一种将数据存储在hive表中的高效方法。该存储格式是为了克服其他存储格式的缺点而设计的，所以在hive中具有最高的性能，以及最小的存储空间。

5. Parquet格式

Parquet是一个面向列的二进制存储格式，是spark的默认存储格式，所以使用parquet可以极大地优化spark地调度和执行。

存储格式	存储方式	特点
TextFile	行存储	存储空间消耗比较大，并且压缩地text无法分割和合并，查询效率最低
SequenceFile	行存储	存储空间消耗最大，压缩地文件可以分割和合并，查询效率高
Orc	行列结合，数据按行分块，每块按列存储	存储空间消耗最小，查询效率最高
Parquet	列存储	相较于Orc，Parquet空间消耗较大，查询效率较低，但是在spark on hive的情况下，性能最好

7.2 压缩格式

1. 为什么使用压缩

节省数据占用的磁盘空间
加快数据在磁盘和网络中的传输速度，从而提高系统的处理速度

2. 常用压缩格式的对比

特性对比

压缩格式	codec类	算法	扩展名	多文件	splitable	native	hadoop自带
deflate	DeflateCodec	deflate	.deflate	否	否	是	是
gzip	GzipCodec	deflate	.gz	否	否	是	是
bzip2	Bzip2Codec	bzip2	.bz2	是	是	否	是
lzo	LzopCodec	lzo	.lzo	否	是	是	否
snappy	SnappyCodec	snappy	.snappy	否	否	是	否