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 hliu: http://wiki.apache.org/hadoop/Hive/LanguageManual/UDF ------------------------------------------------------------------------------ == Built in Operators == === Relational Operators === - The following operators compare the passed operands and generate a TRUE or FALSE value depending on whether the comparison between the operands holds or not. + The following operators compare the passed operands and generate a TRUE or FALSE value depending on whether the comparison between the operands holds or not. ||<10%>'''Operator'''||<10%>'''Operand types'''||'''Description'''|| || A = B || all primitive types || TRUE if expression A is equal to expression B otherwise FALSE || ||A == B||none!||Fails; SQL uses = and not ==!|| @@ -24, +24 @@ === Arithmetic Operators === - The following operators support various common arithmetic operations on the operands. All of them return number types. + The following operators support various common arithmetic operations on the operands. All of them return number types. ||<10%>'''Operator'''||<10%>'''Operand types'''||'''Description'''|| ||A + B ||all number types ||Gives the result of adding A and B. The type of the result is the same as the common parent(in the type hierarchy) of the types of the operands. e.g. since every integer is a float, therefore float is a containing type of integer so the + operator on a float and an int will result in a float.|| ||A - B ||all number types ||Gives the result of subtracting B from A. The type of the result is the same as the common parent(in the type hierarchy) of the types of the operands.|| @@ -37, +37 @@ ||~A ||all number types ||Gives the result of bitwise NOT of A. The type of the result is the same as the type of A. || === Logical Operators === - The following operators provide support for creating logical expressions. All of them return boolean TRUE or FALSE depending upon the boolean values of the operands. + The following operators provide support for creating logical expressions. All of them return boolean TRUE or FALSE depending upon the boolean values of the operands. ||<10%>'''Operator'''||<10%>'''Operand types'''||'''Description|| ||A AND B ||boolean ||TRUE if both A and B are TRUE, otherwise FALSE|| ||A && B ||boolean ||Same as A AND B|| @@ -55, +55 @@ == Built in Functions == === Generic Built in Functions === - The following are built in functions are supported in hive: + The following are built in functions are supported in hive: - ||<8%>'''Return Type'''||<15%>'''Name(Signature)'''||'''Description|| + ||<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|| @@ -95, +95 @@ * ?() : Filter (script) expression. * [,] : Union operator * [start:end:step] : array slice operator - Example + Example: src_json table is a single column (json), single row table: {{{ +-----------------------------------------------------------------------------------------------------------------------------------------+ json +-----------------------------------------------------------------------------------------------------------------------------------------+ - {"store": - {"fruit":[{"weight":8,"type":"apple"},{"weight":9,"type":"pear"}], + {"store": + {"fruit":[{"weight":8,"type":"apple"},{"weight":9,"type":"pear"}], "book":[{"author":"Nigel Rees","category":"reference","title":"Sayings of the Century","price":8.95}, {"author":"Herman Melville","category":"fiction","title":"Moby Dick","price":8.99,"isbn":"0-553-21311-3"}, {"author":"J. R. R. Tolkien","category":"fiction","title":"The Lord of the Rings","price":22.99,"isbn":"0-395-19395-8"} @@ -114, +114 @@ +-----------------------------------------------------------------------------------------------------------------------------------------+ }}} + You can extract json object using these queries: {{{ - SELECT get_json_object(src_json.json, '$') FROM src_json; - - SELECT get_json_object(src_json.json, '$.owner') FROM src_json; + hive> SELECT get_json_object(src_json.json, '$.owner') FROM src_json; - - SELECT get_json_object(src_json.json, '$.store.book[0]') FROM src_json; - - SELECT get_json_object(src_json.json, '$.non_exist_key') FROM src_json; - }}} - {{{ - {"store":{"fruit":[{"weight":8,"type":"apple"},{"weight":9,"type":"pear"}],"book":[{"author":"Nigel Rees","category":"reference","title":"Sayings of the Century","price":8.95},{"author":"Herman Melville","category":"fiction","title":"Moby Dick","price":8.99,"isbn":"0-553-21311-3"},{"author":"J. R. R. Tolkien","category":"fiction","title":"The Lord of the Rings","price":22.99,"reader":[{"name":"bob","age":25},{"name":"jack","age":26}],"isbn":"0-395-19395-8"}],"basket":[[1,2,{"b":"y","a":"x"}],[3,4],[5,6]],"bicycle":{"price":19.95,"color":"red"}},"email":"a...@only_for_json_udf_test.net","owner":"amy"} - amy + hive> SELECT get_json_object(src_json.json, '$.store.book[0]') FROM src_json; - {"author":"Nigel Rees","category":"reference","title":"Sayings of the Century","price":8.95} + {"author":"Nigel Rees","category":"reference","title":"Sayings of the Century","price":8.95} + hive> SELECT get_json_object(src_json.json, '$.non_exist_key') FROM src_json; NULL + }}} - == Built in Aggregate (GROUP BY) Functions == - The following are built in aggregate functions are supported in hive: + The following are built in aggregate functions are supported in hive: ||<10%>'''Return Type'''||<10%>'''Name(Signature)'''||'''Description|| ||BIGINT ||count(1), count(DISTINCT col [, col]...)||count(1) returns the number of members in the group, whereas the count(DISTINCT col) gets the count of distinct values of the columns in the group|| ||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||
