常用函数
查询具体函数
desc function 方法名;
常用日期函数
unix_timestamp:返回当前或指定时间的时间戳
hive (default)> select unix_timestamp();
unix_timestamp(void) is deprecated. Use current_timestamp instead.
unix_timestamp(void) is deprecated. Use current_timestamp instead.
OK
_c0
1628144062
Time taken: 0.192 seconds, Fetched: 1 row(s)
from_unixtime：将时间戳转为日期格式
hive (default)> select from_unixtime(1628143897,"yyyy-mm-dd");
OK
_c0
2021-11-05
Time taken: 0.156 seconds, Fetched: 1 row(s)
current_date：当前日期（可以去括号）
hive (default)> select current_date();
OK
_c0
2021-08-05
Time taken: 0.143 seconds, Fetched: 1 row(s)
current_timestamp：当前的日期加时间
hive (default)> select current_timestamp;
OK
_c0
2021-08-05 14:21:29.1
Time taken: 0.157 seconds, Fetched: 1 row(s)
hive (default)> select current_timestamp();
OK
_c0
2021-08-05 14:21:31.947
Time taken: 0.151 seconds, Fetched: 1 row(s)
to_date：抽取日期部分
hive (default)> select to_date(current_timestamp);
OK
_c0
2021-08-05
Time taken: 0.135 seconds, Fetched: 1 row(s)
year：获取年
hive (default)> select year(current_timestamp);
OK
_c0
2021
Time taken: 0.247 seconds, Fetched: 1 row(s)
month：获取月
day：获取日
hour：获取时
minute：获取分
second：获取秒
weekofyear：当前时间是一年中的第几周
hive (default)> select weekofyear(current_timestamp);
OK
_c0
31
Time taken: 0.178 seconds, Fetched: 1 row(s)
dayofmonth：当前时间是一个月中的第几天
hive (default)> select dayofmonth(current_timestamp);
OK
_c0
5
Time taken: 0.147 seconds, Fetched: 1 row(s)
months_between： 两个日期间的月份
hive (default)> select months_between(current_timestamp,current_timestamp);
OK
_c0
0.0
Time taken: 0.152 seconds, Fetched: 1 row(s)
hive (default)> select current_timestamp;
OK
_c0
2021-08-05 14:28:32.471
Time taken: 0.117 seconds, Fetched: 1 row(s)
hive (default)> select months_between(current_timestamp,"2021-01-01");
OK
_c0
7.14849276
Time taken: 0.155 seconds, Fetched: 1 row(s)
hive (default)> select months_between(current_timestamp,"2021-01-21");
OK
_c0
6.50333744
Time taken: 0.138 seconds, Fetched: 1 row(s)
hive (default)>
add_months：日期加减月
hive (default)> select add_months(current_timestamp,5);
OK
_c0
2022-01-05
Time taken: 0.116 seconds, Fetched: 1 row(s)
datediff：两个日期相差的天数
hive (default)> select datediff(current_timestamp,"2021-08-01");
OK
_c0
4
Time taken: 0.153 seconds, Fetched: 1 row(s)
date_add：日期加天数
hive (default)> select date_add(current_timestamp,5);
OK
_c0
2021-08-10
Time taken: 0.128 seconds, Fetched: 1 row(s)
date_sub：日期减天数
last_day：日期的当月的最后一天
hive (default)> select last_day("2001-01");
OK
_c0
NULL
Time taken: 0.133 seconds, Fetched: 1 row(s)
hive (default)> select last_day("2001-01-05");
OK
_c0
2001-01-31
Time taken: 0.11 seconds, Fetched: 1 row(s)
date_format(): 格式化日期
hive (default)> select date_format(current_timestamp,"MM-yyyy-dd");
OK
_c0
08-2021-05
Time taken: 0.128 seconds, Fetched: 1 row(s)
常用取整函数
round： 四舍五入
hive (default)> select round(3.4);
OK
_c0
3
Time taken: 0.12 seconds, Fetched: 1 row(s)
ceil： 向上取整
floor： 向下取整
常用字符串操作函数
upper： 转大写
hive (default)> select upper("aac");
OK
_c0
AAC
Time taken: 0.15 seconds, Fetched: 1 row(s)
lower： 转小写
length： 长度
hive (default)> select length("aac");
OK
_c0
3
Time taken: 0.094 seconds, Fetched: 1 row(s)
trim： 前后去空格
hive (default)> select trim(" aac ");
OK
_c0
aac
Time taken: 0.105 seconds, Fetched: 1 row(s)
lpad： 向左补齐，到指定长度
hive (default)> select lpad(" aac ",10,"*");
OK
_c0
*** aac
Time taken: 0.125 seconds, Fetched: 1 row(s)
rpad： 向右补齐，到指定长度
hive (default)> select rpad(" aac ",10,"*");
OK
_c0
aac ***
Time taken: 0.114 seconds, Fetched: 1 row(s)
regexp_replace：使用正则表达式匹配目标字符串，匹配成功后替换！
hive (default)> select regexp_replace("a//bds/c","/","*");
OK
_c0
a**bds*c
Time taken: 0.106 seconds, Fetched: 1 row(s)
hive (default)> select regexp_replace("aiibds/c","^i","*");
OK
_c0
aiibds/c
Time taken: 0.125 seconds, Fetched: 1 row(s)
集合操作
size： 集合中元素的个数
hive (default)> select * from test;
OK
test.name test.friends test.children test.address
songsong ["bingbing","lili"] {"xiao song":18,"xiaoxiao song":19} {"street":"hui long guan","city":"beijing"}
yangyang ["caicai","susu"] {"xiao yang":18,"xiaoxiao yang":19} {"street":"chao yang","city":"beijing"}
Time taken: 0.218 seconds, Fetched: 2 row(s)
hive (default)> select size(friends) from test;
OK
_c0
2
2
Time taken: 0.115 seconds, Fetched: 2 row(s)
map_keys： 返回map中的key
hive (default)> select map_keys(children) from test;
OK
_c0
["xiao song","xiaoxiao song"]
["xiao yang","xiaoxiao yang"]
Time taken: 0.107 seconds, Fetched: 2 row(s)
map_values: 返回map中的value
hive (default)> select map_values(children) from test;
OK
_c0
[18,19]
[18,19]
Time taken: 0.206 seconds, Fetched: 2 row(s)
array_contains: 判断array中是否包含某个元素
hive (default)> select array_contains(friends,"lili") from test;
OK
_c0
true
false
Time taken: 0.12 seconds, Fetched: 2 row(s)
sort_array： 将array中的元素排序
hive (default)> select sort_array(friends) from test;
OK
_c0
["bingbing","lili"]
["caicai","susu"]
Time taken: 0.089 seconds, Fetched: 2 row(s)
grouping sets:多维分析
hive (default)> select * from business;
OK
business.name business.orderdate business.cost
jack 2017-01-01 10
tony 2017-01-02 15
jack 2017-02-03 23
tony 2017-01-04 29
jack 2017-01-05 46
jack 2017-04-06 42
tony 2017-01-07 50
jack 2017-01-08 55
mart 2017-04-08 62
mart 2017-04-09 68
neil 2017-05-10 12
mart 2017-04-11 75
neil 2017-06-12 80
mart 2017-04-13 94
hive (default)> select name,orderdate,cost from business group by name,orderdate,cost grouping sets((name,orderdate),(name),(name,orderdate,cost));
Total MapReduce CPU Time Spent: 5 seconds 20 msec
OK
name orderdate cost
jack NULL NULL
jack 2017-01-01 NULL
jack 2017-01-01 10
jack 2017-01-05 NULL
jack 2017-01-05 46
jack 2017-01-08 NULL
jack 2017-01-08 55
jack 2017-02-03 NULL
jack 2017-02-03 23
jack 2017-04-06 NULL
jack 2017-04-06 42
mart NULL NULL
mart 2017-04-08 NULL
mart 2017-04-08 62
mart 2017-04-09 NULL
mart 2017-04-09 68
mart 2017-04-11 NULL
mart 2017-04-11 75
mart 2017-04-13 NULL
mart 2017-04-13 94
neil NULL NULL
neil 2017-05-10 NULL
neil 2017-05-10 12
neil 2017-06-12 NULL
neil 2017-06-12 80
tony NULL NULL
tony 2017-01-02 NULL
tony 2017-01-02 15
tony 2017-01-04 NULL
tony 2017-01-04 29
tony 2017-01-07 NULL
tony 2017-01-07 50
|