Hive Operators and functions

Reference https://cwiki.apache.org/confluence/display/Hive/LanguageManual+UDF# Languagemanualudf-hiveoperatorsanduserdefinedfunctions%28udfs%29

Display Display function information

SHOW FUNCTIONS;

DESCRIBE FUNCTION <function_name>;

DESCRIBE FUNCTION EXTENDED <function_name>;

Built-in operators

Relational operators

A = B returns TRUE or False if NULL returns false

A <=> B If it is similar to =, the decision to support null

A = = B not supported

A! = B or a <> B returns null, TRUE, or false

A < B, a <= B, a > B, a >=b returns NULL, True, or false

A [NOT] between B and C return null, True, or false,true include values of B and C

A is [not] NULL

A [not] like B returns NULL, TRUE or FALSE, "_" wildcard one character, "%" wildcard any character

A rlike B or a REGEXP B regular match

Arithmetic operators

A + B, a A, a * B, A/b

A% B to remainder

A & B bit with

A | B-bit or

A ^ B-bit XOR

~a bit non-

logical operators

A and B or a&&b return null, True, or false,

A or B or a | | b A or b One is true returns TRUE,FALSE or null returns NULL, otherwise returns FALSE

Not A or! A returns NULL, true, or false,

A [NOT] in (Val1, Val2, ...) A whether in (Val1, Val2, ...) In

[NOT] EXISTS (subquery) subquery has results returned, hive0.13 start support

Complex Type construction ( Complex Type Constructors )

Map (key1,value1,key2,value2,....) Create a map that feels like a dictionary in Python

struct (val1, val2, Val3,...) Create a struct with the given data, filed name Col1,col2,col3 ...

Named_struct (Name1,val1,name2,val2,...) Create a struct with the given filed name and data

Array (val1, Val2,val3,...) Create an array with the given data

Create_union (tag, val1, Val2,...) Create a union, point to tag

Complex type operator (Operators on Complex Types)

A[n] A is an array that returns the nth element of an array, with the subscript starting at 0

M[key] M is a map that returns the value corresponding to the map key

S.x S is a struct that returns the value of the struct's filed name X

Built-in functions (built-in Functions)

Mathematical functions

When a parameter has NULL, it returns NULL, (PS.) Here is the pit daddy ... ）

Round (double A), round (double A, INT D) round D decimal, D returns bigint type by default

Floor (DOUBLE a) rounding down, rounding out decimals

Ceil (double A), ceiling (double a) rounding up

RAND (), rand (INT Seed) produces a column of random numbers

Exp (DOUBLE a), exp (DECIMAL A) calculates the A-order of E

ln (DOUBLE a), ln (DECIMAL a) calculates the natural logarithm of a

LOG10 (Double A), log10 (decimal a), OG2 (double A), log2 (decimal a) calculates the logarithm of a, the base is 10 or 2

Log (double base, double a), log (decimal base, decimal a) calculates the logarithm of a, base

Pow (double A, double p), power (double A, double p) calculates the P-side of a

sqrt (DOUBLE a), sqrt (DECIMAL a) calculates the square root of a

Bin (BIGINT a) returns the binary number of a

Hex (BIGINT a) hex (STRING a) hex (binary a) returns the 16 binary of a

Unhex (STRING a) returns the 16 binary inverse

Conv (BIGINT num, int from_base, int to_base), conv (STRING num, int from_base, int to_base) convert num from from_base to To_base

ABS (DOUBLE a) returns absolute value

Pmod (int A, int b), Pmod (double A, double b) Returns the value of a-mode B

Sin (DOUBLE a), sin (DECIMAL a)

ASIN (DOUBLE a), ASIN (DECIMAL a)

cos (DOUBLE a), cos (DECIMAL a)

ACOs (DOUBLE a), ACOs (DECIMAL a)

Tan (DOUBLE a), tan (DECIMAL a)

Atan (DOUBLE a), Atan (DECIMAL a)

Degrees (DOUBLE a), degrees (DECIMAL a) converts a from radians to an angle

Radians (double A), radians (double A) converts a from angle to radians

Positive (INT a), positive (DOUBLE a) returns a

Negative (INT a), negative (DOUBLE a) return-a

Sign (DOUBLE a), signs (DECIMAL a) take a symbol, ' 1.0 ' indicates positive, ' 1.0 ' indicates negative, otherwise ' 0.0 '

E (), Pi ()

Factorial (INT a) returns the factor of a? between [0..20]

CBRT (DOUBLE a) returns cube root

Shiftleft (INT a), Shiftleft (BIGINT a)

Shiftright (INT a), Shiftright (BIGINT a)

Shiftrightunsigned (INT a), shiftrightunsigned (BIGINT a)

Acquisition function (Collection Functions)

Size (map<k.v>) returns the number of elements in a Map

Size (array<t>) returns the number of elements in an Array

Map_keys (map<k.v>) returns an array where the element is the key of the MAP

Map_values (map<k.v>) returns an array whose elements are the values of the MAP

Array_contains (ARRAY<T>, value) whether the Array contains, value

Sort_array (array<t>) sort

Type conversion function (type Conversion Functions)

Binary (string|binary)

Cast (expr as <type>) converts the demerit of expr to type, if the conversion fails returns NULL if the type is Boolean, non-null is the return true

Date function (Day Functions)

Hive Operators and functions