Database, table, and column name requirements Use lower case for table names and table column names in Athena Names that begin with an underscore Table, view, or column names that begin with numbers Column names and complex types Reserved words See also

Names for tables, databases, and columns

Use these tips for naming database objects in Athena.

Database, table, and column name requirements

Acceptable characters for database names, table names, and column names in Amazon Glue must be a UTF-8 string. The string must not be less than 1 or more than 255 bytes long. Exceeding this limit generates an error like Value at 'name' failed to satisfy constraint: Member must have length less than or equal to 255. Characters that can be used include spaces and are defined by the following single-line string pattern:
```
[\u0020-\uD7FF\uE000-\uFFFD\uD800\uDC00-\uDBFF\uDFFF\t]*
```
Currently, the Amazon Glue regex pattern allows leading spaces to be added to the start of names. Because these leading spaces can be hard to detect and can cause usability issues after creation, avoid creating object names that have leading spaces.
If you use an AWS::Glue::Database Amazon CloudFormation template to create an Amazon Glue database and do not specify a database name, Amazon Glue automatically generates a database name in the format resource_name–random_string that is not compatible with Athena.
You can use the Amazon Glue Catalog Manager to rename columns, but not table names or database names. To work around this limitation, you must use a definition of the old database to create a database with the new name. Then you use definitions of the tables from the old database to re-create the tables in the new database. To do this, you can use the Amazon CLI or Amazon Glue SDK. For steps, see Using the Amazon CLI to recreate an Amazon Glue database and its tables.

Use lower case for table names and table column names in Athena

Athena accepts mixed case in DDL and DML queries, but lower cases the names when it executes the query. For this reason, avoid using mixed case for table or column names, and do not rely on casing alone in Athena to distinguish such names. For example, if you use a DDL statement to create a column named Castle, the column created will be lowercased to castle. If you then specify the column name in a DML query as Castle or CASTLE, Athena will lowercase the name for you to run the query, but display the column heading using the casing that you chose in the query.

Database, table, and column names must be less than or equal to 255 characters long.

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"

Column names and complex types

For complex types, only alphanumeric characters, underscore (_), and period (.) are allowed in column names. To create a table and mappings for keys that have restricted characters, you can use a custom DDL statement. For more information, see the article Create tables in Amazon Athena from nested JSON and mappings using JSONSerDe in the Amazon Big Data Blog.

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.