Hive基本查询

全表查询

select * from emp;

选择特定列查询

select empno, ename from emp;

注意:
(1)SQL 语言大小写不敏感

(2)SQL 可以写在一行或者多行

(3)关键字不能被缩写也不能分行

(4)各子句一般要分行写

(5)使用缩进提高语句的可读性

 

列别名: 重命名一个列、便于计算、紧跟列名，也可以在列名和别名之间加入关键字‘AS’

案例：select ename AS name, deptno dn from emp;

 

算术运算符

案例：select sal +1 from emp;

 

常用函数

求总行数(count)
hive (default)> select count(*) cnt from emp;
2.求工资的最大值(max)
hive (default)> select max(sal) max_sal from emp;
3.求工资的最小值(min)
hive (default)> select min(sal) min_sal from emp;
4.求工资的总和(sum)
hive (default)> select sum(sal) sum_sal from emp;
5.求工资的平均值(avg)
hive (default)> select avg(sal) avg_sal from emp;
 

Limit 语句

典型的查询会返回多行数据。LIMIT 子句用于限制返回的行数。
hive (default)> select * from emp limit 5;

Where 语句
1.使用 WHERE 子句，将不满足条件的行过滤掉 
2.WHERE 子句紧随 FROM 子句

案例：查询出薪水大于 1000 的所有员工

select * from emp where sal >1000;

 

比较运算符(Between/In/ Is Null)

案例实操
(1)查询出薪水等于 5000 的所有员工
hive (default)> select * from emp where sal =5000;
(2)查询工资在 500 到 1000 的员工信息
hive (default)> select * from emp where sal between 500 and 1000;
(3)查询 comm 为空的所有员工信息
hive (default)> select * from emp where comm is null;
(4)查询工资是 1500 或 5000 的员工信息
hive (default)> select * from emp where sal IN (1500, 5000);

Like 和 RLike

1)使用 LIKE 运算选择类似的值 
2)选择条件可以包含字符或数字:
% 代表零个或多个字符(任意个字符)。
_ 代表一个字符。
3)RLIKE 子句是 Hive 中这个功能的一个扩展，其可以通过 Java 的正则表达式这个更强大的语言来指定匹配条件。

案例：查找以 2 开头薪水的员工信息

select * from emp where sal LIKE '2%';

查找第二个数值为 2 的薪水的员工信息

select * from emp where sal LIKE '_2%';

查找薪水中含有 2 的员工信息

select * from emp where sal RLIKE '[2]';

 

逻辑运算符(And/Or/Not)

案例：

(1)查询薪水大于 1000，部门是 30
hive (default)> select * from emp where sal>1000 and deptno=30;
(2)查询薪水大于 1000，或者部门是 30
hive (default)> select * from emp where sal>1000 or deptno=30;
(3)查询除了 20 部门和 30 部门以外的员工信息
hive (default)> select * from emp where deptno not IN(30, 20);

 

分组：Group By 语句

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

(1)计算 emp 表每个部门的平均工资

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

(2)计算 emp 每个部门中每个岗位的最高薪水

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

 

Having 语句

1.having 与 where 不同点
(1)where 针对表中的列发挥作用，查询数据;having 针对查询结果中的列发挥作用， 筛选数据。

(2)where 后面不能写聚合函数，而 having 后面可以使用聚合函数。

(3)having 只用于 group by 分组统计语句。

案例：

求每个部门的平均工资

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

求每个部门的平均薪水大于 2000 的部门

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

 

Join 语句

等值 Join：Hive 支持通常的 SQL JOIN 语句，但是只支持等值连接，不支持非等值连接。

案例：根据员工表和部门表中的部门编号相等，查询员工编号、员工名称和部门名称

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

 

表的别名

好处：(1)使用别名可以简化查询。 (2)使用表名前缀可以提高执行效率。

案例：合并员工表和部门表

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

内连接:只有进行连接的两个表中都存在与连接条件相匹配的数据才会被保留下来。

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

左外连接：JOIN 操作符左边表中符合 WHERE 子句的所有记录将会被返回。

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

右外连接：JOIN 操作符右边表中符合 WHERE 子句的所有记录将会被返回。

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

满外连接：

将会返回所有表中符合 WHERE 语句条件的所有记录。如果任一表的指定字段没有符合条件的值的话，那么就使用 NULL 值替代。

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

 

多表连接

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

create table if not exists default.location( loc int,
loc_name string
)
row format delimited fields terminated by '\t';

导入数据

load data local inpath '/opt/module/data/location.txt' into table default.location;

多表连接查询

大多数情况下，Hive 会对每对 JOIN 连接对象启动一个 MapReduce 任务。本例中会首 先启动一个 MapReduce job 对表 e 和表 d 进行连接操作，然后会再启动一个 MapReduce job 将第一个 MapReduce job 的输出和表 l;进行连接操作。

注意:为什么不是表 d 和表 l 先进行连接操作呢?这是因为 Hive 总是按照从左到右的 顺序执行的。

 

笛卡尔积

1.笛卡尔集会在下面条件下产生
(1)省略连接条件 (2)连接条件无效 (3)所有表中的所有行互相连接

案例：select empno, dname from emp, dept;

排序：全局排序(Order By)，Order By:全局排序，一个 Reducer、使用 ORDER BY 子句排序 ASC(ascend): 升序(默认)、DESC(descend): 降序、ORDER BY 子句在 SELECT 语句的结尾

案例实操 (1)查询员工信息按工资升序排列
select * from emp order by sal;
(2)查询员工信息按工资降序排列
select * from emp order by sal desc;

 

按照别名排序

按照员工薪水的 2 倍排序

select ename, sal*2 twosal from emp order by twosal;

 

多个列排序

按照部门和工资升序排序

select ename, deptno, sal from emp order by deptno, sal ;

每个 MapReduce 内部排序(Sort By)、Sort By:每个 Reducer 内部进行排序，对全局结果集来说不是排序。

设置 reduce 个数：set mapreduce.job.reduces=3;

查看设置 reduce 个数：set mapreduce.job.reduces;

根据部门编号降序查看员工信息：select * from emp sort by empno desc;

将查询结果导入到文件中(按照部门编号降序排序)

insert overwrite local directory '/opt/module/datas/sortby-result'  select * from emp sort by deptno desc;

 

分区排序(Distribute By)

Distribute By:类似 MR 中 partition，进行分区，结合 sort by 使用。
注意，Hive 要求 DISTRIBUTE BY 语句要写在 SORT BY 语句之前。
对于 distribute by 进行测试，一定要分配多 reduce 进行处理，否则无法看到 distribute by的效果。

案例：先按照部门编号分区，再按照员工编号降序排序。

set mapreduce.job.reduces=3;

insert overwrite local directory '/opt/module/datas/distribute-result' select * from emp distribute by deptno sort by empno desc;

 

Cluster By
当 distribute by 和 sorts by 字段相同时，可以使用 cluster by 方式。
cluster by 除了具有 distribute by 的功能外还兼具 sort by 的功能。但是排序只能是升序排 序，不能指定排序规则为 ASC 或者 DESC。

以下两种写法等价

select * from emp cluster by deptno;
select * from emp distribute by deptno sort by deptno;

 

分桶及抽样查询

分桶表数据存储
分区针对的是数据的存储路径、分桶针对的是数据文件。
分区提供一个隔离数据和优化查询的便利方式。不过，并非所有的数据集都可形成合理 的分区，特别是之前所提到过的要确定合适的划分大小这个疑虑。
分桶是将数据集分解成更容易管理的若干部分的另一个技术。

 

先创建分桶表，通过直接导入数据文件的方式

创建分桶表

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

查看表结构

desc formatted stu_buck;

导入数据到分桶表中

load data local inpath '/opt/module/datas/student.txt' into table stu_buck;

发现并没有分成 4 个桶。是什么原因呢?

创建分桶表时，数据通过子查询的方式导入

先建一个普通的 stu 表

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

向普通的 stu 表中导入数据

load data local inpath '/opt/module/datas/student.txt' into table stu;

清空 stu_buck 表中数据

truncate table stu_buck

select * from stu_buck

导入数据到分桶表，通过子查询的方式

insert into table stu_buck  select id, name from stu;

发现还是只有一个分桶

hive (default)> set hive.enforce.bucketing=true;

hive (default)> set mapreduce.job.reduces=-1;

hive (default)> insert into table stu_buck select id, name from stu;

查询分桶的数据:select * from stu_buck;

分桶抽样查询

对于非常大的数据集，有时用户需要使用的是一个具有代表性的查询结果而不是全部结果。Hive 可以通过对表进行抽样来满足这个需求。

查询表 stu_buck 中的数据。

select * from stu_buck tablesample(bucket 1 out of 4 on id);

注:tablesample 是抽样语句，语法:TABLESAMPLE(BUCKET x OUT OF y) 。

y 必须是 table 总 bucket 数的倍数或者因子。

hive 根据 y 的大小，决定抽样的比例。

例 如，table 总共分了 4 份，当 y=2 时，抽取(4/2=)2 个 bucket 的数据，当 y=8 时，抽取(4/8=)1/2 个 bucket 的数据。x 表示从哪个 bucket 开始抽取，如果需要取多个分区，以后的分区号为当前分区号加上y。

例如，table 总 bucket 数为 4，tablesample(bucket 1 out of 2)，表示总共抽取(4/2=)2 个 bucket 的数据，抽取第 1(x)个和第 3(x+y)个 bucket 的数据。

注意:x 的值必须小于等于 y 的值，否则

FAILED: SemanticException [Error 10061]: Numerator should not be bigger than denominator in sample clause for table stu_buck

其他常用查询函数

空字段赋值

函数说明
NVL:给值为 NULL 的数据赋值，它的格式是 NVL( string1, replace_with)。它的功能是如果 string1 为 NULL，则 NVL 函数返回 replace_with 的值，否则返回 string1 的值，如果两个参 数都为 NULL ，则返回 NULL。
2)数据准备:采用员工表
3)查询:如果员工的 comm 为 NULL，则用-1 代替

如果员工的 comm 为 NULL，则用领导 id 代替

时间类

date_format:格式化时间

 date_add:时间跟天数相加

date_sub:时间跟天数相减

datediff:两个时间相减

CASE WHEN

求出不同部门男女各多少人。结果如下:

A    2    1
B    1    2

创建本地 emp_sex.txt，添加数据

创建 hive 表并导入数据

按需求查询数据