遇见Hive之中级篇————hive的导入导出方式，分析及窗口函数，排序类型及UDTF函数设定，hiveserver2介绍

1、hive的几种导出方式

将数据导出到本地                                                                                                                                                                   -->insert overwrite local directory '/opt/datas/emp_insert' select * from emp;

将数据导出HDFS文件系统 (不加local，可以指定分隔符，输出的目录可以存在，底层会先进行删除输出目录的操作，然后将数据文件写入文件夹中)                                                                                                                    

-->insert overwrite directory '/make/emp_out' row format delimited fields terminated by ',' select * from emp;

通过HDFS的shell -get命令来获取数据到本地

-->dfs -get /make/emp_out/00000_0  /opt/data

通过hive -e  -f参数将输出结果重定向到本地文件中
 -->bin/hive -e 'select * from emp' >> hive.log

sqoop 等工具从mysql,oracle等数据库导出数据，这里暂不做介绍，因为我也不知道

示例表

2、hive常用过滤函数

• limit
• distinct （经常和count一起用）
• between xxx  and  xxx
• having (分组排序)      --> select deptno,avg(sal) avg_sal from emp group by deptno having avg_sal > 2000;

结果如下

筛选出来了  平均薪水大于2000的部门编号与平均薪资

3、hive常用聚合函数，

• count
• avg、sum
• min、max
• group by分组(去重)
  
• join      left join、right join、inner join(join)、full join、semi join、map join

join的用法，其实自己总结下来的就是，inner join 求得是关联字的都存在的才会查询出来，相当于两表的交集。

left join与reght join 一样 left就是左边的为主表，right就是右边的为主表，只要记着，主表的所有都会被查询出来，就算另外一个中并不存在，也会查询出来,已null值表示。

full join 就是相当于，两张表的所有都会被查询出来，不管对应的表是否存在。

semi join最主要的使用场景就是解决exist in。Hive不支持where子句中的子查询，SQL常用的exist in子句在Hive中是不支持的。

比如-->SELECT a.deptno, a.sal FROM emp a WHERE a.deptno in (SELECT b.deptno FROM dept b);可以改写为：

-->SELECT a.deptno, a.sal FROM emp a LEFT OUTER JOIN dept b ON (a.deptno = b.deptno) WHERE b.deeptno <> NULL;

一个更高效的实现为：  即为semi join 可以以左表为主表，同是去掉不合适的值

-->SELECT a.deptno, a.sal FROM emp a LEFT SEMI JOIN dept b on (a.deptno = b.deptno);

tips-1   hive函数参考大全：http://www.cnblogs.com/end/archive/2012/06/18/2553682.html

tips-2   行列转换的实现

我们有如下两个表，user_info   user_address  

可以看出 a和d分别拥有两个地址，我们要把它转成 a : aaa1,aaa2  d：ddd1,ddd2的格式

用到的concat_ws(string SEP, string A, string B…)    链接多个字符串，字符串之间以指定的分隔符分开。

collect_set(col)    返回无重复记录  

select collect_set(address))  from user_address;

-->collect_set(address)=["aaa1","aaa2","bbb","ccc","ddd1","ddd2","eee"]

select a.name,concat_ws(',', collect_set(address)) as address from user_address a group by a.name;

-->

select max(ui.id), ui.name, concat_ws(',', collect_set(uadr.address)) as adr from user_info ui join user_address uadr on ui.name=uadr.name group by ui.name;  

-->

4、窗口函数

select *,max(sal) over (partition by deptno order by sal desc) as windows from emp order by sal desc; 

对分区里的按找部门编号分区，取部门薪资的最大值为新的一列，并对薪资进行全局降序排序,max(sal) over 是说这个是基于

(partition by deptno order by sal desc) 这个max,为新的一列，在select * from emp order by sal desc; 的基础上，增加这个在新的计算下的新的一列，如下结果，取出了每个部门的最大值薪资

窗口函数，有带继续探索，（未完待续）