Hive05：Hive的进阶操作01之数据库和表的操作、数据类型

置顶做一个有趣的人Zz

已于 2022-03-05 15:41:48 修改

阅读量1.3k

点赞数

文章标签： hadoop hive

于 2022-03-05 15:41:40 首次发布

本文链接：https://blog.csdn.net/weixin_40612128/article/details/123251083

版权

本文深入探讨了Hive的数据库操作，包括查看、选择、创建和删除数据库，以及Hive中表的操作，如创建、修改、加载数据等。详细解释了Hive的数据类型，包括基本数据类型和复合数据类型如Array、Map和Struct。通过实例展示了如何处理列和行分隔符，以及如何处理中文注释的乱码问题。

摘要由CSDN通过智能技术生成

一、Hive中数据库操作

1、查看数据库列表

show databases;

在这里插入图片描述

2、选择数据库

use default;

在这里插入图片描述

default是默认数据库，默认就在这个库里面
咱们前面说过hive的数据都是存储在hdfs上，那这里的default数据库在HDFS上是如何体现的？

在hive-site.xml中有一个参数hive.metastore.warehouse.dir

在这里插入图片描述
它的默认值是/user/hive/warehouse，表示hive的default默认数据库对应的hdfs存储目录。

那我们到HDFS上看一下，发现确实有这个目录，并且这个目录下还有一个t1目录，其实这个t1就是我们前面在default数据库中创建的那个t1表，从这可以看出来，hive中的数据库和hive中的表，在hdfs上面的体现其实都是目录。

在这里插入图片描述
这个默认数据库的信息在Metastore中也有记录，在dbs表中

在这里插入图片描述

3、创建数据库

下面我们来自己创建一个数据库，看看是什么样的

create database mydb1;

在这里插入图片描述
这个数据库在hdfs上的对应目录在哪呢？来看一下

在这里插入图片描述
到metastore中确认一下

如果你不希望创建的数据库在这个目录下面，想要手工指定，那也是可以的，在创建数据库的时候通过location来指定hdfs目录的位置

create database mydb2 location '/user/hive/mydb2';

在这里插入图片描述

到HDFS上确认一下
在这里插入图片描述
到metastore中确认一下

4、删除数据库

drop database mydb1;

在这里插入图片描述

注意：default默认数据库无法删除！

二、Hive中表的操作

1、创建表

create table t2(id int);

在这里插入图片描述

2、查看表信息

show tables;

在这里插入图片描述

3、查看表结构信息

desc t2;

在这里插入图片描述

4、查看表的创建信息

show create table t2;

在这里插入图片描述
从这里的location可以看到这个表在hdfs上的位置。

注意了：表中的数据是存储在hdfs中的，但是表的名称、字段信息是存储在metastore中的

到metastore中看一下
先看tbls表，这个里面中存储的都是在hive中创建的表
可以看到DB_ID 为1
可以到dbs表中看到默认default数据库的id就是1。
TBL_NAME 是这个表的名称。

在这里插入图片描述

在这里插入图片描述
在表COLUMNS_V2中存储的是Hive表的字段信息(包含字段注释、字段名称、字段类型、字段顺序)
其中的CD_ID和tbls中的TBL_ID相等。

在这里插入图片描述

5、修改表名

对表名进行重命名

alter table t2 rename to t2_bak;

在这里插入图片描述
hdfs中对应的目录名称也同步变化了

在这里插入图片描述

6、加载数据

咱们前面向表中添加数据是使用的insert命令，其实使用insert向表里面添加数据只是在测试的时候使用，实际中向表里面添加数据很少使用insert命令的

具体原因我们后面再分析，在这大家先带着这个问题。

向表中加载数据可以使用load命令
以t2_bak为例，在bigdata04机器的/data/soft/hivedata下有一个t2.data文件，将其加载到t2_bak表中

在这里插入图片描述

load data local inpath '/data/soft/hivedata/t2.data' into table t2_bak;

在这里插入图片描述

7、查看表中的内容

select * from t2_bak;

在这里插入图片描述
我们到hdfs上去看一下这个表，发现刚才的文件其实就是上传到了t2_bak目录中

在这里插入图片描述
那我们自己手工通过put命令把数据上传到t2_bak目录中可以吗？
可以的！

hdfs dfs -put /data/soft/hivedata/t2.data /user/hive/warehouse/t2_bak/t2_bak.data

在这里插入图片描述
到hdfs上确认一下，可以看到刚才上传的文件

在这里插入图片描述
再查询一下这个表的数据，可以发现数据多了一份，说明刚才使用hdfs的put命令上传的是可以的。

select * from t2_bak;

在这里插入图片描述

8、表增加字段、注释、删除表

在工作中会有给已存在的表增加字段的需求，需要使用alter命令。

在这里我们给t2_bak表增加一个name字段，重新查看表结构信息，再查询一下这个表中的数据，结果发现，第二列为null，这是正常的，因为我们的数据数据文件中就只有一列，第二列查询不到，就显示为null，不会报错，这一点要注意一下。

在这里插入图片描述
现在我们通过desc查询表中的字段信息发现都没有注释，所以想要给字段加一些注释，以及表本身也可以增加注释，都是使用comment关键字。

重新创建一个表t2
注意：在建表语句中，缩进不要使用tab制表符，否则拷贝到hive命令行下执行会提示语句错误，这里的缩进需要使用空格。

create table t2(
age int comment '年龄'
) comment '测试';

查看这个表的信息，结果发现我们添加的中文注释都是乱码

在这里插入图片描述

原因是什么？怎么破？

中文乱码的原因是因为hive数据库里面的表都是latin1编码的，中文本来就会显示乱码。

但是又不能修改整个数据库里面所有表的编码，否则在使用hive的时候会出问题，那么只有考虑把存储字段注释和表注释相关的表的编码改为utf8。

登陆Mysql数据库切换到Hive库：

mysql -uroot -padmin;
use hive;

在这里插入图片描述
确认一下现在表COLUMNS_V2和TABLE_PARAMS的编码，都是latin1

show create table COLUMNS_V2;
show create table TABLE_PARAMS;

在这里插入图片描述

修改这两张表的编码即可；

alter table COLUMNS_V2 modify column COMMENT varchar(256) character set utf8;
alter table TABLE_PARAMS modify column PARAM_VALUE varchar(4000) character set utf8;

在这里插入图片描述

如果你的表创建了分区的话就要再执行两条命令：

alter table PARTITION_PARAMS modify column PARAM_VALUE varchar(4000) character set utf8;
alter table PARTITION_KEYS modify column PKEY_COMMENT varchar(4000) character set utf8;

在这里插入图片描述

这样修改之后以后就可以看到中文注释了。

注意：需要先把之前创建的t2表删除掉，因为之前存储的中文已经是乱码了，无法恢复，删除之后重新创建就可以了。

删除表使用drop命令。
重新创建t2

drop table t2;

create table t2(
age int comment '年龄'
) comment '测试';

desc t2;

show create table t2;

在这里插入图片描述

9、指定列和行分隔符

在我们实际工作中，肯定不会像上面一样创建一张非常简单的表，实际中表的字段会比较多，下面我们就来创建一个多字段的表t3

create table t3(
id int comment 'ID',
stu_name string comment 'name',
stu_birthday date comment 'birthday',
online boolean comment 'is online'
);

这样创建没有问题，我们来加载对应的数据文件/data/soft/hivedata/t3.data，表中的多列内容之间是使用制表符分割的
看一下表中的数据，会不会有问题呢？

1       张三    2020-01-01      true
2       李四    2020-02-01      false
3       王五    2020-03-01      0

在这里插入图片描述
建表、加载数据、查询数据

create table t3(
id int comment 'ID',
stu_name string comment 'name',
stu_birthday date comment 'birthday',
online boolean comment 'is online'
);

load data local inpath '/data/soft/hivedata/t3.data' into table t3;

select * from t3;

在这里插入图片描述
在这里发现不是我们想要的结果，都是NULL，说明数据没有被识别，这是为什么？
注意了，hive在创建表的时候，需要我们指定相应的行分隔符，列分隔符。而我们在创建mysql表的时候，这些都是不需要的，因为它在组织数据的时候，已经规定好了数据的表现形式。
我们刚才在创建t3的时候没有指定相应的分隔符，所以导致使用制表符分割的数据无法被解析。
实际上呢，hive是有默认的分隔符的，默认的行分隔符是’\n’，就是换行符，而默认的列分隔符呢，是\001。
\001这个是ASCII码中的特殊不常使用的不可见字符，在文本中我们可以通过ctrl+v和ctrl+a来输入\001，这里我们在将t3.data改一下，重新上传，再查看表t3。

修改t3.data
在这里插入图片描述
重新加载数据，查询表数据，这个时候发现刚才修改的那条数据被成功解析了

load data local inpath '/data/soft/hivedata/t3.data' into table t3;

select * from t3;

在这里插入图片描述
那么问题来了，为了我们能够在上传数据之后让hive表正确识别数据，那我们该如何修改hive表的默认分隔符呢？

其实也是非常的简单，只要我们在创建表的时候指定一下分隔符就可以了，我们把建表语句修改一下，重新创建一个表t3_new

create table t3_new(
id int comment 'ID',
stu_name string comment 'name',
stu_birthday date comment 'birthday',
online boolean comment 'is online'
)row format delimited 
fields terminated by '\t' 
lines terminated by '\n';

在这里插入图片描述

在这需要注意的是，lines terminated by 行分隔符可以忽略不写，但是如果要写的话，只能写到最后面！

把t3.data文件中的字段分隔符都恢复为制表符
在这里插入图片描述
然后重新把数据加载到t3_new表中。

load data local inpath '/data/soft/hivedata/t3.data' into table t3_new;

在这里插入图片描述

查看t3_new表中的数据，注意，针对无法识别的数据显示为NULL，因为最后一列为boolean类型，但是在数据中我故意指定了一个数字，所以导致无法解析，但是不会导致数据加载失败，也不会导致查询失败，这就是hive的特性，他不会提前检查数据，只有在使用的时候才会检查数据，如果数据有问题就显示为null，也不报错。

select * from t3_new;

在这里插入图片描述

三、Hive中的数据类型

hive作为一个类似数据库的框架，也有自己的数据类型，便于存储、统计、分析。
Hive中主要包含两大数据类型

一类是基本数据类型
一类是复合数据类型

基本数据类型：常用的有INT,STRING,BOOLEAN,DOUBLE等
复合数据类型：常用的有ARRAY,MAP,STRUCT等

1、基本数据类型

看这个表，一般数字类型我们可以试验int，小数可以使用double，日期可以使用date类型、还有就是boolean类型，这些算是比较常见的了，前面我们在建表的时候基本都用过了。

这些基本数据类型倒没有什么特殊之处

数据类型		开始支持版本		数据类型		开始支持版本
TINYINT			~			TIMESTAMP	0.8.0
SMALLINT		~			DATE		0.12.0
INT/INTEGER		~			STRING		~
BIGINT			~			VARCHAR		0.12.0
FLOAT			~			CHAR		0.13.0
DOUBLE			~			BOOLEAN		~
DECIMAL		0.11.0

2、复合数据类型

下面主要看一下复合数据类型，在这里我们主要分析这三个，array，map和struct

数据类型		开始支持版本		格式
ARRAY		0.14.0			ARRAY<data_type>
MAP			0.14.0			MAP<primitive_type, data_type>
STRUCT		~				STRUCT<col_name : data_type, ...>

（1）Array

先来看Array，这个表示是一个数组结构
在这里举一个例子：学生有多个爱好，有两个学生，zhangsan、lisi，
zhangsan的爱好是swing、sing、coding
lisi的爱好是music、football

每个学生的爱好都是不固定的，有多有少，如果根据学生的每一个爱好都在表里面增加一列，这样就不合适了，后期可能要经常增加列存储不同的爱好

如果我们如果把每个学生的爱好都拼接为一个字符串保存到一个字段中，这样针对存储层面来说是没有问题的，但是后期需要根据爱好的增加而修改字段，这样操作起来很不方便，如果想获取每个学生的1个爱好，这样是没办法直接获取的，因为这些爱好是以字符串的形式保存在一个字段中的

为了方便存储和使用，我们针对学生的爱好这种数据个数不固定的场景，可以使用数组的形式来存储

测试数据是这样的

1       zhangsan        swing,sing,coding
2       lisi    music,football

在这里插入图片描述
来建一张表，指定了一个array数组类型的字段叫favors，数组中存储字符串，数组中的元素怎么分割呢？通过collection items terminated by ','指定的。

create table stu(
  id int,
  name string,
  favors array<string>
)row format delimited
fields terminated by '\t'
collection items terminated by ','
lines terminated by '\n';

在这里插入图片描述
向表中加载数据

load data local inpath '/data/soft/hivedata/stu.data' into table stu;

查询数组中的某一个元素，使用arrayName[index]

select * from stu;
select id,name,favors[1] from stu;

在这里插入图片描述
角标是从0开始的，如果获取到了不存在的角标则返回null
这就是Array类型的使用了

（2）Map

下面来说一下另外一种常见的集合——map，我们知道map集合里面存储的是键值对，每一个键值对属于Map集合的一个item，
这里给大家举个例子，有两个学生zhangsan、lisi，每个学生有语文、数学、英语，成绩如下：

1       zhangsan        chinese:80,math:90,english:100
2       lisi    chinese:89,english:70,math:88

在这里插入图片描述
针对学生的成绩信息最好也是存储到一个字段中，方便管理和使用，发现学生的成绩都是key-value类型的，所以非常适合使用map类型

建表语句如下：指定scores字段类型为map格式
通过collection items terminated by ','指定了map中元素之间的分隔符
通过map keys terminated by ':'指定了key和value之间的分隔符

create table stu2(
id int,
name string,
scores  map<string,int>
)row format delimited
fields terminated by '\t'
collection items terminated by ','
map keys terminated by ':'
lines terminated by '\n';

在这里插入图片描述

向表中加载数据

load data local inpath '/data/soft/hivedata/stu2.data' into table stu2;

在这里插入图片描述

查看表中的数据

select * from stu2;

在这里插入图片描述

查询所有学生的语文和数学成绩

select id,name,scores['chinese'],scores['math'] from stu2;

在这里插入图片描述

在这注意一下，我们取数据是根据元素中的key获取的，和map结构中元素的位置没有关系

（3）Struct

再来介绍最后一种复合类型struct，有点像java中的对象，举个例子说明一下

某学校有2个实习生，zhangsan、lisi，每个实习生都有地址信息，一个是户籍地所在的城市，一个是公司所在的城市

我们来组织一下数据

1       zhangsan        bj,sh
2       lisi    gz,sz

在这里插入图片描述
针对这里面的地址信息，不能懒省事使用字符串，否则后期想要获取他们对应的户籍地城市或者公司所在的城市信息时就比较麻烦了

所以在这我们可以考虑使用Struct类型

建表语句如下

create table stu3(
id int,
name string,
address  struct<home_addr:string,office_addr:string>
)row format delimited
fields terminated by '\t'
collection items terminated by ','
lines terminated by '\n';

在这里插入图片描述

加载数据

load data local inpath '/data/soft/hivedata/stu3.data' into table stu3;

在这里插入图片描述

查询数据

select * from stu3;
select id,name,address.home_addr from stu3;

在这里插入图片描述

在这里大家会发现其实这个需求，我们使用Array也是可以搞定的吧，只不过是在查询的时候只能通过角标访问，不太方便而已。

（4）Struct和Map的区别

如果从建表语句上来分析，其实这个Struct和Map还是有一些相似之处的
来总结一下：

map中可以随意增加k-v对的个数
struct中的k-v个数是固定的

map在建表语句中需要指定k-v的类型
struct在建表语句中需要指定好所有的属性名称和类型

map中通过[]取值
struct中通过.取值，类似java中的对象属性引用

map的源数据中需要带有k-v
struct的源数据中只需要有v即可

总体而言还是map比较灵活，但是会额外占用磁盘空间，因为他比struct多存储了数据的key
struct只需要存储value，比较节省空间，但是灵活性有限，后期无法动态增加k-v