Hive基础知识(个人总结)

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

一. 基础 Hive的特点:

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

迭代式算法无法表达

Hive架构:

Hive和数据库比较

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

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

Hive基础命令:

hive -e “sql语句”

hive -f “sql文件路径”

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

日志:

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

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

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

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

参数设置:

参数的配置三种方式:

1. 配置文件方式 hive-default.xml和hive-site.xml

2. 命令行参数方式 启动 Hive 时，hive -hiveconf mapred.reduce.tasks=10

3. 终端里输入命令

   ​ set 查看所有配置

   ​ set xxx 查看xxx参数的值

   ​ set xxx 1 设置xxx参数的值

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

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

二. Hive数据类型 基本数据类型:

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

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

集合数据类型:

1. STRUCT struct 字段.city

2. MAP map 字段[key]

3. Array array 字段[index]

例, 建表语句

create table test(  
    name string,
    address  struct,
    children  map,
    friends array
)

-- 建表语句后的参数设置(设置导入源文件的格式)
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

类型转化:

1. 隐式类型转换

   TINYINT -> INT -> BIGINT

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

   TINYINT、SMALLINT、INT -> FLOAT

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

2. 强制转换

   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; 复制现有的表结构, 但是不复制数据

字段解释说明:

1. EXTERNAL外部表, 只有元数据可以被删,实际数据保留

2. PARTITIonED BY 分区字段

3. CLUSTERED BY 分桶字段

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

5. ROW FORMAT DELIMITED

   • FIELDS TERMINATED BY ‘t’

   • COLLECTION ITEMS TERMINATED BY ‘t’

   • MAP KEYS TERMINATED BY ‘t’]

   • LINES TERMINATED BY ‘t’

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

7. STORED AS

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

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

9. 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数据操作 数据导入方式:

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

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

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

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

数据导出方式:

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

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

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

4.  -- 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

运算符:

• 基于sql的正则表达式匹配: name like ‘张%’ name not like ‘张%’

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

支持各种join, 包括 full join

排序:

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

分区 distribute by:

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

优点:

1. 分区可以避免全表扫描, 只查询指定分区内容即可

2. 分区就是分目录, 让数据分开存放更有条理

分区表排序, 和sort by混用; 当二者所用字段相同时, 可以使用 cluster by 简写

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

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

动态分区:

比如插入数据时分区不是指定’2020-06-14’, 而是指定$dt, dt是shell脚本的变量

需要设置为非严格模式(默认strict模式, 要求至少一个分区为 静态分区; nonstrict 模式下 所有的分区字段都可以使用动态分区)

分桶表:

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

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

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

可用于抽样查询: 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使之匹配 (对每一行数据进行开窗)

总的来说, 有聚合类开窗, 有排序类开窗(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: 表示到最后面的终点

1. 所有行相加

   sum(cost) over( ) as sample1
   

2. 按name分组, 组内数据相加

   sum(cost) over(partition by name) as sample2
   

3. 按name分组, 组内数据累加

   sum(cost) over(partition by name order by orderdate) as sample3
   

4. 按name分组, 由起点到 当前行的聚合

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

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

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

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

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

7. 按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时, 取默认值；如果不指定, 则为NULL

lead(col,n, default_val)： 往后的第n行数据

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

案例:

1. 查询前20%数据:

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

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

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

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

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

4. 查看顾客上次的购买时间

   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

注意:

分区表查询分区是distribute by

创建分区表时, 是partitioned by

partition by是over里的按哪个字段开窗, 不指定的话 就是把所有数据当成一个大窗口

七. 存储格式:

存储格式:

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

八: 优化:

1. 谓词下推

2. 只select 必需的字段, 不用select *

3. 尽量使用分区字段, 避免全表扫描

4. MapJoin

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

5. MR时, 使用ORC列式存储; Spark时使用Parquet列式存储

6. 采用分区技术

7. 输入端对小文件进行合并 CombineHiveInputFormat(默认)

8. 输出端对小文件进行合并

   相关参数:

   SET hive.merge.mapfiles = true; 	-- 默认true, 在map-only任务结束时合并小文件
   SET hive.merge.mapredfiles = true; 	 -- 默认false, 在map-reduce任务结束时合并小文件
   SET hive.merge.size.per.task = 268435456;	     -- 默认256M
   SET hive.merge.smallfiles.avgsize = 16777216; 	-- 当输出文件的平均大小小于16m该值时, 启动一个独立的map-reduce任务进行文件merge
   

9. 开启map端combiner(不影响业务逻辑时)

   set hive.map.aggr=true;

10. 压缩选择速度快的

11. 开启JVM重用

12. 合理设置Map数

13. 合理设置Reduce数

九: 数据倾斜

由于热点数据, 导致有的Reducer端数据量过大

解决:

1. 处理热点key

   开启数据倾斜时负载均衡, 执行命令 set hive.groupby.skewindata=true;

   拼接随机值, 进行两次MR。第一次MR带着随机值聚合一部分, 即局部聚合; 第二次MR去掉随机值进行最终聚合 即全局聚合

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

2. 控制NULL值分布

   • NULL key属于异常数据就提前过滤掉

   • NULL key不属于异常数据就给他赋随机值(或固定前缀再拼接随机值(能识别其为异常值即可))

     可以使用case when对空值赋上随机值, 比直接写is not null更好; 因为前者job数为1, 后者为2

3. 去重时选用group by, group by性能优于distinct

4. MapJoin

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