Data types in Amazon Athena
When you run CREATE TABLE, you specify column names
and the data type that each column can contain. Athena supports the data types listed below.
For information about the data type mappings that the JDBC driver supports between Athena,
JDBC, and Java, see Data types
-
tinyint
– A 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
andinteger
– Athena uses different expressions for integer depending on the type of query.-
int
– In Data Definition Language (DDL) queries likeCREATE TABLE
, use theint
data type. -
integer
– In DML queries likeSELECT * FROM
, use theinteger
data type.integer
is represented as a 32-bit signed value in two's complement format, with a minimum value of -231 and a maximum value of 231-1.-
To ensure compatibility with business analytics applications, the JDBC driver returns the
integer
type.
-
-
-
bigint
– A 64-bit signed integer in two's complement format, with a minimum value of -263 and a maximum value of 263-1. -
double
– A 64-bit signed double-precision floating point number. The range is 4.94065645841246544e-324d to 1.79769313486231570e+308d, positive or negative.double
follows the IEEE Standard for Floating-Point Arithmetic (IEEE 754). -
float
– A 32-bit signed single-precision floating point number. The range is 1.40129846432481707e-45 to 3.40282346638528860e+38, positive or negative.float
follows the IEEE Standard for Floating-Point Arithmetic (IEEE 754). Equivalent to thereal
in Presto. In Athena, usefloat
in DDL statements likeCREATE TABLE
andreal
in SQL functions likeSELECT CAST
. The AWS Glue crawler returns values infloat
, and Athena translatesreal
andfloat
types internally (see the June 5, 2018 release notes). -
decimal
(
–precision
,scale
)
is the total number of digits.precision
(optional) is the number of digits in fractional part with a default of 0. For example, use these type definitions:scale
decimal(11,5)
,decimal(15)
. The maximum value forprecision
is 38, and the maximum value forscale
is 38.To specify decimal values as literals, such as when selecting rows with a specific decimal value in a query DDL expression, specify the
decimal
type definition, and list the decimal value as a literal (in single quotes) in your query, as in this example:decimal_value = decimal '0.12'
. -
char
– Fixed length character data, with a specified length between 1 and 255, such aschar(10)
. For more information, see CHAR Hive data type. Note To use the
substr
function to return a substring of specified length from achar
data type, you must first cast thechar
value as avarchar
, as in the following example.substr(cast(col1 as varchar), 1, 4)
-
varchar
– Variable length character data, with a specified length between 1 and 65535, such asvarchar(10)
. For more information, see VARCHAR Hive data type. -
string
– A string literal enclosed in single or double quotes. For more information, see STRING Hive data type. Note Non-string data types cannot be cast to
string
in Athena; cast them tovarchar
instead. -
date
– A date in ISO format, such as
. For example,YYYY
-MM
-DD
date '2008-09-15'
. An exception is the OpenCSVSerDe, which uses the number of days elapsed since January 1, 1970. For more information, see OpenCSVSerDe for processing CSV. -
timestamp
– Date and time instant in ajava.sql.Timestamp
compatible format up to a maximum resolution of milliseconds, such as
. For example,yyyy
-MM
-dd
HH
:mm
:ss
[.f...
]timestamp '2008-09-15 03:04:05.324'
. An exception is the OpenCSVSerDe, which usestimestamp
data in the UNIX numeric format (for example,1579059880000
). For more information, see OpenCSVSerDe for processing CSV. -
array
<
– An array of the given component type.data_type
>Example
CREATE TABLE table array_table (c1 array<integer>) LOCATION '...'; INSERT INTO array_table values(ARRAY[1,2,3]);
-
map
<
– A map between the given component types.primitive_type
,data_type
>Example
CREATE TABLE map_table(c1 map<string, integer>) LOCATION '...'; INSERT INTO map_table values(MAP(ARRAY['foo', 'bar'], ARRAY[1, 2]));
-
struct
<
– A collection of elements of different component types.col_name
:data_type
[commentcol_comment
] ,...
>Example
CREATE TABLE struct_table(c1 struct<name:varchar(10), age:integer>) LOCATION '...'; INSERT INTO struct_table SELECT CAST(ROW('Bob', 38) AS ROW(name VARCHAR(10), age INTEGER));