Hive从本质到实战
Hive
本质
将 HQL 转化成 MapReduce 程序
本人理解是使用HQL去写MapReduce
Hive 处理的数据存储在 HDFS
Hive 分析数据底层的实现是 MapReduce
执行程序运行在 Yarn 上
属性配置
配置文件
- hive-site.xml:用户自定义配置会覆盖默认配置
命令行参数
- 在命令行添加-hiveconf param=value 来设定参数
仅对本次有效
参数声明
- 可以在 HQL 中使用 SET 关键字设定参数,仅对本次有效
set mapred.reduce.tasks=100;
Hive的数据类型
基本数据类型
- TINYINT
- SMALLINT
- INT
- BIGINT
- DOUBLE
- FLOAT
- STRING
- BOOLEAN
- TIMESTAMP
- BIGANY
集合数据类型
-
STRUCT
-
struct<street:string, city:string>
- “address”: {
“street”: “hui long guan”,
“city”: “beijing”
}
- “address”: {
-
-
MAP
- map<string, int>
-
ARRAY
- array
基于上述数据结构,我们在 Hive 里创建对应的表,并导入数据
-
可以将数据写在txt文件中,MAP,STRUCT 和 ARRAY 里的元素间关系都可以用同一个字符表示,这里用“_”。
- songsong,bingbing_lili,xiao song:18_xiaoxiao song:19,hui long
guan_beijing
yangyang,caicai_susu,xiao yang:18_xiaoxiao yang:19,chao yang_beijing
- songsong,bingbing_lili,xiao song:18_xiaoxiao song:19,hui long
-
创建Hive表的时候在语句最后添加
row format delimited fields terminated by ‘,’
collection items terminated by ‘_’
map keys terminated by ‘:’
lines terminated by ‘n’;- row format delimited fields terminated by ‘,’ ----> 列分隔符
- collection items terminated by ‘_’ ---->MAP STRUCT 和 ARRAY 的分隔符(数据分割符号)
- map keys terminated by ‘:’ ---->MAP 中的 key 与 value 的分隔符
- lines terminated by ‘n’; ----> 行分隔符
-
导入文本数据到测试表
- load data local inpath ‘/opt/module/hive/datas/test.txt’ into table test;
-
访问三种集合列里的数据,以下分别是 ARRAY,MAP,STRUCT 的访问方式
- hive (default)> select friends[1],children[‘xiao song’],address.city from test
类型转换
-
隐式转换规则
- (1)任何整数类型都可以隐式地转换为一个范围更广的类型,如 TINYINT 可以转换成INT,INT 可以转换成 BIGINT。
- (2)所有整数类型、FLOAT 和 STRING 类型都可以隐式地转换成 DOUBLE。
- (3)TINYINT、SMALLINT、INT 都可以转换为 FLOAT。
- (4)BOOLEAN 类型不可以转换为任何其它的类型。
-
可以使用 CAST 操作显示进行数据类型转换
- CAST(‘1’ AS INT)将把字符串’1’ 转换成整数 1
DDL 数据定义
创建数据库
- CREATE DATABASE [IF NOT EXISTS] database_name
[COMMENT database_comment]
[LOCATION hdfs_path]
[WITH DBPROPERTIES (property_name=property_value, …)]; - 创建一个数据库,数据库在 HDFS 上的默认存储路径是/user/hive/warehouse/*.db。
查询数据库
修改数据库
- 使用 ALTER DATABASE 命令为某个数据库的 DBPROPERTIES 设置键-值对属性值
- hive (default)> alter database db_hive set dbproperties(‘createtime’=‘20170830’);
删除数据库
创建表
-
管理表(内部表)
- 默认建立的表都是管理表,删除的时候会将原始数据删除
-
外部表
- external,外部表删除之后,只会删除元数据,其原始数据在hdfs中不会被删除
-
两表的相互转换
-
管理表转换为外部表
- alter table student2 set tblproperties(‘EXTERNAL’=‘TRUE’);
-
外部表转换为管理表
- alter table student2 set tblproperties(‘EXTERNAL’=‘FALSE’);
-
修改表
DML 数据操作
向表中装载数据(Load)
- hive> load data [local] inpath ‘数据的 path’ [overwrite] into table student [partition (partcol1=val1,…)];
- (1)load data:表示加载数据
- (2)local:表示从本地加载数据到 hive 表;否则从 HDFS 加载数据到 hive 表
- (3)inpath:表示加载数据的路径
- (4)overwrite:表示覆盖表中已有数据,否则表示追加
- (5)into table:表示加载到哪张表
- (6)student:表示具体的表
- (7)partition:表示上传到指定分区
通过查询语句向表中插入数据(Insert)
-
hive (default)> insert overwrite table student_par select id, name from student where month=‘201709’;
-
insert into:以追加数据的方式插入到表或分区,原有数据不会删除
-
insert overwrite:会覆盖表中已存在的数据
-
注意:insert 不支持插入部分字段
-
多表(多分区)插入模式
- hive (default)> from student
insert overwrite table student partition(month=‘201707’)
select id, name where month=‘201709’
insert overwrite table student partition(month=‘201706’)
select id, name where month=‘201709’;
- hive (default)> from student
数据导出
-
Insert 导出
-
Hadoop 命令导出到本地
-
Hive Shell 命令导出
-
Export 导出到 HDFS 上
-
Sqoop 导出
-
清除表中数据(Truncate)
- Truncate 只能删除管理表,不能删除外部表中数据
查询
select
from
where
like
betweeninisnot
andnotor
having
group by
join on
order by
sort by
distribute by
cluster by
分区表和分桶表
分区表
-
一级分区表
- partitioned by (day string)
-
二级分区表
-
partitioned by (day string, hour string)
-
分区表与数据产生关联的三种方式
- 上传数据后修复
- 上传数据后添加分区
- 创建文件夹后load数据到分区
-
-
动态分区调整
-
自动会根据分区字段的值,将数据插入到相应的分区中
-
(1)开启动态分区功能(默认 true,开启)
- hive.exec.dynamic.partition=true
-
(2)设置为非严格模式(动态分区的模式,默认 strict,表示必须指定至少一个分区为静态分区,nonstrict 模式表示允许所有的分区字段都可以使用动态分区。)
- hive.exec.dynamic.partition.mode=nonstrict
-
(3)在所有执行 MR 的节点上,最大一共可以创建多少个动态分区。默认 1000
- hive.exec.max.dynamic.partitions=1000
-
(4)在每个执行 MR 的节点上,最大可以创建多少个动态分区。该参数需要根据实际的数据来设定。比如:源数据中包含了一年的数据,即 day 字段有 365 个值,那么该参数就需要设置成大于 365,如果使用默认值 100,则会报错。
- hive.exec.max.dynamic.partitions.pernode=100
-
(5)整个 MR Job 中,最大可以创建多少个 HDFS 文件。默认 100000
- hive.exec.max.created.files=100000
-
(6)当有空分区生成时,是否抛出异常。一般不需要设置。默认 false
- hive.error.on.empty.partition=false
-
举例:将 dept 表中的数据按照地区(loc 字段),插入到目标表 dept_partition 的相应分区中
- hive (default)> insert into table dept_partition_dy partition(loc) select deptno, dname, loc from dept;
-
-
分桶表
- 分区针对的是数据的存储路径;分桶针对的是数据文件。
- create table stu_buck(id int, name string)
clustered by(id)
into 4 buckets
row format delimited fields terminated by ‘t’;
抽样查询
函数
内置函数
-
nvl
-
窗口函数
-
over
- Current row: 当前行
- n preceding:往前n行数据
- n following:往后n行数据
- unbounded preceding:表示从前面的起点
- unbounded following:表示从后面的起点
-
lag(col,n,default_value)
-
lead(col,n,default_value)
-
ntile(n)
-
-
Rank
- RANK() 排序相同时会重复,总数不会变
- DENSE_RANK() 排序相同时会重复,总数会减少
- ROW_NUMBER() 会根据顺序计算
-
行转列
-
CONCAT(string A/col, string B/col…)
-
SELECT concat(‘abc’, ‘def’) FROM src LIMIT 1;
‘abcdef’
-
-
CONCAT_WS(separator, str1, str2,…)
-
SELECT concat_ws(’.’, ‘www’, array(‘facebook’, ‘com’)) FROM src LIMIT 1;
‘www.facebook.com’
-
-
注意: CONCAT_WS must be "string or array
-
hive (default)> SELECT
t1.c_b,
CONCAT_WS("|",collect_set(t1.name))
FROM (
SELECT
NAME ,
CONCAT_WS(’,’,constellation,blood_type) c_b
FROM person_info
)t1
GROUP BY t1.c_b
-
-
列转行
-
LATERAL VIEW
- LATERAL VIEW udtf(expression) tableAlias AS columnAlias
-
SPLIT(string str, string regex):
-
按照regex字符串分割str,会返回分割后的字符串数组
-
SELECT split(‘oneAtwoBthreeC’, ‘[ABC]’) FROM src LIMIT 1;
[“one”, “two”, “three”]
-
-
EXPLODE(col):
- 将hive一列中复杂的array或者map结构拆分成多行
-
自定义函数
-
UDF(User-Defined-Function)–> 一进一出
-
继承GenericUDF类
- initialize(ObjectInspector[] arguments)
- evaluate(DeferredObject[] arguments)
- getDisplayString(String[] children)
-
打成jar包上传到服务器/opt/module/hive/datas/myudf.jar
-
将jar包添加到hive的classpath
- hive (default)> add jar /opt/module/hive/datas/myudf.jar;
-
创建临时函数与开发好的java class关联
- hive (default)> create temporary function my_len as “com.gis.hive. MyStringLength”;
-
即可在hql中使用自定义的函数
-
-
UDAF(User-Defined Aggregation Function) --> 聚合函数,多进一出,类似:count/max/min
-
UDTF(User-Defined Table-Generating Functions)–> 炸裂函数,一进多出,如:explode()
-
继承GenericUDTF
-
initialize(StructObjectInspector argOIs)
- 初始化
-
process(Object[] args)
- 函数逻辑
-
close()
-
-
打成jar包上传到服务器/opt/module/hive/data/myudtf.jar
-
将jar包添加到hive的classpath下
- hive (default)> add jar /opt/module/hive/data/myudtf.jar;
-
创建临时函数与开发好的java class关联
- hive (default)> create temporary function myudtf as “com.atguigu.hive.MyUDTF”;
-
使用自定义的函数
-
压缩与存储
压缩
- map端压缩
- reduce端压缩
文件存储
调优
执行计划Explain
- explain select * from emp;
- 详细的:explain extended select * from emp;
Fetch抓取
- Fetch抓取是指,Hive中对某些情况的查询可以不必使用MapReduce计算
- 例如SELECT * FROM employees;,是简单的读取输出,不走MapReduce
- 在hive-default.xml.template文件中:
·hive.fetch.task.conversion默认是more
·老版本hive默认是minimal
该属性修改为more以后,在全局查找、字段查找、limit查找等都不走mapreduce。
本地模式
- Hive可以通过本地模式在单台机器上处理所有的任务。对于小数据集,执行时间可以明显被缩短
- 用户可以通过设置hive.exec.mode.local.auto= true,来让Hive在适当的时候自动启动这个优化
表的优化
-
Group By
-
是否在Map端进行聚合,默认为True
- hive(default)> set hive.map.aggr = true
-
在Map端进行聚合操作的条目数目
- hive(default)> set hive.groupby.mapaggr.checkinterval = 100000
-
有数据倾斜的时候进行负载均衡(默认是false)
- hive(default)> set hive.groupby.skewindata = true
-
-
合理设置Map和Reduce
- 小文件合并
-
jvm重用
实战
视频观看量Top10
-
select
videoId,
views
from gulivideo_orc
order byviews
desc
limit 10;- select videoId,sum(views) over(partition by videoId) from gulivideo_orc
统计视频类别热度Top10
-
因为一个视频可能属于一个或多个类别,所以还要进行列转行
- hive(default)> select
tmp01.category_col,
count(tmp01.videoId) num
from (
select
videoId,
category_col
from gulivideo_orc
lateral view
explode(category) t as category_col
) tmp01
group by tmp01.category_col
order by num desc
limit 10;
- hive(default)> select
-
列转行
-
LATERAL VIEW
- LATERAL VIEW udtf(expression) tableAlias AS columnAlias
-
SPLIT(string str, string regex):
- 按照regex字符串分割str,会返回分割后的字符串数组
-
EXPLODE(col):
- 将hive一列中复杂的array或者map结构拆分成多行
-
统计出视频观看数最高的20个视频的所属类别以及类别包含Top20视频的个数
- hive(default)> select
table02.categroy_name,
count(table02.videoId) num
from (
select
videoId,
categroy_name
from (
select
videoId,
views
,
category
from gulivideo_orc
order byviews
desc
limit 20
) table01
lateral view
explode(category) tmp as categroy_name
) table02
group by table02.categroy_nam;