...
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)
String Types
STRING
VARCHAR
(Note: Only available starting with Hive 0.12.0)CHAR
(Note: Only available starting with Hive 0.13.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 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.
Type | Postfix | Example |
---|---|---|
TINYINT | Y | 100Y |
SMALLINT | S | 100S |
BIGINT | L | 100L |
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 will be was introduced in Hive 0.13.0 (HIVE-5191). |
Timestamps
Anchor | ||||
---|---|---|---|---|
|
Anchor | ||||
---|---|---|---|---|
|
Supports traditional UNIX timestamp with optional nanosecond precision.
...
Timestamps in text files have to use the format yyyy-mm-dd hh:mm:ss[.f...]
. If they are in another format, declare them as the appropriate type (INT, FLOAT, STRING, etc.) and use a UDF to convert them to timestamps.
On the table level, alternative timestamp formats can be supported by providing the format to the SerDe property "timestamp.formats" (as of release 1.2.0 with HIVE-9298). For example, yyyy-MM-dd'T'HH:mm:ss.SSS,yyyy-MM-dd'T'HH:mm:ss.
Info | ||
---|---|---|
| ||
Timestamps were introduced in Hive 0.8.0 | ||
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 be 0000-01-01 to 9999-12-31, dependent on
support by the primitive Java Date type.
...
Valid casts to/from Date type | Result |
---|---|
cast(date as date) | Same date value |
cast(timestamp as date) | The year/month/day of the timestamp is determined, based on the local timezone, and returned as a date value. |
cast(string as date) | If the string is in the form 'YYYY-MM-DD', then a date value corresponding to that year/month/day is returned. If the string value does not match this formate, then NULL is returned. |
cast(date as timestamp) | A timestamp value is generated corresponding to midnight of the year/month/day of the date value, based on the local timezone. |
cast(date as string) | The year/month/day represented by the Date is formatted as a string in the form 'YYYY-MM-DD'. |
...
Decimals
...
title | Version |
---|
...
Intervals
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 keyword
| 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 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 | ||||
---|---|---|---|---|
|
Warninginfo | |||
---|---|---|---|
| |||
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 pass-through-onlyUnion datatype was introduced in Hive 0.7.0 (HIVE-537). See HIVE-2390 for planned improvements. |
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.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.
...
- Positive
- Negative
- Addition
- Subtraction
- Multiplication
- Division
- Average (avg)
- Sum
- Count
- Modulus (pmod)
- Sign – Hive 0.13.0 and laterlater
- Exp – Hive 0.13.0 and later
- Ln – Hive 0.13.0 and later
- Log2 – Hive 0.13.0 and later
- Log10 – Hive 0.13.0 and later
- Log(base) – Hive 0.13.0 and later
- Sqrt – Exp – Hive 0.13.0 and later
- Ln – Sin – Hive 0.13.0 and later
- Log2 – Asin – Hive 0.13.0 and later
- Log10 – Cos – Hive 0.13.0 and later
- Log(base) – Acos – Hive 0.13.0 and later
- Sqrt – Tan – Hive 0.13.0 and later
- Sin – Atan – Hive 0.13.0 and later
- Asin – Radians – Hive 0.13.0 and later
- Cos – Degrees – Hive 0.13.0 and later
- Acos – Hive 0.13.0 and later
- Tan – Hive 0.13.0 and later
- Atan – Hive 0.13.0 and later
- Radians – Hive 0.13.0 and later
- Degrees – Hive 0.13.0 and later
These rounding functions can also take decimal types:
- Floor
- Ceiling
- Round
Power(decimal, n) only supports positive integer values for the exponent n.
Casting Decimal Values
Casting is supported between decimal values and any other primitive type such as integer, double, boolean, and so on.
Testing Decimal Types
Two new tests have been added as part of the TestCliDriver framework within Hive. They are decimal_1.q and decimal_2.q. Other tests such as udf7.q cover the gamut of UDFs mentioned above.
More tests need to be added that demonstrate failure or when certain types of casts are prevented (for example, casting to date). There is some ambiguity in the round function because the rounding of Decimal does not work exactly as the SQL standard, and therefore it has been omitted in the current work.
For general information about running Hive tests, see How to Contribute to Apache Hive and Hive Developer FAQ.
Handling of NULL Values
- and later
These rounding functions can also take decimal types:
- Floor
- Ceiling
- Round
Power(decimal, n) only supports positive integer values for the exponent n.
Casting Decimal Values
Casting is supported between decimal values and any other primitive type such as integer, double, boolean, and so on.
Testing Decimal Types
Two new tests have been added as part of the TestCliDriver framework within Hive. They are decimal_1.q and decimal_2.q. Other tests such as udf7.q cover the gamut of UDFs mentioned above.
More tests need to be added that demonstrate failure or when certain types of casts are prevented (for example, casting to date). There is some ambiguity in the round function because the rounding of Decimal does not work exactly as the SQL standard, and therefore it has been omitted in the current work.
For general information about running Hive tests, see How to Contribute to Apache Hive and Hive Developer FAQ.
Handling of NULL 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.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.)
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 |