ALTER TABLE SET TBLPROPERTIES - Amazon Athena
SynopsisParametersExamples

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

Apache Hive Managed 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_name' = 'property_value' [ , ... ])

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.

Predefined property Description
classification Indicates the data type for AWS Glue. Possible values are csv, parquet, orc, avro, or json. Tables created for Athena in the CloudTrail console add cloudtrail as a value for the classification property. For more information, see the TBLPROPERTIES section of CREATE TABLE.
has_encrypted_data Indicates whether the dataset specified by LOCATION is encrypted. For more information, see the TBLPROPERTIES section of CREATE TABLE and Create tables based on encrypted datasets in Amazon S3.
orc.compress Specifies a compression format for data in ORC format. For more information, see ORC SerDe.
parquet.compression Specifies a compression format for data in Parquet format. For more information, see Parquet SerDe.
write.compression Specifies a compression format for data in the text file or JSON formats. For the Parquet and ORC formats, use the parquet.compression and orc.compress properties respectively.
compression_level Specifies a 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.
projection.* Custom properties used in partition projection that allow Athena to know what partition patterns to expect when it runs a query on a table. For more information, see Use partition projection with Amazon Athena.
skip.header.line.count Ignores headers in data when you define a table. For more information, see Ignoring headers.
storage.location.template Specifies a custom Amazon S3 path template for projected partitions. For more information, see Set up partition projection.

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)