Hive基础学习

逃离我推掉我的手 2023-06-30 02:56 12阅读 0赞

[https://mp.weixin.qq.com/s?\_\_biz=MzA3NDc3ODg1MQ==&mid=2653658331&idx=1&sn=798d6125c0b8609348dbe8de0329c222&chksm=84a5ad57b3d2244128aaa3088c8ad11053151d3377a5490c3f8e16c371a192abbc2f002fce59&token=1596872655&lang=zh\_CN\#rd][https_mp.weixin.qq.com_s_biz_MzA3NDc3ODg1MQ_mid_2653658331_idx_1_sn_798d6125c0b8609348dbe8de0329c222_chksm_84a5ad57b3d2244128aaa3088c8ad11053151d3377a5490c3f8e16c371a192abbc2f002fce59_token_1596872655_lang_zh_CN_rd]

> 本节我们主要来学习一些hive的命令操作，同时探究一下Hive，HDFS，MySQL之间的联系，从而更好的理解其内部原理。

常用的基础命令

#此处的命令都是指在hive命令行下执行的命令，所有的命令别忘记以分号结尾。
    hive> show databases;#查看当前数据库列表
    hive> create databases dbname;#创建一个名称为dbname的数据库
    hive> use databasename;#将当前的数据库切换为databasename
    hive> show tables;#查看当前数据库中有哪些表
    hive> desc tbname;#查看表结构，包括字段类型，注释等

Hive的数据类型

![format_png][]

hive基本数据类型

![format_png 1][]

hive集合数据类型

注：图片来自《hive编程指南》

这里先简单了解一下Hive的数据类型，本节我们暂时不会用到太多很复杂的类型。

建表

假设我们现在建立一张`student`表，它有两个字段，`id(int)`和`name(string)`。

hive> create database learn;#创建数据库learn
    hive> use learn;#切换到learn数据库
    hive> create table student(id int, name string);#创建表student
    hive> show create table student;#查看建表语句

![format_png 2][]

创建表，查看建表语句

我们通过`show create table student;`命令查看`student`表建立时一些详细信息。关注一下`LOCATION`的值为：`hdfs://localhost:8020/user/hive/warehouse/learn.db/student`，猜测这是一个HDFS的文件路径，我们通过浏览器验证一下：

![format_png 3][]

在浏览器中查看student表的location

可以看到，我们建立的`student`表是HDFS上的一个目录(文件夹)，目录的位置就是`LOCATION`对的值。

**将本地文件加载到Hive表**

本地文件：当前目录下的`student.txt`，字段之间用**空格**分割，

1 zhangsan
    2 lisi
    3 wangwu

hive> load data local inpath 'student.txt' into table student;

![format_png 4][]

加载本地数据

加载成功之后，我们先来看一下HDFS中student目录，如下图所示，可以看到该目录下出现了一个student文件。

![format_png 5][]

查看HDFS中student目录

然后用hive命令查询一下student表。

hive> select * from student;
    OK
    NULL    NULL
    NULL    NULL
    NULL    NULL
    Time taken: 0.139 seconds, Fetched: 3 row(s)

可以看到结果虽然是有三行，但全部否是null。如果执行`select count(*) from student;`也能输出结果为3。

![format_png 6][]

查询student表的行数

出现这种情况是，我们建表时没有指定数据列之间的分隔符，hive默认的字段分隔符是`\001，即ASCII码的第一个字符Control-A`，而我们的文件的分隔符是空格，二者不一致是导致数据不能正确加载的原因。

接下来我们创建一个新的表并指定分隔符为`\t`，即tab符，然后重新加载以`\t`分割的本地文件。

hive> create table teacher(id int, name string) row format delimited fields terminated by '\t';#创建teacher表并指定行分隔符为'\t'
    hive> load data local inpath 'teacher.txt' into table teacher;#实际运行加了overwrite，表示覆盖表中已有的数据，如果不加overwrite就会在已有的数据后直接添加。

![format_png 7][]

加载tab符分割的数据

创建teacher表同样在HDFS中建立了相应的目录，load数据同样也将本地的数据加载到了相应的目录中。

![format_png 8][]

HDFS中查看teacher目录

**将HDFS文件加载到Hive表**

我们按照以下的步骤进行操作：

①在本地建立`course.txt`文件，写入三行内容，字段之间用空格分割。

1    语文
    2    数学
    3    体育

②将`course.txt`上传到HDFS中并检查是否上传成功。

③在hive中建立`course`表，指定分隔符为tab符

④执行`load data inpath '/course.txt' into table course`命令，加载HDFS数据到course表中。

⑤查询`course`表中的数据，验证是否成功加载

⑥查看HDFS中是否依然存在`course.txt`文件。

![format_png 9][]

![format_png 10][]

![format_png 11][]

从执行的结果我们可以看出，从HDFS中加载数据时，是将HDFS中的文件直接移动到了表对应的HDFS目录中(内部表)。

理解Hive的元数据

Hive环境搭建的时候我们使用MySQL存储了Hive元数据，并且在初始化时生成了很多mysql数据表。那么它们有什么作用呢？我们来看一下TBLS，COLUMNS\_V2，SDS三个表的信息，很容易联系到我们前面创建的表。

mysql> select * from TBLS;#存放我们已经建立的表，具体字段如图所示
    mysql> select * from COLUMNS_V2;#存放表的字段，CD_ID是外键
    mysql> select * from SDS;#存放表的LOCATION和格式信息

![20200114211313659.gif][]

![format_png 12][]

![format_png 13][]

注：这个表横向太长了。因此截成了两段，凑合看。感兴趣的可以自己动手试试。

![format_png 14][]

TBS存放的是我们已经创建的表，COLUMNS\_V2存放了标的字段信息，SDS存放了表的LOCATION和格式信息。其他表的信息我们也可以通过看里面的内容理解其作用~请读者自行了解~

其他命令

查看某个表的基本信息:

hive> desc student;#查看表的字段及其类型
    OK
    id                      int
    name                    string
    Time taken: 0.069 seconds, Fetched: 2 row(s)
    
    hive> desc formatted student;#查看表的详细信息，包括字段，位置，类型，存储信息等
    OK
    # col_name                data_type               comment
    
    id                      int
    name                    string
    
    # Detailed Table Information
    Database:               learn
    Owner:                  chenghengchao
    CreateTime:             Sat Jun 08 00:31:45 CST 2019
    LastAccessTime:         UNKNOWN
    Protect Mode:           None
    Retention:              0
    Location:               hdfs://localhost:8020/user/hive/warehouse/learn.db/student
    Table Type:             MANAGED_TABLE
    Table Parameters:
        COLUMN_STATS_ACCURATE   true
        numFiles                1
        numRows                 0
        rawDataSize             0
        totalSize               27
        transient_lastDdlTime   1559973358
    
    # Storage Information
    SerDe Library:          org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe
    InputFormat:            org.apache.hadoop.mapred.TextInputFormat
    OutputFormat:           org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat
    Compressed:             No
    Num Buckets:            -1
    Bucket Columns:         []
    Sort Columns:           []
    Storage Desc Params:
        serialization.format    1
    Time taken: 0.059 seconds, Fetched: 32 row(s)

在hive命令下直接操作HDFS：

# 在hive下直接操作HDFS，建立data文件夹并上传本地文件到HDFS，命名为a.txt 和b.txt
    hive> dfs -mkdir /data;
    hive> dfs -put student.txt /data/a.txt;
    hive> dfs -put student.txt /data/b.txt;

![format_png 15][]

表的类型有两种MANAGED\_TABLE和EXTERNAL\_TABLE。

> hive内部表和外部表的区别  
>        1）创建表时：创建内部表时，会将数据移动到数据仓库指向的路径；若创建外部表，仅记录数据所在的路径， 不对数据的位置做任何改变。  
>        2）删除表时：在删除表的时候，内部表的元数据和数据会被一起删除，而外部表只删除元数据，不删除数据。这样外部表相对来说更加安全些，数据组织也更加灵活，方便共享源数据
> 
> reference:https://blog.csdn.net/u010886217/article/details/83796151
> 
> reference:https://www.jianshu.com/p/ac2eca9181be
> 
> reference:https://blog.csdn.net/qq\_36743482/article/details/78393678

#创建外部表并指向已有的data目录，如果指定的location不存在则会创建。
    hive> create external table ext_student(id bigint,name string) row format delimited fields terminated by '\t' location '/data';
    OK
    Time taken: 0.064 seconds
    hive> select * from ext_student;
    OK
    1   zhangsan
    2   lisi
    3   wangwu
    1   zhangsan
    2   lisi
    3   wangwu
    Time taken: 0.034 seconds, Fetched: 6 row(s)

查询结果显示了6条记录，前3条和后3条是一样的。这是因为/data 下面有两个文件a.txt 和b.txt，每个文件有3行有数据。如果再上传一次teacher文件到/data目录下。在看ext\_student中的数据

hive> dfs -put teacher.txt /data/c.txt;
    hive> select * from ext_student;
    OK
    1    zhangsan
    2    lisi
    3    wangwu
    1    zhangsan
    2    lisi
    3    wangwu
    1    赵老师
    2    王老师
    3    刘老师
    4    邓老师
    Time taken: 0.032 seconds, Fetched: 10 row(s)

可见，指定的location，即/data目录下的三个文件的数据都被加载到了ext\_student中。事实上，无论是外部表还是内部表，只要把某个文件放到表的目录下，就会被扫描并被查询出来。查询的执行过程是先通过TBLS表找到student表，然后根据表id到COLUMNS\_V2表查找这张表都有哪些字段，然后再根据表id到SDS表中查找应该到HDFS的那个目录下去查找数据。

Hive分区表

hive> create external table players(id bigint,name string) partitioned by (nation string) row format delimited fields terminated by '\t' location '/players';#执行后会在HDFS上创建/players目录，这是一个以nation分区的表
    OK
    Time taken: 0.095 seconds
    hive> dfs -put china.txt /players;#将本地的china.txt上传到HDFS的players目录下
    hive> select * from players;#查询players表，发现结果是空的，但如果从浏览器中查看players牡蛎，发现文件china.txt存在。
    OK
    Time taken: 0.041 seconds

查询没有数据的原因，是没有上传文件到指定分区。我们换一种方式。

hive> load data local inpath 'china.txt' into table players partition(nation='China');
    Loading data to table learn.players partition (nation=China)
    Partition learn.players{nation=China} stats: [numFiles=1, numRows=0, totalSize=38, rawDataSize=0]
    OK
    Time taken: 0.242 seconds
    hive> select * from players;
    OK
    1    zhangjike   China
    2    yijianlian  China
    3    subingtian  China
    Time taken: 0.045 seconds, Fetched: 3 row(s)

![format_png 16][]

我们上传文件时指定分区，就能查询出结果。此时在HDFS上出现了一个`nation=China`的目录，`china.txt`被上传到了改该目录下。

我们试一下手动创建`nation=USA`目录，并将usa.txt上传到该目录。再查询`players`表

hive> dfs -mkdir /players/nation=USA;
    hive> dfs -put usa.txt /players/nation=USA;
    hive> select * from players;
    OK
    1    zhangjike   China
    2    yijianlian  China
    3    subingtian  China
    Time taken: 0.042 seconds, Fetched: 3 row(s)

查询的结果没有USA分区的数据，但从浏览器中查看确实已经存在了/players/nation=USA/usa.txt，并且usa.txt是有数据的。这是因为元数据库中没有记录USA这个分区。(看SDS表的话，只有nation=China的记录)。但如果此时使用load命令加载数据，则可以创建nation=USA的分区。在SDS表中也会出现nation=USA。

hive> load data local inpath 'usa.txt' into table players partition(nation='USA');
    Loading data to table learn.players partition (nation=USA)
    Partition learn.players{nation=USA} stats: [numFiles=2, numRows=0, totalSize=50, rawDataSize=0]
    OK
    hive> select * from players;
    OK
    1    zhangjike   China
    2    yijianlian  China
    3    subingtian  China
    1    Curry   USA
    2    Phelps  USA
    3    Woods   USA
    1    Curry   USA
    2    Phelps  USA
    3    Woods   USA
    Time taken: 0.037 seconds, Fetched: 9 row(s)

查询的结果中出现了USA分区，但是有重复。这是因为nation=USA目录下有两个相同内容的文件。原因是通过load命令将目录“变”为分区的同时，也加载了相同的一份数据。我们也可以看出：分区一定是一个目录，但目录不一定是分区。而且如果删除了分区，文件夹可以继续存在，如果删除了文件夹，分区也继续存在。

![format_png 17][]

我们也可以通过另一种方式修改(增加)分区，如下面命令所示。如果直接运行该命令，会在HDFS上创建目录nation=Other。如果我们先用mkdir创建nation=Other目录，再使用该命令，也可以将目录“变”为分区，此时目录下的文件就会被加载到表中，读者可以自行验证。

hive> alter table players add partition(nation='Other') location '/players/nation=Other';

再总结一下查看和删除分区的命令。

hive> show partitions players;#查看分区
    OK
    nation=China
    nation=Other
    nation=USA
    Time taken: 0.047 seconds, Fetched: 3 row(s)
    hive> alter table players drop if exists partition (nation='Other');#删除某个分区
    Dropped the partition nation=Other
    OK
    Time taken: 0.068 seconds

使用了分区之后，最大的好处是能提高查询的速度，同时也对数据进行了更细粒度的划分。可以使用where关键字限制分区。

hive> select * from players where nation = 'China';
    OK
    1    zhangjike   China
    2    yijianlian  China
    3    subingtian  China
    Time taken: 0.074 seconds, Fetched: 3 row(s)
    hive> select * from players where nation = 'USA';
    OK
    1    Curry   USA
    2    Phelps  USA
    3    Woods   USA
    1    Curry   USA
    2    Phelps  USA
    3    Woods   USA
    Time taken: 0.047 seconds, Fetched: 6 row(s)

总结

本文我们对Hive的一些基础知识进行了学习，包括查看数据库，查看表的基本命令，如何建表并加载数据，hive元数据的存储位置，hive分区表与HDFS的关系等。通过学习我们对Hive底层原理有了更为深刻的认识。

[https_mp.weixin.qq.com_s_biz_MzA3NDc3ODg1MQ_mid_2653658331_idx_1_sn_798d6125c0b8609348dbe8de0329c222_chksm_84a5ad57b3d2244128aaa3088c8ad11053151d3377a5490c3f8e16c371a192abbc2f002fce59_token_1596872655_lang_zh_CN_rd]: https://mp.weixin.qq.com/s?__biz=MzA3NDc3ODg1MQ==&mid=2653658331&idx=1&sn=798d6125c0b8609348dbe8de0329c222&chksm=84a5ad57b3d2244128aaa3088c8ad11053151d3377a5490c3f8e16c371a192abbc2f002fce59&token=1596872655&lang=zh_CN#rd
[format_png]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly9tbWJpei5xcGljLmNuL21tYml6X3BuZy9vVTE1b1VNc2VaYnB3R2liaWI1V1QwbU9wQTlkT3hma0ZmbXNlZ295Rm0zRnAzd0JoNWRUVjQ2aWNFSEFld21VakNDOGxFUWtLMzBuU3JDSTFpYTR1NlJsaWJnLzY0MA?x-oss-process=image/format,png
[format_png 1]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly9tbWJpei5xcGljLmNuL21tYml6X3BuZy9vVTE1b1VNc2VaYnB3R2liaWI1V1QwbU9wQTlkT3hma0ZmdzJxcWtTOHlXdUVDcGp2SXdWMGVMMkRDeERySUxpYVVselFEMXY0dHQ0WGI4S2sxUTdPTlh0QS82NDA?x-oss-process=image/format,png
[format_png 2]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly9tbWJpei5xcGljLmNuL21tYml6X3BuZy9vVTE1b1VNc2VaYnB3R2liaWI1V1QwbU9wQTlkT3hma0Zma0NUd0J2bmpUaGE5d2xUQnhRUzlWVkRJR3J1MUFOeUJIRjc0a1dKa1JTYmRKNlhSOEJad0dBLzY0MA?x-oss-process=image/format,png
[format_png 3]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly9tbWJpei5xcGljLmNuL21tYml6X3BuZy9vVTE1b1VNc2VaYnB3R2liaWI1V1QwbU9wQTlkT3hma0ZmVFpobmljVTZxRGdFOGgzckU2WXVWVzRuNHFKZnlpYW1LN05lNk9pYXVpYnZSSTM2T1oxMjR2c1V0dy82NDA?x-oss-process=image/format,png
[format_png 4]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly9tbWJpei5xcGljLmNuL21tYml6X3BuZy9vVTE1b1VNc2VaYnB3R2liaWI1V1QwbU9wQTlkT3hma0ZmT09pY1VpYUhCcHJreGJCWUZ6MkRzeTVpY1NXdmljU3RkaWNxYUw3Mkg0aWFpYnFrVnNDcHBBOVRrMjVmdy82NDA?x-oss-process=image/format,png
[format_png 5]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly9tbWJpei5xcGljLmNuL21tYml6X3BuZy9vVTE1b1VNc2VaYnB3R2liaWI1V1QwbU9wQTlkT3hma0ZmdVNDbm5SenZjZVVZc2FHaWNhUTdMZHA5aWN6clVYV3lRemdZd3hFS3E4cm9pY1dSSGljNTJwdjhWUS82NDA?x-oss-process=image/format,png
[format_png 6]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly9tbWJpei5xcGljLmNuL21tYml6X3BuZy9vVTE1b1VNc2VaYnB3R2liaWI1V1QwbU9wQTlkT3hma0ZmaWNsMmtpY1JlZGsxSlVERGcxUUVjcXJEcEZLZmdTMVF2YlFPMWM2NnRQWklmSTRhTFgzVGhwWVEvNjQw?x-oss-process=image/format,png
[format_png 7]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly9tbWJpei5xcGljLmNuL21tYml6X3BuZy9vVTE1b1VNc2VaYnB3R2liaWI1V1QwbU9wQTlkT3hma0ZmZ1BEUFZQZTk1am9ETjBIakMyc2dCdjVmRDdpYWljaWFBMjBUamVUaWJoNWszcW5ZSWs3V3RTR2xpYWcvNjQw?x-oss-process=image/format,png
[format_png 8]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly9tbWJpei5xcGljLmNuL21tYml6X3BuZy9vVTE1b1VNc2VaYnB3R2liaWI1V1QwbU9wQTlkT3hma0ZmaWI0ZFo3N1pUVTRFU0NQTmxCTlJETkN0RUxWSHBxUEhEeGcwYkZKb1JUUzF2TlMydmN5Wm1Jdy82NDA?x-oss-process=image/format,png
[format_png 9]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly9tbWJpei5xcGljLmNuL21tYml6X3BuZy9vVTE1b1VNc2VaYnB3R2liaWI1V1QwbU9wQTlkT3hma0Zmd29rcjBpYUhTRkRkNVdnTkQ1VmlhY1RwbUlsM0JIbFh6aWFpYVZIOW1QSG9rVUFiYlh2ZE5BZWRoUS82NDA?x-oss-process=image/format,png
[format_png 10]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly9tbWJpei5xcGljLmNuL21tYml6X3BuZy9vVTE1b1VNc2VaYnB3R2liaWI1V1QwbU9wQTlkT3hma0ZmcmMzcHdSZ2F0WE1MZmljV2lhaWFNWlM1cmQxenBTckpsbWg2clpndkNTMENtZDRONUVzaWJQMmNLdy82NDA?x-oss-process=image/format,png
[format_png 11]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly9tbWJpei5xcGljLmNuL21tYml6X3BuZy9vVTE1b1VNc2VaYnB3R2liaWI1V1QwbU9wQTlkT3hma0ZmV0pVQjRNbnVoVFRyRVBBMll0MTg4ZWtUazVVeEZ1VGlhUnpsNkd3Y2hjWmtDUm94UjRJcUFqUS82NDA?x-oss-process=image/format,png
[20200114211313659.gif]: https://img-blog.csdnimg.cn/20200114211313659.gif
[format_png 12]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly9tbWJpei5xcGljLmNuL21tYml6X3BuZy9vVTE1b1VNc2VaYnB3R2liaWI1V1QwbU9wQTlkT3hma0ZmT0dmQXByNEthU3BaTWgxVUxScnJ1a2Zkd01UbjdKMWlhZGJNNExmeVNmMEdQYUhFaWFaQjFsSEEvNjQw?x-oss-process=image/format,png
[format_png 13]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly9tbWJpei5xcGljLmNuL21tYml6X3BuZy9vVTE1b1VNc2VaYnB3R2liaWI1V1QwbU9wQTlkT3hma0ZmaWFGY01LRjZHbmpIc2xhTHpvU3lCTlFpY3ZSZVV1U09LbldhWGJCeWliZXZEZ0ZOelc0bXd1UGJ3LzY0MA?x-oss-process=image/format,png
[format_png 14]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly9tbWJpei5xcGljLmNuL21tYml6X3BuZy9vVTE1b1VNc2VaYnB3R2liaWI1V1QwbU9wQTlkT3hma0ZmNmhpYjI0NklCRjNMZXlKTUVmM3cybVYwcWlhUEVIaWJKRktpY0RENndxUHZEeVYyU1V2NzdGOGFqdy82NDA?x-oss-process=image/format,png
[format_png 15]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly9tbWJpei5xcGljLmNuL21tYml6X3BuZy9vVTE1b1VNc2VaYnB3R2liaWI1V1QwbU9wQTlkT3hma0ZmQVBuNVZOclB4SmlhRmpUckNRRkRWZ0Q5NkI2dGlhWTVVRjJHaWNRRVptOExMMUppYWprOWFpYjlXYXcvNjQw?x-oss-process=image/format,png
[format_png 16]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly9tbWJpei5xcGljLmNuL21tYml6X3BuZy9vVTE1b1VNc2VaYnB3R2liaWI1V1QwbU9wQTlkT3hma0ZmUlh6QXZ3UVl4VUc5cjV6d052QkFpYVd0MGx3a2ljY1VHUWppYUUxTkF5TjlINDROUU1CNGlhMUFHUS82NDA?x-oss-process=image/format,png
[format_png 17]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly9tbWJpei5xcGljLmNuL21tYml6X3BuZy9vVTE1b1VNc2VaYnB3R2liaWI1V1QwbU9wQTlkT3hma0ZmSDRNVHJDbTNEZ0l4ZW5GWnJRcVlCZHIybGlhS1k2Qm5MY0Q5aWFDbHo5bkFCcndVckRjWFpEZmcvNjQw?x-oss-process=image/format,png