Updated: Jul 17, 2020
Athena does not support the following features, which are supported by an open source Presto version 0.172.
User-defined functions (UDFs or UDAFs).
A particular subset of data types is supported.
INSERT INTO statements.
The maximum number of partitions you can create in the CREATE TABLE AS SELECT (CTAS) statements is 100.
PREPARED statements. You cannot run EXECUTE with USING.
CREATE TABLE LIKE.
DESCRIBE INPUT and DESCRIBE OUTPUT.
When you query columns with complex data types (array, map, struct), and are using Parquet for storing data, Athena currently reads an entire row of data, instead of selectively reading only the specified columns as expected. This is a known issue.
List of Supported Data Types in Athena
Athena supports the following data types:
BOOLEAN. Values are true and false.
- TINYINT. A 8-bit signed INTEGER in two’s complement format, with a minimum value of -2^7 and a maximum value of 2^7-1.
- SMALLINT. A 16-bit signed INTEGER in two’s complement format, with a minimum value of -2^15 and a maximum value of 2^15-1.
- INT. Athena combines two different implementations of the INTEGER data type. In Data Definition Language (DDL) queries, Athena uses the INT data type. In all other queries, Athena uses the INTEGER data type, where INTEGER is represented as a 32-bit signed value in two’s complement format, with a minimum value of-2^31 and a maximum value of 2^31-1. In the JDBC driver, INTEGER is returned, to ensure compatibility with business analytics applications.
- BIGINT.A 64-bit signed INTEGER in two’s complement format, with a minimum value of -2^63 and a maximum value of 2^63-1.
Fixed precision type
DECIMAL [ (precision, scale) ], where precision is the total number of digits, and scale (optional) is the number of digits in fractional part, the default is 0. For example, use these type definitions: DECIMAL(11,5), DECIMAL(15).
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 as char(10).
- VARCHAR. Variable length character data, with a specified length between 1 and 65535, such as varchar(10).
BINARY (for data in Parquet)
Date and time types
DATE, in the UNIX format, such as YYYY-MM-DD.
TIMESTAMP. Instant in time and date in the UNiX format, such as yyyy-mm-dd hh:mm:ss[.f…]. For example, TIMESTAMP ‘2008-09-15 03:04:05.324’. This format uses the session time zone.
ARRAY < data_type >
MAP < primitive_type, data_type >
STRUCT < col_name : data_type [COMMENT col_comment] [, …] >
ALTER DATABASE SET DBPROPERTIES
ALTER TABLE ADD PARTITION
ALTER TABLE DROP PARTITION
ALTER TABLE RENAME PARTITION
ALTER TABLE SET LOCATION
ALTER TABLE SET TBLPROPERTIES
CREATE TABLE AS
MSCK REPAIR TABLE
SHOW CREATE TABLE
SHOW CREATE VIEW
The following native Hive DDLs are not supported by Athena:
ALTER TABLE table_name ARCHIVE PARTITION
ALTER TABLE table_name CLUSTERED BY
ALTER TABLE table_name EXCHANGE PARTITION
ALTER TABLE table_name NOT CLUSTERED
ALTER TABLE table_name NOT SKEWED
ALTER TABLE table_name NOT SORTED
ALTER TABLE table_name NOT STORED AS DIRECTORIES
ALTER TABLE table_name partitionSpec ADD COLUMNS
ALTER TABLE table_name partitionSpec CHANGE COLUMNS
ALTER TABLE table_name partitionSpec COMPACT
ALTER TABLE table_name partitionSpec CONCATENATE
ALTER TABLE table_name partitionSpec REPLACE COLUMNS
ALTER TABLE table_name partitionSpec SET FILEFORMAT
ALTER TABLE table_name RENAME TO
ALTER TABLE table_name SET SKEWED LOCATION
ALTER TABLE table_name SKEWED BY
ALTER TABLE table_name TOUCH
ALTER TABLE table_name UNARCHIVE PARTITION
CREATE TABLE table_name LIKE existing_table_name
CREATE TEMPORARY MACRO
DROP TEMPORARY MACRO
SHOW CURRENT ROLES
SHOW ROLE GRANT