Hive学习笔记整理概括

Hive

以下是笔者对Hive的一些认知以及笔记

什么是Hive

1.Hive是基于Hadoop的一个数据仓库工具;
2.Hive提供Hql(Hive sql)查询功能;
3.数据是存储在HDFS上,Hive本身不存储数据,构建表的逻辑存在知道数据库上(mysql);
4.Hive的本质是将SQL语句转换为MapReduce任务执行;
5.离线大数据计算。

HQL与SQL

HQLSQL
数据存储HDFS、HbaseLocalFS(本地FileSystem)
数据格式用户自定义系统决定
数据更新不支持(多用于查)支持(增删改查)
索引有(0.8版之后增加)
执行MapReduceExecutor
执行延迟
可扩展性高(UDF、UDAF、UDTF)
数据规模大(数据大于TB)

Hive架构图在这里插入图片描述

1.首先在client或者一些web的ui或其他界面写入hql语句;
2.hql会进入到Driver里,Drive会把hql放入complier检查语法是否合法,如select则会报错;
3.获取元数据信息,判断字段、表是否正确;
4.发送元数据;
5.检查所有语法正确,由优化器进行优化后提交给Driver执行;
6.启动MapReduce执行任务。

关于Hive表的执行顺序

在hive的执行语句当中的执行顺序

# 如以下hql
select ... from ... where ... group by ... having ... order by ...; 

执行顺序
from … where … select … group by … having … group by …
实际上hive的执行顺序也就是MapReduce的执行顺序:

  • Map阶段
    • (1).执行from加载,进行表的查找以及加载
    • (2).执行where过滤,进行条件过滤与筛选
    • (3).执行select查询:进行输出项的筛选
    • (4).map端文件合并:reduce阶段:Map端本地溢出写文件的合并操作,每个map最终形成一个临时文件(相当于combiner过程)
  • Reduce阶段
    • (1).group by:对map端发送过来的数据进行分组并进行计算
    • (2).having:最后过滤列用于输出结果
    • (3).order by:排序后进行结果输出到HDFS文件

关于Hive的建表

直接建表

create [external] table [if not exists] table_name (column1 type,column2 type,....)
[row format delimited fields terminated by '\' ]
[stored as textfile]
[tblproperties("skis.header.line.count"="1")]; # 跳过第一行,例如有些数据第一行是字段名称,通过这段语句来跳过第一行

查询建表

# 常用于建临时表,抽取分布数据做验证一些hql执行结果是否正确,然后再用整个大表的数据集。
create table [if not exists] table_name as select * from table1;

建表

create table if not exists table_name like table1;

关于HIVE导入数据

本地数据导入表

load data local inpath '/user/data/a.txt' into table student;

关于HIVE的一些常用解析函数

over(partition by column1 order by column2 asc/desc)
1.partition by column1按哪列进行分组,如果不指定,默认全局排序。如果指定一列,则按照指定列进行分组,然后进行排序;
2.order by按哪一列进行排序,这个不指定就会报错;
3.asc/desc按升序或者降序进行排序,默认升序;
4.输出结果为不改变原有表的行数,在最右把结果拼在后面(N行进N行出)。
row_number()
此函数为排序,即使排序的列里面有相同的值,它的排名也会不同
row_number()&over()这两个函数通常一起出现,多用于分组排名,排名不重复,适用于生成主键(为了join)。

select *,row_number() over(partition by column1 order by column2 desc) as rk from table;

排序rank()
排序的列里面有相同值,排名也会相同

select *,rank() over(partition by column1 order by column2 desc) as rk from table;

rank()&over()一起出现也是用于分组排名,但是有并列名次,且名次不连续[50,50,70,70,100]–>[1,1,3,3,5]
dense_rank()&over()一起出现也是用于分组排名,但是有并列名次,且名次连续[50,50,70,70,100]–>[1,1,2,2,3]

对某一列字段值进行合并操作:collect_set()去重&collect_list()不去重

select column1,collect_set(column2) from table group by column1;
select column1,collect_list(column2) from table group by column1;
数据:
stu_id         collect_list
A                88
A                79
A                93
A                96
B                80
B                76
B                72
B                76

# 如统计每个学生历史的成绩
select stu_id,collect_list(score) from student group by stu_id;
# 输出结果为:
A ["88","79","93","96"]
B ["80","76","72","76"]

**条件函数case when(条件) then() else() end as column1 **

# 成绩大于等于60为及格,大于等于80为优秀,否则不及格
select *,case 
when(score>=60) then "及格",
when(score>=80) then "优秀" 
else "不及格" end as score_evaluate from student;

关于一些HQL函数

对字段值按某个字符进行分割:split

select split(column,' ') from table; # column为字段名称,table为所查询的表名
select split("I love Hive"," ");
# 结果输出为一个列表:["I","love","Hive"]

# 对以下字段值进行分割为两段字段值(a       89)
数据:
column
a:89
b:65
c:78
d:90
select split(column,":")[0] as id,split(column,":")[1] as score from table;
# 结果:
id        score
a         89
b         65
c         78
d         90

UDTF(一行进多行出):行转列操作:explode

select explode(split("I love Hive"," "));
# 结果输出:
I
love
Hive

UDTF(一行进多行出):虚表:lateral view
Lateral View 常用于和UDTF函数【explode,split】结合来使用。
首先通过UDTF函数将数据拆分成多行,再将多行结果组合成一个支持别名的虚拟表。
主要解决在select使用UDTF做查询的过程中查询只能包含单个UDTF,不能包含其它字段以及多个UDTF的情况。

# 如以下情况
数据
game_id         type
A               action,roleplay,adventure
B				simulation,roleplay,leisure	 
需求:
A               action
A               roleplay
A               adventure
B               simulation
B               roleplay
B               leisure
# HQL
select game_id,explode(split(type,",")) from game;
# 执行报错
UDTF's are not supported outside the SELECT clause, nor nested in expressions

语法:LATERAL VIEW udtf(expression) tableAlias AS columnAlias (’,’ columnAlias)

select game_id,types from game lateral view explode(split(type,',')) g as types ;

对字段值进行正则匹配:regexp_replace

#匹配开头和结尾有字符的值进行空值替换
select regexp_replace(column,"re_rule","string") from table; # column为字段名称,re_rule为匹配规则,string为替换所匹配的字符,table为所查询的表名
select regexp_replace("'abcdef.*","^\\W+|\\W+$", ""); # \为转义
# 输出结果为:abcdef

大小写变化:lower

select lower("AbcDef");
# 结果输出为:abcdef

时间函数:unix_timestamp、from_unixtime、date_sub、date_add
日期转时间戳:unix_timestamp

select unix_timestamp("2020-09-20 15:49:20"); # 输入参数类型为string
# 结果输出为:

时间戳转日期:from_unixtime

select from_unixtime();
# 结果输出为:
select from_unixtime(cast(column as int),'yyyy-MM-dd HH-mm-ss') from table; # column为时间戳字段名称,table为表名

日期运算:date_sub、date_add

# 对日期做减
select date_sub("2020-09-20",1);
# 输出结果为:2020-09-19
select date_sub("2020-09-20",10);
# 输出结果为:2020-09-10

# 对日期做加
select date_add("2020-09-20",1);
# 输出结果为:2020-09-21
select date_add("2020-09-20",10);
# 输出结果为:2020-09-30

提取json对象信息get_json_object()

数据集
id                  info
1                   {"store":{"Phone":[{"type":"apple","color":"write"},{"type":"xiaomi","color":"write"}],"Television":[{"type":"xiaomi","color":"black"},{"type":"haier","color","black"}]}}
2                   {"store":{"Phone":[{"type":"oneplus","color":"write"},{"type":"apple","color":"write"}],"Television":[{"type":"TCL","color":"black"},{"type":"haier","color","black"}]}}

# 获取store的信息
select get_json_object(info,'$.store') from table;
# 输出结果:
{"Phone":[{"type":"apple","color":"write"},{"type":"apple","color":"write"}],"Television":[{"type":"xiaomi","color":"black"},{"type":"haier","color","black"}]}
{"Phone":[{"type":"oneplus","color":"write"},{"type":"apple","color":"write"}],"Television":[{"type":"TCL","color":"black"},{"type":"haier","color","black"}]}}

# 获取store里phone的type信息
select get_json_object(info,'$.store.Phone.type') from table;
select get_json_object(info,'$.store.Phone[0]') from table;
# 输出结果:
[apple,xiaomi]
[oneplus,apple]
©️2020 CSDN 皮肤主题: 1024 设计师:上身试试 返回首页