hive数据处理

刚工作两周，大量使用到hive，总结一下使用过程中遇到的问题和容易犯的错误！

示例一.解析出http://www.test.com/catalog1/catalog2/12345678中的12345678，类似于这样的需求。

一眼看到这样的需求，第一反应就是这是一个正则匹配的问题，用正则解析函数一下就搞定了regex_extract(uri,'(.*)/(\\d+)',2),但是在实际中会发现当数据量特别大了以后效率特别特别慢，并且正则解析是一个非常耗cpu的操作，于是就想 用其它方法，仔细看看数据发现字符串是有规律的,即12345678的这个字符串在uri字段中是定长的，并且12345678这个字符串的前缀也是定长的，所以改用substr(a,b,c),效率成倍的提升。

总结，对于可能会用到正则的需求，首先应该考虑能不能有其他的替代方案，然后就需要了解你的数据，如果能够确定你的数据是规则的，那么完全可以采用字符串函数，进行替代。总之就是，正则其实更适合处理不规则的字符串。用字符串函数处理规则的字符串效率会很高。

示例二.

table1-->订单表 主要字段：order_id total_price

table2-->订单商品表主要字段：order_id goods_idprice amount

table3-->商品表。主要字段：goods_id pricename

需求：从这三个表中查询出每个商品销售额；

一眼看上去这是一个简单的关联查询，于是：

select sum(total_price) from table1 join table2 on table1.order_id join table3 on table2.goods_id=table3 group by name;

也许我很弱智所以直接就犯了这个错误。

错误一：没有搞清楚订单表和订单商品表的关系，如果订单表和订单商品表是一对多的关系，那么本来只有一条订单的记录，一旦和订单商品表进行连接的时候订单的记录就会出现多条，这时候去做sum明显是不对的，直接把总价翻倍了;

错误二：没有搞清楚table3中的name是否是唯一，如果name不唯一那么不同的商品应该是由goods_id来决定而不应该是name;

在搞清楚这些问题了以后写出正确的sql语句

select name,sum(price*amount) from table1 join on table2 on table.order_id join table3 on table2.goods_id=table3.goods_id group by table2.goods_id;

总结，在写sql的时候必须要清楚各个表之间的关联关系。字段含义，只有清楚的知道了这些以后才能写出正确的sql;

示例三.举例说明grouping sets() 方法的使用；现在有一个商品表，商品可能存在于一，二，三级类目下。如果一个商品属于某一类的三级类目，那么它也同时属于一级和二级类目；

需求:统计出一级类目，二级类目，三级类目中的商品的个数；

select catalog1,count(1) as goods_amount from test group by catalog1

union all

select catalog1,catalog2,count(1) as goods_amount from test group by catalog1,catalog2

union all 

select catalog1,catalog2,catalog3,count(1) as goods_amount from test group by catalog1,catalog2,catalog3;

可以看见这个sql很长，但是冗余的部分很多，所以我们可以用比较优雅的方式来替代它，即：

select GROUPING__ID,catalog1,catalog2,catalog3,count(1) as goods_amount from test group by catalog1,catalog2,catalog3 grouping sets(catalog1,(catalog1,catalog2),(catalog1,catalog2,catalog3)); 

同时它也等同于select GROUPING__ID,catalog1,catalog2,catalog3,count(1) as goods_amount from test group by catalog1,catalog2,catalog3 with ROLLUP;

grouping sets((...),(....));的意思是说根据你group by 出来的结果可以进一步的对其再分组，GROUPING__ID也是一个函数，它返回的是分组的编号。

还有一种是select GROUPING__ID,catalog1,catalog2,catalog3,count(1) as goods_amount from test group by catalog1,catalog2,catalog3 with CUBE;

group by catalog1,catalog2,catalog3 with CUBE;等价于group by catalog1,catlog2,catalog3 grouping sets((catalog1),(catalog2),(catalog3),(catalog1,catalog2),(catalog2,catalog3),(catalog1,catalog3),(catalog1,catalog2,catalog3));也就是说with CUBE返回的是序列catalog1,catalog2,catalog3的全序集；

示例四.分组的topN问题；

例如:统计每个部门每年年薪排名前3的职工的姓名

select t.department,t.year,t.year_salary from (select department,year,name,sum(salary) as year_salary,row_number() over(distribute by department,year sort by sum(salary) desc) as linenumber from employee group by department,year,name) t where t.linenumber<=3;

这里主要用到了row_number() over()两个窗口函数（统计函数），底层操作以及更多的含义需要以后查明；

总结：还有很多更复杂的情况没有一一说明，总之就是数据处理不能仅仅关注数据，数据怎么来的；最后应该以何种方式呈现；业务逻辑等和数据有关的事情更是需要我们去了解，否则出现数据错误，或者是数据表达不清晰等不好的事情发生。

从数据的打印，收集，存储，处理，展现；这个过程是复杂的，任何一个环节出现错误最后导致的结果就是结论错误，所以作为一名数据分析狮，要做和关心的事情还很多，加油吧，少年！