hql语句

hive中的null

null与任何值运算的结果都是null
函数有：is null 和 is not null
null在hive底层默认是用’\N'来存储的，可以通过alter table test SET SERDEPROPERTIES('serialization.null.format' = 'a');来修改

hiveql和sql语法的区别

没有等值连接 from a,b where a.name=b.name 在hive只能用 a join b on a.name=b.name
hiveql中对分好; 识别不太好 经常会报错 解决: 八进制的ASCII码进行转义
hiveql 中 “” is null 是false
hiveql没有insert into …update/datele 他只是追加数据
hive没有not in 不过用not exists代替了

子查询

Hive 当前没有实现 IN/EXISTS 子查询，可以用 LEFT SEMI JOIN 重写子查询语句

#原先的子查询
SELECT a.key, a.value
FROM a
WHERE a.key exist in
(SELECT b.key
FROM B);
可以被重写为：
SELECT a.key, a.val
FROM a LEFT SEMI JOIN b on (a.key = b.key)

Sort By，Order By，Cluster By，Distrbute By

1. order by：会对输入做全局排序，因此只有一个reducer。但是只有一个reducer，会导致当输入规模较大时，需要较长的计算时间。
2. sort by： 分区内有序，只针对分区内部进行排序
3. distribute by：按照指定的字段对数据进行划分输出到不同的reduce中。用hash散列算法 ，类似MR中Partition，进行分区，结合sort by使用。
4. cluster by：当Distribute by和Sorts by字段相同时，可以使用Cluster by方式代替。Cluster by除了具有Distribute by的功能外还兼具Sort by的功能。但是排序只能是升序排序，不能指定排序规则为ASC或者DESC。

select * from stu distribute by id sort by age desc;

行转列and列转行

#行转列
select col1,col2,concat_ws(',',collect_set(col3)) 
from tmp_jiangzl_test  
group by col1,col2;

#列转行
select col1, col2, col5
from tmp_jiangzl_test a 
lateral  view explode(split(col3,','))  b AS col5

Multi-group by

from A
insert overwrite table B
 select A.a, count(distinct A.b) group by A.a
insert overwrite table C
  select A.c, count(distinct A.b) group by A.c

split、coalesce及collect_list函数的用法

split('a,b,c,d' , ',') ==> ["a","b","c","d"] 将字符串转化为数组。
coalesce(T v1, T v2, …) 返回参数中的第一个非空值；如果所有值都为 NULL，那么返回NULL。
collect_list列出该字段所有的值，不去重  select collect_list(id) from table;