...
TINYINT
(1-byte signed integer, from-128
to127
)SMALLINT
(2-byte signed integer, from-32,768
to32,767
)INT/INTEGER (4-byte signed integer, from -2,147,483,648 to 2,147,483,647)
BIGINT
(8-byte signed integer, from-9,223,372,036,854,775,808
to9,223,372,036,854,775,807
)FLOAT
(4-byte single precision floating point number)DOUBLE
(8-byte double precision floating point number)DOUBLE PRECISION (alias for DOUBLE, only available starting with Hive 2.2.0)
DECIMAL
NUMERIC
(same asDECIMAL
, starting with Hive 3.0.0)
Date/Time Types
TIMESTAMP
(Note: Only available starting with Hive 0.8.0)DATE
(Note: Only available starting with Hive 0.12.0)INTERVAL
(Note: Only available starting with Hive 1.2.0)
...
- 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 | ||||
---|---|---|---|---|
|
Anchor | ||||
---|---|---|---|---|
|
Anchor | ||||
---|---|---|---|---|
|
Anchor | ||||
---|---|---|---|---|
|
Anchor | ||||
---|---|---|---|---|
|
...
Integral literals
Column Types
Integral Types (TINYINT
, SMALLINT
, INT/INTEGER
, BIGINT
)
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 | ||
---|---|---|
| ||
|
Strings
Anchor | ||||
---|---|---|---|---|
|
Anchor | ||||
---|---|---|---|---|
|
String literals can be expressed with either single quotes (') or double quotes ("). Hive uses C-style escaping within the strings.
Varchar
Anchor | ||||
---|---|---|---|---|
|
Anchor | ||||
---|---|---|---|---|
|
Varchar types are created with a length specifier (between 1 and 6535565535), 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
Anchor | ||||
---|---|---|---|---|
|
Anchor | ||||
---|---|---|---|---|
|
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
Anchor | ||||
---|---|---|---|---|
|
Anchor | ||||
---|---|---|---|---|
|
Supports traditional UNIX timestamp with optional nanosecond precision.
...
Info | ||
---|---|---|
| ||
Timestamps were introduced in Hive 0.8.0 (HIVE-2272). |
Dates
Anchor | ||||
---|---|---|---|---|
|
Anchor | ||||
---|---|---|---|---|
|
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 (+/-) | INTERVAL '1-2' YEAR TO MONTH | shorthand for: INTERVAL '1' YEAR + | Hive 1.2.0 (HIVE-9792). | ||
Day to second intervals, format: SD H:M:S.nnnnnn S: optional sign (+/-) D: day countH: hoursM: minutes S: seconds nnnnnn: optional nanotime | INTERVAL '1 2:3:4.000005' DAY | shorthand for: INTERVAL '1' DAY+ | 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: | INTERVAL (1+dt) DAY | enables dynamic intervals | Hive 2.2.0 (HIVE-13557). | ||
Optional usage of interval keyword1 DAY
| 1 DAY | INTERVAL 1 DAY | 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
Anchor | ||||
---|---|---|---|---|
|
...
Anchor | ||||
---|---|---|---|---|
|
Info | ||
---|---|---|
| ||
Decimal datatype was introduced in Hive 0.11.0 (HIVE-2693) and revised in Hive 0.13.0 (HIVE-3976).
|
The DECIMAL
type in Hive is based on Java's BigDecimal which is used for representing immutable arbitrary precision decimal numbers in Java. All regular number operations (e.g. +, -, *, /) and relevant UDFs (e.g. Floor, Ceil, Round, and many more) handle decimal types. You can cast to/from decimal types like you would do with other numeric types. The persistence format of the decimal type supports both scientific and non-scientific notation. Therefore, regardless of whether your dataset contains data like 1E+44 like 4.004E+3 (scientific notation) or 4004 (non-scientific notation) or a combination of both, DECIMAL
can be used for it.
...
For usage, see Floating Point Types in the Literals section below.anchor
Decimal Literals
Anchor | ||||
---|---|---|---|---|
|
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
Anchor | ||||
---|---|---|---|---|
|
Anchor | ||||
---|---|---|---|---|
|
Warning | ||
---|---|---|
Warning | ||
| ||
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 lookpass-atthrough-only. |
Union types can at any one point hold exactly one of their specified data types. You can create an instance of this type using the create_union
UDF:
...
Info | ||
---|---|---|
| ||
Decimal datatype was introduced in Hive 0.11.0 (HIVE-2693). See Decimal Datatype above.
|
Decimal literals provide precise values and greater range for floating point numbers than the DOUBLE type. Decimal data types store exact representations of numeric values, while DOUBLE data types store very close approximations of numeric values.
...
Missing values are represented by the special value NULL. To import data with NULL fields, check documentation of the SerDe used by the table. (The default Text Format uses LazySimpleSerDe which interprets the string \N as NULL when importing.)
Change Types
When hive.metastore.disallow.incompatible.col.type.changes is set to false, the types of columns in Metastore can be changed from any type to any other type. After such a type change, if the data can be shown correctly with the new type, the data will be displayed. Otherwise, the data will be displayed as NULL.
Allowed Implicit Conversions
| void | boolean | tinyint | smallint | int | bigint | float | double | decimal | string | varchar | timestamp | date | binary |
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
void to | true | true | true | true | true | true | true | true | true | true | true | true | true | true |
boolean to | false | true | false | false | false | false | false | false | false | false | false | false | false | false |
tinyint to | false | false | true | true | true | true | true | true | true | true | true | false | false | false |
smallint to | false | false | false | true | true | true | true | true | true | true | true | false | false | false |
int to | false | false | false | false | true | true | true | true | true | true | true | false | false | false |
bigint to | false | false | false | false | false | true | true | true | true | true | true | false | false | false |
float to | false | false | false | false | false | false | true | true | true | true | true | false | false | false |
double to | false | false | false | false | false | false | false | true | true | true | true | false | false | false |
decimal to | false | false | false | false | false | false | false | false | true | true | true | false | false | false |
string to | false | false | false | false | false | false | false | true | true | true | true | false | false | false |
varchar to | false | false | false | false | false | false | false | true | true | true | true | false | false | false |
timestamp to | false | false | false | false | false | false | false | false | false | true | true | true | false | false |
date to | false | false | false | false | false | false | false | false | false | true | true | false | true | false |
binary to | false | false | false | false | false | false | false | false | false | false | false | false | false | true |