Hive数据库及表的基本操作

实验目的

1、了解Hive的基本数据类型

2、了解Hive DDL、DML的基本操作

3、了解Hive数据导入导出的基本用法

实验环境

1、Linux Ubuntu 14.04

2、hadoop-2.6.0-cdh5.4.5

3、hadoop-2.6.0-eclipse-cdh5.4.5.jar

4、mysql

5、hive-1.1.0-cdh5.4.5

实验内容

在/data/mydata/solog文件中，存储有某搜索引擎网站，搜索日志数据。根据要求完成实验。

实验步骤

一、数据仓库的操作

1、启动hive命令行模式，并在hive中创建一个数据仓库，名为mydb

view plain  copy

1. hive  

view plain  copy

1. create database mydb;  

上述命令可以创建了一个mydb数据仓库。但是这条语句可以更进一步的优化，我们可以加上if not exists

view plain  copy

1. create database if not exists mydb;  

加入if not exists的意思是如果没有mydb库就创建，如果已有就不再创建。

2、查看数据仓库mydb的描述信息和文件目录位置路径信息

view plain  copy

1. describe database mydb;  

3、删除名为mydb的数据仓库

view plain  copy

1. drop database if exists mydb;  

二、关于表的操作

Hive的数据表分为两种，内部表和外部表。

内部表：Hive 创建并通过 load data inpath 进数据库的表，这种表可以理解为数据和表结构都保存在一起的数据表。当通过 drop table table_name 删除元数据中表结构的同时，表中的数据也同样会从 HDFS 中被删除。

外部表：在表结构创建以前，数据已经存在，通过创建表结构，将数据映射到表的结构中。当进行 drop table table_name 操作的时候，Hive 仅仅删除元数据的表结构，而不删除数据，所以，相比内部表，外部表可以更放心大胆地使用。

（简单来说，如果删除内部表会将数据一并删除，易丢失数据，危险！而删除外部表，只会将表结构删除，数据并没有删，生产中常使用外部表）

下面详细介绍对表操作的命令及使用方法

1、再次创建名为mydb的数据仓库。查看mydb数据库仓库中，都有哪些表。

view plain  copy

1. create database mydb;  

view plain  copy

1. use mydb;  
2. show tables;  

2、创建一个名为inlog的内部表，用于存储/data/mydata/solog文件中的数据。

先来介绍一下solog这个文本文件的数据结构。solog中用于存储我们的搜索引擎产生的日志。

主要包括字段为时间(dt)，ip (ip)， 屏幕宽(screenx)，屏幕高(screeny)， 搜索词(word)，上一个搜索词(preword)

view plain  copy

1. 时间             ip             屏幕宽 屏幕高   搜索词  上一个搜索词  
2. 20161220155840  192.168.1.179   1366    768     HTML  
3. 20161220155843  192.168.1.179   1366    768     Sqoop   HTML  
4. 20161220155911  192.168.1.179   1366    768     SparkR  Sqoop  
5. 20161220155914  192.168.1.155   1600    900     hadoop  
6. 20161220155928  192.168.1.155   1600    900     sqoop   hahahhaha  
7. ... ...  

在数据库仓库mydb中，创建名为inlog的内部表

view plain  copy

1. use mydb;  

view plain  copy

1. create table inlog (dt string, ip string, screenx int, screeny int, word string, preword string)  
2. row format delimited fields terminated by '\t';  

创建完毕，再使用show tables查看一下创建的表的信息；

view plain  copy

1. show tables;  

创建表与创建库一样，名称不能重复，解决方法是加入if not exists

3、创建一个外部表，命名为outlog，同样用于存储solog文件中的数据。

view plain  copy

1. use mydb;  
2. create external table outlog (dt string, ip string, screenx int, screeny int, word string, preword string)  
3. row format delimited fields terminated by '\t';  

创建完毕，再使用show tables查看一下创建的表的信息；

view plain  copy

1. show tables;  

创建外部表同样可以加入if not exists，进行判断，如果表已存在，则不执行创建操作。

另外，通过对比语句可以看出，外部表较内部表而言，只是在create后加了一个external。这个是我们目前可以看到的现象。

4、下面来看一下两个表的区别。

刚才我们创建了一个内部表inlog，一个外部表outlog，在HDFS文件系统，创建两个目录

view plain  copy

1. hadoop fs -mkdir -p /logs/in  
2. hadoop fs -mkdir -p /logs/out  

然后将Linux上/data/mydata/solog文件，分别上传到HDFS的/logs/in和/logs/out目录下。

view plain  copy

1. hadoop fs -put /data/mydata/solog /logs/in  
2. hadoop fs -put /data/mydata/solog /logs/out  

下面分别将两个数据，加载到两张表中。

加载数据到内部表

view plain  copy

1. load data inpath '/logs/in/solog' into table inlog;  

加载数据到外部表

view plain  copy

1. load data inpath '/logs/out/solog' into table outlog;  

查看hdfs文件系统的变化

view plain  copy

1. hive>dfs -lsr /;  
2. lsr: DEPRECATED: Please use 'ls -R' instead.  
3. drwxr-xr-x   - zhangyu supergroup          0 2016-12-21 06:46 /logs  
4. drwxr-xr-x   - zhangyu supergroup          0 2016-12-21 06:48 /logs/in  
5. drwxr-xr-x   - zhangyu supergroup          0 2016-12-21 06:49 /logs/out  
6. drwxr-xr-x   - zhangyu supergroup          0 2016-12-20 10:09 /mydata  
7. -rw-r--r--   1 zhangyu supergroup       5171 2016-12-20 10:09 /mydata/solog  
8. drwxr-xr-x   - zhangyu supergroup          0 2016-12-20 10:10 /myout  
9. drwx-wx-wx   - zhangyu supergroup          0 2016-12-21 02:00 /tmp  
10. drwxr-xr-x   - zhangyu supergroup          0 2016-12-21 06:43 /user  
11. drwxr-xr-x   - zhangyu supergroup          0 2016-12-21 06:43 /user/hive  
12. drwxr-xr-x   - zhangyu supergroup          0 2016-12-21 06:44 /user/hive/warehouse  
13. drwxr-xr-x   - zhangyu supergroup          0 2016-12-21 06:44 /user/hive/warehouse/mydb.db  
14. drwxr-xr-x   - zhangyu supergroup          0 2016-12-21 06:48 /user/hive/warehouse/mydb.db/inlog  
15. -rwxr-xr-x   1 zhangyu supergroup       5171 2016-12-21 06:46 /user/hive/warehouse/mydb.db/inlog/solog  
16. drwxr-xr-x   - zhangyu supergroup          0 2016-12-21 06:49 /user/hive/warehouse/mydb.db/outlog  
17. -rwxr-xr-x   1 zhangyu supergroup       5171 2016-12-21 06:46 /user/hive/warehouse/mydb.db/outlog/solog  

这里可以看到一个现象，就是使用load加载数据的时候，hive会将原始数据，进行迁移，放到表对应的目录下。

下面执行删除内部表，以及删除外部表的操作。

view plain  copy

1. drop table inlog;  

view plain  copy

1. drop table outlog;  

再来查看hdfs文件系统的变化。

view plain  copy

1. hive>dfs -lsr /;  
2. lsr: DEPRECATED: Please use 'ls -R' instead.  
3. drwxr-xr-x   - zhangyu supergroup          0 2016-12-21 06:46 /logs  
4. drwxr-xr-x   - zhangyu supergroup          0 2016-12-21 06:48 /logs/in  
5. drwxr-xr-x   - zhangyu supergroup          0 2016-12-21 06:49 /logs/out  
6. drwxr-xr-x   - zhangyu supergroup          0 2016-12-20 10:09 /mydata  
7. -rw-r--r--   1 zhangyu supergroup       5171 2016-12-20 10:09 /mydata/solog  
8. drwxr-xr-x   - zhangyu supergroup          0 2016-12-20 10:10 /myout  
9. drwx-wx-wx   - zhangyu supergroup          0 2016-12-21 02:00 /tmp  
10. drwxr-xr-x   - zhangyu supergroup          0 2016-12-21 06:43 /user  
11. drwxr-xr-x   - zhangyu supergroup          0 2016-12-21 06:43 /user/hive  
12. drwxr-xr-x   - zhangyu supergroup          0 2016-12-21 06:44 /user/hive/warehouse  
13. drwxr-xr-x   - zhangyu supergroup          0 2016-12-21 06:50 /user/hive/warehouse/mydb.db  
14. drwxr-xr-x   - zhangyu supergroup          0 2016-12-21 06:49 /user/hive/warehouse/mydb.db/outlog  
15. -rwxr-xr-x   1 zhangyu supergroup       5171 2016-12-21 06:46 /user/hive/warehouse/mydb.db/outlog/solog  

执行完删除之后，在hive数据仓库下，存储数据的目录/user/hive/warehouse/mydb.db下，inlog表已经被删除。而outlog目录以及文件还在。

使用show tables查看当前都有哪些表，可以看到数据仓库中，刚才我们创建的表已经被删除。

数据表在删除的时候，内部表会连数据一起删除，而外部表只删除表结构，数据还是保留的

5、修改表的表结构。再次创建内部表inlog

view plain  copy

1. create table inlog (dt string, ip string, screenx int, screeny int, word string, preword string)  
2. row format delimited fields terminated by '\t';  

修改inlog的表结构，添加两列osname,osversion，用于存储访问者的操作系统名称、操作系统版本。

view plain  copy

1. alter table inlog add columns(osname string,osversion string);  

使用desc命令查看一下加完字段后的inlog表的表结构

view plain  copy

1. desc inlog;  

6、 修改inlog表的表名。把 inlog 表重命名为 tmp1。

view plain  copy

1. alter table inlog rename to tmp1;  

可以使用show tables再次查看表名的变化。

这个命令可以让用户为表更名，数据所在的位置和分区名并不改变。换而言之，旧的表名并未“释放” ，对旧表的更改会改变新表的数据。

7、创建与已知表相同结构的表。创建一个与tmp1表结构相同的表，表名为tmp2，这里要用到 LIKE 关键字。

答案：

view plain  copy

1. create table tmp2 like tmp1;  

创建完成并查看

三、向Hive表导入数据

向Hive中导入数据，主要有4中方式，分别为：

①从本地文件系统中导入数据到Hive表；

②从HDFS上导入数据到Hive表；

③从别的表中查询出相应的数据并导入到Hive表中；

④在创建表的时候通过从别的表中查询出相应的记录并插入到所创建的表中

1、从本地文件系统中导入数据到Hive表

在hive中的mydb数据仓库中，再次创建一个内部表名为inlog，字符类型为string,以“\t”为分隔符

view plain  copy

1. create table inlog (dt string, ip string, screenx int, screeny int, word string, preword string)  
2. row format delimited fields terminated by '\t'  
3. stored as textfile;  

[row format delimited]关键字，是用来设置创建的表在加载数据的时候，支持的列分隔符。

[stored as textfile]关键字是用来设置加载数据的数据类型,默认是TEXTFILE。

执行命令并查看

将本地文件系统中的文件/data/mydata/solog，导入到inlog表。

view plain  copy

1. load data local inpath '/data/mydata/solog' into table inlog;  

查询一下，inlog表中的数据，看看是否已经导入

view plain  copy

1. select * from inlog limit 10;  

2、将HDFS上的数据导入到hive表；

首先将本地/data/mydata/solog文件，上传到HDFS的/logs/in目录下。

(如果目录不存在，首先创建/logs/in目录)

view plain  copy

1. hadoop fs -mkdir -p /logs/in  

view plain  copy

1. hadoop fs -put /data/mydata/solog /logs/in  

在mydb中创建表，名为inlog2。inlog2表与inlog表结构相同

view plain  copy

1. create table inlog2 like inlog;  

接下来将HDFS下/logs/in/solog中的数据，导入到inlog2表中

view plain  copy

1. load data inpath '/logs/in/solog' into table inlog2;  

导入成功,查询一下

view plain  copy

1. select * from inlog2 limit 10;  

通过以上操作，可以看出，从HDFS中数据导入到hive表，较本地linux数据导入到hive表中，主要差别是在load data后少了local。

3、从一个表中查询出数据，并导入到另一个hive表中

在hive中创建一个inlog3表，inlog3表的表结构和inlog表结构相同。

view plain  copy

1. create table inlog3 like inlog;  

将inlog表中的数据，导入到inlog3表中。

view plain  copy

1. insert into table inlog3 select * from inlog;  

如果在inlog3中已经存在一部分数据，如果想要从inlog表中进行查询，并将查询出的结果覆盖inlog3表中的数据，可以使用关键词overwrite

view plain  copy

1. insert overwrite table inlog3 select * from inlog;  

4、在创建表的时候通过从别的表中查询出相应的记录并插入到所创建的表中

在mydb数据仓库中，创建表，命名为inlog5，同时，将inlog表中全部的数据，插入到inlog5中。

view plain  copy

1. create table inlog5 as select * from inlog;  

创建并导入完成，查看inlog5表中的数据

view plain  copy

1. select * from inlog5 limit 10;  

四、将hive中的数据导出

将hive中的数据导出，主要有3种方式：①导出到本地文件系统；②导出到HDFS中；③导出到Hive的另一个表中。

1、导出到本地文件系统；

将hive中的inlog表导出到Linux本地文件系统/data/myout/1中

(如果目录不存在，需建立目录)

view plain  copy

1. mkdir -p /data/myout  

开始导出数据

view plain  copy

1. insert overwrite local directory '/data/myout/1' select * from inlog;  

导出成功，在本地查询一下

view plain  copy

1. cd /data/myout/1       (为目录，目录下有000000_0文件 ）  

view plain  copy

1. cat  000000_0  （发现列之间没有分割 ）  

可以使用下面的方式增加分割

view plain  copy

1. insert overwrite local directory '/data/myout/2'  
2. select dt, concat('\t',ip),concat('\t',screenx),concat('\t',screeny),concat('\t',word),concat('\t',preword) from inlog;  

加完分隔，再次查询，和导入数据到Hive不一样，不能用insert into来将数据导出

2、将数据导出到HDFS中

将hive中的inlog表中的数据，导出到HDFS中的/myout/1里

(如果目录不存在，需建立目录)

view plain  copy

1. hadoop fs -mkdir /myout/1  

开始导出数据

view plain  copy

1. insert overwrite directory '/myout/1'  
2. select dt, concat('\t',ip),concat('\t',screenx),concat('\t',screeny),concat('\t',word),concat('\t',preword) from inlog;  

切换到hadoop目录/myout/1下查询一下

view plain  copy

1. hadoop fs -cat /myout/1/000000_0  

3、导出到Hive的另一个表中

将hive中表inlog中的数据，导出到inlog6表（两表字段及字符类型相同）

首先在hive中的mydb数据仓库下，创建一个表名为inlog6

view plain  copy

1. create table inlog6 (dt string, ip string, screenx int, screeny int, word string, preword string)  
2. row format delimited fields terminated by '\t';  

view plain  copy

1. show tables;  

然后将inlog中的数据导出到inlog6中；

view plain  copy

1. insert into table inlog6 select * from inlog;  

导出完成，查看一下数据导出后的inlog6表

view plain  copy

1. select * from inlog6;  

提交报告 暂时保存