Hive从入门到精通

A115EE

已于 2023-05-06 14:29:28 修改

阅读量1.4k

点赞数 1

文章标签： hive hadoop 大数据

于 2023-05-05 16:12:20 首次发布

本文链接：https://blog.csdn.net/A115EE/article/details/130509235

版权

Hive

概念：
- Hive是一个构建在Hadoop之上的数据分析工具。
- Hive没有存储数据的能力，只有使用数据的能力。
- Hive的底层是由HDFS来提供数据存储。
- Hive的元数据存储在Derby或外部数据库中。（MySQL…）
- Hive可以将结构的数据文件映射成一张数据库表，并且提供类似SQL的查询功能。
- 本质就是将HQL转化成MapReduce程序。
- Hive分析数据的底层实现是MapReduce，执行程序运行在Yarn上。

优点

Hive的交互方式是类似SQL的语法，不用再去写MapReduce的过程，提高开发效率。
有良好的容错性和扩展性。
Hive的优势在于处理大数据，对于处理小数据没有优势。转为Hive的延迟比较高。
Hive支持用户自定义函数，用户可以根据自己的需求来实现自己的函数。

缺点

Hive的HQL表达能力有限。业务逻辑复杂时，无法使用HQL完成，还是需要借助MapReduce来实现。
Hive操作默认是基于MapReduce引擎的。而MapReduce引擎的特点就是慢、延迟高、不适合交互式查询。
Hive调优比较困难，粒度较粗。

Hive的应用场景

日志分析。
海量结构化数据的离线分析。

Hive与SQL的区别

数据存储：
- Hive：Hive是建立在Hadoop之上的，所有Hive的数据都是存储在HDFS中。
- SQL：一般SQL数据库将文件保存在本地。
数据更新：
- Hive：不支持对数据的修改和添加。
- SQL：支持数据的修改和添加操作。
执行方式：
- Hive：Hive中大多数的查询执行是通过Hadoop提供的MapReduce来实现的。
- SQL：数据库通常有自己的执行器。
执行延迟：
- Hive：延迟较高。Hive在查询的时候，由于没有索引需要扫描整张表，延迟较高。另一个导致Hive延迟较高的因素是MapReduce框架，因为MapReduce本身具有较高的延迟。
- **SQL：延迟较低。 **

Hive的数据类型

类型	描述
int	对应Java中的Integer类型
bigint	对应Java中的Long类型
float	对应Java中的Float类型
double	对应Java中的Double类型
boolean	对应Java中的Boolean类型
varchar(n)	变长字符串，＂n＂代表长度
char(n)	定长字符串，＂n＂代表长度
string	对应Java中的String类型，长度为8M
date	日期类型，格式为＂yyyy-MM-dd HH:mm:ss＂
timestamp	时间戳类型
array【复杂类型】	与Java中的Array类似
map【复杂类型】	与Java中的Map类似
struct【复杂类型】	封装了一个命名字段集合，复杂数据类型允许任意层次的嵌套

Hive构架

Client

Hive允许Client的连接方式有三种：
1. CLI。（Hive Shell，Hive的默认方式）
2. JDBC/ODBC。（Java访问Hive的方式）
3. WEBUI。（浏览器访问Hive）

MetaStore（元数据）

MetaStore：元数据。（表的信息、表与文件的映射关系、表的数据所在的目录…）
- 表所属的数据库，默认是default库。
元数据默认存放在自带的Derby数据库中，推荐使用MySQL数据库。
连接数据库需要提供：Driver、uri、username、password。

Hive默认将元数据存储在Derby数据库中。
Derby数据库仅支持单线程操作。
若有一个用户在操作，其他用户则无法使用，造成效率不高。
而且，当切换目录后，重新进入Hive，会找不到原来已经创建的数据库和表。因此，一般使用MySQL数据库存储元数据。

MetaStore服务的作用

客户端连接MetaStore服务后，MetaStore服务再去连接MySQL数据库来存储数据。
有了MetaStore服务，就可以有多个客户端同时连接，而且这些客户端不需要知道MySQL数据库的用户名和密码，只需要连接MetaStore服务即可。

Driver

解析器

将SQL字符串转换成抽象的语法树（AST）。
对语法树进行语法分析。校验表是否存在、字段是否存在、SQL语义是否有误。

编译器

将语法树编译生成逻辑执行计划。

优化器

对逻辑执行计划进行优化。

执行器

把逻辑执行计划转换成可以运行的物理计划。（将HQL转换成MR/Spark）

Hive的工作原理

当创建表的时候，需要指定HDFS的文件路径，表和其文件路径会保存到MetaStore服务上。从而建立表和数据的映射关系。
当数据加载入表时，会根据映射获取到对应的HDFS路径，将数据导入。
当用户输入SQL后，Hive会自动的到封装好的MapReduce模板中进行匹配。匹配完后，运行MapReduce程序，Hive会将其转换成MapReduce任务或者Spark任务，提交到Yarn上执行，执行成功将返回结果。

Hive远程模式的常用命令

检测MySQL服务是否启动：
- **systemctl status mysqld**
启动Zookeeper服务：
- **zkServer.sh start**
查看Zookeeper的服务状态：
- **zkServer.sh status**
启动所有的服务：
- **start-all.sh**
启动MetaStore服务：（远程模式下，需要单独启动MetaStore服务）
- **hive --service metastore**
启动HiveServer2服务：
- **hiveserver2**
连接客户端：
- 方式一：
  - **hive**
- 方式二：
  - **beeline -u jdbc:hive2://IP地址:端口号 -n 用户名**
  - **beeline -u jdbc:hive2://Node-1:10000 -n root**

Hive的基本操作

数据库的相关操作

创建数据库

创建一个数据库（数据库在HDFS上的默认路径为**/hive/warehouse/xxx.db**）：
- **create database 数据库名;**
如果数据库不存在，则创建：（**if not exists**）
- **create database if not exists 数据库名;**
创建数据库，并指定数据库的创建位置（数据库在HDFS上的存储路径）：
- **create database if not exists 数据库名 location '/HDFS上的路径'; **

切换数据库

切换数据库：
- **use 数据库名;**

删除数据库

删除数据库：
- **drop database 数据库名;**
删除数据库（如果存在则删除，不存在则不会报错）：
- **drop database if exists 数据库名;**
级联删除数据库（强制删除数据库）：
- **drop database if exists 数据库名 cascade;**

表的相关操作

创建表

字段说明：

**create table**：创建一个指定名称的表。
- 如果相同名称的表已经存在，则会抛出异常。
- 可以使用**if not exists**语句来先进行判断。
**external**：**external**关键字，表示创建的表是一个外部表。在创建表的同时，指定一个指向实际数据的路径（**location**）。
1. 创建内部表时，会将数据移动到Hive指向的默认路径。（默认位置：**/hive/warehouse**）
2. 创建外部表时，仅记录数据所在的路径，不对数据的位置做任何改变。
3. 在删除表的时候，内部表的元数据和文件会被一起删除，而外部表只会删除元数据，不会删除文件。
**comment**：为表和列添加注释。
**partitioned by**：创建分区表。
**clustered by**：创建分桶表。
**sorted by**：排序方式。（不常用）
**row format**：用户在创建表的时候可以自定义序列化器（SerDe），或使用默认的序列化器（SerDe）。
- 默认的序列化器（SerDe），只支持单字节分隔符。
- **SerDe**是**Serialize**与**Deserialize**的简写，目的是用于序列化和反序列化。
**stored as**：指定文件的存储类型。
- 常用的存储类型有：
  1. **sequencefile**：二进制序列文件。
  2. **textfile**：文本。
  3. **rcfile**：列式存储格式文件。
**location**：指定表在HDFS上的存储位置。
**like**：允许用户复制现有的表结构，但是不会复制数据。

修改表名

修改表名：
- **alter table 旧表名 rename to 新表名;**
  - 内部表修改表名会同时修改文件目录。
  - 外部表因为目录是共享的，所以不会修改目录名称。

删除表

清空表：
- **truncate table 表名;**
  - 清空表，只会删除内部表的数据（HDFS文件），不会删除外部表中的数据（文件）。
删除表：
- **drop table 表名;**
  - 删除表的时候，内部表的元数据和文件会被一起删除，而外部表只会删除元数据，而不会删除文件。

将文件数据载入表中

使用**load data**语句将对应的数据文件移动到映射的表所在的路径下：
- **load data inpath '/文件在HDFS上的路径' into table 表名;**
或者直接将文件上传到表所在的路径下即可。

导出数据

按照指定的格式将数据导出到本地：

insert overwrite local directory '/本地路径'
    row format delimited fields terminated by ','
    collection items terminated by '-'
    map keys terminated by ':'
    lines terminated by '\n'
select *
from t_person;

将查询的结果导出到HDFS：

insert overwrite directory '/HDFS路径'
    row format delimited fields terminated by ','
    collection items terminated by '-'
    map keys terminated by ':'
    lines terminated by '\n'
select *
from t_user;

通过HDFS操作，拷贝文件到需要导出的路径：

hdfs dfs -cp /hive/warehouse/t_person/* /bd/export/person;

将元数据和文件同时导出：

-- 将表结构和数据同时导出
export table t_person to '/bd/export/person';


-- 测试恢复数据
-- 删除表
drop table t_person;

-- 恢复表结构和数据：
import from '/bd/export/person';

内部表和外部表的区别

内部表数据由Hive自身管理，外部表数据由HDFS管理。在删除表的时候，内部表的元数据和文件会被一起删除，而外部表只删除元数据，不删除文件。
内部表：默认创建的就是内部表。创建内部表时，会将数据移动到Hive指定的路径中。（Hive配置时的路径）
外部表：创建表的时候，仅仅指向一个外部目录而已。仅记录数据所在的路径，不对数据的位置做任何改变。

Hive的分区和分桶

Hive分区

作用：分区可以避免全表扫描，根据分区列查询指定目录，提升查询效率。
- 使用分区表时，尽量使用分区字段进行查询。如果不使用分区字段查询，就会全表扫描，分区就没有意义了。
特点：
- 在逻辑上，分区和未分区没有区别，还是同一张表。
- 在物理上，分区就是将分区表的数据存储在相同的文件夹中。
类型：
- 分区表类型分为静态分区和动态分区。
  - 静态分区：需要手动指定。
    - 单分区。
    - 多分区。
  - 动态分区：通过数据自动判断分区。
    - 单分区。
    - 多分区。
      :::success
      注意：静态分区和动态分区的建表语句是一样的。
创建单分区：
- **创建分区表 partitioned by (分区字段名分区字段类型)**
创建多分区表：
- **创建分区表 partitioned by (分区字段名分区字段类型, 分区字段名2 分区字段类型2)**

注意：**partitioned by()**括号中指定的分区字段名不能和表中的字段名一样。
:::

静态分区

语法：

-- 单分区：创建分区表 partitioned by (分区字段名 分区字段类型)
-- 多分区：创建分区表 partitioned by (分区字段名1 分区字段类型1, 分区字段名2 分区字段类型2)

-- 注意：partitioned by()括号中指定的分区字段名不能和表中的字段名一样。
-- 注意：如果创建的是多分区，那么前后两个分区的关系为父子关系。也就是，分区字段名1下面有多个分区字段名2子文件夹。

create table if not exists 表名
(
    字段名 字段类型
) partitioned by (分区字段名 分区字段类型)
    row format delimited fields terminated by "分隔符";

注意：

partition by()括号中指定的分区字段名不能和表中的字段名一样。
如果创建的是多分区，那么前后两个分区的关系为父子关系。即：分区字段名1下面有多个分区字段名2的子文件夹。

:::info
静态分区与动态分区的区别：

静态分区：
- 静态分区的列是在编译时期通过用户传递来决定的。
- 静态分区不管有没有数据，都会创建分区。
动态分区：
- 动态分区只有在SQL执行时才能决定。
- 动态分区是有结果集才会创建分区，否则不会创建分区。
  :::

动态分区

在Hive命令行设置动态分区参数。
:::info
– 开启动态分区支持
**set hive.exec.dynamic.partition=true;**

– 是否开始严格模式，是否允许所有分区都是动态的【strict：严格模式，要求至少包含一个静态分区列。nonstrict（默认）：非严格模式，则无此要求。】
**set hive.exec.dynamic.partition.mode=nonstrict;**
:::

创建动态分区。

-- 单分区：创建分区表 partitioned by (分区字段名 分区字段类型)
-- 多分区：创建分区表 partitioned by (分区字段名1 分区字段类型1, 分区字段名2 分区字段类型2)

-- 注意：partitioned by()括号中指定的分区字段名不能和表中的字段名一样。
-- 注意：如果创建的是多分区，那么前后两个分区的关系为父子关系。也就是，分区字段名1下面有多个分区字段名2子文件夹。

create table if not exists 表名
(
    字段名 字段类型
) partitioned by (分区字段名 分区字段类型)
    row format delimited fields terminated by "分隔符";

添加分区

添加分区：
- **alter table 表名 add if not exists partition (分区名 = 分区字段值);**

查看分区

查看分区：
- **show partitions 表名;**

删除分区

删除分区：
- **alter table 表名 drop partition (分区字段名 = 分区字段值);**

Hive分桶

分桶原理：
- Hive是采用列值哈希，然后对桶的数量取模的方式，决定该条记录要存放在哪个桶中。
计算公式：
- **数据存放在bucket的位置 = hash(列名) % bucket_number**

分桶

在Hive命令行开启分桶功能。
:::info
– 开启分桶功能
**set hive.enforce.bucketing=true;**

– 设置Reduce的个数。默认是**-1**，**-1**时会通过计算得到Reduce的个数。（一般Reduce的个数与buckets的数量一致）
**set mapred.reduce.tasks=3;**
:::

分桶语法：

create table 表名(字段1 类型1, 字段2 类型2)
clustered by (表内字段)
sorted by (表内字段)
into 分桶数 buckets;

窗口函数

:::warning

窗口函数

窗口函数指的是＂over()＂函数，窗口是由一个＂over＂语句定义的多行记录。
:::

窗口函数练习

查询每个部门薪资第二高的员工信息

create table employee
(
    employee_id     VARCHAR(8),
    employee_name   VARCHAR(8),
    employee_salary INT(8),
    department      VARCHAR(8)
);

-- 插入数据
insert into employee (employee_id, employee_name, employee_salary, department)
    value ('a001', 'Bob', 7000, 'b1')
    ,('a002', 'Jack', 9000, 'b1')
    ,('a003', 'Alice', 8000, 'b2')
    ,('a004', 'Ben', 5000, 'b2')
    ,('a005', 'Candy', 4000, 'b2')
    ,('a006', 'Allen', 5000, 'b2')
    ,('a007', 'Linda', 10000, 'b3');

create table department
(
    department_id   VARCHAR(8),
    department_name VARCHAR(8)
);

-- 插入数据
insert into department (department_id, department_name)
    value ('b1', 'Sales')
    ,('b2', 'IT')
    ,('b3', 'Product');

解题思路：

使用开窗函数，根据部门编号分组，然后根据员工薪资进行降序排序。
**dense_rank()****：连续不间断。**可能会出现两个第一，就没有第二的情况。

关联子查询，根据部门编号进行关联，条件是最大工资的排名为2的记录。

select employee.employee_id,
       employee.employee_name,
       employee.department,
       employee.employee_salary,
       t_temp.max_sal
from employee
         join (select *, dense_rank() over (partition by department order by employee_salary desc ) as max_sal
               from employee) t_temp
              on employee.employee_id = t_temp.employee_id
where t_temp.max_sal = 2;

计算每个用户登录日期间隔小于5天的次数

create table login_info
(
    user_id    VARCHAR(8),
    login_time DATE
);

-- 插入数据
insert into login_info (user_id, login_time)
    value ('a001', '2021-01-01')
    ,('b001', '2021-01-01')
    ,('a001', '2021-01-03')
    ,('a001', '2021-01-06')
    ,('a001', '2021-01-07')
    ,('b001', '2021-01-07')
    ,('a001', '2021-01-08')
    ,('a001', '2021-01-09')
    ,('b001', '2021-01-09')
    ,('b001', '2021-01-10')
    ,('b001', '2021-01-15')
    ,('a001', '2021-01-16')
    ,('a001', '2021-01-18')
    ,('a001', '2021-01-19')
    ,('b001', '2021-01-20')
    ,('a001', '2021-01-23');

解题思路：

使用窗口函数，根据**id**进行分组，然后根据登录日期升序排序，查询出下一个登录日期。
**timestampdiff(interval, datetime_expr1, datetime_expr2)**
**interval**：日期间隔。（年、月、日、小时、分钟、秒）
**datetime_expr**：日期表达式。小日期在前，大日期在后。

使用嵌套子查询，根据id进行分组，查询条件为登录日期间隔数小于5天的记录。

select t_next_login.user_id,
       count(*) as num
from (select user_id,
             login_time,
             lead(login_time, 1) over (partition by user_id order by login_time) as next_login_time
      from login_info) as t_next_login
where timestampdiff(day, login_time, next_login_time) < 5
group by user_id;