hadoop-common-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Apache Wiki <wikidi...@apache.org>
Subject [Hadoop Wiki] Update of "Hive/HiveQL/UDF" by hliu
Date Thu, 22 Jan 2009 01:08:56 GMT
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/HiveQL/UDF

------------------------------------------------------------------------------
  [[TableOfContents]]
  
  == Built in operators ==
+ === Relational 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 ==!||
@@ -14, +15 @@

  ||A >= B||all primitive types||TRUE if expression A is greater than or equal to expression
B otherwise FALSE||
  ||A IS NULL||all types||TRUE if expression A evaluates to NULL otherwise FALSE||
  ||A IS NOT NULL||all types||TRUE if expression A evaluates to NULL otherwise FALSE||
- ||A LIKE B||strings||TRUE if string A matches the SQL simple regular expression B, otherwise
FALSE. The comparison is done character by character. The _ character in B matches any character
in A(similar to . in posix regular expressions) while the % character in B matches an arbitrary
number of characters in A(similar to .* in posix regular expressions) e.g. 'foobar' like 'foo'
evaluates to FALSE where as 'foobar' like 'foo___' evaluates to TRUE and so does 'foobar'
like 'foo%'||
+ ||A LIKE B||strings||TRUE if string A matches the SQL simple regular expression B, otherwise
FALSE. The comparison is done character by character. The _ character in B matches any character
in A(similar to . in posix regular expressions) while the % character in B matches an arbitrary
number of characters in A(similar to .* in posix regular expressions) e.g. 'foobar' like 'foo'
evaluates to FALSE where as 'foobar' like 'foo_ _ _' evaluates to TRUE and so does 'foobar'
like 'foo%'||
  ||A RLIKE B||strings||TRUE if string A matches the Java regular expression B(See Java regular
expressions syntax), otherwise FALSE e.g. 'foobar' rlike 'foo' evaluates to FALSE where as
'foobar' rlike '^f.*r$' evaluates to TRUE||
  ||A REGEXP B||strings||Same as RLIKE|| 
  
+ 
+ === Arithmetic Operators ===
- 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.||
@@ -30, +33 @@

  ||A ^ B ||all number types ||Gives the result of bitwise XOR of 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.||
  ||~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 ===
- 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||
@@ -39, +43 @@

  ||NOT A ||boolean ||TRUE if A is FALSE, otherwise FALSE||
  ||! A ||boolean ||Same as NOT A ||
  
+ === Operators on Complex Types ===
- Operators on Complex Types - The following operators provide mechanisms to access elements
in  Complex Types
+ The following operators provide mechanisms to access elements in  Complex Types
  ||<10%>'''Operator'''||<10%>'''Operand types'''||'''Description'''||
  ||A[n] ||A is an Array and n is an int ||returns the nth element in the array A. The first
element has index 0 e.g. if A is an array comprising of ['foo', 'bar'] then A[0] returns 'foo'
and A[1] returns 'bar'||
  ||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. ||
  
+ === Generic built in Functions ===
- The following built in functions are supported in hive: 
+ The following are built in functions are supported in hive: 
  ||<10%>'''Return Type'''||<10%>'''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||
@@ -63, +69 @@

  ||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.||
+ || <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 ||
  
+ 
+ === Built in Aggregate (GROUP BY) Functions ===
- The following 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||

Mime
View raw message