一、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 排序

1. order by

​ 全局排序，Reducer的数量一定为1，与设置的数量无关

2. sort by

​ 每个MapReduce内部排序，Reducer的个数可以手动设置，如果设置为1，则效果和order by一样；

​ 因为系统并不知道我们想要根据什么分区，所以默认为随机分区

3. distribute by

​ 与 sort by 结合使用，指定分区

4. cluster by

​ 当 sort by 的字段和 distribute by 字段相同时，可以使用 cluster by 代替

6.2 函数

1. nvl

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

2. 行转列

1. concat(string A/col, string B/col···)：返回输入字符串连接后的结果，支持任意个输入字符串；

   2. concat_ws(separator, str1, str2,…)：特殊形式的concat，第一个参数指定分隔符，如果分隔符为null，返回值也为null
   3. 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》 灾难

解题：

1. 创建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;
   

2. 查询语句

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

4. 窗口函数

1. over：指定分析函数的数据窗口大小，窗口大小可能会随着行的变化而变化，over 中可以指定分区和排序、

   CURRENT ROW：当前行

   n PRECEDING：往前n行数据

   n FOLLOWING：往后n行数据

   UNBOUNDED：起点，UNBOUNDED PRECEDING 表示从前面的起点， UNBOUNDED FOLLOWING表示到后面的终点

2. lag(col, n)：往前第n行数据

3. lead(col, n)：往后第n行数据

4. ntile(n)：把有序分区中的行分发到指定数据的组中，每个组有编号，编号从1开始

5. 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月份购买过的顾客及总人数

selectname,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) sortedfrom 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	否	否	是	否

  文件的可分割性在 Hadoop 中是很非常重要的，它会影响到在执行作业时 Map 启动的个数，从而会影响到作业的执行效率！

• 性能对比

  压缩格式	压缩比	压缩速率	解压速率
  gzip/deflate	13.4%	21MB/s	118MB/s
  bzip2	13.2%	2.4MB/s	9.5MB/s
  lzo	20.5%	135MB/s	410MB/s
  snappy	22.2%	172MB/s	409MB/s

3. 使用情景

• gzip
  • 当每个文件压缩之后在一个块大小内，可以考虑用gzip压缩格式。
• bzip2
  • 对速度要求不高，但需要较高压缩率并且需要支持split的情况
• lzo
  • 一个很大的文本文件，压缩之后还大于200M的可以考虑用lzo，而且单个文件越大，lzo的优点越明显
• snappy
  • map输出的数据比较大的时候，作为map到reduce的中间数据压缩格式