Hive 函数详解
1.数学函数
| Return Type | Name (Signature) | Description |
|---|---|---|
| DOUBLE | round(DOUBLE a) | 返回对a四舍五入的BIGINT值 |
| DOUBLE | round(DOUBLE a) | 返回对a四舍五入的BIGINT值 |
| DOUBLE | round(DOUBLE a, INT d) | 返回DOUBLE型d的保留n位小数的DOUBLW型的近似值 |
| DOUBLE | bround(DOUBLE a) | 银行家舍入法（14：舍，69：进，5->前位数是偶：舍，5->前位数是奇：进） |
| DOUBLE | bround(DOUBLE a, INT d) | 银行家舍入法,保留d位小数 |
| BIGINT | floor(DOUBLE a) | 向下取整，最数轴上最接近要求的值的左边的值 如：6.10->6 -3.4->-4 |
| BIGINT | ceil(DOUBLE a), ceiling(DOUBLE a) | 求其不小于小给定实数的最小整数如：ceil(6) = ceil(6.1)= ceil(6.9) = 6 |
| DOUBLE | rand(), rand(INT seed) | 每行返回一个DOUBLE型随机数seed是随机因子 |
| DOUBLE | exp(DOUBLE a), exp(DECIMAL a) | 返回e的a幂次方， a可为小数 |
| DOUBLE | ln(DOUBLE a), ln(DECIMAL a) | 以自然数为底d的对数，a可为小数 |
| DOUBLE | log10(DOUBLE a), log10(DECIMAL a) | 以10为底d的对数，a可为小数 |
| DOUBLE | log2(DOUBLE a), log2(DECIMAL a) | 以2为底数d的对数，a可为小数 |
| DOUBLE | log(DOUBLE base, DOUBLE a) | 以base为底的对数，base 与 a都是DOUBLE类型 |
| DOUBLE | pow(DOUBLE a, DOUBLE p), power(DOUBLE a, DOUBLE p) | 计算a的p次幂 |
| DOUBLE | sqrt(DOUBLE a), sqrt(DECIMAL a) | 计算a的平方根 |
| STRING | bin(BIGINT a) | 计算二进制a的STRING类型，a为BIGINT类型 |
| STRING | hex(BIGINT a) hex(STRING a) hex(BINARY a) | 计算十六进制a的STRING类型，如果a为STRING类型就转换成字符相对应的十六进制 |
| STRING | conv(BIGINT num, INT from_base, INT to_base), conv(STRING num, INT from_base, INT to_base) | 将GIGINT/STRING类型的num从from_base进制转换成to_base进制 |
| DOUBLE | abs(DOUBLE a) | 计算a的绝对值 |
| INT or DOUBLE | pmod(INT a, INT b), pmod(DOUBLE a, DOUBLE b) | a对b取模 |
| DOUBLE | sin(DOUBLE a), sin(DECIMAL a) | 求a的正弦值 |
| DOUBLE | asin(DOUBLE a), asin(DECIMAL a) | 求d的反正弦值 |
| DOUBLE | cos(DOUBLE a), cos(DECIMAL a) | 求余弦值 |
| DOUBLE | acos(DOUBLE a), acos(DECIMAL a) | 求反余弦值 |
| DOUBLE | tan(DOUBLE a), tan(DECIMAL a) | 求正切值 |
| DOUBLE | atan(DOUBLE a), atan(DECIMAL a) | 求反正切值 |
| DOUBLE | degrees(DOUBLE a), degrees(DECIMAL a) | 奖弧度值转换角度值 |
| DOUBLE | radians(DOUBLE a), radians(DOUBLE a) | 将角度值转换成弧度值 |
| INT or DOUBLE | positive(INT a), positive(DOUBLE a) | 返回a |
| INT or DOUBLE | negative(INT a), negative(DOUBLE a) | 返回a的相反数 |
| DOUBLE or INT | sign(DOUBLE a), sign(DECIMAL a) | 如果a是正数则返回1.0，是负数则返回-1.0，否则返回0.0 |
| DOUBLE | e() | 数学常数e |
| DOUBLE | pi() | 数学常数pi |
| BIGINT | factorial(INT a) | 求a的阶乘 |
| DOUBLE | cbrt(DOUBLE a) | 求a的立方根 |
| INT BIGINT | shiftleft(TINYINT或SMALLINT或INT a, INT b),shiftleft(BIGINT a, INT b) | 按位左移 |
| INT BIGINT | shiftright(TINYINT或SMALLINT或INT a, INTb),shiftright(BIGINT a, INT b) | 按拉右移 |
| INT BIGINT | shiftrightunsigned(TINYINT或SMALLINT或INTa, INT b),shiftrightunsigned(BIGINT a, INT b) | 无符号按位右移（<<<） |
| T | greatest(T v1, T v2, …) | 求最大值 |
| T | least(T v1, T v2, …) | 求最小值 |
2.集合函数
| Return Type | Name(Signature) | Description |
|---|---|---|
| int | size(Map | 求map的长度 |
| int | size(Array) | 求数组的长度 |
| array | map_keys(Map | 返回map中的所有key |
| array | map_values(Map | 返回map中的所有value |
| boolean | array_contains(Array, value) | 如该数组Array包含value返回true。，否则返回false |
| array | sort_array(Array) | 按自然顺序对数组进行排序并返回 |
3.类型转换函数
| Return Type | Name(Signature) | Description |
|---|---|---|
| binary | binary(string或binary) | 将输入的值转换成二进制 |
| type | cast(expr as ) | 将expr转换成type类型 如：cast(“1” as BIGINT) 将字符串1转换成了BIGINT类型，如果转换失败将返回NULL |
4.日期函数
| Return Type | Name(Signature) | Description |
|---|---|---|
| string | from_unixtime(bigint unixtime[, string format]) | 将时间的秒值转换成format格式（format可为“yyyy-MM-dd hh:mm:ss”,“yyyy-MM-dd hh”,“yyyy-MM-dd hh:mm”等等）如from_unixtime(1250111000,“yyyy-MM-dd”) 得到2009-03-12 |
| bigint | unix_timestamp() | 获取本地时区下的时间戳 |
| bigint | unix_timestamp(string date) | 将格式为yyyy-MM-dd HH:mm:ss的时间字符串转换成时间戳 如unix_timestamp(‘2009-03-20 11:30:01’) = 1237573801 |
| bigint | unix_timestamp(string date, string pattern) | 将指定时间字符串格式字符串转换成Unix时间戳，如果格式不对返回0 如：unix_timestamp(‘2009-03-20’, ‘yyyy-MM-dd’) = 1237532400 |
| string | to_date(string timestamp) | 返回时间字符串的日期部分 |
| int | year(string date) | 返回时间字符串的年份部分 |
| int | quarter(date/timestamp/string) | 返回当前时间属性哪个季度 如quarter(‘2015-04-08’) = 2 |
| int | month(string date) | 返回时间字符串的月份部分 |
| int | day(string date) dayofmonth(date) | 返回时间字符串的天 |
| int | hour(string date) | 返回时间字符串的小时 |
| int | minute(string date) | 返回时间字符串的分钟 |
| int | second(string date) | 返回时间字符串的秒 |
| int | weekofyear(string date) | 返回时间字符串位于一年中的第几个周内 如weekofyear(“1970-11-01 00:00:00”) = 44, weekofyear(“1970-11-01”) = 44 |
| int | datediff(string enddate, string startdate) | 计算开始时间startdate到结束时间enddate相差的天数 |
| string | date_add(string startdate, int days) | 从开始时间startdate加上days |
| string | date_sub(string startdate, int days) | 从开始时间startdate减去days |
| timestamp | from_utc_timestamp(timestamp, string timezone) | 如果给定的时间戳并非UTC，则将其转化成指定的时区下时间戳 |
| timestamp | to_utc_timestamp(timestamp, string timezone) | 如果给定的时间戳指定的时区下时间戳，则将其转化成UTC下的时间戳 |
| date | current_date | 返回当前时间日期 |
| timestamp | current_timestamp | 返回当前时间戳 |
| string | add_months(string start_date, int num_months) | 返回当前时间下再增加num_months个月的日期 |
| string | last_day(string date) | 返回这个月的最后一天的日期，忽略时分秒部分（HH:mm:ss） |
| string | next_day(string start_date, string day_of_week) | 返回当前时间的下一个星期X所对应的日期 如：next_day(‘2015-01-14’, ‘TU’) = 2015-01-20 以2015-01-14为开始时间，其下一个星期二所对应的日期为2015-01-20 |
| string | trunc(string date, string format) | 返回时间的最开始年份或月份 如trunc(“2016-06-26”,“MM”)=2016-06-01 trunc(“2016-06-26”,“YY”)=2016-01-01 注意所支持的格式为MONTH/MON/MM, YEAR/YYYY/YY |
| double | months_between(date1, date2) | 返回date1与date2之间相差的月份，如date1>date2，则返回正，如果date1 |
| string | date_format(date/timestamp/string ts, string fmt) | 按指定格式返回时间date 如：date_format(“2016-06-22”,“MM-dd”)=06-22 |
5.条件函数
| Return Type | Name(Signature) | Description |
|---|---|---|
| T | if(boolean testCondition, T valueTrue, T valueFalseOrNull) | 如果testCondition 为true就返回valueTrue,否则返回valueFalseOrNull ，（valueTrue，valueFalseOrNull为泛型） |
| T | nvl(T value, T default_value) | 如果value值为NULL就返回default_value,否则返回value |
| T | COALESCE(T v1, T v2, …) | 返回第一非null的值，如果全部都为NULL就返回NULL 如：COALESCE (NULL,44,55)=44/strong> |
| T | CASE a WHEN b THEN c [WHEN d THEN e]* [ELSE f] END | 如果a=b就返回c,a=d就返回e，否则返回f 如CASE 4 WHEN 5 THEN 5 WHEN 4 THEN 4 ELSE 3 END 将返回4 |
| T | CASE WHEN a THEN b [WHEN c THEN d]* [ELSE e] END | 如果a=ture就返回b,c= ture就返回d，否则返回e 如：CASE WHEN 5>0 THEN 5 WHEN 4>0 THEN 4 ELSE 0 END 将返回5；CASE WHEN 5<0 THEN 5 WHEN 4<0 THEN 4 ELSE 0 END 将返回0 |
| boolean | isnull( a ) | 如果a为null就返回true，否则返回false |
| boolean | isnotnull ( a ) | 如果a为非null就返回true，否则返回false |
6.字符函数
| Return Type | Name(Signature) | Description |
|---|---|---|
| int | ascii(string str) | 返回str中首个ASCII字符串的整数值 |
| string | base64(binary bin) | 将二进制bin转换成64位的字符串 |
| string | concat(string或binary A, string或binary B…) | 对二进制字节码或字符串按次序进行拼接 |
| array | context_ngrams(array | 与ngram类似，但context_ngram()允许你预算指定上下文(数组)来去查找子序列，具体看StatisticsAndDataMining(这里的解释更易懂) |
| string | concat_ws(string SEP, string A, string B…) | 与concat()类似，但使用指定的分隔符喜进行分隔 |
| string | concat_ws(string SEP, array) | 拼接Array中的元素并用指定分隔符进行分隔 |
| string | decode(binary bin, string charset) | 使用指定的字符集charset将二进制值bin解码成字符串，支持的字符集有：‘US-ASCII’, ‘ISO-8859-1’, ‘UTF-8’, ‘UTF-16BE’, ‘UTF-16LE’, ‘UTF-16’，如果任意输入参数为NULL都将返回NULL |
| binary | encode(string src, string charset) | 使用指定的字符集charset将字符串编码成二进制值，支持的字符集有：‘US-ASCII’, ‘ISO-8859-1’, ‘UTF-8’, ‘UTF-16BE’, ‘UTF-16LE’, ‘UTF-16’，如果任一输入参数为NULL都将返回NULL |
| int | find_in_set(string str, string strList) | 返回以逗号分隔的字符串中str出现的位置，如果参数str为逗号或查找失败将返回0，如果任一参数为NULL将返回NULL回 |
| string | format_number(number x, int d) | 将数值X转换成"#,###,###.##"格式字符串，并保留d位小数，如果d为0，将进行四舍五入且不保留小数 |
| string | get_json_object(string json_string, string path) | 从指定路径上的JSON字符串抽取出JSON对象，并返回这个对象的JSON格式，如果输入的JSON是非法的将返回NULL,注意此路径上JSON字符串只能由数字 字母 下划线组成且不能有大写字母和特殊字符，且key不能由数字开头，这是由于Hive对列名的限制 |
| boolean | in_file(string str, string filename) | 如果文件名为filename的文件中有一行数据与字符串str匹配成功就返回true |
| int | instr(string str, string substr) | 查找字符串str中子字符串substr出现的位置，如果查找失败将返回0，如果任一参数为Null将返回null，注意位置为从1开始的 |
| int | length(string A) | 返回字符串的长度 |
| int | locate(string substr, string str[, int pos]) | 查找字符串str中的pos位置后字符串substr第一次出现的位置 |
| string | lower(string A) lcase(string A) | 将字符串A的所有字母转换成小写字母 |
| string | lpad(string str, int len, string pad) | 从左边开始对字符串str使用字符串pad填充，最终len长度为止，如果字符串str本身长度比len大的话，将去掉多余的部分 |
| string | ltrim(string A) | 去掉字符串A前面的空格 |
| array | ngrams(array | 返回出现次数TOP K的的子序列,n表示子序列的长度，具体看StatisticsAndDataMining (这里的解释更易懂) |
| string | parse_url(string urlString, string partToExtract [, string keyToExtract]) | 返回从URL中抽取指定部分的内容，参数url是URL字符串，而参数partToExtract是要抽取的部分，这个参数包含(HOST, PATH, QUERY, REF, PROTOCOL, AUTHORITY, FILE, and USERINFO,例如：parse_url(‘http://facebook.com/path1/p.php?k1=v1&k2=v2#Ref1’, ‘HOST’) =‘facebook.com’，如果参数partToExtract值为QUERY则必须指定第三个参数key 如：parse_url(‘http://facebook.com/path1/p.php?k1=v1&k2=v2#Ref1’, ‘QUERY’, ‘k1’) =‘v1’ |
| string | printf(String format, Obj… args) | 按照printf风格格式输出字符串 |
| string | regexp_extract(string subject, string pattern, int index) | 抽取字符串subject中符合正则表达式pattern的第index个部分的子字符串，注意些预定义字符的使用，如第二个参数如果使用’\s’将被匹配到s,’\s’才是匹配空格 |
| string | regexp_replace(string INITIAL_STRING, string PATTERN, string REPLACEMENT) | 按照Java正则表达式PATTERN将字符串INTIAL_STRING中符合条件的部分成REPLACEMENT所指定的字符串，如里REPLACEMENT这空的话，抽符合正则的部分将被去掉 如：regexp_replace(“foobar”, "oo |
| string | repeat(string str, int n) | 重复输出n次字符串str |
| string | reverse(string A) | 反转字符串 |
| string | rpad(string str, int len, string pad) | 从右边开始对字符串str使用字符串pad填充，最终len长度为止，如果字符串str本身长度比len大的话，将去掉多余的部分 |
| string | rtrim(string A) | 去掉字符串后面出现的空格 |
| array | sentences(string str, string lang, string locale) | 字符串str将被转换成单词数组，如：sentences(‘Hello there! How are you?’) =( (“Hello”, “there”), (“How”, “are”, “you”) ) |
| string | space(int n) | 返回n个空格 |
| array | split(string str, string pat) | 按照正则表达式pat来分割字符串str,并将分割后的数组字符串的形式返回 |
| map | str_to_map(text[, delimiter1, delimiter2]) | 将字符串str按照指定分隔符转换成Map，第一个参数是需要转换字符串，第二个参数是键值对之间的分隔符，默认为逗号;第三个参数是键值之间的分隔符，默认为"=" |
| string | substr(string | binary A, int start) substring(string |
| string | substr(string | binary A, int start, int len) substring(string |
| string | substring_index(string A, string delim, int count) | 截取第count分隔符之前的字符串，如count为正则从左边开始截取，如果为负则从右边开始截取 |
| string | translate(string或char或varchar input, string或char或varchar from, string或char或varchar to) | 将input出现在from中的字符串替换成to中的字符串 如：translate(“MOBIN”,“BIN”,“M”)=“MOM” |
| string | trim(string A) | 将字符串A前后出现的空格去掉 |
| binary | unbase64(string str) | 将64位的字符串转换二进制值 |
| string | upper(string A) ucase(string A) | 将字符串A中的字母转换成大写字母 |
| string | initcap(string A) | 将字符串A转换第一个字母大写其余字母的字符串 |
| int | levenshtein(string A, string B) | 计算两个字符串之间的差异大小 如：levenshtein(‘kitten’, ‘sitting’) = 3 |
| string | soundex(string A) | 将普通字符串转换成soundex字符串 |
7.聚合函数
| Return Type | Name(Signature) | Description |
|---|---|---|
| IGINT | count(*), count(expr), count(DISTINCT expr[, expr…]) | count(*) 统计总行数，包括含有NULL值的行 count(expr)统计提供非NULL的expr表达式值的行数 count(DISTINCT expr[, expr]) 统计提供非NULL且去重后的expr表达式值的行数 |
| DOUBLE | sum(col), sum(DISTINCT col) | sum(col),表示求指定列的和，sum(DISTINCT col)表示求去重后的列的和 |
| DOUBLE | avg(col), avg(DISTINCT col) | avg(col),表示求指定列的平均值，avg(DISTINCT col)表示求去重后的列的平均值 |
| DOUBLE | min(col) | 求指定列的最小值 |
| DOUBLE | max(col) | 求指定列的最大值 |
| DOUBLE | variance(col), var_pop(col) | 求指定列数值的方差 |
| DOUBLE | var_samp(col) | 求指定列数值的样本方差 |
| DOUBLE | stddev_pop(col) | 求指定列数值的标准偏差 |
| DOUBLE | stddev_samp(col) | 求指定列数值的样本标准偏差 |
| DOUBLE | covar_pop(col1, col2) | 求指定列数值的协方差 |
| DOUBLE | covar_samp(col1, col2) | 求指定列数值的样本协方差 |
| DOUBLE | corr(col1, col2) | 返回两列数值的相关系数 |
| DOUBLE | percentile(BIGINT col, p) | 返回col的p%分位数 |
8.表生成函数
| Return Type | Name(Signature) | Description |
|---|---|---|
| Array Type | explode(array a) | 对于a中的每个元素，将生成一行且包含该元素 |
| N rows | explode(ARRAY) | 每行对应数组中的一个元素 |
| N rows | explode(MAP) | 每行对应每个map键-值，其中一个字段是map的键，另一个字段是map的值 |
| N rows | posexplode(ARRAY) | 与explode类似，不同的是还返回各元素在数组中的位置 |
| N rows | stack(INT n, v_1, v_2, …, v_k) | 把M列转换成N行，每行有M/N个字段，其中n必须是个常数 |
| tuple | json_tuple(jsonStr, k1, k2, …) | 从一个JSON字符串中获取多个键并作为一个元组返回，与get_json_object不同的是此函数能一次获取多个键值 |
| tuple | parse_url_tuple(url, p1, p2, …) | 返回从URL中抽取指定N部分的内容，参数url是URL字符串，而参数p1,p2,…是要抽取的部分，这个参数包含HOST, PATH, QUERY, REF, PROTOCOL, AUTHORITY, FILE, USERINFO, QUERY:<KEY> |
| inline(ARRAY | 将结构体数组提取出来并插入到表中 |
UDTF函数的使用
select id,name,type from mv lateral view explode(split(types,",")) table as type
本文来自互联网用户投稿，文章观点仅代表作者本人，不代表本站立场，不承担相关法律责任。如若转载，请注明出处。 如若内容造成侵权/违法违规/事实不符，请点击【内容举报】进行投诉反馈！