Hive函数及语法说明

接受任意长度参数

.

string

concat_ws(string

SEP, string A, string

B...)

string

substr(string A, int

start)

substring(string A,

int start)

string

substr(string A, int

start, int len)

substring(string A,

int start, int len)

返回

A

的子串从

start

位置开始直到结尾，

例如

. substr('foobar', 4)

返回

'bar' (

参见

[

/doc/refman/5.0/en/stri

#function_substr

])

返回

A

的子串从

start

位置开始直到

len

长

度

例如

substr('foobar', 4, 1)

返回

'b' (

参见

[

/ doc/refman/5.0/en/stri

#function_substr< /p>

])

如同

concat(),

但间隔连接自定义的分隔符

SEP.

string

upper(string A)

ucase(string A)

Returns the string resulting from converting

all characters of A to upper case e.g.

upper('fOoBaR') results in 'FOOBAR'

string

lower(string A)

lcase(string A)

Returns the string resulting from converting

all characters of B to lower case e.g.

lower('fOoBaR') results in 'foobar'

string

trim(string A)

Returns the string resulting from trimming

spaces from both ends of A e.g. trim(' foobar

') results in 'foobar'

string

ltrim(string A)

Returns the string resulting from trimming

spaces from the beginning(left hand side) of

A e.g. ltrim(' foobar ') results in 'foobar '

string

rtrim(string A)

Returns the string resulting from trimming

spaces from the end(right hand side) of A

e.g. rtrim(' foobar ') results in ' foobar'

string

regexp_replace(strin

g A, string B, string

C)

Returns the string resulting from replacing

all substrings in B that match the Java

regular expression syntax(See Java regular

expressions syntax) with C e.g.

regexp_replace(

'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.

string

regexp_extract(strin

g subject, string

pattern, int index)

Returns the string extracted using the

pattern. e.g. 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/ for

more information on the 'index' or Java

regex group() method.

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. e.g.

parse_url('/pa th1/?

k1=v1&k2=v2#Ref1', 'HOST') returns

''. Also a value of a particular

key in QUERY can be extracted by providing

the key as the third argument, e.g.

parse_url('/path1/?

'v1'.

string

get_json_object(strin

g json_string, string

path)

Extract json object from a json string based

on json path specified, and return 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.

string

string

space(int n)

repeat(string str, int

n)

返回

n

个空格字符串

返回

n

次重复

str

连接的字符串

int

string

ascii(string str)

lpad(string str, int

len, string pad)

返回

st r

的首个字符的

ascii

码对应数字

Returns str, left-padded with pad to a length

of len

Returns str, right-padded with pad to a

length of len

按照

pat

正则拆分

str (pat

是正则表达式

)

string

rpad(string str, int

len, string pad)

array

split(string str, string

pat)

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. e.g. find_in_set('ab',

'abc,b,ab,c,def') returns 3

array

ring>>

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. e.g. sentences('Hello there! How

are you?') returns ( (

array

ring,double>>

ngrams(array

>, 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

[Hive-StatisticsAndDataMining] for more

information.

array

ring,double>>

context_ngrams(arra

< br>y>,

array, int K,

int pf)

Returns the top-k contextual N-grams from a

set of tokenized sentences, given a string of

[Hive- StatisticsAndDataMining] for more

information.

其他函数

Misc. Functions

xpath

The following functions are described in [Hive- LanguageManual-XPathUDF]:

?

xpath, xpath_short, xpath_int, xpath_long, xpath_float, xpath_double, xpath_number,

xpath_string

get_json_object

A limited version of JSONPath is supported:

?

?

?

?

$$ : Root object

. : Child operator

[] : Subscript operator for array

* : Wildcard for []

Syntax not supported that's worth noticing:

?

?

: Zero length string as key

.. : Recursive descent

?

?

?

?

?

@ : Current object/element

() : Script expression

?() : Filter (script) expression.

[,] : Union operator

[start:] : array slice operator

Example: src_json table is a single column (json), single row table:

json

{

:

< /p>

{

:[{

:8,

:

:9,

}],

:{

:19.95,

:

}

},

:

,

:

}

The fields of the json object can be extracted using these queries:

hive>

SELECT

get_json_object(src_,

'$$.owner')

FROM

src_json;

amy

hive>

SELECT

get_json_object(src_,

'$$.[0]')

FROM

src_json;

{

:8,

:

< br>}

hive> SELECT get_json_object(src_, '$$.non_exist_key') FROM

src_json;

NULL

内置聚合函数

Built-in Aggregate Functions (UDAF)

返回类型

bigint

< br>函数名称

(

参数

)

count(*), count(expr),

count(DISTINCT expr[,

expr_.])

描述

count(*)

–

返回总行数，包括空

值

NULL; count(expr) - Returns

the number of rows for which

the supplied expression is

non-NULL; count(DISTINCT

expr[, expr]) - Returns the

number of rows for which the

supplied expression(s) are

unique and non-NULL.

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

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

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

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(col, p)

Returns the exact p^th^

percentile of an integer column

in the group (does not work

with floating point types). p

must be between 0 and 1.

array

percentile(col,

array(p~1,, [, p,,2,,]...))

Returns the exact percentiles

p,,1,,, p,,2,,, ... of an integer

column in the group (does not

work with floating point types).

p,,i~ must be between 0 and 1.

double

percentile_approx(col, p

[, B])

Returns an approximate p^th^

percentile of a numeric column

(including floating point types)

in the group. The B parameter

controls approximation

accuracy at the cost of

memory. Higher values yield

better approximations, and the

default is 10,000. When the

number of distinct values in col

is smaller than B, this gives an

exact percentile value.

array

percentile_approx(col,

array(p~1,, [, p,,2_]...) [,

B])

Same as above, but accepts

and returns an array of

percentile values instead of a

single one.

array

'x','y'

}>

histogram_numeric(col,

b)

Computes a histogram of a

numeric column in the group

using b non-uniformly spaced

bins. The output is an array of

size b of double-valued (x,y)

coordinates that represent the

bin centers and heights

array

collect_set(col)

Returns a set of objects with

duplicate elements eliminated

?

增加

oracle

函数

返回类型

T