Database Reference
In-Depth Information
do this in
Storage Formats
), the following query demonstrates the field accessor operators
for each type:
hive>
SELECT c1[0], c2['b'], c3.c, c4 FROM complex;
1 2 1.0 {1:63}
Operators and Functions
The usual set of SQL operators is provided by Hive: relational operators (such as
x =
'a'
for testing equality,
x IS NULL
for testing nullity, and
x LIKE 'a%'
for pattern
matching), arithmetic operators (such as
x + 1
for addition), and logical operators (such
as
x OR y
for logical
OR
). The operators match those in MySQL, which deviates from
SQL-92 because
||
is logical
OR
, not string concatenation. Use the
concat
function for
the latter in both MySQL and Hive.
Hive comes with a large number of built-in functions — too many to list here — divided
into categories that include mathematical and statistical functions, string functions, date
functions (for operating on string representations of dates), conditional functions, aggreg-
ate functions, and functions for working with XML (using the
xpath
function) and
JSON.
You can retrieve a list of functions from the Hive shell by typing
SHOW
DESCRIBE
command:
hive>
DESCRIBE FUNCTION length;
length(str | binary) - Returns the length of str or number of bytes
in binary
data
In the case when there is no built-in function that does what you want, you can write your
own; see
User-Defined Functions
.
Conversions
Primitive types form a hierarchy that dictates the implicit type conversions Hive will per-
form in function and operator expressions. For example, a
TINYINT
will be converted to
an
INT
if an expression expects an
INT
; however, the reverse conversion will not occur,
and Hive will return an error unless the
CAST
operator is used.
The implicit conversion rules can be summarized as follows. Any numeric type can be im-
plicitly converted to a wider type, or to a text type (
STRING
,
VARCHAR
,
CHAR
). All the
text types can be implicitly converted to another text type. Perhaps surprisingly, they can
