Dear Wiki user, You have subscribed to a wiki page or wiki category on "Hadoop Wiki" for change notification.
The following page has been changed by ZhengShao: http://wiki.apache.org/hadoop/Hive/LanguageManual/UDF ------------------------------------------------------------------------------ ||M[key] ||M is a Map<K, V> and key has type K ||returns the value corresponding to the key in the map e.g. if M is a map comprising of {'f' -> 'foo', 'b' -> 'bar', 'all' -> 'foobar'} then M['all'] returns 'foobar'|| ||S.x ||S is a struct ||returns the x field of S e.g for struct foobar {int foo, int bar} foobar.foo returns the integer stored in the foo field of the struct. || - == Built in Functions == + == Built-in Functions == - === Generic Built in Functions === + === Mathematical Functions === - The following are built in functions are supported in hive: + The following built-in mathematical functions are supported in hive: ||<10%>'''Return Type'''||<15%>'''Name(Signature)'''||'''Description|| ||BIGINT ||round(double a) ||returns the rounded BIGINT value of the double|| ||BIGINT ||floor(double a) ||returns the maximum BIGINT value that is equal or less than the double|| ||BIGINT ||ceil(double a) ||returns the minimum BIGINT value that is equal or greater than the double|| ||double ||rand(), rand(int seed) ||returns a random number (that changes from row to row). Specifiying the seed will make sure the generated random number sequence is deterministic.|| + ||double ||exp(double a) ||Return e^a where e is the base of the natural logarithm|| + ||double ||ln(double a) ||Return the natural logarithm of the argument|| + ||double ||log10(double a) ||Return the base-10 logarithm of the argument|| + ||double ||log2(double a) ||Return the base-2 logarithm of the argument|| + ||double ||log(double base, double a) ||Return the base "base" logarithm of the argument|| + ||double ||pow(double a, double p) ||Return a^p|| + ||double ||power(double a, double p) ||Synonym of pow|| + + === Collection Functions === + The following built-in collection functions are supported in hive: + ||int ||size(Map<K.V>) ||returns the number of elements in the map type|| + ||int ||size(Array<T>) ||returns the number of elements in the array type|| + + === Type Conversion Functions === + The following type conversion functions are supported in hive: + || <type> ||cast(expr as <type>) ||converts the results of the expression expr to <type> e.g. cast('1' as BIGINT) will convert the string '1' to it integral representation. A null is returned if the conversion does not succeed.|| + + === Date Functions === + The following built-in date functions are supported in hive: + ||string ||from_unixtime(int unixtime) ||convert 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"|| + ||string ||to_date(string timestamp) ||Return the date part of a timestamp string: to_date("1970-01-01 00:00:00") = "1970-01-01"|| + ||int ||year(string date) ||Return the year part of a date or a timestamp string: year("1970-01-01 00:00:00") = 1970, year("1970-01-01") = 1970|| + ||int ||month(string date) ||Return 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) ||Return the day part of a date or a timestamp string: day("1970-11-01 00:00:00") = 1, day("1970-11-01") = 1 || + ||T ||if(boolean testCondition, T valueTrue, T valueFalseOrNull) ||Return valueTrue when testCondition is true, returns valueFalseOrNull otherwise || + + === String Functions === + The following are built-in String functions are supported in hive: ||string ||concat(string A, string B)||returns the string resulting from concatenating B after A e.g. concat('foo', 'bar') results in 'foobar'|| ||string ||substr(string A, int start) ||returns the substring of A starting from start position till the end of string A e.g. concat('foobar', 3) results in 'bar'|| ||string ||upper(string A)||returns the string resulting from converting all characters of A to upper case e.g. upper('fOoBaR') results in 'FOOBAR'|| @@ -71, +99 @@ ||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(string 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('foobar', 'oo|ar', '') returns 'fb'|| - ||int ||size(Map<K.V>) ||returns the number of elements in the map type|| - ||int ||size(Array<T>) ||returns the number of elements in the array type|| - || <type> ||cast(expr as <type>) ||converts the results of the expression expr to <type> e.g. cast('1' as BIGINT) will convert the string '1' to it integral representation. A null is returned if the conversion does not succeed.|| - ||string ||from_unixtime(int unixtime) ||convert 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"|| - ||string ||to_date(string timestamp) ||Return the date part of a timestamp string: to_date("1970-01-01 00:00:00") = "1970-01-01"|| - ||int ||year(string date) ||Return the year part of a date or a timestamp string: year("1970-01-01 00:00:00") = 1970, year("1970-01-01") = 1970|| - ||int ||month(string date) ||Return 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) ||Return the day part of a date or a timestamp string: day("1970-11-01 00:00:00") = 1, day("1970-11-01") = 1 || - ||T ||if(boolean testCondition, T valueTrue, T valueFalseOrNull) ||Return valueTrue when testCondition is true, returns valueFalseOrNull otherwise || ||string ||get_json_object(string 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|| ==== get_json_object ====
