Menu
Amazon Athena
User Guide

SQL and HiveQL Reference

Amazon Athena is based on the Hive metastore and Presto.

Athena syntax consists of a combination of standard ANSI SQL for queries (select) and relational operations (join) and HiveQL DDL statements for altering metadata (create, alter).

SQL Queries

DDL Statements

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

  • CREATE VIEW

  • 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

Functions

The functions supported in Athena queries are those found within Presto. For more information, see Functions and Operators in the Presto documentation.