From CSV to Parquet in Amazon Athena with one query
How to query CSV files in S3 with Athena, then convert them to partitioned Parquet with a single CTAS statement to scan less data and pay less.
Why bother
Athena bills by the amount of data each query scans. CSV forces it to read every byte of every file. Parquet is columnar and compressed, and with partitions Athena can skip whole folders. Converting takes one query.
1. Query the CSV
Upload your files to their own S3 prefix (for example
s3://your-bucket/sales/csv/) and set a query result location in Athena. Then:1
2
3
4
5
6
7
8
9
10
11
12
CREATE DATABASE IF NOT EXISTS demo;
CREATE EXTERNAL TABLE demo.sales_csv (
order_id string,
order_date date,
country string,
amount double
)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ','
LOCATION 's3://your-bucket/sales/csv/'
TBLPROPERTIES ('skip.header.line.count'='1');Dates need to be in
YYYY-MM-DD format. If your fields contain quoted commas, use OpenCSVSerDe instead.2. Convert to Parquet with CTAS
1
2
3
4
5
6
7
8
CREATE TABLE demo.sales_parquet
WITH (
format = 'PARQUET',
external_location = 's3://your-bucket/sales/parquet/',
partitioned_by = ARRAY['country']
) AS
SELECT order_id, order_date, amount, country
FROM demo.sales_csv;Partition columns must come last in the
SELECT, and the target location must be empty.3. Compare
Run the same query against both tables:
1
SELECT sum(amount) FROM demo.sales_parquet WHERE country = 'AR';Check "Data scanned" in the query stats. The Parquet version reads only one partition and one column.
Gotchas
- A single CTAS query can write at most 100 partitions. For more, use
INSERT INTOin batches. - Partition on columns you filter by often, and avoid ones with very many unique values.
- CTAS doesn't refresh itself. New CSV data needs another
INSERT INTO.
Enjoyed reading this content? Let the author know!
Your likes, comments, shares, and saves help creators reach more builders.
Loading recommendations
Loading article