大数据开发工程师-第八周 第4章 Hive核心实战

发表于 | 分类于 ， | 浏览 次
字数统计: 2.8k | 阅读时长 ≈ 12

第八周 第4章 Hive核心实战

Hive中数据库的操作

1
2
3
4
5
show databases;
use default;
create database xxx;

drop database xxxx;  注意：default默认数据库无法删除！
1
2
3
default是默认数据库，默认就在这个库里面

咱们前面说过hive的数据都是存储在hdfs上，那这里的default数据库在HDFS上是如何体现的？在 hive-site.xml中有一个参数 hive.metastore.warehouse.dir

image-20230319212757783

1
2
它的默认值是 /user/hive/warehouse ，表示hive的default默认数据库对应的hdfs存储目录。
那我们到HDFS上看一下，发现确实有这个目录，并且这个目录下还有一个t1目录，其实这个t1就是我们前面在default数据库中创建的那个t1表，从这可以看出来，hive中的数据库和hive中的表，在hdfs上面的体现其实都是目录。

image-20230319212927116

1
这个默认数据库的信息在Metastore中也有记录，在dbs表中

image-20230319212951431

image-20230319213124521

image-20230319213139700

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

image-20230319213234031

Hive中表的操作

创建表

1
create table xxx(id int);

查看创建的表

1
2
查看表信息
show tables;###

查看表结构信息

1
desc xxx;

查看表的创建信息

image-20230319213651697

1
2
3
4
show create table xxx;
从这里的location可以看到这个表在hdfs上的位置。

注意了：表中的数据是存储在hdfs中的，但是表的名称、字段信息是存储在metastore中的
1
2
3
4
5
到metastore中看一下
先看tbls表，这个里面中存储的都是在hive中创建的表
可以看到DB_ID 为1
可以到dbs表中看到默认default数据库的id就是1。
TBL_NAME 是这个表的名称。

image-20230319213833024

image-20230319213856977

1
2
在表COLUMNS_V2中存储的是Hive表的字段信息(包含字段注释、字段名称、字段类型、字段顺序)
其中的CD_ID和tbls中的TBL_ID相等

image-20230319214021186

修改表名

1
2
3
hive (default)> alter table t2 rename to t2_bak;

hdfs中对应的目录名称也同步变化了

加载数据

1
2
3
4
5
6
咱们前面向表中添加数据是使用的insert命令，其实使用insert向表里面添加数据只是在测试的时候使用，实际中向表里面添加数据很少使用insert命令的
具体原因我们后面再分析，在这大家先带着这个问题。
insert into test2(id, name) values(1,"zhangsan");

向表中加载数据可以使用load命令
以t2_bak为例，在bigdata04机器的 /data/soft/hivedata 下有一个 t2.data 文件，将其加载到 t2_bak表中
1
2
3
4
5
6
7
8
9
10
11
12
13
14
[root@bigdata04 hivedata]# pwd
/data/soft/hivedata
[root@bigdata04 hivedata]# more t2.data
1 2 3 4 5
hive (default)> load data local inpath '/data/soft/hivedata/t2.data' into table t2_bak

hive (default)> select * from t2_bak;
OK
t2_bak.id
1
2
3
4
5
1
我们到hdfs上去看一下这个表，发现刚才的文件其实就是上传到了t2_bak目录中

HzRNWV.md.png

1
2
3
4
5
那我们自己手工通过put命令把数据上传到t2_bak目录中可以吗？
可以的！
hdfs dfs -put /data/soft/hivedata/t2_bak.data /user/hive/warehouse/t2_bak

到hdfs上确认一下，可以看到刚才上传的文件

HzWY0H.md.png

1
2
3
4
5
6
7
8
9
10
11
12
13
14
再查询一下这个表的数据，可以发现数据多了一份，说明刚才使用hdfs的put命令上传的是可以的。
hive (default)> select * from t2_bak;
OK
t2_bak.id
1 
2 
3 
4 
5 
1 
2 
3 
4 
5

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

增加字段

1
2
在工作中会有给已存在的表增加字段的需求，需要使用alter命令
在这里我们给t2_bak表增加一个name字段，重新查看表结构信息，再查询一下这个表中的数据，结果发现，第二列为null，这是正常的，因为我们的数据数据文件中就只有一列，第二列查询不到，就显示为null，不会报错，这一点要注意一下
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
hive (default)> alter table t2_bak add columns (name string);
OK
Time taken: 0.175 seconds
hive (default)> desc t2_bak;
OK
col_name data_type comment
id int
name string
Time taken: 0.121 seconds, Fetched: 2 row(s)
hive (default)> select * from t2_bak;
OK
t2_bak.id t2_bak.name
1 NULL
2 NULL
3 NULL
4 NULL
5 NULL
1 NULL
2 NULL
3 NULL
4 NULL
5 NULL
Time taken: 0.199 seconds, Fetched: 10 row(s)

增加注释

1
2
3
4
现在我们通过desc查询表中的字段信息发现都没有注释，所以想要给字段加一些注释，以及表本身也可以增加注释，都是使用comment关键字
重新创建一个表t2

注意：在建表语句中，缩进不要使用tab制表符，否则拷贝到hive命令行下执行会提示语句错误，这里的缩进需要使用空格
1
2
3
create table t2(
age int comment '年龄'
) comment '测试';
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
查看这个表的信息，结果发现我们添加的中文注释都是乱码

hive (default)> desc t2;
OK
col_name data_type comment
age int ??
Time taken: 0.086 seconds, Fetched: 1 row(s)
hive (default)> show create table t2;
OK
createtab_stmt
CREATE TABLE `t2`(
`age` int COMMENT '??')
COMMENT '??'
ROW FORMAT SERDE
'org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe'
1
2
原因是什么？怎么破？
中文乱码的原因是因为hive数据库里面的表都是latin1编码的，中文本来就会显示乱码，但是又不能修改整个数据库里面所有表的编码，否则在使用hive的时候会出问题，那么只有考虑把存储字段注释和表注释相关的表的编码改为utf8。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
登陆Mysql数据库切换到Hive库：

C:\Users\yehua>mysql -uroot -padmin
mysql: [Warning] Using a password on the command line interface can be insecu
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 736
Server version: 8.0.16 MySQL Community Server - GPL
Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement
mysql> use hive;
Database changed
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
28
29
30
31
32
33
确认一下现在表COLUMNS_V2和TABLE_PARAMS的编码，都是latin1

mysql> show create table COLUMNS_V2;
+------------+---------------------------------------------------------------
| Table | Create Table
+------------+---------------------------------------------------------------
| COLUMNS_V2 | CREATE TABLE `columns_v2` (
`CD_ID` bigint(20) NOT NULL,
`COMMENT` varchar(256) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL
`COLUMN_NAME` varchar(767) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL
`TYPE_NAME` mediumtext,
`INTEGER_IDX` int(11) NOT NULL,
PRIMARY KEY (`CD_ID`,`COLUMN_NAME`),
KEY `COLUMNS_V2_N49` (`CD_ID`),
CONSTRAINT `COLUMNS_V2_FK1` FOREIGN KEY (`CD_ID`) REFERENCES `cds` (`CD_ID`
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+------------+---------------------------------------------------------------
1 row in set (0.00 sec)

mysql> show create table TABLE_PARAMS;
+--------------+-------------------------------------------------------------
| Table | Create Table
+--------------+-------------------------------------------------------------
| TABLE_PARAMS | CREATE TABLE `table_params` (
`TBL_ID` bigint(20) NOT NULL,
`PARAM_KEY` varchar(256) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL,
`PARAM_VALUE` mediumtext CHARACTER SET latin1 COLLATE latin1_bin,
PRIMARY KEY (`TBL_ID`,`PARAM_KEY`),
KEY `TABLE_PARAMS_N49` (`TBL_ID`),
CONSTRAINT `TABLE_PARAMS_FK1` FOREIGN KEY (`TBL_ID`) REFERENCES `tbls` (`TB
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+--------------+-------------------------------------------------------------
1 row in set (0.00 sec)
1
2
3
4
5
6
7
8
修改这两张表的编码即可；
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;
1
2
这样修改之后以后就可以看到中文注释了。
注意：需要先把之前创建的t2表删除掉，因为之前存储的中文已经是乱码了，无法恢复，删除之后重新创建就可以了

指定列和行分隔符的指定

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
在我们实际工作中，肯定不会像上面一样创建一张非常简单的表，实际中表的字段会比较多，下面我们就来创建一个多字段的表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 ，表中的多列内容之间是使用制表符分割的
看一下表中的数据，会不会有问题呢？

[root@bigdata04 hivedata]# pwd
/data/soft/hivedata
[root@bigdata04 hivedata]# more t3.data
1 	 张三 	2020-01-01	 true
2	 李四	    2020-02-01 	 false
3	 王五 	2020-03-01  	0
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
hive (default)> load data local inpath '/data/soft/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
Time taken: 0.229 seconds, Fetched: 3 row(s)

在这里发现不是我们想要的结果，都是NULL，说明数据没有被识别，这是为什么？(行分割符默认是\n没问题)
注意了，hive在创建表的时候，需要我们指定相应的行分隔符，列分隔符。而我们在创建mysql表的时候，这些都是不需要的，因为它在组织数据的时候，已经规定好了数据的表现形式。

我们刚才在创建t3的时候没有指定相应的分隔符，所以导致使用制表符分割的数据无法被解析。

实际上呢，hive是有默认的分隔符的，默认的行分隔符是 '\n'，就是换行符，而默认的列分隔符呢，是\001 。
\001 这个是ASCII码中的特殊不常使用的不可见字符，在文本中我们可以通过ctrl+v和ctrl+a来输入\001，这里我们在将t3.data改一下，重新上传，再查看表t3。
修改t3.data

[root@bigdata04 hivedata]# vi t3.data
1^A张三^A2020-01-01^Atrue
2 李四 2020-02-01 false
3 王五 2020-03-01 0
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
重新加载数据，查询表数据，这个时候发现刚才修改的那条数据被成功解析了

hive (default)> load data local inpath '/data/soft/hivedata/t3.data' into table t3


Loading data to table default.t3
OK
Time taken: 0.367 seconds


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
1 张三 2020-01-01 true
NULL NULL NULL NULL
NULL NULL NULL NULL
Time taken: 0.149 seconds, Fetched: 6 row(s)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
那么问题来了，为了我们能够在上传数据之后让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 行分隔符可以忽略不写，但是如果要写的话，只能写到最后面！
1
2
3
4
5
6
7
8
把t3.data文件中的字段分隔符都恢复为制表符，然后重新把数据加载到t3_new表中。

hive (default)> load data local inpath '/data/soft/hivedata/t3.data' into table t3_new


查看t3_new表中的数据，注意，针对无法识别的数据显示为NULL，因为最后一列为boolean类型，但是在数据中我故意指定了一个数字，

所以导致无法解析，但是不会导致数据加载失败，也不会导致查询失败，这就是hive的特性，他不会提前检查数据，只有在使用的时候才会检查数据，如果数据有问题就显示为null，也不报错。
1
2
3
4
5
6
hive (default)> select * from t3_new;
OK
t3_new.id t3_new.stu_name t3_new.stu_birthday t3_new.online
1 张三 2020-01-01 true
2 李四 2020-02-01 false
3 王五 2020-03-01 NULL

本文标题:大数据开发工程师-第八周 第4章 Hive核心实战

文章作者:TTYONG

发布时间:2022年02月20日 - 12:02

最后更新:2023年04月04日 - 18:04

原始链接:http://tianyong.fun/%E5%A4%A7%E6%95%B0%E6%8D%AE%E5%BC%80%E5%8F%91%E5%B7%A5%E7%A8%8B%E5%B8%88-%E7%AC%AC%E5%85%AB%E5%91%A8-%E7%AC%AC4%E7%AB%A0-Hive%E6%A0%B8%E5%BF%83%E5%AE%9E%E6%88%981-%E6%95%B0%E6%8D%AE%E5%BA%93%E5%92%8C%E8%A1%A8%E6%93%8D%E4%BD%9C.html

许可协议: 转载请保留原文链接及作者。

多少都是爱
0%