七. 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 子句
- 概述
按照某些字段进行排序
现象：order by 可以使用多列进行排序（全局排序），此操作在reduce阶段进行，只有一个reduce
问题：数据量很大
- 全局排序：order by
asc|desc
- 案例
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 子句（多表连接查询）（子查询）
- 概述
两张表m,n之间可以按照on的条件进行连接，m中的一条记录和n表中的一条记录组成一个新的记录。
join 等值连接（内连接）：只有某个值在m和n中同时存在时，才连接。
left outer join (左外连接) ：左表中的值无论在右表中是否存在，都输出；右表中的值只有在左表中存在才输出
right outer join (右外连接) ：相反
mapjoin （map端完成连接）: 在map端进行表的连接操作，基于内存（优化的方式）
- 案例
等值连接：
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执行的顺序
- 子查询
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 汽车类型)