use Parquet Column Indexes with Amazon Athena

Amazon Athena lately added assist for studying Parquet Column Indexes in Apache Iceberg tables on November 21, 2025. With this optimization, Athena can carry out page-level information pruning to skip pointless information inside Parquet row teams, probably decreasing the quantity of information scanned and enhancing question runtime for queries with selective filters. For information groups, this may increasingly assist allow sooner insights and assist cut back prices when analyzing large-scale information lakes.

Knowledge groups constructing information lakes typically select Apache Iceberg for its ACID transactions, schema evolution, and metadata administration capabilities. Athena is a serverless question engine that permits you to question Amazon S3-based information lakes utilizing SQL, and also you don’t have to handle infrastructure. Primarily based on the kind of information and question logic, Athena can apply a number of question optimizations to enhance efficiency and cut back prices.

On this weblog submit, we use Athena and Amazon SageMaker Unified Studio to discover Parquet Column Indexes and show how they will enhance Iceberg question efficiency. We clarify what Parquet Column Indexes are, show their efficiency advantages, and present you easy methods to use them in your functions.

Overview of Parquet Column Indexes

Parquet Column Indexes retailer metadata that question engines can use to skip irrelevant information with higher precision than row group statistics alone. To grasp how they work, take into account how information is structured inside Parquet recordsdata and the way engines like Athena course of them.

Parquet recordsdata arrange information hierarchically by dividing information into row teams (usually 128-512 MB every) and additional subdividing them into pages (usually 1 MB every). Historically, Parquet maintains metadata on the contents of every row group stage within the type of min/max statistics, permitting engines like Athena to skip row teams that don’t fulfill question predicates. Though this method reduces the bytes scanned and question runtime, it has limitations. If even a single web page inside a row group overlaps with the values you might be trying to find, Athena scans all pages inside the row group.

Parquet Column Indexes assist handle this drawback by storing page-level min/max statistics within the Parquet file footer. Row group statistics present coarse-grained filtering, however Parquet Column Indexes allow finer-grained filtering by permitting question engines like Athena to skip particular person pages inside a row group. Think about a Parquet file with a single row group containing 5 pages for a column. The row group has min/max statistics of (1, 20), and every web page for that column has the next min/max statistics.

row-group-0: min=1, max=20
    page-0: min=1, max=10
    page-1: min=1, max=10
    page-2: min=5, max=15
    page-3: min=6, max=16
    page-4: min=10, max=20

When Athena runs a question filtering for values equal to 2, it first checks the row group statistics and confirms that 2 falls inside the vary (1, 20). Athena will then plan to scan the pages inside that row group. With out Parquet Column Indexes, Athena scans every of the 5 pages within the row group. With Parquet Column Indexes, Athena examines the page-level statistics and determines that solely page-0 and page-1 should be learn, skipping the remaining 3 pages.

use Parquet Column Indexes with Athena

Athena makes use of Parquet Column Indexes primarily based on desk sort:

• Amazon S3 Tables: Athena robotically makes use of Parquet Column Indexes by default when they’re current.
• Iceberg tables in S3 normal goal buckets: Athena doesn’t use Parquet Column Indexes by default. To permit Athena to make use of Parquet Column Indexes, add an AWS Glue desk property named use_iceberg_parquet_column_index and set it to true. Use the AWS Glue console or AWS Glue UpdateTable API to carry out these actions.

Learn extra about easy methods to use this characteristic in Use Parquet column indexing.

Measuring Athena efficiency features when utilizing Parquet Column Indexes

Now that we perceive what Parquet Column Indexes are, we’ll show the efficiency advantages of utilizing Parquet Column Indexes by analyzing the catalog_sales desk from a 3TB TPC-DS dataset. This desk comprises ecommerce transaction information together with order dates, gross sales quantities, buyer IDs, and product data. This dataset is an efficient proxy for the forms of enterprise evaluation that you simply would possibly carry out by yourself information, corresponding to figuring out gross sales developments, analyzing buyer buying patterns, and calculating income metrics. We evaluate question execution statistics with and with out Parquet Column Indexes to quantify the efficiency enchancment.

Stipulations

Earlier than you start, you could have the next assets:

1. A SageMaker Unified Studio IAM-based area.
2. An Execution IAM Position configured inside the SageMaker Unified Studio IAM-based area with entry to S3, AWS Glue Knowledge Catalog, and Athena.
3. An S3 bucket in your account to retailer Iceberg desk information and Athena question outcomes.

Create catalog_sales Iceberg desk

Full the next steps utilizing SageMaker Unified Studio notebooks. There, you should use SageMaker Unified Studio’s multi-dialect pocket book performance to work together with your information utilizing the Athena SQL and Spark engines. To create a catalog_sales Iceberg desk in your account, comply with these steps:

1. Navigate to Amazon SageMaker within the AWS Administration Console and select Open underneath Get began with Amazon SageMaker Unified Studio.
2. From the aspect navigation, choose Notebooks and select Create Pocket book. The following steps on this submit will execute scripts on this pocket book.
3. Create a brand new SQL cell within the pocket book and set the connection sort to Athena (Spark). Execute the next question to create a database for the tables on this submit.

   CREATE DATABASE parquet_column_index_blog;

4. Create a brand new SQL cell within the pocket book and confirm the connection sort is Athena (Spark). Execute the next question to create a Hive desk pointing to the placement of the TPC-DS catalog_sales desk information on the public S3 bucket.

   CREATE TABLE IF NOT EXISTS parquet_column_index_blog.catalog_sales_hive (
   	  cs_sold_time_sk int,
   	  cs_ship_date_sk int,
   	  cs_bill_customer_sk int,
   	  cs_bill_cdemo_sk int,
   	  cs_bill_hdemo_sk int,
   	  cs_bill_addr_sk int,
   	  cs_ship_customer_sk int,
   	  cs_ship_cdemo_sk int,
   	  cs_ship_hdemo_sk int,
   	  cs_ship_addr_sk int,
   	  cs_call_center_sk int,
   	  cs_catalog_page_sk int,
   	  cs_ship_mode_sk int,
   	  cs_warehouse_sk int,
   	  cs_item_sk int,
   	  cs_promo_sk int,
   	  cs_order_number bigint,
   	  cs_quantity int,
   	  cs_wholesale_cost decimal(7, 2),
   	  cs_list_price decimal(7, 2),
   	  cs_sales_price decimal(7, 2),
   	  cs_ext_discount_amt decimal(7, 2),
   	  cs_ext_sales_price decimal(7, 2),
   	  cs_ext_wholesale_cost decimal(7, 2),
   	  cs_ext_list_price decimal(7, 2),
   	  cs_ext_tax decimal(7, 2),
   	  cs_coupon_amt decimal(7, 2),
   	  cs_ext_ship_cost decimal(7, 2),
   	  cs_net_paid decimal(7, 2),
   	  cs_net_paid_inc_tax decimal(7, 2),
   	  cs_net_paid_inc_ship decimal(7, 2),
   	  cs_net_paid_inc_ship_tax decimal(7, 2),
   	  cs_net_profit decimal(7, 2))
   	USING parquet
   	PARTITIONED BY (cs_sold_date_sk int)
   	LOCATION 's3://blogpost-sparkoneks-us-east-1/weblog/BLOG_TPCDS-TEST-3T-partitioned/catalog_sales/'
   	TBLPROPERTIES (
   	  'parquet.compression'='SNAPPY'
   	);

5. Create a brand new SQL cell within the pocket book and confirm the connection sort is Athena (Spark). Execute the next question so as to add the Hive partitions to the AWS Glue metadata.

   MSCK REPAIR TABLE parquet_column_index_blog.catalog_sales_hive;

6. Create a brand new SQL cell within the pocket book and confirm the connection sort is Athena (Spark). Exchange s3://amzn-s3-demo-bucket/athena_parquet_column_index_blog/catalog_sales/ with the S3 URI the place you need to retailer your Iceberg desk information, then execute the next question to create the catalog_sales Iceberg desk from the Hive desk.

   CREATE TABLE parquet_column_index_blog.catalog_sales
   	USING iceberg
   	PARTITIONED BY (cs_sold_date_sk)
   	LOCATION 's3://amzn-s3-demo-bucket/athena_parquet_column_index_blog/catalog_sales/'
   	AS
   	SELECT * FROM parquet_column_index_blog.catalog_sales_hive;

7. Create a brand new SQL cell within the pocket book and confirm the connection sort is Athena (Spark). Execute the next question to delete the catalog_sales_hive desk, which was solely wanted to create the catalog_sales Iceberg desk.

   DROP TABLE parquet_column_index_blog.catalog_sales_hive;

Run an Athena question with out Parquet Column Indexes

After creating the catalog_sales Iceberg desk within the previous steps, we run a easy question that analyzes delivery delays of the highest 10 most ordered gadgets. This sort of evaluation might be essential for ecommerce and retail operations. By figuring out which standard gadgets expertise the best delays, achievement groups can focus assets the place they matter most. For instance, you possibly can regulate stock placement, change warehouse assignments, or handle provider points. Moreover, standard gadgets with vital delivery delays usually tend to lead to order cancellations or returns, so proactively figuring out these points helps shield income.

SELECT cs_item_sk,
    SUM(cs_quantity) as total_orders,
    AVG(cs_ship_date_sk - cs_sold_date_sk) as avg_ship_delay_days,
    MIN(cs_ship_date_sk - cs_sold_date_sk) as min_ship_delay,
    MAX(cs_ship_date_sk - cs_sold_date_sk) as max_ship_delay,
    SUM(
        CASE
            WHEN cs_ship_date_sk - cs_sold_date_sk > 7 THEN 1 ELSE 0
        END
    ) as late_shipments,
    SUM(
        CASE
            WHEN cs_ship_date_sk - cs_sold_date_sk > 7 THEN 1 ELSE 0
        END
    ) * 100.0 / COUNT(*) as late_shipment_pct,
    AVG(cs_ext_ship_cost) as avg_shipping_cost
FROM parquet_column_index_blog.catalog_sales
WHERE cs_item_sk IN (
        SELECT cs_item_sk
        FROM parquet_column_index_blog.catalog_sales
        WHERE cs_item_sk IS NOT NULL
        GROUP BY cs_item_sk
        ORDER BY SUM(cs_quantity) DESC
        LIMIT 10
    )
    AND cs_ship_date_sk IS NOT NULL
    AND cs_sold_date_sk IS NOT NULL
GROUP BY cs_item_sk
ORDER BY avg_ship_delay_days DESC;

Moreover, this question is an efficient candidate for demonstrating the effectiveness of utilizing Parquet Column Indexes as a result of it has a selective filter predicate on a single column cs_item_sk. When Athena executes this question, it first identifies row teams whose min/max ranges overlap with the highest 10 most ordered gadgets. With out utilizing Parquet Column Indexes, Athena has to scan each web page of information inside these matched row teams. Nevertheless, when utilizing Parquet Column Indexes, Athena can prune information additional by skipping particular person pages inside these row teams whose min/max ranges don’t overlap with the ids. Full the next steps to determine baseline question efficiency when Athena doesn’t use Parquet Column Indexes in the course of the question.

1. Create a brand new Python cell within the pocket book. Exchange s3://amzn-s3-demo-bucket/athena_parquet_column_index_blog/query_results/ with the S3 URI the place you need to retailer your Athena question outcomes, then execute the next script. Word the runtime and bytes scanned that might be printed. The script will run the question 5 occasions with question outcome reuse disabled and chooses the minimal runtime and the corresponding bytes scanned amongst these iterations. See our numbers within the Run Athena question with Parquet Column Indexes part.

   import boto3
   import time
   
   # Configuration
   DATABASE = "parquet_column_index_blog"
   OUTPUT_LOCATION = "s3://amzn-s3-demo-bucket/athena_parquet_column_index_blog/query_results/"
   
   def run_athena_query(question: str, database: str, output_location: str):
       athena_client = boto3.shopper('athena')
       
       response = athena_client.start_query_execution(
           QueryString=question,
           QueryExecutionContext={'Database': database},
           ResultConfiguration={'OutputLocation': output_location}
       )
       
       query_execution_id = response['QueryExecutionId']
       
       whereas True:
           outcome = athena_client.get_query_execution(QueryExecutionId=query_execution_id)
           state = outcome['QueryExecution']['Status']['State']
           
           if state in ['SUCCEEDED', 'FAILED', 'CANCELLED']:
               break
           
           time.sleep(5)
       
       if state != 'SUCCEEDED':
           increase Exception(f"Question failed with state: {state}")
       
       stats = outcome['QueryExecution']['Statistics']
       
       return {
           'execution_time_sec': stats['EngineExecutionTimeInMillis'] / 1000,
           'data_scanned_gb': stats['DataScannedInBytes'] / (1024 ** 3)
       }
   
   
   def benchmark_query(question: str, database: str, output_location: str, num_runs: int = 5):
       outcomes = []
       
       for i in vary(num_runs):
           stats = run_athena_query(question, database, output_location)
           outcomes.append(stats)
       
       best_run = min(outcomes, key=lambda r: r['execution_time_sec'])
       
       execution_time = spherical(best_run['execution_time_sec'], 1)
       data_scanned = spherical(best_run['data_scanned_gb'], 1)
       
       print(f"Execution time: {execution_time} sec")
       print(f"Knowledge scanned: {data_scanned} GB")
   
   
   QUERY = """
   SELECT cs_item_sk,
       SUM(cs_quantity) as total_orders,
       AVG(cs_ship_date_sk - cs_sold_date_sk) as avg_ship_delay_days,
       MIN(cs_ship_date_sk - cs_sold_date_sk) as min_ship_delay,
       MAX(cs_ship_date_sk - cs_sold_date_sk) as max_ship_delay,
       SUM(
           CASE
               WHEN cs_ship_date_sk - cs_sold_date_sk > 7 THEN 1 ELSE 0
           END
       ) as late_shipments,
       SUM(
           CASE
               WHEN cs_ship_date_sk - cs_sold_date_sk > 7 THEN 1 ELSE 0
           END
       ) * 100.0 / COUNT(*) as late_shipment_pct,
       AVG(cs_ext_ship_cost) as avg_shipping_cost
   FROM parquet_column_index_blog.catalog_sales
   WHERE cs_item_sk IN (
           SELECT cs_item_sk
           FROM parquet_column_index_blog.catalog_sales
           WHERE cs_item_sk IS NOT NULL
           GROUP BY cs_item_sk
           ORDER BY SUM(cs_quantity) DESC
           LIMIT 10
       )
       AND cs_ship_date_sk IS NOT NULL
       AND cs_sold_date_sk IS NOT NULL
   GROUP BY cs_item_sk
   ORDER BY avg_ship_delay_days DESC;
   """
   
   # Run benchmark
   benchmark_query(QUERY, DATABASE, OUTPUT_LOCATION, num_runs=5)

Type the catalog_sales desk

Earlier than rerunning the question with Athena utilizing Parquet Column Indexes, it is advisable kind the catalog_sales desk by the cs_item_sk column. Within the previous question, there’s a dynamic filter as a subquery on the cs_item_sk column:

cs_item_sk IN (
        SELECT cs_item_sk
        FROM parquet_column_index_blog.catalog_sales
        WHERE cs_item_sk IS NOT NULL
        GROUP BY cs_item_sk
        ORDER BY SUM(cs_quantity) DESC
        LIMIT 10
    )

When executing this question, Athena pushes down the filter predicate to the info supply stage, fetching solely rows that match the highest 10 most ordered gadgets. To maximise web page pruning with Parquet Column Indexes, rows with the identical cs_item_sk values needs to be saved close to one another within the Parquet file. With out sorting, matching values might be scattered throughout many pages, forcing Athena to learn extra information. Sorting the desk by cs_item_sk clusters related values collectively, enabling Athena to learn fewer pages.

Let’s study the Parquet Column Indexes in one of many Parquet recordsdata to grasp how the info within the catalog_sales desk is at present organized. First, obtain the Parquet file from the cs_sold_date_sk = 2450815 partition and set up the open-source parquet-cli software in your native machine. Exchange with the trail to the downloaded Parquet file, then run the next command in your native machine:

This shows Parquet Column Indexes for all columns. For brevity, solely the primary 11 pages of the cs_item_sk column from the primary row group are proven within the following instance:

row-group 0:
column index for column cs_item_sk:
Boundary order: UNORDERED
         null_count  min  max
page-0            0    4  359989
page-1            0    2  359996
page-2            0   10  359995
page-3            0   13  359996
page-4            0   22  359989
page-5            0   25  359984
page-6            0   13  359989
page-7            0   56  359990
page-8            0   14  359984
page-9            0    7  359978
page-10           0    1  359998

Discover that almost each web page comprises a variety of values. This overlap means Athena can not eradicate pages when filtering with Parquet Column Indexes on cs_item_sk. For instance, trying to find cs_item_sk = 100 requires scanning every of the 11 pages as a result of the worth 100 falls inside each web page’s min/max vary. With this overlap, enabling Athena to make use of Parquet Column Indexes would offer no efficiency profit. Sorting the info by cs_item_sk eliminates this overlap, creating distinct, non-overlapping ranges for every web page. To make Parquet Column Indexes more practical, kind the desk by finishing the next step:

1. Create a brand new SQL cell within the pocket book and confirm the connection sort is Athena (Spark). Execute the question to kind the cs_item_sk column values of the catalog_sales desk in ascending order and to place all of the null values in the previous few Parquet pages. New Iceberg information recordsdata might be generated from this question.

   CALL spark_catalog.system.rewrite_data_files(
   desk => 'parquet_column_index_blog.catalog_sales', 
   technique => 'kind', 
   sort_order => 'cs_item_sk ASC NULLS LAST', 
   choices => map('target-file-size-bytes', '1073741824', 
   'rewrite-all', 'true', 'max-concurrent-file-group-rewrites', '200'));

Operating the parquet column-index command on the sorted information file from the cs_sold_date_sk = 2450815 partition exhibits that the Parquet Column Indexes at the moment are sorted and have non-overlapping ranges. The primary 11 pages of the cs_item_sk column from the primary row group are proven within the following instance:

row-group 0:
column index for column cs_item_sk:
Boundary order: ASCENDING
         null_count  min    max
page-0           0      1   5282
page-1           0   5282  10556
page-2           0  10556  15842
page-3           0  15842  21154
page-4           0  21154  26434
page-5           0  26434  31669
page-6           0  31669  36916
page-7           0  36916  42205
page-8           0  42205  47528
page-9           0  47528  52808
page-10          0  52808  58189

Now when trying to find cs_item_sk = 100, Athena solely must learn page-0, skipping the remaining 10 pages solely.

Run Athena question with Parquet Column Indexes

Now that the info is sorted to eradicate overlapping pages inside the row teams for the cs_item_sk column, we run two experiments on the sorted information. The primary measures the influence of sorting alone, and the second measures the mixed impact of sorting with Parquet Column Indexes.

1. Create a brand new Python cell within the pocket book. Execute the identical script within the part Run Athena question with out Parquet Column Indexes and be aware of the question runtime and bytes scanned outcomes. This measures the efficiency of querying sorted information with out utilizing Parquet Column Indexes.
2. Create a brand new Python cell within the pocket book. Execute the next Python script to set the use_iceberg_parquet_column_index desk property to true for the catalog_sales desk within the AWS Glue Knowledge Catalog.

   import boto3
   
   def add_iceberg_parquet_column_index(database_name: str, table_name: str):
       glue_client = boto3.shopper('glue')
       
       # Get present desk definition
       response = glue_client.get_table(DatabaseName=database_name, Title=table_name)
       desk = response['Table']
       
       # Construct TableInput with solely allowed fields
       table_input = {'Title': desk['Name']}
       
       allowed_fields = [
           'Description', 'Owner', 'LastAccessTime', 'LastAnalyzedTime',
           'Retention', 'StorageDescriptor', 'PartitionKeys', 'ViewOriginalText',
           'ViewExpandedText', 'TableType', 'Parameters', 'TargetTable'
       ]
       
       for area in allowed_fields:
           if area in desk:
               table_input[field] = desk[field]
       
       # Add the property
       if 'Parameters' not in table_input:
           table_input['Parameters'] = {}
       table_input['Parameters']['use_iceberg_parquet_column_index'] = 'true'
       
       # Replace the desk
       glue_client.update_table(DatabaseName=database_name, TableInput=table_input)
   
   # Utilization
   add_iceberg_parquet_column_index("parquet_column_index_blog", "catalog_sales")

3. Create a brand new Python cell within the pocket book. Execute the identical script within the part Run Athena question with out Parquet Column Indexes and be aware of the question runtime and bytes scanned outcomes. This measures the efficiency of querying sorted information utilizing Parquet Column Indexes.

Athena question time and bytes scanned enchancment

The next desk summarizes the outcomes from every experiment. The proportion enhancements for the sorted experiments are measured towards the unsorted baseline.

Suggestions

To maximise Athena’s means to make use of Parquet Column Indexes and obtain optimum question efficiency, we suggest the next.

1. Type information by continuously filtered columns. This enables Athena to effectively learn Parquet Column Indexes and skip irrelevant pages, probably decreasing scan time. When information is sorted by a filter column, related values are clustered collectively inside pages. As a result of Parquet Column Indexes retailer min/max values for every web page, Athena can rapidly decide which pages include matching values and skip the remaining.
2. Type information by high-cardinality columns. This creates distinct worth ranges between pages, maximizing the chance for Athena to skip pages throughout question execution. Excessive-cardinality (many distinct values) columns produce non-overlapping min/max ranges throughout pages, permitting Athena to extra successfully filter out irrelevant pages. In distinction, low-cardinality columns corresponding to boolean or standing fields lead to overlapping ranges throughout many pages, decreasing the variety of skipped pages.

Clear up

When you’ve got completed the steps on this submit, full the next cleanup actions to keep away from incurring ongoing costs:

1. Create a brand new SQL cell within the pocket book and set the connection sort to Athena (Spark). Execute the next command to drop the parquet_column_index_blog database and the catalog_sales desk.

   DROP DATABASE parquet_column_index_blog CASCADE;

2. Delete the Iceberg desk information and the Athena question outcomes out of your S3 bucket.
3. Delete the SageMaker Unified Studio IAM-based area whether it is now not wanted.

Conclusion

On this submit, we confirmed you ways Athena makes use of Parquet Column Indexes to hurry up queries and cut back the variety of bytes scanned. Through the use of Parquet Column Indexes, Athena can skip irrelevant information pages to enhance question efficiency, particularly for queries with selective filters on sorted information. Discuss with Optimize Iceberg tables to study extra about this characteristic and take a look at it out by yourself queries.

In regards to the Writer