Delete specific column from table without partition

Hi Moise, thanks for your question to re:Post.

Issue

The error occurs because Athena does not support ALTER TABLE DROP COLUMN syntax for non-Iceberg tables. The parser expects DROP PARTITION, not DROP COLUMN.

-- This does NOT work in Athena:
ALTER TABLE table_name DROP COLUMN column_name

Error: FAILED: ParseException line 1:22 mismatched input 'column' expecting PARTITION near 'DROP'

---

Solution: Use ALTER TABLE REPLACE COLUMNS

To remove a column in Athena, use REPLACE COLUMNS and specify only the columns you want to keep:

ALTER TABLE table_name 
REPLACE COLUMNS (
  column1 datatype1,
  column2 datatype2,
  column3 datatype3
  -- Omit the column you want to drop
)

Important Notes:

1. List all columns you want to keep - any column not listed will be dropped
2. This only works with LazySimpleSerDe tables (default for text-based formats)
3. The underlying data in S3 is not modified - only the table metadata changes
4. Use SHOW COLUMNS IN table_name to see current columns before making changes

---

Alternative: Create a New Table

If REPLACE COLUMNS doesn't work for your table format, use CTAS (Create Table As Select):

-- Create new table without the unwanted column
CREATE TABLE new_table_name AS
SELECT column1, column2, column3  -- List only columns you want
FROM old_table_name;

-- Then drop the old table and rename if needed
DROP TABLE old_table_name;

This approach:

• Works with any table format (Parquet, ORC, Iceberg, etc.)
• Creates new data files in S3 without the dropped column
• Gives you control over the output format and location

---

References

• ALTER TABLE REPLACE COLUMNS
• Create Table As Select (CTAS)