# DDL statements
Use the supported data definition language (DDL) statements presented here directly in Athena. The Athena query engine is based in part on [HiveQL DDL](https://cwiki.apache.org/confluence/display/Hive/LanguageManual+DDL). Athena does not support all DDL statements, and there are some differences between HiveQL DDL and Athena DDL. For more information, see the reference topics in this section and [Unsupported DDL](unsupported-ddl.md).
**Topics**
+ [Unsupported DDL](unsupported-ddl.md)
+ [ALTER DATABASE SET DBPROPERTIES](alter-database-set-dbproperties.md)
+ [ALTER TABLE ADD COLUMNS](alter-table-add-columns.md)
+ [ALTER TABLE ADD PARTITION](alter-table-add-partition.md)
+ [ALTER TABLE CHANGE COLUMN](alter-table-change-column.md)
+ [ALTER TABLE DROP PARTITION](alter-table-drop-partition.md)
+ [ALTER TABLE RENAME PARTITION](alter-table-rename-partition.md)
+ [ALTER TABLE REPLACE COLUMNS](alter-table-replace-columns.md)
+ [ALTER TABLE SET LOCATION](alter-table-set-location.md)
+ [ALTER TABLE SET TBLPROPERTIES](alter-table-set-tblproperties.md)
+ [ALTER VIEW DIALECT](alter-view-dialect.md)
+ [CREATE DATABASE](create-database.md)
+ [CREATE TABLE](create-table.md)
+ [CREATE TABLE AS](create-table-as.md)
+ [CREATE VIEW](create-view.md)
+ [DESCRIBE](describe-table.md)
+ [DESCRIBE VIEW](describe-view.md)
+ [DROP DATABASE](drop-database.md)
+ [DROP TABLE](drop-table.md)
+ [DROP VIEW](drop-view.md)
+ [MSCK REPAIR TABLE](msck-repair-table.md)
+ [SHOW COLUMNS](show-columns.md)
+ [SHOW CREATE TABLE](show-create-table.md)
+ [SHOW CREATE VIEW](show-create-view.md)
+ [SHOW DATABASES](show-databases.md)
+ [SHOW PARTITIONS](show-partitions.md)
+ [SHOW TABLES](show-tables.md)
+ [SHOW TBLPROPERTIES](show-tblproperties.md)
+ [SHOW VIEWS](show-views.md)
# Unsupported DDL
The following DDL statements are not supported by Athena SQL. For DDL statements supported for Iceberg tables in Athena, see [Evolve Iceberg table schema](querying-iceberg-evolving-table-schema.md) and [Perform other DDL operations on Iceberg tables](querying-iceberg-additional-operations.md).
+ ALTER INDEX
+ ALTER TABLE *table\$1name* ARCHIVE PARTITION
+ ALTER TABLE *table\$1name* CLUSTERED BY
+ ALTER TABLE *table\$1name* DROP COLUMN (supported for Iceberg tables)
+ ALTER TABLE *table\$1name* EXCHANGE PARTITION
+ ALTER TABLE *table\$1name* NOT CLUSTERED
+ ALTER TABLE *table\$1name* NOT SKEWED
+ ALTER TABLE *table\$1name* NOT SORTED
+ ALTER TABLE *table\$1name* NOT STORED AS DIRECTORIES
+ ALTER TABLE *table\$1name* partitionSpec CHANGE COLUMNS
+ ALTER TABLE *table\$1name* partitionSpec COMPACT
+ ALTER TABLE *table\$1name* partitionSpec CONCATENATE
+ ALTER TABLE *table\$1name* partitionSpec SET FILEFORMAT
+ ALTER TABLE *table\$1name* RENAME TO (supported for Iceberg tables)
+ ALTER TABLE *table\$1name* SET SERDEPROPERTIES
+ ALTER TABLE *table\$1name* SET SKEWED LOCATION
+ ALTER TABLE *table\$1name* SKEWED BY
+ ALTER TABLE *table\$1name* TOUCH
+ ALTER TABLE *table\$1name* UNARCHIVE PARTITION
+ COMMIT
+ CREATE INDEX
+ CREATE ROLE
+ CREATE TABLE *table\$1name* LIKE *existing\$1table\$1name*
+ CREATE TEMPORARY MACRO
+ DELETE FROM
+ DESCRIBE DATABASE
+ DFS
+ DROP INDEX
+ DROP ROLE
+ DROP TEMPORARY MACRO
+ EXPORT TABLE
+ GRANT ROLE
+ IMPORT TABLE
+ 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 STATS
+ SHOW TRANSACTIONS
+ START TRANSACTION
+ UNLOCK DATABASE
+ UNLOCK TABLE
# ALTER DATABASE SET DBPROPERTIES
Creates one or more properties for a database. The use of `DATABASE` and `SCHEMA` are interchangeable; they mean the same thing.
## Synopsis
```
ALTER {DATABASE|SCHEMA} database_name
SET DBPROPERTIES ('property_name'='property_value' [, ...] )
```
## Parameters
**SET DBPROPERTIES ('property\$1name'='property\$1value' [, ...]**
Specifies a property or properties for the database named `property_name` and establishes the value for each of the properties respectively as `property_value`. If `property_name` already exists, the old value is overwritten with `property_value`.
## Examples
```
ALTER DATABASE jd_datasets
SET DBPROPERTIES ('creator'='John Doe', 'department'='applied mathematics');
```
```
ALTER SCHEMA jd_datasets
SET DBPROPERTIES ('creator'='Jane Doe');
```
# ALTER TABLE ADD COLUMNS
Adds one or more columns to an existing table. When the optional `PARTITION` syntax is used, updates partition metadata.
## Synopsis
```
ALTER TABLE table_name
[PARTITION
(partition_col1_name = partition_col1_value
[,partition_col2_name = partition_col2_value][,...])]
ADD COLUMNS (col_name data_type)
```
## Parameters
**PARTITION (partition\$1col\$1name = partition\$1col\$1value [,...])**
Creates a partition with the column name/value combinations that you specify. Enclose `partition_col_value` in quotation marks only if the data type of the column is a string.
**ADD COLUMNS (col\$1name data\$1type [,col\$1name data\$1type,...])**
Adds columns after existing columns but before partition columns.
## Examples
```
ALTER TABLE events ADD COLUMNS (eventowner string)
```
```
ALTER TABLE events PARTITION (awsregion='us-west-2') ADD COLUMNS (event string)
```
```
ALTER TABLE events PARTITION (awsregion='us-west-2') ADD COLUMNS (eventdescription string)
```
## Notes
+ To see a new table column in the Athena Query Editor navigation pane after you run `ALTER TABLE ADD COLUMNS`, manually refresh the table list in the editor, and then expand the table again.
+ `ALTER TABLE ADD COLUMNS` does not work for columns with the `date` datatype. To workaround this issue, use the `timestamp` datatype instead.
# ALTER TABLE ADD PARTITION
Creates one or more partition columns for the table. Each partition consists of one or more distinct column name/value combinations. A separate data directory is created for each specified combination, which can improve query performance in some circumstances. Partitioned columns don't exist within the table data itself, so if you use a column name that has the same name as a column in the table itself, you get an error. For more information, see [Partition your data](partitions.md).
In Athena, a table and its partitions must use the same data formats but their schemas may differ. For more information, see [Update tables with partitions](updates-and-partitions.md).
For information about the resource-level permissions required in IAM policies (including `glue:CreatePartition`), see [AWS Glue API permissions: Actions and resources reference](https://docs.aws.amazon.com/glue/latest/dg/api-permissions-reference.html) and [Configure access to databases and tables in the AWS Glue Data Catalog](fine-grained-access-to-glue-resources.md). For troubleshooting information about permissions when using Athena, see the [Permissions](troubleshooting-athena.md#troubleshooting-athena-permissions) section of the [Troubleshoot issues in Athena](troubleshooting-athena.md) topic.
## Synopsis
```
ALTER TABLE table_name ADD [IF NOT EXISTS]
PARTITION
(partition_col1_name = partition_col1_value
[,partition_col2_name = partition_col2_value]
[,...])
[LOCATION 'location1']
[PARTITION
(partition_colA_name = partition_colA_value
[,partition_colB_name = partition_colB_value
[,...])]
[LOCATION 'location2']
[,...]
```
## Parameters
When you add a partition, you specify one or more column name/value pairs for the partition and the Amazon S3 path where the data files for that partition reside.
**[IF NOT EXISTS]**
Causes the error to be suppressed if a partition with the same definition already exists.
**PARTITION (partition\$1col\$1name = partition\$1col\$1value [,...])**
Creates a partition with the column name/value combinations that you specify. Enclose `partition_col_value` in string characters only if the data type of the column is a string.
**[LOCATION 'location']**
Specifies the directory in which to store the partition defined by the preceding statement. The `LOCATION` clause is optional when the data uses Hive-style partitioning (`pk1=v1/pk2=v2/pk3=v3`). With Hive-style partitioning, the full Amazon S3 URI is constructed automatically from the table's location, the partition key names, and the partition key values. For more information, see [Partition your data](partitions.md).
## Considerations
Amazon Athena does not impose a specific limit on the number of partitions you can add in a single `ALTER TABLE ADD PARTITION` DDL statement. However, if you need to add a significant number of partitions, consider breaking the operation into smaller batches to avoid potential performance issues. The following example uses successive commands to add partitions individually and uses `IF NOT EXISTS` to avoid adding duplicates.
```
ALTER TABLE table_name ADD IF NOT EXISTS PARTITION (ds='2023-01-01')
ALTER TABLE table_name ADD IF NOT EXISTS PARTITION (ds='2023-01-02')
ALTER TABLE table_name ADD IF NOT EXISTS PARTITION (ds='2023-01-03')
```
When working with partitions in Athena, also keep in mind the following points:
+ Although Athena supports querying AWS Glue tables that have 10 million partitions, Athena cannot read more than 1 million partitions in a single scan.
+ To optimize your queries and reduce the number of partitions scanned, consider strategies like partition pruning or using partition indexes.
For additional considerations regarding working with partitions in Athena, see [Partition your data](partitions.md).
## Examples
The following example adds a single partition to a table for Hive-style partitioned data.
```
ALTER TABLE orders ADD
PARTITION (dt = '2016-05-14', country = 'IN');
```
The following example adds multiple partitions to a table for Hive-style partitioned data.
```
ALTER TABLE orders ADD
PARTITION (dt = '2016-05-31', country = 'IN')
PARTITION (dt = '2016-06-01', country = 'IN');
```
When the table is not for Hive-style partitioned data, the `LOCATION` clause is required and should be the full Amazon S3 URI for the prefix that contains the partition's data.
```
ALTER TABLE orders ADD
PARTITION (dt = '2016-05-31', country = 'IN') LOCATION 's3://amzn-s3-demo-bucket/path/to/INDIA_31_May_2016/'
PARTITION (dt = '2016-06-01', country = 'IN') LOCATION 's3://amzn-s3-demo-bucket/path/to/INDIA_01_June_2016/';
```
To ignore errors when the partition already exists, use the `IF NOT EXISTS` clause, as in the following example.
```
ALTER TABLE orders ADD IF NOT EXISTS
PARTITION (dt = '2016-05-14', country = 'IN');
```
## Zero byte `_$folder$` files
If you run an `ALTER TABLE ADD PARTITION` statement and mistakenly specify a partition that already exists and an incorrect Amazon S3 location, zero byte placeholder files of the format `partition_value_$folder$` are created in Amazon S3. You must remove these files manually.
To prevent this from happening, use the `ADD IF NOT EXISTS` syntax in your `ALTER TABLE ADD PARTITION` statement, as in the following example.
```
ALTER TABLE table_name ADD IF NOT EXISTS PARTITION […]
```
# ALTER TABLE CHANGE COLUMN
Changes the name, type, order, or comment for a column in a table.
## Synopsis
```
ALTER TABLE [db_name.]table_name
CHANGE [COLUMN] col_old_name col_new_name column_type
[COMMENT col_comment] [FIRST|AFTER column_name]
```
## Examples
The following example changes the column name `area` to `zip`, makes the data type integer, and places the renamed column after the `id` column.
```
ALTER TABLE example_table CHANGE COLUMN area zip int AFTER id
```
The following example adds a comment to the `zip` column in the metadata for `example_table`. To see the comment, use the AWS CLI [https://awscli.amazonaws.com/v2/documentation/api/latest/reference/athena/get-table-metadata.html](https://awscli.amazonaws.com/v2/documentation/api/latest/reference/athena/get-table-metadata.html) command or visit the schema for the table in the AWS Glue console.
```
ALTER TABLE example_table CHANGE COLUMN zip zip int COMMENT 'USA zipcode'
```
# ALTER TABLE DROP PARTITION
Drops one or more specified partitions for the named table.
## Synopsis
```
ALTER TABLE table_name DROP [IF EXISTS] PARTITION (partition_spec) [, PARTITION (partition_spec)]
```
## Parameters
**[IF EXISTS]**
Suppresses the error message if the partition specified does not exist.
**PARTITION (partition\$1spec)**
Each `partition_spec` specifies a column name/value combination in the form `partition_col_name = partition_col_value [,...]`.
## Examples
```
ALTER TABLE orders
DROP PARTITION (dt = '2014-05-14', country = 'IN');
```
```
ALTER TABLE orders
DROP PARTITION (dt = '2014-05-14', country = 'IN'), PARTITION (dt = '2014-05-15', country = 'IN');
```
## Notes
The `ALTER TABLE DROP PARTITION` statement does not provide a single syntax for dropping all partitions at once or support filtering criteria to specify a range of partitions to drop.
As a workaround, you can use the AWS Glue API [GetPartitions](https://docs.aws.amazon.com/glue/latest/dg/aws-glue-api-catalog-partitions.html#aws-glue-api-catalog-partitions-GetPartitions) and [BatchDeletePartition](https://docs.aws.amazon.com/glue/latest/dg/aws-glue-api-catalog-partitions.html#aws-glue-api-catalog-partitions-BatchDeletePartition) actions in scripting. The `GetPartitions` action supports complex filter expressions like those in a SQL `WHERE` expression. After you use `GetPartitions` to create a filtered list of partitions to delete, you can use the `BatchDeletePartition` action to delete the partitions in batches of 25.
# ALTER TABLE RENAME PARTITION
Renames a partition value.
**Note**
ALTER TABLE RENAME PARTITION does not rename partition columns. To change a partition column name, you can use the AWS Glue console. For more information, see [Renaming a partition column in AWS Glue](#alter-table-rename-partition-column-name) later in this document.
## Synopsis
For the table named `table_name`, renames the partition value specified by `partition_spec` to the value specified by `new_partition_spec`.
```
ALTER TABLE table_name PARTITION (partition_spec) RENAME TO PARTITION (new_partition_spec)
```
## Parameters
**PARTITION (partition\$1spec)**
Each `partition_spec` specifies a column name/value combination in the form `partition_col_name = partition_col_value [,...]`.
## Examples
```
ALTER TABLE orders
PARTITION (dt = '2014-05-14', country = 'IN') RENAME TO PARTITION (dt = '2014-05-15', country = 'IN');
```
## Renaming a partition column in AWS Glue
Use the following procedure to rename partition column names in the AWS Glue console.
**To rename a table partition column in the AWS Glue console**
1. Sign in to the AWS Management Console and open the AWS Glue console at [https://console.aws.amazon.com/glue/](https://console.aws.amazon.com/glue/).
1. In the navigation pane, choose **Tables**.
1. On the **Tables** page, use the **Filter tables** search box to find the table that you want to change.
1. In the **Name** column, choose the link of the table that you want to change.
1. On the details page for the table, in the **Schema** section, do one of the following:
+ To make the name change in JSON format, choose **Edit schema as JSON**.
+ To change the name directly, choose **Edit schema**. This procedure chooses **Edit schema**.
1. Select the check box for the partitioned column that you want to rename, and then choose **Edit**.
1. In the **Edit schema entry** dialog box, for **Name**, enter the new name for the partition column.
1. Choose **Save as new table version**. This action updates the partition column name and preserves the schema evolution history without creating a separate physical copy of your data.
1. To compare table versions, on the details page for the table, choose **Actions**, and then choose **Compare versions**.
## Additional resources
For more information about partitioning, see [Partition your data](partitions.md).
# ALTER TABLE REPLACE COLUMNS
Removes all existing columns from a table created with the [LazySimpleSerDe](lazy-simple-serde.md) and replaces them with the set of columns specified. When the optional `PARTITION` syntax is used, updates partition metadata. You can also use `ALTER TABLE REPLACE COLUMNS` to drop columns by specifying only the columns that you want to keep.
## Synopsis
```
ALTER TABLE table_name
[PARTITION
(partition_col1_name = partition_col1_value
[,partition_col2_name = partition_col2_value][,...])]
REPLACE COLUMNS (col_name data_type [, col_name data_type, ...])
```
## Parameters
**PARTITION (partition\$1col\$1name = partition\$1col\$1value [,...])**
Specifies a partition with the column name/value combinations that you specify. Enclose `partition_col_value` in quotation marks only if the data type of the column is a string.
**REPLACE COLUMNS (col\$1name data\$1type [,col\$1name data\$1type,...])**
Replaces existing columns with the column names and datatypes specified.
## Notes
+ To see the change in table columns in the Athena Query Editor navigation pane after you run `ALTER TABLE REPLACE COLUMNS`, you might have to manually refresh the table list in the editor, and then expand the table again.
+ `ALTER TABLE REPLACE COLUMNS` does not work for columns with the `date` datatype. To workaround this issue, use the `timestamp` datatype in the table instead.
+ Note that even if you are replacing just a single column, the syntax must be `ALTER TABLE table-name REPLACE COLUMNS`, with *columns* in the plural. You must specify not only the column that you want to replace, but the columns that you want to keep – if not, the columns that you do not specify will be dropped. This syntax and behavior derives from Apache Hive DDL. For reference, see [Add/Replace columns](https://cwiki.apache.org/confluence/display/Hive/LanguageManual+DDL#LanguageManualDDL-Add/ReplaceColumns) in the Apache documentation.
## Example
In the following example, the table `names_cities`, which was created using the [LazySimpleSerDe](lazy-simple-serde.md), has three columns named `col1`, `col2`, and `col3`. All columns are of type `string`. To show the columns in the table, the following command uses the [SHOW COLUMNS](show-columns.md) statement.
```
SHOW COLUMNS IN names_cities
```
Result of the query:
```
col1
col2
col3
```
The following `ALTER TABLE REPLACE COLUMNS` command replaces the column names with `first_name`, `last_name`, and `city`. The underlying source data is not affected.
```
ALTER TABLE names_cities
REPLACE COLUMNS (first_name string, last_name string, city string)
```
To test the result, `SHOW COLUMNS` is run again.
```
SHOW COLUMNS IN names_cities
```
Result of the query:
```
first_name
last_name
city
```
Another way to show the new column names is to [preview the table](creating-tables-showing-table-information.md) in the Athena Query Editor or run your own `SELECT` query.
# ALTER TABLE SET LOCATION
Changes the location for the table named `table_name`, and optionally a partition with `partition_spec`.
## Synopsis
```
ALTER TABLE table_name [ PARTITION (partition_spec) ] SET LOCATION 'new location'
```
## Parameters
**PARTITION (partition\$1spec)**
Specifies the partition with parameters `partition_spec` whose location you want to change. The `partition_spec` specifies a column name/value combination in the form `partition_col_name = partition_col_value`.
**SET LOCATION 'new location'**
Specifies the new location, which must be an Amazon S3 location. For information about syntax, see [Table Location in Amazon S3](tables-location-format.md).
## Examples
```
ALTER TABLE customers PARTITION (zip='98040', state='WA') SET LOCATION 's3://amzn-s3-demo-bucket/custdata/';
```
# ALTER TABLE SET TBLPROPERTIES
Adds custom or predefined metadata properties to a table and sets their assigned values. To see the properties in a table, use the [SHOW TBLPROPERTIES](show-tblproperties.md) command.
Apache Hive [Managed tables](https://cwiki.apache.org/confluence/display/Hive/Managed+vs.+External+Tables) are not supported, so setting `'EXTERNAL'='FALSE'` has no effect.
## Synopsis
```
ALTER TABLE table_name SET TBLPROPERTIES ('property_name' = 'property_value' [ , ... ])
```
## Parameters
**SET TBLPROPERTIES ('property\$1name' = 'property\$1value' [ , ... ])**
Specifies the metadata properties to add as `property_name` and the value for each as `property value`. If `property_name` already exists, its value is set to the newly specified `property_value`.
The following predefined table properties have special uses.
****
[\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/athena/latest/ug/alter-table-set-tblproperties.html)
## Examples
The following example adds a comment note to table properties.
```
ALTER TABLE orders
SET TBLPROPERTIES ('notes'="Please don't drop this table.");
```
The following example modifies the table `existing_table` to use Parquet file format with ZSTD compression and ZSTD compression level 4.
```
ALTER TABLE existing_table
SET TBLPROPERTIES ('parquet.compression' = 'ZSTD', 'compression_level' = 4)
```
# ALTER VIEW DIALECT
Adds or drops an engine dialect from a AWS Glue Data Catalog view. Applies to AWS Glue Data Catalog views only. Requires `Lake Formation` admin or definer permissions.
For more information about AWS Glue Data Catalog views, see [Use Data Catalog views in Athena](views-glue.md).
## Syntax
```
ALTER VIEW name [ FORCE ] [ ADD|UPDATE ] DIALECT AS query
```
```
ALTER VIEW name [ DROP ] DIALECT
```
**FORCE**
The `FORCE` keyword causes conflicting engine dialect information in a view to be overwritten with the new definition. The `FORCE` keyword is useful when an update to a Data Catalog view results in conflicting view definitions across existing engine dialects. Suppose a Data Catalog view has both the Athena and Amazon Redshift dialects and the update results in a conflict with Amazon Redshift in the view definition. In this case, you can use the `FORCE` keyword to allow the update to complete and mark the Amazon Redshift dialect as stale. When engines marked as stale query the view, the query fails. The engines throw an exception to disallow stale results. To correct this, update the stale dialects in the view.
**ADD**
Adds a new engine dialect to the Data Catalog view. The engine specified must not already exist in the Data Catalog view.
**UPDATE**
Updates an engine dialect that already exists in the Data Catalog view.
**DROP**
Drops an existing engine dialect from a Data Catalog view. After you drop an engine from a Data Catalog view, the Data Catalog view cannot be queried by the engine that was dropped. Other engine dialects in the view can still query the view.
**DIALECT AS**
Introduces an engine-specific SQL query.
## Examples
```
ALTER VIEW orders_by_date FORCE ADD DIALECT
AS
SELECT orderdate, sum(totalprice) AS price
FROM orders
GROUP BY orderdate
```
```
ALTER VIEW orders_by_date FORCE UPDATE DIALECT
AS
SELECT orderdate, sum(totalprice) AS price
FROM orders
GROUP BY orderdate
```
```
ALTER VIEW orders_by_date DROP DIALECT
```
# CREATE DATABASE
Creates a database. The use of `DATABASE` and `SCHEMA` is interchangeable. They mean the same thing.
**Note**
For an example of creating a database, creating a table, and running a `SELECT` query on the table in Athena, see [Get started](getting-started.md).
## Synopsis
```
CREATE {DATABASE|SCHEMA} [IF NOT EXISTS] database_name
[COMMENT 'database_comment']
[LOCATION 'S3_loc']
[WITH DBPROPERTIES ('property_name' = 'property_value') [, ...]]
```
For restrictions on database names in Athena, see [Name databases, tables, and columns](tables-databases-columns-names.md).
## Parameters
**[IF NOT EXISTS]**
Causes the error to be suppressed if a database named `database_name` already exists.
**[COMMENT database\$1comment]**
Establishes the metadata value for the built-in metadata property named `comment` and the value you provide for `database_comment`. In AWS Glue, the `COMMENT` contents are written to the `Description` field of the database properties.
**[LOCATION S3\$1loc]**
Specifies the location where database files and metastore will exist as `S3_loc`. The location must be an Amazon S3 location.
**[WITH DBPROPERTIES ('property\$1name' = 'property\$1value') [, ...] ]**
Allows you to specify custom metadata properties for the database definition.
## Examples
```
CREATE DATABASE clickstreams;
```
```
CREATE DATABASE IF NOT EXISTS clickstreams
COMMENT 'Site Foo clickstream data aggregates'
LOCATION 's3://amzn-s3-demo-bucket/clickstreams/'
WITH DBPROPERTIES ('creator'='Jane D.', 'Dept.'='Marketing analytics');
```
## Viewing database properties
To view the database properties for a database that you create in AWSDataCatalog using `CREATE DATABASE`, you can use the AWS CLI command [https://awscli.amazonaws.com/v2/documentation/api/latest/reference/glue/get-database.html](https://awscli.amazonaws.com/v2/documentation/api/latest/reference/glue/get-database.html), as in the following example:
```
aws glue get-database --name
```
In JSON output, the result looks like the following:
```
{
"Database": {
"Name": "",
"Description": "",
"LocationUri": "s3://amzn-s3-demo-bucket",
"Parameters": {
"": ""
},
"CreateTime": 1603383451.0,
"CreateTableDefaultPermissions": [
{
"Principal": {
"DataLakePrincipalIdentifier": "IAM_ALLOWED_PRINCIPALS"
},
"Permissions": [
"ALL"
]
}
]
}
}
```
For more information about the AWS CLI, see the [AWS Command Line Interface User Guide](https://docs.aws.amazon.com/cli/latest/userguide/).
# CREATE TABLE
Creates a table with the name and the parameters that you specify.
**Note**
This page contains summary reference information. For more information about creating tables in Athena and an example `CREATE TABLE` statement, see [Create tables in Athena](creating-tables.md). For an example of creating a database, creating a table, and running a `SELECT` query on the table in Athena, see [Get started](getting-started.md).
## Synopsis
```
CREATE EXTERNAL TABLE [IF NOT EXISTS]
[db_name.]table_name [(col_name data_type [COMMENT col_comment] [, ...] )]
[COMMENT table_comment]
[PARTITIONED BY (col_name data_type [COMMENT col_comment], ...)]
[CLUSTERED BY (col_name, col_name, ...) INTO num_buckets BUCKETS]
[ROW FORMAT row_format]
[STORED AS file_format]
[WITH SERDEPROPERTIES (...)]
[LOCATION 's3://amzn-s3-demo-bucket/[folder]/']
[TBLPROPERTIES ( ['has_encrypted_data'='true | false',] ['encryption_option'='SSE_S3 | SSE_KMS | CSE_KMS',] ['kms_key'='aws_kms_key_arn',] ['classification'='aws_glue_classification',] property_name=property_value [, ...] ) ]
```
## Parameters
**EXTERNAL**
Specifies that the table is based on an underlying data file that exists in Amazon S3, in the `LOCATION` that you specify. Except when creating [Iceberg](querying-iceberg-creating-tables.md) tables, always use the `EXTERNAL` keyword. If you use `CREATE TABLE` without the `EXTERNAL` keyword for non-Iceberg tables, Athena issues an error. When you create an external table, the data referenced must comply with the default format or the format that you specify with the `ROW FORMAT`, `STORED AS`, and `WITH SERDEPROPERTIES` clauses.
**[IF NOT EXISTS]**
This parameter checks if a table with the same name already exists. If it does, the parameter returns `TRUE`, and Amazon Athena cancels the `CREATE TABLE` action. Because cancellation occurs before Athena calls the data catalog, it doesn't emit a AWS CloudTrail event.
**[db\$1name.]table\$1name**
Specifies a name for the table to be created. The optional `db_name` parameter specifies the database where the table exists. If omitted, the current database is assumed. If the table name includes numbers, enclose `table_name` in quotation marks, for example `"table123"`. If `table_name` begins with an underscore, use backticks, for example, ``_mytable``. Special characters (other than underscore) are not supported.
Athena table names are case-insensitive; however, if you work with Apache Spark, Spark requires lowercase table names. For restrictions on table names in Athena, see [Name databases, tables, and columns](tables-databases-columns-names.md).
**[ ( col\$1name data\$1type [COMMENT col\$1comment] [, ...] ) ]**
Specifies the name for each column to be created, along with the column's data type. Column names do not allow special characters other than underscore `(_)`. If `col_name` begins with an underscore, enclose the column name in backticks, for example ``_mycolumn``. For restrictions on column names in Athena, see [Name databases, tables, and columns](tables-databases-columns-names.md).
The `data_type` value can be any of the following:
+ `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` – In Data Definition Language (DDL) queries like `CREATE TABLE`, use the `int` keyword to represent an integer. In other queries, use the keyword `integer`, 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.
+ `double` – A 64-bit signed double-precision floating point number. The range is 4.94065645841246544e-324d to 1.79769313486231570e\$1308d, positive or negative. `double` follows the IEEE Standard for Floating-Point Arithmetic (IEEE 754).
+ `float` – A 32-bit signed single-precision floating point number. The range is 1.40129846432481707e-45 to 3.40282346638528860e\$138, positive or negative. `float` follows the IEEE Standard for Floating-Point Arithmetic (IEEE 754). Equivalent to the `real` in Presto. In Athena, use `float` in DDL statements like `CREATE TABLE` and `real` in SQL functions like `SELECT CAST`. The AWS Glue crawler returns values in `float`, and Athena translates `real` and `float` types internally (see the [June 5, 2018](release-notes.md#release-note-2018-06-05) release notes).
+ `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)`. The maximum value for *precision* is 38, and the maximum value for *scale* is 38.
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)`. For more information, see [CHAR Hive data type](https://cwiki.apache.org/confluence/display/Hive/LanguageManual+Types#LanguageManualTypes-char).
+ `varchar` – Variable length character data, with a specified length between 1 and 65535, such as `varchar(10)`. For more information, see [VARCHAR Hive data type](https://cwiki.apache.org/confluence/display/Hive/LanguageManual+Types#LanguageManualTypes-varchar).
+ `string` – A string literal enclosed in single or double quotes.
**Note**
Non-string data types cannot be cast to `string` in Athena; cast them to `varchar` instead.
+ `binary` – (for data in Parquet)
+ `date` – A date in ISO format, such as `YYYY-MM-DD`. For example, `date '2008-09-15'`. An exception is the OpenCSVSerDe, which uses the number of days elapsed since January 1, 1970. For more information, see [Open CSV SerDe for processing CSV](csv-serde.md).
+ `timestamp` – Date and time instant in a [https://docs.oracle.com/javase/8/docs/api/java/sql/Timestamp.html](https://docs.oracle.com/javase/8/docs/api/java/sql/Timestamp.html) compatible format up to a maximum resolution of milliseconds, such as `yyyy-MM-dd HH:mm:ss[.f...]`. For example, `timestamp '2008-09-15 03:04:05.324'`. An exception is the OpenCSVSerDe, which uses `TIMESTAMP` data in the UNIX numeric format (for example, `1579059880000`). For more information, see [Open CSV SerDe for processing CSV](csv-serde.md).
+ `array` < data\$1type >
+ `map` < primitive\$1type, data\$1type >
+ `struct` < col\$1name : data\$1type [comment col\$1comment] [, ...] >
**[COMMENT table\$1comment]**
Creates the `comment` table property and populates it with the `table_comment` you specify.
**[PARTITIONED BY (col\$1name data\$1type [ COMMENT col\$1comment ], ... ) ]**
Creates a partitioned table with one or more partition columns that have the `col_name`, `data_type` and `col_comment` specified. A table can have one or more partitions, which consist of a distinct column name and value combination. A separate data directory is created for each specified combination, which can improve query performance in some circumstances. Partitioned columns don't exist within the table data itself. If you use a value for `col_name` that is the same as a table column, you get an error. For more information, see [Partitioning Data](partitions.md).
After you create a table with partitions, run a subsequent query that consists of the [MSCK REPAIR TABLE](msck-repair-table.md) clause to refresh partition metadata, for example, `MSCK REPAIR TABLE cloudfront_logs;`. For partitions that are not Hive compatible, use [ALTER TABLE ADD PARTITION](alter-table-add-partition.md) to load the partitions so that you can query the data.
**[CLUSTERED BY (col\$1name, col\$1name, ...) INTO num\$1buckets BUCKETS]**
Divides, with or without partitioning, the data in the specified `col_name` columns into data subsets called *buckets*. The `num_buckets` parameter specifies the number of buckets to create. Bucketing can improve the performance of some queries on large data sets.
**[ROW FORMAT row\$1format]**
Specifies the row format of the table and its underlying source data if applicable. For `row_format`, you can specify one or more delimiters with the `DELIMITED` clause or, alternatively, use the `SERDE` clause as described below. If `ROW FORMAT` is omitted or `ROW FORMAT DELIMITED` is specified, a native SerDe is used.
+ [DELIMITED FIELDS TERMINATED BY char [ESCAPED BY char]]
+ [DELIMITED COLLECTION ITEMS TERMINATED BY char]
+ [MAP KEYS TERMINATED BY char]
+ [LINES TERMINATED BY char]
+ [NULL DEFINED AS char]
Available only with Hive 0.13 and when the STORED AS file format is `TEXTFILE`.
**--OR--**
+ SERDE 'serde\$1name' [WITH SERDEPROPERTIES ("property\$1name" = "property\$1value", "property\$1name" = "property\$1value" [, ...] )]
The `serde_name` indicates the SerDe to use. The `WITH SERDEPROPERTIES` clause allows you to provide one or more custom properties allowed by the SerDe.
**[STORED AS file\$1format]**
Specifies the file format for table data. If omitted, `TEXTFILE` is the default. Options for `file_format` are:
+ SEQUENCEFILE
+ TEXTFILE
+ RCFILE
+ ORC
+ PARQUET
+ AVRO
+ ION
+ INPUTFORMAT input\$1format\$1classname OUTPUTFORMAT output\$1format\$1classname
**[LOCATION 's3://amzn-s3-demo-bucket/[folder]/']**
Specifies the location of the underlying data in Amazon S3 from which the table is created. The location path must be a bucket name or a bucket name and one or more folders. If you are using partitions, specify the root of the partitioned data. For more information about table location, see [Specify a table location in Amazon S3](tables-location-format.md). For information about data format and permissions, see [Amazon S3 considerations](creating-tables.md#s3-considerations).
Use a trailing slash for your folder or bucket. Do not use file names or glob characters.
**Use:**
`s3://amzn-s3-demo-bucket/`
`s3://amzn-s3-demo-bucket/folder/`
`s3://amzn-s3-demo-bucket/folder/anotherfolder/`
**Don't use:**
`s3://amzn-s3-demo-bucket`
`s3://amzn-s3-demo-bucket/*`
`s3://amzn-s3-demo-bucket/mydatafile.dat`
**[TBLPROPERTIES ( ['has\$1encrypted\$1data'='true \$1 false',] ['encryption\$1option'='SSE\$1S3 \$1 SSE\$1KMS \$1 CSE\$1KMS',] ['kms\$1key'='aws\$1kms\$1key\$1arn',] ['classification'='classification\$1value',] property\$1name=property\$1value [, ...] ) ]**
Specifies custom metadata key-value pairs for the table definition in addition to predefined table properties, such as `"comment"`.
**has\$1encrypted\$1data** – Athena has a built-in property, `has_encrypted_data`. Set this property to `true` to indicate that the underlying dataset specified by `LOCATION` is CSE-KMS encrypted. If omitted and if the workgroup's settings do not override client-side settings, `false` is assumed. If omitted or set to `false` when underlying data is encrypted, the query results in an error. For more information, see [Encryption at rest](encryption.md).
**encryption\$1option** – Set this property to either `SSE_S3`, `SSE_KMS`, or `CSE_KMS` to indicate the highest level of encryption used in the underlying dataset specified by `LOCATION`. For more information, see [Encryption at rest](encryption.md).
**kms\$1key** – Set this property to the AWS KMS key ARN used for encrypting and decrypting table data files. Athena uses this key to encrypt table data files when writing with `SSE_KMS` or `CSE_KMS` encryption, and to decrypt CSE-KMS encrypted table data files. This property is only required when `encryption_option` is set to `SSE_KMS` or `CSE_KMS`. For more information, see [Encryption at rest](encryption.md).
**classification** – Tables created for Athena in the CloudTrail console add `cloudtrail` as a value for the `classification` property. To run ETL jobs, AWS Glue requires that you create a table with the `classification` property to indicate the data type for AWS Glue as `csv`, `parquet`, `orc`, `avro`, or `json`. For example, `'classification'='csv'`. ETL jobs will fail if you do not specify this property. You can subsequently specify it using the AWS Glue console, API, or CLI. For more information, see [Create tables for ETL jobs](schema-classifier.md) and [Authoring Jobs in AWS Glue](https://docs.aws.amazon.com/glue/latest/dg/author-job.html) in the *AWS Glue Developer Guide*.
**compression\$1level** – The `compression_level` property specifies the compression level to use. This property applies only to ZSTD compression. Possible values are from 1 to 22. The default value is 3. For more information, see [Use ZSTD compression levels](compression-support-zstd-levels.md).
For more information about other table properties, see [ALTER TABLE SET TBLPROPERTIES](alter-table-set-tblproperties.md).
## Examples
The following example `CREATE TABLE` statement creates a table based on tab-separated planet data stored in Amazon S3.
```
CREATE EXTERNAL TABLE planet_data (
planet_name string,
order_from_sun int,
au_to_sun float,
mass float,
gravity_earth float,
orbit_years float,
day_length float
)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '\t'
STORED AS TEXTFILE
LOCATION 's3://amzn-s3-demo-bucket/tsv/'
```
Note the following points:
+ The `ROW FORMAT DELIMITED` clause indicates that the data is delimited by a specific character.
+ The `FIELDS TERMINATED BY '\t'` clause specifies that the fields in the TSV data are separated by the tab character ('\$1t').
+ The `STORED AS TEXTFILE` clause indicates that the data is stored as plain text files in Amazon S3.
To query the data, you could use a simple `SELECT` statement like the following:
```
SELECT * FROM planet_data
```
To use the example to create your own TSV table in Athena, replace the table and column names with the names and data types of your own table and columns, and update the `LOCATION` clause to point to the Amazon S3 path where your TSV files are stored.
For more information about creating tables, see [Create tables in Athena](creating-tables.md).
# CREATE TABLE AS
Creates a new table populated with the results of a [SELECT](select.md) query. To create an empty table, use [CREATE TABLE](create-table.md). `CREATE TABLE AS` combines a `CREATE TABLE` DDL statement with a `SELECT` DML statement and therefore technically contains both DDL and DML. Note that although `CREATE TABLE AS` is grouped here with other DDL statements, CTAS queries in Athena are treated as DML for Service Quotas purposes. For information about Service Quotas in Athena, see [Service Quotas](service-limits.md).
**Note**
For CTAS statements, the expected bucket owner setting does not apply to the destination table location in Amazon S3. The expected bucket owner setting applies only to the Amazon S3 output location that you specify for Athena query results. For more information, see [Specify a query result location using the Athena console](query-results-specify-location-console.md).
For additional information about `CREATE TABLE AS` that is beyond the scope of this reference topic, see [Create a table from query results (CTAS)](ctas.md).
**Topics**
+ [Synopsis](#synopsis)
+ [CTAS table properties](#ctas-table-properties)
+ [Examples](#ctas-table-examples)
## Synopsis
```
CREATE TABLE table_name
[ WITH ( property_name = expression [, ...] ) ]
AS query
[ WITH [ NO ] DATA ]
```
Where:
**WITH ( property\$1name = expression [, ...] )**
A list of optional CTAS table properties, some of which are specific to the data storage format. See [CTAS table properties](#ctas-table-properties).
**query**
A [SELECT](select.md) query that is used to create a new table.
If you plan to create a query with partitions, specify the names of partitioned columns last in the list of columns in the `SELECT` statement.
**[ WITH [ NO ] DATA ]**
If `WITH NO DATA` is used, a new empty table with the same schema as the original table is created.
**Note**
To include column headers in your query result output, you can use a simple `SELECT` query instead of a CTAS query. You can retrieve the results from your query results location or download the results directly using the Athena console. For more information, see [Work with query results and recent queries](querying.md).
## CTAS table properties
Each CTAS table in Athena has a list of optional CTAS table properties that you specify using `WITH (property_name = expression [, ...] )`. For information about using these parameters, see [Examples of CTAS queries](ctas-examples.md).
** `WITH (property_name = expression [, ...], )` **
`table_type = ['HIVE', 'ICEBERG']`
Optional. The default is `HIVE`. Specifies the table type of the resulting table
Example:
```
WITH (table_type ='ICEBERG')
```
`external_location = [location]`
Because Iceberg tables are not external, this property does not apply to Iceberg tables. To define the root location of an Iceberg table in a CTAS statement, use the `location` property described later in this section.
Optional. The location where Athena saves your CTAS query in Amazon S3.
Example:
```
WITH (external_location ='s3://amzn-s3-demo-bucket/tables/parquet_table/')
```
Athena does not use the same path for query results twice. If you specify the location manually, make sure that the Amazon S3 location that you specify has no data. Athena never attempts to delete your data. If you want to use the same location again, manually delete the data, or your CTAS query will fail.
If you run a CTAS query that specifies an `external_location` in a workgroup that [enforces a query results location](workgroups-settings-override.md), the query fails with an error message. To see the query results location specified for the workgroup, [see the workgroup's details](viewing-details-workgroups.md).
If your workgroup overrides the client-side setting for query results location, Athena creates your table in the following location:
```
s3://amzn-s3-demo-bucket/tables/query-id/
```
If you do not use the `external_location` property to specify a location and your workgroup does not override client-side settings, Athena uses your [client-side setting](query-results-specify-location-console.md) for the query results location to create your table in the following location:
```
s3://amzn-s3-demo-bucket/Unsaved-or-query-name/year/month/date/tables/query-id/
```
`is_external = [boolean]`
Optional. Indicates if the table is an external table. The default is true. For Iceberg tables, this must be set to false.
Example:
```
WITH (is_external = false)
```
`location = [location]`
Required for Iceberg tables. Specifies the root location for the Iceberg table to be created from the query results.
Example:
```
WITH (location ='s3://amzn-s3-demo-bucket/tables/iceberg_table/')
```
`field_delimiter = [delimiter]`
Optional and specific to text-based data storage formats. The single-character field delimiter for files in CSV, TSV, and text files. For example, `WITH (field_delimiter = ',')`. Currently, multicharacter field delimiters are not supported for CTAS queries. If you don't specify a field delimiter, `\001` is used by default.
`format = [storage_format]`
The storage format for the CTAS query results, such as `ORC`, `PARQUET`, `AVRO`, `JSON`, `ION`, or `TEXTFILE`. For Iceberg tables, the allowed formats are `ORC`, `PARQUET`, and `AVRO`. If omitted, `PARQUET` is used by default. The name of this parameter, `format`, must be listed in lowercase, or your CTAS query will fail.
Example:
```
WITH (format = 'PARQUET')
```
`bucketed_by = ARRAY[ column_name[,…], bucket_count = [int] ]`
This property does not apply to Iceberg tables. For Iceberg tables, use partitioning with bucket transform.
An array list of buckets to bucket data. If omitted, Athena does not bucket your data in this query.
`bucket_count = [int]`
This property does not apply to Iceberg tables. For Iceberg tables, use partitioning with bucket transform.
The number of buckets for bucketing your data. If omitted, Athena does not bucket your data. Example:
```
CREATE TABLE bucketed_table WITH (
bucketed_by = ARRAY[column_name],
bucket_count = 30, format = 'PARQUET',
external_location ='s3://amzn-s3-demo-bucket/tables/parquet_table/'
) AS
SELECT
*
FROM
table_name
```
`partitioned_by = ARRAY[ col_name[,…] ]`
This property does not apply to Iceberg tables. To use partition transforms for Iceberg tables, use the `partitioning` property described later in this section.
Optional. An array list of columns by which the CTAS table will be partitioned. Verify that the names of partitioned columns are listed last in the list of columns in the `SELECT` statement.
`partitioning = ARRAY[partition_transform, ...]`
Optional. Specifies the partitioning of the Iceberg table to be created. Iceberg supports a wide variety of partition transforms and partition evolution. Partition transforms are summarized in the following table.
****
[\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/athena/latest/ug/create-table-as.html)
Example:
```
WITH (partitioning = ARRAY['month(order_date)',
'bucket(account_number, 10)',
'country']))
```
`optimize_rewrite_min_data_file_size_bytes = [long]`
Optional. Data optimization specific configuration. Files smaller than the specified value are included for optimization. The default is 0.75 times the value of `write_target_data_file_size_bytes`. This property applies only to Iceberg tables. For more information, see [Optimize Iceberg tables](querying-iceberg-data-optimization.md).
Example:
```
WITH (optimize_rewrite_min_data_file_size_bytes = 402653184)
```
`optimize_rewrite_max_data_file_size_bytes = [long]`
Optional. Data optimization specific configuration. Files larger than the specified value are included for optimization. The default is 1.8 times the value of `write_target_data_file_size_bytes`. This property applies only to Iceberg tables. For more information, see [Optimize Iceberg tables](querying-iceberg-data-optimization.md).
Example:
```
WITH (optimize_rewrite_max_data_file_size_bytes = 966367641)
```
`optimize_rewrite_data_file_threshold = [int]`
Optional. Data optimization specific configuration. If there are fewer data files that require optimization than the given threshold, the files are not rewritten. This allows the accumulation of more data files to produce files closer to the target size and skip unnecessary computation for cost savings. The default is 5. This property applies only to Iceberg tables. For more information, see [Optimize Iceberg tables](querying-iceberg-data-optimization.md).
Example:
```
WITH (optimize_rewrite_data_file_threshold = 5)
```
`optimize_rewrite_delete_file_threshold = [int]`
Optional. Data optimization specific configuration. If there are fewer delete files associated with a data file than the threshold, the data file is not rewritten. This allows the accumulation of more delete files for each data file for cost savings. The default is 2. This property applies only to Iceberg tables. For more information, see [Optimize Iceberg tables](querying-iceberg-data-optimization.md).
Example:
```
WITH (optimize_rewrite_delete_file_threshold = 2)
```
`vacuum_min_snapshots_to_keep = [int]`
Optional. Vacuum specific configuration. The minimum number of most recent snapshots to retain. The default is 1. This property applies only to Iceberg tables. For more information, see [VACUUM](vacuum-statement.md).
The `vacuum_min_snapshots_to_keep` property requires Athena engine version 3.
Example:
```
WITH (vacuum_min_snapshots_to_keep = 1)
```
`vacuum_max_snapshot_age_seconds = [long]`
Optional. Vacuum specific configuration. A period in seconds that represents the age of the snapshots to retain. The default is 432000 (5 days). This property applies only to Iceberg tables. For more information, see [VACUUM](vacuum-statement.md).
The `vacuum_max_snapshot_age_seconds` property requires Athena engine version 3.
Example:
```
WITH (vacuum_max_snapshot_age_seconds = 432000)
```
`write_compression = [compression_format]`
The compression type to use for any storage format that allows compression to be specified. The `compression_format` value specifies the compression to be used when the data is written to the table. You can specify compression for the `TEXTFILE`, `JSON`, `PARQUET`, and `ORC` file formats.
For example, if the `format` property specifies `PARQUET` as the storage format, the value for `write_compression` specifies the compression format for Parquet. In this case, specifying a value for `write_compression` is equivalent to specifying a value for `parquet_compression`.
Similarly, if the `format` property specifies `ORC` as the storage format, the value for `write_compression` specifies the compression format for ORC. In this case, specifying a value for `write_compression` is equivalent to specifying a value for `orc_compression`.
Multiple compression format table properties cannot be specified in the same CTAS query. For example, you cannot specify both `write_compression` and `parquet_compression` in the same query. The same applies for `write_compression` and `orc_compression`. For information about the compression types that are supported for each file format, see [Use compression in Athena](compression-formats.md).
`orc_compression = [compression_format]`
The compression type to use for the `ORC` file format when `ORC` data is written to the table. For example, `WITH (orc_compression = 'ZLIB')`. Chunks within the `ORC` file (except the `ORC` Postscript) are compressed using the compression that you specify. If omitted, ZLIB compression is used by default for `ORC`.
For consistency, we recommend that you use the `write_compression` property instead of `orc_compression`. Use the `format` property to specify the storage format as `ORC`, and then use the `write_compression` property to specify the compression format that `ORC` will use.
`parquet_compression = [compression_format]`
The compression type to use for the Parquet file format when Parquet data is written to the table. For example, `WITH (parquet_compression = 'SNAPPY')`. This compression is applied to column chunks within the Parquet files. If omitted, GZIP compression is used by default for Parquet.
For consistency, we recommend that you use the `write_compression` property instead of `parquet_compression`. Use the `format` property to specify the storage format as `PARQUET`, and then use the `write_compression` property to specify the compression format that `PARQUET` will use.
`compression_level = [compression_level]`
The compression level to use. This property applies only to ZSTD compression. Possible values are from 1 to 22. The default value is 3. For more information, see [Use ZSTD compression levels](compression-support-zstd-levels.md).
## Examples
For examples of CTAS queries, consult the following resources.
+ [Examples of CTAS queries](ctas-examples.md)
+ [Use CTAS and INSERT INTO for ETL and data analysis](ctas-insert-into-etl.md)
+ [Use CTAS statements with Amazon Athena to reduce cost and improve performance](https://aws.amazon.com/blogs/big-data/using-ctas-statements-with-amazon-athena-to-reduce-cost-and-improve-performance/)
+ [Use CTAS and INSERT INTO to work around the 100 partition limit](ctas-insert-into.md)
# CREATE VIEW and CREATE PROTECTED MULTI DIALECT VIEW
A view is a logical table that can be referenced by future queries. Views do not contain any data and do not write data. Instead, the query specified by the view runs each time you reference the view by another query.
+ `CREATE VIEW` creates an Athena view from a specified `SELECT` query. Athena views work within Athena. For more information about Athena views, see [Work with views](views.md).
+ `CREATE PROTECTED MULTI DIALECT VIEW` creates a AWS Glue Data Catalog view in the AWS Glue Data Catalog. AWS Glue Data Catalog views provide a single common view across AWS services like Amazon Athena and Amazon Redshift. For more information about AWS Glue Data Catalog views, see [Use Data Catalog views in Athena](views-glue.md).
## CREATE VIEW
Creates a view for use within Athena.
### Synopsis
```
CREATE [ OR REPLACE ] VIEW view_name AS query
```
The optional `OR REPLACE` clause lets you update the existing view by replacing it. For more information, see [Create views](views-console.md#creating-views).
### Examples
To create a view `test` from the table `orders`, use a query similar to the following:
```
CREATE VIEW test AS
SELECT
orderkey,
orderstatus,
totalprice / 2 AS half
FROM orders;
```
To create a view `orders_by_date` from the table `orders`, use the following query:
```
CREATE VIEW orders_by_date AS
SELECT orderdate, sum(totalprice) AS price
FROM orders
GROUP BY orderdate;
```
To update an existing view, use an example similar to the following:
```
CREATE OR REPLACE VIEW test AS
SELECT orderkey, orderstatus, totalprice / 4 AS quarter
FROM orders;
```
For more information about using Athena views, see [Work with views](views.md).
## CREATE PROTECTED MULTI DIALECT VIEW
Creates a AWS Glue Data Catalog view in the AWS Glue Data Catalog. A Data Catalog view is a single view schema that works across Athena and other SQL engines such as Amazon Redshift and Amazon EMR.
### Syntax
```
CREATE [ OR REPLACE ] PROTECTED MULTI DIALECT VIEW view_name
SECURITY DEFINER
[ SHOW VIEW JSON ]
AS query
```
**OR REPLACE**
(Optional) Updates the existing view by replacing it. A Data Catalog view cannot be replaced if SQL dialects from other engines are present in the view. If the calling engine has the only SQL dialect present in the view, the view can be replaced.
**PROTECTED**
Required keyword. Specifies that the view is protected against data leaks. Data Catalog views can only be created as a `PROTECTED` view.
**MULTI DIALECT**
Specifies that the view supports the SQL dialects of different query engines and can therefore be read by those engines.
**SECURITY DEFINER**
Specifies that definer semantics are in force for this view. Definer semantics mean that the effective read permissions on the underlying tables belong to the principal or role that defined the view rather than the principal that performs the actual read.
**SHOW VIEW JSON**
(Optional) Returns the JSON for the Data Catalog view specification without actually creating a view. This "dry-run" option is useful when you want to validate the SQL for the view and return the table metadata that AWS Glue will use.
### Example
The following example creates the `orders_by_date` Data Catalog view based on a query on the `orders` table.
```
CREATE PROTECTED MULTI DIALECT VIEW orders_by_date
SECURITY DEFINER
AS
SELECT orderdate, sum(totalprice) AS price
FROM orders
WHERE order_city = 'SEATTLE'
GROUP BY orderdate
```
For more information about using AWS Glue Data Catalog views, see [Use Data Catalog views in Athena](views-glue.md).
# DESCRIBE
Shows one or more columns, including partition columns, for the specified table. This command is useful for examining the attributes of complex columns.
## Synopsis
```
DESCRIBE [EXTENDED | FORMATTED] [db_name.]table_name [PARTITION partition_spec] [col_name ( [.field_name] | [.'$elem$'] | [.'$key$'] | [.'$value$'] )]
```
**Important**
The syntax for this statement is `DESCRIBE table_name`, not `DESCRIBE TABLE table_name`. Using the latter syntax results in the error message FAILED: SemanticException [Error 10001]: Table not found table.
## Parameters
**[EXTENDED \$1 FORMATTED]**
Determines the format of the output. Omitting these parameters shows column names and their corresponding data types, including partition columns, in tabular format. Specifying `FORMATTED` not only shows column names and data types in tabular format, but also detailed table and storage information. `EXTENDED` shows column and data type information in tabular format, and detailed metadata for the table in Thrift serialized form. This format is less readable and is useful primarily for debugging.
**[PARTITION partition\$1spec]**
If included, lists the metadata for the partition specified by `partition_spec`, where `partition_spec` is in the format `(partition_column = partition_col_value, partition_column = partition_col_value, ...)`.
**[col\$1name ( [.field\$1name] \$1 [.'\$1elem\$1'] \$1 [.'\$1key\$1'] \$1 [.'\$1value\$1'] )\$1 ]**
Specifies the column and attributes to examine. You can specify `.field_name` for an element of a struct, `'$elem$'` for array element, `'$key$'` for a map key, and `'$value$'` for map value. You can specify this recursively to further explore the complex column.
### Examples
```
DESCRIBE orders
```
```
DESCRIBE FORMATTED mydatabase.mytable PARTITION (part_col = 100) columnA;
```
The following query and output shows column and data type information from an `impressions` table based on Amazon EMR sample data.
```
DESCRIBE impressions
```
```
requestbegintime string from deserializer
adid string from deserializer
impressionid string from deserializer
referrer string from deserializer
useragent string from deserializer
usercookie string from deserializer
ip string from deserializer
number string from deserializer
processid string from deserializer
browsercokie string from deserializer
requestendtime string from deserializer
timers struct from deserializer
threadid string from deserializer
hostname string from deserializer
sessionid string from deserializer
dt string
# Partition Information
# col_name data_type comment
dt string
```
The following example query and output show the result for the same table when the `FORMATTED` option is used.
```
DESCRIBE FORMATTED impressions
```
```
requestbegintime string from deserializer
adid string from deserializer
impressionid string from deserializer
referrer string from deserializer
useragent string from deserializer
usercookie string from deserializer
ip string from deserializer
number string from deserializer
processid string from deserializer
browsercokie string from deserializer
requestendtime string from deserializer
timers struct from deserializer
threadid string from deserializer
hostname string from deserializer
sessionid string from deserializer
dt string
# Partition Information
# col_name data_type comment
dt string
# Detailed Table Information
Database: sampledb
Owner: hadoop
CreateTime: Thu Apr 23 02:55:21 UTC 2020
LastAccessTime: UNKNOWN
Protect Mode: None
Retention: 0
Location: s3://us-east-1.elasticmapreduce/samples/hive-ads/tables/impressions
Table Type: EXTERNAL_TABLE
Table Parameters:
EXTERNAL TRUE
transient_lastDdlTime 1587610521
# Storage Information
SerDe Library: org.openx.data.jsonserde.JsonSerDe
InputFormat: org.apache.hadoop.mapred.TextInputFormat
OutputFormat: org.apache.hadoop.hive.ql.io.IgnoreKeyTextOutputFormat
Compressed: No
Num Buckets: -1
Bucket Columns: []
Sort Columns: []
Storage Desc Params:
paths requestbegintime, adid, impressionid, referrer, useragent, usercookie, ip
serialization.format 1
```
The following example query and output show the result for the same table when the `EXTENDED` option is used. The detailed table information is output on a single line, but is formatted here for readability.
```
DESCRIBE EXTENDED impressions
```
```
requestbegintime string from deserializer
adid string from deserializer
impressionid string from deserializer
referrer string from deserializer
useragent string from deserializer
usercookie string from deserializer
ip string from deserializer
number string from deserializer
processid string from deserializer
browsercokie string from deserializer
requestendtime string from deserializer
timers struct from deserializer
threadid string from deserializer
hostname string from deserializer
sessionid string from deserializer
dt string
# Partition Information
# col_name data_type comment
dt string
Detailed Table Information Table(tableName:impressions, dbName:sampledb, owner:hadoop, createTime:1587610521,
lastAccessTime:0, retention:0, sd:StorageDescriptor(cols:[FieldSchema(name:requestbegintime, type:string, comment:null),
FieldSchema(name:adid, type:string, comment:null), FieldSchema(name:impressionid, type:string, comment:null),
FieldSchema(name:referrer, type:string, comment:null), FieldSchema(name:useragent, type:string, comment:null),
FieldSchema(name:usercookie, type:string, comment:null), FieldSchema(name:ip, type:string, comment:null),
FieldSchema(name:number, type:string, comment:null), FieldSchema(name:processid, type:string, comment:null),
FieldSchema(name:browsercokie, type:string, comment:null), FieldSchema(name:requestendtime, type:string, comment:null),
FieldSchema(name:timers, type:struct, comment:null), FieldSchema(name:threadid,
type:string, comment:null), FieldSchema(name:hostname, type:string, comment:null), FieldSchema(name:sessionid,
type:string, comment:null)], location:s3://us-east-1.elasticmapreduce/samples/hive-ads/tables/impressions,
inputFormat:org.apache.hadoop.mapred.TextInputFormat,
outputFormat:org.apache.hadoop.hive.ql.io.IgnoreKeyTextOutputFormat, compressed:false, numBuckets:-1,
serdeInfo:SerDeInfo(name:null, serializationLib:org.openx.data.jsonserde.JsonSerDe, parameters:{serialization.format=1,
paths=requestbegintime, adid, impressionid, referrer, useragent, usercookie, ip}), bucketCols:[], sortCols:[], parameters:{},
skewedInfo:SkewedInfo(skewedColNames:[], skewedColValues:[], skewedColValueLocationMaps:{}),
storedAsSubDirectories:false), partitionKeys:[FieldSchema(name:dt, type:string, comment:null)],
parameters:{EXTERNAL=TRUE, transient_lastDdlTime=1587610521}, viewOriginalText:null, viewExpandedText:null,
tableType:EXTERNAL_TABLE)
```
# DESCRIBE VIEW
Shows the list of columns for the specified Athena or AWS Glue Data Catalog view. Useful for examining the attributes of a complex view.
For Data Catalog views, the output of the statement is controlled by Lake Formation access control and shows only the columns that the caller has access to.
## Synopsis
```
DESCRIBE [db_name.]view_name
```
## Example
```
DESCRIBE orders
```
See also [SHOW COLUMNS](show-columns.md), [SHOW CREATE VIEW](show-create-view.md), [SHOW VIEWS](show-views.md), and [DROP VIEW](drop-view.md).
# DROP DATABASE
Removes the named database from the catalog. If the database contains tables, you must either drop the tables before running `DROP DATABASE` or use the `CASCADE` clause. The use of `DATABASE` and `SCHEMA` are interchangeable. They mean the same thing.
## Synopsis
```
DROP {DATABASE | SCHEMA} [IF EXISTS] database_name [RESTRICT | CASCADE]
```
## Parameters
**[IF EXISTS]**
Causes the error to be suppressed if `database_name` doesn't exist.
**[RESTRICT\$1CASCADE]**
Determines how tables within `database_name` are regarded during the `DROP` operation. If you specify `RESTRICT`, the database is not dropped if it contains tables. This is the default behavior. Specifying `CASCADE` causes the database and all its tables to be dropped.
## Examples
```
DROP DATABASE clickstreams;
```
```
DROP SCHEMA IF EXISTS clickstreams CASCADE;
```
**Note**
When you try to drop a database whose name has special characters (for example, `my-database`), you may receive an error message. To resolve this issue, try enclosing the database name in back tick (`) characters. For information about naming databases in Athena, see [Name databases, tables, and columns](tables-databases-columns-names.md).
# DROP TABLE
Removes the metadata table definition for the table named `table_name`. When you drop an external table, the underlying data remains intact.
## Synopsis
```
DROP TABLE [IF EXISTS] table_name
```
## Parameters
**[ IF EXISTS ]**
Causes the error to be suppressed if `table_name` doesn't exist.
## Examples
```
DROP TABLE fulfilled_orders
```
```
DROP TABLE IF EXISTS fulfilled_orders
```
When using the Athena console query editor to drop a table that has special characters other than the underscore (\$1), use backticks, as in the following example.
```
DROP TABLE `my-athena-database-01.my-athena-table`
```
When using the JDBC connector to drop a table that has special characters, backtick characters are not required.
```
DROP TABLE my-athena-database-01.my-athena-table
```
# DROP VIEW
Drops (deletes) an existing Athena or AWS Glue Data Catalog view. The optional `IF EXISTS` clause causes the error to be suppressed if the view does not exist.
For Data Catalog views, drops the view only if Athena view syntax (dialect) is present in the Data Catalog view. For example, if a user calls `DROP VIEW` from Athena, the view is dropped only if the Athena dialect exists in the view. Otherwise, the operation fails. Dropping Data Catalog views requires Lake Formation admin or view definer permissions.
For more information, see [Work with views](views.md) and [Use Data Catalog views in Athena](views-glue.md).
## Synopsis
```
DROP VIEW [ IF EXISTS ] view_name
```
## Examples
```
DROP VIEW orders_by_date
```
```
DROP VIEW IF EXISTS orders_by_date
```
See also [CREATE VIEW and CREATE PROTECTED MULTI DIALECT VIEW](create-view.md), [SHOW COLUMNS](show-columns.md), [SHOW CREATE VIEW](show-create-view.md), [SHOW VIEWS](show-views.md), and [DESCRIBE VIEW](describe-view.md).
# MSCK REPAIR TABLE
Use the `MSCK REPAIR TABLE` command to update the metadata in the catalog after you add Hive compatible partitions.
The `MSCK REPAIR TABLE` command scans a file system such as Amazon S3 for Hive compatible partitions that were added to the file system after the table was created. `MSCK REPAIR TABLE` compares the partitions in the table metadata and the partitions in S3. If new partitions are present in the S3 location that you specified when you created the table, it adds those partitions to the metadata and to the Athena table.
When you add physical partitions, the metadata in the catalog becomes inconsistent with the layout of the data in the file system, and information about the new partitions needs to be added to the catalog. To update the metadata, run `MSCK REPAIR TABLE` so that you can query the data in the new partitions from Athena.
**Note**
`MSCK REPAIR TABLE` only adds partitions to metadata; it does not remove them. To remove partitions from metadata after the partitions have been manually deleted in Amazon S3, run the command `ALTER TABLE table-name DROP PARTITION`. For more information see [ALTER TABLE DROP PARTITION](alter-table-drop-partition.md).
## Considerations and limitations
When using `MSCK REPAIR TABLE`, keep in mind the following points:
+ It is possible it will take some time to add all partitions. If this operation times out, it will be in an incomplete state where only a few partitions are added to the catalog. You should run `MSCK REPAIR TABLE` on the same table until all partitions are added. For more information, see [Partition your data](partitions.md).
+ For partitions that are not compatible with Hive, use [ALTER TABLE ADD PARTITION](alter-table-add-partition.md) to load the partitions so that you can query their data.
+ Partition locations to be used with Athena must use the `s3` protocol (for example, `s3://amzn-s3-demo-bucket/folder/`). In Athena, locations that use other protocols (for example, `s3a://bucket/folder/`) will result in query failures when `MSCK REPAIR TABLE` queries are run on the containing tables.
+ Because `MSCK REPAIR TABLE` scans both a folder and its subfolders to find a matching partition scheme, be sure to keep data for separate tables in separate folder hierarchies. For example, suppose you have data for table 1 in `s3://amzn-s3-demo-bucket1` and data for table 2 in `s3://amzn-s3-demo-bucket1/table-2-data`. If both tables are partitioned by string, `MSCK REPAIR TABLE` will add the partitions for table 2 to table 1. To avoid this, use separate folder structures like `s3://amzn-s3-demo-bucket1` and `s3://amzn-s3-demo-bucket2` instead. Note that this behavior is consistent with Amazon EMR and Apache Hive.
+ Due to a known issue, `MSCK REPAIR TABLE` fails silently when partition values contain a colon (`:`) character (for example, when the partition value is a timestamp). As a workaround, use [ALTER TABLE ADD PARTITION](alter-table-add-partition.md).
+ `MSCK REPAIR TABLE` does not add partition column names that begin with an underscore (\$1). To work around this limitation, use [ALTER TABLE ADD PARTITION](alter-table-add-partition.md).
## Synopsis
```
MSCK REPAIR TABLE table_name
```
## Examples
```
MSCK REPAIR TABLE orders;
```
## Troubleshooting
After you run `MSCK REPAIR TABLE`, if Athena does not add the partitions to the table in the AWS Glue Data Catalog, check the following:
+ **AWS Glue access** – Make sure that the AWS Identity and Access Management (IAM) role has a policy that allows the `glue:BatchCreatePartition` action. For more information, see [Allow glue:BatchCreatePartition in the IAM policy](#msck-repair-table-troubleshooting-allow-gluebatchcreatepartition-in-the-policy) later in this document.
+ **Amazon S3 access** – Make sure that the role has a policy with sufficient permissions to access Amazon S3, including the [https://docs.aws.amazon.com/AmazonS3/latest/API/API_control_DescribeJob.html](https://docs.aws.amazon.com/AmazonS3/latest/API/API_control_DescribeJob.html) action. For an example of which Amazon S3 actions to allow, see the example bucket policy in [Configure cross-account access in Athena to Amazon S3 buckets](cross-account-permissions.md).
+ **Amazon S3 object key casing** – Make sure that the Amazon S3 path is in lower case instead of camel case (for example, `userid` instead of `userId`), or use `ALTER TABLE ADD PARTITION` to specify the object key names. For more information, see [Change or redefine the Amazon S3 path](#msck-repair-table-troubleshooting-change-or-redefine-the-amazon-s3-path) later in this document.
+ **Query timeouts** – `MSCK REPAIR TABLE` is best used when creating a table for the first time or when there is uncertainty about parity between data and partition metadata. If you use `MSCK REPAIR TABLE` to add new partitions frequently (for example, on a daily basis) and are experiencing query timeouts, consider using [ALTER TABLE ADD PARTITION](alter-table-add-partition.md).
+ **Partitions missing from file system** – If you delete a partition manually in Amazon S3 and then run `MSCK REPAIR TABLE`, you may receive the error message Partitions missing from filesystem. This occurs because `MSCK REPAIR TABLE` doesn't remove stale partitions from table metadata. To remove the deleted partitions from table metadata, run [ALTER TABLE DROP PARTITION](alter-table-drop-partition.md) instead. Note that [SHOW PARTITIONS](show-partitions.md) similarly lists only the partitions in metadata, not the partitions in the file system.
+ **"NullPointerException name is null" error**
If you use the AWS Glue [CreateTable](https://docs.aws.amazon.com/glue/latest/webapi/API_CreateTable.html) API operation or the CloudFormation [https://docs.aws.amazon.com/AWSCloudFormation/latest/UserGuide/aws-resource-glue-table.html](https://docs.aws.amazon.com/AWSCloudFormation/latest/UserGuide/aws-resource-glue-table.html) template to create a table for use in Athena without specifying the `TableType` property and then run a DDL query like `SHOW CREATE TABLE` or `MSCK REPAIR TABLE`, you can receive the error message FAILED: NullPointerException Name is null.
To resolve the error, specify a value for the [TableInput](https://docs.aws.amazon.com/glue/latest/webapi/API_TableInput.html) `TableType` attribute as part of the AWS Glue `CreateTable` API call or [CloudFormation template](https://docs.aws.amazon.com/AWSCloudFormation/latest/UserGuide/aws-properties-glue-table-tableinput.html). Possible values for `TableType` include `EXTERNAL_TABLE` or `VIRTUAL_VIEW`.
This requirement applies only when you create a table using the AWS Glue `CreateTable` API operation or the `AWS::Glue::Table` template. If you create a table for Athena by using a DDL statement or an AWS Glue crawler, the `TableType` property is defined for you automatically.
The following sections provide some additional detail.
### Allow glue:BatchCreatePartition in the IAM policy
Review the IAM policies attached to the role that you're using to run `MSCK REPAIR TABLE`. When you [use the AWS Glue Data Catalog with Athena](data-sources-glue.md), the IAM policy must allow the `glue:BatchCreatePartition` action. For an example of an IAM policy that allows the `glue:BatchCreatePartition` action, see [AWS managed policy: AmazonAthenaFullAccess](security-iam-awsmanpol.md#amazonathenafullaccess-managed-policy).
### Change or redefine the Amazon S3 path
If one or more object keys in the Amazon S3 path are in camel case instead of lower case, `MSCK REPAIR TABLE` might not add the partitions to the AWS Glue Data Catalog. For example, if your Amazon S3 path includes the object key name `userId`, the following partitions might not be added to the AWS Glue Data Catalog:
```
s3://amzn-s3-demo-bucket/path/userId=1/
s3://amzn-s3-demo-bucket/path/userId=2/
s3://amzn-s3-demo-bucket/path/userId=3/
```
To resolve this issue, do one of the following:
+ Use lower case instead of camel case when you create your Amazon S3 object keys:
```
s3://amzn-s3-demo-bucket/path/userid=1/
s3://amzn-s3-demo-bucket/path/userid=2/
s3://amzn-s3-demo-bucket/path/userid=3/
```
+ Use [ALTER TABLE ADD PARTITION](alter-table-add-partition.md) to redefine the location, as in the following example:
```
ALTER TABLE table_name ADD [IF NOT EXISTS]
PARTITION (userId=1)
LOCATION 's3://amzn-s3-demo-bucket/path/userId=1/'
PARTITION (userId=2)
LOCATION 's3://amzn-s3-demo-bucket/path/userId=2/'
PARTITION (userId=3)
LOCATION 's3://amzn-s3-demo-bucket/path/userId=3/'
```
Note that although Amazon S3 object key names can use upper case, Amazon S3 bucket names themselves must always be in lower case. For more information, see [Object key naming guidelines](https://docs.aws.amazon.com/AmazonS3/latest/userguide/object-keys.html#object-key-guidelines) and [Bucket naming rules](https://docs.aws.amazon.com/AmazonS3/latest/userguide/bucketnamingrules.html) in the *Amazon S3 User Guide*.
# SHOW COLUMNS
Shows only the column names for a single specified table, Athena view, or Data Catalog view. To obtain more detailed information for Athena views, query the AWS Glue Data Catalog instead. For information and examples, see the following sections of the [Query the AWS Glue Data Catalog](querying-glue-catalog.md) topic:
+ To view column metadata such as data type, see [List or search columns for a specified table or view](querying-glue-catalog-listing-columns.md).
+ To view all columns for all tables in a specific database in `AwsDataCatalog`, see [List or search columns for a specified table or view](querying-glue-catalog-listing-columns.md).
+ To view all columns for all tables in all databases in `AwsDataCatalog`, see [List all columns for all tables](querying-glue-catalog-listing-all-columns-for-all-tables.md).
+ To view the columns that specific tables in a database have in common, see [List the columns that specific tables have in common](querying-glue-catalog-listing-columns-in-common.md).
For Data Catalog views, the output of the statement is controlled by Lake Formation access control and shows only the columns that the caller has access to.
## Synopsis
```
SHOW COLUMNS {FROM|IN} database_name.table_or_view_name
```
```
SHOW COLUMNS {FROM|IN} table_or_view_name [{FROM|IN} database_name]
```
The `FROM` and `IN` keywords can be used interchangeably. If *table\$1or\$1view\$1name* or *database\$1name* has special characters like hyphens, surround the name with back quotes (for example, ``my-database`.`my-table``). Do not surround the *table\$1or\$1view\$1name* or *database\$1name* with single or double quotes. Currently, the use of `LIKE` and pattern matching expressions is not supported.
## Examples
The following equivalent examples show the columns from the `orders` table in the `customers` database. The first two examples assume that `customers` is the current database.
```
SHOW COLUMNS FROM orders
```
```
SHOW COLUMNS IN orders
```
```
SHOW COLUMNS FROM customers.orders
```
```
SHOW COLUMNS IN customers.orders
```
```
SHOW COLUMNS FROM orders FROM customers
```
```
SHOW COLUMNS IN orders IN customers
```
# SHOW CREATE TABLE
Analyzes an existing table named `table_name` to generate the query that created it.
## Synopsis
```
SHOW CREATE TABLE [db_name.]table_name
```
## Parameters
**TABLE [db\$1name.]table\$1name**
The `db_name` parameter is optional. If omitted, the context defaults to the current database.
The table name is required.
## Examples
```
SHOW CREATE TABLE orderclickstoday;
```
```
SHOW CREATE TABLE `salesdata.orderclickstoday`;
```
## Troubleshooting
If you use the AWS Glue [CreateTable](https://docs.aws.amazon.com/glue/latest/webapi/API_CreateTable.html) API operation or the CloudFormation [https://docs.aws.amazon.com/AWSCloudFormation/latest/UserGuide/aws-resource-glue-table.html](https://docs.aws.amazon.com/AWSCloudFormation/latest/UserGuide/aws-resource-glue-table.html) template to create a table for use in Athena without specifying the `TableType` property and then run a DDL query like `SHOW CREATE TABLE` or `MSCK REPAIR TABLE`, you can receive the error message FAILED: NullPointerException Name is null.
To resolve the error, specify a value for the [TableInput](https://docs.aws.amazon.com/glue/latest/webapi/API_TableInput.html) `TableType` attribute as part of the AWS Glue `CreateTable` API call or [CloudFormation template](https://docs.aws.amazon.com/AWSCloudFormation/latest/UserGuide/aws-properties-glue-table-tableinput.html). Possible values for `TableType` include `EXTERNAL_TABLE` or `VIRTUAL_VIEW`.
This requirement applies only when you create a table using the AWS Glue `CreateTable` API operation or the `AWS::Glue::Table` template. If you create a table for Athena by using a DDL statement or an AWS Glue crawler, the `TableType` property is defined for you automatically.
# SHOW CREATE VIEW
Shows the SQL statement that created the specified Athena or Data Catalog view. The SQL returned shows the create view syntax used in Athena. Calling `SHOW CREATE VIEW` on Data Catalog views requires Lake Formation admin or view definer permissions.
## Synopsis
```
SHOW CREATE VIEW view_name
```
## Examples
```
SHOW CREATE VIEW orders_by_date
```
See also [CREATE VIEW and CREATE PROTECTED MULTI DIALECT VIEW](create-view.md) and [DROP VIEW](drop-view.md).
# SHOW DATABASES
Lists all databases defined in the metastore. You can use `DATABASES` or `SCHEMAS`. They mean the same thing.
The programmatic equivalent of `SHOW DATABASES` is the [ListDatabases](https://docs.aws.amazon.com/athena/latest/APIReference/API_ListDatabases.html) Athena API action. The equivalent method in AWS SDK for Python (Boto3) is [list\$1databases](https://boto3.amazonaws.com/v1/documentation/api/latest/reference/services/athena/client/list_databases.html).
## Synopsis
```
SHOW {DATABASES | SCHEMAS} [LIKE 'regular_expression']
```
## Parameters
**[LIKE '*regular\$1expression*']**
Filters the list of databases to those that match the `regular_expression` that you specify. For wildcard character matching, you can use the combination `.*`, which matches any character zero to unlimited times.
## Examples
```
SHOW SCHEMAS;
```
```
SHOW DATABASES LIKE '.*analytics';
```
# SHOW PARTITIONS
Lists all the partitions in an Athena table in unsorted order.
## Synopsis
```
SHOW PARTITIONS table_name
```
+ To show the partitions in a table and list them in a specific order, see the [List partitions for a specific table](querying-glue-catalog-listing-partitions.md) section on the [Query the AWS Glue Data Catalog](querying-glue-catalog.md) page.
+ To view the contents of a partition, see the [Query the data](partitions.md#query-the-data) section on the [Partition your data](partitions.md) page.
+ `SHOW PARTITIONS` does not list partitions that are projected by Athena but not registered in the AWS Glue catalog. For information about partition projection, see [Use partition projection with Amazon Athena](partition-projection.md).
+ `SHOW PARTITIONS` lists the partitions in metadata, not the partitions in the actual file system. To update the metadata after you delete partitions manually in Amazon S3, run [ALTER TABLE DROP PARTITION](alter-table-drop-partition.md).
## Examples
The following example query shows the partitions for the `flight_delays_csv` table, which shows flight table data from the US Department of Transportation. For more information about the example `flight_delays_csv` table, see [Lazy Simple SerDe for CSV, TSV, and custom-delimited files](lazy-simple-serde.md). The table is partitioned by year.
```
SHOW PARTITIONS flight_delays_csv
```
**Results**
```
year=2007
year=2015
year=1999
year=1993
year=1991
year=2003
year=1996
year=2014
year=2004
year=2011
...
```
The following example query shows the partitions for the `impressions` table, which contains sample web browsing data. For more information about the example `impressions` table, see [Partition your data](partitions.md). The table is partitioned by the `dt` (datetime) column.
```
SHOW PARTITIONS impressions
```
**Results**
```
dt=2009-04-12-16-00
dt=2009-04-13-18-15
dt=2009-04-14-00-20
dt=2009-04-12-13-00
dt=2009-04-13-02-15
dt=2009-04-14-12-05
dt=2009-04-14-06-15
dt=2009-04-12-21-15
dt=2009-04-13-22-15
...
```
### Listing partitions in sorted order
To order the partitions in the results list, use the following `SELECT` syntax instead of `SHOW PARTITIONS`.
```
SELECT * FROM database_name."table_name$partitions" ORDER BY column_name
```
The following query shows the list of partitions for the `flight_delays_csv` example, but in sorted order.
```
SELECT * FROM "flight_delays_csv$partitions" ORDER BY year
```
**Results**
```
year
1987
1988
1989
1990
1991
1992
1993
1994
1995
1996
1997
1998
1999
...
```
For more information, see the [List partitions for a specific table](querying-glue-catalog-listing-partitions.md) section on the [Query the AWS Glue Data Catalog](querying-glue-catalog.md) page.
# SHOW TABLES
Lists all the base tables and views in a database.
**Note**
The [StatementType](https://docs.aws.amazon.com/athena/latest/APIReference/API_QueryExecution.html#athena-Type-QueryExecution-StatementType) parameter for `SHOW TABLES` in [GetQueryExecution](https://docs.aws.amazon.com/athena/latest/APIReference/API_GetQueryExecution.html) API operation is categorized as `UTILITY`, not `DDL`.
## Synopsis
```
SHOW TABLES [IN database_name] ['regular_expression']
```
## Parameters
**[IN database\$1name]**
Specifies the `database_name` from which tables will be listed. If omitted, the database from the current context is assumed.
`SHOW TABLES` may fail if `database_name` uses an [unsupported character](tables-databases-columns-names.md) such as a hyphen. As a workaround, try enclosing the database name in backticks.
**['regular\$1expression']**
Filters the list of tables to those that match the `regular_expression` you specify. To indicate any character in `AWSDataCatalog` tables, you can use the `*` or `.*` wildcard expression. For Apache Hive databases, use the `.*` wildcard expression. To indicate a choice between characters, use the `|` character.
## Examples
**Example – Show all of the tables in the database `sampledb`**
```
SHOW TABLES IN sampledb
```
`Results`
```
alb_logs
cloudfront_logs
elb_logs
flights_2016
flights_parquet
view_2016_flights_dfw
```
**Example – Show the names of all tables in `sampledb` that include the word "flights"**
```
SHOW TABLES IN sampledb '*flights*'
```
`Results`
```
flights_2016
flights_parquet
view_2016_flights_dfw
```
**Example – Show the names of all tables in `sampledb` that end in the word "logs"**
```
SHOW TABLES IN sampledb '*logs'
```
`Results`
```
alb_logs
cloudfront_logs
elb_logs
```
# SHOW TBLPROPERTIES
Lists table properties for the named table.
## Synopsis
```
SHOW TBLPROPERTIES table_name [('property_name')]
```
## Parameters
**[('property\$1name')]**
If included, only the value of the property named `property_name` is listed.
## Examples
```
SHOW TBLPROPERTIES orders;
```
```
SHOW TBLPROPERTIES orders('comment');
```
# SHOW VIEWS
Lists the Athena or Data Catalog views in a list of `STRING` type values. Each value in the list is the name of a view in the specified database, or in the current database if you omit the database name. Use the optional `LIKE` clause with a regular expression to restrict the list of view names. For Data Catalog views, lists only the views that use Athena SQL syntax. Other Data Catalog views are filtered out.
## Synopsis
```
SHOW VIEWS [IN database_name] [LIKE 'regular_expression']
```
### Parameters
**[IN database\$1name]**
Specifies the `database_name` from which views will be listed. If omitted, the database from the current context is assumed.
**[LIKE 'regular\$1expression']**
Filters the list of views to those that match the `regular_expression` you specify. Only the wild card character `*`, which indicates any character, or `|`, which indicates a choice between characters, can be used.
## Examples
```
SHOW VIEWS
```
```
SHOW VIEWS IN marketing_analytics LIKE 'orders*'
```
See also [SHOW COLUMNS](show-columns.md), [SHOW CREATE VIEW](show-create-view.md), [DESCRIBE VIEW](describe-view.md), and [DROP VIEW](drop-view.md).