文章简介
-由于hive的基本操作与MySQL大致相同,因此本文不对hive的基本操作进行讲解,而只从hive与MySQL不相同的点出发,对hive进行学习
1 hive简介
1.1 本质
- hive是一个Hadoop的一个数据仓库工具,也是Hadoop的客户端,其目的是将sql语句转化为mapreduce程序。hive本质上只是一个工具,是为了将数据库能够分布的实现,也就是mapreduce组合起来,这就要求hive的每张表都需要储存在hdfs上,底层实现是mapreduce,也可以是spark,任务运行在yarn上。
- 统筹多个数据库,将sql语句转化为mapreduce的工具
1.2 架构原理
- 如图所示为hive的架构,其有client、drive、metastore组成,附属在Hadoop上。
- client:客户端,用于提交sql语言,可以看出client在hive内部,也就是说客户端得附属有hive才能提交任务。
- hiveServer2:hiveServer2用于为客户端提供jdbc/odbd接口,使得远程用户可以访问hive数据
- metastore:记录着各个表在hdfs上的位置,也就是元数据。由于hive操作的表是在hdfs上的,hive就必须知道各个表的具体位置,这个任务由metastore来完成。
- driver:hive的工作程序,真正将sql语句转化为mapreduce程序,其分为7步进行:
- sql parser:sql语句解析器,也可以看成接口
- 语义分析器:进行语句分析和语义分析,分析通过后生成语义树
- 逻辑计划生成器:将传入的语义树转化为逻辑计算语句。
- 逻辑计划优化器:对逻辑计划进行优化。(若是MySQL的话,到这里就可以转化为执行计划,然后传到引擎上进行运行了,hive则还要继续将其转化为mapreduce)
- 物理计划生成器:将sql的逻辑计划转化为mapreduce程序。
- 物理计划优化器:对mapreduce进行优化,返回执行计划
- 执行器:将执行计划提交到yarn上去。
- Hadoop:hive的附属对象,hive的操作表在Hadoop的hdfs上,运行地点为mapreduce yarn。
3 DDL语句
3.1 数据库
3.1.1 创建数据库
-
语法
create database 库名 location 'hdfs_path' with dbproperties (属性=值) #为数据库指定一定的属性
-
例子
hive (default)> create database db_hive1 location '/db_hive1' with dbproperties('create_date'='2023-04-14'); hive (default)> desc database extended db_hive1; OK db_name comment location owner_name owner_type parameters db_hive1 hdfs://node1:9000/db_hive1 root USER {create_date=2023-04-14}
3.1.2 删除数据库
-
语法
DROP DATABASE [IF EXISTS] database_name [restrict | cascade] restrict:严格模式,只有数据库中没有数据才会被删除,默认q cascade:级联模式,会连库带表一起删除
3.1.3 修改数据库
-
用户可以使用 alter database 命令修改数据库某些信息,其中能够修改的信息包括 dbproperties、location、owner user。需要注意的是:修改数据库 location,不会改变当前 已有表的路径信息,而只是改变后续创建的新表的默认的父目录。
-
语法
alter database 库名set [dbproperties(...)\ location hdfs_path \ owner user user_name]
3.2 表
3.2.1 普通建表
00) 语法
create [temporary] [external] table [if not exists]
[库名.]表名
[(列名 数据类型 [comment col_comment], ....)]
[comment table_comment] # 约束
[partitioned by ...] # 分区表
[clustered by ...] # 分桶表
[sorted by ...] # 排序
[row format ...] # 每一行的序列化情况,每一行的解析情况
[stored as ...] # 文件的解析情况
[location hdfs_path] # 表的储存位置
[tblproperties ...] # 表的描述
01) temporary
- 临时表,当前会话可见,会话结束后,表会被删除。
02) external(重点)
-
外部表,与之对应的是内部表(管理表)。管理表以为着hive会全权接管这个表,包括元数据和hdfs数据,而外部表表示hive只会管理元数据。
两者的区别体现在删除时,管理表会将元数据和hdfs中的数据全部删除,而外部表只会删除元数据。
- 外部表的建立比较灵活,当后台已经存在有表了,可以直接创建一个外部表来使用他,相当于创建一个链接。
03) data_type
- 数据类型,可以分为复杂类型和简单类型
类型 | 说明 | 定义 |
---|---|---|
int | 4byte有符号整数 | |
bigint | 8byte有符号整数 | |
double | 双精度 | |
decimal | 十进制精准数字类型 | decimal(16,2)表示14个整数2个小数 |
varchar | 字符序列,需指定长度 | varchar(32) |
string | 字符串,无需指定长度 | |
array | 数组,类型的集合 | array<string> |
map | 键值对组合 | map<string, int> |
stuct | 结构体 | struct<id:int, name:string> |
结构体:实际上是map的进阶版,map中的value只能由一种类型,而struct中的value可以有多种类型
- 类型转化:显示转化如下:
cast(值 as <type>)
04) row format(重点)
-
指定,每一行数据的序列化和反序列化形式(serde),即解析每一行的格式
-
在hive中,没有行数据的读写情况如下:
读数据:HDFS files --> InputFileFormat --> <key, value> --> Deserializer --> Row object
写数据:Row object --> Serializer --> <key, value> --> OutputFileFormat --> HDFS files
对于读数据,由hdfs数据inputfileformat成键值对,然后反序列化称为java的row数据类型。写数据则反之,可见重点在于序列化。
-
语法
row format delimited # 关键字 [fields terminated by char] # 指定每一列的分隔符号 [collection items terminated by char] # map\struct\array中每一个元素的分隔符号 [map keys terminated by char] # map中key和value的分隔符 [lines terminated by char] # 行分割符 [null defined as char] # null值的定义,默认为 \N
05) stored by(重点)
-
指定文件的存储格式,也指文件解析格式,默认为textfile, 还有sequence file, orc file qarquetfile等等。
-
例子:查看默认储存文件的行解析格式和列解析格式
hive (default)> show create table stu; createtab_stmt CREATE TABLE `stu`( `id` int, `name` string) ROW FORMAT SERDE # row format情况,即行解析情况 'org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe' STORED AS INPUTFORMAT # stored as 的情况,文件解析情况 'org.apache.hadoop.mapred.TextInputFormat' OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat' LOCATION 'hdfs://node1:9000/user/hive/warehouse/stu' TBLPROPERTIES ( 'bucketing_version'='2', 'transient_lastDdlTime'='1681356658') Time taken: 1.242 seconds, Fetched: 14 row(s)
06) partitoned by(重点)
- 分区表,见后文5.1
07) clutered by … sorted by … into … buckets(重点)
- 分桶表,见后文5.2
3.2.2 CTAS 建表
-
利用select 的返回语句进行建表,与普通建表的区别为,不能建立外表
-
语法
create [temporary] table [if not exists] [库名.]表名 [(列名 数据类型 [comment col_comment], ....)] [comment table_comment] # 约束 [row format ...] # 每一行的序列化情况,每一行的解析情况 [stored as ...] # 文件的解析情况 [location hdfs_path] # 表的储存位置 [tblproperties ...] # 表的描述 [as select_statement]
3.2.3 例子
-
serde解析使用和复杂数据类型
-
若有下方json数据类型,其是格式化后的数据,在文件中应该为一行
{ "name": "dasongsong", "friends": [ "bingbing", "lili" ], "students": { "xiaohaihai": 18, "xiaoyangyang": 16 }, "address": { "street": "hui long guan", "city": "beijing", "postal_code": 10010 } }
-
面对这个数据,我们可以利用hive中自带专门处理json数据的序列化,json serde,然后根据数据情况设计字段。相应字段应该数据保持一致
create table teacher ( name string, friends array<string>, # 一级字段需一致 students map<string,int>, address struct<city:string,street:string,postal_code:int> # 二级字段也一致 ) row format serde 'org.apache.hadoop.hive.serde2.JsonSerDe' location '/user/hive/warehouse/teacher';
4 DML语句
4.1 select from
-
基本语法
select 字段 from 表名 join where .. group by .. having ... order by ... cluster by 字段 distribute by 字段 sort by 字段
4.2 四种排序
4.2.1 order by
- order by:全局排序,放在select后面,表示对查询结果按某个字段排序,和sql中的一致。
4.2.2 sort by
-
sort by:局部排序,每一个reduce内部有序
-
hive中的数据是会通过mr操作的,所以在mr之间会有shuffle的过程,shuffle在合并map结果和合并分区的时候,会根据一个字段进行排序,默认情况下为主键,sort by 用于指定这个字段,使结果使reduce内部有序,而全局不是有序的,但是效率快。order by是将多个reduce聚集在一起然后在排序,因此使全局有序,但是效率慢。
-
但我们需要全局有序的全体记录时,order by是必不可少的,但如果我们需要是排序的前几个记录时,利用sort by + limit n的操作时最有效的!
例子:
如下图语句,在reduce中预先排序后,只需要从每个reduce中取前10个进行排序即可,比起order by快了很多
# 取工资的前10名。 select * from employees sort by salary limit 10;
4.2.3 distribute by
- distribute by相当于mr中的partition(自定义分区),在map到reduce中有shuffle,shuffle需要根据某个字段进行分区,distribute by可以用于指定这个分区。
- 当我们需要将某些特定的行指定到某个reduce中去的时候就可以使用这个关键字。
4.2.4 cluster by
- 当distribute by 和 sort by的字段一致时,可以省略为cluster by
- order by、sort by、distribute by作用位置如图所示
5 分区与分桶
5.1 分区表
5.1.1 定义与创建
- 一般而言,一张表的数据应该在同一个路径下,但当一个表太大时,放在一个文件中显得有点大,因此将同一张表按字段分为多个区,每一个区在不同目录下,这就是分区表。
- 分区表是物理上的分区,真正的把一个表内部分为了多个区,就像放文件夹一样,同意类型的放在同一文件夹中。在逻辑上仍然是同一个表。
- 分区表的有点:
- 查询时,可以减少io的次数,提高效率
- 便于管理
创建分区表:
create table [external] dept_partition # 创建表 depr_partition
(
列名,
deptno int, --部门编号
dname string, --部门名称
loc string --部门位置
)
partitioned by (day string) # 指定分区字段
row format delimited fields terminated by '\t' # 行解析情况
- external外部表同样对分区表适用。
- 由创建分区表的语句可以看出,在创建表时,列名不需要包括建表字段!!!
5.1.2 读写数据
01) 写数据
-
load
- 准备数据
node1>>& vim dept_20220401.log 10 行政部 1700 20 财务部 1800 # 不包含分区字段
- 上传数据
load data local inpath './datas/dept_20220401.log' into table dept_partition partition(day = '20230416') !!!!
注意:
由上传的数据可以看出,行数据中是不包含分区字段的,但是需要load阶段指定上传数据的partition。
-
insert
insert [overwrite] table 表名 partition(分区字段) ... # 例子 insert overwrite table dept_partition partition(day = '20230417') # 指定分区 select * from dept_partition
注意:insert 插入的时候,行数据也不需要包含分区字段,但需要再insert后面指定上传到的partition
02) 读数据
-
读数据时,需要选择分区字段也会字段输出
0: jdbc:hive2://node1:10000> select * from dept_partition; # 会字段显示分区字段 +-----------------------+-----------------------+---------------------+---------------------+ | dept_partition.depto | dept_partition.dname | dept_partition.loc | dept_partition.day | +-----------------------+-----------------------+---------------------+---------------------+ | 10 | 行政部 | 1700 | 200111 | | 20 | 财务部 | 1800 | 200111 | +-----------------------+-----------------------+---------------------+---------------------+ 2 rows selected (1.782 seconds)
5.1.3 分区操作
01) 查看分区
hive> show partitions dept_partition
02) 增加分区
- 单个分区
hive (default)>
alter table dept_partition
add partition(day='20220403');
- 多个分区,分区之间不需要有逗号
hive (default)>
alter table dept_partition
add partition(day='20220404') partition(day='20220405');
03) 删除分区
# 单个分区
hive (default)>
alter table dept_partition
drop partition(day='20220403');
# 多个分区之间需要有逗号
hive (default)>
alter table dept_partition
drop partition(day='20220404'), partition(day='20220405');
04) 分区修复
-
分区修复指:当元数据记录信息和hdfs中信息不一致时,需要修改元数据形式使两者一致。
注意!修改的是元数据,因为hdfs不归hive管。
-
当元数据和hdfs路径不一致时,hive访问不到hdfs对应数据,元数据库中的数据映射的是hdfs,因此需要保持一致
-
语法
hive (default)> msck repair table table_name [add/drop/sync partitions];
- msck repair table table_name add partitions:该命令会增加HDFS路径存在但元数据缺失的分区信息。
- msck repair table table_name drop partitions:该命令会删除HDFS路径已经删除但元数据仍然存在的分区信息。
- msck repair table table_name sync partitions:该命令会同步HDFS路径和元数据分区信息,相当于同时执行上述的两个命令。
5.1.3 二级分区
-
建表
hive (default)> create table dept_partition2( deptno int, -- 部门编号 dname string, -- 部门名称 loc string -- 部门位置 ) partitioned by (day string, hour string) # 指定两个字段 row format delimited fields terminated by '\t';
-
插入
load data local inpath '/opt/module/hive/datas/dept_20220401.log' into table dept_partition2 partition(day='20220401', hour='12'); # 指定两个字段
5.1.4 动态分区
- 动态分区是指向分区表insert数据时,被写往的分区不由用户指定,而是由每行数据的最后一个字段的值来动态的决定。使用动态分区,可只用一个insert语句将数据写入多个分区。
01) 相关参数设置
(1)动态分区功能总开关(默认true,开启)
set hive.exec.dynamic.partition=true
(2)严格模式和非严格模式
动态分区的模式,默认strict(严格模式),要求必须指定至少一个分区为静态分区,nonstrict(非严格模式)允许所有的分区字段都使用动态分区。
set hive.exec.dynamic.partition.mode=nonstrict
(3)一条insert语句可同时创建的最大的分区个数,默认为1000。
set hive.exec.max.dynamic.partitions=1000
(4)单个Mapper或者Reducer可同时创建的最大的分区个数,默认为100。
set hive.exec.max.dynamic.partitions.pernode=100
(5)一条insert语句可以创建的最大的文件个数,默认100000。
hive.exec.max.created.files=100000
(6)当查询结果为空时且进行动态分区时,是否抛出异常,默认false。
hive.error.on.empty.partition=false
- 其中第4条:单个Mapper或者Reducer可同时创建的最大的分区个数,因为hive是mr过来的,因此最后储存到分区也是由reduce或map完成的,因此可以指定其能产生的最大分区。
02) 例子
-
建表:指定以loc 为分区字段
hive (default)> create table dept_partition_dynamic( id int, name string ) partitioned by (loc int) row format delimited fields terminated by '\t';
-
插入语句:
set hive.exec.dynamic.partition.mode = nonstrict; hive (default)> insert into table dept_partition_dynamic partition(loc) select deptno, dname, loc # !!!!最后一个为分区字段 from dept;
-
动态分区和普通分区插入的差别在于:动态分区需要在数据的最后一列添加分区字段,相反普通分区不用。
5.2 分桶表
5.2.1 定义与创建
- 分桶表和分区表有相似,都是将同一个文件按照某个字段分为多个文件,其中分区表是按照字段的值进行分区,有几个值分为几个区。而分桶表是按字段的hash值进行分桶,一般需要先指定分为几个桶。
- 分区表可以看成特殊的分桶表,其分桶的个数 = 字段值的个数。
语法:
hive (default)>
create table stu_buck(
id int,
name string
)
clustered by(id) # 分桶字段
into 4 buckets # 指定分桶个数
row format delimited fields terminated by '\t';
5.2.2 插入数据
- 分桶表的插入数据是直接插入就行。行数据中有包括分桶的字段。分区表的插入一般不包含分区字段,因为分区字段是已知的了。
5.2.3 分桶排序表
- 分桶完根据某一个字段进行排序,这就是分桶排序表,order by
hive (default)>
create table stu_buck_sort(
id int,
name string
)
clustered by(id)
sorted by(id) # 排序字段
into 4 buckets
row format delimited fields terminated by '\t';
文章参考B站尚硅谷视频