THIS IS A TEST INSTANCE. ALL YOUR CHANGES WILL BE LOST!!!!
DRAFT
Hive User-defined functions (UDFs) are custom functions that are developed in Java and integrated with Apache Hive. UDFs are routines that accept parameters, perform an action, and return the result of the action as a value. The return value can be a single scalar row or a result set, it depends on the UDF's code and implemented interface.
...
Return Type | Name(Signature) | Description | |||||
|---|---|---|---|---|---|---|---|
T | if(boolean testCondition, T valueTrue, T valueFalseOrNull) | Returns valueTrue when testCondition is true, returns valueFalseOrNull otherwise. | |||||
| boolean | isnull( a ) | Returns true if a is NULL and false otherwise. | |||||
| boolean | isnotnull ( a ) | Returns true if a is not NULL and false otherwise. | |||||
| T | nvl(T value, T default_value) | Returns default value if value is null else returns value. | |||||
T | COALESCE(T v1, T v2, ...) | Returns the first v that is not NULL, or NULL if all v's are NULL. | |||||
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. | |||||
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. | |||||
| T | nullif( a, b ) | Returns NULL if a=b; otherwise returns a.
| |||||
| void | assert_true(boolean condition) | Throw an exception if 'condition' is not true, otherwise return null. For example, select assert_true (2<1). |
Data Masking Functions
Misc. Functions
Geospatial Status colour Green title New
| colour | Green |
|---|---|
| title | New |
Built-in Table-Generating Functions (UDTF)
Creating Custom UDF's
The following built-in data masking functions are supported in Hive:
Return Type | Name(Signature) | Description |
|---|---|---|
string | mask(string str[, string upper[, string lower[, string number]]]) | Returns a masked version of str. By default, upper case letters are converted to "X", lower case letters are converted to "x" and numbers are converted to "n". Example: mask("abcd-EFGH-8765-4321") results in xxxx-XXXX-nnnn-nnnn. You can override the characters used in the mask by supplying additional arguments: the second argument controls the mask character for upper case letters, the third argument for lower case letters and the fourth argument for numbers. For example, mask("abcd-EFGH-8765-4321", "U", "l", "#") results in llll-UUUU-####-####. |
| string | mask_first_n(string str[, int n]) | Returns a masked version of str with the first n values masked. Upper case letters are converted to "X", lower case letters are converted to "x" and numbers are converted to "n". For example, mask_first_n("1234-5678-8765-4321", 4) results in nnnn-5678-8765-4321. |
| string | mask_last_n(string str[, int n]) | Returns a masked version of str with the last n values masked. Upper case letters are converted to "X", lower case letters are converted to "x" and numbers are converted to "n". For example, mask_last_n("1234-5678-8765-4321", 4) results in 1234-5678-8765-nnnn. |
| string | mask_show_first_n(string str[, int n]) | Returns a masked version of str, showing the first n characters unmasked. Upper case letters are converted to "X", lower case letters are converted to "x" and numbers are converted to "n". For example, mask_show_first_n("1234-5678-8765-4321", 4) results in 1234-nnnn-nnnn-nnnn. |
| string | mask_show_last_n(string str[, int n]) | Returns a masked version of str, showing the last n characters unmasked. Upper case letters are converted to "X", lower case letters are converted to "x" and numbers are converted to "n". For example, mask_show_last_n("1234-5678-8765-4321", 4) results in nnnn-nnnn-nnnn-4321. |
| string | mask_hash(string|char|varchar str) | Returns a hashed value based on str. The hash is consistent and can be used to join masked values together across tables. This function returns null for non-string types. |
Miscellaneous Functions
In Hive several built-in functions do not belong to any categories above. These are the special functions that Hive has.
Return Type | Name(Signature) | Description |
|---|---|---|
varies | java_method(class, method[, arg1[, arg2..]]) | Synonym for |
varies | reflect(class, method[, arg1[, arg2..]]) | Calls a Java method by matching the argument signature, using reflection. See Reflect (Generic) UDF for examples. |
int | hash(a1[, a2...]) | Returns a hash value of the arguments. |
| string | current_user() | Returns current user name from the configured authenticator manager. Could be the same as the user provided when connecting, but with some authentication managers (for example HadoopDefaultAuthenticator) it could be different. |
| string | logged_in_user() | Returns current user name from the session state. This is the username provided when connecting to Hive. |
| string | current_database() | Returns current database name. |
| string | md5(string/binary) | Calculates an MD5 128-bit checksum for the string or binary. The value is returned as a string of 32 hex digits, or NULL if the argument was NULL. Example: md5('ABC') = '902fbdd2b1df0c4f70b4a5d23525e932'. |
| string | sha1(string/binary) sha(string/binary) | Calculates the SHA-1 digest for string or binary and returns the value as a hex string. Example: sha1('ABC') = '3c01bdbb26f358bab27f267924aa2c9a03fcfdb8'. |
| bigint | crc32(string/binary) | Computes a cyclic redundancy check value for string or binary argument and returns bigint value. Example: crc32('ABC') = 2743272264. |
| string | sha2(string/binary, int) | Calculates the SHA-2 family of hash functions (SHA-224, SHA-256, SHA-384, and SHA-512). The first argument is the string or binary to be hashed. The second argument indicates the desired bit length of the result, which must have a value of 224, 256, 384, 512, or 0 (which is equivalent to 256). SHA-224 is supported starting from Java 8. If either argument is NULL or the hash length is not one of the permitted values, the return value is NULL. Example: sha2('ABC', 256) = 'b5d4045c3f466fa91fe2cc6abe79232a1a57cdf104f7a26e716e0a1e2789df78'. |
| binary | aes_encrypt(input string/binary, key string/binary) | Encrypt input using AES. Key lengths of 128, 192 or 256 bits can be used. 192 and 256 bits keys can be used if Java Cryptography Extension (JCE) Unlimited Strength Jurisdiction Policy Files are installed. If either argument is NULL or the key length is not one of the permitted values, the return value is NULL. Example: base64(aes_encrypt('ABC', '1234567890123456')) = 'y6Ss+zCYObpCbgfWfyNWTw=='. |
| binary | aes_decrypt(input binary, key string/binary) | Decrypt input using AES. Key lengths of 128, 192 or 256 bits can be used. 192 and 256 bits keys can be used if Java Cryptography Extension (JCE) Unlimited Strength Jurisdiction Policy Files are installed. If either argument is NULL or the key length is not one of the permitted values, the return value is NULL. Example: aes_decrypt(unbase64('y6Ss+zCYObpCbgfWfyNWTw=='), '1234567890123456') = 'ABC'. |
| string | version() | Returns the Hive version. The string contains 2 fields, the first being a build number and the second being a build hash. Example: "select version();" might return "2.1.0.2.5.0.0-1245 r027527b9c5ce1a3d7d0b6d2e6de2378fb0c39232". Actual results will depend on your build. |
| bigint | surrogate_key([write_id_bits, task_id_bits]) | Automatically generate numerical Ids for rows as you enter data into a table. Can only be used as default value for acid or insert-only tables. |
Geospatial Status colour Green title New
| colour | Green |
|---|---|
| title | New |
Built-in Table-Generating Functions (UDTF)
Row-set columns types | Name(Signature) | Description |
|---|---|---|
T | explode(ARRAY<T> a) | Explodes an array to multiple rows. Returns a row-set with a single column (col), one row for each element from the array. |
Tkey,Tvalue | explode(MAP<Tkey,Tvalue> m) | Explodes a map to multiple rows. Returns a row-set with a two columns (key,value) , one row for each key-value pair from the input map. |
| int,T | posexplode(ARRAY<T> a) | Explodes an array to multiple rows with additional positional column of int type (position of items in the original array, starting with 0). Returns a row-set with two columns (pos,val), one row for each element from the array. |
T1,...,Tn | inline(ARRAY<STRUCT<f1:T1,...,fn:Tn>> a) | Explodes an array of structs to multiple rows. Returns a row-set with N columns (N = number of top level elements in the struct), one row per struct from the array. |
| T1,...,Tn/r | stack(int r,T1 V1,...,Tn/r Vn) | Breaks up n values V1,...,Vn into r rows. Each row will have n/r columns. r must be constant. |
| string1,...,stringn | json_tuple(string jsonStr,string k1,...,string kn) | Takes JSON string and a set of n keys, and returns a tuple of n values. This is a more efficient version of the get_json_object UDF because it can get multiple keys with just one call. |
string 1,...,stringn | parse_url_tuple(string urlStr,string p1,...,string pn) | Takes URL string and a set of n URL parts, and returns a tuple of n values. 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>. |
Creating Custom UDF's
Apache Hive has rich built-in UDFs, but still, if you need Apache Hive has rich built-in UDFs, but still, if you need something special that the built-in functions did not cover you can write your own. Just a small Java knowledge needs is needed to write a custom UDF.
UDFs can be divided into three types depending on the number of input rows and the number of output rows returned. Each of these UDFs needs to derive (implement) a different interface.
...