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;
如图:
表已经成功删除。当然关于该表的元数据也会被清空,如图:
三个文件数据并没有受到任何的影响,如图:
领取专属 10元无门槛券
私享最新 技术干货