This Confluence has been LDAP enabled, if you are an ASF Committer, please use your LDAP Credentials to login. Any problems file an INFRA jira ticket please.

Child pages
  • LanguageManual Types

Versions Compared

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.
Comment: Refactor of page - partly done

Hive Data Types

Table of Contents

Column Types

...

Overview

This lists all supported data types in Hive:Primitive types:.

Numeric Types

Date/Time Types

  • TIMESTAMP (Note: Only available starting with Hive 0.8.0)
  • DECIMAL DATE (Note: Only available starting with Hive 0.1112.0)DATE

Misc Types

  • BOOLEAN
  • STRING
  • BINARY (Note: Only available starting with Hive 0.128.0)

Complex

...

Types

  • arrays: ARRAY<data_type>
  • maps: MAP<primitive_type, data_type>
  • Wiki Markup
    structs: {{STRUCT<col_name : data_type \[COMMENT col_comment], ...>}}
  • union: UNIONTYPE<data_type, data_type, ...>

Column Types

Integral Types (TINYINT, SMALLINT, INT, BIGINT)

Anchor
tinyint
tinyint
Anchor
smallint
smallint
Anchor
int
int
Anchor
bigint
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.

Type

Postfix

Example

TINYINT

Y

100Y

SMALLINT

S

100S

BIGINT

L

100L

Strings

Anchor
string
string

String literals can be expressed with either single quotes (') or double quotes ("). Hive uses C-style escaping within the strings.

Timestamps

Anchor
timestamp
timestamp

Supports traditional UNIX timestamp with optional nanosecond precision.

...

  • Integer numeric types: Interpreted as UNIX timestamp in seconds
  • Floating point numeric types: Intepreted Interpreted as UNIX timestamp in seconds with decimal precision
  • Strings: JDBC compliant java.sql.Timestamp format "YYYY-MM-DD HH:MM:SS.fffffffff" (9 decimal place precision)

Timestamps are interpreted to be timezoneless and stored as an offset from the UNIX epoch. Convenience UDFs for conversion to and from timezones are provided (to_utc_timestamp, from_utc_timestamp).
All existing datetime UDFs (month, day, year, hour, etc.) work with the TIMESTAMP data type.

Info
titleVersion

Timestamps were introduced in Hive 0.8.0 (HIVE-2272).

Decimal Datatype

...

Decimals

Anchor
decimal
decimal

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) have been updated to 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 (scientific notation) or 4004 (non-scientific notation) or a combination of both, decimal datatype DECIMAL can be used for it.

With the present Hive implementation, the Decimal datatype type has precision limited to 38 digits. While this has minimal impact on usability, it's worth noting for performance reasons. HIVE-3796 is tracking the progress of allowing users to specify scale and precision when creating tables with decimal datatype.

...

Info
titleVersion

Decimal datatype was introduced in Hive 0.11.0 (HIVE-2693).

Union Types

Anchor
union
union

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:

...

No Format
SELECT create_union(0, key), create_union(if(key<100, 0, 1), 2.0, value), create_union(1, "a", struct(2, "b")) FROM src LIMIT 2;

{0:"238"}	{1:"val_238"}	{1:{"col1":2,"col2":"b"}}
{0:"86"}	{0:2.0}	{1:{"col1":2,"col2":"b"}}

Literals

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

Type

Postfix

Example

TINYINT

Y

100Y

SMALLINT

S

100S

BIGINT

L

100L

String Types

String literals can be expressed with either single quotes (') or double quotes ("). Hive uses C-style escaping within the strings.

Floating Point Types

Floating point literals are assumed to be DOUBLE. Scientific notation is not yet supported.

...