1、Hive的DDL语法操作

1.1、Hive数据库DDL操作

（1）创建数据库
create database db_hive2;` `或者` `create database if not exists db_hive;
数据库在HDFS上的默认存储路径/user/hive/warehouse/*.db
（2）显示所有数据库
show databases;
（3）查询数据库
show database like ‘db_hive’;
（4）查询数据库详情
desc database db_hive;
（5）显示数据库
desc database extended db_hive;
（6）切换当前数据库
use db_hive;
（7）删除数据库
#删除为空的数据控
drop database db_hive;
#如果删除的数据库不存在，最好采用if exists判断数据库是否存在
drop database if exists db_hive;
#如果数据库中有表存在，需要使用cascade强制删除数据库
drop database if exists db_hive cascade;

1.2、Hive表的DDL操作

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] row format delimited fields terminated by “分隔符”
[STORED AS file_format] 存储对应的文件格式
[LOCATION hdfs_path]存储在hdfs的哪个目录

字段解释说明：

CREATE TABLE ：创建指定名称的表，如果存在报异常，可以使用 IF NOT EXISTS ：来避免这个异常。
EXTERNAL：创建外部表，在建表的同时可以指定源数据的路径LOCATION：创建内部表时，会将数据移动到数据仓库指向的路径，若创建外部表不会有任何改变。在删除表时，内部表的元数据和源数据都会被删除，外部表不会删除源数据。
COMMENT：为表和列增加注释
PARTITIONED BY：创建分区表
CLUSTERED BY：创建分桶表
SORTED BY：创建排序后分桶表（不常用）
STORED AS ：指定存储文件类型sequencefile（二进制序列文件）、textfile（文本）、rcfile（列式存储格式文件），如果文件数据是纯文本，可以使用STORED AS TEXTFILE。如果需要使用压缩，使用STORED AS SEQUENCEFILE
LOCATION 指定表在 hdfs 上的存储位置

1.2.2、创建内部表

1、直接使用标准的建表语句：

create table if not exists student11(
id int,
name string
)
row format delimited fields terminated by '\t'
stored as textfile;

使用文本data.txt

1 zhang

2 lisi

2、查询建表法:

通过AS查询语句完成建表：将子查询的结果存放在新表里，有数据

create table if not exists student1 as select id,name from student;

3、like建表法：

根据已存在的表结构创建表

create table if not exists student2 like student;

4、查询表的类型：

desc formatted student;

5、内部表的默认位置：

（根据自己情况来定）

/user/hive_remote/warehouse/db_hive.db

6、将数据导入到Hive表中：

举列子：student11s是Hive表

load data local inpath '/opt/bigdata2.7/hivedata/student.txt' into table student11;

1.2.3、创建外部表

注意：default是数据库的名

create external table if not exists default.emp(
id int,
name string
)
row format delimited fields terminated by '\t'
location '/ opt/bigdata2.7/hivedata'

创建外部表的时候需要加上external关键字，location字段可以指定，也可以不指定，不指定的话就是使用默认目录/user/hive/warehouse

1.2.4、内部表与外部表相互转换

​ 1、内部表转换为外部表

#把student 内部表改为外部表

alter table student set tblproperties('EXTERNAL'='TRUE');

​ 2、外部表转换成内部表

alter table student set tblproperties('EXTERNAL'='FALSE');

1.2.5、内部表与外部表区别

1、建表语法不同：

外部表建表的时候需要加上external关键字

2、数据存储位置不同：

创建内部表的时候，会将数据移动到数据仓库指向的路径；若创建外部表，仅仅记录数据所在的路径，不对数据的位置进行任何改变。

2、删除表之后：

内部表会删除元数据，删除表的数据。

外部表删除之后，仅仅是把表的元数据删除了，真实的数据还在，后期还可以恢复出来。

1.3、Hive表DDL语法经典案列

1.3.1、电影案列分析

1、数据格式：

战狼1,吴京1:吴刚1:小明1,2017-08-01

战狼2,吴京2:吴刚2:小明2,2017-08-02

战狼3,吴京4:吴刚4:小明4,2017-08-03

战狼4,吴京3:吴刚3:小明3,2017-08-04

战狼5,吴京5:吴刚5:小明5,2017-08-05

2、建表语句：

create table t_movie(movie_name string,actors array<string>,first_date string)
row format delimited fields terminated by ','
collection items terminated by ':';

3、导入数据：

确保hadoop用户对该文件夹有读写权限。
load data local inpath '/opt/bigdata2.7/hive/movie';

4、查询每个电影的第二个主演：

select movie_name,actors[1] from t_movie;

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-hgfz0RZZ-1579482997640)(2%E3%80%81Hive%E7%9A%84DDL%E8%AF%AD%E6%B3%95%E6%93%8D%E4%BD%9C.assets/image-20200109093038358.png)]

5、查询每部电影有几名主演:

select movie_name,size(actors) as num from t_movie;

6、主演里包含吴刚5的电影

select movie_name,actors from t_movie where array_contains(actors,'吴刚5');

解析：

这里我们首先看到比较特殊的是主演的名字，而名字有都是string类型的，所以考虑到使用array类型，以为array存储的都是想同类型的元素。这里我们要使用collection items terminated by ‘:’,来设置指定复杂元素数据类型中元素的分隔符。

需要注意的是：collection items terminated by不仅是用来分隔array的，它的作用是分隔复杂数据类型里面的元素的。size内置函数是用来判断array元素的个数，array_contains()是判断array是否有这个元素。

1.3.2、个人档案型数据建表案例：

1、数据格式：

1，张三，18:male:北京

2，李四，19:male:南京

3，王五，20:male:上海

4，哈哈，18:male:北京

5，嘿嘿，12:male:成都

6，嘻嘻，14:male:济南

7，张丽，17:male:深圳

8，李物，19:male:重庆

2、建表语句：

create table t_user(id int,name string,info struct<age:string,sex:string,addr:string>)
row format delimited fields terminated by ','
collection items terminated by ':';

3、导入数据：

load data local inpath '/opt/bigdata2.7/hive/user' into table t_user;

4、 查询每一个人的id,名字,居住地址：

select id,name,info.addr from t_user;

解析：

这里比较特殊的字段是18:male:北京，对应的是年龄：性别：地址，每一个都有特殊的含义，我们考虑到无法构成一个键值对，所以map不合适，array只能包含相同的元素，而年龄是int类型，地址是strin类型，所以array不合适，所以考虑struct。

1.3.3、家庭档案数据建表案列

1、数据描述：

1,小明,father:张三#mother:李丽#brother:小刚,28

2,小鸿,father:李四#mother:王丽#brother:小志,28

3,小鹏,father:张物#mother:李美#brother:小英,28

4,张飞,father:张五#mother:李影#brother:小全,28

2、建表语句：

create table t_family(id int,name string,family_mem map<string,string>,age int)
row format delimited fields terminated by ','
collection items terminated by '#'
map keys terminated by ':';

3、导入数据：

load data local inpath '/opt/bigdata2.7/hive/family' into table t_family;

4、查看每个人的父亲：

select name,family_mem["father"] from t_family;

5、查看有哪些亲属关系：
select name,map_keys(family_mem),age from t_family;

6、查出每个人的亲人名字：

select name,map_values(family_mem) as relations,age from t_family;

7、查出每个人亲人的数量：

select id,name,size(family_mem) as relation_num,age from t_family;

2、Hive的DML语法操作

2.1、修改表的结构

2.1.1、修改表的名称

alter table student_partition1 rename to student_partition2

2.1.2、表的结构信息

desc student_partition3;

desc formated student_partition3;

2.1.3、增加/修改/替换列

增加列：

alter table student_partition3 add columns(address string);

修改列：

alter table student_partition3 change column address address_id int;

替换列：

alter table student_partition3 replace columns(deptno string,dname string,loc string);

2.1.4、增加/删除/查看表的分区

1、添加分区:

（1）添加单个分区：

alter table student_partition1 add partition(dt='20170601');

（2）添加多个分区：

alter table student_partition1 add partition(dt='20170602') partition(dt='20170603');

2、删除分区:

alter table student_partition1 drop partition (dt='20170601');

alter table student_partition1 drop partition (dt='20170601') partition (dt='20170602');

3、查看分区:

show partitions student_partition1;

2.2、Hive表的数据导入

2.2.1向表中加载数据

load data [local] impath 'datapath' overwrite | into table student [partition (partcol1=val1,...)];

load data: 表示加载数据

local:表示从本地加载数据到hive表中；否则从HDFS加载到hive表中

inpath: 表示加载数据的路径

overwite：表示覆盖表中已有数据，否则表示追加

into table:表示加载到哪张表

普通表举例：

load data local inpath '/opt/bigdata2.7/hive/person.txt' into table person;

分区表举例：

load data local inpath '/opt/bigdata2.7/hive/person.txt' into table person partition (dt="20190202");

2.2.2通过查询语句向表中插入数据

从指定的表中查询数据结果然后插入到目标表中

insert into/overwrite table tablename select **** from tablename;

insert into table student_partion1 partition(dt="2019-07-08") select * from tablename;

2.2.3、查询语句中创建并加载数据（as select）

create table if not exists tablename as select id,name from tablename;

2.2.4、创建表时通过location指定加载路径

创建表，并指定在hdfs上的位置

create table if not exists student1(
id int,
name string)
row format delimited fields terminated by '\t'
location '/usr/hive_remote/warehouse/student1';

create table if not exists person(
id int,
name string,
age int,
sex string
)
row format delimited fields terminated by ',';

上传数据文件到hdfs对应的目录中

在Linux中运行，注意不是hive端口

hdfs dfs -put /opt/bigdata2.7/hive/student1.txt /usr/hive_remote/warehouse/student1

2.2.5、Import数据到指定Hive表中

**注意：**先用export导出之后，再将数据导入

create table student2 like student1;

export table student1 to '/export/student1';

import table student2 from 'export/student1'

2.3、Hive表的导出

2.3.1、insert导出

1、将查询数据的结果导出到本地

insert overwrite local directory '/opt/bigdata/export/student' select * from student;

2、将查询结构格式化的导出到本地

insert overwrite local directory '/opt/bigdata/export/student'
row format delimited fields teminated by ','
select * from student;

3、将查询结果导出到HDFS（没有local）

insert overwrite directory '/user/export/student'
row format delimited fields terminated by ','
select * from student;

2.3.2、Hadoop命令导出到本地

hdfs dfs -get /user/hive_remote/warehouse/student/student.txt /opt/bigdata2.7/data

2.3.3、Hive Shell命令导出

hive -e 'select * from default.student' > /opt/bigdata/data/student1.txt

2.3.4、export导出到HDFS

export table default.student to '/user/hive/warehouse/export/student1';

3、Hive分区表语法

3.1、静态分区

表的分区字段的值需要开发人员主动手动给定

1、创建分区

create table order_partition(
order_number string,
order_price double,
order_time string
)
partitoned by (month string)
row format delimited fields terminated by '\t';

2、准备数据 order_created.txt的内容如下：

10001 100 2019-03-01
10002 200 2019-03-02
10003 300 2019-03-03
10004 400 2019-03-04
10005 500 2019-03-05

3、加载数据到分区表

load data local inpath '/opt/bigdata2.7/hive/tempdata/order_created.txt' overwrite into table order_partition partition(month='2019-03');

4、插叙数据结果

select * from order_partition where month ='2019-03';

3.1、动态分区语法

按照需求实现把数据自动导入道标的不同分区中，不需要手动指定

需求：按照不同部门作为分区导数据到目标表：

1、创建表

--创建普通表

create table t_order(
order_num string,
order_price double,
order_tome string
)
row format delimited fields terminated by '\t';

--创建目标分区表

create table order_dynamic_partition(
order_num string,
order_price double
)

partition by (order_time string)
row format delimited fields terminated by '\t';

2、准备数据order_created.txt的内容如下：

10001 100 2019-03-01
10002 200 2019-03-02
10003 300 2019-03-03
10004 400 2019-03-04
10005 500 2019-03-05
10006 100 2019-03-06
10007 200 2019-03-07
10008 300 2019-03-08
10009 400 2019-03-09
100010 500 2019-03-10

3、向普通表t_order加载数据

load data local inpath '/opt/bigdata2.7/hive/tempdata/order_created.txt' overwrite into table t_order;

4、动态加载数据到分区表中

–想要进行动态分区，需要进行参数设置

Linux输入hive，打开命令端口

set hive.exec.dynamic.partition=true;//使用动态分区

set hive.exec.dynamic.partition.mode = nonstrict;//非严格模式

--插入数据

insert into table order_dynamic_partition partition(order_time) select order_num,order_price,order_tome from t_order;

5、查看分区

show partitions order_dynamic_partition ;

4、Hive分桶表语法

分区的本质是在HDFS上进行分目录操作

分桶的本质就是讲一个表的数据分成很多的文件

分桶将整个数据内容按照某列属性的值区hash值进行区分，具有相同的hash值得数据进入到同一个文件中

举例：

比如按照name属性分为三个桶，就是对name属性的hash值取模，按照去摸的结果对数据进行分桶。

取模结果为0的数据存放在一个文件

取模结果为1的数据存放在一个文件

取模结果为2的数据存放在一个文件

取模结果为3的数据存放在一个文件

作用：

（1）取样本sample跟高效，没有分区的话需要扫描整个数据库

（2）提升某些查询操作效率，列入 map side join

案列演示：

1、创建分通表

主要:在创建分通表之前要执行命名

set hive.enforce.bucketing=true;开启对分通表的支持

set mapreduce.job.reduces=4;设置与桶相同的reduce的个数（默认只有一个reduce）

–分通表

create table use_buckets_demo(id int,name string)
clustered by(id)
into 4 buckets
row format delimited fields terminated by ‘\t’;

–普通表

create table user_demo(id int,name string)
row format delimited fields terminated by ‘\t’;

2、准备数据buckets.txt

1 lisi
2 wangwu
3 lili
4 lisisf
5 wangwu
6 lilis2
7 lisi2878
8 wangwu56
9 lili555
10 lili21212

3、加载数据到普通表user_demo

load data local inpath ‘/opt/bigdata2.7/hive/tempdata/buckets.txt’ into table user_demo;

4、加载数据到桶表user_buckets_demo

insert into table use_buckets_demo select * from user_demo;

5、抽样查询通表的数据

tablesample抽样数据，语法：tablesample(bucket x out of y)

x:表示第几个桶开始取数据

y:表示桶的倍数，一共需要从桶数/y个桶中取数据

select * from use_buckets_demo tablesample(bucket 1 out of 2)

--需要的总桶数= 4/2=2个

--先从第一个桶中取数据

--再从1+2=3桶中取数据

5、Hive查询语法

5.1、基本查询

注意：

大小写不敏感

sql可以写在一行或者多行

关键字不能缩写和分行

各子句也要分行

5.1.1、全表和特定列查询

（1）全表查询

selec * from stduent;

（2）选择特定列查询

select name from student;

5.1.2、列起别名

紧跟列名，也可以在列名后面加上关键字‘as’

实例：

select ename as name ,deomo as dn from student;

5.1.3、常用函数

可以开启hive本地local模式运行：

set hive.exec.model.local.atuo=true;

（1）求总行数：

select count(*) from emp;

（2）最大值：

select max(sal) from emp;

（3）求最小值：

select min(sal) from emp;

（4）求总和：

select sum(sal) from emp;

（5）求平均值：

select mean(sal) from emp;

5.1.4、limit语句

典型的查询会返回多行数据，limit语句用于限制返回的行数

select * from emp limit 3;

5.1.5、where语句

注意：使用where语句，将不满足条件的行过滤掉，where语句紧跟着from语句

select *from emp where sal>1000;

5.2、分组查询

5.2.1、Group by语句

Group By语句通常和聚合函数一起使用，按照一个或者多个列结果进行分组，然后对每个组进行聚合操作

案列：

（1）计算emp的每个部门的平均工资

select t.depno,avg(t.sal) as avg_sal from emp t group by t.deptno;

（2）计算每个部门每个岗位最高薪水

select t.deptno,t.job ,max(t.sal) as max_sal from emp t group by t.deptno,t.job;

5.2.2、Having语句

Having和where的不同点：

（1）where语句针对表中列发挥作用，查询数据；having针对查询结果中的列发挥作用，筛选数据

（2）where后面不能写分组函数，而having后面可以写分组函数

（3）having只用于group by分组统计数据

案列：

（1）求每个部门的平均工资：

select deptno,avg(sal) from emp group by deptno;

（2）求每个部门的平均工资大于2000的部门

select deptno,avg(sal) as avg_sal from emp group by deptno having avg_sal >2000;

5.3、join查询

5.3.1、等值join

Hive支持通常的SQL JOIN语句，但是只支持等值连接，不支持费等职连接

案列

（1）根据员工表和部门表的部门编号相等查询员工编号、员工名称、员工部门

select e.empno,e.ename,d.deptno,d.deptname from emp e join dept d on e.deptno = d.deptno;

5.3.2、内连接inner join

内连接 ：只有进行连接的连个表中都存在于连接条件相匹配的数据才会保留下来

join 默认是inner join

案列：

select * from teacher t iner join course c on t.id = c.id；

5.3.3、左外连接 left outer join

左外连接：join操作符左表中复合where子句的所有记录将会返回。

select * from teacher t left outer join course c on t.id = c.id;

5.3.4、右外连接 right outer join

右外连接：join操作符右表中复合where子句的所有记录将会返回。

select * from teacher t right outer join course c on t.id = c.id;

5.3.5、满外连接 full outer join

满外连接：将会返回所有表中复合where语句条件的所有记录。如果任何一张表中指定字符字段没有复合条件值得话，使用null值替代。

select * from teacher t full outer join course c on t.id = c.id

5.3.6、多表连接

多个表使用join连接

注意：连接n个表，至少需要n-1个连接条件。例如，连接三个表需要使用两个连接条件。

案列：多表连接查询查询老师对应的课程以及对应的分数，对应的学生

select * from

​ teacher t

​ left join course c on t.id = c.id

​ left join score s on s.sid = c.cid

​ left join student stu on s.s_id = stu.s_id;

5.5、排序

5.5.1、全局排序 order by

order by 说明：

（1）全局排序，只有一个reduce。

（2）使用order by排序，升序asd,降序desc。

（3）order by在select语句的末尾。

案列：

select * from student order by score desc;

5.5.2、多个列排序

按照学生分数和年龄升序排序

select * from students order by score,age asd;

5.5.3、每个MapReduce内部排序（sort by）局部排序

sort by：每个reduce内部进行排序，对全局结果集说不是排序

（1）设置reduce的个数

set mapreduce.job.reduces = 3;

（2）查看设置reduce个数

set mapreduce.job.reduces;

（3）查询成绩按照成绩降序排列

select * from student s sort by s.score;

（4）将查询结果导入文件中（按照成绩进行排列）

insert overwrite local directory ‘/home/hadoop/hivedata/sort’ select * from student s sort by s.score;

5.5.4、distribute by分区排序

distribute by：类似MR中的partition采用hash算法，在map端将查询结果中hash值相同的结果分发到对饮的reduce中。结合sort by使用

注意：Hive中要求distribute by要写在sort by前面。

案列：先按照学生sid进行分区，在按照学生成绩进行排序

（1）设置reduce的个数

set mapreduce.job.reduces = 3;

（2）通过distribute by 进行数据的分区，将不同的sid划分到对应的reduce中

insert ovrewrite lical directory ‘/home/hadoop/hivedata/distribute’ select * from student distribute by sid sort by score;

5.5.4、cluster by分区排序

当distribute by 和 sort by 字段相同时，可以使用cluster by方式

cluster by = distribute by + sort by

–以下两种写法相同：

insert ovrewrite lical directory ‘/home/hadoop/hivedata/distribute’ select * from student distribute by sid sort by score;

insert ovrewrite lical directory ‘/home/hadoop/hivedata/distribute’ select * from student cluster by score;

6、扩展

6.1、case…when…then语句

case…when…then语句和if条件语句类似，用于处理单个列的查询结果

案例：

（1）创建表：

create table employee(
empid int,
depid int,
sex string,
salary double
)
row format delimited fields terminated by ’ ';

（2）数据文件employee.txt:

1 10 female 5500.0
2 10 female 3500.0
3 20 emale 4500.0
4 10 female 7500.0
5 20 emale 8500.01
6 30 emale 9500.0
7 40 emale 1500.0
8 50 female 5200.0
9 30 emale 5300.0
10 30 female 5400.0

（3）导入数据：

load data local inpath ‘/opt/bigdata2.7/hive/tempdata/employee.txt’ into table employee;

（4）将员工按照薪资待遇进行划分等级

select *
case
when salary <5000 then “低等收入”
when salary >=5000 then “中等收入”
when salary >10000 then “高等收入”
end as level
from employee;

ribute’ select * from student distribute by sid sort by score;

insert ovrewrite lical directory ‘/home/hadoop/hivedata/distribute’ select * from student cluster by score;

6、扩展

6.1、case…when…then语句

case…when…then语句和if条件语句类似，用于处理单个列的查询结果

案例：

（1）创建表：

create table employee(
empid int,
depid int,
sex string,
salary double
)
row format delimited fields terminated by ’ ';

（2）数据文件employee.txt:

1 10 female 5500.0
2 10 female 3500.0
3 20 emale 4500.0
4 10 female 7500.0
5 20 emale 8500.01
6 30 emale 9500.0
7 40 emale 1500.0
8 50 female 5200.0
9 30 emale 5300.0
10 30 female 5400.0

（3）导入数据：

load data local inpath ‘/opt/bigdata2.7/hive/tempdata/employee.txt’ into table employee;

（4）将员工按照薪资待遇进行划分等级

select *
case
when salary <5000 then “低等收入”
when salary >=5000 then “中等收入”
when salary >10000 then “高等收入”
end as level
from employee;