Hive入门

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