Hive(3)-内部表外部表测试

1、内部表测试
1.1 创建数据库hivedb：
hive> create database hivedb
#强制删除数据库：hive> drop database hivedb cascade;

1.2 创建表employee
假设需要使用CREATE TABLE语句创建一个名为employee表。下表列出了employee表中的字段和数据类型：

Sr.No   字段名称       数据类型1        Eid          int2        Name         String3        Salary       Float4        Designation  string

下面的数据是一个注释，行格式字段，如字段终止符，行终止符，并保存的文件类型。
COMMENT ‘Employee details’
FIELDS TERMINATED BY ‘\t’
LINES TERMINATED BY ‘\n’
STORED IN TEXTILE

hive> CREATE TABLE IF NOT EXISTS employee ( eid int, name String,salary String, destination String)COMMENT 'employee details'ROW FORMAT DELIMITEDFIELDS TERMINATED BY '\t'LINES TERMINATED BY '\n'STORED AS TEXTFILE;

.3 加载数据**
一般来说，在SQL创建表后，我们就可以使用INSERT语句插入数据。但在Hive中，可以使用LOAD DATA语句插入数据。
同时将数据插入到Hive，最好是使用LOAD DATA来存储大量记录。有两种方法用来加载数据：一种是从本地文件系统，第二种是从Hadoop文件系统。
1.3.1 加载数据的语法
LOAD DATA [LOCAL] INPATH ‘filepath’ [OVERWRITE] INTO TABLE tablename
[PARTITION (partcol1=val1, partcol2=val2 …)]
• LOCAL是标识符指定本地路径。它是可选的。
• OVERWRITE 是可选的，覆盖表中的数据。
• PARTITION 这是可选的
数据文件可以从HDFS或者本地操作系统加载到表中，如果加载HDFS文件使用LOAD DATA INPATH，而加载本地操作系统文件使用LOAD DATA LOCAL INPATH命令。HIVE表保存的默认路径在${HIVE_HOME}/conf/hive-site.xml配置文件的hive.metastore.warehouse.dir属性指定，当创建表时会在hive.metastore.warehouse.dir指向的目录下以表名创建一个文件夹，在本演示中表默认指向的是/user/hive/warehouse。

1.3.2 示例
我们将插入下列数据到表中。在/home/user目录中名为sample.txt的文件。
注意数字间tab隔开，根据定义表的时候决定，否则插入失败
1201 Gopal 45000 Technical manager
1202 Manisha 45000 Proof reader
1203 Masthanvali 40000 Technical writer
1204 Kiran 40000 Hr Admin
1205 Kranthi 30000 Op Admin
下面的查询加载给定文本插入表中。

hive> LOAD DATA LOCAL INPATH '/usr/local/sample.txt' OVERWRITE INTO TABLE employee;

下载成功完成，能看到以下回应：
OK
Time taken: 15.905 seconds
hive>

1.3.3 查询
select * from employee;//不会转换成MapReduce任务
查询行数
可以用count关键字查询SogouQ2.txt数据行数，查询时会启动MapReduce进行计算，Map的个数一般和数据分片个数对应，在本查询中有2个Map任务（数据文件有2个Block），1个Reduce任务。
hive>select count(*) from employee;
输出：5

模糊查询：
可以用like关键字进行模糊查询，Map的个数一般和数据分片个数对应。
hive> select count(*) from employee where destination like ‘%Admin%’;
输出：2

排序查询：(降序：desc)
hive> SELECT eid, name, salary FROM employee ORDER BY salary ;
Total MapReduce CPU Time Spent: 5 seconds 270 msec
OK
1205 Kranthi 30000
1204 Kiran 40000
1203 Masthanvali 40000
1202 Manisha 45000
1201 Gopal 45000
Time taken: 45.302 seconds, Fetched: 5 row(s)
hive>

分组查询：
hive> SELECT salary,count(*) FROM employee GROUP BY salary;
成功执行查询后，能看到以下回应：
MapReduce Jobs Launched:
Stage-Stage-1: Map: 1 Reduce: 1 Cumulative CPU: 5.51 sec HDFS Read: 7579 HDFS Write: 24 SUCCESS
Total MapReduce CPU Time Spent: 5 seconds 510 msec
OK
30000 1
40000 2
45000 2
Time taken: 44.023 seconds, Fetched: 3 row(s)
hive>

1.3.4 查看HDFS文件系统
查看HDFS文件系统，可见：所有的数据库都对应/user/hive/warehouse下生成一个
.db结尾的目录
[root@hdp-node-01 local]# hadoop fs -ls /user/hive/warehouse
/user/hive/warehouse/hive.db
/user/hive/warehouse/hivedb.db
/user/hive/warehouse/test

且内部表存储在hivedb.db目录下
[root@hdp-node-01 local]# hadoop fs -ls /user/hive/warehouse/hivedb.db
/user/hive/warehouse/hivedb.db/employee

sample.txt存储在以表名为目录名的目录下面
[root@hdp-node-01 local]# hadoop fs -ls /user/hive/warehouse/hivedb.db/employee
/user/hive/warehouse/hivedb.db/employee/sample.txt

2、外部表测试
在删除表的时候，内部表将删除表的元数据和数据文件；而删除外部表的时候，仅仅删除外部表的元数据，不删除数据文件
2.1 在HDFS创建外部表存放数据目录
hadoop fs -mkdir -p /hivedb/hive

2.2 在Hive创建外部表，指定表存放目录
hive>CREATE EXTERNAL TABLE IF NOT EXISTS employee2 ( eid int, name String,salary String, destination String)
COMMENT ‘employee details’
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ‘\t’
LINES TERMINATED BY ‘\n’
STORED AS TEXTFILE
LOCATION ‘/hivedb/hive’;

hive>show tables;
观察一下创建表和外部表的区别，会发现创建外部表多了EXTERNAL关键字以及指定了表对应存放文件夹LOCATION ‘/hivedb/hive’

2.3 加载数据文件到外部表对应的目录中
创建Hive外部表关联数据文件有两种方式:
一种是把外部表数据位置直接关联到数据文件所在目录上，这种方式适合数据文件已经在HDFS存在;
另外一种方式是创建表时指定外部表数据目录，随后把数据加载到该目录下。以下将以第二种方式进行演示：
hadoop fs -copyFromLocal /usr/local/sample.txt /hivedb/hive/
hadoop fs -ls /hivedb/hive/
hadoop fs -tail /hivedb/hive/sample.txt //将文件写到标准输出

2.4 查询行数
hive>select count(*) from employee2;

2.5 显示前3行
hive>select * from employee2 limit 3;
可以看出Hive会根据查询不同任务决定是否生成Job，获取前3条并没有生成Job，而是得到数据后直接进行显示。

2.6 查看HDFS文件系统
在/hivedb/hive/下存在sample.txt文件
[root@hdp-node-01 local]# hadoop fs -ls /hivedb/hive
/hivedb/hive/sample.txt

在/user/hive/warehouse/hivedb.db/下没有与employee2同名的目录

注：若不指定存放位置，则默认存放到hive/warehouse/hivedb.db/下
CREATE EXTERNAL TABLE IF NOT EXISTS employee3 ( eid int, name String,salary String, destination String)
COMMENT ‘employee details’
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ‘\t’
LINES TERMINATED BY ‘\n’
STORED AS TEXTFILE;

LOAD DATA LOCAL INPATH ‘/usr/local/sample.txt’ OVERWRITE INTO TABLE employee3;

[root@hdp-node-01 local]# hadoop fs -ls /user/hive/warehouse/hivedb.db/
drwxr-xr-x - root supergroup 0 2017-08-21 21:47 /user/hive/warehouse/hivedb.db/employee
drwxr-xr-x - root supergroup 0 2017-08-21 22:46 /user/hive/warehouse/hivedb.db/employee3