Hive常用函数

hive函数
一、关系运算：
等值比较: =
等值比较:<=>
不等值比较: <>和!=
小于比较: <
小于等于比较: <=
大于比较: >
大于等于比较: >=
区间比较
空值判断: IS NULL
非空判断: IS NOT NULL 10. LIKE 比较: LIKE
JAVA 的 LIKE 操作:
RLIKE 12. --str rlike regexp - 如果str匹配regexp则返回true，否则返回false
REGEXP 操作: REGEXP --str regexp regexp - 如果str匹配regexp则返回true，否则返回false
二、数学运算：
加法操作: +
减法操作: –
乘法操作: *
除法操作: /
取余操作: %
位与操作: &
位或操作: |
位异或操作: ^
9.位取反操作: ~
三、逻辑运算：
逻辑与操作: AND 、&&
逻辑或操作: OR 、||
逻辑非操作: NOT、!
四、复合类型构造函数
map 结构
struct 结构 --struct（col1，col2，col3，…） - 使用给定的字段值创建结构
named_struct 结构 --named_struct（name1，val1，name2，val2，…） - 使用给定的字段名称和值创建结构
array 结构
create_union --create_union（tag，obj1，obj2，obj3，…） - 使用给定标记的对象创建一个联合
五、复合类型操作符
获取 array 中的元素 array[索引]
获取 map 中的元素 map.key键
获取 struct 中的元素
六、数值计算函数
取整函数: round --round（x [，d]） - 舍入x到d小数位
bround（x [，d]） - 使用HALF_EVEN舍入模式将x舍入到d小数位。银行家的四舍五入。该值四舍五入到最接近的偶数。也称为高斯舍入
指定精度取整函数: round --round（x [，d]） - 舍入x到d小数位
向下取整函数: floor --floor（x） - 查找不大于x的最大整数
向上取整函数: ceil --ceil（x） - 求不小于x的最小整数
向上取整函数: ceiling --ceiling（x） - 找到不小于x的最小整数
取随机数函数: rand --rand（[seed]） - 返回0到1之间的伪随机数
自然指数函数: exp --exp（x） -计算e的x次的值
以 10 为底对数函数: log10 --log10（x） - 返回x的基数为10的对数
以 2 为底对数函数: log2 --log2（x） - 返回x的基数为2的对数
对数函数: log --log（[b]，x） - 返回x与base b的对数
幂运算函数: pow --pow（x1，x2） - 将x1提升到x2的幂的值。 pow(2,3) --8
幂运算函数: power --power（x1，x2） - 将x1提升到x2的幂的值。 power(2,3) --8
开平方函数: sqrt --sqrt（x） - 返回x的平方根
开立方根: cbrt（double） - 返回double值的立方根。
二进制函数: bin --bin（n） - 返回二进制,如果n中的n是BIGINT。如果n为NULL，则返回NULL
十六进制函数: hex --hex（n，bin或str） - 将参数转换为十六进制,如果参数是字符串，则为字符串中的每个字符返回两个十六进制数字。如果参数是数字或二进制，则返回十六进制表示
反转十六进制函数: unhex --unhex（str） - 将十六进制参数转换为二进制,执行HEX（str）的反向操作。也就是说，它将参数中的每对十六进制数字解释为数字，
并将其转换为数字的字节表示形式。该结果字符返回一个二进制字符串
进制转换函数: conv --conv（num，from_base，to_base） - 将num从from_base转换为to_base,如果to_base为负数，则将num视为有符号整数，否则将其视为无符号整数
绝对值函数: abs --abs(x) 返回x的绝对值
degrees（x） - 将弧度转换为度数
正切函数: atan（x） - 返回x的atan（arctan）（x是弧度）
tan（x） - 返回x的正切（x是弧度）
正取余函数: pmod --a pmod b - 计算正模数
正弦函数: sin --sin（x） - 返回x的正弦值（x以弧度表示）
反正弦函数: asin --asin（x） - 如果-1 <= x <= 1则返回x的反正弦，否则返回NULL
余弦函数: cos --cos（x） - 返回x的余弦值（x是弧度）
反余弦函数: acos --acos（x） 如果-1 <= x <= 1则返回x的反余弦值，否则返回NULL
positive 函数: positive --positive(a) - 返回a
negative 函数: negative --negative(a) - 返回-a
least（v1，v2，…） - 返回值列表中的最小值 --least(2,4,3)
ln（x） - 返回x的自然对数
a mod b - 当除以b时返回余数
pi（） - 返回pi 3.14159 …
shiftleft（a，b） - 按位左移,为tinyint，smallint和int a返回int。返回bigint的bigint a
shiftright（a，b） - 按位右移,为tinyint，smallint和int a返回int。返回bigint的bigint a。
七、集合操作函数
map 类型大小：size --size（map）
array 类型大小：size --size(array)
判断元素数组是否包含元素：array_contains —array_contains（array，value） - 如果数组包含值，则返回TRUE
获取 map 中所有 value 集合 --map_values（map） - 返回包含输入映射值的无序数组。
获取 map 中所有 key 集合 --map_keys（map） - 返回包含输入映射键的无序数组
数组排序 sort_array（array（obj1，obj2，…）） - 根据数组元素的自然顺序按升序对输入数组进行排序
sort_array_by（array（obj1，obj2，…），‘f1’，‘f2’，…，[‘ASC’，‘DESC’]） - 按用户指定的顺序（ASC，DESC）对输入元组数组进行排序by desired field [s] name如果用户未提及排序顺序，则dafault排序顺序为升序
八、类型转换判断函数
二进制转换：binary --base64（bin） - 将参数从二进制转换为base 64字符串
基础类型之间强制转换：cast --cast(原类型 as 新类型)
判断条件是否为真: assert_true（condition） - 如果’condition’不为真，则抛出异常
nvl（value，default_value） - 如果value为null则返回默认值，否则返回value
九、日期函数
UNIX 时间戳转日期函数: from_unixtime --from_unixtime（unix_time时间戳，format） - 以指定的格式返回unix_time
获取当前 UNIX 时间戳函数: --unix_timestamp()
日期转 UNIX 时间戳函数: unix_timestamp --unix_timestamp（date [，pattern]） - 将时间转换为数字将指定时间转换为自1970-01-01以来的秒数。不推荐使用unix_timestamp（void）重载，使用current_timestamp
指定格式日期转 UNIX 时间戳函数: unix_timestamp
from_utc_timestamp（timestamp，string timezone） - 假设给定的时间戳是UTC并转换为给定的时区（从Hive 0.8.0开始）
转换为字符串: date_format（date / timestamp / string，fmt） - 以日期格式fmt指定的格式将日期/时间戳/字符串转换为字符串值
floor_day（param） - 返回一天的时间戳粒度,param需要是一个时间戳值
floor_hour（param） - 返回一小时的时间戳粒度,param需要是一个时间戳值
floor_minute（param） - 返回一分钟时间戳粒度,param需要是一个时间戳值
floor_month（param） - 返回一个月的时间戳粒度,param需要是一个时间戳值
floor_quarter（param） - 返回四分之一粒度,param的时间戳需要是一个时间戳值
floor_second（param） - 返回第二个粒度,参数的时间戳需要是时间戳值
floor_week（param） - 返回一周的时间戳粒度,param需要是一个时间戳值
floor_year（param） - 返回一年中的时间戳粒度,param需要是一个时间戳值
日期时间转日期函数: to_date --to_date（expr） - 提取日期或日期时间表达式expr的日期部分
日期转年函数: year --year（param） - 返回日期/时间戳/间隔的年份组件
日期转月函数: month --month（param） - 返回日期/时间戳/间隔的月份组件
日期转天函数: day --day（param） - 返回日期/时间戳的月份日期或间隔的日期组件
–dayofmonth（param） - 返回日期/时间戳的月份日期或间隔的日期组成部分
星期函数: dayofweek（param） - 返回日期/时间戳的星期几（1 =星期日，2 =星期一，…，7 =星期六）
日期转小时函数: hour --hour（param） - 返回字符串/ timestamp / interval的小时组件
日期转分钟函数: minute --minute（param） - 返回字符串/ timestamp / interval的分钟组件
日期转秒函数: second --second（date） - 返回字符串/ timestamp / interval的第二个组成部分
日期转周函数: weekofyear --weekofyear（date） - 返回给定日期的一年中的一周。一周被认为是在星期一开始，第一周是第一周，> 3天
日期比较函数: datediff() --datediff（date1，date2） - 返回date1和date2之间的天数,date1和date2是格式为’yyyy-MM-dd HH：mm：ss’或’yyyy-MM-dd’的字符串。时间部分被忽略。如果date1早于date2，则结果为负
日期增加函数: date_add --date_add（start_date，num_days） - 返回start_date之后的num_days日期。start_date是格式为’yyyy-MM-dd HH：mm：ss’或’yyyy-MM-dd’的字符串。num_days是一个数字。start_date的时间部分被忽略
日期减少函数: date_sub --date_sub（start_date，num_days） - 返回start_date之前的num_days日期。start_date是格式为’yyyy-MM-dd HH：mm：ss’或’yyyy-MM-dd’的字符串。num_days是一个数字。start_date的时间部分被忽略
月份增加函数: add_months（start_date，num_months） - 返回start_date之后的num_months日期。start_date是格式为’yyyy-MM-dd HH：mm：ss’或’yyyy-MM-dd’的字符串。num_months是一个数字。start_date的时间部分被忽略
current_date（） - 返回查询评估开始时的当前日期。同一查询中current_date的所有调用都返回相同的值。
current_timestamp（） - 返回查询评估开始时的当前时间戳。同一查询中的current_timestamp的所有调用都返回相同的值
last_day（date） - 返回日期所属月份的最后一天。
months_between（date1，date2，roundOff） - 返回date1和date2之间的月数。
如果date1晚于date2，则结果为正。如果date1早于date2，则结果为负数。如果date1和date2是该月的同一天或两个月的最后几天，则结果始终为整数。否则，UDF将根据31天的月份计算结果的小数部分，并考虑时间组件date1和date2的差异。
date1和date2类型可以是日期，时间戳或字符串，格式为’yyyy-MM-dd’或’yyyy-MM-dd HH：mm：ss’。结果默认舍入为8位小数。否则设置roundOff = false
next_day（start_date，day_of_week） - 返回晚于start_date并按指示命名的第一个日期。start_date是格式为’yyyy-MM-dd HH：mm：ss’或’yyyy-MM-dd’的字符串。day_of_week是星期几（例如，Mo，星期五，星期五）
quarter（date / timestamp / string） - 返回日期的季度，范围为1到4
to_unix_timestamp（date [，pattern]） - 返回UNIX时间戳,将指定时间转换为自1970-01-01以来的秒数
to_utc_timestamp（timestamp，string timezone） - 假设给定时间戳在给定时区内并转换为UTC（从Hive 0.8.0开始）
十、条件函数
If 函数: if --IF（expr1，expr2，expr3） - 如果expr1为TRUE（expr1 <> 0且expr1 <> NULL）则IF（）返回expr2; 否则返回expr3。IF（）返回数值或字符串值，具体取决于使用它的上下文
非空查找函数: COALESCE(字段,字段…) 返回第一个非空的值
条件判断函数：CASE —case [when … then …] else … end
十一、字符串函数
字符 ascii 码函数：ascii --ascii（str） - 返回str的第一个字符的数值,如果str为空则返回0;如果str为NULL，则返回NULL
base64 --base64（bin） - 将参数从二进制转换为base 64字符串
字符串连接函数：concat --concat（str1，str2，… strN） - 返回str1，str2，… strN或concat（bin1，bin2，… binN）的串联 - 返回二进制数据bin1，bin2，…中的字节串联。 … binN如果任何参数为NULL，则返回NULL
带分隔符字符串连接函数：concat_ws --concat_ws（separator，string1,string2…） - 返回由分隔符separator分隔的字符串的串联
数组转换成字符串的函数：concat_ws --concat_ws（separator，array（string）） - 返回由分隔符separator分隔的字符串的串联
示例:SELECT concat_ws（’.’，‘www’，array（‘facebook’，‘com’））FROM src LIMIT 1; —‘www.facebook.com’
小数位格式化成字符串函数：format_number --format_number（X，D或F） - 将数字X格式化为’＃，###，###。##‘等格式，舍入到D小数位，或者使用指定格式F格式化，并返回结果作为字符串。如果D为0，则结果没有小数点或小数部分。这应该像MySQL的FORMAT
字符处理函数: chr（str） - 将n中的n：[0,256]转换为ascii等价物作为varchar。如果n小于0则返回空字符串。如果n> 256，则返回chr（n％256）
字符串/二进制函数: crc32（str或bin） - 计算字符串或二进制参数的循环冗余校验值并返回bigint值
字符串截取函数：substr,substring --substr（str，pos [，len]） - 返回str的子字符串，该字符串以pos开头并且长度为len orsubstr（bin，pos [，len]） - 返回以pos开头且长度为的字节数组的片段len
字符串截取函数：substr,substring --substring（str，pos [，len]） - 返回str的子字符串，该字符串以pos开头并且长度为len orsubstr（bin，pos [，len]） - 返回以pos开头且长度为的字节数组的片段len
substr（‘Facebook’，5）–book,substr（‘Facebook’， - 5）–ebook,substr（‘Facebook’，5,1）–b
字符串查找函数：instr --instr（str，substr） - 返回str中第一次出现substr的索引
字符串长度函数：length --length（str | binary） - 返回str的长度或二进制数据中的字节数
判断字符串/二进制长度函数: char_length（str | binary）/character_length（str | binary） - 返回str或二进制数据中的字符数
字符串查找函数：locate --locate（substr，str [，pos]） - 返回位置pos后str中第一次出现substr的位置
字符串格式化函数：prdoublef
字符串转换成 map 函数：str_to_map --str_to_map（text，delimiter1，delimiter2） - 通过解析文本
使用两个分隔符将文本拆分为键值对来创建映射。第一个分隔符分隔对，第二个分隔符分配键和值。如果只给出一个参数，则使用默认分隔符：’，‘作为delimiter1，’：'作为delimiter2
base64 解码函数：unbase64(string str) --unbase64（str） - 将参数从base 64字符串转换为binary
字符串转大写函数：upper,ucase --ucase（str）/upper(str) - 返回str，所有字符都更改为大写
字符串转小写函数：lower,lcase --lcase（str） - 返回str，所有字符都改为小写 --lower（str） - 返回str，所有字符都改为小写
去空格函数：trim --trim（str） - 从str中删除前导和尾随空格字符
左边去空格函数：ltrim --ltrim（str） - 从str中删除前导空格字符
右边去空格函数：rtrim --rtrim（str） - 从str中删除尾随空格字符
正则表达式替换函数：regexp_replace --regexp_replace（str，regexp，rep） - 将与regexp匹配的str的所有子串替换为rep
正则表达式解析函数：regexp_extract --regexp_extract（str，regexp [，idx]） - 提取与regexp匹配的组
URL 解析函数：parse_url --parse_url（url，partToExtract [，key]） - 从URL中提取部件：HOST，PATH，QUERY，REF，PROTOCOL，AUTHORITY，FILE，USERINFO 键指定要提取的查询
json 解析函数：get_json_object --get_json_object（json_txt，path） - 从路径中提取json对象从基于指定的json路径的json字符串中提取json对象，并返回提取的json对象的json字符串。如果输入json字符串无效，它将返回null
json_tuple（jsonStr，p1，p2，…，pn） - 与get_json_object类似，但它需要多个名称并返回一个元组。所有输入参数和输出列类型都是字符串
空格字符串函数：space --space（n） - 返回n个空格
重复字符串函数：repeat --repeat（str，n） - 重复str n次
左补足函数：lpad --lpad（str，len，pad） - 返回str，左边用pad填充长度为len,如果str长于len，则返回值缩短为len个字符。如果是空填充字符串，则返回值为null
右补足函数：rpad rpad（str，len，pad） - 返回str，右边填充pad，长度为len,如果str长于len，则返回值缩短为len个字符。如果是空填充字符串，则返回值为null
分割字符串函数: split --split（str，regex） - Splits str匹配正则表达式的匹配
get_splits（string，int） - 返回引用的表字符串的长度为int serialized的数组。
集合查找函数: find_in_set --find_in_set（str，str_array） - 返回str_array中str的第一个匹配项，其中str_array是逗号分隔的字符串。如果任一参数为null，则返回null。如果第一个参数有逗号，则返回0
分词函数：sentences --sentences（str，lang，country） - 将str拆分成句子数组，其中每个句子都是一个单词数组。‘lang’和’country’参数是可选的，如果省略，则使用默认语言环境
分词后统计一起出现频次最高的 TOP-K
分词后统计与指定单词一起出现频次最高的 TOP-K
test in（val1，val2 …） - 如果test等于任何valN，则返回true
in_file（str，filename） - 如果str出现在文件中，则返回true
index（a，n） - 返回a的第n个元素
initcap（str） - 返回str，每个单词的第一个字母用大写字母表示，所有其他字母用小写字母表示。单词由空格分隔
octet_length（str | binary） - 返回str或二进制数据中的字节数
replace（str，search，rep） - 将str中与’search’匹配的所有字符串替换为’rep’
substring_index（str，delim，count） - 在分隔符delim的计数出现之前，从字符串str返回子字符串。
如果count为正数，则返回最终分隔符左侧的所有内容（从左侧开始计算）。 --substring_index(‘www.apache.org’,‘a’,2) --www.ap
如果count为负数，则返回最终分隔符右侧的所有内容（从右侧开始计算）。 --substring_index(‘www.apache.org’,‘a’,-2) --pache.org
在搜索delim时，Substring_index执行区分大小写的匹配
uuid（） - 返回通用唯一标识符（UUID）字符串。
translate（input，from，to） - 通过将from字符串中的字符替换为to字符串中的相应字符来转换输入字符串
translate(‘abcdefg’,‘adc’,‘1’) --1befg
translate(‘abcdefg’,‘adc’,‘198’) --1b89efg
十二、混合函数
调用 Java 函数：java_method --java_method（class，method [，arg1 [，arg2 …]]）使用反射调用方法,使用此UDF通过匹配参数签名来调用Java方法
调用 Java 函数：reflect --reflect（class，method [，arg1 [，arg2 …]]）使用反射调用方法,使用此UDF通过匹配参数签名来调用Java方法
字符串的 hash 值：hash --hash（a1，a2，…） - 返回参数的哈希值
十三、XPath 解析 XML 函数
xpath --xpath（xml，xpath） - 返回xml节点中与xpath表达式匹配的值的字符串数组
xpath_string --xpath_string（xml，xpath） - 返回与xpath表达式匹配的第一个xml节点的文本内容
xpath_boolean --xpath_boolean（xml，xpath） - 计算布尔xpath表达式
xpath_short --xpath_short（xml，xpath） - 返回与xpath表达式匹配的short值
xpath_double --xpath_double（xml，xpath） - 返回与xpath表达式匹配的double值
xpath_long --xpath_long（xml，xpath） - 返回与xpath表达式匹配的long值
xpath_float --xpath_float（xml，xpath） - 返回与xpath表达式匹配的浮点值
xpath_number --xpath_number（xml，xpath） - 返回与xpath表达式匹配的double值
xpath_int（xml，xpath） - 返回与xpath表达式匹配的整数值
十四、汇总统计函数（UDAF）
个数统计函数: count --count（*）/count(1) - 返回检索到的行的总数，包括包含NULL值的行。
count（expr） - 返回提供的表达式为非NULL的行数
count（DISTINCT expr [，expr …]） - 返回提供的表达式唯一且非NULL的行数。
总和统计函数: sum --sum（x） - 返回一组数字的总和
平均值统计函数: avg --avg（x） - 返回一组数字的平均值
最小值统计函数: min --min（expr） - 返回expr的最小值
最大值统计函数: max --max（expr） - 返回expr的最大值
非空集合总体变量函数: var_pop --var_pop（x） - 返回一组数字的方差
非空集合样本变量函数: var_samp --var_samp（x） - 返回一组数字的样本方差
总体标准偏离函数: stddev_pop --stddev_pop（x） - 返回一组数字的标准偏差.别名：std，stddev
样本标准偏离函数: stddev_samp --stddev_samp（x） - 返回一组数字的样本标准差
中位数函数: percentile --percentile（expr，pc） - 返回pc上expr的百分位数（范围：[0,1]）。pc可以是double或double数组
近似中位数函数: percentile_approx – percentile_approx（expr，pc，[nb]） - 对于非常大的数据，使用可选参数[nb]作为要使用的直方图区间数，从直方图计算近似百分位数值。较高的nb值导致更准确的近似，代价是更高的内存使用量。
'expr’可以是任何数字列，包括双精度和浮点数，‘pc’可以是具有请求百分位数的单个double / float，也可以是具有多个百分位数的double / float数组。如果未指定’nb’，则默认近似值使用10,000个直方图区间，这意味着如果’expr’中有10,000个或更少的唯一值，则可以获得精确的结果。百分位数（）函数总是计算精确百分位数，如果列中有太多唯一值，则可能会耗尽内存，这就需要此功能
直方图: histogram_numeric --histogram_numeric（expr，nb） - 使用nb bin计算数字’expr’的直方图。
集合去重数：collect_set --collect_set（x） - 返回一组消除了重复元素的对象
集合不去重函数：collect_list --collect_list（x） - 返回具有重复项的对象列表
compute_stats（x） - 返回一组基本类型值的统计摘要。
md5（str或bin） - 为字符串或二进制文件计算MD5 128位校验和。该值以32个十六进制数字的字符串形式返回，如果参数为NULL，则返回NULL
十五、表格生成函数 Table-Generating Functions (UDTF)
1．数组拆分成多行：explode(array) --explode（a） - 将数组a的元素分成多行，或将map的元素分成多个行和列
2．Map 拆分成多行：explode(map) --explode（a） - 将数组a的元素分成多行，或将map的元素分成多个行和列