Hive学习笔记_fetchoperator

Hive

一、 Hive概述

1. 什么是Hive

Hive是一个基于Hadoop的数仓开发工具。Hive将结构化的数据映射为一张表，提供类SQL语言，对数据进行分析检索功能。

所以，Hive不是一门SQL语言，而是一个Hadoop上进行数据分析的客户端，类SQL的分析工具。Hive将SQL转换成MR程序，并放到Yarn、Spark等分析引擎上并行执行。

2. Hive工作机制

1. 数据存储

• Hive部署在Hadoop，将真实数据全部存储在HDFS中；
• 而Hive数据的元数据信息都记录在metastore中，默认metstore使用Hive自带的derby。
• Hive操作数据时会从metastore读取元数据，映射到HDFS上的真实数据。

2. 工作流程

1. 用户在客户端执行SQL，SQL被发送到Driver；
2. Driver会从metastore读取元数据，映射到HDFS读取真实数据。
3. Driver中集成了很多常用的MR程序模板，将SQL翻译成MR程序，并放到计算引擎执行。
4. 计算引擎将数据分析完成后，把结果返回给客户端展示。

常用的计算引擎有：MapReduce、tez、Spark等。

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-ElTPyb7W-1664638646998)(E:\笔记\hadoop\图片\image-20221001110613017.png)]

3. Hive架构原理

1. 首先明确Hive不是一门语言，而是一个数仓开发工具。
2. Hive客户端可以通过CLI直连Driver，也可以通过JDBC/ODBC驱动，使用hiveserer2服务，远程连接Driver。
   • JDBC的移植性好于ODBC。
3. Hive的真实数据存储在HDFS中，元数据存在metastore。metastore中的信息是真实数据在HDFS存储位置的映射。
   • 元数据信息包括：Hive的数据库名（默认数据库是Default），表名、表的拥有者、列/分区字段、表的类型（管理表、外部表）、表数据存放位置等。
4. 客户端写的SQL会被放到Driver，使用Driver中的集成模板翻译成对应的MR程序，并将MR程序放到计算引擎分析运算。

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-2FlMtSXR-1664638646998)(E:\笔记\hadoop\图片\image-20221001110942902.png)]

4. Hive的优缺点

优点：

1. Hive使用类SQL语法进行开发，提供了快速开发的能力；
2. 避免大量写MR程序，减少了开发成本和时间；
3. Hive支持用户自定义函数，拥有较强的扩展性。

缺点：

1. Hive的执行延迟高，同样难以做到毫秒级的运算；
2. Hive的数据存在HDFS，所以数据不支持随机读写，只能在数据的末尾追加；
3. Hive不能高效的执行update和delete操作。

4. Hiveserver2服务

Hive提供了两种连接到Driver的方式：CLI直连、hiveserver2代理访问。

hiveserver2服务提供远程代理访问功能，但是由于Hadoop集群的数据具备访问控制权限，那么通过hiveserver2访问数据，是一那种身份进行访问呢？

hiveserver2访问数据库的具体身份是由hive.server2.enable.doAs参数决定的，该参数代表是否启用用户模拟功能。

如果该参数设置为false，那么就是启动hiveserver2服务的用户的身份。

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-KShRYphc-1664638646999)(E:\笔记\hadoop\图片\image-20221001112723288.png)]

如果设置为true，hiveserver2会模拟成登陆客户端的用户，用模拟用户的身份访问数据。

默认情况下，该参数设置为true。因为开启模拟用户功能后，可以保证各个用户之间的权限隔离。

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-Wdcuz6Tc-1664638646999)(E:\笔记\hadoop\图片\image-20221001112733856.png)]

二、 Hive的数据类型、表结构

1. Hive的数据类型

基本数据类型：

集合数据类型：

Hive中由上述三种复杂数据类型。其中Map和Array类似于Java中的集合；struct是一种特殊的类型，一个struct结构体中可以包含多个成员，每个成员都可以是不同的数据类型。

类型转换机制：

Hive中的原子数据类型可以进行隐式转换，例如Java中的自动类型提升。例如tinyint会自动转换成int，只能向上转型，不能够自动向下转型。除非使用==cast( xxx as 数据类型)==进行类型转换。

Hive中所有的整数类型、float类型、string类型都可以隐式转换为double类型。

tinyint、smallint、int都可以转换为float。

但是，boolean类型不能转换成其他任何类型。

2. Hive表

Hive的表分为管理表（内部表）、外部表。

1. 管理表（内部表）

Hive执行创建表时，默认创建的都是管理表（内部表）。管理表的数据，都会或多或少的被Hive控制生命周期，表数据默认存放在Hive配置的默认存储路径下，默认路径通过修改配置文件修改。

删除管理表时，Hive会删除表的元数据和真实数据信息。所以管理表不适合跟其他工具共享数据。

CREATE TABLE [IF NOT EXISTS] table_name  -- 创建表名
[(col_name data_type [COMMENT col_comment], ...)] 	-- 添加表字段
[COMMENT table_comment] 	-- 表的描述信息
[PARTITIONED BY (col_name data_type [COMMENT col_comment], ...)]	-- 表的分区字段，创建分区表
[CLUSTERED BY (col_name, col_name, ...) 	-- 表内数据分桶字段，创建分桶表
[SORTED BY (col_name [ASC|DESC], ...)] INTO num_buckets BUCKETS] 	-- 对分桶表中一个或多个列另外排序（不常用）
[ROW FORMAT row_format] 	-- 指定建表的格式
[STORED AS file_format] 	-- 存储格式
[LOCATION hdfs_path]		-- 声明存储位置	
[TBLPROPERTIES (property_name=property_value, ...)]		-- 向表中添加自定义或预设的元数据属性，并赋值
[AS select_statement]	-- 根据查询结果创建表
[LIKE table_name]		-- 允许复制表的结构，但是不能复制表内数据

1
2
3
4
5
6
7
8
9
10
11
12
13

2. 外部表

创建外部表时使用==external==声明创建的是外部表。

CREATE EXTERNAL TABLE [IF NOT EXISTS] table_name   -- 创建表名
1

外部表在删除表时，hive只会删除表的元数据信息，并不会删除表内的真实数据。

外部表和内部表可以相互转换。

‘EXTERNAL’=‘TRUE’，转换为外部表，FALSE为内部表，书写格式默认大写。

3. 修改表

ALTER TABLE table_name RENAME TO new_table_name	-- 修改表名
1

除了修改表名，Hive还支持修改表内信息：

• 表内列名可以随意修改；

  ALTER TABLE table_name CHANGE [COLUMN] col_old_name col_new_name column_type [COMMENT col_comment]	-- 更新列的信息，使用CHANGE关键字
  1

• 但是列的字段类型只能小改大，不能大改小；

• ADD可以在表的最后一列增加字段；

• Replace是将表的所有字段全部替换，所以使用Replace时需要将字段和数据类型全部一一对应；

• 使用Replace可以增加、减少字段数量。

  ALTER TABLE table_name ADD|REPLACE COLUMNS (col_name data_type [COMMENT col_comment], ...)	--	增加、替换关键字，替换其实包括了修改、增加、删除列的功能
  1

4. 删除、清空表

• DROP可以删除表的信息和表结构；

  DROP TABLE table_name;
  1

• truncate只能清空表内信息，不能删除表结构。

  TRUNCATE TABLE table_name;
  1

3. DML数据操作

1. 数据装载

load data [local] inpath '数据的path' 	-- 将数据导入的路径，local表示本地路径，不加local是HDFS路径
[overwrite] into table student [partition (partcol1=val1,…)];
-- overwrite表示覆盖原表中的数据。

insert into(overwrite) table table_name_A
select * from table_name_B	-- 将B表中查询的结果插入到A表中，overwrite表示原表数据，into表示普通插入数据
1
2
3
4
5
6

使用 insert…select 往表中导入数据时，查询的字段个数必须和目标的字段个数相同，不能多，也不能少,否则会报错。但是如果字段的类型不一致的话，则会使用null值填充，不会报错。

而使用load data形式往hive表中装载数据时，则不会检查。如果字段多了则会丢弃，少了则会null值填充。同样如果字段类型不一致，也是使用null值填充。

2. SQL执行顺序

from -> join -> where -> group by -> having -> select -> distinct -> order by -> limit -> union
1

• join：hive中支持等值连接，不支持非等值连接；
  • join：按照条件关联，只查询匹配相同字段的数据。
  • left join：左表为主表，查询左表中符合where条件的值，如果有右表中不存在该税局，则返回null。
  • right join：与左连接相同，只是以右表为主表。
  • full join：将所有满足where条件的数据全部返回，如果任意一张表中的某个字段没有符合条件的值，则返回null。
• group by：按照分组条件对数据进行聚合，聚合之后数据量会减少，并可能改变表的粒度；
• having：having只能用于有group by的分组统计，声明在group by之后。having可以使用分组聚合函数，而where后不能接分组聚合函数。
• 笛卡尔积：省略连接条件或是连接条件无效，将表中所有的列互相连接。
• union/union all：联合查询，将两个select的查询结果上下拼接成一张表，union查询的字段必须完全相同，字段名以第一张表为准。
  • union查询结果去重；
  • union all查询结果不去重。
• order by：将数据全局排序；
• limit：全部数据中取出符合limit条件的所有数据。
• sort by：针对reduce阶段的内部排序，是的数据在分区内有序，但是全局数据不一定有序。
• distribute by：类似于mr、kafka中的分区概念，将数据按照自定义的规则分到指定的reduce，提高任务并行度，常常配合sort by使用。
• cluster by：分区排序，可以替换distribute by … sort by进行分区排序，但是cluster by只能升序排序，不能改变顺序。

三、 Hive函数

Hive中一共封装了四种函数：单行函数、聚合函数、炸裂函数、窗口函数。

1. 单行函数

一行数据输入，输出一行数据 -----> 一进一出。

• 时间函数
• 集合函数
• 流程控制函数
• 字符串函数
• ……

2. 聚合函数

多行数据输入，一行数据输出 -----> 多进一出。

• 普通聚合函数（sum、count、avg……）
• 高级聚合函数
  • collect_set：将数据收集成set集合，并将结果去重
  • collect_list：将数据收集成list集合，结果不去重

3. 炸裂函数

一行数据输入，多行数据输出，将一行数据炸裂成多行 -----> 一进多出。

• explode：将数组或map炸开，一行数据分成多行输出。
• posexplode：在explode函数基础上给结果添加一个索引列，索引从0开始。
• json_tuple：将json字符串中的属性取出。

hive中不支持将炸裂函数和普通的查询列一起写。

所以炸裂函数需要使用lateral view侧写体现。

侧写lateral view

select ...	--(普通查询条件)
from table_name
lateral view
udtf(expression) -- 炸裂函数（表达式）
table_name_tmp -- (虚拟表名)
AS colum_name_tmp -- (虚拟表中的字段名)
1
2
3
4
5
6

lateral view每行数据都调用udtf，将一行数据炸成多行并将结果存储为一张虚拟表，表内字段名可以自定义，生成的虚拟表支持起别名。

4. 窗口函数

窗口函数并不是普通的聚合函数。窗口函数是给聚合函数确定聚合的范围，并给范围内的数据添加标记，标记将作为最后一列数据输出，支持起别名。

注意：窗口函数是一行一行执行的！

select sum(xxx) over (partition by a order by b rows between ... and ...)
1

• over：窗口函数开窗关键字，默认的窗口大小包含所有数据。
• partition by：按照分区字段划定窗口范围，相同字段进入同一个窗口。
• order by：窗口内的数据按照指定规则排序，并且可以指定升序或降序。
• rows between … and …：可以规定窗口函数中行的范围。

常用的窗口函数

• lag(col,n,default_val)：往前第n行数据。
• lead(col,n, default_val)：往后第n行数据。
• first_value (col,true/false)：当前窗口下的第一个值，第二个参数为true，下一条数据取上一个非空值。
• last_value (col,true/false)：当前窗口下的最后一个值，第二个参数为true，下一条数据取上一个非空值。
• max：最大值。
• min：最小值。
• sum：求和。
• avg：平均值。
• count：计数。
• rank：排名相同时会重复总数不会减少。
• dense_rank：排名相同时会重复总数会减少。
• row_number：行号。
• ntile：分组并给上组号。

5. 自定义函数

Hive支持用户自定义函数，具有良好的扩展性。

• udf：一进一出函数。
• udaf：自定义聚合函数，多进一出。
• udtf：自定义炸裂函数，一进多出。

四、 分区表和分桶表

1. 分区表

• Hive中的分区表实际就是将数据按照某种规则分目录存储。在HDFS中按照分区表的字段生成一个文件夹存放符合规则的数据。
• 分区表提高了Hive的查询效率和执行效率。
• 分区表的字段不能是数据表中已经存在的数据；可以将分区字段当作虚拟列或是文件夹的名称。

动态分区

Hive支持动态分区，插入数据时数据库会自动按照分区字段的值将数据插入对应分区，使用动态分区需要关闭严格模式。

• 动态分区是基于SQL查询语句的参数推断分区名称，从而建立分区。动态分区的分区表字段必须卸载查询的最后一列。
• 多字段分区时，静态分区字段要声明在动态分区之前，如果分区字段全部为动态分区，则需要按照分区声明顺序，依次查询分区字段。

Hive的分区表与Kafka中的分区概念不同。

• Hive的分区是将数据按照一定的规则存储到同一个分区（HDFS中的文件夹）。
• Kafka的分区则是将topic下的数据分为多个分区，供消费者组内的不同消费者消费，提高数据的吞吐量和消费并行度。

2. 分桶表

• 将一张表或一个分区中的数据，划分为更细粒度的数据范围。
• 将数据细分为桶文件，可以提高join的效率，加速数据抽样的效率。
• 分区针对的是数据的存储路径；分桶针对的是数据文件。

3. 压缩和存储格式

• 压缩：Hive中存储的数据在hdfs上，所以支持压缩，常用的压缩算法：gzip、bigzip2、lzo、snappy、zstd等。
• Hive主要支持的格式：textfile、sequencefile（二进制文件），orc（列式存储），parquet。

Hive常用的存储格式搭配： ORC + snappy。

五、Hive优化

1. explain执行计划

explain计划由若干个stage组成，stage之间相互依赖。每个stage又包括map operator tree和reduce operator tree。一个operator代表map或reduce在单一一个阶段的逻辑操作。

常见的Operator及其作用

• TableScan：表扫描操作，通常map端第一个操作肯定是表扫描操作
• Select Operator：选取操作。
• Group By Operator：分组聚合操作。
• Reduce Output Operator：输出到 reduce 操作。
• Filter Operator：过滤操作。
• Join Operator：join 操作。
• File Output Operator：文件输出操作。
• Fetch Operator 客户端获取数据操作。

2. Hive优化

1. 分组聚合

未经优化的分组聚合，是通过一个MR程序实现，按照正常的MR程序流程完成数据分析。分组聚合是在Map端维护一个map-side聚合，使用Hash Table将数据加载到缓存，减少Shuffle落盘的数据量，完成聚合后将结果按照分区字段发送到reduce端。

map-side能有效地减少shuffle的数据量，提高分组聚合的效率。

2. 数据倾斜

大量相同的key值涌入同一个reduce端，各个reduce task的数据分配量差异十分巨大，由此造成了数据倾斜。

优化方式

• 使用map-side聚合，map-side的最佳状态下是可以完全屏蔽数据倾斜的情况。
• skew-groupby优化：使用两个MR程序，第一个MR按照随机数分区，将数据发送到各个reduce端计算，第二个MR将数据一阶段的数据结果按照分组字段分区，发送对应reduce完成聚合。
• 如果是join导致的数据倾斜，可以使用map join、skew groupby、调整sql语句的方式优化。

3. 其他优化方式

1.cbo优化

Hive会自动调整join顺序，将数据量小的表提前进行join，再用结果去join其他的表从而减少数据传输量。

2. 谓词下推（predicate pushdown）

尽量将过滤操作前移，减少后续操作的数据量。cbo也会完成一部分谓词下推操作。

3. Fetch抓取

优化方式

• 使用map-side聚合，map-side的最佳状态下是可以完全屏蔽数据倾斜的情况。
• skew-groupby优化：使用两个MR程序，第一个MR按照随机数分区，将数据发送到各个reduce端计算，第二个MR将数据一阶段的数据结果按照分组字段分区，发送对应reduce完成聚合。
• 如果是join导致的数据倾斜，可以使用map join、skew groupby、调整sql语句的方式优化。

3. 其他优化方式

1.cbo优化

Hive会自动调整join顺序，将数据量小的表提前进行join，再用结果去join其他的表从而减少数据传输量。

2. 谓词下推（predicate pushdown）

尽量将过滤操作前移，减少后续操作的数据量。cbo也会完成一部分谓词下推操作。

3. Fetch抓取

Hive中的某些查询情况，可以不进行MR计算，直接从表中查询数据。例如select * from 表操作。