...
- 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. |
...