hive sql 学习笔记

1、coalesce

    语法：

COALESCE ( expression [ ,...n ] )   

　　参数：

　　expression 任何类型的表达式。

　　返回类型：

　　返回数据类型优先级最高的 expression 的数据类型。 如果所有表达式都不可为 Null，则结果的类型也不可为 Null。

　　备注

 

  　如果所有参数均为 NULL，则 COALESCE 返回 NULL。 至少应有一个 Null 值为 NULL 类型。

 

　　比较 COALESCE 和 CASE

COALESCE 表达式是 CASE 表达式的语法快捷方式。 即查询优化器将代码 COALESCE(expression1,...n) 重写为以下 CASE 表达式：

CASE

WHEN (expression1 IS NOT NULL) THEN expression1

WHEN (expression2 IS NOT NULL) THEN expression2

...

ELSE expressionN

END

这意味着将多次计算输入值（expression1、expression2、expressionN 等）。 此外，为了符合 SQL 标准，包含子查询的值表达式被视为不确定的且子查询被计算两次。 在每种情况中，第一次计算和后续计算可能返回不同的结果。

2、having子句

　　sql中having子句与where子句类似，都是表示条件的设定，它们的区别在于，having子句在查询过程中慢于聚合语句(sum,min,max,avg,count)；而where子句在查询过程中则快于聚合语句(sum,min,max,avg,count)。

　　简单说来：  

　　where子句：  
　　select sum(num) as rmb from order where id>10  
　　//先查询出id大于10的记录才能进行聚合语句  

　　having子句:  
　　select reportsto as manager, count(*) as reports from employees  
　　group by reportsto having count(*) > 4  

　　//having的条件表达式count()是一个聚合语句，因此having的执行一定慢于聚合语句count，如果换成where则会出错。

　　//统计分组数据时用到聚合语句，对分组数据再次判断时要用having。如果不用这些关系就不存在使用having。直接使用where就行了。  

　　having就是来弥补where在分组数据判断时的不足。因为where要快于聚合语句。

　　如果你对何时应该使用WHERE，何时使用HAVING仍旧很迷惑，请遵照下面的说明：   

　　WHERE语句在GROUP BY语句之前；SQL会在分组之前计算WHERE语句。   

　　HAVING语句在GROUP BY语句之后；SQL会在分组之后计算HAVING语句。

3、mapjoin

使用MAPJOIN时，需要注意：
1、LEFT OUTER JOIN的左表必须是大表；
2、RIGHT OUTER JOIN的右表必须是大表；
3、INNER JOIN左表或右表均可以作为大表；
4、FULL OUTER JOIN不能使用MAPJOIN；
5、MAPJOIN支持小表为子查询；
6、使用MAPJOIN时需要引用小表或是子查询时，需要引用别名；
7、在MAPJOIN中，可以使用不等值连接或者使用OR连接多个条件；

　　mapjoin里写的是小表，且left outer join时小表写在join的后面；

　　hive中使用mapjoin有时可以大大提高sql语句的执行效率。

　　其原理是：它会把小表全部读入内存中，在map的时候直接拿另外一张表的数据和内存中表的数据做匹配，进行join操作，这样省去了reduce。

　　（1）在“关联操作中有一个表非常小，另一个表很大”的场景下，mapjoin就不会由于数据倾斜而导致某个reduce上落数据太多而失败；

　　例子：

SELECT /*+ MAPJOIN(b) */ a.key, a.value

FROM a JOIN b ON a.key = b.key

• does not need a reducer. For every mapper of A, B is read completely. The restriction is that a FULL/RIGHT OUTER JOIN b cannot be performed.

（2）在需要进行“不等值连接操作”的场景中（如a.x<b.y 或 a.x like b.y 等），由于where子句中的不等值join操作产生笛卡尔积，引起数据异常增大，速度会变慢；而mapjoin则可以提高此操作的效率，即使遇到笛卡尔积也不会对运行速度带来太大影响；

　　例子：

　　

select /*+ MAPJOIN(a) */
  a.start_level, b.*
 from dim_level a
  join (select * from test) b
 where b.xx>=a.start_level and b.xx<end_level;

　　在同时需要进行大小表关联和不等值关联的情景下，用mapjoin会有更明显的效果，尤其是大表数据倾斜比较严重的时候。

　　例子：

　　使用mapjoin前：

create table hive_no_mapjoin as 

　　select f.id,f.dt, coalesce(k.amt,0.0) amt from(

　　select a.id,t.dt from hive_dt t join (select id, min(dt) min_dt from hive_mapjoin group by id) a
　　where t.dt>= a.min_dt) f 
　　left outer join hive_mapjoin k on f.dt = k.dt and f.id = k.id;

　　使用mapjoin后：

create table hive_ok_mapjoin as select f.id,f.dt, coalesce(k.amt,0.0) amt from(

select /*+ mapjoin(t) */ a.id,t.dtfrom hive_dt t 
join (

select id,  min(dt) min_dt from hive_mapjoin group by id) a 
where  t.dt>= a.min_dt) f 
left outer join tmp.tst1 kon f.dt = k.dt and f.id = k.id;

　　友情链接：http://www.cnblogs.com/tmeily/p/4250858.html

　　　　　　　http://www.ithao123.cn/content-579190.html

4、hive中，group by 需要比较多的reduce ， order by rand()需要比较多的map reduce，所以当数据量比较大的时候，如果这两个动作同时执行，会很耗资源，解决的办法是分成两步，先order by rand() limit n 取出来，然后再从这个结果里面group by;

5、hive中，需要select 多个字段，但只要distinct某个字段并列出它的值，可以用：

select cid,count(distinct cid) from credit.vec_sim_pairs where dt=${CUR_DATE} and cid!='0' group by cid；

忽略count(distinct cid)这一列就可以了。

参考来源：http://www.jb51.net/article/24717.htm

 

6、查看某个表的某个分区的hdfs路径 语句：

use database;

show table extended like table_name partition(dt='%Y%m%d')

 

7、随机抽样的语句：（1）select * from tablename TABLESAMPLE(n PERCENT|ROWS|ByteLengthLiteral ) where ……

其中，n percent将会抽取 n%的比例数据 （但是这种方法结果出来的数据并没有真的是按比例抽取）

　　　　　　　　　（2）select * from my_table distribute by rand() sort by rand() limit 10000;（亲测可靠）

 

参考：http://www.joefkelley.com/736/

　　　http://www.aichengxu.com/other/9663663.htm

　　   http://lxw1234.com/archives/2015/08/444.htm

8、hive中字段类型转换

　　cast(field as type)

或者：

　　convert(field,type)

 

 9、explain

　　使用explain可以查看hql语句运行的详细信息；

例如：

explain select a.key c1, a.value c2, b.key c3, b.value c4 from src a join src_skewjoin1 b on a.key = b.key;