Hive学习路线-Hive的DQL

七. Hive的DQL

1. 语法

select [distinct|all] expr，expr... ... from tbName where whereCondition group by colList having havingCondition order by colList cluster by colList limit num

2. where子句

is null is not null between...and... in(v1,v2,v3,... ...) | not in( v1,v2,v3,... ...) and or like | rlike % 通配符 _ 占位符

3. group by 子句

概述：

按照某些字段的值进行分组，有相同值的放置到一起

通常和聚合函数一起使用

SQL案例：

select c1 ,c2

where condition ------>map端执行

group by c1,c2 ------>reduce端执行

having condition ------>reduce端执行

3.1 案例：

1. 建表 create table order_tb(oid int ,num int ,pname string ,uid int) row format delimited fields terminated by ','; 导入数据： load data local inpath '/home/zhangsan/order.txt' into table order_tb; create table user_tb(uid int ,name string ,addr string) row format delimited fields terminated by ','; 导入数据： load data local inpath '/home/zhangsan/user.txt' into table user_tb; 2. 注意 select 后面非聚合列，必须要出现在group by 中 可以使用as对表和列设置别名 having和where的区别： 位置，condition使用的过滤字段，针对的数据（单位：一条数据；单位：一组数据） 3. 按照用户id分组查询订单表 select o.uid from order_tb as o group by o.uid 使用分组函数|聚合函数：查询用户购买商品数量 select o.uid , count(o.num) from order_tb as o group by o.uid 使用分组函数|聚合函数：查询用户购买商品数量，显示数量大于2的信息 select o.uid , count(o.num) from order_tb as o group by o.uid having count(o.num) >2; 4. 常见的聚合参数 count() max() min() sum() avg()

4. order by 子句

1. 概述

按照某些字段进行排序

现象：order by 可以使用多列进行排序（全局排序），此操作在reduce阶段进行，只有一个reduce

问题：数据量很大

2. 全局排序：order by

asc|desc

3. 案例

select * from order_tb order by num desc; select * from order_tb order by num ,oid asc ; // 多字段排序

5. sort by 排序

在mapreduce内部的排序，不是全局排序 1. 设置reduce的个数 默认 < 配置文件 < 命令行 set key=value 设置属性 set key 获取属性 set mapreduce.job.reduces=3; 设置属性值 set mapreduce.job.reduces ; 查看属性值 2. 执行排序 select * from order_tb sort by num; 3. 查看reduce中各个阶段输出的结果数据（将结果导出到本地查看） insert overwrite local directory '/home/zhangsan/sortbyResult' select * from order_tb sort by num desc; 总结：只对当前的分区进行内容的排序

6. distribute by 分区

mapreduce中的partition操作,进行分区，结果sort by使用 distribute by字句需要写在sort by之前（先分区，在排序） 案例：sql转成mr时，内部使用oid字段进行分区，使用num字段进行排序 insert overwrite local directory '/home/zhangsan/distributebyResult' select * from order_tb distribute by oid sort by num desc;

7. cluster by 分区并排序

insert overwrite local directory '/home/zhangsan/clusterbyResult' select * from cluster by num ; 注意：分区和排序的字段需要一致 不可以指定排序的方向

8. join 子句（多表连接查询）（子查询）

1. 概述

两张表m,n之间可以按照on的条件进行连接，m中的一条记录和n表中的一条记录组成一个新的记录。

join 等值连接（内连接）：只有某个值在m和n中同时存在时，才连接。

left outer join (左外连接) ：左表中的值无论在右表中是否存在，都输出；右表中的值只有在左表中存在才输出

right outer join (右外连接) ：相反

mapjoin （map端完成连接）: 在map端进行表的连接操作，基于内存（优化的方式）

2. 案例

等值连接：

select from user_tb u join order_tb o ; // 笛卡尔积现象

select u.name,o.num,o.pname from user_tb u join order_tb o on u.uid=o.uid ; //添加过滤条件避免，笛卡尔积现象

左外连接：（和右外连接相对的）

insert into user_tb values(10004,‘xiaoliu’,‘beijing’);

select u.name,o.num,o.pname from user_tb u

left join order_tb o on u.uid=o.uid ;

注意：表和表的一次连接，代表一个MapReduce 作业job

如果sql语句中存在多个表之间的连接，会创建多个job作业，连接的顺序就是job执行的顺序

3. 子查询

SQL语句的嵌套查询

select * from (select * from ...) selct * from tbName where col = (select * from ...)

嵌套的SQL语句中，子SQL语句的结果 可以作为查询的数据源，也可以作为查询的条件

统计汽车类型的分布：

商用 n1/count

个人 n2/count

hive:

select 汽车的类型 , count() from cart group by 汽车类型

select count() from cart

select 汽车类型 ， 当前类型数量/ (select count(*) from cart ) from (select 汽车的类型 , count(*) from cart group by 汽车类型)