Names for Tables, Databases, and Columns - Amazon Athena

Names for Tables, Databases, and Columns

Use these tips for naming items in Athena.

Table names and table column names in Athena must be lowercase

If you are interacting with Apache Spark, then your table names and table column names must be lowercase. Athena is case-insensitive and turns table names and column names to lower case, but Spark requires lowercase table and column names.

Queries with mixedCase column names, such as profileURI, or upper case column names do not work.

Special characters

Special characters other than underscore (_) are not supported. For more information, see the Apache Hive LanguageManual DDL documentation.

Important

Although you may succeed in creating table, view, database, or column names that contain special characters other than underscore by enclosing them in backtick (`) characters, subsequent DDL or DML queries that reference them can fail.

Names that begin with an underscore

When creating tables, use backticks to enclose table, view, or column names that begin with an underscore. For example:

CREATE EXTERNAL TABLE IF NOT EXISTS `_myunderscoretable`( `_id` string, `_index` string) LOCATION 's3://my-athena-data/'

Table, view, or column names that begin with numbers

When running SELECT, CTAS, or VIEW queries, put quotation marks around identifiers like table, view, or column names that start with a digit. For example:

CREATE OR REPLACE VIEW "123view" AS SELECT "123columnone", "123columntwo" FROM "234table"

Reserved words

Certain reserved words in Athena must be escaped. To escape reserved keywords in DDL statements, enclose them in backticks (`). To escape reserved keywords in SQL SELECT statements and in queries on views, enclose them in double quotes ('').

For more information, see Reserved Keywords.