AWS Athena -Limitations,Supported Data Types,Supported DDL,Unsupported DDL

Limitations

Athena does not support the following features, which are supported by an open source Presto version 0.172.

User-defined functions (UDFs or UDAFs).

Stored procedures.

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.

EXPLAIN statements.

Federated connectors.

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.

Integer types

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

Floating-point types

DOUBLE

FLOAT

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

String types

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

Structural types

ARRAY < data_type >

MAP < primitive_type, data_type >

STRUCT < col_name : data_type [COMMENT col_comment] [, …] >

Supported DDL

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 DATABASE

CREATE TABLE

CREATE TABLE AS

CREATE VIEW

DESCRIBE TABLE

DESCRIBE VIEW

DROP DATABASE

DROP TABLE

DROP VIEW

MSCK REPAIR TABLE

SHOW COLUMNS

SHOW CREATE TABLE

SHOW CREATE VIEW

SHOW DATABASES

SHOW PARTITIONS

SHOW TABLES

SHOW TBLPROPERTIES

SHOW VIEWS

Unsupported DDL

The following native Hive DDLs are not supported by Athena:

ALTER INDEX

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

COMMIT

CREATE INDEX

CREATE ROLE

CREATE TABLE table_name LIKE existing_table_name

CREATE TEMPORARY MACRO

DELETE FROM

DESCRIBE DATABASE

DFS

DROP INDEX

DROP ROLE

DROP TEMPORARY MACRO

EXPORT TABLE

GRANT ROLE

IMPORT TABLE

INSERT INTO

LOCK DATABASE

LOCK TABLE

REVOKE ROLE

ROLLBACK

SHOW COMPACTIONS

SHOW CURRENT ROLES

SHOW GRANT

SHOW INDEXES

SHOW LOCKS

SHOW PRINCIPALS

SHOW ROLE GRANT

SHOW ROLES

SHOW TRANSACTIONS

START TRANSACTION

UNLOCK DATABASE

UNLOCK TABLE

©2019 by Raghavendra Kambhampati