HiveQL简介之内部表、分区表和外部表

HiveQL简介之内部表、分区表和外部表

本简介使用Hive远程模式（MySQL）

一、Database基本使用

执行hive命令打开Hive命令行，如图：

创建如下3个数据库：

a、数据库d1

数据库d1中无任何表

HiveQL语句：create database d1;

如图：

成功创建后，HDFS如图：

数据库d1以d1.db目录名称存在。

b、数据库d2

数据库中有一张stu表，表中无数据

HiveQL语句：create database d2;

create table d2.stu(id int,name string,age int);

如图：

HDFS数据仓库目录如下：

HDFS数据库d2目录如下：

HDFS表stu目录如下：

c、数据库d3

有一张stu表，表中有3条数据

HiveQL语句：create database d3;

create table d3.stu(id int,name string,age int);

insert into d3.stu values(1,"Tom",20);

insert into d3.stu values(2,"Mike",28);

insert into d3.stu values(3,"Jone",34);

如图：

Hive引擎将数据插入语句转换成了MapReduce任务执行，日志显示，这种方式将来会被丢弃，推荐使用Spark on Hive。

HDFS数据文件如图：

每条数据生成一个HDFS文件,每个文件大小是128M。当然，这种方式如此消耗空间，是不允许的！后我们采用另外一种方式加载数据。

问题1：前面在创建表时为何要使用数据库名称作为引用，例如d2.stu、d3.stu，如果不使用会出现什么情况，

我们在hive命令行直接执行HiveQL语句：create table stu(id int,name string,age int);，如图：

HDFS数据仓库目录如图：

我们发现，stu表与三个数据库在同一级目录！表不是应当在数据库中吗？没错！我们执行

HiveQL语句：select current_database();

如图：

确实在数据库中！在Hive中默认数据库就是default！我们可以使用

HiveQL语句：use databasname;

对数据库进行切换！例如：

我们就成功切换到d2数据库中！此次可以对数据库中的表进行操作！可以使用

HiveQL语句：use default;

再次切换到默认数据库！

我们登录MySQL实例hive数据库查看元数据存储情况，数据库元数据保存在表DBS中，如下：

表元数据存储在TBLS表中，如下：

确实，我们刚才创建了三张stu表，类型都是managed table，这是Hive创建表时的默认类型。

字段元数据存储在COLUMNS_V2表中，如图：

下面我们将删除三个数据库，

HiveQL：drop database d1;

drop database d2;

drop database d3;

如图：

d1数据库被成功删除！d2和d3删除失败，原因是当前数据库中含有表！在HiveQL语句末尾加上关键字cascade，进行级联删除，如图：

HDFS数据仓库如图：

数据库目录也已经成功，我们再登录MySQL实例hive数据库查看元数据是否还在。如图：

DBS表：

TBLS表：

COLUMNS_V2表：

关于三个数据库的元数据全部被删除！

二、Managed Table或者Internal Table数据加载

1、数据准备

我们准备两份数据，一份存储在Linux本地文件系统/root/temp/csv目录下，另一份存储在HDFS文件系统/input目录下，如图：

Linux本地文件系统：

HDFS文件系统：

emp和dept两个文件数据格式如下：

均采用逗号分隔！

2、HDFS数据加载

（1）创建scott数据库

HiveQL语句：create database scott;

（2）创建emp和dept表

在数据库scott中创建emp和dept表，如下：

HiveQL语句：create table emp(

empno int,

ename string,

job string,

mgr string,

hiredata string,

sal int,

comm string,

deptno int

) row format delimited fields terminated by ',';

create table dept(

deptno int,

dname string,

loc string

) row format delimited fields terminated by ',';

如图：

HDFS文件系统scott数据库如下：

在创建表的HiveQL语句末尾我们对字段分隔符进行了声明，例如：row format delimited fields terminated by ','，要求字段使用逗号分隔，如果不申明，表创建时默认使用分隔符Ctrl+A。

（3）加载HDFS文件系统数据

HiveQL语句：load data inpath "/input/dept.csv" into table dept;

load data inpath "/input/emp.csv" into table emp;

如图：

通过查询表dept和emp，说明数据已经加载成功！我们再查看HDFS文件系统中发生了什么，如下：

HDFS表目录：

原数据目录/input：

我们发现，两个数据文件从HDFS文件系统/input中转移到了scott数据库表中！事实上，两个文件的本质并没有变化，只是在HDFS文件系统中路径发生变化！

（4）加载本地文件系统数据

a、同上，我们创建两个表empLocal和deptLocal

如图：

HDFS文件系统scott数据库目录：

我们看到文件系统对表名大小写不敏感！

b、数据加载

在HDFS文件系统数据加载HiveQL语句中加入local关键字，并修改数据文件路径，如下：

HiveQL语句：load data local inpath "/root/temp/csv/dept.csv" into table deptlocal;

load data local inpath "/root/temp/csv/emp.csv" into table emplocal;

如图：

HDFS文件系统scott数据库表emplocal目录：

我们查看Linux系统数据文件是否存在，如下：

结果表明，本地文件系统数据加载的本质是将数据从本地文件系统复制到HDFS文件系统中！

三、Partitioned Tables分区表

下面，我们来创建一张emp的部门分区表emp_partition，查询管理表emp和分区表emp_partition部门号是10的员工信息，并比较两者的查询速度！

1、创建分区表emp_partition

HiveQL语句：create table emp_partition(

empno int,

ename string,

job string,

mgr string,

hiredate string,

sal int,

comm string

) partitioned by (deptno int)

row format delimited fields terminated by ',';

与管理表不同的是，分区表中deptno字段单独作为partitioned by子句定义。如图：

HDFS数据库scott目录如下：

2、加载数据

我们使用select子句向emp_partition分区表中加载数据，如下：

HiveQL语句：insert into table emp_partition partition(deptno=10)

select empno,ename,job,hiredate,mgr,sal,comm from emp where deptno=10;

insert into table emp_partition partition(deptno=20)

select empno,ename,job,hiredate,mgr,sal,comm from emp where deptno=20;

insert into table emp_partition partition(deptno=30)

select empno,ename,job,hiredate,mgr,sal,comm from emp where deptno=30;

如图：

当然，Hive引擎会将插入语句转换成MapReduce任务执行！

3、查询部门10的员工信息

emp管理表HiveQL语句：select * from emp where deptno=10

emp_partition管理表HiveQL语句：select * from emp_partition where deptno=10

查询结果如图：

我们发现对管理表和分区表的HiveQL查询并没有任何区别，结果也是一致！

emp管理表执行计划HiveQL语句：

explain select * from emp where deptno=10;

emp_partition管理表执行计划HiveQL语句：

explain select * from emp_partition where deptno=10;

结果如图：

管理表执行查询时，扫描了全部14条数据，扫描数据大小589字节。

分区表执行查询时，进扫描了3条数据，扫描数据大小113字节。因为数据量小，相比于管理表查询，查询速度提升不明显。如果是PB级数据，分区表查询性能提升是非常明显的！

四、External Tables外部表

前面，我们看到，删除数据库时，数据库中的表、表中数据以及存储在MySQL中的元数据都会被删除！有时候我们希望删除表时，能够保存其中的数据！注意到，在Hive中数据是以文件的形式单独存储在HDF文件系统中，我们思考能否有一种办法，在创建表时，让表只是与数据建立起一种链接，删除表时，只是删除这种链接！Hive为我们提供了这样一种表，就是所谓的外部表！

1、数据准备

在Linux系统/root/temp/txt/目录下创建三个数据格式相同的文件stu1.txt，stu2.txt，stu3.txt，如图：

数据格式如下：

然后将三个文件上传至HDFS文件系统/student目录下，如图：

2、创建外部表

外部表创建需要用external关键字声明，并在末尾使用location子句说明链接的HDFS文件路径，如下：

HiveQL语句：create external table stu_ext(

id int,

name string,

age int

) row format delimited fields terminated by ','

location '/student';

如图：

stu_ext外部表已经创建成功！在scott数据库中并没有真正的创建该表，事实上在数据仓库warehouse中没有任何关于stu_ext的足迹！当然该表的元数据会保存在MySQL的表TBLS中，如图：

三个数据文件也无任何变化，表语数据之间只是建立的一种看不见的链接而已。

3、查询外部表

在上面，我们看到使用查询语句可以查询出在/student目录下的三个文件。

4、删除外部表

HiveQL语句：drop table stu_ext;

如图：

表已经成功删除。当然关于该表的元数据也会被清空，如图：

三个文件数据并没有受到任何的影响，如图：