You are viewing a plain text version of this content. The canonical link for it is here.
Posted to common-commits@hadoop.apache.org by Apache Wiki <wi...@apache.org> on 2009/01/23 19:29:13 UTC
[Hadoop Wiki] Update of "Hive/LanguageManual/UDF" by hliu
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":"amy@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||