一、查询系统内置函数
1.1 查看系统自带的函数
show functions;
1.2 显示自带的函数的用法
语法
desc function 【函数名称】;
-- 查询upper函数用法
0: jdbc:hive2://hadoop001:10000> desc function upper;
-- 运行结果
+----------------------------------------------------+
| tab_name |
+----------------------------------------------------+
| upper(str) - Returns str with all characters changed to uppercase |
+----------------------------------------------------+
1.3 详细显示自带的函数的用法
语法
desc function extended 【函数名称】;
-- 查询upper的具体用法
0: jdbc:hive2://hadoop001:10000> desc function extended upper;
-- 查询结果
+----------------------------------------------------+
| tab_name |
+----------------------------------------------------+
| upper(str) - Returns str with all characters changed to uppercase |
| Synonyms: ucase |
| Example: |
| > SELECT upper('Facebook') FROM src LIMIT 1; |
| 'FACEBOOK' |
| Function class:org.apache.hadoop.hive.ql.udf.generic.GenericUDFUpper |
| Function type:BUILTIN |
+----------------------------------------------------+
二、常用内置函数
-
字符函数
| 函数名称 | 返回值类型 | 说明 |
|---|---|---|
concat(string A, string B…) | string | 用于拼接字符串,返回输入字符串连接后的结果，支持任意个输入字符串 |
concat_ws(string SEP, string A, string B…) | string | 返回输入字符串连接后的结果，SEP表示各个字符串间的分隔符 |
instr(string str,string substr) | int | 返回子字符串 substr在 str中的位置，若不存在则返回 0 |
length(string str) | int | 返回字符串长度 |
locate(string substr,string str, int pos) | int | 返回位置pos之后str中第一次出现substr的位置 |
lower(string str) /lcase(string str) | string | 将字符串中的所有字母转换成小写字母 |
ucase(string str) /upper(string str) | string | 将字符串中的所有字母转换成大写字母 |
regexp_replace(string x,string y,string z) | string | 替换字符串中的指定字段 第一个参数 x 为字符串 第二个参数 y 为需要替换的字符 第三个参数 z 为替换成的字符 |
split(string str,string x) | array | 以指定的 x 为分隔符切割字符串 |
substr(string A, int start), substring(string A, int start) | string | 返回字符串A从start位置到结尾的字符串 |
substr(string A, int start, int len),substring(string A, int start, int len) | string | 返回字符串A从start位置开始，长度为len的字符串 |
| trim(string str) | string | 去掉字符串前后的空格 |
str_to_map(string text,string delimiter1,string delimiter2) | map | 将字符串text按照指定分隔符转换成Map |
space(int n) | string | 返回长度为n的字符串 |
repeat(string str, int n) | string | 返回重复n次后的str字符串 |
lpad(string str, int len, string pad) | string | 左补足函数,将str用pad进行左补足到len位 |
rpad(string str, int len, string pad) | string | 右补足函数，将str用pad进行右补足到len位 |
find_in_set(string str, string strList) | int | 集合查找函数，返回str在strlist第一次出现的位置，strlist是用逗号分割的字符串。如果没有找该str字符，则返回0 |
regexp_extract(string subject, string pattern, int index) | string | 正则表达式解析函数，将字符串subject按照pattern正则表达式的规则拆分，返回index指定的字符。注意，在有些情况下要使用转义字符 |
parse_url(url, partToExtract[, key]) | string | 解析URL字符串，partToExtract的选项包含[HOST,PATH,QUERY,REF,PROTOCOL,FILE,AUTHORITY,USERINFO] |
| get_json_object(string json_string, string path) | string | 解析json的字符串json_string,返回path指定的内容。如果输入的json字符串无效，那么返回NULL。 |
-
类型转换函数
| 函数名称 | 返回值类型 | 说明 |
|---|---|---|
cast(expr as type) | type类型数据 | 将 expr 转换成 type 类型 |
binary(string str) | binary | 将输入的值转换成二进制 |
- 数学函数
| 函数名称 | 返回类型 | 说明 |
|---|---|---|
round(double a) | BIGINT | 取整函数,返回double类型的整数值部分 （遵循四舍五入） |
| round(double a, int d) | DOUBLE | 返回指定精度d的double类型 |
ceil(double a)/ceiling(double a) | BIGINT | 向上取整函数,返回等于或者大于该double变量的最小的整数 |
floor(double a) | BIGINT | 向下取整函数,返回等于或者小于该double变量的最大的整数 |
rand(), rand(int seed) | double | 取随机数函数,返回一个0到1范围内的随机数。如果指定种子seed，则会等到一个稳定的随机数序列。 |
power(double a,int n) | double | 求指定 a 的 n 次方 |
abs(double a) | double | 求 a 的绝对值 |
-
日期函数
| 函数名称 | 返回类型 | 说明 |
|---|---|---|
from_unixtime(bigint unixtime, string format) | string | 将时间戳转换成format格式，时间格式可选，不选则为默认时间格式 |
unix_timestamp() | bigint | 获得当前时区的UNIX时间戳 |
unix_timestamp(string date) | bigint | 转换格式为"yyyy-MM-dd HH:mm:ss"的日期到UNIX时间戳。如果转化失败，则返回0。 |
unix_timestamp(string date, string pattern) | bigint | 转换pattern格式的日期到UNIX时间戳。如果转化失败，则返回0。 |
to_date(string datetime) | string | 日期时间转日期函数,返回日期时间字段中的日期部分 |
year(string date) | int | 日期转年函数,返回日期中的年 |
month (string date) | int | 日期转月函数,返回date或datetime中的月份 |
day (string date) | int | 日期转天函数,返回日期中的天 |
hour(string date) | int | 日期转小时函数,返回日期中的小时 |
minute (string date) | int | 日期转分钟函数,返回日期中的分钟 |
second(string date) | int | 日期转秒函数,返回时间字符串中的秒数 |
weekofyear(string date) | int | 日期转周函数,返回日期在当前的周数 |
datediff(string enddate, string startdate) | int | 日期比较函数,返回结束日期减去开始日期的天数 |
date_add(string startdate, int days) | string | 日期增加函数,返回开始日期startdate增加days天后的日期 |
date_sub (string startdate, int days) | string | 日期减少函数,返回开始日期startdate减少days天后的日期 |
current_timestamp() | timestamp | 返回当前时间，精确到毫秒 |
current_date() | date | 返回当前时间的日期 |
date_format(date/timestamp/string ts, string fmt) | string | 按指定格式返回时间date |
-
集合函数
| 函数名称 | 返回类型 | 说明 |
|---|---|---|
size(Map<K,V> map) | int | 返回 map 中键值对个数 |
size(Array<T> array) | int | 返回数组的元素数量 |
map_keys(Map<K,V> map) | array<k> | 返回 map 中的所有 key |
map_values(Map<K,V> map) | array<V> | 返回 map 中的所有 values |
array_contains(Array<T> array, value) | boolean | 判断array中是否包含某个元素 |
sort_array(Array<T> array) | array | 对数组进行正序排序 |
-
条件函数
| 函数 | 说明 |
|---|---|
if(x, y, z) | 如果条件 x 为结果 true 就返回 x，否则返回 y |
case when | 类似于 java 中的 switch |
isnull(x) | 如果 x 为 null 就返回 true，否则返回 false |
isnotnull (x) | 如果 x 为非 null 就返回 true，否则返回 false |
nvl(x,y) | 将 x 替换为 y |
coalesce | 返回第一非null的值，如果全部都为 null 就返回 null |
