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;

如图:

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

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

  • 发表于:
  • 原文链接https://kuaibao.qq.com/s/20180608G1ZTES00?refer=cp_1026
  • 腾讯「云+社区」是腾讯内容开放平台帐号(企鹅号)传播渠道之一,根据《腾讯内容开放平台服务协议》转载发布内容。

同媒体快讯

扫码关注云+社区

领取腾讯云代金券