本文深入解读Hive作为Hadoop数据仓库工具的特性，包括其与关系型数据库的区别，优缺点分析，架构细节，基本操作如创建数据库、表及数据类型，以及SQL查询语法、排序方法、内置函数和经典案例。探讨了数据倾斜问题及企业级优化策略。
一、Hive概述
1.1 Hive简介
Hive是基于Hadoop的一个数据仓库工具，它可以将结构化或半结构化的数据文件转化为一张数据库表，并提供简单的sql查询功能。
Hive产生的原因：非Java编程者可通过sql语句对HDFS的数据做mapreduce操作。
Hive本质：将HQL转换成MapReduce程序
Hive处理的数据存储在HDFS上
交给MapReduce计算数据
执行程序在yarn上
1.2 Hive和关系型数据库区别
1.3 Hive的优缺点
优点
1)接口采用SQL语法,提供快速开发能力
2)避免去写MapReduce,减少开发者学习成本
3)Hive延迟较高,适合处理大规模数据分析,对实时性要求较低的场景
4)Hive支持用户自定义函数,用户可以根据自己的需求来是实现自己的函数
缺点
1)Hive的HQL表达能力不强
迭代算法无法表达
数据挖掘方面不擅长，由于MapReduce数据处理流程的限制，效率更高的算法却无法实现。
2)Hive的效率较低
Hive自动生成的MapReduce作业，通常情况下不够智能化
Hive调优比较困难，粒度较粗
1.4 Hive架构
1）用户接口主要由三个：CLI，JDBC/ODBC，WEB UI。其中最常用的是CLI，CLI启动的时候，会同时启动一个HIVE副本。Client是Hive的客户端，用户连接至Hive Server。在启动 Client模式的时候，需要 指出Hive Server所在节点，并且在该节点启动Hive Server。 WUI是通过浏览器访问Hive。
2）HIVE将元数据存储在数据库中，如mysql、derby。HIVE中的元数据包括表的名字，表的列和分区及其属性，表的属性，表的数据所在目录。
3）解释器（SQL Parser）、编译器（Compiler）、优化器（Optimizer）完成HQL查询语句从词法分析、语法分析、编译、优化以及查询计划的生成。生成的查询计划存储在HDFS中并随后在执行器Executor调用MapReduce执行。
解释器：将HQL字符串转换为抽象语法树AST，通过使用第三方工作完成，比较antlr;对AST进行语法分析，比如表是否存在、字段是否存在、HQL语义是否存在错误等。
编译器：将AST编译生成逻辑执行计划。
优化器：对逻辑执行计划进行优化。
执行器：把优化后的逻辑执行计划转换成可以运行的物理计划。对于HIve来说就是MR/Spark。
4）Hive的数据存储在HDFS中，大部分的查询计算由MapReduce完成（包含*的查询，比如select * from tbl不会生成mapreduce任务）。
Hive架构
编译器将一个Hive SQL转换为操作符
操作符是HIve的最小处理单元
每个操作符代表HDFS的一个操作或一道mapreduce作业
ANTLR词法语法分析工具解析hql
二、Hive的基本操作
2.1 Hive库操作
2.1.1 创建数据库
1）创建一个数据库，在HDFS上的默认路径是/hive/warehouse/*.db
create database shop;
2）避免要创建的数据库已经存在错误，增加if not exists判断。（标准写法）
create database if not exists shop;
2.1.2 创建数据库和位置
create database if not exists school location '/school.db';
2.1.3 修改数据库
用户可以使用ALTER DATABASE命令为某个数据库的DBPROPERTIES设置键-值对属性值，来描述这个数据 库的属性信息。数据库的其他元数据信息都是不可更改的，包括数据库名和数据库所在的目录位置。
alter database school set dbproperties('createtime'='20201213');
2.1.4 数据库详情信息
1）显示数据库（根据like过滤）
show databases like 's*';
2）查看详情
desc database school;
2.1.5 删除数据库(如果数据库不为空，使用cascade命令进行强制删除。)
drop database if exists school cascade;
2.2 Hive 数据类型
2.2.1 基础数据类型
基础数据类型跟mysql数据类型相同
2.2.2 复杂数据类型
Hive有三种复杂数据类型ARRAY、MAP 和 STRUCT。ARRAY和MAP与Java中的Array和Map类似，而STRUCT与C语言中的Struct类似，它封装了一个命名字段集合，复杂数据类型允许任意层次的嵌套。
2.3.1 创建表
根据数据创建表结构
案例1：简单用户信息
复杂人员信息
2.4 Hive内外部表
2.4.1 Hive内部表
当创建好表的时候，HDFS会在当前表所属的库中创建一个文件夹
当设置表路径的时候，如果直接指向一个已有的路径,可以直接去使用文件夹中的数据
当load数据的时候，就会将数据文件存放到表对应的文件夹中
而且数据一旦被load，就不能被修改
我们查询数据也是查询文件中的文件,这些数据最终都会存放到HDFS
当我们删除表的时候，表对应的文件夹会被删除，同时数据也会被删除
2.4.2 Hive
外部表说明
外部表因为是指定其他的hdfs路径的数据加载到表中来，所以hive会认为自己不完全独占这份
数据
删除hive表的时候，数据仍然保存在hdfs中，不会删除。
操作案例:
分别创建dept，emp，salgrade。并加载数据。
2.5 Hive载入数据
2.6 Hive导出数据
将表中的数据备份
将查询结果存放到本地
2.7 Hive分区表
假如现在我们公司一天产生3亿的数据量，那么为了方便管理和查询，
建立分区（可按日期 部门等具体业务分区）。
分门别类的管理。
2.7.1 静态分区
静态分区（SP）static partition
创建单分区表语法
创建多分区语法
2.7.2 动态分区
静态分区与动态分区的主要区别在于静态分区是手动指定，而动态分区是通过数据来进行判断。
详细来说，静态分区的列是在编译时期通过用户传递来决定的；动态分区只有在SQL执行时才能决
定。
开启动态分区首先要在hive会话中设置如下的参数
如果静态分区的话，我们插入数据必须指定分区的值。
如果想要插入多个班级的数据，我要写很多SQL并且执行24次很麻烦。
而且静态分区有可能会产生数据错误问题
如果使用动态分区，动态分区会根据select的结果自动判断数据应该load到哪儿分区去。 
2.8 分桶表
2.8.1 业务场景
数据分桶的适用场景：
分区提供了一个隔离数据和优化查询的便利方式，不过并非所有的数据都可形成合理的分区，
尤其是需要确定合适大小的分区划分方式
不合理的数据分区划分方式可能导致有的分区数据过多，而某些分区没有什么数据的尴尬情况
分桶是将数据集分解为更容易管理的若干部分的另一种技术。
分桶就是将数据按照字段进行划分，可以将数据按照字段划分到多个文件当中去。
2.8.2 数据分桶原理
Hive采用对列值哈希，然后除以桶的个数求余的方式决定该条记录存放在哪个桶当中。
bucket num = hash_function(bucketing_column) mod num_buckets
列的值做哈希取余 决定数据应该存储到哪个桶
2.8.3 数据分桶优势
方便抽样
使取样（sampling）更高效。在处理大规模数据集时，在开发和修改查询的阶段，如果能在
数据集的一小部分数据上试运行查询，会带来很多方便
提高join查询效率
获得更高的查询处理效率。桶为表加上了额外的结构，Hive 在处理有些查询时能利用这个结
构。具体而言，连接两个在（包含连接列的）相同列上划分了桶的表，可以使用 Map 端连接
（Map-side join）高效的实现。比如JOIN操作。对于JOIN操作两个表有一个相同的列，如果
对这两个表都进行了桶操作。那么将保存相同列值的桶进行JOIN操作就可以，可以大大较少
JOIN的数据量。
2.8.4 数据分桶实战
2.8.5 数据抽样算法
桶表抽样：
随机抽样:
三、Hive查询语法
3.1 Hive独特的排序
3.1.1 全局排序
order by会对输入的数据做全局排序，因此只有一个reduce，会导致当输入规模较大时，需要较长的计算时间。
使用order by子句排序，（ascend）升序|desc降序
3.1.2 局部排序
sort by不是全局排序，其在数据进入reducer前完成排序
如果使用sort by进行排序，并且设置mapred.reduce.task>1，则sort by只保证每个reducer的输出有序，不保证全局有序。
3.1.3 分区排序
distribute by(字段)根据指定的字段将数据分到不同的reducer，且分发算法是hash散列。
类似于MR中的partition进行分区，结合sort by使用。（注意：distribute by 要在sort by之前）
对于distribute by进行测试，一定要多分配reduce进行处理，否则无法看到distribute的效果
3.1.4 分区并排序
cluster by（字段）处理具有distribute by的功能外，还会对该字段进行排序
cluster by=distribute by+sort by 只能默认升序，不能降序。
3.2 Hive内置函数
3.2.1 独树一帜的UDTF
-- explode 可以将一组数组的数据变成一列表
select explode(split(types,"-")) from t_movie1;
-- lateral view 表生成函数，可以将explode的数据生成一个列表
select id,name,type from t_movie1,lateral view explode(split(types,"- "))typetable as type;
-- collect_set()和collect_list()都是对列转成行，区别就是list里面可重复而set里面是去
重的
-- concat_ws(':',collect_set(type)) ':' 表示你合并后用什么分隔，
collect_set(stage)表示要合并表中的那一列数据
select id,concat_ws(':',collect_set(type)) as types from t_movie2 group by
id;
3.3 Hive窗口函数
普通的聚合函数每组(Group by)只返回一个值，而开窗函数则可为窗口中的每行都返回一个值。
简单理解，就是对查询的结果多出一列，这一列可以是聚合值，也可以是排序值。
开窗函数一般就是说的是over（）函数，其窗口是由一个 OVER 子句 定义的多行记录
开窗函数一般分为两类,聚合开窗函数和排序开窗函数。
测试数据
3.3.1 聚合开窗函数
rows必须跟在Order by 子句之后，对排序的结果进行限制，使用固定的行数来限制分区中
的数据行数量。
OVER()：指定分析函数工作的数据窗口大小，这个数据窗口大小可能会随着行的变而变
化。
CURRENT ROW：当前行
n PRECEDING：往前n行数据
n FOLLOWING：往后n行数据
UNBOUNDED：起点，UNBOUNDED PRECEDING 表示从前面的起点， UNBOUNDED
FOLLOWING表示到后面的终点
LAG(col,n,default_val)：往前第n行数据，col是列名，n是往上的行数，当第n行为null的时候取
default_val
LEAD(col,n, default_val)：往后第n行数据，col是列名，n是往下的行数，当第n行为null的时候取
default_val
NTILE(n)：把有序分区中的行分发到指定数据的组中，各个组有编号，编号从1开始，对于每一
行，NTILE返回此行所属的组的编号。
cume_dist（），计算某个窗口或分区中某个值的累积分布。假定升序排序，则使用以下公式确定
累积分布：
小于等于当前值x的行数 / 窗口或partition分区内的总行数。其中，x 等于 order by 子句中指
定的列的当前行中的值。
3.3.2. 排序开窗函数
RANK() 排序相同时会重复，总数不会变
DENSE_RANK() 排序相同时会重复，总数会减少
ROW_NUMBER() 会根据顺序计算
percent_rank() 计算给定行的百分比排名。可以用来计算超过了百分之多少的人
(当前行的rank值-1)/(分组内的总行数-1)
3.4 自定义函数
Hive自带了一些函数，比如：max/min等，但是数量有限，自己可以通过自定义的UDF来方便的扩
展
当Hive提供的内置函数无法满足你的业务处理需要的时候，此时可以考虑使用用户自定义函数
UDF(User-Defined-Function) 单行函数，一进一出
size/sqrt
UDAF(User- Defined Aggregation Funcation) 聚集函数，多进一出。
count/max/min/sum/avg
UDTF(User-Defined Table-Generating Functions) 一进多出
lateral view explode()
四、经典案例
4.1 wordcount
4.1.1 指标
计算每个单词出现的次数
4.1.2 样例数据
“I suppose so,” said Mrs. Dursley stiffly.
“What's his name again? Howard, isn't it?”
“Harry. Nasty, common name, if you ask me.”
“Oh, yes,” said Mr. Dursley, his heart sinking horribly. “Yes, I quite agree.”
4.1.3 处理方案
-- 表结构
create table t_wordcount(
line string
)
row format delimited lines terminated by '\n';
-- 加载数据
load data inpath '/yjx/harry.txt' into table t_wordcount;
-- 处理方案
select regexp_replace(line,"\[\^0-9a-zA-Z\`\'\\-\\s\]","") from t_wordcount;
select split(regexp_replace(line,"\[\^0-9a-zA-Z\`\'\\-\\s\]",""),"\\s+") from
t_wordcount;
select explode(split(regexp_replace(line,"\[\^0-9a-zA-Z\`\'\\-
\\s\]",""),"\\s+")) word from t_wordcount;
select word,count(word) from t_wordcount, lateral view
explode(split(regexp_replace(line,"\[\^0-9a-zA-Z\`\'\\-\\s\]",""),"\\s+"))
wordtable as word group by word;
4.2 天气系统
4.2.1 指标
每个市每天的最高温度和最低温度和平均温度
每天每个省最热的城市是那一个？
查询出每个省份每个月最高的三个温度和日期 ，最低的三个温度和日期？
综合每月数据查询出那个城市的 上报日期与创建日期 延迟最高？
查询出 7月份全国晴天最多的省份
4.2.2 样例数据
1199,广东,广州市,440100,多云,25,西南,≤3,97,3/6/2020 01:52:18,3/6/2020 02:00:01
1207,广东,南沙区,440115,多云,25,北,≤3,97,3/6/2020 02:52:19,3/6/2020 03:00:01
1212,北京,海淀区,110108,晴,20,北,≤3,60,3/6/2020 02:56:18,3/6/2020 03:00:01
1225,广东,天河区,440106,阴,25,东,≤3,97,3/6/2020 02:52:19,3/6/2020 03:00:01
1229,广东,广州市,440100,阴,25,东,≤3,97,3/6/2020 02:52:18,3/6/2020 03:00:01
1237,广东,南沙区,440115,多云,25,东,≤3,99,3/6/2020 03:52:18,3/6/2020 04:00:01
1247,广东,南海区,440605,阴,25,东南,≤3,92,3/6/2020 03:52:11,3/6/2020 04:00:01
1257,广东,越秀区,440104,阴,25,北,≤3,97,3/6/2020 03:52:19,3/6/2020 04:00:01
1260,北京,密云区,110118,晴,17,东,≤3,83,3/6/2020 03:56:15,3/6/2020 04:00:01
1273,上海,闵行区,310112,雨,25,南,≤3,94,3/6/2020 04:53:46,3/6/2020 05:00:01
1274,上海,青浦区,310118,雨,25,南,≤3,94,3/6/2020 04:53:46,3/6/2020 05:00:01
1281,北京,顺义区,110113,晴,18,东北,≤3,79,3/6/2020 04:56:16,3/6/2020 05:00:01
1293,北京,东城区,110101,晴,19,东,≤3,76,3/6/2020 05:56:20,3/6/2020 06:00:15
4.2.3 处理方案
--创建表
create table t_weather(
id int,
province string,
city string,
adcode int,
weather string,
temperature int,
winddirection string,
windpower string,
humidity int,
reporttime string,
createtime string
)
row format delimited fields terminated by ','
lines terminated by '\n';
-- 载入数据
load data inpath '/yjx/weather.txt' into table t_weather;
--解决方案 每个市每天的最高温度和最低温度和平均温度
select * from t_weather limit 0,10;
select UNIX_TIMESTAMP(reporttime,'dd/MM/yyyy HH:mm:ss') from t_weather limit
0,10;
select from_unixtime(UNIX_TIMESTAMP(reporttime,'dd/MM/yyyy
HH:mm:ss'),'yyyyMMdd') from t_weather limit 0,10;
select adcode , from_unixtime(UNIX_TIMESTAMP(reporttime,'dd/MM/yyyy
HH:mm:ss'),'yyyyMMdd'),max(temperature),min(temperature),avg(temperature) from
t_weather group by adcode , from_unixtime(UNIX_TIMESTAMP(reporttime,'dd/MM/yyyy
HH:mm:ss'),'yyyyMMdd') ;
--解决方案 每天每个省最热的城市是那一个？
select province,city,from_unixtime(UNIX_TIMESTAMP(reporttime,'dd/MM/yyyy
HH:mm:ss'),'yyyyMMdd') ymd,temperature,max(temperature) over(partition by
province,from_unixtime(UNIX_TIMESTAMP(reporttime,'dd/MM/yyyy
HH:mm:ss'),'yyyyMMdd') order by temperature ) mt from t_weather ;
select province,city,ymd,temperature from (select
province,city,from_unixtime(UNIX_TIMESTAMP(reporttime,'dd/MM/yyyy
HH:mm:ss'),'yyyyMMdd') ymd,temperature,row_number() over(partition by
province,from_unixtime(UNIX_TIMESTAMP(reporttime,'dd/MM/yyyy
HH:mm:ss'),'yyyyMMdd') order by temperature desc ) rn from t_weather ) hottable
where hottable.rn <= 1;
六、数据倾斜
6.1 定义
数据倾斜，即单个节点任务所处理的数据量远大于同类型任务所处理的数据量，导致该节点成为整
个作业的瓶颈，这是分布式系统不可能避免的问题。
从本质来说，导致数据倾斜有两种原因：
一是任务读取大文件，最常见的就是读取压缩的不可分割的大文件。
二是任务需要处理大量相同键的数据。
set mapred.reduce.tasks=3;
set mapred.reduce.tasks;
0-hive.fetch.task.conversion=more;将hive拉取的模式设置为more模式
1-hive.exec.mode.local.auto 决定 Hive 是否应该自动地根据输入文件大小，在本地运行（在
GateWay运行） ;
2-hive.auto.convert.join ：是否根据输入小表的大小，自动将 Reduce 端的 Common Join 转化为
Map Join，从而加快大表关联小表的 Join 速度。 默认：false。
3-mapred.reduce.tasks ：所提交 Job 的 reduer 的个数，使用 Hadoop Client 的配置。 默认
是-1，表示Job执行的个数交由Hive来分配；
mapred.map.tasks:设置提交Job的map端个数；
4-hive.map.aggr=true 开启map端聚合；
hive.groupby.skewindata=true ：决定 group by 操作是否支持倾斜的数据。
原理是，在Group by中，对一些比较小的分区进行合并，默认是false；
5-hive.merge.mapredfiles ：是否开启合并 Map/Reduce 小文件，对于 Hadoop 0.20 以前的版
本，起一首新的 Map/Reduce Job，对于 0.20 以后的版本，则是起使用 CombineInputFormat 的
MapOnly Job。 默认是：false；
6-hive.mapred.mode ：Map/Redure 模式，如果设置为 strict，将不允许笛卡尔积。 默认
是：'nonstrict'；
7-hive.exec.parallel ：是否开启 map/reduce job的并发提交。
默认Map/Reduce job是顺序执行的，默认并发数量是8，可以配置。默认是：false；
8-hive.exec.dynamic.partition =true：是否打开动态分区。 需要打开，默认：false；
set hive.exec.dynamic.partition.mode=nonstirct数据含有大量无意义的数据，例如空值(NULL)、空字符串等
含有倾斜数据在进行聚合计算时无法聚合中间结果，大量数据都需要经过 Shuffle阶段的
处理，引起数据倾斜。
数据在计算时做多维数据集合，导致维度膨胀引起的数据倾斜。
两表进行Join，都含有大量相同的倾斜数据键。
6.2. 原因
当集群的数据量增长到一定规模，有些数据需要归档或者转储，这时候往往会对数据进行压缩;
当对文件使用GZIP压缩等不支持文件分割操作的压缩方式，在日后有作业涉及读取压缩后的文件
时，该压缩文件只会被一个任务所读取。
如果该压缩文件很大，则处理该文件的Map需要花费的时间会远多于读取普通文件的Map时间，该
Map任务会成为作业运行的瓶颈。
七、Hive企业级优化
Hive优化的核心思想：把Hive SQL 当做Mapreduce程序去优化。
7.1. Fetch
我们之前是不是说过select * from 表；这样的sql语句是不会产生MR任务的，这涉及到一个
fetch（抓取）的概念。
hive-default.xml.template hive.fetch.task.conversion-->
默认more 如果 none那么每次执行select * from 都是执行MapReduce
7.2. 本地模式
hive.exec.mode.local.auto=true
7.3. 并行执行
set hive.exec.parallel=true; //打开任务并行执行
set hive.exec.parallel.thread.number=16; //同一个sql允许最大并行度
7.4. 严格模式
开启严格模式需要修改hive.mapred.mode值为strict，开启严格模式可以禁止3种类型的查询。
防止用户执行低效率的SQL查询
对于分区表，除非where语句中含有分区字段过滤条件来限制范围，否则不允许执行
对于使用了order by语句的查询，要求必须使用limit语句
限制笛卡尔积的查询。
7.5. JVM重用
JVM重用可以使得JVM实例在同一个job中重新使用N次。
N的值可以在Hadoop的mapred-site.xml文件中进行配置。
通常在10-20之间，具体多少需要根据具体业务场景测试得出。
很难避免小文件的场景或task特别多的场景，这类场景大多数执行时间都很短
7.6. 表的优化（小表与大表）
Hive默认第一个(左面的)表是小表，然后将其存放到内存中，然后去与第二张表进行比较
现在优化后小表前后无所谓
7.7. 表的优化（大表与大表）
针对于空值，可以将空值随机设置一个不影响结果的值
将来reduce的时候可以分区到不同的reduce，减少压力
7.8. mapside聚合
默认情况下，Map阶段同一Key数据分发给一个reduce，当一个key数据过大时就倾斜了。
并不是所有的聚合操作都需要在Reduce端完成，很多聚合操作都可以先在Map端进行部分聚合，
最后在Reduce端得出最终结果。
开启Map端聚合参数设置
是否在Map端进行聚合，默认为True
hive.map.aggr = true
在Map端进行聚合操作的条目数目
hive.groupby.mapaggr.checkinterval = 100000
有数据倾斜的时候进行负载均衡（默认是false）
hive.groupby.skewindata = true
当选项设定为 true，生成的查询计划会有两个MR Job。
分两次进行mapredue,第一次随机分获取中间结果,第二次正常分，获取最终结果
7.9. Count(Distinct)
防止所有的数据都分到一个Reduce上面
首先使用Group By对数据进行分组，然后在统计
7.10. 防止笛卡尔积
行列过滤(列裁剪)
当表关联的时候，优先使用子查询对表的数据进行过滤,这样前面表关联数据就是少的，减少关联的次数
工作上经常用到的hivesql小技巧
多表关联时要多用union all ,多表union all会优化成一个job。
修改表结构要用REPLACE COLUMNS。使用add colmns数据为空，这时因为元数据库没有更
新
注意Hive中NULL和’ ‘的区别，不管哪一种做相加的时候都会变成null
去列参数set hive.support.quoted.identifiers=none;
(membership_level|extra_info)?+.+
查询切记加limit
扫一扫
1902
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
