Data types in Amazon Athena - Amazon Athena

Data types in Amazon Athena

When you run CREATE TABLE, you specify column names and the data type that each column can contain. The tables that you create are stored in the AWS Glue Data Catalog.

To facilitate interoperability with other query engines, Athena uses Apache Hive data type names for DDL statements like CREATE TABLE. For DML queries like SELECT, CTAS, and INSERT INTO, Athena uses Trino data type names. The following table shows the data types supported in Athena. Where DDL and DML types differ in terms of name, availability, or syntax, they are shown in separate columns.

DDL DML Description
BOOLEAN Values are true and false.
TINYINT An 8-bit signed integer in two's complement format, with a minimum value of -27 and a maximum value of 27-1.
SMALLINT A 16-bit signed integer in two's complement format, with a minimum value of -215 and a maximum value of 215-1.
INT, INTEGER A 32-bit signed value in two's complement format, with a minimum value of -231 and a maximum value of 231-1.
BIGINT A 64-bit signed integer in two's complement format, with a minimum value of -263 and a maximum value of 263-1.
FLOAT REAL A 32-bit signed single-precision floating point number. The range is 1.40129846432481707e-45 to 3.40282346638528860e+38, positive or negative. Follows the IEEE Standard for Floating-Point Arithmetic (IEEE 754).
DOUBLE A 64-bit signed double-precision floating point number. The range is 4.94065645841246544e-324d to 1.79769313486231570e+308d, positive or negative. Follows the IEEE Standard for Floating-Point Arithmetic (IEEE 754).
DECIMAL(precision, scale) precision is the total number of digits. scale (optional) is the number of digits in fractional part with a default of 0. For example, use these type definitions: decimal(11,5), decimal(15). The maximum value for precision is 38, and the maximum value for scale is 38.
CHAR, CHAR(length)

Fixed length character data, with a specified length between 1 and 255, such as char(10). If length is specified, strings are truncated at the specified length when read. If the underlying data string is longer, the underlying data string remains unchanged.

For more information, see CHAR Hive data type.

STRING VARCHAR Variable length character data.
VARCHAR(length) Variable length character data with a maximum read length. Strings are truncated at the specified length when read. If the underlying data string is longer, the underlying data string remains unchanged.
BINARY VARBINARY Variable length binary data.
TIME A time of day with millisecond precision.
Not available TIME(precision) A time of day with a specific precision. TIME(3) is equivalent to TIME.
Not available TIME WITH TIME ZONE A time of day in a time zone. Time zones should be specified as offsets from UTC.
DATE A calendar date with year, month, and day.
TIMESTAMP TIMESTAMP, TIMESTAMP WITHOUT TIME ZONE A calendar date and time of day with millisecond precision.
Not available TIMESTAMP(precision), TIMESTAMP(precision) WITHOUT TIME ZONE A calendar date and time of day with a specific precision. TIMESTAMP(3) is equivalent to TIMESTAMP.
Not available TIMESTAMP WITH TIME ZONE A calendar date and time of day in a time zone. Time zones can be specified as offsets from UTC, as IANA time zone names, or using UTC, UT, Z, or GMT.
Not available TIMESTAMP(precision) WITH TIME ZONE A calendar date and time of day with a specific precision, in a time zone.
Not available INTERVAL YEAR TO MONTH An interval of one or more whole months
Not available INTERVAL DAY TO SECOND An interval of one or more seconds, minutes, hours, or days
ARRAY<element_type> ARRAY[element_type] An array of values. All values must be of the same data type.
MAP<key_type, value_type> MAP(key_type, value_type) A map where values can be looked up by key. All keys must be of the same data type, and all values must be of the same data type. For example, map<string, integer>.
STRUCT<field_name_1:field_type_1, field_name_2:field_type_2, …> ROW(field_name_1 field_type_1, field_name_2 field_type_2, …) A data structure with named fields and their values.
Not available JSON JSON value type, which can be a JSON object, a JSON array, a JSON number, a JSON string, true, false or null.
Not available UUID A UUID (Universally Unique IDentifier).
Not available IPADDRESS An IPv4 or IPv6 address.
Not available HyperLogLog These data types support approximate function internals. For more information about each type, visit the link to the corresponding entry in the Trino documentation.
P4HyperLogLog
SetDigest
QDigest
TDigest