hive学高级查询常用函数关键字总结

问题导读
1、Hive查询语句和SQL查询语句区别与联系。
2、distribute by、group by和Sort by的区别。
3、MapJoin的优缺点是什么？



聚合函数
1.count计数
count(*)：不全都是NULL，就加1；count(1):当只要有一列是NULL就不会加1；count(col)：当col列不为空就会加1
2.sum求和
sum(可转成数字的值)返回bigint，比如求和后加1,1必须转化成为bigint类型，sum(col)+cast(1 as bigint)
3.avg求平均值
avg(可转化成数字的值)返回double
4.distinct不同值的个数
count(distinct col)

Order by
按照某些字段排序，后面可以有很多列进行排序，默认是字典排序。Order by 是全局排序，Order by这个过程只需要一个且只有一个reduce，无论你配置多少各reduce，到最后都会汇总一个reduce进行Order by，所以当数据量非常大的时候要考虑好是否使用Order by；
select c1,other from tableName where condition order by c1,c2[asc|desc]
如：select * from usertable order id asc,name desc;

Group by
根据某些字段值进行分组，有相同值的放到一起。注意，select后面查询的列，除了聚合函数外都应该出现再group by中，group by后面也可以跟表达式，如substr(col),对某一列字段截取的部分进行分组。having是在group by之后进一步进行筛选的。他使用了reduce操作，受限于reduce个数，设置reduce参数mapred.reduce.tasks。文件的输出个数就是reduce个数，文件大小与reduce处理的数据量相关。存在的问题：网络负载过重；数据倾斜，优化参数hive.groupby,skeweindata设置为true(起两个mapreduce，第一个在每个key加一个随机值，然后进行group by)。

select c1,[c2..] count(1).. from test_group where condition group by c1[,c2..] [having]
set mapred.reduce.tasks=n;
set hive.groupby.skewindata=true;
select name,count(1) as num from tableName group by name;

group by 与distinct
都能达到去重的效果，走的mapreduce都是一样的流程，其reduce个数优化，还有hive.groupby.skewindata都起作用。group by中查找的必须在group by 中出现，但是distinct可以，但是distinct需要后面的多个col都相同才会去重。

Join表连接
两个表m和n之间按照on条件进行连接，m中的一条记录和n中的一条记录组成一条新的记录。
join：等值连接(内连接)，只有m和n中同时存在才会筛选出。
如：
tableA col1 col2
       1   w
       2   j
       4   g
tableB col3 col4
      1   y
      1   t
      5   z
select a.col1,a.col2,b.col4 from (select col1,col2 from tableA) a join (select col3,col4 from tableB) b on a.col1=b.col3
     result:
     1 w y
     1 w t
left outer join：左边的表的值无论是否在右表中出现，都会作为结果输出，如果右边的值不存在则为NULL。而右边表的值只有在左边的值出现才会出现。
select a.col1,a.col2,b.col4 from (select col1,col2 from tableA) a left outer join (select col3,col4 tableB) b on a.col1=b.col3
    result:
    1 w y
    1 w t
    2 j NULL
    4 g NULL
right outer join：与left outer join正相反
  result：
    1 w y
    1 j y
    5 NULL z
left semi join :类似exists，判断左表中数据是否在右表中，如果在则筛选出来。
select a.col1,a.col2,b.col4 from (select col1,col2 from tableA) a left semi join (select col3,col4 from tableB) b on a.col1=b.col3
    result:
    1 w y
mapjoin：在map端完成join操作，不需要reduce，基于内存做join，属于优化操作，但是需要将整个表加载到内存中去。

select m.col1 as col1,m.col2 as col2,n.col3 as col3
from
(select col1,col2 from tableA where condition(在map端执行) )m
[left outer|right outer|left semi] join
(select col3 from tableB)n
on m.col1= n.col3 [and condition]
where condition(在reduce端执行)
注：hive中join不能使用类似<,>这种比较
如果寻在数据倾斜问题可以设置：
set hive.optimize.skewjoin=true;

MapJoin
在map端把小表加载到内存中，然后读取大表，和内存中的小表进行比较。其中使用了分布式缓存。
优缺点：
不消耗reduce资源(reduce相对较小)；减少reduce操作，加快程序执行；降低网络负载；
占用部分内存，所以加载到内存中的表不能太大，因为每个计算节点都会加载一次。生成较多小文件。(每个map对应一个输出文件)
使用MapJoin：
自动使用
配置：set hive.auto.convert.join=true;
set hive.mapjoin.smalltable.filesize=n(当查询的表中小表小于等于这个值就会使用MapJoin。但是可能会有很多任务，都加载进去了消耗内存)
手动指定
slelet /*+mapjoin(n)*/ m.col1,m.col2,n.col4 from (...)m join (...)n on m.col1=n.col3
也就是在select中将小表使用/*+mapjoin(n)*/参数加载到内存中去

Distribute by 和 Sort by
distribute by col1,col2
按照col列将数据分散到不同的reduce
sort by col1，col2[asc|desc]
将col1，col2列排序(这是将每个reduce内部排序)
两者接合使用，确保每个reduce的输出都是有序的，但是整体不是有序的。

distribute by 和 group by
都是按照key划分数据
都是用reduce操作
distribute by只是单纯的分散数据，而group by是将相同的key聚集到一起，后续必须是聚合操作
order by 和sort by
order by确保全局有序
sort by只是保证每个reduce上面输出有序，如果只有一个reduce时，和order by效果一样
应用场景
小文件过多(通过reduce个数来控制输出文件个数)
文件超大
map输出的文件大小不均
reduce输出的文件大小不均

cluster by
把有相同的数据聚集到一起，并排序
cluster by col
相当于：distribute by col col order by col

union all
将多个表的数据合并成一个表，hive部支持union操作
select col
from(
select a as col from t1
union all
select b as col from t2
)tmp
没有reduce操作
要求：
字段名字一样(可以通过as使用别名)
字段类型一样
字段个数一样(子查询的个数)
字表不能又别名(join每个子查询要别名)
合并之后的表如果需要查询数据，则需要给合并结果起别名

count(*)：不全都是NULL，就加1；count(1):当只要有一列是NULL就不会加1
确定是这样么？？我怎么不知道，试验了一下好像不是啊，楼主！！

over()  这些分析函数一起加上，可能会更好