一篇文章了解hive【hive与MySQL的不同点】_hive mysql

文章简介

-由于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步进行：
  1. sql parser：sql语句解析器，也可以看成接口
  2. 语义分析器：进行语句分析和语义分析，分析通过后生成语义树
  3. 逻辑计划生成器：将传入的语义树转化为逻辑计算语句。
  4. 逻辑计划优化器：对逻辑计划进行优化。（若是MySQL的话，到这里就可以转化为执行计划，然后传到引擎上进行运行了，hive则还要继续将其转化为mapreduce）
  5. 物理计划生成器：将sql的逻辑计划转化为mapreduce程序。
  6. 物理计划优化器：对mapreduce进行优化，返回执行计划
  7. 执行器：将执行计划提交到yarn上去。
• Hadoop：hive的附属对象，hive的操作表在Hadoop的hdfs上，运行地点为mapreduce yarn。

3 DDL语句

3.1 数据库

3.1.1 创建数据库

• 语法

  create database 库名
  location 'hdfs_path'
  with dbproperties (属性=值)	#为数据库指定一定的属性 
  1
  2
  3

• 例子

  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}
  1
  2
  3
  4
  5

3.1.2 删除数据库

• 语法

  DROP DATABASE [IF EXISTS] database_name [restrict | cascade]
  restrict:严格模式，只有数据库中没有数据才会被删除，默认q
  cascade:级联模式，会连库带表一起删除
  1
  2
  3

3.1.3 修改数据库

• 用户可以使用 alter database 命令修改数据库某些信息，其中能够修改的信息包括 dbproperties、location、owner user。需要注意的是：修改数据库 location，不会改变当前 已有表的路径信息，而只是改变后续创建的新表的默认的父目录。

• 语法

  alter database 库名set
  [dbproperties(...)\
  location hdfs_path \
  owner user user_name]
  1
  2
  3
  4

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 ...]		# 表的描述
1
2
3
4
5
6
7
8
9
10
11
12
13
14

01) temporary

• 临时表，当前会话可见，会话结束后，表会被删除。

02) external(重点)

• 外部表，与之对应的是内部表（管理表）。管理表以为着hive会全权接管这个表，包括元数据和hdfs数据，而外部表表示hive只会管理元数据。

  两者的区别体现在删除时，管理表会将元数据和hdfs中的数据全部删除，而外部表只会删除元数据。

  • 外部表的建立比较灵活，当后台已经存在有表了，可以直接创建一个外部表来使用他，相当于创建一个链接。

03) data_type

• 数据类型，可以分为复杂类型和简单类型

结构体：实际上是map的进阶版，map中的value只能由一种类型，而struct中的value可以有多种类型

• 类型转化：显示转化如下：

cast(值 as <type>)
1

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
  1
  2
  3
  4
  5
  6

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)
  1
  2
  3
  4
  5
  6
  7
  8
  9
  10
  11
  12
  13
  14
  15
  16
  17

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]
  1
  2
  3
  4
  5
  6
  7
  8
  9
  10
  11
  12

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
      }
  }
  1
  2
  3
  4
  5
  6
  7
  8
  9
  10
  11
  12
  13
  14
  15
  16

• 面对这个数据，我们可以利用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';
  1
  2
  3
  4
  5
  6
  7
  8
  9

4 DML语句

4.1 select from

• 基本语法

  select 字段 from 表名
  join 
  where ..
  group by ..
  having  ...
  order by ...
  cluster by 字段
  distribute by 字段
  sort by 字段
  1
  2
  3
  4
  5
  6
  7
  8
  9

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;
  1
  2
  3
  4

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'	# 行解析情况
1
2
3
4
5
6
7
8
9

• external外部表同样对分区表适用。
• 由创建分区表的语句可以看出，在创建表时，列名不需要包括建表字段！！！

5.1.2 读写数据

01) 写数据

1. load

   • 准备数据

   node1>>& vim dept_20220401.log
   10 行政部 1700
   20 财务部 1800	# 不包含分区字段
   1
   2
   3

   • 上传数据

   load data local inpath './datas/dept_20220401.log'
   into table dept_partition
   partition(day = '20230416') ！！！！
   1
   2
   3

   注意：

   由上传的数据可以看出，行数据中是不包含分区字段的，但是需要load阶段指定上传数据的partition。

2. insert

   insert [overwrite] table 表名 partition(分区字段)
   ...
   
   # 例子
   insert overwrite table dept_partition partition(day = '20230417')		# 指定分区
   select * from dept_partition
   1
   2
   3
   4
   5
   6

   注意：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)
  
  1
  2
  3
  4
  5
  6
  7
  8
  9
  10
  11

5.1.3 分区操作

01) 查看分区

hive> show partitions dept_partition
1

02) 增加分区

• 单个分区

hive (default)>
alter table dept_partition
add partition(day='20220403');
1
2
3

• 多个分区，分区之间不需要有逗号

hive (default)>
alter table dept_partition
add partition(day='20220404') partition(day='20220405');
1
2
3

03) 删除分区

# 单个分区
hive (default)>
alter table dept_partition
drop partition(day='20220403');

# 多个分区之间需要有逗号
hive (default)>
alter table dept_partition
drop partition(day='20220404'), partition(day='20220405');
1
2
3
4
5
6
7
8
9

04) 分区修复

• 分区修复指：当元数据记录信息和hdfs中信息不一致时，需要修改元数据形式使两者一致。

  注意！修改的是元数据，因为hdfs不归hive管。

• 当元数据和hdfs路径不一致时，hive访问不到hdfs对应数据，元数据库中的数据映射的是hdfs，因此需要保持一致

• 语法

  hive (default)>
  msck repair table table_name [add/drop/sync partitions];
  1
  2

  • 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 二级分区

1. 建表

   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';
   1
   2
   3
   4
   5
   6
   7
   8

2. 插入

   load data local inpath '/opt/module/hive/datas/dept_20220401.log' 
   into table dept_partition2 
   partition(day='20220401', hour='12'); # 指定两个字段
   1
   2
   3

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
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

• 其中第4条：单个Mapper或者Reducer可同时创建的最大的分区个数，因为hive是mr过来的，因此最后储存到分区也是由reduce或map完成的，因此可以指定其能产生的最大分区。

02) 例子

1. 建表：指定以loc 为分区字段

   hive (default)> 
   create table dept_partition_dynamic(
       id int, 
       name string
   ) 
   partitioned by (loc int) 
   row format delimited fields terminated by '\t';
   1
   2
   3
   4
   5
   6
   7

2. 插入语句：

   set hive.exec.dynamic.partition.mode = nonstrict;
   hive (default)> 
   insert into table dept_partition_dynamic 
   partition(loc) 
   select 
       deptno, 
       dname, 
       loc 		# ！！！！最后一个为分区字段
   from dept;
   1
   2
   3
   4
   5
   6
   7
   8
   9

3. 动态分区和普通分区插入的差别在于：动态分区需要在数据的最后一列添加分区字段，相反普通分区不用。

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';
1
2
3
4
5
6
7
8

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';
1
2
3
4
5
6
7
8
9

文章参考B站尚硅谷视频