大数据实训笔记10：hive的应用

目录

数据定义

数据仓库操作

数据仓库的创建

数据仓库的查询

数据仓库的修改

数据仓库的删除

表操作

内部表

外部表

分区表

桶表

表的修改

表的删除

视图

数据操作

数据导入

加载数据

插入数据

数据导出

HQL查询

数据准备

select查询

where语句

模糊查询

分组查询

join

排序

---

在上一节大数据实训笔记9：hive的安装部署的基础上，我们这一节来介绍如何使用hive。

数据定义

数据仓库操作

下面我们用JDBC访问的方式来介绍一下hive数据仓库的基础应用。

[hadoop@hadoop101 hive-3.1.2]$ bin/beeline -u jdbc:hive2://hadoop101:10000 -n hadoop

数据仓库的创建

create database [if not exists] 数据库名 location <路径>

首先来创建一个数据库bigdata。

create database if not exists bigdata;

我们可以访问 http://hadoop101:9870 ，进入/user/hive/warehouse/查看。

数据仓库的查询

对数据仓库的查询包括查询数据仓库的个数和查看数据仓库的详情等。查看数据仓库的详细信息要使用关键字extended。

//显示数据仓库
show databases;

//过滤显示查询的数据仓库
show databases like 'big*';

//查看数据仓库的详情
desc database extended bigdata;

//切换数据仓库
use bigdata;

数据仓库的修改

用户可以使用alter命令修改数据仓库的Dbproperties键值对的属性值，来描述这个数据仓库的属性信息。数据仓库其他的元数据信息都是不可更改的，包括数据仓库名和数据仓库所在存储的位置。

alter database bigdata set dbproperties("createtime"="20220707");
desc database extended bigdata;

数据仓库的删除

drop database [if exists] 数仓名 [cascade];

//删除数据仓库
drop database bigdata;
//如果存在则删除数据仓库
drop database if exists bigdata;
//级联删除数据仓库
drop database bigdata cascade;

表操作

在hive中，表都在HDFS的相应目录中存储数据。目录的名称是在创建表的时自动创建以表名来命名的，表中的数据都保存在此目录中。数据以文件形式存储在HDFS中。

表的元数据会存储在数据库中，可以是derby数据库或MySQL数据库。

Create table [if not exists] 表名
(列名1 数据类型 [comment列注释],........)
[comment表的注释]
[partition by(列名,.....)]        //分区表
[clustered by(列名,.....)]        //创建桶表
[sorder by(列名[asc|desc])]       //排序
[row format delimited 格式]       //分隔符
[stored as 文件格式]              //表文件的存储格式
[location hdfs路径]               //hdfs存储位置

内部表

不带external关键字创建的表是管理表，有时也称为内部表。hive表是归属某个数据仓库的，默认情况下hive会将表存储在default数据仓库中，也可以使用use命令切换数据仓库，将所创建的表存储在切换后的数据仓库中。删除内部表时，表的元数据和表数据文件会同时被删除。

先来看一个简单的案例。在hive数据仓库bigdata中创建内部表test，将本地文件的数据test.txt导入到表中。

//创建本地文件test.txt
[hadoop@hadoop101 hive-3.1.2]$ mkdir data
[hadoop@hadoop101 hive-3.1.2]$ cd data
[hadoop@hadoop101 data]$ vim test.txt

注意，下面的数据之间以\t（tab）隔开。 直接复制很可能出错，导入数据会显示null，因此大家最好自己手打。

1001    Alice
1002    Bob
1003    Caroline
1004    Delores
1005    Ethan
1006    Floyd

//创建bigdata数据仓库
create database if not exists bigdata;
//切换数据仓库
use bigdata;
//创建表
create table test(id int,name string)
row format delimited fields terminated by '\t';
//导入数据
load data local inpath '/opt/module/hive-3.1.2/data/test.txt' into table test;
//查询结果
select * from test;

查询结果，导入成功。

接下来我们看另一个案例，需要在hive数据仓库bigdata中创建复杂数据类型（Array, Map, Struct）内部表complex，将本地文件的数据complex.txt导入到表中。

//创建本地文件complex.txt
[hadoop@hadoop101 data]$ vim complex.txt

注意，不要有其他多余的符号和空格！ 

Alice,Bob_Caroline,Delores:23_Ethan:24,NewYork_USA
Floyd,Grace_Helen,Ian:25_James:23,London_England

 创建表如下：

create table complex(
name string,
friends array<string>,
partners map<string,int>,
address struct<city:string,country:string>)
row format delimited fields terminated by ','
collection items terminated by '_'
map keys terminated by ':';

//导入数据
load data local inpath '/opt/module/hive-3.1.2/data/complex.txt' into table complex;
//查看
select * from complex;

我们还可以创建指定目录的内部表。（这个目录是http://haoop101:9870上的）

create table if not exists test2(id int, name string) row format delimited fields terminated by '\t'
stored as textfile
location '/user/hive/warehouse/test2';

desc formatted test2;

外部表

带external关键字创建的表是外部表。外部表和内部表在元数据组织上都是一样的。

在hive数据仓库bigdata中创建外部表dept，将本地文件的数据dept.txt导入到表中。

[hadoop@hadoop101 data]$ vim dept.txt

注意，下面的数据之间以\t（tab）隔开。 

101     计算机学院      2100
102     数学学院        2200
103     外国语学院      2300
104     化学学院        2400
105     新闻媒体学院    2500

create external table if not exists dept(
deptno int,
dname string,
snum int)
row format delimited fields terminated by '\t';

//导入数据
load data local inpath '/opt/module/hive-3.1.2/data/dept.txt' into table dept;
//设置字符编码，否则会出现乱码
alter table dept set serdeproperties('serialization.encoding'='utf-8');
//查看
select * from dept;

内部表和外部表之间还可以相互转换。注意下面括号里的部分必须要大写。

alter table 表名 set tblproperties('EXTERNAL'='TRUE|FALSE');

//将内部表转为外部表
//区分大小写！！！括号里的部分必须大写
alter table test2 set tblproperties('EXTERNAL'='TRUE');

分区表

hive中的分区表就是分目录，把一个大的数据集根据业务需要分割成小的数据集。

create table if not exists dept2(deptno int, dname string, snum int)
partitioned by(month string)
row format delimited fields terminated by '\t';

load data local inpath '/opt/module/hive-3.1.2/data/dept.txt' into table dept2 partition(month='202201');
load data local inpath '/opt/module/hive-3.1.2/data/dept.txt' into table dept2 partition(month='202202');
load data local inpath '/opt/module/hive-3.1.2/data/dept.txt' into table dept2 partition(month='202203');

我们到浏览器上查询结果，分区表创建成功。

//单分区查询
select deptno,dname,snum from dept2 where month='202201';
//多分区联合查询
select deptno,dname,snum from dept2 where month='202201'
union
select deptno,dname,snum from dept2 where month='202202'
union
select deptno,dname,snum from dept2 where month='202203';

桶表

桶表就是将数据分解成更容易管理的若干部分。

create table bucket(
id int,
name string)
clustered by(id)
row format delimited fields terminated by '\t';

//设置桶表属性
set hive.enforce.bucketing=true;
set mapreduce.job.reduces=-1
//插入数据
insert into table bucket select id, name from test;

表的修改

hive表是可以修改的，包括表名、修改列名、增加列、删除或替换列等，但不提倡使用。

//查看表信息
desc dept1;

//重命名
alter table dept rename to dept1;

//修改列名
alter table 表名 change 旧列名 新列名 新列名的数据类型;

alter table dept1 change snum stu_num int;

//增加或替换列
alter table 表名 add|replace columns(列名 列名数据类型, ...);

alter table dept1 add columns(create_date string);

表的删除

drop table [if exists] 表名;

//删除表
drop table dept1;

视图

//创建视图
create view test_view as select * from test;
//查询视图
select * from test_view;
//删除视图
drop view test_view;

数据操作

hive数据操作包含load、insert、update、delete等。不要频繁的使用update和delete操作，这样会违背了hive的初衷。

数据导入

加载数据

load data [local] inpath '数据路径' [overwrite] into table 表名;

导入本地数据的操作在前文创建表中已经演示了，大家可以回去看一下。下面我们介绍其他的数据导入操作。

//将本地文件上传到hdfs
[hadoop@hadoop101 data]$ hdfs dfs -mkdir /user/hive/warehouse/data
[hadoop@hadoop101 data]$ hdfs dfs -put test.txt /user/hive/warehouse/data

create table test3(
id int,
name string)
row format delimited fields terminated by '\t';

//加载hdfs文件到hive表中
load data inpath '/user/hive/warehouse/data/test.txt' into table test3;
//查看
select * from test3;

注意，加载hdfs文件到hive表中，hdfs中的相应文件会自动被删除，大家可以到浏览器上查看。

接下来我们再上传一个test2.txt文件，同样导入test3表中，覆盖之前的数据。

//创建test2.txt文件，和test.txt相似，随便修改几个数据就好了
[hadoop@hadoop101 data]$ vim test2.txt
//将test2.txt上传到hdfs
[hadoop@hadoop101 data]$ hdfs dfs -put test2.txt /user/hive/warehouse/data

//加载数据并覆盖
load data inpath '/user/hive/warehouse/data/test2.txt' overwrite into table test3;
//查看
select * from test3;

可以看到数据和刚刚不同，说明被成功覆盖。

插入数据

insert into table test3(id, name) values(1007, "Grace"),(1008, "James");

如果insert语句没有报错，但是卡住了，可以先退出hive，重新进入。输入下列命令，再此执行insert语句，应该就可以成功。

set hive.exec.mode.local.auto=true;    //设置为本地模式

数据导出

//创建test3.txt，用来存放导出的数据
[hadoop@hadoop101 data]$ touch test3.txt
//导出数据到本地，目标文件有则覆盖，无则写入
insert overwrite local directory '/opt/module/hive-3.1.2/data/test3.txt' select * from test3;

//导出数据到hdfs
insert overwrite directory '/user/hive/warehouse/data/test1'
row format delimited fields terminated by '\t'
select * from test3;

//使用export导出数据到hdfs
export table test3 to '/user/hive/warehouse/data/test2';

HQL查询

数据准备

以下数据均以\t为分隔符，注意复制后要调整一下！

[hadoop@hadoop101 data]$ vim dept.txt

101     人事部
102     财务部
103     研发部
104     市场部
105     后勤部

[hadoop@hadoop101 data]$ vim emp.txt

1001    Alice   F       23      25000   101
1002    Bob     M       22      20000   103
1003    Cathy   F       24      20000   102
1004    Debbie  F       23      15000   102
1005    Ethan   M       25      16000   103
1006    Floyd   M       24      12000   105

//创建表dept
create table dept(
dno int,
dname string)
row format delimited fields terminated by '\t';
//加载数据到dept表
load data local inpath '/opt/module/hive-3.1.2/data/dept.txt' into table dept;
//查看
select * from dept;

//创建表emp
create table emp(
eno int,
ename string,
sex string,
age int,
salary int,
dno int)
row format delimited fields terminated by '\t';
//加载数据到emp表
load data local inpath '/opt/module/hive-3.1.2/data/emp.txt' into table emp;
//查看
select * from emp;

select查询

select [all|distinct]列名1,列名2,.....
from 表名
[where 条件]
[group by 列名1,列名2,.....]
[having 分组条件]
[order by 列名....]
[limit 数据]
....

全表查询刚刚已经演示过了，相信大家已经很熟悉了。下面我们演示其他的基础查询方式。

//查询特定列
select eno, ename from emp;

//查询并设置列的别名
select eno id, ename name from emp;

//limit限制查询返回的行数
select * from emp limit 2;

where语句

//查询工资为20000的员工信息
select * from emp where salary=20000;

//查询所有员工的编号、姓名及其所在的部门编号和姓名
select e.eno, e.ename, e.dno, d.dname from emp e, dept d where e.dno=d.dno;

//查询工资在17000-22000之间的员工信息
select * from emp where salary between 17000 and 22000;

//查询年龄为23或24的员工信息
select * from emp where age in (23, 24);

模糊查询

_表示任意一个字符，%表示任意个（包括零）字符。

//查询名字里有a的员工信息
select * from emp where ename like "%a%";

//查询名字第二个字母是a的员工信息
select * from emp where ename like "_a%";

//查询名字中有l的员工信息
select * from emp where ename rlike "[l]";

分组查询

//查询每个部门的平均工资、总工资、最高工资、最低工资
select d.dno, d.dname, avg(e.salary) avg_salary, sum(e.salary) sum_salary, max(e.salary) max_salary, min(e.salary) min_salary
from emp e, dept d
where e.dno=d.dno
group by d.dno, d.dname;

//查询部门表中平均工资大于18000的部门信息
select d.dno, d.dname, avg(e.salary) avg_salary
from emp e, dept d
where e.dno=d.dno
group by d.dno, d.dname
having avg_salary>18000;

//查询员工表中工资大于平均工资的员工信息
select *
from emp
where salary>(
select avg(e.salary) 
from emp e);

join

hive只支持等值连接、外连接和左半连接。

//两表连接
select e.*, d.dname
from emp e join dept d
on e.dno = d.dno;

排序

hive 常用的排序方法有order by, sort by, distribute by, cluster by。

//两表连接后按照工资升序排序
select e.*, d.dname
from emp e join dept d
on e.dno = d.dno
order by e.salary asc;