...
- arrays:
ARRAY<data_type>
(Note: negative values and non-constant expressions are allowed as of Hive 0.14.) - maps:
MAP<primitive_type, data_type>
(Note: negative values and non-constant expressions are allowed as of Hive 0.14.) - structs:
STRUCT<col_name : data_type [COMMENT col_comment], ...>
- union:
UNIONTYPE<data_type, data_type, ...>
(Note: Only available starting with Hive 0.7.0.)
Column Types
Integral Types (TINYINT
, SMALLINT
, INT/INTEGER
, BIGINT
)
Anchor |
---|
| Integral Types (TINYINT, SMALLINT, INT/INTEGER, BIGINT) |
---|
| Integral Types (TINYINT, SMALLINT, INT/INTEGER, BIGINT) |
---|
|
...
Column Types
...
Integral literals are assumed to be INT
by default, unless the number exceeds the range of INT
in which case it is interpreted as a BIGINT, or if one of the following postfixes is present on the number.
...
Info |
---|
|
INTEGER is introduced as a synonym for INT in Hive 2.2.0 (HIVE-14950).
|
Strings
String literals can be expressed with either single quotes (') or double quotes ("). Hive uses C-style escaping within the strings.
Varchar
Varchar types are created with a length specifier (between 1 and 65355), which defines the maximum number of characters allowed in the character string. If a string value being converted/assigned to a varchar value exceeds the length specifier, the string is silently truncated. Character length is determined by the number of code points contained by the character string.
...
Info |
---|
|
Varchar datatype was introduced in Hive 0.12.0 (HIVE-4844). |
Char
Char types are similar to Varchar but they are fixed-length meaning that values shorter than the specified length value are padded with spaces but trailing spaces are not important during comparisons. The maximum length is fixed at 255.
...
Info |
---|
|
Char datatype was introduced in Hive 0.13.0 (HIVE-5191). |
Timestamps
Supports traditional UNIX timestamp with optional nanosecond precision.
...
Info |
---|
|
Timestamps were introduced in Hive 0.8.0 (HIVE-2272). |
Dates
DATE
values describe a particular year/month/day, in the form YYYY-MM-DD
. For example, DATE '2013-01-01'. Date types do not have a time of day component. The range of values supported for the Date type is 0000-01-01 to 9999-12-31, dependent on support by the primitive Java Date type.
...
Supported Interval Description | Example | Meaning | Since |
---|
Intervals of time units: SECOND / MINUTE / DAY / MONTH / YEAR | INTERVAL '1' DAY | an interval of 1 day(s) | Hive 1.2.0 (HIVE-9792). |
Year to month intervals, format: SY-M S: optional sign (+/-) Y: year count M: month count | INTERVAL '1-2' YEAR TO MONTH | shorthand for: INTERVAL '1' YEAR + INTERVAL '2' MONTH | Hive 1.2.0 (HIVE-9792). |
Day to second intervals, format: SD H:M:S.nnnnnn S: optional sign (+/-) D: day countH: hours M: minutes S: seconds nnnnnn: optional nanotime | INTERVAL '1 2:3:4.000005' DAY | shorthand for: INTERVAL '1' DAY+ INTERVAL '2' HOUR + INTERVAL '3' MINUTE + INTERVAL '4' SECOND + INTERVAL '5' NANO | Hive 1.2.0 (HIVE-9792). |
Support for intervals with constant numbers | INTERVAL 1 DAY | aids query readability / portability | Hive 2.2.0 (HIVE-13557). |
Support for intervals with expressions: this may involve other functions/columns. The expression must return with a number (which is not floating-point) or with a string. | INTERVAL (1+dt) DAY | enables dynamic intervals | Hive 2.2.0 (HIVE-13557). |
Optional usage of interval keyword | 1 DAY '1-2' YEAR TO MONTH (1+dt) SECOND | INTERVAL 1 DAY INTERVAL '1-2' YEARS TO MONTH INTERVAL (1+dt) SECOND | Hive 2.2.0 (HIVE-13557). |
Add timeunit aliases to aid portability / readability: SECONDS / MINUTES / HOURS / DAYS / WEEKS / MONTHS / YEARS | 2 SECONDS | 2 SECOND | Hive 2.2.0 (HIVE-13557). |
Decimals
Info |
---|
|
Decimal datatype was introduced in Hive 0.11.0 (HIVE-2693) and revised in Hive 0.13.0 (HIVE-3976). |
...
For usage, see Floating Point Types in the Literals section below.anchor
Decimal Literals
Anchor |
---|
| Decimal Literals |
---|
| Decimal Literals |
---|
|
Integral literals larger than BIGINT must be handled with Decimal(38,0). The Postfix BD is required. Example:
...
The Decimal datatype is discussed further in Floating Point Types below.
Union Types
Warning |
---|
title | UNIONTYPE support is incomplete |
---|
|
The UNIONTYPE datatype was introduced in Hive 0.7.0 (HIVE-537), but full support for this type in Hive remains incomplete. Queries that reference UNIONTYPE fields in JOIN (HIVE-2508), WHERE, and GROUP BY clauses will fail, and Hive does not define syntax to extract the tag or value fields of a UNIONTYPE. This means that UNIONTYPEs are effectively look-at-only. |
...