Course contentsShow
← Cloud and Infrastructure
Lesson 2659 of 2,98957. Serverless Data ProcessingFree lesson

Athena Query Syntax and DDL

Writing CREATE TABLE, ALTER TABLE, and DROP TABLE statements in Athena using Presto SQL syntax.

Athena Query Syntax and DDL

What you'll learn: How to define and modify table structures in Amazon Athena using Presto SQL DDL statements.

Understanding Athena DDL

Athena uses Presto SQL syntax to define, modify, and remove table structures. Think of DDL (Data Definition Language) as the blueprint language for your data lake tables—you're not moving data around, but rather telling Athena how to interpret files stored in S3 as structured tables.

CREATE TABLE Syntax

The most common DDL operation defines a new table pointing to existing data:

CREATE EXTERNAL TABLE sales_data (
  order_id STRING,
  customer_id INT,
  amount DECIMAL(10,2),
  order_date DATE
)
STORED AS PARQUET
LOCATION 's3://my-bucket/sales/';

The EXTERNAL keyword means data lives outside Athena's control—it's just reading S3 files. You specify column names, data types, file format (Parquet, JSON, CSV), and the S3 path.

ALTER TABLE Operations

Modify existing table structures without rewriting data:

-- Add a new column
ALTER TABLE sales_data ADD COLUMNS (region STRING);

-- Change location
ALTER TABLE sales_data SET LOCATION 's3://my-bucket/sales-2024/';

These operations update the metadata in the Glue Data Catalog instantly.

DROP TABLE Cleanup

Remove table definitions when no longer needed:

DROP TABLE IF EXISTS sales_data;

This only deletes the table metadata—your S3 data remains untouched, which is crucial for data lake safety.

Practical Pattern

DDL statements are typically run once during setup or when schema evolves. Your crawlers might create initial tables, but you'll use ALTER TABLE for partition management or adding columns as your data model grows.

Key Takeaway: Athena DDL statements (CREATE, ALTER, DROP) manage table metadata using Presto SQL syntax, defining how to query S3 data without moving or modifying the underlying files.