hive查询

查询

基础语法

select [ALL|DISTINCT] 字段名1,字段名2
from 表名   -- 从哪个表查询
[where 分组前过滤条件]  -- 进行分组前的过滤
[group by 分组字段]  -- 分组查询
[having 分组后过滤条件]  -- 分组后的过滤条件
[order by 字段名]   --排序
[cluster by 字段名]
[distribute by 字段名]
[sort by 字段名]
[limit 输出行数]

执行顺序：
1、先执行from 表名 来表示从哪个表查询数据
2、然后执行where 过滤条件进行分组前的过滤
3、然后执行group by按某一个字段进行分组
4、然后执行select 查询语句
5、然后执行having对查询出来的数据进行一个分组后的过滤
6、最后对数据进行order by 排序
7、利用limit用来输出多少行记录

基本查询

1、例子1：全表查询数据

-- 从emp表中查询所有数据
select * from emp

2、例子2：特定列(empno,ename)查询

select empno,ename
from emp;

3、例子3：列名起名字，as可以省略

select ename as name
from emp;

4、例子4：limit语句查询

-- 查询前五行记录
select * from emp limit5;

-- 从第二行开始，向下查找3条记录
select *
from emp
limit 2,3;

5、例子5：利用where条件过滤

select *
from emp
where sal>1000;

关系运算函数

a=b：如果a等于b则返回true，否则返回false

a<=>b：如果a和b都为null或者都不为null，则返回true，如果有一边是null，返回false

a<>b,a!=b：a或者b为null，则返回null；如果a不等于b，则返回true，反之返回false

a<b：a或者b为null，返回null;如果a小于b，则返回true，反之返回false

a<=b：a或者b为null，则返回null；如果a小于等于b，则返回true，反之返回false

a>b：a或者b为null，则返回null；如果a大于b，则返回true，反之返回false

a>=b：a或者b为null，则返回null；如果a大于等于b，则返回true，反之返回false
a[not] between b and c：如果a，b或者c任一为null，则结果为null。如果a的值大于等于b而且小于或等于c，则结果为true，反之为false。如果使用not关键字则可达到相反的效果

a is null：如果a等于null，则返回true，反之返回false

a is not null：如果a不等于null，则返回true，反之返回false

in(数值1，数值2)：使用in运算显示列表中的值

a [not] like b：b是一个SQL下的简单正则表达式，也叫通配符模式，如果a与其匹配的话，则返回true；反之返回false。b的表达式说明如下：‘x%’表示a必须以字母‘x’开头，‘%x’表示a必须以字母‘x’结尾，而‘%x%’表示a包含有字母‘x’,可以位于开头，结尾或者字符串中间。如果使用not关键字则可达到相反的效果

a rlike b,a regexp b：b是基于java的正则表达式，如果a与其匹配，则返回true；反之返回false。匹配使用的是JDK中的正则表达式接口实现的，因为正则也依据其中的规则。例如，正则表达式必须和整个字符串A相匹配，而不是只需与其字符串匹配。

逻辑运算函数

and:逻辑与
or:逻辑或
not:逻辑否

聚合函数

（1）count(*)：表示统计所有行数，包含null值
（2）count(某一列)：表示该列一共由多少行，不包含null值
（3）max()：求最大值，不包含null，除非所有值是null
（4）min()：求最小值，不包含null，除非所有值都是null
（5）sum()：求和，不包含null
（6）avg()：求平均值，不包含null

分组

group by

having语句

(1)where是分组前过滤，不可以带分组函数
(2)having是分组后的过滤，可以带分组函数

Join语句

将两个表emp和dept进行等值连接

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

内连接

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

外连接

左外连接

概念：join操作符左边表中符合where子句的所有记录将会被返回

右外连接

概念：join操作符右边表中符合where子句中所有记录都会被返回

满外连接

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

多表连接

概念：连接n个表，至少需要n-1个连接条件。

笛卡尔积

（1）省略连接条件
（2）连接条件无效
（3）所有表中的所有行互相连接

联合

上下拼接的，而join是左右关联的
（1）union：去除重复记录
（2）union all：不会去除重复记录
注意：两个sql的结果，列的个数必须相同，上下列类型必须一致

排序

全局排序
order by：全局排序，只有一个reduce
asc:升序 desc:降序（默认）
每个reduce内部排序(sort by)
sort by：为每一个reduce产生一个排序文件，每个reduce内部进行排序，对全局结果集来说不是排序

分区(distribute by)
distribute by:有些情况，我们需要控制某个特定行到哪个reduce，distribute类似mapreduce中自定义分区，结合sort by使用

注意：distribute by的分区规则是根据分区字段的hash码与reduce个数进行相除后，余数相同的分到一个区

hive要求distribute by放到sort by后面

分区排序(cluster by)

当distribute by和sort by字段相同时，可以使用cluster by