- 自然指数函数: exp
语法: exp(double a) 返回值: double 说明: 返回自然对数e的a次方 hive> select exp(2) ; 7.38905609893065
- 以10为底对数函数: log10
语法: log10(double a) 返回值: double 说明: 返回以10为底的a的对数 hive> select log10(100) ; 2.0
此外还有：以2为底对数函数: log2()、对数函数: log()
- 幂运算函数: pow
语法: pow(double a, double p) 返回值: double 说明: 返回a的p次幂 hive> select pow(2,4) ; 16.0
- 开平方函数: sqrt
语法: sqrt(double a) 返回值: double 说明: 返回a的平方根 hive> select sqrt(16) ; 4.0
- 二进制函数: bin
语法: bin(BIGINT a) 返回值: string 说明: 返回a的二进制代码表示 hive> select bin(7) ; 111
十六进制函数: hex()、将十六进制转化为字符串函数: unhex()
进制转换函数: conv(bigint num, int from_base, int to_base) 说明: 将数值num从from_base进制转化到to_base进制
此外还有很多数学函数： 绝对值函数: abs()、正取余函数: pmod()、正弦函数: sin()、反正弦函数: asin()、余弦函数: cos()、反余弦函数: acos()、positive函数: positive()、negative函数: negative()
条件函数
- If函数: if
语法: if(boolean testCondition, T valueTrue, T valueFalseOrNull) 返回值: T 说明: 当条件testCondition为TRUE时，返回valueTrue；否则返回valueFalseOrNull hive> select if(1=2,100,200) ; 200 hive> select if(1=1,100,200) ; 100
- 非空查找函数: coalesce
语法: coalesce(T v1, T v2, …) 返回值: T 说明: 返回参数中的第一个非空值；如果所有值都为NULL，那么返回NULL hive> select coalesce(null,'100','50') ; 100
- 条件判断函数：case when (两种写法，其一)
语法: case when a then b [when c then d]* [else e] end 返回值: T 说明：如果a为TRUE,则返回b；如果c为TRUE，则返回d；否则返回e hive> select case when 1=2 then 'tom' when 2=2 then 'mary' else 'tim' end from tableName; mary
- 条件判断函数：case when (两种写法，其二)
语法: case a when b then c [when d then e]* [else f] end 返回值: T 说明：如果a等于b，那么返回c；如果a等于d，那么返回e；否则返回f hive> Select case 100 when 50 then 'tom' when 100 then 'mary' else 'tim' end from tableName; mary
日期函数
注：以下SQL语句中的 from tableName 可去掉，不影响查询结果
- 获取当前UNIX时间戳函数: unix_timestamp
语法: unix_timestamp() 返回值: bigint 说明: 获得当前时区的UNIX时间戳 hive> select unix_timestamp() from tableName; 1616906976
b.UNIX时间戳转日期函数: from_unixtime
语法: from_unixtime(bigint unixtime[, string format]) 返回值: string 说明: 转化UNIX时间戳（从1970-01-01 00:00:00 UTC到指定时间的秒数）到当前时区的时间格式 hive> select from_unixtime(1616906976,'yyyyMMdd') from tableName; 20210328
c.日期转UNIX时间戳函数: unix_timestamp
语法: unix_timestamp(string date) 返回值: bigint 说明: 转换格式为"yyyy-MM-dd HH:mm:ss"的日期到UNIX时间戳。如果转化失败，则返回0。 hive> select unix_timestamp('2021-03-08 14:21:15') from tableName; 1615184475
d.指定格式日期转UNIX时间戳函数: unix_timestamp
语法: unix_timestamp(string date, string pattern) 返回值: bigint 说明: 转换pattern格式的日期到UNIX时间戳。如果转化失败，则返回0。 hive> select unix_timestamp('2021-03-08 14:21:15','yyyyMMdd HH:mm:ss') from tableName; 1615184475
e.日期时间转日期函数: to_date
语法: to_date(string timestamp) 返回值: string 说明: 返回日期时间字段中的日期部分。 hive> select to_date('2021-03-28 14:03:01') from tableName; 2021-03-28
f.日期转年函数: year
语法: year(string date) 返回值: int 说明: 返回日期中的年。 hive> select year('2021-03-28 10:03:01') from tableName; 2021 hive> select year('2021-03-28') from tableName; 2021
g.日期转月函数: month
语法: month (string date) 返回值: int 说明: 返回日期中的月份。 hive> select month('2020-12-28 12:03:01') from tableName; 12 hive> select month('2021-03-08') from tableName; 8
h.日期转天函数: day
语法: day (string date) 返回值: int 说明: 返回日期中的天。 hive> select day('2020-12-08 10:03:01') from tableName; 8 hive> select day('2020-12-24') from tableName; 24
i.日期转小时函数: hour
语法: hour (string date) 返回值: int 说明: 返回日期中的小时。 hive> select hour('2020-12-08 10:03:01') from tableName; 10
j.日期转分钟函数: minute
语法: minute (string date) 返回值: int 说明: 返回日期中的分钟。 hive> select minute('2020-12-08 10:03:01') from tableName; 3
k.日期转秒函数: second
语法: second (string date) 返回值: int 说明: 返回日期中的秒。 hive> select second('2020-12-08 10:03:01') from tableName; 1
l.日期转周函数: weekofyear
语法: weekofyear (string date) 返回值: int 说明: 返回日期在当前的周数。 hive> select weekofyear('2020-12-08 10:03:01') from tableName; 49
m.日期比较函数: datediff
语法: datediff(string enddate, string startdate) 返回值: int 说明: 返回结束日期减去开始日期的天数。 hive> select datediff('2020-12-08','2012-05-09') from tableName; 213
n.日期增加函数: date_add
语法: date_add(string startdate, int days) 返回值: string 说明: 返回开始日期startdate增加days天后的日期。 hive> select date_add('2020-12-08',10) from tableName; 2020-12-18
o.日期减少函数: date_sub
语法: date_sub (string startdate, int days) 返回值: string 说明: 返回开始日期startdate减少days天后的日期。 hive> select date_sub('2020-12-08',10) from tableName; 2020-11-28
字符串函数
- 字符串长度函数：length
语法: length(string A) 返回值: int 说明：返回字符串A的长度 hive> select length('abcedfg') from tableName; 7
b.字符串反转函数：reverse
语法: reverse(string A) 返回值: string 说明：返回字符串A的反转结果 hive> select reverse('abcedfg') from tableName; gfdecba
c.字符串连接函数：concat
语法: concat(string A, string B…) 返回值: string 说明：返回输入字符串连接后的结果，支持任意个输入字符串 hive> select concat('abc','def’,'gh')from tableName; abcdefgh
d.带分隔符字符串连接函数：concat_ws
语法: concat_ws(string SEP, string A, string B…) 返回值: string 说明：返回输入字符串连接后的结果，SEP表示各个字符串间的分隔符 hive> select concat_ws(',','abc','def','gh')from tableName; abc,def,gh
e.字符串截取函数：substr,substring
语法: substr(string A, int start),substring(string A, int start) 返回值: string 说明：返回字符串A从start位置到结尾的字符串 hive> select substr('abcde',3) from tableName; cde hive> select substring('abcde',3) from tableName; cde hive> select substr('abcde',-1) from tableName; （和ORACLE相同） e
f.字符串截取函数：substr,substring
语法: substr(string A, int start, int len),substring(string A, int start, int len) 返回值: string 说明：返回字符串A从start位置开始，长度为len的字符串 hive> select substr('abcde',3,2) from tableName; cd hive> select substring('abcde',3,2) from tableName; cd hive>select substring('abcde',-2,2) from tableName; de
g.字符串转大写函数：upper,ucase
语法: upper(string A) ucase(string A) 返回值: string 说明：返回字符串A的大写格式 hive> select upper('abSEd') from tableName; ABSED hive> select ucase('abSEd') from tableName; ABSED
h.字符串转小写函数：lower,lcase
语法: lower(string A) lcase(string A) 返回值: string 说明：返回字符串A的小写格式 hive> select lower('abSEd') from tableName; absed hive> select lcase('abSEd') from tableName; absed
i.去空格函数：trim
语法: trim(string A) 返回值: string 说明：去除字符串两边的空格 hive> select trim(' abc ') from tableName; abc
j.左边去空格函数：ltrim
语法: ltrim(string A) 返回值: string 说明：去除字符串左边的空格 hive> select ltrim(' abc ') from tableName; abc
k.右边去空格函数：rtrim
语法: rtrim(string A) 返回值: string 说明：去除字符串右边的空格 hive> select rtrim(' abc ') from tableName; abc
l.正则表达式替换函数：regexp_replace
语法: regexp_replace(string A, string B, string C) 返回值: string 说明：将字符串A中的符合java正则表达式B的部分替换为C。注意，在有些情况下要使用转义字符,类似oracle中的regexp_replace函数。 hive> select regexp_replace('foobar', 'oo|ar', '') from tableName; fb
m.正则表达式解析函数：regexp_extract
语法: regexp_extract(string subject, string pattern, int index) 返回值: string 说明：将字符串subject按照pattern正则表达式的规则拆分，返回index指定的字符。 hive> select regexp_extract('foothebar', 'foo(.*?)(bar)', 1) from tableName; the hive> select regexp_extract('foothebar', 'foo(.*?)(bar)', 2) from tableName; bar hive> select regexp_extract('foothebar', 'foo(.*?)(bar)', 0) from tableName; foothebar strong>注意，在有些情况下要使用转义字符，下面的等号要用双竖线转义，这是java正则表达式的规则。 select data_field, regexp_extract(data_field,'.*?bgStart\\=([^&]+)',1) as aaa, regexp_extract(data_field,'.*?contentLoaded_headStart\\=([^&]+)',1) as bbb, regexp_extract(data_field,'.*?AppLoad2Req\\=([^&]+)',1) as ccc from pt_nginx_loginlog_st where pt = '2021-03-28' limit 2;
n.URL解析函数：parse_url
语法: parse_url(string urlString, string partToExtract [, string keyToExtract]) 返回值: string 说明：返回URL中指定的部分。partToExtract的有效值为：HOST, PATH, QUERY, REF, PROTOCOL, AUTHORITY, FILE, and USERINFO. hive> select parse_url ('https://www.tableName.com/path1/p.php?k1=v1&k2=v2#Ref1', 'HOST') from tableName; www.tableName.com hive> select parse_url ('https://www.tableName.com/path1/p.php?k1=v1&k2=v2#Ref1', 'QUERY', 'k1') from tableName; v1
o.json解析函数：get_json_object
语法: get_json_object(string json_string, string path) 返回值: string 说明：解析json的字符串json_string,返回path指定的内容。如果输入的json字符串无效，那么返回NULL。 hive> select get_json_object('{"store":{"fruit":\[{"weight":8,"type":"apple"},{"weight":9,"type":"pear"}], "bicycle":{"price":19.95,"color":"red"} },"email":"amy@only_for_json_udf_test.net","owner":"amy"}','$.owner') from tableName;
