hive內置方法一覽

DOUBLE

round(DOUBLE a)

Returns the rounded BIGINT value of a.

回傳對a四舍五入的BIGINT值

DOUBLE

round(DOUBLE a, INT d)

Returns a rounded to d decimal places.

回傳DOUBLE型d的保留n位小數的DOUBLW型的近似值

BIGINT

floor(DOUBLE a)

Returns the maximum BIGINT value that is equal to or less than a

向下取整，最數軸上最接近要求的值的左邊的值  如：6.10->6   -3.4->-4

BIGINT

ceil(DOUBLE a), ceiling(DOUBLE a)

Returns the minimum BIGINT value that is equal to or greater than a.

求其不小于小給定實數的最小整數如：ceil(6) = ceil(6.1)= ceil(6.9) = 6

DOUBLE

rand(), rand(INT seed)

Returns a random number (that changes from row to row) that is distributed uniformly from 0 to 1. Specifying the seed will make sure the generated random number sequence is deterministic.

每行回傳一個DOUBLE型亂數seed是隨機因子

DOUBLE

exp(DOUBLE a), exp(DECIMAL a)

Returns ea where e is the base of the natural logarithm. Decimal version added in Hive 0.13.0.

回傳e的a冪次方， a可為小數

DOUBLE

ln(DOUBLE a), ln(DECIMAL a)

Returns the natural logarithm of the argument a. Decimal version added in Hive 0.13.0.

以自然數為底d的對數，a可為小數

DOUBLE

log10(DOUBLE a), log10(DECIMAL a)

Returns the base-10 logarithm of the argument a. Decimal version added in Hive 0.13.0.

以10為底d的對數，a可為小數

DOUBLE

log2(DOUBLE a), log2(DECIMAL a)

Returns the base-2 logarithm of the argument a. Decimal version added in Hive 0.13.0.

以2為底數d的對數，a可為小數

DOUBLE

log(DOUBLE base, DOUBLE a)

log(DECIMAL base, DECIMAL a)

Returns the base-base logarithm of the argument a. Decimal versions added in Hive 0.13.0.

以base為底的對數，base 與 a都是DOUBLE型別

DOUBLE

pow(DOUBLE a, DOUBLE p), power(DOUBLE a, DOUBLE p)

Returns ap.

計算a的p次冪

DOUBLE

sqrt(DOUBLE a), sqrt(DECIMAL a)

Returns the square root of a. Decimal version added in Hive 0.13.0.

計算a的平方根

STRING

bin(BIGINT a)

Returns the number in binary format (see http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_bin).

計算二進制a的STRING型別，a為BIGINT型別

STRING

hex(BIGINT a) hex(STRING a) hex(BINARY a)

If the argument is an INT or binary, hex returns the number as a STRING in hexadecimal format. Otherwise if the number is a STRING, it converts each character into its hexadecimal representation and returns the resulting STRING. (Seehttp://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_hex, BINARY version as of Hive 0.12.0.)

計算十六進制a的STRING型別，如果a為STRING型別就轉換成字符相對應的十六進制

BINARY

unhex(STRING a)

Inverse of hex. Interprets each pair of characters as a hexadecimal number and converts to the byte representation of the number. (BINARY version as of Hive 0.12.0, used to return a string.)

hex的逆方法

STRING

conv(BIGINT num, INT from_base, INT to_base), conv(STRING num, INT from_base, INT to_base)

Converts a number from a given base to another (see http://dev.mysql.com/doc/refman/5.0/en/mathematical-functions.html#function_conv).

將GIGINT/STRING型別的num從from_base進制轉換成to_base進制

DOUBLE

abs(DOUBLE a)

Returns the absolute value.

計算a的絕對值

INT or DOUBLE

pmod(INT a, INT b), pmod(DOUBLE a, DOUBLE b)

Returns the positive value of a mod b.

a對b取模

DOUBLE

sin(DOUBLE a), sin(DECIMAL a)

Returns the sine of a (a is in radians). Decimal version added in Hive 0.13.0.

求a的正弦值

DOUBLE

asin(DOUBLE a), asin(DECIMAL a)

Returns the arc sin of a if -1<=a<=1 or NULL otherwise. Decimal version added in Hive 0.13.0.

求d的反正弦值

DOUBLE

cos(DOUBLE a), cos(DECIMAL a)

Returns the cosine of a (a is in radians). Decimal version added in Hive 0.13.0.

求余弦值

DOUBLE

acos(DOUBLE a), acos(DECIMAL a)

Returns the arccosine of a if -1<=a<=1 or NULL otherwise. Decimal version added in Hive 0.13.0.

求反余弦值

DOUBLE

tan(DOUBLE a), tan(DECIMAL a)

Returns the tangent of a (a is in radians). Decimal version added in Hive 0.13.0.

求正切值

DOUBLE

atan(DOUBLE a), atan(DECIMAL a)

Returns the arctangent of a. Decimal version added in Hive 0.13.0.

求反正切值

DOUBLE

degrees(DOUBLE a), degrees(DECIMAL a)

Converts value of a from radians to degrees. Decimal version added in Hive 0.13.0.

獎弧度值轉換角度值

DOUBLE

radians(DOUBLE a), radians(DOUBLE a)

Converts value of a from degrees to radians. Decimal version added in Hive 0.13.0.

將角度值轉換成弧度值

INT or DOUBLE

positive(INT a), positive(DOUBLE a)

Returns a.

回傳a

INT or DOUBLE

negative(INT a), negative(DOUBLE a)

Returns -a.

回傳a的相反數

DOUBLE or INT

sign(DOUBLE a), sign(DECIMAL a)

Returns the sign of a as '1.0' (if a is positive) or '-1.0' (if a is negative), '0.0' otherwise. The decimal version returns INT instead of DOUBLE. Decimal version added in Hive 0.13.0.

如果a是正數則回傳1.0，是負數則回傳-1.0，否則回傳0.0

DOUBLE

e()

Returns the value of e.

數學常數e

DOUBLE

pi()

Returns the value of pi.

數學常數pi

INT BIGINT

shiftleft(TINYINT|SMALLINT|INT a, INT b)

shiftleft(BIGINT a, INT b)

Bitwise left shift (as of Hive 1.2.0). Shifts a b positions to the left.

Returns int for tinyint, smallint and int a. Returns bigint for bigint a.

按位左移

INT

BIGINT

shiftright(TINYINT|SMALLINT|INT a, INTb)

shiftright(BIGINT a, INT b)

Bitwise right shift (as of Hive 1.2.0). Shifts a b positions to the right.

Returns int for tinyint, smallint and int a. Returns bigint for bigint a.

按拉右移

INT

BIGINT

shiftrightunsigned(TINYINT|SMALLINT|INTa, INT b),

shiftrightunsigned(BIGINT a, INT b)

Bitwise unsigned right shift (as of Hive 1.2.0). Shifts a b positions to the right.

Returns int for tinyint, smallint and int a. Returns bigint for bigint a.

無符號按位右移（<<<）

int

size(Map<K.V>)

Returns the number of elements in the map type.

求map的長度

int

size(Array<T>)

Returns the number of elements in the array type.

求陣列的長度

array<K>

map_keys(Map<K.V>)

Returns an unordered array containing the keys of the input map.

回傳map中的所有key

array<V>

map_values(Map<K.V>)

Returns an unordered array containing the values of the input map.

回傳map中的所有value

boolean

array_contains(Array<T>, value)

Returns TRUE if the array contains value.

如該陣列Array<T>包含value回傳true，，否則回傳false

array

sort_array(Array<T>)

Sorts the input array in ascending order according to the natural ordering of the array elements and returns it (as of version 0.9.0).

按自然順序對陣列進行排序并回傳

binary

binary(string|binary)

Casts the parameter into a binary.

將輸入的值轉換成二進制

Expected "=" to follow "type"

cast(expr as <type>)

Converts the results of the expression expr to <type>. For example, cast('1' as BIGINT) will convert the string '1' to its integral representation. A null is returned if the conversion does not succeed. If cast(expr as boolean) Hive returns true for a non-empty string.

將expr轉換成type型別 如：cast("1" as BIGINT) 將字串1轉換成了BIGINT型別，如果轉換失敗將回傳NULL

string

from_unixtime(bigint unixtime[, string format])

Converts the number of seconds from unix epoch (1970-01-01 00:00:00 UTC) to a string representing the timestamp of that moment in the current system time zone in the format of "1970-01-01 00:00:00".

將時間的秒值轉換成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()

Gets current Unix timestamp in seconds.

獲取本地時區下的時間戳

bigint

unix_timestamp(string date)

Converts time string in format yyyy-MM-dd HH:mm:ss to Unix timestamp (in seconds), using the default timezone and the default locale, return 0 if fail: unix_timestamp('2009-03-20 11:30:01') = 1237573801

將格式為yyyy-MM-dd HH:mm:ss的時間字串轉換成時間戳  如unix_timestamp('2009-03-20 11:30:01') = 1237573801

bigint

unix_timestamp(string date, string pattern)

Convert time string with given pattern (see [http://docs.oracle.com/javase/tutorial/i18n/format/simpleDateFormat.html]) to Unix time stamp (in seconds), return 0 if fail: unix_timestamp('2009-03-20', 'yyyy-MM-dd') = 1237532400.

將指定時間字串格式字串轉換成Unix時間戳，如果格式不對回傳0 如：unix_timestamp('2009-03-20', 'yyyy-MM-dd') = 1237532400

string

to_date(string timestamp)

Returns the date part of a timestamp string: to_date("1970-01-01 00:00:00") = "1970-01-01".

回傳時間字串的日期部分

int

year(string date)

Returns the year part of a date or a timestamp string: year("1970-01-01 00:00:00") = 1970, year("1970-01-01") = 1970.

回傳時間字串的年份部分

回傳當前時間屬性哪個季度 如quarter('2015-04-08') = 2

int

month(string date)

Returns the month part of a date or a timestamp string: month("1970-11-01 00:00:00") = 11, month("1970-11-01") = 11.

回傳時間字串的月份部分

int

day(string date) dayofmonth(date)

Returns the day part of a date or a timestamp string: day("1970-11-01 00:00:00") = 1, day("1970-11-01") = 1.

回傳時間字串的天

int

hour(string date)

Returns the hour of the timestamp: hour('2009-07-30 12:58:59') = 12, hour('12:58:59') = 12.

回傳時間字串的小時

int

minute(string date)

Returns the minute of the timestamp.

回傳時間字串的分鐘

int

second(string date)

Returns the second of the timestamp.

回傳時間字串的秒

int

weekofyear(string date)

Returns the week number of a timestamp string: weekofyear("1970-11-01 00:00:00") = 44, weekofyear("1970-11-01") = 44.

回傳時間字串位于一年中的第幾個周內  如weekofyear("1970-11-01 00:00:00") = 44, weekofyear("1970-11-01") = 44

int

datediff(string enddate, string startdate)

Returns the number of days from startdate to enddate: datediff('2009-03-01', '2009-02-27') = 2.

計算開始時間startdate到結束時間enddate相差的天數

string

date_add(string startdate, int days)

Adds a number of days to startdate: date_add('2008-12-31', 1) = '2009-01-01'.

從開始時間startdate加上days

string

date_sub(string startdate, int days)

Subtracts a number of days to startdate: date_sub('2008-12-31', 1) = '2008-12-30'.

從開始時間startdate減去days

timestamp

from_utc_timestamp(timestamp, string timezone)

Assumes given timestamp is UTC and converts to given timezone (as of Hive 0.8.0). For example, from_utc_timestamp('1970-01-01 08:00:00','PST') returns 1970-01-01 00:00:00.

如果給定的時間戳并非UTC，則將其轉化成指定的時區下時間戳

timestamp

to_utc_timestamp(timestamp, string timezone)

Assumes given timestamp is in given timezone and converts to UTC (as of Hive 0.8.0). For example, to_utc_timestamp('1970-01-01 00:00:00','PST') returns 1970-01-01 08:00:00.

如果給定的時間戳指定的時區下時間戳，則將其轉化成UTC下的時間戳

Returns the current date at the start of query evaluation (as of Hive 1.2.0). All calls of current_date within the same query return the same value.

回傳當前時間日期

Returns the current timestamp at the start of query evaluation (as of Hive 1.2.0). All calls of current_timestamp within the same query return the same value.

回傳當前時間戳

Returns the date that is num_months after start_date (as of Hive 1.1.0). start_date is a string, date or timestamp. num_months is an integer. The time part of start_date is ignored. If start_date is the last day of the month or if the resulting month has fewer days than the day component of start_date, then the result is the last day of the resulting month. Otherwise, the result has the same day component as start_date.

回傳當前時間下再增加num_months個月的日期

回傳這個月的最后一天的日期，忽略時分秒部分（HH:mm:ss）

回傳當前時間的下一個星期X所對應的日期 如：next_day('2015-01-14', 'TU') = 2015-01-20  以2015-01-14為開始時間，其下一個星期二所對應的日期為2015-01-20

回傳時間的最開始年份或月份  如trunc("2016-06-26",“MM”)=2016-06-01  trunc("2016-06-26",“YY”)=2016-01-01   注意所支持的格式為MONTH/MON/MM, YEAR/YYYY/YY

回傳date1與date2之間相差的月份，如date1>date2，則回傳正，如果date1<date2,則回傳負，否則回傳0.0  如：months_between('1997-02-28 10:30:00', '1996-10-30') = 3.94959677  1997-02-28 10:30:00與1996-10-30相差3.94959677個月

Converts a date/timestamp/string to a value of string in the format specified by the date format fmt (as of Hive 1.2.0). Supported formats are Java SimpleDateFormat formats –https://docs.oracle.com/javase/7/docs/api/java/text/SimpleDateFormat.html. The second argument fmt should be constant. Example: date_format('2015-04-08', 'y') = '2015'.

date_format can be used to implement other UDFs, e.g.:

• dayname(date) is date_format(date, 'EEEE')
• dayofyear(date) is date_format(date, 'D')

  按指定格式回傳時間date 如：date_format("2016-06-22","MM-dd")=06-22

T

if(boolean testCondition, T valueTrue, T valueFalseOrNull)

Returns valueTrue when testCondition is true, returns valueFalseOrNull otherwise.

如果testCondition 為true就回傳valueTrue,否則回傳valueFalseOrNull ，（valueTrue，valueFalseOrNull為泛型） 

如果value值為NULL就回傳default_value,否則回傳value

T

COALESCE(T v1, T v2, ...)

Returns the first v that is not NULL, or NULL if all v's are NULL.

回傳第一非null的值，如果全部都為NULL就回傳NULL  如：COALESCE (NULL,44,55)=44/strong>

T

CASE a WHEN b THEN c [WHEN d THEN e]* [ELSE f] END

When a = b, returns c; when a = d, returns e; else returns f.

如果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

When a = true, returns b; when c = true, returns d; else returns e.

如果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

如果a為null就回傳true，否則回傳false

如果a為非null就回傳true，否則回傳false

int

ascii(string str)

Returns the numeric value of the first  character of str.

回傳str中首個ASCII字串的整數值

string

base64(binary bin)

Converts the argument from binary to a base 64 string (as of Hive 0.12.0)..

將二進制bin轉換成64位的字串

string

concat(string|binary A, string|binary B...)

Returns the string or bytes resulting from concatenating the strings or bytes passed in as parameters in order. For example, concat('foo', 'bar') results in 'foobar'. Note that this function can take any number of input strings..

對二進制位元組碼或字串按次序進行拼接

array<struct<string,double>>

context_ngrams(array<array<string>>, array<string>, int K, int pf)

Returns the top-k contextual N-grams from a set of tokenized sentences, given a string of "context". See StatisticsAndDataMining for more information..

與ngram類似，但context_ngram()允許你預算指定背景關系(陣列)來去查找子序列，具體看StatisticsAndDataMining(這里的解釋更易懂)

string

concat_ws(string SEP, string A, string B...)

Like concat() above, but with custom separator SEP..

與concat()類似，但使用指定的分隔符喜進行分隔

string

concat_ws(string SEP, array<string>)

Like concat_ws() above, but taking an array of strings. (as of Hive 0.9.0).

拼接Array中的元素并用指定分隔符進行分隔

string

decode(binary bin, string charset)

Decodes the first argument into a String using the provided character set (one of 'US-ASCII', 'ISO-8859-1', 'UTF-8', 'UTF-16BE', 'UTF-16LE', 'UTF-16'). If either argument is null, the result will also be null. (As of Hive 0.12.0.).

使用指定的字符集charset將二進制值bin解碼成字串，支持的字符集有：'US-ASCII', 'ISO-8859-1', 'UTF-8', 'UTF-16BE', 'UTF-16LE', 'UTF-16'，如果任意輸入引數為NULL都將回傳NULL

binary

encode(string src, string charset)

Encodes the first argument into a BINARY using the provided character set (one of 'US-ASCII', 'ISO-8859-1', 'UTF-8', 'UTF-16BE', 'UTF-16LE', 'UTF-16'). If either argument is null, the result will also be null. (As of Hive 0.12.0.).

使用指定的字符集charset將字串編碼成二進制值，支持的字符集有：'US-ASCII', 'ISO-8859-1', 'UTF-8', 'UTF-16BE', 'UTF-16LE', 'UTF-16'，如果任一輸入引數為NULL都將回傳NULL

int

find_in_set(string str, string strList)

Returns the first occurance of str in strList where strList is a comma-delimited string. Returns null if either argument is null. Returns 0 if the first argument contains any commas. For example, find_in_set('ab', 'abc,b,ab,c,def') returns 3..

回傳以逗號分隔的字串中str出現的位置，如果引數str為逗號或查找失敗將回傳0，如果任一引數為NULL將回傳NULL回

string

format_number(number x, int d)

Formats the number X to a format like '#,###,###.##', rounded to D decimal places, and returns the result as a string. If D is 0, the result has no decimal point or fractional part. (As of Hive 0.10.0; bug with float types fixed in Hive 0.14.0, decimal type support added in Hive 0.14.0).

將數值X轉換成"#,###,###.##"格式字串，并保留d位小數，如果d為0，將進行四舍五入且不保留小數

string

get_json_object(string json_string, string path)

Extracts json object from a json string based on json path specified, and returns json string of the extracted json object. It will return null if the input json string is invalid. NOTE: The json path can only have the characters [0-9a-z_], i.e., no upper-case or special characters. Also, the keys *cannot start with numbers.* This is due to restrictions on Hive column names..

從指定路徑上的JSON字串抽取出JSON物件，并回傳這個物件的JSON格式，如果輸入的JSON是非法的將回傳NULL,注意此路徑上JSON字串只能由數字 字母 下劃線組成且不能有大寫字母和特殊字符，且key不能由數字開頭，這是由于Hive對列名的限制

boolean

in_file(string str, string filename)

Returns true if the string str appears as an entire line in filename..

如果檔案名為filename的檔案中有一行資料與字串str匹配成功就回傳true

int

instr(string str, string substr)

Returns the position of the first occurrence of substr in str. Returns null if either of the arguments are null and returns 0 if substr could not be found in str. Be aware that this is not zero based. The first character in str has index 1..

查找字串str中子字串substr出現的位置，如果查找失敗將回傳0，如果任一引數為Null將回傳null，注意位置為從1開始的

int

length(string A)

Returns the length of the string..

回傳字串的長度

int

locate(string substr, string str[, int pos])

Returns the position of the first occurrence of substr in str after position pos..

查找字串str中的pos位置后字串substr第一次出現的位置

string

lower(string A) lcase(string A)

Returns the string resulting from converting all characters of B to lower case. For example, lower('fOoBaR') results in 'foobar'..

將字串A的所有字母轉換成小寫字母

string

lpad(string str, int len, string pad)

Returns str, left-padded with pad to a length of len..

從左邊開始對字串str使用字串pad填充，最終len長度為止，如果字串str本身長度比len大的話，將去掉多余的部分

string

ltrim(string A)

Returns the string resulting from trimming spaces from the beginning(left hand side) of A. For example, ltrim(' foobar ') results in 'foobar '..

去掉字串A前面的空格

array<struct<string,double>>

ngrams(array<array<string>>, int N, int K, int pf)

Returns the top-k N-grams from a set of tokenized sentences, such as those returned by the sentences() UDAF. See StatisticsAndDataMining for more information..

回傳出現次數TOP K的的子序列,n表示子序列的長度，具體看StatisticsAndDataMining (這里的解釋更易懂)

string

parse_url(string urlString, string partToExtract [, string keyToExtract])

Returns the specified part from the URL. Valid values for partToExtract include HOST, PATH, QUERY, REF, PROTOCOL, AUTHORITY, FILE, and USERINFO. For example, parse_url('http://facebook.com/path1/p.php?k1=v1&k2=v2#Ref1', 'HOST') returns 'facebook.com'. Also a value of a particular key in QUERY can be extracted by providing the key as the third argument, for example, parse_url('http://facebook.com/path1/p.php?k1=v1&k2=v2#Ref1', 'QUERY', 'k1') returns 'v1'..

回傳從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)

Returns the input formatted according do printf-style format strings (as of Hive0.9.0)..

按照printf風格格式輸出字串

string

regexp_extract(string subject, string pattern, int index)

Returns the string extracted using the pattern. For example, regexp_extract('foothebar', 'foo(.*?)(bar)', 2) returns 'bar.' Note that some care is necessary in using predefined character classes: using '\s' as the second argument will match the letter s; '\\s' is necessary to match whitespace, etc. The 'index' parameter is the Java regex Matcher group() method index. See docs/api/java/util/regex/Matcher.html for more information on the 'index' or Java regex group() method..

抽取字串subject中符合正則運算式pattern的第index個部分的子字串，注意些預定義字符的使用，如第二個引數如果使用'\s'將被匹配到s,'\\s'才是匹配空格

string

regexp_replace(string INITIAL_STRING, string PATTERN, string REPLACEMENT)

Returns the string resulting from replacing all substrings in INITIAL_STRING that match the java regular expression syntax defined in PATTERN with instances of REPLACEMENT. For example, regexp_replace("foobar", "oo|ar", "") returns 'fb.' Note that some care is necessary in using predefined character classes: using '\s' as the second argument will match the letter s; '\\s' is necessary to match whitespace, etc..

按照Java正則運算式PATTERN將字串INTIAL_STRING中符合條件的部分成REPLACEMENT所指定的字串，如里REPLACEMENT這空的話，抽符合正則的部分將被去掉  如：regexp_replace("foobar", "oo|ar", "") = 'fb.' 注意些預定義字符的使用，如第二個引數如果使用'\s'將被匹配到s,'\\s'才是匹配空格

string

repeat(string str, int n)

Repeats str n times..

重復輸出n次字串str

string

reverse(string A)

Returns the reversed string..

反轉字串

string

rpad(string str, int len, string pad)

Returns str, right-padded with pad to a length of len..

從右邊開始對字串str使用字串pad填充，最終len長度為止，如果字串str本身長度比len大的話，將去掉多余的部分

string

rtrim(string A)

Returns the string resulting from trimming spaces from the end(right hand side) of A. For example, rtrim(' foobar ') results in ' foobar'..

去掉字串后面出現的空格

array<array<string>>

sentences(string str, string lang, string locale)

Tokenizes a string of natural language text into words and sentences, where each sentence is broken at the appropriate sentence boundary and returned as an array of words. The 'lang' and 'locale' are optional arguments. For example, sentences('Hello there! How are you?') returns ( ("Hello", "there"), ("How", "are", "you") )..

字串str將被轉換成單詞陣列，如：sentences('Hello there! How are you?') =( ("Hello", "there"), ("How", "are", "you") )

string

space(int n)

Returns a string of n spaces..

回傳n個空格

array

split(string str, string pat)

Splits str around pat (pat is a regular expression)..

按照正則運算式pat來分割字串str,并將分割后的陣列字串的形式回傳

map<string,string>

str_to_map(text[, delimiter1, delimiter2])

Splits text into key-value pairs using two delimiters. Delimiter1 separates text into K-V pairs, and Delimiter2 splits each K-V pair. Default delimiters are ',' for delimiter1 and '=' for delimiter2..

將字串str按照指定分隔符轉換成Map，第一個引數是需要轉換字串，第二個引數是鍵值對之間的分隔符，默認為逗號;第三個引數是鍵值之間的分隔符，默認為"="

string

substr(string|binary A, int start) substring(string|binary A, int start)

Returns the substring or slice of the byte array of A starting from start position till the end of string A. For example, substr('foobar', 4) results in 'bar' (see [http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_substr])..

對于字串A,從start位置開始截取字串并回傳

string

substr(string|binary A, int start, int len) substring(string|binary A, int start, int len)

Returns the substring or slice of the byte array of A starting from start position with length len. For example, substr('foobar', 4, 1) results in 'b' (see [http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_substr])..

對于二進制/字串A,從start位置開始截取長度為length的字串并回傳

截取第count分隔符之前的字串，如count為正則從左邊開始截取，如果為負則從右邊開始截取

string

translate(string|char|varchar input, string|char|varchar from, string|char|varchar to)

Translates the input string by replacing the characters present in the from string with the corresponding characters in the to string. This is similar to the translatefunction in PostgreSQL. If any of the parameters to this UDF are NULL, the result is NULL as well. (Available as of Hive 0.10.0, for string types)

Char/varchar support added as of Hive 0.14.0..

將input出現在from中的字串替換成to中的字串 如：translate("MOBIN","BIN","M")="MOM"

string

trim(string A)

Returns the string resulting from trimming spaces from both ends of A. For example, trim(' foobar ') results in 'foobar'.

將字串A前后出現的空格去掉

binary

unbase64(string str)

Converts the argument from a base 64 string to BINARY. (As of Hive 0.12.0.).

將64位的字串轉換二進制值

string

upper(string A) ucase(string A)

Returns the string resulting from converting all characters of A to upper case. For example, upper('fOoBaR') results in 'FOOBAR'..

將字串A中的字母轉換成大寫字母

將字串A轉換第一個字母大寫其余字母的字串

計算兩個字串之間的差異大小  如：levenshtein('kitten', 'sitting') = 3

將普通字串轉換成soundex字串

BIGINT

count(*), count(expr), count(DISTINCT expr[, expr...])

count(*) - Returns the total number of retrieved rows, including rows containing NULL values.

統計總行數，包括含有NULL值的行

count(expr) - Returns the number of rows for which the supplied expression is non-NULL.

統計提供非NULL的expr運算式值的行數

count(DISTINCT expr[, expr]) - Returns the number of rows for which the supplied expression(s) are unique and non-NULL. Execution of this can be optimized with hive.optimize.distinct.rewrite.

統計提供非NULL且去重后的expr運算式值的行數

DOUBLE

sum(col), sum(DISTINCT col)

Returns the sum of the elements in the group or the sum of the distinct values of the column in the group.

sum(col),表示求指定列的和，sum(DISTINCT col)表示求去重后的列的和

DOUBLE

avg(col), avg(DISTINCT col)

Returns the average of the elements in the group or the average of the distinct values of the column in the group.

avg(col),表示求指定列的平均值，avg(DISTINCT col)表示求去重后的列的平均值

DOUBLE

min(col)

Returns the minimum of the column in the group.

求指定列的最小值

DOUBLE

max(col)

Returns the maximum value of the column in the group.

求指定列的最大值

DOUBLE

variance(col), var_pop(col)

Returns the variance of a numeric column in the group.

求指定列數值的方差

DOUBLE

var_samp(col)

Returns the unbiased sample variance of a numeric column in the group.

求指定列數值的樣本方差

DOUBLE

stddev_pop(col)

Returns the standard deviation of a numeric column in the group.

求指定列數值的標準偏差

DOUBLE

stddev_samp(col)

Returns the unbiased sample standard deviation of a numeric column in the group.

求指定列數值的樣本標準偏差

DOUBLE

covar_pop(col1, col2)

Returns the population covariance of a pair of numeric columns in the group.

求指定列數值的協方差

DOUBLE

covar_samp(col1, col2)

Returns the sample covariance of a pair of a numeric columns in the group.

求指定列數值的樣本協方差

DOUBLE

corr(col1, col2)

Returns the Pearson coefficient of correlation of a pair of a numeric columns in the group.

回傳兩列數值的相關系數

DOUBLE

 percentile(BIGINT col, p)

Returns the exact p^th percentile of a column in the group (does not work with floating point types). p must be between 0 and 1. NOTE: A true percentile can only be computed for integer values. Use PERCENTILE_APPROX if your input is non-integral.

回傳col的p%分位數

Array Type

explode(array<TYPE> a)

For each element in a, generates a row containing that element.

對于a中的每個元素，將生成一行且包含該元素

N rows

explode(ARRAY)

Returns one row for each element from the array..

每行對應陣列中的一個元素

N rows

explode(MAP)

Returns one row for each key-value pair from the input map with two columns in each row: one for the key and another for the value. (As of Hive 0.8.0.).

每行對應每個map鍵-值，其中一個欄位是map的鍵，另一個欄位是map的值

N rows

posexplode(ARRAY)

Behaves like explode for arrays, but includes the position of items in the original array by returning a tuple of (pos, value). (As of Hive 0.13.0.).

與explode類似，不同的是還回傳各元素在陣列中的位置

N rows

stack(INT n, v_1, v_2, ..., v_k)

Breaks up v_1, ..., v_k into n rows. Each row will have k/n columns. n must be constant..

把M列轉換成N行，每行有M/N個欄位，其中n必須是個常數

tuple

json_tuple(jsonStr, k1, k2, ...)

Takes a set of names (keys) and a JSON string, and returns a tuple of values. This is a more efficient version of the get_json_object UDF because it can get multiple keys with just one call..

從一個JSON字串中獲取多個鍵并作為一個元組回傳，與get_json_object不同的是此函式能一次獲取多個鍵值

tuple

parse_url_tuple(url, p1, p2, ...)

This is similar to the parse_url() UDF but can extract multiple parts at once out of a URL. Valid part names are: HOST, PATH, QUERY, REF, PROTOCOL, AUTHORITY, FILE, USERINFO, QUERY:<KEY>..

回傳從URL中抽取指定N部分的內容，引數url是URL字串，而引數p1,p2,....是要抽取的部分，這個引數包含HOST, PATH, QUERY, REF, PROTOCOL, AUTHORITY, FILE, USERINFO, QUERY:<KEY>

inline(ARRAY<STRUCT[,STRUCT]>)

Explodes an array of structs into a table. (As of Hive 0.10.).

將結構體陣列提取出來并插入到表中