Aggregating Data with Glue

Welcome back! In the previous lesson, you learned how to register your processed Parquet data in the AWS Glue Catalog using a Glue Crawler. This made your data discoverable and ready for analysis with tools like Amazon Athena. Now that your data is organized and cataloged, the next step is to prepare it for business analysis by summarizing or aggregating it.

Aggregation is a key part of any data pipeline. It allows you to turn large amounts of raw data into meaningful summaries, such as daily totals or event counts. These summaries are much easier to analyze and are often what business users and analysts need for reporting and decision-making. AWS Glue ETL jobs make it possible to automate this process so you can regularly create up-to-date summary tables from your raw or processed data.

In this lesson, you will learn how to build a Glue ETL job that reads processed data from your Glue Catalog, performs aggregations using PySpark, and writes the results back to S3 in a format that is ready for further analysis.

Before we dive into the example, let’s quickly review the main components you’ll see in a Glue ETL script for aggregation. If you’ve followed along with earlier lessons, some of these will be familiar, but I’ll point out what’s new.

A typical Glue ETL script starts by setting up the job environment. This includes creating a GlueContext, which is the main entry point for working with AWS Glue, and a SparkContext, which is needed for running Spark jobs. You’ll also see the use of getResolvedOptions to read job arguments, such as the source database and table, and the target S3 bucket and prefix where you want to write your results.

One important concept in Glue is the DynamicFrame, which is a flexible data structure designed for semi-structured data. However, for aggregation tasks, it’s common to convert a DynamicFrame to a Spark DataFrame, which provides powerful functions for grouping and summarizing data.

Let’s look at how you can aggregate data using PySpark within a Glue ETL job. Suppose you have a table of user events, and you want to create a daily summary that counts the number of events and sums up the revenue for each day.

Here’s a code example that shows how to do this. This script assumes your processed data is already registered in the Glue Catalog, and you have the necessary job arguments set up.

import sys
from awsglue.transforms import *
from awsglue.utils import getResolvedOptions
from pyspark.context import SparkContext
from awsglue.context import GlueContext
from awsglue.job import Job
from pyspark.sql import functions as F

args = getResolvedOptions(sys.argv, [
    'JOB_NAME',
    'SOURCE_DATABASE',
    'SOURCE_TABLE',
    'TARGET_BUCKET',
    'CURATED_PREFIX'
])

sc = SparkContext()
glueContext = GlueContext(sc)
spark = glueContext.spark_session
job = Job(glueContext)
job.init(args['JOB_NAME'], args)

df = glueContext.create_dynamic_frame.from_catalog(
    database=args['SOURCE_DATABASE'],
    table_name=args['SOURCE_TABLE']
).toDF()

agg = df.groupBy("event_date").agg(
    F.count("*").alias("total_events"),
    F.sum("revenue").alias("total_revenue")
)

agg.write.mode("overwrite").parquet(
    f"s3://{args['TARGET_BUCKET']}/{args['CURATED_PREFIX']}daily_summary/"
)

job.commit()

Let’s break down what’s happening here. The script starts by importing the necessary libraries and reading job arguments. It then creates the Spark and Glue contexts, which are required for running the ETL job. The script loads the source data from the Glue Catalog as a DynamicFrame and immediately converts it to a Spark DataFrame using .toDF(). This is important because Spark DataFrames provide the groupBy and aggregation functions you need.

The aggregation step groups the data by the event_date column. For each date, it counts the number of events and sums the revenue column. The result is a new DataFrame with one row per day, showing the total number of events and the total revenue for that day.