Parquet and Partitioning for Cheaper Athena Queries

Athena's per-query price has one input: the bytes the query scans. The pricing page says you are charged for bytes scanned, rounded up to the nearest megabyte, with a 10 MB minimum per query (AWS: Athena pricing). The query's complexity, its run time and the number of rows returned do not appear in that sentence. So the cheapest query is the one that reads the fewest bytes, and how many bytes it must read is decided mostly before the query is written, by how the files were stored.

Three choices control it: compression, columnar format and partitioning. This article measures the first two on real data. Partition design has its own article, partitioning data in S3 for Athena.

The measurement

The file is one month of New York yellow taxi trips, published by the city's Taxi and Limousine Commission (NYC TLC: trip record data): 2,964,624 rows and 19 columns. We wrote the same rows four ways on a local disk and measured the files. Then we took a typical question, total fare by payment type, which needs two of the 19 columns, and added up the bytes in the Parquet files that hold those two columns.

import gzip, os, shutil
import pyarrow.parquet as pq
import pyarrow.csv as pacsv

SRC = "yellow_tripdata_2024-01.parquet"
table = pq.read_table(SRC)
print(f"{table.num_rows:,} rows, {table.num_columns} columns")

# The same data in four layouts
pacsv.write_csv(table, "trips.csv")
with open("trips.csv", "rb") as f, gzip.open("trips.csv.gz", "wb", compresslevel=6) as g:
    shutil.copyfileobj(f, g)
pq.write_table(table, "trips_snappy.parquet", compression="snappy")
pq.write_table(table, "trips_zstd.parquet", compression="zstd")

MB = 1024 * 1024
csv_bytes = os.path.getsize("trips.csv")
print()
print("Whole file")
for name in ["trips.csv", "trips.csv.gz", "trips_snappy.parquet", "trips_zstd.parquet"]:
    size = os.path.getsize(name)
    print(f"  {name:22} {size / MB:8.1f} MB  {size / csv_bytes:6.1%} of CSV")

# A query that needs two columns: total fare by payment type
need = ["payment_type", "fare_amount"]
print()
print("Bytes holding only", need)
for name in ["trips_snappy.parquet", "trips_zstd.parquet"]:
    meta = pq.ParquetFile(name).metadata
    used = 0
    for rg in range(meta.num_row_groups):
        for c in range(meta.num_columns):
            col = meta.row_group(rg).column(c)
            if col.path_in_schema in need:
                used += col.total_compressed_size
    print(f"  {name:22} {used / MB:8.2f} MB  {used / csv_bytes:6.2%} of CSV")

2,964,624 rows, 19 columns

Whole file
  trips.csv                 306.5 MB  100.0% of CSV
  trips.csv.gz               54.4 MB   17.8% of CSV
  trips_snappy.parquet       58.0 MB   18.9% of CSV
  trips_zstd.parquet         47.0 MB   15.3% of CSV

Bytes holding only ['payment_type', 'fare_amount']
  trips_snappy.parquet       4.50 MB   1.47% of CSV
  trips_zstd.parquet         3.47 MB   1.13% of CSV

This ran locally, not on Athena, so read the sizes as the bytes an engine would have to fetch from S3, not as figures from Athena's query statistics.

What the numbers say

Compression alone removes about four fifths. Gzipping the CSV takes it from 306.5 MB to 54.4 MB. Athena charges on the compressed size: its documentation says querying compressed data is cheaper because you pay for the bytes scanned before decompression (Athena docs: optimize data).

Parquet is about the same size as gzipped CSV, and that is not the point. Snappy Parquet is 58.0 MB, slightly larger than the gzip file; Zstandard Parquet is 47.0 MB. If a query reads every column, the saving over gzipped CSV is small.

The saving comes from reading fewer columns. A row-based file has to be read in full to answer any question, because every row carries all 19 values. In the Parquet file the two columns the query needs occupy 4.50 MB, or 3.47 MB with Zstandard: about one and a half percent of the original CSV, and under a tenth of the gzipped file. The Athena guide lists this first among the advantages of columnar formats: only the columns needed for the query are loaded.

Selecting every column throws the saving away. Write SELECT * and the engine must read every column. On columnar data, naming the columns you need is a cost decision, not a matter of style.

AWS's own worked example makes the same argument with round numbers: a 3 TB text file with four equal columns costs the full 3 TB to query for one column; gzipped to 1 TB it costs a third; as Parquet of the same 1 TB, the query reads one column, a quarter of a terabyte. The example prices a terabyte at 5 US dollars; check the pricing page for the current rate in your Region.

Two further effects the size table does not show

• Skipping within a column. Parquet stores minimum and maximum values for blocks of each column, and Athena uses those statistics to decide whether to read or skip a block (Athena docs: columnar storage). This works best when the data is sorted on the column you filter by, so that each block covers a narrow range.
• Parallel reading. A gzipped text file must be read from the beginning, so one worker reads it. Parquet files can be split between readers. The gzip file may cost about the same as Parquet for a full scan, but it will be slower.

Converting a table

If the data is already queryable in Athena as CSV or JSON, a CREATE TABLE AS SELECT statement rewrites it as Parquet in one step. The following is written from the documentation and has not been run:

CREATE TABLE trips_parquet
WITH (
  format = 'PARQUET',
  write_compression = 'ZSTD',
  external_location = 's3://amzn-s3-demo-bucket/trips_parquet/',
  partitioned_by = ARRAY['pickup_date']
) AS
SELECT vendorid,
       tpep_pickup_datetime,
       payment_type,
       fare_amount,
       date_format(tpep_pickup_datetime, '%Y-%m-%d') AS pickup_date
FROM trips_csv;

Three details matter:

• Partition columns go last in the select list.
• A CTAS statement can write at most 100 partitions. The guide documents the limit and a workaround using further INSERT INTO statements (Athena docs: CTAS considerations). A month of daily partitions fits; several years do not fit in one statement.
• The conversion scans the source in full, and that scan is billed like any other query.

For regular pipelines, a Glue or Spark job that writes Parquet directly is a common choice; CTAS suits one-off conversions and small tables.

Where the gains stop

• Small files. The guide warns that for small files the overhead of a columnar format outweighs the benefits, and that datasets of many small files perform poorly. Convert, then check file sizes.
• The 10 MB minimum. Each query is billed for at least 10 MB. The two-column read in this example is below that, so it would be billed as 10 MB; savings beneath the minimum are not passed on, and the ratios matter most at gigabytes and terabytes.
• Wide queries. A report that reads most columns gains little from the format beyond compression and parallelism.
• Nested or changing data. Converting JSON with an unstable structure needs a schema decision first. Parquet vs ORC vs Avro compares the formats and what each handles well.

A short checklist

1. Store analytical data as Parquet, compressed with Snappy or Zstandard.
2. Partition on the column most queries filter by, usually a date.
3. Keep files reasonably large; compact small ones.
4. Name columns in queries; avoid SELECT * on wide tables.
5. Sort within files on a commonly filtered column.
6. Look at "Data scanned" in the query statistics after each change, and keep what works.

For the service itself, tables, workgroups and result locations, see Amazon Athena: querying S3 with SQL.

File formats, partitioning and AWS data services including S3 and Glue are covered in the Data Engineering course.

Sources

• Amazon Athena pricing (Amazon Web Services) — Amazon Web Services
• Use columnar storage formats (Amazon Athena User Guide) — AWS Documentation
• Optimize data (Amazon Athena User Guide) — AWS Documentation
• Considerations and limitations for CTAS queries (Amazon Athena User Guide) — AWS Documentation
• TLC Trip Record Data (NYC Taxi and Limousine Commission) — NYC Taxi and Limousine Commission