Hive的hql操作方式

本篇内容介绍了“Hive的hql操作方式”的有关知识，在实际案例的操作过程中，不少人都会遇到这样的困境，接下来就让小编带领大家学习一下如何处理这些情况吧！希望大家仔细阅读，能够学有所成！

前言：
常见hive参数设置：

//Hive 显示列名/表头
set hive.cli.print.header=true;
//hive设置本地资源执行
set hive.exec.mode.local.auto=true;
1
2
3
4
1、hive的DDL——data define language数据定义语言
1.1库的定义语言
1）创建数据库
create database  if not exists  数据库名;
1
2）切换数据库
use  数据库名；
1
3）查看所有数据库
show databases;
show databases like "test*";    所有test开头的数据库
1
2
4）查看数据库的描述信息
desc database  数据库名;
desc database  extended 数据库名;    //查看数据库的扩展信息
1
2
5)查看正在使用的数据库
select current_database();
1
6）修改数据库——不支持
7）删除数据库
drop database [if exists] 数据库名[restrict];         //只能删除空数据库
drop database 数据库名 cascade；      //级联删除非空数据库
1
2
1.2表的定义语言
1.2.1创建表
CREATE [EXTERNAL] TABLE [IF NOT EXISTS] table_name
 [(col_name data_type [COMMENT col_comment], ...)]
 [COMMENT table_comment]
 [PARTITIONED BY (col_name data_type [COMMENT col_comment], ...)]
 [CLUSTERED BY (col_name, col_name, ...)
 [SORTED BY (col_name [ASC|DESC], ...)] INTO num_buckets BUCKETS]
 [ROW FORMAT row_format]
 [STORED AS file_format]
1
2
3
4
5
6
7
8
建表语句说明：
①externel hive中的建表关键字
外部表关键字，加上后建的表就是外部表，不加默认是内部表
②if not exists 建表防止报错
if exists 删表防止报错
③comment 指定列或表的描述信息
④partitioned by(字段名 字段类型,…)
分区表用于指定分区用的，括号里的是指定分区字段用的，一般是常用过滤字段
注意：分区字段一定不是建表中字段的字段
⑤clustered by (col_name,col_name,…)
sorted by (col_name [ASC | DSC],…) into num_buckets buckets
指定分桶表相关信息
分桶字段——分桶过程中需要按照哪一个字段将大表切成小文件
clustered by（分桶字段）
into num_buckets buckets 指定分桶个数，每一个桶的数据，分桶字段.hash % 分桶个数
sorted by 指定桶中数据的排序
注意：分桶字段一定包含在建表字段中
⑥row format 指定行格式化
hive不支持update、delete，支持insert，但是效率低
hive擅长导入表中数据方式——load，之间将一个文件导入到hive表中
这里的行格式化指定的是文件每一个字段的切分依据，行之间的切分依据
⑦指定存储格式
指定的是hive表数据在hdfs存储的文件格式
TEXTFILE 文本——默认
SEQUENCEFILE——二进制
RCFILE——行列结合
⑧location 指定hive的表数据在hdfs的存储路径
hive表数据存储路径

hive-default.xml hive.metastore.warehouse.dir 默认的
hive-site.xml 修改后的
location hive表存储路径
这个表必须在hdfs上，在建表过程中指定
1.2.2创建表案例
1）内部表
create table if not exists stu_managed(
sid int,name string,sex string,age int,dept string
) comment "test one managed_table" 
row format delimited fields terminated by "," 
stored as textfile location "/data/hive/managed/stu";
1
2
3
4
5
2）外部表
create external table if not exists stu_external(
sid int,name string,sex string,age int,dept string
) comment "test one external_table" 
row format delimited fields terminated by "," ;
1
2
3
4
3）分区表
分区字段：dept

create table if not exists stu_partitioned(
sid int,name string,sex string,age int
) comment "test one partitioned_table" 
partitioned by (dept string) row format delimited fields terminated by "," ;
1
2
3
4
4）分桶表
分桶字段：age
分桶分数：3

create table if not exists stu_buk(
sid int,name string,sex string,age int,dept string
) clustered by (age) sorted by (dept desc,age asc) into 3 buckets
row format delimited fields terminated by ",";
1
2
3
4
5）表复制
like
只会复制表的字段信息，不会复制表的属性（存储位置、存储格式、权限）

create [external] table if not exists tbname1 like tbname2;
1
6）ctas建表
将一个sql查询结果存放到一张表中

create [external] table tbname as select ....
1
1.2.3查看表列表
show tables;
show tables like "stu_*";
show tables in 数据库名;
查看表的详细描述信息
desc 表名;                 //查看表字段信息
desc extended 表名;      查看表扩展信息，但是不易读
desc formatted 表名   //查看表扩展信息，格式化显示，易读
1
2
3
4
5
6
7
1.2.4表修改 alter
1）修改表名
alter table 老表名 rename to 新表名;
1
2）修改表列信息
添加一个列
alter table 表名 add columns(列名 列属性);    ——添加到表的最后
1
修改列
alter table 表名change 老列名  新列名 新列属性;    ——修改列名，类型不变
alter table 表名change 列名  列名 新属性;    ——修改类型，列名不变
1
2
注意： 修改列属性，大→小是可以的，由小→大不支持

3）删除列——不支持
4）修改表的分区信息
默认分区表中的分区是0个

①添加一个分区信息
alter table 表名 add partition(dept="IS");     约束当前分区存储的数据
//添加多个分区
alter table stu_partitioned add partition(dept="IS")
partition(dept="MA") partition(dept="CS");
1
2
3
4
②修改表的分区信息——了解
修改表的分区的存储位置
分区表的每一个分区，默认的存储位置

/user/myhive/warehouse/test_1901.db/stu_ptn/dept="is"
1
添加分区的时候直接指定这个分区的存储位置
alter table stu_ptn add if not exists partition(dept="aa")
location "/data/hive/ptn/aa";
1
2
对于已经添加的分区，set location修改分区存储位置
alter table stu_ptn partition(dept="IS") 
set location "/data/hive/ptn/is";
1
2
这个指定的路径不会立即创建，插入数据的时候才会创建，原来的路径也不会帮我们删除

③查询表的分区信息
show partitions 表名；    ——查询表的所有分区
分区字段超过一个就是多级分区，前面的字段是高级分区，后面的是低级分区
show partitions 表名 partition(高级分区);       ——查看某一分区下的所有子分区
1
2
3
④删除表的分区信息
alter table 表名 drop if exists partition(分区字段=分区名)；
1
1.2.5清空表
truncate table 表名；
1
清空表数据，不会删除表，只能内部表使用，删除表目录下的所有文件

1.2.6删除表
drop table if exists 表名;
1
1.2.7其他辅助命令
show create table 表名;    查看建表语句
1
2、hive的DML——data manage language数据操作/管理语言
2.1表数据插入
load
将数据从一个文件直接加载到hive的一个表中

LOAD DATA [LOCAL] INPATH 'filepath' [OVERWRITE] INTO TABLE
tablename [PARTITION(partcol1=val1, partcol2=val2 ...)]
1
2
语句说明：

1）load data 加载数据；
2）local从本地磁盘加载的关键字，从本地磁盘把数据加载到hive表中；不加local是从hdfs加载数据，从hdfs把数据加载到hive中 ；
3）inpath 文件的存储路径；
4）overwrite 覆盖导入，将原来表中的数据清空，不加 overwrite则追加；
5）partition 指定分区表的数据导入。

注意： 分区表的数据导入一定指定导入到哪一个分区
总结： load就是将数据放在hive表存储的hdfs路径下，hive中表就是hdfs一个路径（目录）的使用者，只要在这个路径下添加文件，就可以被表识别该文件没有限制，关联上以表结构的形式呈现出来

insert
1）单条数据插入
每次只插入一条数据
insert into table tbname values(.....);
?:
insert into table stu_copy values(1,"zs","nan",99,"ufo");
1
2
3
实际上hive的语句插入是将插入语句转换 mapreduce任务
过程如下：
先生成一个临时表（当前客户端），最终将这个临时表的数据插入到需要插入表中

2）单重数据插入
一次插入多条数据
#直接将本地文件加载到表中
load data local inpath "/home/hadoop/tmpdata/student.txt" into table stu_managed;
#从一个表中进行查询数据，将查询的结果插入到另一个表中
insert into table tbname select ...
?:
insert into table stu_copy select * from stu_managed where age=20;
1
2
3
4
5
6
3)多重数据插入
对一个表扫描一次，将多个结果插入到不同的表中

eg： stu_managed  
        age=18  age=19   stu_copy 
        age=18   age=20   stu_external
1
2
3
正常的写法：
insert into table stu_copy select * from stu_managed where age=18 or age=19;
insert into table stu_external select * from stu_managed where age=18 or age=20;
1
2
上面操作会对原始表 stu_managed扫描两次

优化写法：
对同一个表扫描只扫描一次，最终不同的结果插入到不同的表中
from tbname 
insert ...select ...where ...
insert ... select ...where...
?:
from stu_managed 
insert into stu_copy select * where age=18 or age=19 
insert into stu_external select * where age=18 or age=20;
1
2
3
4
5
6
7
2.2数据导入
2.2.1数据导入——分区表的数据导入
注意：分区表数据无法直接导入，必须指定分区

1）静态分区导入
导入数据的时候是静态指定分区名，分区名导入数据的时候是写死的

缺陷：
一定要足够了解数据有哪些分区，如果数据很大，比如有2T，分区很多的时候这个方式不太适用了
适用：
分区数比较少、分区名固定的情况下，分区表进行读取数据的时候，前面的表字段正常从表对应的文件中读取，分区字段从导入数据的时候指定的分区字段读取 分区字段存储在分区的目录上

①load的方式
可以向分区表导入数据，但是这个时候不会对导入的数据进行检查（本质就是数据文件的移动或复制） 所以这种方式在进行分区表数据导入的时候要慎重，只有当你确定这个数据一定是这个分区的时候才可以这么使用，如果不确定则不可以使用这种方式；
生产上也会使用load方式进行导入数据，一般会按照日期建分区，数据采集一般也会按照日期存储。

②insert方式
先将原始数据导入到一个普通表（非分区表）中，再从这个表结果放到分区表中

单重数据插入
insert into table tbname partition (分区字段=分区值) select ... from....
1
注意：
a、分区表数据 insert插入的时候，select的字段的个数和顺序一定要和分区表的建表字段一致；
b、插入insert或导入load 数据到分区表的时候，分区已经存在直接将数据放在分区目录下，分区不存在则自动创建这个分区。

多重数据插入
对原始表扫描一次 最终将数据插入到了多个分区中
from ...
insert ... select ... where ..
insert ... select .... where ...
1
2
3
注意：分区字段在进行查询的时候，按照普通字段查询就可以了

select * from stu_ptn where dept="MA";
只会扫描  /user/myhive/warehouse/test_1901.db/stu_ptn/dept=MA 分区下的数据
select * from stu_ptn where age=18;  全表扫描的
1
2
3
2）动态分区插入方式
根据分区字段的实际值进行动态生成分区名，这种方式插入数据只能使用insert的方式
语法：

insert into table tbname partition(分区字段名) select ... from ...
1
分区字段对应的值是根据select查询的数据来的，所以select中需要将分区字段查询出来

insert into table stu_ptn partition(dept) 
select sid,name,sex,age,dept from stu_copy;
1
2
此时会报错：

FAILED: SemanticException [Error 10096]: Dynamic partition strict
mode requires at least one static partition column. To turn this
off set hive.exec.dynamic.partition.mode=nonstrict
1
2
3
解决办法：
需要打开动态分区参数，默认没有打开动态分区

set hive.exec.dynamic.partition.mode=nonstrict;
1
注意： 分区表在建表的时候，会将分区字段放在最后

补充说明：
多级分区的时候，分区级别超过1级，分区字段的个数超过1个一般会按照日期建分区year/month/day；分区字段超过一个按照分区字段的顺序划分级别，前面的字段的级别高于后面的字段的级别

craete ... partitioned by(dept string,age int)...
1
分区：先按照dept分区，再按照age进行分区，最终目录是
stu_ptn02/dept=../age=..

2.2.2导入数据实例
1）两个分区都是静态分区
load和insert 均可，以insert为例

insert into table stu_ptn02 partition(dept="CS",age=18) 
select sid,name,sex from stu_copy where dept="CS" and age=18;
1
2
2）一静一动
注意： 静态分区必须是高级分区dept，高级分区必须是静态分区，低级分区为动态分区，否则语法报错
insert

insert into table stu_ptn02 partition(dept="MA",age) 
select sid,name,sex,age from stu_copy where dept="MA";
#目录结构如下：
/user/myhive/warehouse/test_1901.db/stu_ptn02/dept=MA/age=17
1
2
3
4
3）两个分区都是动态分区
insert

insert into table stu_ptn02 partition(dept,age) 
select sid,name,sex,dept,age from stu_copy;
1
2
2.2.2数据导入——分桶表的数据导入
1）load 方式
将表数据分成 3个文件存储
分桶依据： 分桶字段.hash % 分桶个数 ，0 1 2 针对每一条数据进行判断
原则上load方式不支持
load data local inpath “/home/hadoop/tmpdata/student.txt” into table stu_buk;
不支持load的 只能insert

2）insert方式
分桶表插入数据：设置参数

set hive.strict.checks.bucketing=false;
set hive.mapred.mode=nonstrict;
insert into table stu_buk select * from stu_copy;
1
2
3
Number of reducers (= 3) is more than 1 默认设置reducetask的个数就是3个 == 桶的个数的
查询表stu_copy数据导入stu_buk的时候，会根据建表语句中指定的分桶字段分桶，分桶个数将数据分成3个
总结：

分桶表 只能用insert方式
分桶规则
如果分桶字段整型，分桶字段 % 分桶个数；不是整型，分桶字段.hash % 分桶个数，余数相同的数据到同一个桶中
2.3数据导出
hive中提供将一个查询结果导出为一个文件

2.3.1单重数据导出
INSERT OVERWRITE [LOCAL] DIRECTORY 文件夹（本地的|hdfs） select_statemen
1
参数解释：
local 导出数据到本地
案例：

#本地：
insert overwrite local directory "/home/hadoop/hive_data" 
select * from stu_copy where age=18;
#hdfs：
insert overwrite directory "/home/hadoop/hive_data" 
select * from stu_copy where age=18;
1
2
3
4
5
6
2.3.2多重数据导出
扫描一次表，将不同的结果导出不同的目录下

from ... 
insert ...
insert ....
1
2
3
案例：

from stu_copy 
insert overwrite local directory "/home/hadoop/hive/age18" 
select * where age=18 
insert overwrite local directory "/home/hadoop/hive/age19" 
select * where age=19;
1
2
3
4
5
3、hive的DML之查询
语法顺序：

join  group by  order by  limit   where  having 
select .... from ... join...on...where...
group by...having....order by....limit ... 
1
2
3
3.1join
注意：

join 支持等值，不支持非等值
支持and连接 不支持or连接 多个连接条件的时候
select * from a join b on a.id=b.id and a.name=b.name;  支持
select * from a join b on a.id=b.id or a.name=b.name;   不支持 mapkey不好设计
1
2
支持超过2个表的连接
3.1.1join的分类
3.1.1.1内连接 inner join | join
求两个表的连接键的交集,两个表都有返回

3.1.1.2外连接 outer join
左外 left outer join == left join
join 左侧表作为基础表，左侧表有的则返回，没有的不要了，左有右没有就补null

select * from a left join b on a.id=b.id; 
    结果
    1       zs      1       23
    2       ls      2       45
    4       ww      NULL    NULL
1
2
3
4
5
3.1.1.3右外 right outer join == right join
join 右侧表作为基础表，右表有的则返回，没有的不要了，右有左没有就补null

select * from a right join b on a.id=b.id;
结果：
1       zs      1       23
2       ls      2       45
NULL    NULL    3       56
1
2
3
4
5
3.1.1.4全外 full outer join == full join
求两个表的并集，两个表中有的所有的关联建都会返回，哪一个对应的关联建有数据，补充数据，没有数据补null

select * from a full join b on a.id=b.id;
结果：
1       zs      1       23
2       ls      2       45
NULL    NULL    3       56
4       ww      NULL    NULL
1
2
3
4
5
6
3.1.1.5半连接 semi join
左半连接 left semi join
mysql in/exits 判断字段值是否包含在我们给定的范围中
select * 
from a where id in (select id from b);
hive2中这个语句可以执行但是执行效率低  mapkey hive1中不支持 
1
2
3
半连接就是高效解决 in /exists 问题

select * from a left semi join b on a.id=b.id;
1
最终取的结果a b进行内连接，取的是左半表；
最终返回的是a表中在b表中出现过的a所有的数据。
另一种实现方式：

select a.* from a join b on a.id=b.id;
1
内连接取左部分的表

3.2group by
group by后面的字段相同的分到一组中
注意：group by 使用的时候是有很大的限制的

3.2.1使用group by时select后面的字段(需要查询的字段)有严格的限制
只能跟两种类型的字段

1）group by的分组字段
select dept from stu_copy group by dept;
1
2)根据分组字段的进行的聚合函数
聚合函数 多–>一条，如sum max avg min…

select dept,max(age) from stu_copy group by dept;
错误示范：
select dept,sid,max(age) from stu_copy group by dept;
1
2
3
3.2.2group by 的执行顺序在select 执行之前 group by中不能使用select中字段的别名
错误的
select dept d,max(age) from stu_copy group by d;
正确的：
select dept d,max(age) from stu_copy group by dept;
1
2
3
4
3.3where和having
where:
where是对聚合函数之前的数据进行过滤的，where就是为聚合函数准备数据，需要聚合的数据先进行一步过滤再聚合。
having:
执行顺序是在聚合函数之后，针对聚合之后的结果进行过滤。

3.4order by
注意： order by 的执行顺序在select之后，可以使用select中的别名
在hive中order by这个位置4个语法：

order by 用于全局排序，无论启动多少个reducetask最终全局有序
sort by 用于局部排序，每一个reducetask的运行结果有序的，不保证全局有序
distribute by 用于字段分配
reducetask的个数需要手动指定 set mapreduce.job.reduces=2;
按照指定的字段进行分配reducetask的数据
distribute by 分+ sort by 排序
需要指定字段进行分 reducetask （mapreduce 分区） 指定字段排序
先要按照性别分成两个reducetask，再在每一个reducetask中按照年龄排序

select * from stu_copy distribute by sex sort by age;
1
划分依据： sex .hash % reducetasks
cluster by 用于先按照指定字段切分reducetask的数据再按照指定字段进行升序排序=distribute by 字段 + sort by 字段
调整 reducetask的个数：
set mapreduce.job.reduces=2;
当distribute by 的字段和sort by的字段一致的时候，并且升序可以使用cluster by 替换

按照age 切分，按照age升序

select * from stu_copy cluster by age;
select * from stu_copy distribute by age sort by age;
1
2
3.5limit
全局前几个，所有数据的前几个

select * from stu_copy limit 1;
1
补充：hql语句转换为mr时候日志中显示和解释

In order to change the average load for a reducer (in bytes):
    每一个reducer平均加载的字节数
  set hive.exec.reducers.bytes.per.reducer=<number>
In order to limit the maximum number of reducers:
    reducetask最大限制
  set hive.exec.reducers.max=<number>  1009 
In order to set a constant number of reducers:
    设置reducetask的个数的
  set mapreduce.job.reduces=<number>  -1 
  0  没有reducetask  需要的时候1 
  特例 分桶表插入数据的时候  reducetask=== 桶的个数
————————————————
 

“Hive的hql操作方式”的内容就介绍到这里了，感谢大家的阅读。如果想了解更多行业相关的知识可以关注亿速云网站，小编将为大家输出更多高质量的实用文章！