Hive分布式SQL计算平台

图片无法加载请跳转CSDN：Hive分布式SQL计算平台-CSDN博客

一、Hive 概述

使用Hadoop MapReduce直接处理数据所面临的问题 ，人员学习成本太高 需要掌握java、Python等编程语言
MapReduce实现复杂查询逻辑开发难度太大

基于Hive为用户提供了分布式SQL计算的能力，写的是SQL、执行的是MapReduce

使用Hive处理数据的好处

  • 操作接口采用类SQL语法，提供快速开发的能力（简单、容易上手）
  • 底层执行MapReduce，可以完成分布式海量数据的SQL处理

  1. 什么是分布式SQL计算？
    以分布式的形式，执行SQL语句，进行数据统计分析。

  2. Apache Hive是做什么的？
    将SQL语句翻译成MapReduce程序，从而提供用户分布式SQL计算的能力。
    传统MapReduce开发：写MR代码->得到结果
    使用Hive开发：写SQL->得到结果
    底层都是MR在运行，但是使用层面上更加简单了。

二、Hive架构

元数据管理，称之为Metastore服务
SQL解析器（Driver驱动程序），完成SQL解析、执行优化、代码提交等功能
用户接口：提供用户和Hive交互的功能

元数据存储：通常是存储在关系数据库如 mysql/derby中。Hive 中的元数据包括表的名字，表的列和分区及其属性，表的属性（是否为外部表等），表的数据所在目录等。
Hive提供了 Metastore 服务进程提供元数据管理功能

Driver驱动程序：包括语法解析器、计划编译器、优化器、执行器：完成 HQL 查询语句从词法分析、语法分析、编译、优化以及查询计划的生成。生成的查询计划存储在 HDFS 中，并在随后有执行引擎调用执行。

用户接口：包括 CLI、JDBC/ODBC、WebGUI。其中，CLI(command line interface)为shell命令行；Hive中的Thrift服务器允许外部客户端通过网络与Hive进行交互，类似于JDBC或ODBC协议。WebGUI是通过浏览器访问Hive。
Hive提供了 Hive Shell、 ThriftServer等服务进程向用户提供操作接口

三、Hive客户端

1、Hive有哪些客户端可以使用

  1. 执行：bin/hive，这是Hive提供的Shell环境，可以直接写SQL执行

  2. 启动HiveServer2，bin/hive --service hiveserver2

    这是Hive的Thrift服务，对外提供接口（默认端口10000）可供其它客户端链接，如：bin/beeline（内置）、IntelliJ IDEA （第三方）DataGrip（第三方）、DBeaver（第三方） 等

Hive的客户端体系如下

启动：在hive安装的服务器上，首先启动metastore服务，然后启动hiveserver2服务。

1
2
3
4
# 先启动metastore服务 然后启动hiveserver2服务
# 后台运行，并将标准输出和标准错误输出重定向到 /dev/null，使其不再显示在终端上，如有需要可以将日志重定向在文件中
nohup hive --service metastore 1>/dev/null 2>&1 &
nohup hive --service hiveserver2 1>/dev/null 2>&1 &

通过beeline连接

1
2
3
4
5
6
beeline  # 进入beeline
!connect jdbc:hive2://node1:10000	# 通过beeline连接hiveserver2
# 下面输入有hdfs权限的账号和密码，连接成功就可以操作了

# 连在一起可以通过-u参数直接连接
beeline -u jdbc:hive2://single01:10000

2、Hive第三方客户端

IntelliJ IDEA 连接 hive

  • 打开Database窗口 (在IDEA中，点击菜单栏中的View -> Tool Windows -> Database，打开Database工具窗口)
  • 在Database工具窗口中，点击上方的加号+，选择Data Source -> Apache Hive
  • 填写Hive数据库的连接信息，包括JDBC URL（如jdbc:hive2://localhost:10000）、用户名和密码（如果Hive配置了认证）
    连接需要驱动，点击可以下载驱动

连接成功后，你就可以在IDEA中通过SQL编辑器编写和执行HiveQL语句了。这些语句将直接发送到HiveServer2执行，并返回结果。

在这里插入图片描述

四、Hive使用语法

1、数据库操作

创建数据库

1
2
3
4
5
6
7
8
-- 语法：CREATE DATABASE [IF NOT EXISTS] db_name [LOCATION position];
create database if not exists myhive;
use myhive;
-- 查看数据库详细信息
desc database myhive;

-- 创建数据库并指定hdfs存储位置，使用location关键字，可以指定数据库在HDFS的存储路径。
create database myhive2 location '/myhive2';

删除数据库

1
2
3
4
5
6
-- 语法：DROP DATABASE db_name [CASCADE];
-- 删除一个空数据库，如果数据库下面有数据表，那么就会报错
drop  database  myhive;

-- 强制删除数据库，包含数据库下面的表一起删除
drop  database  myhive2  cascade;

数据库和HDFS的关系

  • Hive的库在HDFS上就是一个以.db结尾的目录
  • 默认存储在：/user/hive/warehouse内
  • 可以通过LOCATION关键字在创建的时候指定存储目录

2、内部表，外部表

创建表的语法

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
CREATE [EXTERNAL] TABLE [IF NOT EXISTS] table_name  
-- EXTERNAL: 可选，表示创建一个外部表。如果指定，则Hive不会管理表的数据文件，删除表时数据文件不会被删除。 
 
    [(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]  
    -- 可选，定义表的分桶策略。分桶通常与排序（SORTED BY）一起使用，但不是必须的。分桶可以提高某些查询的性能，特别是当进行抽样或需要局部排序时。  

    [ROW FORMAT row_format]  
    -- 可选，定义行的格式。常见的格式包括Delimited（分隔符分隔的字段）和SerDe（序列化/反序列化）。  
    -- 对于Delimited，通常还需要指定字段分隔符、集合元素分隔符等。  

    [STORED AS file_format]  
    -- 指定表数据的存储格式。Hive支持多种存储格式，如TEXTFILE、SEQUENCEFILE、ORC、PARQUET等。  
    -- 这些格式在压缩、性能、兼容性等方面有所不同。  

    [LOCATION hdfs_path]  
    -- 可选，指定表数据在HDFS上的存储位置。对于外部表来说，这个选项特别有用，因为它允许你指定表数据已经存在的位置。  
    -- 对于内部表，如果不指定LOCATION，Hive将在默认的仓库目录中为表创建一个目录。

关键词：EXTERNAL，创建外部表、PARTITIONED BY， 分区表、CLUSTERED BY，分桶表、STORED AS，存储格式、LOCATION，存储位置

数据类型

分类 类型 描述 字面量示例
原始类型 BOOLEAN true/false TRUE
TINYINT 1字节的有符号整数-128~127 1Y
SMALLINT 2个字节的有符号整数， -32768~32767 1S
INT 4个字节的带符号整数 1
BIGINT 8字节带符号整数 1L
FLOAT 4字节单精度浮点数 1.0
DOUBLE 8字节双精度浮点数 1.0
DEICIMAL 任意精度的带符号小数 1.0
STRING 字符串，变长 “a” ‘b’
VARCHAR 变长字符串 “a”, ‘b’
CHAR 固定长度字符串 “a”, ‘b’
BINARY 字节数组
TIMESTAMP 时间戳，毫秒值精度 122327493795
DATE 日期 ‘2016-03-29’
时间频率间隔
复杂类型 ARRAY 有序的的同类型的集合 array(1,2)
MAP key-value,key必须为原始类型, value可以任意类型 map(‘a’,1,’b’,2)
STRUCT 字段集合,类型可以不同 struct(‘1’,1,1.0), named_stract(‘col1’,’1’,’col2’,1, ‘clo3’,1.0)
UNION 在有限取值范围内的一个值 create_union(1,’a’ ,63)

Hive中可以创建的表有好几种类型， 分别是：内部表、外部表、分区表、分桶表

内部表（CREATE TABLE table_name ……）

  • 未被external关键字修饰的即是内部表， 即普通表。 内部表又称管理表,内部表数据存储的位置由hive.metastore.warehouse.dir参数决定（默认：/user/hive/warehouse），删除内部表会直接删除元数据（metadata）及存储数据，因此内部表不适合和其他工具共享数据。

外部表（CREATE EXTERNAL TABLE table_name ……LOCATION……）

  • 被external关键字修饰的即是外部表， 即关联表。
  • 外部表是指表数据可以在任何位置，通过LOCATION关键字指定。 数据存储的不同也代表了这个表在理念是并不是Hive内部管理的，而是可以随意临时链接到外部数据上的。
  • 在删除外部表的时候， 仅仅是删除元数据（表的信息），不会删除数据本身。
创建 存储位置 删除数据 理念
内部表 CREATE TABLE …… Hive管理，默认/user/hive/warehouse 删除 元数据（表信息）删除 数据 Hive管理表持久使用
外部表 CREATE EXTERNAL TABLE …… 随意，LOCATION关键字指定 仅删除 元数据（表信息）保留 数据 临时链接外部数据用

数据分隔符

  • 默认是特殊字符: ‘\001’ （^A soh)
  • 可以通过row format delimited fields terminated by在创建表的时候修改

其它创建内部表的形式

1
2
3
4
5
6
7
8
-- CREATE TABLE table_name as，基于查询结果建表
create table stu3 as select * from stu2;

-- CREATE TABLE table_name like，基于已存在的表结构建表
create table stu4 like stu2;

-- DESC FORMATTED table_name，查看表类型和详情
DESC FORMATTED stu2;

外部表的创建

  • 可以先有表，然后把数据移动到表指定的LOCATION中
  • 也可以先有数据，然后创建表通过LOCATION指向数据
1
2
3
4
5
6
7
8
9
10
-- 首先检查：hdfs dfs -ls /tmp，确认不存在/tmp/test_ext1目录
create external table test_ext1(
	id int, 
	name string
) 
	row format delimited fields terminated by '\t' 	-- 分隔符
	location '/tmp/test_ext1';						-- 可以看到，目录/tmp/test_ext1被创建
-- 上传数据： hadoop fs -put test_external.txt /tmp/test_ext1/
-- select * from test_ext1; 即可看到数据结果
-- 先存在数据，后创建外部表，同理

内部表外部表转换

1
2
talbename set tblproperties('EXTERNAL'='TRUE') 	-- 注意大写
talbename set tblproperties('EXTERNAL'='FALSE')

3、数据的导入与导出

1)数据加载

1、使用 LOAD 语法，从外部将数据加载到Hive内（不会走MapReduce 小文件速度快）

1
2
--加载数据				   数据路径  						 被加载的内部表
LOAD DATA [LOCAL] INPATH 'filepath' [OVERWRITE] INTO TABLE tablename;
  • 使用local，数据不在HDFS，可以使用file://协议指定路径
  • 不使用local，数据在HDFS，可以使用HDFS://协议指定路径
  • 加 OVERWRITE 代表覆盖已有数据

注意：基于HDFS进行load加载数据，源数据文件会消失（本质是被移动到表所在的目录中）

2、可以通过SQL语句，从其它表中加载数据 

1
2
3
4
5
6
--		是否覆盖
INSERT [OVERWRITE | INTO] TABLE tablename1 [PARTITION (partcol1=val1, partcol2=val2 ...) [IF NOT EXISTS]] select_statement1 FROM from_statement;

--示例
INSERT INTO TABLE tbl1 SELECT * FROM tbl2;
INSERT OVERWRITE TABLE tbl1 SELECT * FROM tbl2;

注意：走MapReduct少了数据时不如直接导入

2)数据导出

1、将查询的结果导出

1
2
3
4
5
6
7
8
-- 将查询的结果导出到本地 - 使用默认列分隔符
insert overwrite local directory '/home/hadoop/export1' select * from test_load;

-- 将查询的结果导出到本地 - 指定列分隔符
insert overwrite local directory '/home/hadoop/export2' row format delimited fields terminated by '\t' select * from test_load;

-- 将查询的结果导出到HDFS上(不带local关键字)
insert overwrite directory '/tmp/export' row format delimited fields terminated by '\t' select * from test_load;

2、hive表数据导出 - hive shell

1
2
3
4
-- 基本语法：（hive -f/-e 执行语句或者脚本 > file）
hive -e "select * from myhive.test_load;" > /home/hadoop/export3/export4.txt

hive -f export.sql > /home/hadoop/export4/export4.txt

4、分区表

可以选择字段作为表分区
分区其实就是HDFS上的不同文件夹
分区表可以极大的提高特定场景下Hive的操作性能
分区表的分区列，在partitioned by 中定义，不在普通列中定义

            左侧单分区表																		右侧多分区表(三级分区)

分区表的使用

1、创建分区表，加载数据

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
-- 基本语法
create table tablename(...) partitioned by (分区列 列类型, ......) 
row format delimited fields terminated by '';

-- 创建分区表 按照月分区
create table score(id string, cid string, score int) partitioned by (month string)
row format delimited fields terminated by '\t';

-- 向202006分区插入数据
load data local inpath '/root/data/score.txt' into table score partition(month = '202006');

-- 多级分区
create table score2(id string, cid string, score int) partitioned by (year string, month string, day string)
row format delimited fields terminated by '\t';

-- 多级分区加载数据
load data local inpath '/root/data/score.txt' into table score2 partition(year="2024", month="05", day="20");

2、查看，修改，删除分区

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
-- 查看分区
show partitions score;

-- 添加一个分区
alter table score add partition(month='202007');
-- 同时添加多个分区
alter table score add partition(month='202303') partition(month='202407');

-- 修改分区值
ALTER TABLE tablename PARTITION (month='202005') RENAME TO PARTITION (month='201105');

-- 删除分区
alter table score drop partition(month='202006');

-- 修复表，如果数据已按分区目录导入hdfs，可以修复表一键导入分区
msck repair table score;

其余属性可参见 LanguageManual DDL - Apache Hive - Apache Software Foundation

5、分桶表

分桶和分区一样，也是一种通过改变表的存储模式，从而完成对表优化的一种调优方式
但和分区不同，分区是将表拆分到不同的子文件夹中进行存储，而分桶是将表拆分到固定数量的不同文件中进行存储。

在这里插入图片描述

分桶表创建

1
2
3
4
5
6
-- 开启分桶的自动优化（自动匹配reduce task数量和桶数量一致）
set hive.enforce.bucketing=true;
-- 创建分桶表
create table course (c_id string,c_name string,t_id string) 
	clustered by(c_id) into 3 buckets 		-- 设置 分桶列c_id, 分三个桶
	row format delimited fields terminated by '\t';

分桶表加载数据

桶表的数据加载，由于桶表的数据加载通过load data无法执行，只能通过insert select.

1
2
3
4
5
6
7
8
9
-- 1. 创建一个临时表中转表（外部表或内部表均可），通过load data加载数据进入表
create table course_common(c_id string, c_name string, t_id string)
	row format delimited fields terminated by '\t';

-- 普通表中加载数据
load data local inpath '/root/data/course.txt' into table course_common;

-- 通过insert overwrite 给桶中加载数据
insert overwrite table course select * from course_common cluster by(c_id);

为什么不可以用load data，必须用insert select插入数据

  • 一旦有了分桶设置，比如分桶数量为3，那么，表内文件或分区内数据文件的数量就限定为3
  • 当数据插入的时候，需要一分为3，进入三个桶文件内。
  • 数据的三份划分基于分桶列的值进行Hash取模来决定
  • 由于load data不会触发MapReduce，也就是没有计算过程（无法执行Hash算法），只是简单的移动数据而已
    所以无法用于分桶表数据插入。

什么是Hash取模？

  • 基于Hash算法，对值进行计算，同一个值得到同样的结果
  • 对结果进行取模（除以桶数量得到余数），确认当前数据应该去哪一个桶文件
  • 同样key（分桶列的值）的数据，会在同一个桶中。

分桶表的性能提升

分区表的性能提升是：在指定分区列的前提下，减少被操作的数据量，从而提升性能。
分桶表的性能提升就是：基于分桶列的特定操作，如：过滤、JOIN、分组，均可带来性能提升。

  • 基于分桶列，过滤单个值：根据Hash值
  • 基于分桶列，进行双表JOIN：桶文件对桶文件
  • 基于分桶列，group by 分组：自动归并为各个组了

6、复杂类型操作

array类型

  1. array类型，主要存储：数组格式
    保存一堆同类型的元素，如：1, 2, 3, 4, 5

  2. 定义格式：
    array<类型>
    数组元素之间的分隔符：collection items terminated by ‘分隔符’

  3. 在查询中使用
    数组[数字序号]，可以取出指定需要元素（从0开始）
    size(数组)，可以统计数组元素个数
    array_contains(数组, 数据)，可以查看指定数据是否在数组中存在

示例数据 和 代码

1
2
zhangsan	beijing,shanghai,tianjin,hangzhou
wangwu	changchun,chengdu,wuhan,beijin
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
create table myhive.test_array(name string, work_locations array<string>)
    row format delimited fields terminated by '\t'	-- 表示列分隔符是\t
    COLLECTION ITEMS TERMINATED BY ',';				-- 表示集合（array）元素的分隔符是逗号

-- 序号从0开始，取出第一位
select name, work_locations[0] from test_array;

-- 查询array类型中的元素个数
select name, size(work_locations) location from myhive.test_array;

-- 查询location数组中包含tianjin的信息
select * from test_array where array_contains(work_locations, 'tianjin');

-- 加载上述数据
load data local inpath '/root/data/data_for_array_type.txt' into table test_array;

map类型

  1. map类型，主要存储：K-V键值对类型数据
    保存一堆同类型的键值对，如：“a”:1, “b”: 2, “c”: 3
  2. 定义格式：
    map<key类型, value类型>
    不同键值对之间：COLLECTION ITEMS TERMINATED BY ‘分隔符’ 分隔
    一个键值对内，使用： MAP KEYS TERMINATED BY ‘分隔符’ 分隔K-V
    如：father:xiaoming#mother:xiaohuang#brother:xiaoxu
    不同KV之间使用#分隔，同一个KV内用:分隔K和V
  3. 在查询中使用
    map[key]来获取指定key的值
    map_keys(map)取到全部的key作为array返回，map_values(map)取到全部values
    size(map)可以统计K-V对的个数
    array_contains(map_values(map), 数据) 可以统计map是否包含指定数据

示例数据 和 代码

1
2
3
4
1,林杰均,father:林大明#mother:小甜甜#brother:小甜,28
2,周杰伦,father:马小云#mother:黄大奕#brother:小天,22
3,王葱,father:王林#mother:如花#sister:潇潇,29
4,马大云,father:周街轮#mother:美美,26
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
create table test_map(
    id int, name string, member map<string, string>, age int
)
row format delimited
fields terminated by ','
collection items terminated by '#'   -- 每个键值对间的分隔符
MAP KEYS TERMINATED BY ':';          -- 单个键值对内部，键和值的分隔符

alter table test_map change member members map<string, string>;

load data local inpath '/root/data/data_for_map_type.txt' into table test_map;

select * from test_map;

-- 查看成员中，每个成员的父亲,母亲是谁
select id, name, members['father'], members['mother'] from test_map;

-- 取出map中全部key，返回类型为array
select map_keys(members) from test_map;

-- size 查看map的元素 (K-V对)的个数
select size(members) from test_map;

-- array_contains 去查看指定的数据是否包含在map中，看看谁有王林这个value
select * from test_map where array_contains(map_values(members), '王林');

struct类型

  1. struct类型，主要存储：复合格式
    可以包含多个二级列，二级列支持列名和类型，如
    “a”: 1, “b”: “foo”, “c”: “2000-01-01”
  2. 定义格式：
    struct<name:string, age:int>
    struct的分隔符只需要：COLLECTION ITEMS TERMINATED BY ‘分隔符’ 只需要分隔数据即可（数据中不记录key，key是建表定义的固定的）
  3. 在查询中使用
    struct.key 即可取得对应的value

示例数据 和 代码

1
2
3
4
5
1#周杰轮:11
2#林均杰:16
3#刘德滑:21
4#张学油:26
5#蔡依临:23
1
2
3
4
5
6
7
8
9
10
11
12
13
14
create table myhive.test_struct(
    id int,
    info struct<name: string, age:int>
)
row format delimited
fields terminated by '#'
collection items terminated by ':';      -- 表示struct中二级列的数据之间的分割符

load data local inpath '/root/data/data_for_struct_type.txt' into table test_struct;

select * from test_struct;

-- 查询内容
select id, info.name, info.age from test_struct;

7、数据抽样

大数据体系下，表内容一般偏大，小操作也要很久，所以如果想要简单看看数据，可以通过抽样快速查看

  • 桶抽样方式，TABLESAMPLE(BUCKET x OUT OF y ON(colname | rand()))，推荐，完全随机，速度略慢块抽样，使用分桶表可以加速

    1
    2
    3
    4
    5
    -- 10个抽3份 orderId Hash取模, 其他条件不变的话运行结果一致
    select * from orders tablesample (bucket 3 out of 10 on orderId);

    -- 完全随机，分10个桶随机抽3个
    select * from orders tablesample (bucket 3 out of 10 on rand());

  • 块抽样方式，TABLESAMPLE(num ROWS | num PERCENT | num(K|M|G))，速度快于桶抽样方式，但不随机，只是按照数据顺序从前向后取。

    1
    2
    3
    select * from orders tablesample (100 rows);	-- 抽取前100条
    select * from orders tablesample (10 percent);	-- 抽取前10%
    select * from orders tablesample (1K);			-- 抽取前1KB

8、Virtual Columns 虚拟列

虚拟列是Hive内置的可以在查询语句中使用的特殊标记，可以查询数据本身的详细参数。

1
2
3
4
5
6
7
8
9
10
-- 虚拟列
-- INPUT__FILE__NAME            -- 显示数据行所在的具体文件
-- BLOCK__OFFSET__INSIDE__FILE  -- 显示数据行所在的偏移量
-- ROW__OFFSET__INSIDE__BLOCK   -- 显示数据所在HDFS块的偏移量, 设置 set hive.exec.rowoffset=true 才可使用
set hive.exec.rowoffset=true;

select orderId, userName, INPUT__FILE__NAME, BLOCK__OFFSET__INSIDE__FILE, ROW__OFFSET__INSIDE__BLOCK from orders;

-- (分区 分桶 表) 查看每个文件有多少条数据
select INPUT__FILE__NAME, count(*) FROM orders GROUP BY INPUT__FILE__NAME;

虚拟列的作用

  • 查看行级别的数据详细参数
  • 可以用于WHERE、GROUP BY等各类统计计算中
  • 可以协助进行错误排查工作

9、Hive函数

Hive的函数分为两大类：内置函数（Built-in Functions）、用户定义函数UDF（User-Defined Functions）：

在这里插入图片描述

具体可查看：官方文档

1
2
show functions;
describe function extended funcname;	-- 来查看函数的使用方式。
大数据 > hive
#bigData #hive #sql
Hive分布式SQL计算平台
https://leaf-domain.gitee.io/2024/08/04/bigdata/hive/hive01/
作者
叶域
发布于
2024年8月4日
许可协议