Hive常用查询

1.关系运算（=，!=，<，<=，>，>=，is null，is not null，like）

select * from ylq where name = 'ylq'

select * from ylq where name != 'ylq'

select * from ylq where age < 10

select * from ylq where age <= 12

select * from ylq where age > 10

select * from ylq where age >= 10

select * from ylq where null is null

select * from ylq where name is not null

select * from ylq where name like '%lin%'

2.逻辑运算（and，or）

select * from ylq where name = 'ylq' and age = 12

select * from ylq where name = 'ylq' or name = 'zq'

3.常用函数

1.round(a)取整函数
select round(1.231) from ylq
> 1

2.round(a, b)指定精度取整函数
select round(1.2321, 2) from ylq
> 1.23

3.floor(a)向下取整函数
select floor(1.64) from ylq
> 1

4.ceil(a)向上取整函数
select ceil(1.12) from ylq
> 2

5.rand()随机函数
返回一个0-1之间的随机数
select rand() from ylq
> 0.21321231

6.log10(a)以10为底的对数函数
select log10(100) from ylq
> 2 

7.log2(a)以2为底的对数函数
select log2(8) from ylq
> 3

8.log(a, b)对数函数
select log(3, 9) from ylq
> 2

9.pow(a, b)幂运算
select pow(2, 3) from ylq
> 8

10.sqrt(a)开平方函数
select sqrt(16) from ylq
> 4

11.abs(a)绝对值函数
select abs(-1) from ylq
> 1

12.size(array())数组大小函数
select size(split(phone, ',')) from ylq
> 4

13.split(a, b)字符串切割函数
select split('123,345,456', ',') from ylq
> ["123", "345", "456"]

14.array_contains(a, b)判断数组中是否存在元素
select array_contains(split('123,456,789', ','), '123') from ylq 
> true

15.map_values(a)获取map中的所有值
select map_values(map('k1', 'v1', 'k2', 'v2')) from ylq
> ["v1", "v2"]

16.map_keys(a)获取map中的所有key
select map_keys(map('k1', 'v1', 'k2', 'v2')) from ylq
> ["k1", "k2"]

17.sort_array(a)数组排序
select sort_array(2, 12, 1, 4, 3) from ylq
> [1, 2, 3, 4, 12]

18.from_unixtime(a, format)unix时间戳转换为日期函数
select from_unixtime(1469955207, 'yyyy-MM-dd HH:mm:ss') from ylq
> 2016-07-31 16:53:27

19.unix_timestamp()获取当前unix时间戳
select unix_timestamp() from ylq
> 1468211399
select unix_timestamp('2016-07-11 12:29:59') from ylq
> 1468211399

20.if(a, true, false)条件函数
select if(1=2, 1, 0) from ylq
> 0

21.concat(a,b...)字符串拼接函数
select concat('ab', 'cd', 'ef') from ylq
> abcdef

22.concat_ws(sep, a, b...)带分隔符的字符串拼接函数
select concat_ws('|', 'ab', 'cd', 'ef') from ylq
> ab|cd|ef

23.concat_ws(sep, a)将数组转化为字符串
select concat_ws('|', array(1, 2, 3)) from ylq
> 1|2|3

24.substr(a, start, length)字符串截取
select substr('abcdfsd', 3, 2) from ylq
> cd

25.length(a)字符串长度
select length('asd') from ylq
> 3

26.localte(a, b, start)字符串查找函数,返回字符串首次出现的位置
select locate('a', 'abc', 2) from ylq
> 0
select localte('a', 'abc', 1) from ylq
> 1
select if(locate('a', 'bc') = 0, 'not find', 'find') from ylq
> not find

27.parse_url(url, value)解析URL地址
value：HOST，PATH，QUERY， REF....

select parse_url('http://www.baidu.com/path/index.php?site=cd&uid=123', 'HOST') from ylq
> www.baidu.com
select parse_url('http://www.baidu.com/path/index.php?site=cd&uid=123', 'PATH') from ylq
> /path/index.php
select parse_url('http://www.baidu.com/path/index.php?site=cd&uid=123', 'QUERY') from ylq
> site=cd&uid=123
select parse_url('http://www.baidu.com/path/index.php?site=cd&uid=123', 'QUERY', 'site') from ylq
> cd

28.get_json_object(json_string, key)解析json字符串
select get_json_object('{"uid":123,"infos":{"math":99,"english":100}}', '$.uid') from ylq
> 123
select get_json_object('{"uid":123,"infos":{"math":99,"english":100}}', '$.infos') from ylq
> {"math":99,"english":100}
select get_json_object('{"uid":123,"infos":{"math":99,"english":100}}', '$.infos.math') from ylq
> 99

29.split(a)字符串分割函数
select split('ab,cd,ef') from ylq
> ["ab", "cd", "ef"]

30.count()个数统计函数
select count(*) from ylq
> 10

31.sum()求和统计函数
select sum(age) from ylq
> 55

32.explode(a)将数组切割为多行（用于列转行）
将列字符串变为数组：
select split('aaa,bbb,ccc,ddd', ',') from ylq
> ["aaa", "bbb", "ccc", "ddd"]
将数组切割为行
select msg from (select split('aaa,bbb,ccc,ddd', ',') as content from ylq) tmp lateral view explode(content) b as msg
> aaa
> bbb
> ccc
> ddd


32.collect_set()行转列（去重，转化为一个数组）
ylq aa
ylq bbb
ylq aa
zq  ccc
zq  ddd
select name,collect_set(info) from ylq group by name
> ylq ["aa", "bbb"]
> zq  ["ccc", "ddd"]

33.collect_list()列转行（不去重）