零、准备工作
1. Hive环境安装
参见搭建Hive 3.x环境(CentOS 9 + Hadoop3.x)
2. 准备数据
在虚拟机HOME目录创建如下文件内容:
cd /root
vi emp.csv
内容如下:
7369,SMITH,CLERK,7902,1980/12/17,800,,20
7499,ALLEN,SALESMAN,7698,1981/2/20,1600,300,30
7521,WARD,SALESMAN,7698,1981/2/22,1250,500,30
7566,JONES,MANAGER,7839,1981/4/2,2975,,20
7654,MARTIN,SALESMAN,7698,1981/9/28,1250,1400,30
7698,BLAKE,MANAGER,7839,1981/5/1,2850,,30
7782,CLARK,MANAGER,7839,1981/6/9,2450,,10
7788,SCOTT,ANALYST,7566,1987/4/19,3000,,20
7839,KING,PRESIDENT,,1981/11/17,5000,,10
7844,TURNER,SALESMAN,7698,1981/9/8,1500,0,30
7876,ADAMS,CLERK,7788,1987/5/23,1100,,20
7900,JAMES,CLERK,7698,1981/12/3,950,,30
7902,FORD,ANALYST,7566,1981/12/3,3000,,20
7934,MILLER,CLERK,7782,1982/1/23,1300,,10
vi dept.csv
内容如下:
10,ACCOUNTING,NEW YORK
20,RESEARCH,DALLAS
30,SALES,CHICAGO
40,OPERATIONS,BOSTON
3. 进入Hive CLI
在hadoop1上执行下面命令启动Hadoop集群
start-all.sh
mapred --daemon start historyserver
在hadoop2上启动MetaStore服务
nohup hive --service metastore &
在hadoop2或者hadoop3上进入Hive CLI
hive
一、HQL基本练习
1. 上传数据
创建HDFS目录/hivedata
hive> dfs -mkdir /hivedata;
上传数据到/hive_test
hive> dfs -put /root/emp.csv /hivedata;
hive> dfs -put /root/dept.csv /hivedata;
验证数据是否上传成功
hive> dfs -ls /hivedata;
Hive Cli可以执行如下几类命令,需要注意的是命令要使用
;
作为结束符:
- 执行Shell命令
在Hive中,你可以使用
!
符号来执行Shell命令。例如,你可以在Hive中执行ls
命令来列出Linux根目录下的文件。hive > !ls;
- 执行HDFS命令
在Hive中,你也可以直接使用HDFS命令来操作HDFS上的文件。例如,你可以使用
dfs -mkdir
命令来创建一个新的目录。hive > dfs -mkdir /jsuttest;
- 执行HQL
在Hive中,你可以执行HQL语句。
hive > show databases;
2. 数据库的基本操作
hive> show databases --查看数据库;
hive> create database tmp_db location '/home/hive/db/tmp_db' --创建数据库时指定位置;
hive> desc database tmp_db --查看一下数据库的信息;
#查看一下数据库的HDFS目录;
hive> dfs -ls -R /home/hive/db/;
hive> drop database tmp_db --删除数据库;
hive> create database test_db;
hive> use test_db;
hive> select current_database() --查看当前使用的数据库;
#观察HDFS的目录变化;
hive> dfs -ls -R /user/hive;
3. 内部表的基本操作
hive> create table emp
> (empno int,ename string,job string,mgr int,hiredate string,sal int,comm int,deptno int)
> row format delimited fields terminated by ',';
hive> desc emp;
hive> desc formatted emp;
hive> load data inpath '/hivedata/emp.csv' into table emp;
#观察HDFS目录变化;
hive> dfs -ls -R /user/hive/warehouse;
hive> dfs -ls -R /hivedata;
hive> select * from emp;
hive> drop table emp --删除表;
#观察HDFS目录变化;
hive> dfs -ls -R /user/hive/warehouse;
4. 外部表的基本操作
hive> dfs -mkdir -p /hivedata/emp.tb;
hive> dfs -put /root/emp.csv /hivedata/emp.tb;
hive> create external table emp
> (empno int,ename string,job string,mgr int,hiredate string,sal int,comm int,deptno int)
> row format delimited fields terminated by ','
> location '/hivedata/emp.tb';
hive> desc formatted emp;
#观察HDFS目录变化;
hive> dfs -ls -R /user/hive/warehouse;
hive> dfs -ls -R /hivedata/emp.tb;
hive> select * from emp;
hive> drop table emp --删除表;
#观察HDFS目录变化;
hive> dfs -ls -R /hivedata/emp.tb;
5. 分区表的基本操作
hive> create external table emp_ext
> (empno int,ename string,job string,mgr int,hiredate string,sal int,comm int,deptno int)
> row format delimited fields terminated by ','
> location '/hivedata/emp.tb';
hive> create table emp_partition
> (empno int,ename string,job string,mgr int,hiredate string,sal int,comm int)
> partitioned by (deptno int)
> row format delimited fields terminated by ',';
hive> desc emp_partition --查看表的信息;
hive> insert into table emp_partition partition(deptno=10)
> select empno,ename,job,mgr,hiredate,sal,comm from emp_ext where deptno=10 --插入一个分区的数据;
hive> insert into table emp_partition partition(deptno=20)
> select empno,ename,job,mgr,hiredate,sal,comm from emp_ext where deptno=20;
hive> select * from emp_partition;
hive> dfs -ls -R /user/hive;
hive> dfs -cat /user/hive/warehouse/test_db.db/emp_partition/deptno=10/000000_0;
hive> show partitions emp_partition --查看分区个数;
hive> desc formatted emp_partition;
#hdfs新建一个目录;
hive> dfs -mkdir /user/hive/warehouse/test_db.db/emp_partition/deptno=30;
hive> show partitions emp_partition --查看分区;
hive> msck repair table emp_partition --修复表;
hive> show partitions emp_partition --查看分区;
6. 桶表
hive> create table emp_bucket
> (empno int,ename string,job string,mgr int,hiredate string,sal int,comm int,deptno int)
> clustered by(ename) into 2 buckets;
hive> insert into emp_bucket select * from emp_ext;
#观察HDFS目录变化;
hive> dfs -ls -R /user/hive;
hive> select * from emp_bucket;
7. 其他
hive> alter table emp_ext rename to emp_new;
hive> alter table emp_new set tblproperties('EXTERNAL'='FALSE');
hive> desc formatted emp_new;
hive> drop table emp_new;
hive> dfs -ls -R /hivedata;
二、查询分析
1. 数据准备
hive> create table emp
> (empno int,ename string,job string,mgr int,hiredate string,sal int,comm int,deptno int)
> row format delimited fields terminated by ',';
hive> load data local inpath '/root/emp.csv' into table emp;
hive> create table dept(deptno int,dname string,loc string)
> row format delimited fields terminated by ',';
hive> load data local inpath '/root/dept.csv' into table dept;
2. 简单查询
1)全表和特定列查询
hive> select * from emp --查询所有列;
hive> select empno, ename from emp --查询指定列;
hive> select distinct mgr from emp --查询有哪些管理者;
2)列别名
重命名一个列,紧跟列名,也可以在列名和别名之间使用关键字 AS
hive> select ename AS name, deptno dn from emp;
3)算术运算
+ - * 、 % & | ^ ~
hive> select empno,ename,sal*12 from emp --计算年薪;
4)LIMIT语句
hive> select * from emp limit 2;
5)常用函数
hive> select count(*) cnt from emp --求总行数(count);
hive> select max(sal) max_sal from emp --求工资的最大值(max);
hive> select min(sal) min_sal from emp --求工资的最小值(min);
hive> select sum(sal) sum_sal from emp --求工资的总和(sum);
hive> select avg(sal) avg_sal from emp --求工资的平均值(avg);
hive> select empno,ename,sal,comm, sal*12+nvl(comm, 0) from emp --注意NULL的处理;
hive> select empno,ename,job,sal,
> case job when 'PRESIDENT' then sal+1000
> when 'MANAGER' then sal+800
> else sal+400
> end
> from emp --加薪 PRESIDENT+1000,MANAGER+800,其他+400;
6)Where子句
hive> select * from emp where sal >1000;
7)Like 和 RLike
-
使用 LIKE 运算选择类似的值, 选择条件可以包含字符或数字:
- % 代表零个或多个字符(任意个字符)。
- _ 代表一个字符**。**
-
RLIKE 子句是 Hive 中这个功能的一个扩展,其可以通过 Java 的正则表达式这个更强大的语言来指定匹配条件。
hive> select * from emp where ename LIKE 'A%' --查找名字以 A 开头的员工信息;
hive> select * from emp where ename LIKE '_A%' --查找名字中第二个字母为 A 的员工信息;
hive> select * from emp where ename RLIKE '[A]' --查找名字中带有 A 的员工信息;
8)比较运算符(Between/In/ Is Null)
等于 | 小于 | 小于等于 | 大于 | 大于等于 | 不等 | NULL****安全的等于 |
---|---|---|---|---|---|---|
= | < | <= | > | >= | <> != | <=> |
-
A [NOT] BETWEEN B AND C :A是否属于[B,C]区间,A,B,C任一为NULL, 结果为NULL
-
A IS [NOT] NULL:A是否为空
-
A IN(数值 1, 数值 2):A是否是列表中的值
-
A [NOT] LIKE B:A是否与B相似
-
A RLIKE B, A REGEXP B:B是基于Java的正则表达式
hive> select * from emp where sal =5000 --查询出薪水等于 5000 的所有员工;
hive> select * from emp where sal between 500 and 1000 --查询工资在 500 到 1000 的员工信息;
hive> select * from emp where comm is null --查询 bonus 为空的员工信息;
hive> select * from emp where sal IN (1500, 5000) --查询工资是 1500 或 5000 的员工信息;
9) 逻辑运算符(And/Or/Not)
hive> select * from emp where sal>1000 and deptno=30 --查询薪水大于 1000,部门是 30;
hive> select * from emp where sal>1000 or deptno=30 --查询薪水大于 1000,或者部门是 30;
hive> select * from emp where deptno not IN(30, 20) --查询除了 20 部门和 30 部门以外的员工信息;
10) 分组
- Group By 语句通常会和聚合函数一起使用,按照一个或者多个列队结果进行分组,然后对每个组执行聚合操作。
hive> select deptno, avg(sal) avg_sal from emp group by deptno --计算 emp 表每个部门的平均工资;
hive> select deptno, job, max(e.sal) max_sal from emp e group by deptno, job --计算 emp 每个部门中每个岗位的最高薪水;
- Having 语句 与 where 不同: where 后面不能写分组函数,而 having 后面可以使用分组函数;having 只用于 group by 分组统计语句。
hive> select deptno, avg(sal) from emp group by deptno --求每个部门的平均工资;
hive> select deptno, avg(sal) avg_sal from emp group by deptno having avg_sal > 2000 --求每个部门的平均薪水大于 2000 的部门;
11) Join语句
-
内连接
-
左(右)外连接
-
全外连接: full join
-
多表连接
hive> --根据员工表和部门表中的部门编号相等,查询员工编号、员工名称和部门名称;
hive> select e.empno, e.ename, d.deptno, d.dname from emp e join dept d on e.deptno = d.deptno --内连接;
hive> select e.empno, e.ename, d.deptno from emp e left join dept d on e.deptno = d.deptno --左连接;
12)排序
- 全局排序(Order By)
- Order By:全局排序,只有一个 Reducer;
- 排序方式:ASC(ascend): 升序(默认)DESC(descend): 降序
hive> select * from emp order by sal desc --按工资降序排列;
hive> select ename, deptno, sal from emp order by deptno, sal -- 多个列排序;
- 每个Reduce内部排序(Sort By)
Sort By:对于大规模的数据集 order by 的效率非常低。在很多情况下,并不需要全局排序,此时可以使用 sort by。Sort by 为每个 reducer 产生一个排序文件。每个 Reducer 内部进行排序,对全局结果集来说不是排序
hive> set mapreduce.job.reduces=3 --设置 reduce 个数;
hive> set mapreduce.job.reduces --查看设置 reduce 个数;
hive> select * from emp sort by deptno desc --根据部门编号降序查看员工信息;
hive> insert overwrite local directory '/home/hadoop/data/sortby-result'
> select * from emp sort by deptno desc --将查询结果导入到文件中(按照部门编号降序排序);
- 分区(Distribute By)
- Distribute By: 在有些情况下,需要控制某个特定行应该到哪个 reducer,通常是为了进行后续的聚集操作。distribute by 子句可以做这件事。distribute by 类似 MR 中 partition(自定义分区),进行分区,结合 sort by 使用。
- 对于 distribute by 进行测试,一定要分配多 reduce 进行处理,否则无法看到 distribute by 的效果
hive> set mapreduce.job.reduces=3;
hive> insert overwrite local directory '/home/hadoop/data/distribute-result'
> select * from emp distribute by deptno sort by empno desc --先按照部门编号分区,再按照员工编号降序排序;
#查看输出目录;
hive> !ls /home/hadoop/data/distribute-result;
- Cluster By
当 distribute by 和 sorts by 字段相同时,可以使用 cluster by 方式。
cluster by 除了具有 distribute by 的功能外还兼具 sort by 的功能。但是排序只能是升序排序,不能指定排序规则为 ASC 或者 DESC。
hive> select * from emp cluster by deptno;
hive> select * from emp distribute by deptno sort by deptno;