Hive基础知识(个人总结)

hellosrc2023

已于 2022-02-25 14:17:12 修改

阅读量4.7k

点赞数 2

分类专栏： Hive 大数据文章标签： 1024程序员节 hive 大数据

于 2021-10-24 19:10:56 首次发布

本文链接：https://blog.csdn.net/weixin_46141936/article/details/120938646

版权

大数据同时被 2 个专栏收录

39 篇文章 11 订阅

订阅专栏

Hive

1 篇文章 0 订阅

订阅专栏

    声明: 1. 本文为我的个人复习总结, 并非那种从零基础开始普及知识 内容详细全面, 言辞官方的文章
              2. 由于是个人总结, 所以用最精简的话语来写文章
              3. 若有错误不当之处, 请指出

一. 基础

Hive的特点:

Hive 的执行延迟比较高, 不支持直接修改, 只能进行insert owewrite覆盖

迭代式算法无法表达

Hive架构:

在这里插入图片描述

Hive和数据库比较

Hive并不是数据库, 自身没有存储数据的能力; 本质是 HDFS和MySQL里存的元数据进行表结构的映射

Hive和数据库除了拥有类似的查询语言, 再无类似之处

数据是否允许修改, 是否有索引, 数据规模方面都有差异

Hive基础命令:

hive -e “sql语句”

hive -f “sql文件路径”

对于HDFS的操作, 前缀dfs即可, 如:dfs -ls /

日志:

~/.hivehistory 用户执行的hive命令操作记录

/tmp/用户名/hive.log 运行日志

修改 hive 的 log 存放日志到/opt/module/hive/logs:

修改conf/hive-log4j2.properties.template名称
hive.log.dir=/opt/module/hive/logs

参数设置:

参数的配置三种方式:

配置文件方式 hive-default.xml和hive-site.xml
命令行参数方式启动 Hive 时，hive -hiveconf mapred.reduce.tasks=10
终端里输入命令

set 查看所有配置

set xxx 查看xxx参数的值

set xxx 1 设置xxx参数的值

优先级: 配置文件<命令行参数<终端里输入命令

注意某些系统级的参数, 例如 log4j 的设定必须用前两种方式设定, 因为那些参数的读取在会话建立以前已经完成了

坑:

Hive中 order by后跟的聚合字段, 必须得在select中出现
Hive中子查询表必须起别名

hive中 join查询中的on只支持等值连接, 不支持非等值连接

-- 不支持的写法
SELECT *  
FROM table1  
LEFT JOIN table2  
ON table1.x LIKE CONCAT('pre_' , table2.y )

-- 支持的写法
SELECT *  
FROM table1  
LEFT JOIN table2  
ON TRUE
WHERE table1.x LIKE CONCAT('pre_' , table2.y )

substr(string A, int start, int len) 和 substring(string A, int start, int len) 用法一样
功能：返回字符串A从下标start位置开始，长度为len的字符串，下标从1开始

二. Hive数据类型

基本数据类型:

Hive 数据类型	Java 数据类型
TINYINT	byte
SMALINT	short
INT	int
BIGINT	long
BOOLEAN	boolean
FLOAT	float
DOUBLE	double
STRING	string
TIMESTAMP

String 类型相当于数据库的 varchar 类型, 该类型是一个可变的字符串; 不过它不能声明其中最多能存储多少个字符, 理论上它可以存储 2GB 的字符数

集合数据类型:

STRUCT struct<street:string, city:string> 字段.city
MAP map<string, int> 字段[key]
Array array<string> 字段[index]

例, 建表语句

create table test(  
    name string,
    address  struct<street:string, city:string>,
    children  map<string, int>,
    friends array<string>
)

-- 建表语句后的参数设置(设置导入源文件的格式)
row format delimited fields terminated by ','     -- 导入文件数据格式的 列分隔符
lines terminated by '\n';	-- 导入文件数据格式的 行分隔符
collection items terminated by '_'   -- MAP,STRUCT,ARRAY内部的数据分割符号
map keys terminated by ':'    -- MAP中的key与value的分隔符

插入数据示例:

zhangsan, xingfu_beijing, zhanga:18_zhangb:19, lisi_wangwu

查询语句:

select address.city, children['zhangsan1'], friends[1]

-- 得到:      beijing   18   lisi

类型转化:

隐式类型转换

TINYINT -> INT -> BIGINT

TINYINT、SMALLINT、INT、BIGINT、FLOAT 、STRING -> DOUBLE

TINYINT、SMALLINT、INT -> FLOAT

BOOLEAN 类型不可以转换为其它类型
强制转换

CAST(‘1’ AS INT) 将把字符串’1’ 转换成整数 1, 如果转换失败则返回 NULL

三. DDL 数据定义

数据库默认存放在HDFS上的 /user/hive/warehouse/*.db下

创建数据库语句模板:

create database if not exists demo01 comment '测试' location '/my/demo01' with dbproperties (createtime=20170830, aaa=bbb)

修改数据库:

alter database demo01 set dbproperties('createtime'='20170830');

创建表语句模板:

create external table if not exists t1(
name string
) comment 't1临时表'
partitioned by (dt date )  
clustered by (name)
sorted by name desc
into 3 buckets
row format fields terminated by '\t'
serde '序列化反序列化名称'
stored as textfile 		-- SerDe是Serialize/Deserilize的简称
location '/my/demo01/t1'
tblproperties (key=value, key=value)
as select username from user;   -- 复制现有的表结构, 并且复制数据
-- like select username from user; 复制现有的表结构, 但是不复制数据

字段解释说明:

EXTERNAL 外部表, 只有元数据可以被删,实际数据保留(实际工作中常用)

PARTITIONED BY 分区字段

CLUSTERED BY 分桶字段

SORTED BY 不常用, 对桶中的列排序

ROW FORMAT DELIMITED

FIELDS TERMINATED BY ‘\t’

COLLECTION ITEMS TERMINATED BY ‘\t’

MAP KEYS TERMINATED BY ‘\t’]

LINES TERMINATED BY ‘\t’

SerDe 是Serialize/Deserilize的简称,指定自定义的 SerDe

STORED AS

SEQUENCEFILE（二进制序列文件）
TEXTFILE（文本）
RCFILE（列式存储格式文件）

as sql查询语句根据查询结果创建表,复制数据

like sql查询语句复制现有的表结构, 但是不复制数据

修改表:

-- 注意：('EXTERNAL'='TRUE')和('EXTERNAL'='FALSE')为固定写法, 区分大小写
alter table t1 set tblproperties('EXTERNAL'='TRUE')

desc formatted t1; 可以查看表类型是否为EXTERNAL

重命名表名字:

ALTER TABLE t1 RENAME TO t2

更新列:

alter table t2 change column name myName string

增加列:

alter table t2 add columns(password string)

增加和替换列:

alter table t2 replace columns(username string, age int);

四. DML数据操作

数据导入方式:

 -- load data方式  只是数据存储格式校验+剪切
 load data [local] inpath '数据的 path' overwrite/into table t2 [partition(dt='2020-06-14')]

 -- insert select方式
 insert t3 select * from t2

 -- create table as select方式
 create table if not exists t4 as select name from t3

 -- import方式, 注意它读取的文件是export命令导出的文件, export和import主要用于两个Hadoop平台集群之间Hive表迁移
 export table default.t4 to '/root/t4.dat'
 import table t5 from '/root/t4.dat'

如果直接把数据上传到HDFS, mysql里没有该数据的元数据信息, 是感知不到这些文件的, 需要用msck repair table student修复

数据导出方式:

 -- 将查询的结果格式化导出
 insert overwrite [local] directory '/root'
 ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
 select * from t2

 -- Shell命令将查询的结果复写输出到本地文件
 hive -e 'select * from t2' >> /root/result.txt

 -- Hive里输入Hadoop命令get到本地
 dfs -get '/my/demo/t2.txt' '/my/demo/t2.txt.bak'

 -- export命令导出
 export table default.t4 to '/root/t4.dat'

五. DQL查询

查询语句语法:

select (distinct) *
from student
where score>80
group by class
order by age
distribute by col_list  
sort by col_list
limit 10

with … as 语句:

with t1 as
select id,name
from user

运算符:

基于sql的正则表达式匹配: name like ‘张%’ name not like ‘张%’
基于java的正则表达式匹配: name rlike ‘张%’ name regexp ‘张%’ java的正则语法更强大

支持各种join, 包括 full join

排序:

order by: 全局排序, 只有一个 Reducer 所以慎用order by, 除非有过滤条件或limit之类的减少数据量
sort by: 每个 Reduce 内部排序, 可以有多个Reducer

分区 distribute by:

分区是把数据分到不同的reducer上, hash%reducer数量

distribute by 常和 sort by混用; 当二者所用字段相同时, 可以使用 cluster by 简写

分区表:

分区可以避免全表扫描, 只查询指定分区内容即可
分区就是分目录, 让数据分开存放更有条理

创建分区表: 加上 partitioned by(dt string)分区字段不能是表中已有的字段, 它像是一个伪列, 可以指定多个字段形成多级分区

静态分区:

往分区表插入数据:

insert overwrite user partition(dt='2020-06-14') select id, name from....

动态分区:

insert overwrite user partition(dt) select id, name, createTime from....
-- 最后一个字段即为动态分区字段dt

默认是strict模式: 要求至少一个分区为静态分区

nonstrict 模式: 所有的分区字段都可以使用动态分区

分桶表:

分区针对的是数据的存储路径, 而分桶针对的是数据文件, 是更细粒度的数据范围划分, 一般不用它

分桶表操作需要注意的事项:

reduce 的个数设置为-1, 让 Job 自行决定需要用多少个 reduce; 或者设置reduce个数>=分桶表的桶数
不要使用本地模式

可用于抽样查询: select * from student tablesample(bucket 1 out of 4 on id); 抽取一个桶进行抽样统计

六. 函数:

UDF: 进一出一

UDAF: 聚合函数

UDTF: 炸裂函数

nvl(username, “xxx”)

substring(str, index, length)

case sex when ‘女’ then 1 else 0 end

多个函数嵌套组合: sum(if(xxx))

行转列(合并列):

concat(str1, str2, …)

concat_ws(分隔符, str1, str2, …)

collect_set(col): 对某字段的值进行去重汇总, 返回 Array

列转行(炸裂行):

explode(array/map)

lateral view: 如movie和type两个字段, category是个Array类型,炸裂后行数>movie; 所以lateral view把 movie行数扩充去适配炸裂后的type行数

示例:

select
movie, category_name
from
movie_info 
lateral view explode(split(category,",")) movie_info_tmp as category_name;

开窗函数 over( ):

类似于group by,

但group by的粒度是对整组的所有数据进行操作, 而开窗可以选取当前窗口的部分数据进行操作;

group by是一组数据里用的是同一个组, 而over是每行数据都是一个窗口

一行匹配多行时(对每一行数据进行开窗), 有点类似lateral view使之匹配

总的来说, 有聚合类开窗(sum, count), 有排序类开窗(rank)

聚合类开窗:

sum(cost) over(partition by name order by date rows between 1 preceding and current row) as total

先order by 才能rows

rows:

current row：当前行

n preceding：相对于当前行之前的n行数据

n following：相对于当前行之后的n行数据

unbounded preceding: 表示从最前面的起点开始

unbounded following: 表示到最后面的终点

所有行相加
```
sum(cost) over( ) as sample1
```

按name分组, 组内数据相加

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

按name分组, 当前行和前面一行做聚合

sum(cost) over(partition by name order by orderdate rows between 1 preceding and current row) as sample5

按name分组, 当前行和前边一行及后面一行

sum(cost) over(partition by name order by orderdate rows between 1 preceding and 1 following ) as sample6

按name分组, 当前行及后面所有行

sum(cost) over(partition by name order by orderdate rows between current row and unbounded following ) as sample7

lag(col,n,default_val)：往前的第n行数据, 当向上n行为NULL时, 取默认值；

n如果不指定, 则为1

default_val如果不指定, 则为NULL

lead(col,n, default_val)：往后的第n行数据, 当向下n行为NULL时, 取默认值；

n如果不指定, 则为1

default_val如果不指定, 则为NULL

ntile(n)：给数据大致分组, 比如90个数据进行大致尽量均分成n个组

案例:

查询前20%数据:

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

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

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

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

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

查看顾客上次的购买时间

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

排序类开窗:

三种rank:

ROW_NUMBER( ) 根据行号顺序; 1,2,3,4,5
RANK( ) 并列时会重复, 总数可能不变; 像成绩排名: 1,2,2,4,5
DENSE_RANK( ) 并列时会重复, 总数会减少; 1,2,2,3,4

示例:

rank( ) over(partition by subject order by score desc) rp

七. 存储格式:

存储格式:

textfile(不做处理, 行式存储)
sequence file(二进制格式, 占内存比textfile略小些)
orc(自带略微压缩, 列式存储)
parquet(自带略微压缩, 列式存储), 像.docx, 没有被7zip压缩照样自带压缩

在不额外lzo或者snappy压缩时, 三者查询效率差不多(当然还是没自带压缩的textfile快), 但是存储效率ORC最高占存储空间少
lzo可以额外建立索引文件使之能切片, 而snappy不能切片, 但是可以人为控制文件输出的大小, 生成snappy文件时使每个snappy不超过128M之类的
不使用额外压缩, 就把额外描述的压缩参数设为None
MR最好采用orc存储格式
Spark自身对parquet优化, 最好用parquet存储格式
orc和parquet文件存储结构都差不多, 都是先分行组, 行组内再列式存储(这一列存完了, 再存下一列)
ads层 hive导出到mysql时, 用的是hdfs文件不走hive, 故采用原格式textfile

八: 优化:

谓词下推
只select 必需的字段, 不用select *
尽量使用分区字段, 避免全表扫描
MapJoin

set hive.auto.convert.join=true; --默认为 true
MR时, 使用ORC列式存储; Spark时使用Parquet列式存储
采用分区技术
输入端对小文件进行合并 CombineHiveInputFormat(默认)

输出端对小文件进行合并

九: 数据倾斜

由于热点数据, 导致有的Reduce分区数据量过大

解决:

处理热点数据(key)
- key为NULL时
  - 属于异常数据就提前过滤掉
  - 不属于异常数据就拼接随机值
- key不为NULL时
  
  拼接随机值
然后进行两阶段MR聚合:

第一次MR带着随机值聚合一部分, 即局部聚合;

第二次MR去掉随机值进行最终聚合即全局聚合

所谓的随机值, 并不是UUID完全随机, 因为那样第一个MR相当于没干任何聚合的活, 第二个MR拆掉后缀随机值后照样数据倾斜;
应该是某一个区间内的随机值(如随机值%100), 当1亿个同key的数据%100 [0,99]分区进行聚合, 第二个MR去掉后缀随机值后只需要聚合的是这100个同key的数据, 任务量就很小了
去重时选用group by, group by性能优于distinct

MapJoin

set hive.auto.convert.join=true;   -- 默认为true

开启Map端combiner预聚合
join时若关联字段数据类型不一致, 使用手动cast进行强转

比如A表string类型的uid 去 join B表bigint类型的uid:

若不手动把bigint强转为string, 在hive3.x里便会隐式地把string转为bigint, 可能会导致数据溢出从而返回NULL,

若这种溢出状况较多, 便会导致了热点数据NULL

hellosrc2023

关注

2
点赞
踩
30

收藏

觉得还不错? 一键收藏
0
评论
Hive基础知识(个人总结)

    声明: 1. 本文为我的个人复习总结, 并非那种从零基础开始普及知识 内容详细全面, 言辞官方的文章              2. 由于是个人总结, 所以用最精简的话语来写文章           
复制链接

扫一扫