Hive SQL 函数实战-优快云博客

开始之前，先了解几个命令：

在Hive CLI中，可以使用如下一些命令

显示当前会话有多少函数可用

show functions;

显示函数的描述信息

desc function concat；

显示函数的扩展描述信息

desc function extended concat；

现在来练习下Hive中的一些常用内置函数

先来查看下表winfunc：

hive (default)> desc winfunc;
OK
col_name	data_type	comment
id                  	int                 	                    
money               	float               	                    
type                	string              	                    
Time taken: 0.132 seconds, Fetched: 3 row(s)
hive (default)> select * from winfunc;
OK
winfunc.id	winfunc.money	winfunc.type
1001	100.0	ABC
1001	150.0	BCD
1001	200.0	CDE
1001	150.0	DEF
1002	200.0	ABC
1002	200.0	ABC
1002	100.0	BCD
1002	300.0	CDE
1002	50.0	DEF
1002	400.0	EFG
1003	100.0	ABC
1003	50.0	BCD
1004	60.0	ABC
Time taken: 1.177 seconds, Fetched: 13 row(s)

1. and和or的优先级

现在想要求下id为1001、1002的两个人工资为100.0时候的记录

hive (default)> select id,money,type from winfunc where id=1001 or id=1002 and money=100.0;
OK
id	money	type
1001	100.0	ABC
1001	150.0	BCD
1001	200.0	CDE
1001	150.0	DEF
1002	100.0	BCD
Time taken: 0.13 seconds, Fetched: 5 row(s)

发现结果并不是我们想要的，为什么呢？其实是因为or和and优先级不一样导致的，因为and的优先级比or高，所以出现了上面的结果，如果要得到我们想要的结果，该怎么处理呢？

hive (default)> select id,money,type from winfunc where (id=1001 or id=1002) and money=100.0;
OK
id	money	type
1001	100.0	ABC
1002	100.0	BCD
Time taken: 0.147 seconds, Fetched: 2 row(s)

2. cast类型转换

如果想要将上面结果中的money类型转换成int类型，该如何实现呢？

hive (default)> select id,cast(money as int),type from winfunc where (id=1001 or id=1002) and money =100.0;
OK
id	money	type
1001	100	ABC
1002	100	BCD
Time taken: 2.489 seconds, Fetched: 2 row(s)

3. case...when

hive (default)> select case 
              >  when id='1001' then 'tom' 
              >  when id='1002' then 'kitty'
              >  when id='1003' then 'jerry'
              >  else 'others'
              > end
              > from winfunc group by id;

OK
_c0
tom
kitty
jerry
others
Time taken: 1.505 seconds, Fetched: 4 row(s)

4. parse_url

语法：parse_url(string urlString, string partToExtract [, string keyToExtract])。

返回值：string

返回 URL 中指定的部分。 partToExtract 的有效值为： HOST, PATH, QUERY, REF, PROTOCOL, AUTHORITY, FILE, and USERINFO.

hive (default)> select 
              > parse_url('http://master:50070/explorer.html#/hive','HOST')
              > from winfunc limit 1;
OK
_c0
master
Time taken: 0.102 seconds, Fetched: 1 row(s)

5. concat

语法: concat(string A, string B…)

返回值: string

说明：返回输入字符串连接后的结果，支持任意个输入字符串

hive (default)> select concat(type,'123')
              > from winfunc limit 3;
OK
_c0
ABC123
BCD123
CDE123
Time taken: 0.103 seconds, Fetched: 3 row(s)

6. concat_ws

语法: concat_ws(string SEP, string A, string B…)

返回值: string

说明：返回输入字符串连接后的结果， SEP 表示各个字符串间的分隔符

hive (default)> select concat_ws('_',type,'123')
              > from winfunc limit 3;
OK
_c0
ABC_123
BCD_123
CDE_123
Time taken: 0.99 seconds, Fetched: 3 row(s)

此外，concat_ws也可以处理数组，例如：

hive (default)> select concat_ws('_',split(type,''))
              > from winfunc limit 1;
OK
_c0
_A_B_C_
Time taken: 0.125 seconds, Fetched: 1 row(s)

先将winfunc表中的第一行的type列的值通过split函数来切分成一个数组，然后通过concat_ws来将该数组中的没两个元素之间加上“_”。

7. collect_set

collect_set(x) - Returns a set of objects with duplicate elements eliminated

hive (default)> select collect_set(id)
              > from winfunc;

OK
_c0
[1001,1002,1003,1004]
Time taken: 1.546 seconds, Fetched: 1 row(s)

8. collect_list

collect_list(x) - Returns a list of objects with duplicates

Time taken: 1.546 seconds, Fetched: 1 row(s)
hive (default)> select collect_list(id)
              > from winfunc;

OK
_c0
[1001,1001,1001,1001,1002,1002,1002,1002,1002,1002,1003,1003,1004]
Time taken: 2.894 seconds, Fetched: 1 row(s)

也就是说collect_set会去重，而collect_list不会去重。

关于Hive的练习

相关推荐