1、查看内置（预定义）函数
show functions；
2、查看函数如何使用
hive> desc function upper;
upper(str) - Returns str with all characters changed to uppercase
Time taken: 0.063 seconds, Fetched: 1 row(s)
更详细：
hive> desc function extended upper;
upper(str) - Returns str with all characters changed to uppercase
Synonyms: ucase
Example:
> SELECT upper('Facebook') FROM src LIMIT 1;
'FACEBOOK'
Time taken: 0.009 seconds, Fetched: 5 row(s)
3、测试时间函数
hive> create table dual(x string);
hive> insert into table dual values('');
hive> select unix_timestamp() from dual; ===》时间戳
1528468146
hive> select current_date from dual;====》返回当前天
OK
2018-06-08
hive> select current_timestamp from dual;===》返回当前时间戳
OK
2018-06-08 22:31:53.817
hive> select year("2018-08-08 20:08:08") from dual;===》取年
OK
2018
hive> select cast(current_timestamp as date) from dual;===》用cast 将当前时间戳转化成date
OK
2018-06-08
4、测试字符函数
hive> select concat_ws(".","192","168","199","151") from dual; ===》用.将后面字符连起来
OK
192.168.199.151
hive> select length ("123456789") from dual; ===》取长度
OK
9
hive> select split("192.168.137.130","\\.") from dual; ===》拆分
OK
["192","168","137","130"]
5、HIVE实现word count
hive> create table hive_wc(sentence string); 创建表
hive> load data local inpath '/home/hadoop/data/hive-wc.txt' into table hive_wc; 导入数据
hive> select * from hive_wc ;
hello,world,welcome
hello,welcom
先用split函数将拆分成每隔单词
hive> select split(sentence,",") from hive_wc;
["hello","world","welcome"]
["hello","welcom"]
使用explode函数把每个单词放一行
hive> select explode(split(sentence,",")) from hive_wc;
hello
world
welcome
hello
welcom
最后使用group by 完成word count
hive> select word, count(1) as c from (select explode(split(sentence,",")) as word from hive_wc) t group by word ;
hello 2
welcom 1
welcome 1
world 1
6、josn_tuple 函数
hive> create table rating_json(json string); ===》创建表
hive> load data local inpath '/home/hadoop/data/rating.json' into table rating_json; ===》导入数据
hive> select * from rating_json limit 2; ===》查看数据
{"movie":"1193","rate":"5","time":"978300760","userid":"1"}
{"movie":"661","rate":"3","time":"978302109","userid":"1"}
hive> select json_tuple(json,"movie","rate","time","userid") as (movie,rate,time,userid) from rating_json limit 2; 》使用json_tuple函数取字段值
1193 5 978300760 1
661 3 978302109 1
网友评论