Hive的基本语法

最新推荐文章于 2024-05-02 13:32:23 发布

原创最新推荐文章于 2024-05-02 13:32:23 发布 · 583 阅读

2 ·

CC 4.0 BY-SA版权

文章标签：

#hive

Hive 专栏收录该内容

4 篇文章

订阅专栏

本文详细介绍了 Hive 的基本语法，包括 DDL（数据定义语言）的库和表操作，如创建、查看、删除和修改表，以及 DML（数据管理语言）的数据导入、分区表的操作。还探讨了 Hive 的 DQL（数据查询语言），如连接、过滤、分组、排序和数据导出等概念。重点讲解了分区表和分桶表的数据加载，并对比了静态和动态分区的差异。

DDL

DDL：data-define-lauguage 数据定义语言

库操作

建库：create database if not exists dbname;

切换库 ：use dbname;

查询正在使用的库：select current_database();

查询库列表

查询所有数据库：show databases;
模糊查询：show databases like "*test";
查询库的详细描述信息：desc database dbname;

删除数据库

删除空数据库：drop database if exists dbname;
级联删除：drop database dbname cascade;

表操作

创建表

建表语句

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]
 [location hdfs_path]

external ：外部表关键字，不加时创建的是内部表
if not exists ：防止报错，当表不存在时创建表
comment ：指定字段或表的描述信息
partitioned by（col_name data_type 字段名与类型） ：指定分区表的分区字段，注意分区字段一定不能在建表字段中，分区字段是单独存储的
clustered by (col_name, col_name, …) [sorted by (col_name [asc|desc], …)] into num_buckets buckets]：指定分桶，clustered by 指定分桶标志，into num_buckets buckets 指定分桶个数，sorted by指定桶中的排序规则，注意分桶字段一定是建表字段中的某一个或某几个
row format row_format ：指定行格式化，指定字段分割符（指的是存储文件的分隔符而不是加载文件的分隔符，加载文件只是把文件复制到hdfs的指定路径下，当使用插入方式或通过hbase表解析出来是存储的文件中的字段是按照指定分割符分割的）

delimited lines terminated by \n 行分割符
delimited fields terminated by \001 字段分割符

stored as file_format ：指定表数据的存储格式

textfile ：默认的存储格式
SequenceFile ：二进制的存储格式
RCFile ：行列结合的形式存储
ORCFile：以hanlie结合的形式存储，是RCFile存储格式的优化

location ：指定存储位置，如果不指定是默认存储在 /user/hive/warehouse下

示例：创建表来存储学生的信息（编号 int，姓名 string，性别 string，年龄 int，部门 string）

创建内部表

create table if not exists stu_managed(sid int,name string,sex string,age int,dept string) row format delimited fields terminated by ",";

创建外部表

create external table if not exists stu_external(sid int,name string,sex string,age int,dept string) row format delimited fields terminated by ",";

创建分区表

分区字段 dept

create table if not exists stu_ptn_dept(sid int,name string,sex string,age int) partitioned by (dept string) row format delimited fields terminated by "," location "/user/hivedata/stu_ptn";

分区字段 sex

create table if not exists stu_ptn_sex(sid int,name string,age int,dept string) partitioned by (sex string) row format delimited fields terminated by ",";

创建分桶表：分桶字段 age 分桶个数 3

create table if not exists stu_buk(sid int,name string,sex string,age int,dept string) clustered by (age) sorted by(sid) into 3 buckets row format delimited fields terminated by ",";

表复制：create table tbname like tbname1;仅复制表结构，不复制表数据和属性

create external table stu_copy like stu_buk;

ctas建表：create table tbname as select ....将查询结果存储在一个表中，使用ctas建表时默认的字段名称为_c0,_c1…

create table tbname as select * from stu_managed;

查看表

查看表信息：desc [extended] [formatted] 库,表,分区,视图...
查看表的字段信息：desc tbname;
查看表的扩展信息：desc extended tbname;
格式化显示表的详细信息：desc formatted tbname;
查看表列表：

查看当前数据库下的表：show tables;
查看指定数据库下的表：show tables in dbname;

查看建表语句：show create table table_name;

删除表

drop table tbname;

修改表

修改表名：alter table tbname rename to tbname_new;
修改字段

添加字段：alter table tbname add columns(column_name column_type);
修改字段：alter table tbname change old_column new_column new_type;字段小类型可以修改为大类型，但是大类型不可以修改为小类型
删除字段：Hive中不支持删除字段
替换字段 alter table tbname replace columns (col type);,相当于删除表后重新定义字段，替换字段后表中的数据还存在

修改分区
注意事项

修改分区只针对分区表，对其他表格添加分区时会报错
分区表相当于将原始表的字段分为建表字段与分区字段
分区字段是分区的标志

添加单个分区：alter table tbname add partition(需要添加的分区);

alter table stu_ptn_dept add partition(dept="CS");  
//这个分区存储都是dept 为CS的数据

添加多个分区：alter table tbname add partition(需要添加的分区) partition(分区) partition(分区);

alter table stu_ptn_dept add partition(dept="IS") partition(dept="MA");

删除分区：alter table tbname drop partition(分区名);

alter table stu_ptn_dept drop partition(dept="CS");

修改分区的存储路径

添加分区的时候指定一个新的路径：alter table tname add partition(分区名) location "路径名";

alter table stu_ptn_dept add partition(dept="CS") location "/user/ptn/data/dept";

修改已经添加过的分区的路径：alter table tname partition(分区名) set location "新路径";修改路径后不会直接显示，等添加数据时才会创建新的分区路径

alter table stu_ptn_dept partition(dept="CS") set location "/user/hivedata/stu_ptn";

查询某一个表的分区：show partitions tbname;

清空数据：truncate table tbname;

只清空表的数据，保留表的结构
只适用于内部表，原因：不清空元数据

DML

data-manager-lauguage 数据管理语言

数据导入

数据加载

语法：load data [local] inpath "路径" into table tbname [partition()];local代表从本地（hive所在节点）加载数据，不加local表示从hdfs加载数据

从本地加载数据，相当于将本地文件上传到hive表所在的路径下，本地文件仍然存在

load data local inpath "/home/hadoop/hive_data/student.txt" into table stu_managed;

从hdfs加载数据，相当于将hdfs上存储数据的文件移动到hive表所在的路径下，原路径文件将消失

load data inpath "/hive_data" into table stu_managed;

本质

将文件传入hive的表所在的hdfs的路径下
如果手动将一个数据直接上传到hive的表路径下，hive也是可以解析到的
hive相当于hdfs的一个路径的使用者

数据插入

单条数据插入：insert into table tbname values();每次插入一条数据，效率很低

insert into table stu_managed values(1,"zs","f",45,"CS");

单重数据插入：insert into table tbname select .....执行一次插入操作插入多条数据，适用于将一个表的查询结果插入到另一个表中

insert into table stu_external select * from stu_managed;

多重数据插入：只对原始数据表扫描一次，可以向多个表或一个表的多个分区中插入数据

from tbname
insert into table tbname01 select ... where .....
insert into table tbname02 select .... where ....

from stu_managed 
insert into table stu01 select * where age <= 18 
insert into table stu02 select * where age>18 and age<=19 
insert into table stu03 select * where age>19;

区别

load对文件进行操作，insert是对表的查询结果进行操作
load过程中无法对数据进行筛选，不适用于所有分桶表与分区表的一些情况

分区表的数据加载

静态分区加载

静态加载数据的时候需要手动静态指定分区，需要手动添加分区

alter table stu_ptn_dept add partition(dept="CS");

load方式：这种方法不会进行数据过滤，最终数据所有的dept字段对应的值都变为CS，可能会导致数据错误，因为分区字段是单独存储的；使用load方式在进行数据加载的时候一定要十分确定分区字段都为此分区的值时才能使用这种方式；在生产上将日期做为分区字段时可以使用这种方法加载

load data local inpath "/home/hadoop/hive_data/student.txt" into table stu_ptn_dept partition(dept="CS");

insert方式：从一个非分区表中查询数据插入到分区表中，会进行数据过滤，但是必须足够了解数据（知道每一个分区名），在分区很多时不适用

单重数据插入

insert into stu_ptn_dept partition(dept="CS") select sid,name,sex,age from stu_managed01 where dept="CS";

多重数据插入

from stu_managed01 
insert into stu_ptn_dept partition(dept="IS") select sid,name,sex,age where dept="IS" 
insert into stu_ptn_dept partition(dept="MA") select sid,name,sex,age where dept="MA";

动态分区加载

动态分区加载时分区名根据数据自动生成分区，不需要自己指定分区名，但是不支持lode的方式进行数据加载，只能使用insert方式加载数据，需要注意hive中默认动态分区是关闭的，通过set hive.exec.dynamic.partition.mode=nonstrict;来开启
加载数据：insert into table tbname partition(分区字段) select ...

insert into table stu_ptn_dept partition(dept) select sid,name,sex,age,dept from stu_managed01;

静态分区与动态分区的区别

概念不同
- 动态分区：分区根据数据自动生成，不需要手动指定
- 静态分区：插入数据的时候一定要静态指定分区字段的值
数据
- 静态分区：可能存储某一个分区中没有任何数据的
- 动态分区：每一个分区中至少有一条数据
性能
- 静态分区的性能高于动态分区

多级分区

多级分区：分区字段有多个，先按字段靠前（高级分区）的分，再按字段靠后（低级分区）的分
创建表

create table if not exists stu_ptn_dj(sid int,name string,sex string) partitioned by (dept string,age int) row format delimited fields terminated by ",";

数据插入

静态分区加载

insert into table stu_ptn_dj partition(dept="CS",age=18) select sid,name,sex from stu_managed01 where dept="CS" and age=18;

静态与动态混合加载：此时静态分区必须设定为高级分区

insert into table stu_ptn_dj partition(dept="MA",age) select sid,name,sex,age from stu_managed01 where dept="MA";

动态分区加载

insert into table stu_ptn_dj partition(dept,age) select sid,name,sex,dept,age from stu_managed01;

分桶表的数据加载

分桶：将数据按照分桶规则（分区算法）分到不同的桶中，分桶个数对应reducetask的个数
分桶规则

string类型：分桶字段 .hash % 分桶个数
int类型：分桶字段 % 分桶个数

数据导入：分桶表不支持数据加载（load）的方式导入数据，只能用数据插入（insert）的方式导入数据

insert into table stu_buk select * from stu_managed01;

数据查询

查询全表的所有桶：

select * from stu_buk;

查询某一个桶：select * from tbname tablesample (bucket x out of y)y表示桶簇（一或多个桶组成的一簇）个数，一簇中包含（桶的个数/簇的个数）个桶；x表示桶簇编号，是由1开始顺序递增的

select * from stu_buk tablesample(bucket 1 out of 3);

数据导出

数据导出：将hql语句的查询结果导出到本地或hdfs的文件
单重导出：insert overwrite [local] directory "路径" select_statement;local为导出本地，不加时导出到HDFS

insert overwrite local directory "/home/hadoop/hive_data/ag19" select * from stu_managed01 where age>19;

多重导出：对同一个表的不同查询结果导出到不同的路径下

from tbname
insert overwrite local directory "路径" select ... where ...
insert overwrite local directory "路径" select ... where ...

from stu_managed01 
insert overwrite directory "/data/age18" select * where age=18 
insert overwrite directory "/data/age19" select * where age=19;

DQL

select 
... 
from ... 
join 
... 
on ... 
where ... 
group by ... 
having ... 
order by ... 
limit ...

连接

join … on …：按照某个字段连接两表

Hive只支持等值连接，不支持不等值连接

select * a join b on a.id=b.id;

Hive中有多个连接条件的时候支持and连接，不支持or连接；注意在外连接时将过滤条件放在and后面与放在where后面是有区别的

select * a join b on a.id=b.id and a.name=b.name;

hive中支持多表连接

有a表（id，name）与b表（id，name）如下：

a表：

id	name
1	zs
2	ls
3	ww
5	xh

b表：

id	name
1	12
2	23
3	43
4	11

建表

create table if not exists a(id int,name string) row format delimited fields terminated by "\t";

load data local inpath "/home/hadoop/hive_data/a" into table a;

create table if not exists b(id int,age int) row format delimited fields terminated by "\t";

load data local inpath "/home/hadoop/hive_data/b" into table b;

内连接：inner join，两个或多个表的交集（公共的部分），最终返回的是两个或多个表都有的关联键的记录

select * from a join b on a.id=b.id;

a.id    a.name  b.id    b.age
1       zs      1       12
2       ls      2       23
3       ww      3       43

左外连接：left outer join（left join），以join左侧的表为主表的，主表中有几条数据，最终关联出来几条数据，右表有则关联，没有则补为null

select * from a left join b on a.id=b.id;

a.id    a.name  b.id    b.age
1       zs      1       12
2       ls      2       23
3       ww      3       43
5       xh      NULL    NULL

右外连接：right outer join（right join），与左外连接相反

select * from a right join b on a.id=b.id;

a.id    a.name  b.id    b.age
1       zs      1       12
2       ls      2       23
3       ww      3       43
NULL    NULL    4       11

全外连接：full outer join（full join），不管记录能不能够关联上都输出

select * from a full join b on a.id=b.id;

1       zs      1       12
2       ls      2       23
3       ww      3       43
NULL    NULL    4       11
5       xh      NULL    NULL

半连接：semi join，查询一个表中被另一个表包含的数据，是对in（底层无法确定key的值）的优化，结果只显示一个表中的数据，等同于内连接后只输出一张表中的字段

#内连接
select a.* from a join b on a.id=b.id;

a.id    a.name
1       zs
2       ls
3       ww
#左半连接：left semi join
select * from a left semi join b on a.id=b.id;

a.id    a.name
1       zs
2       ls
3       ww

过滤

where：过滤聚合之前的数据，为聚合函数准备数据
having：对聚合结果做过滤，是在聚合函数之后执行的，having只能对group by的结果进行操作，不能单独使用

//求每个部门中年龄大于等于19的人数多于4人的部门
select dept,count(*) peoplecount from stu_managed01 where age > 19 or age = 19 group by dept having peoplecount > 4;

分组

group by：按照指定字段对数据进行分组，group by后可以同时使用多个分组字段来进行分组，不能在分组字段中对分组字段起别名
group by是在select之前执行的，不能使用select后面的字段
group by使用的时候select后面的字段只能为：

group by的字段
聚合函数 max min avg count sum

排序

order by：全局排序，无论数据量多大都对所有数据按照指定的字段进行排序，只有一个reducer
sort by：局部排序，针对每一个reducetask进行排序，在数据进入reducer之前完成排序，每一个reducetask的结果是有序的，全局是无序的，默认只有一个reducetask，此时效果等同于order by
distribute by：按照指定的字段将数据进行划分输出到不同的reducer中，分区的算法为字段.hash % reducetasks，使用时一般为distribute by（指定数据分配）+ sort by（指定排序）,distribute by的字段和sort by的字段可以不一致

select * from stu_managed01 distribute by age sort by age desc；

cluster by：先按照指定字段分配数据，再按照指定字段进行升序排序，当distribute by 和sort by 的字段为同一个字段且升序排序时与cluster by的效果相同

select * from stu_managed01 cluster by age;
#等同于：
select * from stu_managed01 distribute by age sort by age;

切割

limit：取全局的前几条记录