Hive学习笔记

最新推荐文章于 2023-04-17 16:21:35 发布

can_chen

最新推荐文章于 2023-04-17 16:21:35 发布

阅读量455

点赞数 2

分类专栏：大数据学习笔记文章标签： hive 大数据 hadoop 行存储列存储

本文链接：https://blog.csdn.net/can_chen/article/details/120432783

版权

学习笔记同时被 2 个专栏收录

8 篇文章 0 订阅

订阅专栏

大数据

4 篇文章 0 订阅

订阅专栏

文章目录

1. Hive数据库相关命令

创建数据库，并指定在HDFS上的目录：create database ssb location '/user/hive/ssb'; （最后的ssb代表数据库名称）
删除不为空的数据库（default默认数据库无法删除）：drop database ssb cascade;
查看当前数据库：select current_database();

2. Hive表相关命令

创建表

（行分隔符可以忽略不写，默认就是’\n’，但是如果要写的话，需要写到字段分隔符后面）

# 创建表：指定行列分隔符
create table student(
id int,
stu_name string,
stu_birthday date,
online boolean)
row format delimited fields terminated by ','
lines terminated by '\n';

# 创建分区表：注意是partitioned，不是partition
create table teacher(
id int)
partitioned by(pt string)
row format delimited fields terminated by ',';

# 指定表的存储方式（默认是textfile方式）：表的存储方式需要写在字段分隔符以及行分隔符后面
create table test_orc(
id int,
name string)
row format delimited fields terminated by ','
lines terminated by '\n'
stored as orc;

查看表结构信息：desc student;
查看表的创建信息：show create table student;
给表添加一个字段：alter table student add columns(age int);
修改表的字段信息：alter table student replace columns(id int, name string);

3. Hive表导入数据相关命令

本地文件数据导入Hive表（追加数据）：load data local inpath '/home/hive_test/student.txt' into table student;
本地文件数据导入Hive表（覆盖数据）：load data local inpath '/home/hive_test/student.txt' overwrite into table student;
本地文件数据导入Hive分区表（需要指定分区字段的值）：load data local inpath '/home/hive_test/student.txt' into table student partition(pt='20200101');
HDFS文件导入Hive表：load data inpath '/data/student.txt' into table student;
（HDFS文件导入Hive表不需要加上local关键字，HDFS文件导入Hive表之后，会把HDFS上对应的文件删除掉）
通过上传文件到HDFS路径的方式导入Hive表（其实就是将本地文件上传到Hive表在HDFS上的路径）：hdfs dfs -put /data/student.txt /user/hive/ssb/student;
通过查询语句向表中插入数据（追加数据）：insert into teacher partition(pt='20210920') select id from student;
通过查询语句向表中插入数据（覆盖数据，注意需要加上“table”关键字）：insert overwrite table teacher partition(pt='20210920') select id from student;
创建表并从某个表加载数据：create table test as select id from teacher;

4. Hive分区相关命令

创建分区：alter table student add partition(pt='20200101');
删除分区：alter table student drop partition(pt='20200101');
查看某张表所有分区：show partitions student;
查看Hive表中某个分区的数据：select * from student where pt='20200101';
如果HDFS上已经上传了数据到某个分区，Hive表查询不到数据，可能是没有创建对应的分区和HDFS上的目录关联起来，添加分区并指定路径即可：alter table student add partition(pt='20200101') location '/data/student/pt=20200101';
（注意：如果hdfs上已经存在该分区数据了，Hive表添加分区时必须指定location，否则创建分区会报错）
修复分区命令（如果HDFS上已经上传了某个分区的数据，但Hive表还未创建对应分区，那么在Hive表中是查询不到该分区的数据的，可以通过修复命令一键修复HDFS上已存在但Hive表未创建的分区）：msck repair table student;

5. Hive的三种文件存储格式

Hive三种文件存储格式分别是ORC、Parquet、textFile，其中textFile是基于行式存储，ORC和Parquet是基于列式存储的。
请添加图片描述
行存储的特点： 查询满足条件的一整行数据时，列存储则需要去每个聚集的字段找到对应的每个列的值，行存储只需要找到其中一个值，其余的值在相邻地方，所以此时行存储查询的速度更快。例如对于一个表，我们经常查询的是所有字段的值，即使用：select * from student; 那使用行存储的效率更高。

列存储的特点： 因为每个字段的数据聚集存储，在查询只需要少数几个字段的时候，能大大减少读取的数据量，每个字段的数据类型一定是相同的，列存储可以针对性的设计更好的压缩算法。例如对于一个表，我们经常查询的是部分字段的值，即类似使用：select id,name from student; 查询语句，那使用列存储的效率更高。

创建一个Hive表，不指定表的存储方式时，默认就是textFile格式，textFile格式数据不做压缩，磁盘开销大，数据解析开销大。可结合Gzip、Bzip2使用，但使用Gzip这种方式，hive不会对数据进行切分，从而无法对数据进行并行操作。

经测试，三种存储方式的压缩比：ORC>Parquet>textFile；三种存储方式的查询速度相近。

orc格式与textFile格式的不同点：

对于textFile存储方式，我们通常使用load的方式向hive表导入数据，也就是说可以将本地文件或者HDFS上的文件通过load命令加载到hive表，或者直接将对应的文件上传到hive表在HDFS上的路径；但是orc存储格式的表不支持load方式加载数据。

例如：使用load命令将本地文件数据加载到存储格式为orc的hive表失败
请添加图片描述
例如：直接将本地文件上传到hive表所在HDFS的路径，虽然上传成功，但是查询时报错，无法解析该文件

3. 正确的导入数据到orc存储格式的表，应该是通过textFile存储格式的临时表进行中转，先通过load数据的方式导入数据到textFile中间表，再使用select语句查询该中间表，并通过insert的方式加载到orc存储格式的表。

例如：test_textfile是textfile存储格式的表，通过查询该表的数据，使用insert语句加载到orc存储格式的test_orc表
请添加图片描述

4. 对于orc存储格式的表数据，在HDFS上体现的文件夹名称是类似于0000_00之类的；而textFile格式的表数据，可以直接是一个.txt为后缀的文本文件。

6. 关于Hive表的更新操作

Hive不支持对表进行更新操作，包括更新数据、删除数据，也就是不支持delete、update语句
请添加图片描述
对于Hive表的更新操作，最常见的删除某个分区的数据，例如创建了一个根据时间进行分区的Hive表，可能会定期对Hive表进行清理，那么就会涉及对分区数据的删除，但是只能删除整个分区的数据，不支持对分区的部分数据进行更改。
请添加图片描述

7. Hive的数据类型

# 基本数据类型
1. 数值类型-整型
    TINYINT (1字节整数)
    SMALLINT (2字节整数)
    INT/INTEGER (4字节整数)
    BIGINT (8字节整数)
2. 数值类型-浮点型
    FLOAT (4字节浮点数)
    DOUBLE (8字节双精度浮点数)
3. 时间类型
	TIMESTAMP (时间戳) (包含年月日时分秒的一种封装)
	DATE (日期)（只包含年月日，例如2021-09-10）
4. 字符串类型
	STRING （不设定长度）
    VARCHAR (字符串1-65355长度，超长截断)
    CHAR (字符串，最大长度255)
5. 其它类型
	BOOLEAN（布尔类型）：true false
	BINARY (二进制)

# 复合数据类型
1. array数组类型
	例如：array<int>
	通过 collection items terminated by ',' 指定数组中的元素分隔符
	导入数据示例：swing,sing,coding
    查看数据示例：["swing","sing","coding"]
    根据数组下标访问值：select id,name,favors[1] from stu;
2. map集合类型，kv键值对类型
    例如：map<int,string>
    通过 collection items terminated by ',' 指定了map中元素之间的分隔符
    通过 map keys terminated by ':' 指定了key和value之间的分隔符
    导入数据示例：chinese:80,math:90,english:100
    查看数据示例：{"chinese":80,"math":90,"english":100}
    根据键访问值：select scores['chinese'],scores['math'] from stu2;
3. struct复合类型，类似于java中的对象
	例如：struct<home_addr:string,office_addr:string>
	导入数据示例：bj,sh
	查看数据示例：{"home_addr":"bj","office_addr":"sh"}
	根据.访问值：select id,name,address.home_addr from stu3;

Struct类型和Map类型的区别

map中可以随意增加k-v对的个数；struct中的k-v个数是固定的
map在建表语句中需要指定k-v的类型；struct在建表语句中需要指定好所有的属性名称和类型
map中通过[]取值；struct中通过.取值，类似java中的对象属性引用
map的源数据中需要带有k-v；struct的源数据中只需要有v即可

总体而言还是map比较灵活，但是会额外占用磁盘空间，因为他比struct多存储了数据的key struct只需要存储value，比较节省空间，但是灵活性有限，后期无法动态增加k-v。

# 复合类型建表语句示例
create table student (
id int comment 'id',
name string comment 'name',
favors array<string>,
scores map<string, int>,
address struct<home_addr:string,office_addr:string>
) row format delimited
fields terminated by '\t'
collection items terminated by ','
map keys terminated by ':'
lines terminated by '\n';

8. Hive的窗口函数

Hive的窗口函数为over()，窗口函数通常与分析函数一起使用，例如count()，sum()， max()等；分析函数之后紧跟着一个窗口函数，代表的是该分析函数的作用范围为后面的窗口；Hive会给每一条查询出来的数据开窗，窗口的大小取决于over()函数的设置。

窗口函数over()的语法结构：

分析函数 over(partition by 列名 order by 列名 rows between 开始位置 and 结束位置)

over()函数中包括三个函数：包括指定分区partition by 列名、排序order by 列名、指定窗口范围rows between 开始位置 and 结束位置。我们在使用over()窗口函数时，over()函数中的这三个函数可组合使用也可以不使用。

over()函数中如果不使用这三个函数，窗口大小是针对查询产生的所有数据，如果指定了分区，窗口大小是针对每个分区的数据。

窗口范围说明：

PRECEDING：往前
FOLLOWING：往后
CURRENT ROW：当前行
UNBOUNDED：起点（一般结合PRECEDING，FOLLOWING使用）
UNBOUNDED PRECEDING 表示该窗口最前面的行（起点）
UNBOUNDED FOLLOWING：表示该窗口最后面的行（终点）

窗口范围举例：

ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW（表示从起点到当前行）
ROWS BETWEEN 2 PRECEDING AND 1 FOLLOWING（表示往前2行到往后1行）
ROWS BETWEEN 2 PRECEDING AND 1 CURRENT ROW（表示往前2行到当前行）
ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING（表示当前行到终点）

我们常使用的窗口范围是ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW（表示从起点到当前行），常用该窗口来计算累加。

如果不指定窗口范围，默认就是从起点到终点，也就是ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING

窗口函数与group by：

在Hive中，通过group by 进行分组，select查询的字段必须是包含在分组字段里的，否则会报错，例如我们希望通过name进行分组，统计每个分组的总数，并查询出对应的成绩，通过group by无法实现这个操作，因为分组字段里不包含score
请添加图片描述
去掉score字段，就可以查询出每个分组的name以及总数，group by实际上也实现了去重的效果

select name, count(*) as times group by name from student_score;

请添加图片描述
而通过窗口函数，就可以解决这个问题，select查询的字段也可以是不包含在分组字段里的

select name, score, count(*) over(partition by name) from student_score;

请添加图片描述
窗口函数举例：

示例数据如下：
请添加图片描述

# count(*)的作用范围是over()指定的窗口大小，该窗口大小为查询出来的所有数据
# 该sql表达的意思是：查询出test表的所有数据，并计算出所有数据的总数
select name, count(*) over() from test;

请添加图片描述

# 该窗口大小为根据name进行分区的每个分区数据
# 该sql表达的意思是：根据name进行分区，查询出test表的所有数据，并计算出每个分区的总数
select *, count(*) over(partition by name) from test;

请添加图片描述

# over()窗口函数加上了order by排序，可以实现分组内的数据排序，并且对于窗口前的分析函数来说，数据是累加的
select *, count(*) over(partition by name order by score) from student_score;

请添加图片描述

select *, sum(score) over(partition by name order by score) as sum_score from student_score;

请添加图片描述
分组排序取TopN

涉及的函数有：row_number(), over()

（row_number()对数据进行编号，over()可以理解为把数据划分到一个窗口内，里面可以加上partition by，表示按照字段对数据进行分组，还可以加上order by 表示对每个分组内的数据按照某个字段进行排序）

a. 对数据进行编号：select *, row_number() over() as num from student_score;
请添加图片描述
b. 对数据按照科目进行分组，然后对分组内的数据按照成绩进行降序排序，再编号：select *, row_number() over(partition by sub order by score desc) as num from student_score;

c. 取每组数据的前三条数据，也就是分组后取每组的Top3：select * from (select *, row_number() over(partition by sub order by score desc) as num from student_score) t where t.num <= 3;
请添加图片描述
d. 取每组分数最高的那条数据，再对组外的数据根据分数进行降序排序：select * from (select *, row_number() over(partition by sub order by score desc) as num from student_score) t where t.num <= 1 order by score desc;

注意：row_number() 可以替换为 rank() 或者 dense_rank()，其中rank() 表示上下两条记录的score相等时，记录的行号是一样的，但下一个score值的行号递增N （N是重复的次数），比如：有两条并列第一，下一个是第三，没有第二；dense_rank() 表示上下两条记录的score相等时，下一个score值的行号递增1，比如：有两条并列第一，下一个是第二。

综合案例

案例背景：query_failed_record表是查询失败sql表，保存了查询失败的sql记录，字段包括id、sql、user_name（用户名）、query_time（查询时间）、reason（失败原因）；现在要根据sql语句和用户名进行分组，统计每组失败次数，并查询出每组最新查询的那条记录信息，最后对每组数据先根据报错总次数降序排序，报错次数相同的再按照查询时间降序排序，可以分解为以下几步查询

# 根据sql语句和用户名分组统计每组总数，也就是报错总次数
select *, count(*) over(partition by sql, user_name) as error_times from query_failed_record;

# 给每个分组组内进行按照查询时间降序排序，并进行编号
select *, row_number() over(partition by sql, user_name order by query_time) as num from (select *, count(*) over(partition by sql, user_name) as error_times from query_failed_record) s;

# 取出已经排好序的每组第一条数据，并先按照报错次数error_times降序排序，报错次数相同的再按照查询时间降序排序
select id, sql, user_name, query_time, reason, error_times from (select *, row_number() over(partition by sql, user_name order by query_time) as num from (select *, count(*) over(partition by sql, user_name) as error_times from query_failed_record) s) t where num = 1 order by error_times desc, query_time desc;