hive的查询

查询注意事项：

1、order by ==全局排序，因此只有一个reducer，会导致当输入规模较大时，需要较长的计算时间。
2、sort by不是全局排序，其在数据进入reducer前完成排序。因此，如果用sort by进行排序，并且设置mapred.reduce.tasks>1，则sort by只保证每个reducer的输出有序，不保证全局有序。
3、distribute by(字段)根据指定的字段将数据分到不同的reducer，且分发算法是hash散列，相当于分桶操作
4、Cluster by(字段) 除了具有Distribute by的功能外，还会对该字段进行排序。相当distribute by+sort by

like和rlike

查找成绩中含9的所有成绩数据
select * from score where s_score rlike ‘[9]’;

分组

group by

注意group by的字段，必须是select后面的字段，select后面的字段不能比group by的字段多，函数不算

（1）计算每个学生的平均分数
select s_id ,avg(s_score) from score group by s_id;

having

求每个学生平均分数大于85的人
select s_id ,avg(s_score) avgscore from score group by s_id having avgscore > 85;

join语句

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

select
a.,b.
from tablea a inner join tableb b on a.id=b.id;

左连接（左表中符合WHERE子句的所有记录将会被返回。）

select
a.,b.
from tablea a left outer join tableb b on a.id=b.id;
左表有的显示 左表有右表没有显示null 左表没有右表有 不显示

右连接（右表中符合WHERE子句的所有记录将会被返回。）

全外链接

select
a.,b.
from tablea a full outer join tableb b on a.id=b.id;
左右表取并集

半连接

左半连接
select
a.*
from tablea a left semi join tableb b on a.id=b.id;
查找的左半边表中id在右半边表中id存在的数据 返回的是左表中存在的数据

排序

order by全局排序

Order By：全局排序，一个reduce
1）使用 ORDER BY 子句排序
ASC（ascend）: 升序（默认）
DESC（descend）: 降序
2）ORDER BY 子句在SELECT语句的结尾。
3）案例实操
（1）查询学生的成绩，并按照分数降序排列
SELECT * FROM student s LEFT JOIN score sco ON s.s_id = sco.s_id ORDER BY sco.s_score DESC;
（2）查询学生的成绩，并按照分数升序排列
SELECT * FROM student s LEFT JOIN score sco ON s.s_id = sco.s_id ORDER BY sco.s_score asc;
（3）按照学生分数的平均值排序
select s_id ,avg(s_score) avg from score group by s_id order by avg;

sort by局部排序

Sort By：每个MapReduce内部进行排序，对全局结果集来说不是排序。
1）设置reduce个数
set mapreduce.job.reduces=3;
2）查看设置reduce个数
set mapreduce.job.reduces;
3）查询成绩按照成绩降序排列
select * from score sort by s_score;
4）将查询结果导入到文件中（按照成绩降序排列）生成三个文件
insert overwrite local directory ‘/export/servers/hivedatas/sort’ select * from score sort by s_score;

分区排序（DISTRIBUTE BY）

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

CLUSTER BY

当distribute by和sort by字段相同时，可以使用cluster by方式。
cluster by除了具有distribute by的功能外还兼具sort by的功能。但是排序只能是倒序排序，不能指定排序规则为ASC或者DESC。
1）以下两种写法等价
select * from score cluster by s_id;
select * from score distribute by s_id sort by s_id;

hive的shell参数

-e 执行sql语句
-f 执行脚本

参数配置

1，配置文件
Hive的配置文件包括
用户自定义配置文件：$HIV{E}_{C}ON{F}_{D}IR/hive-site.xml默认配置文件：$HIVE_CONF_DIR/hive-default.xml
用户自定义配置会覆盖默认配置。
另外，Hive也会读入Hadoop的配置，因为Hive是作为Hadoop的客户端启动的，Hive的配置会覆盖Hadoop的配置。
配置文件的设定对本机启动的所有Hive进程都有效。

2，命令行的参数
启动Hive（客户端或Server方式）时，可以在命令行添加-hiveconf param=value来设定参数，例如：
bin/hive -hiveconf hive.root.logger=INFO,console
这一设定对本次启动的Session（对于Server方式启动，则是所有请求的Sessions）有效。

3，参数声明
可以在HQL中使用SET关键字设定参数，例如：
set mapred.reduce.tasks=100;
这一设定的作用域也是session级的。