Hive的常用HiveQL操作
1、HiveQL概述
首先,我们简单叙述一下HiveQL的基本数据类型。
Hive支持基本数据类型和复杂类型, 基本数据类型主要有数值类型(INT、FLOAT、DOUBLE ) 、布尔型和字符串, 复杂类型有三种:ARRAY、MAP 和 STRUCT。
基本数据类型:
类型 |
描述 |
TINYINT |
1个字节 |
SMALLINT |
2个字节 |
INT |
4个字节 |
BIGINT |
8个字节 |
FLOAT |
4个字节,单精度浮点型 |
DOUBLE |
8个字节,双精度浮点型 |
BOOLEAN |
TRUE/FALSE,布尔型 |
STRING |
字符串 |
复杂数据类型:
类型 |
描述 |
ARRAY |
有序字段 |
MAP |
无序字段 |
STRUCT |
一组命名的字段 |
2、HiveQL操作命令
Hive常用的HiveQL操作命令主要包括:数据定义、数据操作。接下来详细介绍一下这些命令即用法:
2.1 数据定义
2.1.1 创建、修改和删除数据库
create database if not exists hive; #创建数据库
show databases; #查看Hive中包含的数据库
show databases like 'h.*'; #查看Hive中以h开头的数据库
describe database hive; #查看hive数据库位置等信息
![](https://img-blog.csdnimg.cn/2ff7704e0c1e4a78a5677a902250fd91.png#pic_left)
alter database hive set dbproperties('edited-by'='lily'); #为hive数据库设置键值对属性
![](https://img-blog.csdnimg.cn/f068152c1e154eb397c43b85e7f6b6f2.png#pic_left)
use hive; #切换到hive数据库下
drop database if exists hive; #删除不含表的数据库
drop database if exists hive cascade; #删除数据库和它中的表
![](https://img-blog.csdnimg.cn/416fa3c29efb4e36a469e01179161e66.png#pic_left)
注意:除 dbproperties属性外,数据库的元数据信息都是不可更改的,包括数据库名和数据库所在的目录位置,没有办法删除或重置数据库属性。
2.1.2 创建、修改和删除表
创建内部表(管理表):
create table if not exists hive.usr(
name string comment 'username',
pwd string comment 'password',
address struct<street:string,city:string,state:string,zip:int> comment 'home address',
identify map<int,tinyint> comment 'number,sex')
comment 'description of the table'
tblproperties('creator'='me','time'='2016.1.1');
![](https://img-blog.csdnimg.cn/f5dd60c7698f438b8cb0a79046e079b0.png#pic_left)
创建外部表:
create external table if not exists usr2(
name string,
pwd string,
address struct<street:string,city:string,state:string,zip:int>,
identify map<int,tinyint>)
row format delimited fields terminated by ','
location '/usr/local/hive/warehouse/hive.db/usr';
![](https://img-blog.csdnimg.cn/c33eb26795734aa9ae8d7aa6b9e9eb9b.png#pic_left)
创建分区表:
create table if not exists usr3(
name string,
pwd string,
address struct<street:string,city:string,state:string,zip:int>,
identify map<int,tinyint>)
partitioned by(city string,state string);
![](https://img-blog.csdnimg.cn/954d183497334c14b90509614eb707b7.png#pic_left)
复制usr表的表模式:
create table if not exists hive.usr1 like hive.usr;
![](https://img-blog.csdnimg.cn/c4962f7ad391455588e4ba10f40efc58.png#pic_left)
查看表及相关信息:
show tables in hive;
show tables 'u.*'; #查看hive中以u开头的表
describe hive.usr; #查看usr表相关信息
alter table usr rename to custom; #重命名表
![](https://img-blog.csdnimg.cn/d92c4ccce4ec42edbca1e9649dd473c5.png#pic_left)
修改信息:
alter table usr1 add columns(hobby string); #增加列
alter table usr1 replace columns(uname string); #删除替换列
alter table usr1 set tblproperties('creator'='liming'); #修改表属性
![](https://img-blog.csdnimg.cn/4d8cc501da1c4e708f8ae9eb33eec8ea.png#pic_left)
删除表:
use hive; #切换到hive数据库下
drop table if exists usr1; #删除表
drop database if exists hive cascade; #删除数据库和它中的表
![](https://img-blog.csdnimg.cn/76ea6e491dfa4d42b2067cf2e2f8cf84.png#pic_left)
2.1.3 视图/索引创建、修改和删除
create database if not exists hive; #创建数据库
create table if not exists usr(id bigint,name string,age int); #创建表
create view little_usr as select id,age from usr; #创建视图
alter view little_usr set tblproperties('create_at'='refer to timestamp'); #修改视图
![](https://img-blog.csdnimg.cn/e4ad67beac93452ca8d6952a3fad3b27.png#pic_left)
因为视图是只读的,所以 对于视图只允许改变元数据中的 tblproperties属性。
2.1.4 用户自定义函数
在新建用户自定义函数(UDF)方法前,先了解一下Hive自带的那些函数。
以下命令会显示Hive中所有的函数名称:
show functions;
![](https://img-blog.csdnimg.cn/043b4a67b0bd4ad79c1587e9ee5445f2.png#pic_left)
…
![](https://img-blog.csdnimg.cn/b6b977aaaec84c15bc602cd110cd3b31.png#pic_left)
若想要查看具体函数使用方法可使用describe function 函数名
:
describe function abs;
![](https://img-blog.csdnimg.cn/ed90c6e812d1421a809b77810be9e99f.png#pic_left)
2.2 数据操作
主要实现的是将数据装载到表中(或是从表中导出),并进行相应查询操作,对熟悉SQL语言的用户应该不会陌生。
2.2.1 向表中装载数据
这里我们以只有两个属性的简单表为例来介绍。首先创建表stu和course,stu有两个属性id与name,course有两个属性cid与sid。
create table if not exists hive.stu(id int,name string)
row format delimited fields terminated by '\t';
create table if not exists hive.course(cid int,sid int)
row format delimited fields terminated by '\t';
![](https://img-blog.csdnimg.cn/0be02ab6157640baa89ab5bfd920a863.png#pic_left)
向表中装载数据有两种方法:从文件中导入和通过查询语句插入。
(1)从文件中导入:
假如这个表中的记录存储于文件stu.txt中,该文件的存储路径为/usr/liang/stu.txt
,内容如下。
1 leo
2 @
3 liang
下面我们把这个文件中的数据装载到表stu中,操作如下:
load data local inpath '/usr/liang/stu.txt' overwrite into table stu;
![](https://img-blog.csdnimg.cn/f57e7264f7494c9fb0996b4bdf0459db.png#pic_left)
如果stu.txt文件存储在HDFS 上,则不需要 local 关键字。
(2)通过查询语句插入:
使用如下命令,创建stu1表,它和stu表属性相同,我们要把从stu表中查询得到的数据插入到stu1中:
create table stu1 as select id,name from stu;
![](https://img-blog.csdnimg.cn/799d56f4e2ef43f49692aa54718287b6.png#pic_left)
上面是创建表,并直接向新表插入数据;若表已经存在,向表中插入数据需执行以下命令:
insert overwrite table stu1 select id,name from stu where(条件);
这里关键字overwrite的作用是替换掉表(或分区)中原有数据,换成into关键字,直接追加到原有内容后。
2.2.2 从表中导出数据
a.可以简单拷贝文件或文件夹
hadoop fs -cp source_path target_path;
b.写入临时文件
insert overwrite local directory '/usr/local/liang/stu' select id,name from stu;
2.2.3 查询操作
和SQL的查询完全一样,这里不再赘述。
主要使用select…from…where…等语句,再结合关键字group by、having、like、rlike等操作。
这里我们简单介绍一下SQL中没有的case…when…then…句式、join操作和子查询操作:
select id,name,
case
when id=1 then 'first'
when id=2 then 'second'
else 'third'
end from stu;
![](https://img-blog.csdnimg.cn/5f0bdfe87a554aa0a1e3d5322a88603b.png#pic_left)
2.2.4 连接
连接(join)是将两个表中在共同数据项上相互匹配的那些行合并起来, HiveQL 的连接分为内连接、左向外连接、右向外连接、全外连接和半连接 5 种。