AWS Athena查询异常：Parquet格式查询Top10扫描数据量高于CSV格式

Let's break down why you're seeing this unexpected behavior with your limit 10 query—this result ties directly to how Athena handles limit clauses with different storage formats, plus how your Parquet files were generated.

Key Reasons for the Reverse Scan Volume #

1. File Count + Athena's Early Termination for Limit Queries #

Your CSV dataset (91.3MB) is almost certainly split into multiple small files (common for public NYC taxi datasets), while your Glue-generated Parquet is likely a single large 19.4MB file. Here's how that impacts your query:

• For CSV: Athena can parallel-read the start of multiple small files. As soon as it collects 10 matching rows, it stops scanning the rest of the files. That's why you only see 721.96KB scanned—you're only touching the first few lines of a handful of small files.
• For Parquet: If the entire dataset is one big file, Athena can't easily stop mid-row-group. Parquet uses row groups as its basic storage unit; if your whole file is one row group, Athena has to read the full columns you requested for that entire row group to get your 10 rows, hence the 10.9MB scan.

2. Glue Crawler's Parquet Output Settings #

Glue crawlers tend to create minimal files by default, often lumping converted data into one or a few large Parquet files instead of splitting them into smaller chunks. Unlike CSV's small-file structure, this eliminates Athena's ability to terminate early for limit queries.

Parquet's columnar shine comes through for large, filtered queries, but for small limit queries, big row groups work against you—you can't avoid reading the full column data in the row group even if you only need a handful of rows.

3. Parquet Metadata Overhead #

Parquet files include extra metadata (like column stats, row group indexes, and schema info) that CSV doesn't have. For a single large Parquet file, Athena has to load this metadata upfront, which adds to the total scanned data size. CSV has almost no metadata, so Athena can jump straight to reading rows until it hits the limit.

How to Verify & Fix This #

• Check file structure: Look at your S3 buckets—your CSV dataset should have dozens/hundreds of small files, while the Parquet one likely has just one big file.
• Reconvert with Glue ETL (not crawler): Use a Glue ETL job instead of a crawler to convert the CSV to Parquet. Configure it to split output into smaller files (e.g., 64MB chunks) and set smaller row group sizes. Running your limit 10 query on this properly partitioned Parquet dataset should result in a much smaller scan volume.

内容的提问来源于stack exchange，提问作者Scrooge McD