Hive核心实战

Hive中数据库的操作

show databases; # 查看数据库列表
use default; # 选择数据库
create database mydb1; # 创建数据库
create database mydb2 location '/user/hive/mydb2'; # 指定hdfs目录的位置
drop database mydb1; # 删除数据库

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表中

Hive中表的操作

基本操作

show tables; # 显示当前数据库中所有的表名
drop table tb_user; # 删除表
create table tb_user(id int,name string); # 创建表
desc tb_user; # 查看表结构信息
show create table tb_user; # 查看表的创建信息
alter table tb_user rename to tb_user2; # 修改表名
alter table tb_user add columns(address string); # 新增字段

注意了：表中的数据是存储在hdfs中的，但是表的名称、字段信息是存储在metastore中的。tbls表中存储的都是在hive中创建的表，表COLUMNS_V2中存储的是Hive表的字段信息(包含字段注释、字段名称、字段类型、字段顺序)，其中的CD_ID关联tbls中的TBL_ID字段

加载数据

我们前面向表中添加数据是使用的insert命令，其实使用insert向表里面添加数据只是在测试的时候使用，实际中向表里面添加数据很少使用insert命令的，具体原因我们后面再分析，这里大家先带着这个问题。

向表中加载数据可以使用load命令

t2.data内容

1
2
3
4
5

load data local inpath '/root/test_hive/hivedata/t2.data' into table tb_user2;

我们到hdfs上去看一下这个表，发现刚才的文件其实就是上传到了tb_user2目录中

那我们自己手工通过put命令把数据上传到tb_user2目录中可以吗？
可以的！

hdfs dfs -put /root/test_hive/hivedata/t2.data /user/hive/warehouse/tb_user2/t3.data

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

解决中文乱码

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

create table tb_user(id int comment 'id',name string comment '姓名') comment '用户表'; # 创建表 带注释
desc tb_user;

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

原因是什么？
中文乱码的原因是因为hive数据库里面的表都是latin1编码的，中文本来就会显示乱码，但是又不能修改整个数据库里面所有表的编码，否则在使用hive的时候会出问题（可能，没有实践），那么只有考虑把存储字段注释和表注释相关的列的编码改为utf8。

alter table COLUMNS_V2 modify column COMMENT varchar(256) character set utf8mb4; # 字段注释
alter table TABLE_PARAMS modify column PARAM_VALUE mediumtext character set utf8mb4; # 表注释

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

alter table PARTITION_PARAMS modify column PARAM_VALUE mediumtext character set utf8mb4;
alter table PARTITION_KEYS modify column PKEY_COMMENT varchar(256) character set utf8mb4;

这样修改之后以后就可以看到中文注释了。
注意：需要先把之前创建的t2表删除掉，因为之前存储的中文已经是乱码了，删除之后重新创建就可以了。

指定列和行分隔符

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

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

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

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

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

hive (default)> select * from t3;
OK
t3.id   t3.stu_name     t3.stu_birthday t3.online
NULL    NULL    NULL    NULL
NULL    NULL    NULL    NULL
NULL    NULL    NULL    NULL

在这里发现不是我们想要的结果，都是 NULL ，说明数据没有被识别，这是为什么？
注意了，hive在创建表的时候，需要我们指定相应的行分隔符，列分隔符。而我们在创建mysql表的时候，这些都是不需要的，因为它在组织数据的时候，已经规定好了数据的表现形式。我们刚才在创建t3的时候没有指定相应的分隔符，所以导致使用制表符分割的数据无法被解析。实际上呢，hive是有默认的分隔符的，默认的行分隔符是 '\n' ，就是换行符，而默认的列分隔符呢，是\001 。

create table t3(
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 ' '
lines terminated by '\n';

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

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

Hive中的数据类型

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

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

基本数据类型

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

复合数据类型

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

Array

在这里举一个例子：一个学生可以有多个爱好，有两个学生，zhangsan、lisi，

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

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

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

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 ' '
collection items terminated by ','
lines terminated by '\n';

查询数组中的某一个元素，使用 arrayName[index]，角标是从0开始的，如果获取到了不存在的角标则返回null

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类型

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';

查询map中的某一个元素，使用 mapName[key]

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

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';

查询struct中的某一个元素，使用 structName.key

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

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

案例：复合数据类型的使用

1 zhangsan english,sing,swing chinese:80,math:90,english:10 bj,sh
2 lisi games,coding chinese:89,english:70,math:88 gz,sz

根据这份数据建表，根据我们前面的学习，这里面这几个字段分别是int类型、string类型，array类型，map类型，struct类型

其实也不一定非要使用这些复合类型，主要是需要根据具体业务分析，使用复合数据类型可以更方便的操作数据

create table student (
id int comment 'id',
name string comment 'name',
favors array<string> ,
scores map<string, int>,
address struct<home_addr:string,office_addr:string>
) row format delimited
fields terminated by ' '
collection items terminated by ','
map keys terminated by ':'
lines terminated by '\n';

加载数据

load data local inpath '/root/test_hive/hivedata/student.data' into table student;

查询数据

select id,name,favors[0] firstFavor,scores['chinese'] chineseScore, address.home_addr homeAddr from student;

思考题

问：在mysql中有一张表student(id,name)，还有一张表address(stu_id,home,school)，还有联系方式表contact(stu_id,mine,parents,others)。如果把这三张表迁移到hive中，如何迁移？

答：

可以一一对应迁移，优点是迁移成本非常低，包括DDL和业务逻辑，几乎不需要修改，可以直接使用。缺点是产生大量的表连接，造成查询慢。

可以一对多，mysql中的多张关联表可以创建为hive中的一张表。优点是减少表连接操作。缺点是迁移成本高，需要修改原有的业务逻辑。

实际上，在我们日常的开发过程中遇到这样的问题，要想比较完美、顺利的解决，一般都分为两个阶段，

• 第一个阶段，快捷迁移，就是上面说的一一对应，让我们的系统能跑起来，
• 在此基础之上呢，再做一张大表，尽量包含以上所有字段，例如：stu(id, name, address struct<home,school>, contact struct<…>);等第二个阶段完工之后了，就可以跑在新的系统里面了。