Hive内置函數大全

目錄

                                    Hive内置函數大全

1.複合類型構造函數

2 複合類型操作符

3 數值計算函數

4 日期函數

5 條件函數

6 字元串函數

7 彙總統計函數（UDAF）

8 表格生成函數(UDTF) 

9 類型轉換函數

10 數學函數

11 數學運算

12 邏輯運算

13 排序函數

14 混合函數

15 XPath解析XML函數

1.複合類型構造函數

函數	描述	舉例
map	map(key0, value0, key1, value1…)——使用給定的鍵/值對建立映射
struct	struct(col1, col2, col3，…)——用給定的字段值建立一個struct。STRUCT可以包含不同資料類型的元素。這些元素可以通過”點文法”的方式來得到所需要的元素
named_struct	named_struct(name1, val1, name2, val2，…)——用給定的字段名和值建立一個結構體，使用給定的表達式，構造一個指定列名的 struct 資料結構
array	array(n0, n1…)——用給定的元素建立一個數組
create_union	create_union (tag, val1, val2, …)使用給定的 tag 和表達式，構造一個 uniontype 資料結構。tag 表示使用第 tag 個 表達式作為 uniontype 的 value

2 複合類型操作符

函數	描述	舉例
Map[key];size(Map<k .V>)	傳回 map 結構 M 中 key 對應的 value,沒有對應的 key 傳回 NULL;傳回map類型的長度
S.x	傳回 struct 結構 S 中名為 x 的元素。
A[n];size(Array)	傳回數組 A 中第 n 個索引的元素值,數組的起始下标為0;傳回array類型的長度
array_contains(array, value)	如果數組包含值，則傳回TRUE。

3 數值計算函數

函數	描述	舉例
round(x[,d])	四舍五入(x[， d])——将x四舍五入到d的小數位
floor(x)	求不大于x的最大整數，向下取整函數
ceil(x)，ceiling(x)	求不小于x的最小整數，向上取整函數
rand([seed])	傳回0到1之間的僞随機數
exp(x)	傳回e的x次方
log10(x)	傳回以10為底的x的對數
log2(x)	傳回以2為底的x的對數
log([b], x)	傳回以b為底的x的對數
pow(x1, x2),power(x1, x2)	取x1的x2次方
sqrt(x)	開平方函數,傳回√x
bin(n)	傳回n的二進制
hex(n, bin, or str)	将參數轉換為十六進制， 如果參數是字元串，則為字元串中的每個字元傳回兩個十六進制數字。 如果參數是數字或二進制，則傳回十六進制表示
unhex(str)	将十六進制參數轉換為二進制
conv(num, from_base, to_base)	将num從from_base轉換為to_base，如果to_base是負數，則将num視為帶符号整數，否則将其視為無符号整數。
abs(x)	傳回x的絕對值
pmod（a,b）	a對b取模
sin(x)	傳回sinx (x以弧度為機關)
asin(x)	傳回arcsin (x)如果-1<=x<=1，否則傳回NULL
cos(x)	傳回cosx (x以弧度為機關)
acos(x)	傳回arccos (x)如果-1<=x<=1，否則傳回NULL
positive(x)	傳回x
negative(x)	傳回-x

4 日期函數

函數	描述	舉例
from_unixtime(unix_time, format)	UNIX時間戳轉日期函數，傳回指定格式的unix_time
unix_timestamp(date[, pattern])	将時間轉換為數字，擷取目前（指定）UNIX時間戳函數
current_timestamp	擷取目前UNIX時間戳函數
to_date(expr)	提取date或datetime表達式expr的date部分，日期時間轉日期函數
year(param)	傳回日期/時間戳/間隔的year
month(param)	傳回日期/時間戳/間隔的month
day(param)	傳回日期/時間戳/間隔的day
hour(param)	傳回日期/時間戳/間隔的hour
minute(param)	傳回日期/時間戳/間隔的minute
second(param)	傳回日期/時間戳/間隔的second
weekofyear(date)	傳回給定日期的年份的星期。一周被認為從周一開始，第一周是>第3天的第一周。
datediff(date1, date2)	傳回date1和date2之間的天數
date_add(start_date, num_days)	日期增加函數,傳回start_date之後的num_days日期。
date_sub	日期減少函數，傳回start_date之前的num_days日期。

5 條件函數

函數	描述	舉例
IF(expr1,expr2,expr3)	如果expr1為真(expr1 <> 0和expr1 <> NULL)，則If()傳回expr2;否則傳回expr3。IF()傳回一個數值或字元串值，這取決于使用它的上下文。
CASE	① CASE           WHEN a THEN b           [WHEN cTHEN d]...            [ELSE e] END 這種case，case後面沒有表達式，用于處理單個列的查詢結果 ②CASE a          WHEN b THEN c          [WHENd THEN e]...          [ELSE f] END 這種CASE A和if差不多，如果a等于b，那麼傳回c；如果a等于d，那麼傳回e；否則傳回f
COALESCE(T v1, T v2, …)	傳回參數中的第一個非空值；如果所有值都為NULL，那麼傳回NULL

6 字元串函數

函數	描述	舉例
ascii(str)	傳回str的第一個字元的數值
base64(bin)	将參數從二進制轉換為以64為基數的字元串
concat(str1, str2, ... strN	傳回str1、str2、…strN或concat(bin1, bin2，…傳回二進制資料bin1, bin2，…binN 如果任何參數為空，則傳回NULL。
concat_ws(separator, [string | array(string)]+)	傳回由分隔符分隔的字元串的連接配接。
format_number(X, D or F)	将數字X格式化為'#，###，###。，四舍五入到D位小數，或者使用指定的F格式進行格式化，并将結果作為字元串傳回。如果D為0，則結果沒有小數點或小數部分。這應該像MySQL的格式一樣工作
substr(str, pos[, len]),substring(str, pos[, len])	傳回從pos開始的長度為len的位元組數組片段
instr(str, substr)	傳回str中第一次出現substr的索引
length(str | binary)	傳回二進制資料中的str長度或位元組數
locate(substr, str[, pos])	傳回位置pos之後str中第一次出現substr的位置
printf(String format, Obj... args)	該函數可以根據printf樣式的格式字元串格式化字元串
str_to_map(text, delimiter1, delimiter2)	使用兩個分隔符将文本分割為鍵值對。第一個分隔符分隔對，第二個分隔符分隔鍵和值。如果隻給出一個參數，則使用預設分隔符:'，'作為delimiter1，':'作為delimiter2。
unbase64(str)	将參數從基本的64字元串轉換為二進制
upper(str),ucase(str)	傳回所有字元都更改為大寫的str
lower(str),lcase(str)	傳回所有字元都更改為小寫的str
trim(str)	從str中删除前導和尾随空格字元
ltrim(str)	從str中删除前導空格字元
rtrim(str)	從str中删除尾随空格字元
regexp_replace(str, regexp, rep)	用rep替換所有比對regexp的str子字元串
regexp_extract(str, regexp[, idx])	提取比對regexp的組
parse_url(url, partToExtract[, key])	從URL中提取一部分
get_json_object(json_txt, path)	根據指定的json路徑從json字元串中提取json對象，并傳回提取的json對象的json字元串。如果輸入的json字元串無效，則傳回null。
space(n)	傳回n個空格
repeat(str, n)	重複str n次
lpad(str, len, pad)	傳回str，用pad左填充到len的長度
rpad(str, len, pad)	傳回str，右填充pad到len的長度
split(str, regex)	拆分比對正規表達式的str
find_in_set(str,str_array)	傳回str_array中第一個出現的str，其中str_array是一個逗号分隔的字元串。如果其中一個參數為空，則傳回null。如果第一個參數有逗号，則傳回0。
sentences(str, lang, country)	将str拆分為句子數組，其中每個句子都是單詞數組。“lang”和“country”參數是可選的，如果省略，則使用預設

7 彙總統計函數（UDAF）

函數	描述
①count(*)②count(expr)③count(DISTINCT expr[, expr...])	個數統計：①傳回檢索到的行總數，包括包含空值的行②傳回提供的表達式為非空的行數③傳回所提供的表達式惟一且非空的行數。
sum(x)	總和統計，傳回一組數字的和
avg(x)	平均值統計
min(x)	最小值統計
max(x)	最大值統計
var_pop(x)	非空集合總體變量函數
var_samp(x)	非空集合樣本變量函數
stddev_pop(x)	總體标準偏離函數
stddev_samp	樣本标準偏離函數
percentile(expr, pc)	中位數函數,傳回expr在pc上的百分位數(範圍:[0,1])。pc可以是雙數組或雙數組
percentile_approx(expr, pc, [nb])	近似中位數函數
histogram_numeric(expr, nb)	直方圖
collect_set(x)	集合去重數,傳回一組消除了重複元素的對象
collect_list(x)	集合不去重函數,傳回具有重複項的對象清單

8 表格生成函數(UDTF) 

函數	描述
explode(a)	将數組a的元素分隔為多行，或将映射的元素分隔為多行和多列

9 類型轉換函數

函數	描述
binary(a)	将a轉換為二進制
cast	基礎類型之間強制轉換

10 數學函數

函數	描述
=	等值比較
<=>	等值比較
<>和!=	不等值比較
<	小于比較
<=	小于等于比較
>=	大于比較
IS NULL	空值判斷
IS NOT NULL	非空判斷
LIKE	LIKE比較
RLIKE	JAVA的LIKE操作
REGEXP	REGEXP操作（如果str比對REGEXP，則傳回true，否則傳回false）

11 數學運算

函數	描述
+	加法操作
–	減法操作
*	乘法操作
/	除法操作
%	取餘操作
&	位與操作
|	位或操作
^	位異或操作
~	位取反操作

12 邏輯運算

函數	描述
AND 、&&	邏輯與操作
OR 、||	邏輯或操作
NOT、!	邏輯非操作

13 排序函數

rank()	相同的排序是一樣的，但是下一個小的會跳着排序，比如 等級 排序
dense_rank()	相同的排序相同，下一個小的會緊挨着排序，這樣總個數是相對減少的，适合求某些名額前幾個等級的個數。
row_number()	順序排序，總個數是不變的，适合求某些值的前幾名

14 混合函數

函數	描述
java_method(class,method[,arg1[,arg2..]])	使用反射調用方法
reflect(class,method[,arg1[,arg2..]])	使用反射調用方法
hash(a1, a2, ...)	傳回參數的哈希值

15 XPath解析XML函數

函數	描述
xpath(xml, xpath)	傳回比對xpath表達式的xml節點中的值的字元串數組
xpath_string(xml, xpath)	傳回比對xpath表達式的第一個xml節點的文本内容
xpath_boolean(xml, xpath)	計算布爾xpath表達式
xpath_short(xml, xpath) ，xpath_int, xpath_long，xpath_float, xpath_double, xpath_number	傳回與xpath表達式比對的short...