Comparing the features and performance of different AWS analytics services for Extract, Load, Transform (ELT)

Introduction

According to Wikipedia, “Extract, load, transform (ELT) is an alternative to extract, transform, load (ETL) used with data lake implementations. In contrast to ETL, in ELT models the data is not transformed on entry to the data lake but stored in its original raw format. This enables faster loading times. However, ELT requires sufficient processing power within the data processing engine to carry out the transformation on demand, to return the results in a timely manner.”

As capital investments and customer demand continue to drive the growth of the cloud-based analytics market, the choice of tools seems endless, and that can be a problem. Customers face a constant barrage of commercial and open-source tools for their batch, streaming, and interactive exploratory data analytics needs. The major Cloud Service Providers (CSPs) have even grown to a point where they now offer multiple services to accomplish similar analytics tasks.

This post will examine the choice of analytics services available on AWS capable of performing ELT. Specifically, this post will compare the features and performance of AWS Glue Studio, Amazon Glue DataBrew, Amazon Athena, and Amazon EMR using multiple ELT use cases and service configurations.

Analytics Use Case

We will address a simple yet common analytics challenge for this comparison — preparing a nightly data feed for analysis the next day. Each night a batch of approximately 1.2 GB of raw CSV-format healthcare data will be exported from a Patient Administration System (PAS) and uploaded to Amazon S3. The data must be cleansed, deduplicated, refined, normalized, and made available to the Data Science team the following morning. The team of Data Scientists will perform complex data analytics on the data and build machine learning models designed for early disease detection and prevention.

Sample Dataset

The dataset used for this comparison is generated by Synthea, an open-source patient population simulation. The high-quality, synthetic, realistic patient data and associated health records cover every aspect of healthcare. The dataset contains the patient-related healthcare history for allergies, care plans, conditions, devices, encounters, imaging studies, immunizations, medications, observations, organizations, patients, payers, procedures, providers, and supplies.

The Synthea dataset was first introduced in my March 2021 post examining the handling of sensitive PII data using Amazon Macie: Data Lakes: Discovery, Security, and Privacy of Sensitive Data.

The Synthea synthetic patient data is available in different record volumes and various data formats, including HL7 FHIR, C-CDA, and CSV. We will use CSV-format data files for this post. Since this post seeks to measure the performance of different AWS ELT-capable services, we will use a larger version of the Synthea dataset containing hundreds of thousands to millions of records.

AWS Glue Data Catalog

The dataset comprises nine uncompressed CSV files uploaded to Amazon S3 and cataloged to an AWS Glue Data Catalog, a persistent metadata store, using an AWS Glue Crawler.

Test Cases

We will use three data preparation test cases based on the Synthea dataset to examine the different AWS ELT-capable services.

Test Case 1: Encounters for Symptom

An encounter is a health care contact between the patient and the provider responsible for diagnosing and treating the patient. In our first test case, we will process 1.26M encounters records for an ongoing study of patient symptoms by our Data Science team.

	id	date	patient	code	description	reasoncode	reasondescription
	714fd61a-f9fd-43ff-87b9-3cc45a3f1e53	2014-01-09	33f33990-ae8b-4be8-938f-e47ad473abfe	185345009	Encounter for symptom	444814009	Viral sinusitis (disorder)
	23e07532-8b96-4d05-b14e-d4c5a5288ed2	2014-08-18	33f33990-ae8b-4be8-938f-e47ad473abfe	185349003	Outpatient Encounter
	45044100-aaba-4209-8ad1-15383c76842d	2015-07-12	33f33990-ae8b-4be8-938f-e47ad473abfe	185345009	Encounter for symptom	36971009	Sinusitis (disorder)
	ffdddbfb-35e8-4a74-a801-89e97feed2f3	2014-08-12	36d131ee-dd5b-4acb-acbe-19961c32c099	185345009	Encounter for symptom	444814009	Viral sinusitis (disorder)
	352d1693-591a-4615-9b1b-f145648f49cc	2016-05-25	36d131ee-dd5b-4acb-acbe-19961c32c099	185349003	Outpatient Encounter
	4620bd2f-8010-46a9-82ab-8f25eb621c37	2016-10-07	36d131ee-dd5b-4acb-acbe-19961c32c099	185345009	Encounter for symptom	195662009	Acute viral pharyngitis (disorder)
	815494d8-2570-4918-a8de-fd4000d8100f	2010-08-02	660bec03-9e58-47f2-98b9-2f1c564f3838	698314001	Consultation for treatment
	67ec5c2d-f41e-4538-adbe-8c06c71ddc35	2010-11-22	660bec03-9e58-47f2-98b9-2f1c564f3838	170258001	Outpatient Encounter
	dbe481ce-b961-4f43-ac0a-07fa8cfa8bdd	2012-11-21	660bec03-9e58-47f2-98b9-2f1c564f3838	50849002	Emergency room admission
	b5f1ab7e-5e67-4070-bcf0-52451eb20551	2013-12-04	660bec03-9e58-47f2-98b9-2f1c564f3838	185345009	Encounter for symptom	10509002	Acute bronchitis (disorder)

view raw encounters.csv hosted with ❤ by GitHub

Data preparation includes the following steps:

1. Load 1.26M encounter records using the existing AWS Glue Data Catalog table.
2. Remove any duplicate records.
3. Select only the records where the description column contains “Encounter for symptom.”
4. Remove any rows with an empty reasoncodes column.
5. Extract a new year, month, and day column from the date column.
6. Remove the date column.
7. Write resulting dataset back to Amazon S3 as Snappy-compressed Apache Parquet files, partitioned by year, month, and day.
8. Given the small resultset, bucket the data such that only one file is written per day partition to minimize the impact of too many small files on future query performance.
9. Catalog resulting dataset to a new table in the existing AWS Glue Data Catalog, including partitions.

Test Case 2: Observations

Clinical observations ensure that treatment plans are up-to-date and correctly administered and allow healthcare staff to carry out timely and regular bedside assessments. We will process 5.38M encounters records for our Data Science team in our second test case.

	date	patient	encounter	code	description	value	units
	2011-07-02	33f33990-ae8b-4be8-938f-e47ad473abfe	673daa98-67e9-4e80-be46-a0b547533653	8302-2	Body Height	175.76	cm
	2011-07-02	33f33990-ae8b-4be8-938f-e47ad473abfe	673daa98-67e9-4e80-be46-a0b547533653	29463-7	Body Weight	56.51	kg
	2011-07-02	33f33990-ae8b-4be8-938f-e47ad473abfe	673daa98-67e9-4e80-be46-a0b547533653	39156-5	Body Mass Index	18.29	kg/m2
	2011-07-02	33f33990-ae8b-4be8-938f-e47ad473abfe	673daa98-67e9-4e80-be46-a0b547533653	8480-6	Systolic Blood Pressure	119.0	mmHg
	2011-07-02	33f33990-ae8b-4be8-938f-e47ad473abfe	673daa98-67e9-4e80-be46-a0b547533653	8462-4	Diastolic Blood Pressure	77.0	mmHg
	2012-06-17	33f33990-ae8b-4be8-938f-e47ad473abfe	be0aa510-645e-421b-ad21-8a1ab442ca48	8302-2	Body Height	177.25	cm
	2012-06-17	33f33990-ae8b-4be8-938f-e47ad473abfe	be0aa510-645e-421b-ad21-8a1ab442ca48	29463-7	Body Weight	59.87	kg
	2012-06-17	33f33990-ae8b-4be8-938f-e47ad473abfe	be0aa510-645e-421b-ad21-8a1ab442ca48	39156-5	Body Mass Index	19.05	kg/m2
	2012-06-17	33f33990-ae8b-4be8-938f-e47ad473abfe	be0aa510-645e-421b-ad21-8a1ab442ca48	8480-6	Systolic Blood Pressure	113.0	mmHg
	2012-03-26	36d131ee-dd5b-4acb-acbe-19961c32c099	296a1fd4-56de-451c-a5fe-b50f9a18472d	8302-2	Body Height	174.17	cm

view raw observations.csv hosted with ❤ by GitHub

Data preparation includes the following steps:

1. Load 5.38M observation records using the existing AWS Glue Data Catalog table.
2. Remove any duplicate records.
3. Extract a new year, month, and day column from the date column.
4. Remove the date column.
5. Write resulting dataset back to Amazon S3 as Snappy-compressed Apache Parquet files, partitioned by year, month, and day.
6. Given the small resultset, bucket the data such that only one file is written per day partition to minimize the impact of too many small files on future query performance.
7. Catalog resulting dataset to a new table in the existing AWS Glue Data Catalog, including partitions.

Test Case 3: Sinusitis Study

A medical condition is a broad term that includes all diseases, lesions, and disorders. In our second test case, we will join the conditions records with the patient records and filter for any condition containing the term ‘sinusitis’ in preparation for our Data Science team.

	start	stop	patient	encounter	code	description
	2012-09-05	2012-10-16	bc33b032-8e41-4d16-bc7e-00b674b6b9f8	05a6ef43-d690-455e-ab2f-1ea19d902274	44465007	Sprain of ankle
	2014-09-08	2014-09-28	bc33b032-8e41-4d16-bc7e-00b674b6b9f8	1cdcbe46-caaf-4b3f-b58c-9ca9ccb13013	283371005	Laceration of forearm
	2014-11-28	2014-12-13	bc33b032-8e41-4d16-bc7e-00b674b6b9f8	b222e257-98da-4a1b-a46c-45d5ad01bbdc	195662009	Acute viral pharyngitis (disorder)
	1980-01-09		01858c8d-f81c-4a95-ab4f-bd79fb62b284	ffbd4177-280a-4a08-a1af-9770a06b5146	40055000	Chronic sinusitis (disorder)
	1989-06-25		01858c8d-f81c-4a95-ab4f-bd79fb62b284	ffbd4177-280a-4a08-a1af-9770a06b5146	201834006	Localized primary osteoarthritis of the hand
	1996-01-07		01858c8d-f81c-4a95-ab4f-bd79fb62b284	ffbd4177-280a-4a08-a1af-9770a06b5146	196416002	Impacted molars
	2016-02-07		01858c8d-f81c-4a95-ab4f-bd79fb62b284	748cda45-c267-46b2-b00d-3b405a44094e	15777000	Prediabetes
	2016-04-27	2016-05-20	01858c8d-f81c-4a95-ab4f-bd79fb62b284	a64734f1-5b21-4a59-b2e8-ebfdb9058f8b	444814009	Viral sinusitis (disorder)
	2014-02-06	2014-02-19	d32e9ad2-4ea1-4bb9-925d-c00fe85851ae	c64d3637-8922-4531-bba5-f3051ece6354	43878008	Streptococcal sore throat (disorder)
	1982-05-18		08858d24-52f2-41dd-9fe9-cbf1f77b28b2	3fff3d52-a769-475f-b01b-12622f4fee17	368581000119106	Neuropathy due to type 2 diabetes mellitus (disorder)

view raw conditions.csv hosted with ❤ by GitHub

Data preparation includes the following steps:

1. Load 483k condition records using the existing AWS Glue Data Catalog table.
2. Inner join the condition records with the 132k patient records based on patient ID.
3. Remove any duplicate records.
4. Drop approximately 15 unneeded columns.
5. Select only the records where the description column contains the term “sinusitis.”
6. Remove any rows with empty ethnicity, race, gender, or marital columns.
7. Create a new column, condition_age, based on a calculation of the age in days at which the patient’s condition was diagnosed.
8. Write the resulting dataset back to Amazon S3 as Snappy-compressed Apache Parquet-format files. No partitions are necessary.
9. Given the small resultset, bucket the data such that only one file is written to minimize the impact of too many small files on future query performance.
10. Catalog resulting dataset to a new table in the existing AWS Glue Data Catalog.

AWS ELT Options

There are numerous options on AWS to handle the batch transformation use case described above; a non-exhaustive list includes:

1. AWS Glue Studio (UI-driven with AWS Glue PySpark Extensions)
2. Amazon Glue DataBrew
3. Amazon Athena
4. Amazon EMR with Apache Spark
5. AWS Glue Studio (Apache Spark script)
6. AWS Glue Jobs (Legacy jobs)
7. Amazon EMR with Presto
8. Amazon EMR with Trino
9. Amazon EMR with Hive
10. AWS Step Functions and AWS Lambda
11. Amazon Redshift Spectrum
12. Partner solutions on AWS, such as Databricks, Snowflake, Upsolver, StreamSets, Stitch, and Fivetran
13. Self-managed custom solutions using a combination of OSS, such as dbt, Airbyte, Dagster, Meltano, Apache NiFi, Apache Drill, Apache Beam, Pandas, Apache Airflow, and Kubernetes

For this comparison, we will choose the first five options listed above to develop our ELT data preparation pipelines: AWS Glue Studio (UI-driven job creation with AWS Glue PySpark Extensions), Amazon Glue DataBrew, Amazon Athena, Amazon EMR with Apache Spark, and AWS Glue Studio (Apache Spark script).

AWS Glue Studio

According to the documentation, “AWS Glue Studio is a new graphical interface that makes it easy to create, run, and monitor extract, transform, and load (ETL) jobs in AWS Glue. You can visually compose data transformation workflows and seamlessly run them on AWS Glue’s Apache Spark-based serverless ETL engine. You can inspect the schema and data results in each step of the job.”

AWS Glue Studio’s visual job creation capability uses the AWS Glue PySpark Extensions, an extension of the PySpark Python dialect for scripting ETL jobs. The extensions provide easier integration with AWS Glue Data Catalog and other AWS-managed data services. As opposed to using the graphical interface for creating jobs with AWS Glue PySpark Extensions, you can also run your Spark scripts with AWS Glue Studio. In fact, we can use the exact same scripts run on Amazon EMR.

For the tests, we are using the G.2X worker type, Glue version 3.0 (Spark 3.1.1 and Python 3.7), and Python as the language choice for this comparison. We will test three worker configurations using both UI-driven job creation with AWS Glue PySpark Extensions and Apache Spark script options:

• 10 workers with a maximum of 20 DPUs
• 20 workers with a maximum of 40 DPUs
• 40 workers with a maximum of 80 DPUs

AWS Glue DataBrew

According to the documentation, “AWS Glue DataBrew is a visual data preparation tool that enables users to clean and normalize data without writing any code. Using DataBrew helps reduce the time it takes to prepare data for analytics and machine learning (ML) by up to 80 percent, compared to custom-developed data preparation. You can choose from over 250 ready-made transformations to automate data preparation tasks, such as filtering anomalies, converting data to standard formats, and correcting invalid values.”

DataBrew allows you to set the maximum number of DataBrew nodes that can be allocated when a job runs. For this comparison, we will test three different node configurations:

• 3 maximum nodes
• 10 maximum nodes
• 20 maximum nodes

Amazon Athena

According to the documentation, “Athena helps you analyze unstructured, semi-structured, and structured data stored in Amazon S3. Examples include CSV, JSON, or columnar data formats such as Apache Parquet and Apache ORC. You can use Athena to run ad-hoc queries using ANSI SQL, without the need to aggregate or load the data into Athena.”

Although Athena is classified as an ad-hoc query engine, using a CREATE TABLE AS SELECT (CTAS) query, we can create a new table in the AWS Glue Data Catalog and write to Amazon S3 from the results of a SELECT statement from another query. That other query statement performs a transformation on the data using SQL.

	— Purpose: Process data for sinusitis study using Amazon Athena
	— Author: Gary A. Stafford (January 2022)
	CREATE TABLE "sinusitis_athena" WITH (
	format = 'Parquet',
	write_compression = 'SNAPPY',
	external_location = 's3://databrew-demo-111222333444-us-east-1/sinusitis_athena/',
	bucketed_by = ARRAY['patient'],
	bucket_count = 1
	) AS
	SELECT DISTINCT
	patient,
	code,
	description,
	date_diff(
	'day',
	date(substr(birthdate, 1, 10)),
	date(substr(start, 1, 10))
	) as condition_age,
	marital,
	race,
	ethnicity,
	gender
	FROM conditions AS c,
	patients AS p
	WHERE c.patient = p.id
	AND gender <> ''
	AND ethnicity <> ''
	AND race <> ''
	AND marital <> ''
	AND description LIKE '%sinusitis%'
	ORDER BY patient, code;

view raw sinusitis_athena.sql hosted with ❤ by GitHub

	— Purpose: Process data for sinusitis study using Amazon Athena
	— Author: Gary A. Stafford (January 2022)
	CREATE TABLE "sinusitis_athena" WITH (
	format = 'Parquet',
	write_compression = 'SNAPPY',
	external_location = 's3://databrew-demo-111222333444-us-east-1/sinusitis_athena/',
	bucketed_by = ARRAY['patient'],
	bucket_count = 1
	) AS
	SELECT DISTINCT
	patient,
	code,
	description,
	date_diff(
	'day',
	date(substr(birthdate, 1, 10)),
	date(substr(start, 1, 10))
	) as condition_age,
	marital,
	race,
	ethnicity,
	gender
	FROM conditions AS c,
	patients AS p
	WHERE c.patient = p.id
	AND gender <> ''
	AND ethnicity <> ''
	AND race <> ''
	AND marital <> ''
	AND description LIKE '%sinusitis%'
	ORDER BY patient, code;

view raw sinusitis_athena.sql hosted with ❤ by GitHub

Amazon Athena is a fully managed AWS service and has no performance settings to adjust or monitor.

CTAS and Partitions

A notable limitation of Amazon Athena for the batch use case is the 100 partition limit with CTAS queries. Athena [only] supports writing to 100 unique partition and bucket combinations with CTAS. Partitioned by year, month, and day, the observations test case requires 2,558 partitions, and the observations test case requires 10,433 partitions. There is a recommended workaround using an INSERT INTO statement. However, the workaround requires additional SQL logic, computation, and most important cost. It is not practical, in my opinion, compared to other methods when a higher number of partitions are needed. To avoid the partition limit with CTAS, we will only partition by year and bucket by month when using Athena. Take this limitation into account when comparing the final results.

Amazon EMR with Apache Spark

According to the documentation, “Amazon EMR is a cloud big data platform for running large-scale distributed data processing jobs, interactive SQL queries, and machine learning (ML) applications using open-source analytics frameworks such as Apache Spark, Apache Hive, and Presto. You can quickly and easily create managed Spark clusters from the AWS Management Console, AWS CLI, or the Amazon EMR API.”

For this comparison, we are using two different Spark 3.1.2 EMR clusters:

• (1) r5.xlarge Master node and (2) r5.2xlarge Core nodes
• (1) r5.2xlarge Master node and (4) r5.2xlarge Core nodes

All Spark jobs are written in both Python (PySpark) and Scala. We are using the AWS Glue Data Catalog as the metastore for Spark SQL instead of Apache Hive.

	# Purpose: Process data for sinusitis study using either Amazon EMR and AWS Glue with PySpark
	# Author: Gary A. Stafford (January 2022)
	from pyspark.sql import SparkSession
	table_name = "sinusitis_emr_spark"
	spark = SparkSession \
	.builder \
	.appName(table_name) \
	.config("hive.metastore.client.factory.class",
	"com.amazonaws.glue.catalog.metastore.AWSGlueDataCatalogHiveClientFactory") \
	.enableHiveSupport() \
	.getOrCreate()
	spark.sql("USE synthea_patient_big_data;")
	sql_query_data = """
	SELECT DISTINCT
	patient,
	code,
	description,
	datediff(
	date(substr(start, 1, 10)),
	date(substr(birthdate, 1, 10))
	) as condition_age,
	marital,
	race,
	ethnicity,
	gender
	FROM conditions as c, patients as p
	WHERE c.patient = p.id
	AND gender <> ''
	AND ethnicity <> ''
	AND race <> ''
	AND marital <> ''
	AND description LIKE '%sinusitis%';
	"""
	df_data = spark.sql(sql_query_data)
	df_data \
	.coalesce(1) \
	.write \
	.bucketBy(1, "patient") \
	.sortBy("patient", "code") \
	.mode("overwrite") \
	.format("parquet") \
	.option("path", f"s3://databrew-demo-111222333444-us-east-1/{table_name}/") \
	.saveAsTable(name=table_name)
	# update glue table
	spark.sql(f"ALTER TABLE {table_name} SET TBLPROPERTIES ('classification'='parquet');")

view raw sinusitis-emr-spark.py hosted with ❤ by GitHub

	# Purpose: Process encounters dataset using either Amazon EMR and AWS Glue with PySpark
	# Author: Gary A. Stafford (January 2022)
	from pyspark.sql import SparkSession
	table_name = "encounter_emr_spark"
	spark = SparkSession \
	.builder \
	.appName(table_name) \
	.config("hive.metastore.client.factory.class",
	"com.amazonaws.glue.catalog.metastore.AWSGlueDataCatalogHiveClientFactory") \
	.config("hive.exec.dynamic.partition",
	"true") \
	.config("hive.exec.dynamic.partition.mode",
	"nonstrict") \
	.config("hive.exec.max.dynamic.partitions",
	"10000") \
	.config("hive.exec.max.dynamic.partitions.pernode",
	"10000") \
	.enableHiveSupport() \
	.getOrCreate()
	spark.sql("USE synthea_patient_big_data;")
	sql_query_data = """
	SELECT DISTINCT
	id,
	patient,
	code,
	description,
	reasoncode,
	reasondescription,
	year(date) as year,
	month(date) as month,
	day(date) as day
	FROM encounters
	WHERE description='Encounter for symptom';
	"""
	df_data = spark.sql(sql_query_data)
	df_data \
	.coalesce(1) \
	.write \
	.partitionBy("year", "month", "day") \
	.bucketBy(1, "patient") \
	.sortBy("patient") \
	.mode("overwrite") \
	.format("parquet") \
	.option("path", f"s3://databrew-demo-111222333444-us-east-1/{table_name}/") \
	.saveAsTable(name=table_name)
	# update glue table
	spark.sql(f"ALTER TABLE {table_name} SET TBLPROPERTIES ('classification'='parquet');")

view raw encounter_emr_spark.py hosted with ❤ by GitHub

	package main.spark.demo
	// Purpose: Process observations dataset using Spark on Amazon EMR with Scala
	// Author: Gary A. Stafford
	// Date: 2022-03-06
	import org.apache.spark.SparkContext
	import org.apache.spark.sql.{DataFrame, SparkSession}
	object Observations {
	def main(args: Array[String]): Unit = {
	val (spark: SparkSession, sc: SparkContext) = createSession
	performELT(spark, sc)
	}
	private def createSession = {
	val spark: SparkSession = SparkSession.builder
	.appName("Observations ELT App")
	.config("hive.metastore.client.factory.class",
	"com.amazonaws.glue.catalog.metastore.AWSGlueDataCatalogHiveClientFactory")
	.config("hive.exec.dynamic.partition",
	"true")
	.config("hive.exec.dynamic.partition.mode",
	"nonstrict")
	.config("hive.exec.max.dynamic.partitions",
	"10000")
	.config("hive.exec.max.dynamic.partitions.pernode",
	"10000")
	.enableHiveSupport()
	.getOrCreate()
	val sc: SparkContext = spark.sparkContext
	sc.setLogLevel("INFO")
	(spark, sc)
	}
	private def performELT(spark: SparkSession, sc: SparkContext) = {
	val tableName: String = sc.getConf.get("spark.executorEnv.TABLE_NAME")
	val dataLakeBucket: String = sc.getConf.get("spark.executorEnv.DATA_LAKE_BUCKET")
	spark.sql("USE synthea_patient_big_data;")
	val sql_query_data: String =
	"""
	SELECT DISTINCT
	patient,
	encounter,
	code,
	description,
	value,
	units,
	year(date) as year,
	month(date) as month,
	day(date) as day
	FROM observations
	WHERE date <> 'date';
	"""
	val observationsDF: DataFrame = spark
	.sql(sql_query_data)
	observationsDF
	.coalesce(1)
	.write
	.partitionBy("year", "month", "day")
	.bucketBy(1, "patient")
	.sortBy("patient")
	.mode("overwrite")
	.format("parquet")
	.option("path", s"s3://${dataLakeBucket}/${tableName}/")
	.saveAsTable(tableName = tableName)
	spark.sql(s"ALTER TABLE ${tableName} SET TBLPROPERTIES ('classification'='parquet');")
	}
	}

view raw Observations.scala hosted with ❤ by GitHub

Results

Data pipelines were developed and tested for each of the three test cases using the five chosen AWS ELT services and configuration variations. Each pipeline was then run 3–5 times, for a total of approximately 150 runs. The resulting AWS Glue Data Catalog table and data in Amazon S3 were deleted between each pipeline run. Each new run created a new data catalog table and wrote new results to Amazon S3. The median execution times from these tests are shown below.

Although we can make some general observations about the execution times of the chosen AWS services, the results are not meant to be a definitive guide to performance. An accurate comparison would require a deeper understanding of how each of these managed services works under the hood, in order to both optimize and balance their compute profiles correctly.

Amazon Athena

The Resultset column contains the final number of records written to Amazon S3 by Athena. The results contain the data pipeline’s median execution time and any additional data points.

AWS Glue Studio (AWS Glue PySpark Extensions)

Tests were run with three different configurations for AWS Glue Studio using the graphical interface for creating jobs with AWS Glue PySpark Extensions. Times for each configuration were nearly identical.

AWS Glue Studio (Apache PySpark script)

As opposed to using the graphical interface for creating jobs with AWS Glue PySpark Extensions, you can also run your Apache Spark scripts with AWS Glue Studio. The tests were run with the same three configurations as above. The execution times compared to the Amazon EMR tests, below, are almost identical.

Amazon EMR with Apache Spark

Tests were run with three different configurations for Amazon EMR with Apache Spark using PySpark. The first set of results is for the 2-node EMR cluster. The second set of results is for the 4-node cluster. The third set of results is for the same 4-node cluster in which the data was not bucketed into a single file within each partition. Compare the execution times and the number of objects against the previous set of results. Too many small files can negatively impact query performance.

It is commonly stated that “Scala is almost ten times faster than Python.” However, with Amazon EMR, jobs written in Python (PySpark) and Scala had similar execution times for all three test cases.

Amazon Glue DataBrew

Tests were run with three different configurations Amazon Glue DataBrew, including 3, 10, and 20 maximum nodes. Times for each configuration were nearly identical.

Observations

1. All tested AWS services can read and write to an AWS Glue Data Catalog and the underlying datastore, Amazon S3. In addition, they all work with the most common analytics data file formats.
2. All tested AWS services have rich APIs providing access through the AWS CLI and SDKs, which support multiple programming languages.
3. Overall, AWS Glue Studio, using the AWS Glue PySpark Extensions, appears to be the most capable ELT tool of the five services tested and with the best performance.
4. Both AWS Glue DataBrew and AWS Glue Studio are no-code or low-code services, democratizing access to data for non-programmers. Conversely, Amazon Athena requires knowledge of ANSI SQL, and Amazon EMR with Apache Spark requires knowledge of Scala or Python. Be cognizant of the potential trade-offs from using no-code or low-code services on observability, configuration control, and automation.
5. Both AWS Glue DataBrew and AWS Glue Studio can write a custom Parquet writer type optimized for Dynamic Frames, GlueParquet. One potential advantage, a pre-computed schema is not required before writing.
6. There is a slight ‘cold-start’ with Glue Studio. Studio startup times ranged from 7 seconds to 2 minutes and 4 seconds in the tests. However, the lower execution time of AWS Glue Studio compared to Amazon EMR with Spark and AWS Glue DataBrew in the tests offsets any initial cold-start time, in my opinion.
7. Changing the maximum number of units from 3 to 10 to 20 for AWS Glue DataBrew made negligible differences in job execution times. Given the nearly identical execution times, it is unclear exactly how many units are being used by the job. More importantly, how many DataBrew node hours we are being billed for. These are some of the trade-offs with a fully-managed service — visibility and fine-tuning configuration.
8. Similarly, with AWS Glue Studio, using either 10 workers w/ max. 20 DPUs, 20 workers w/ max. 40 DPUs, or 40 workers w/ max. 80 DPUs resulted in nearly identical executions times.
9. Amazon Athena had the fastest execution times but is limited by the 100 partition limit for large CTAS resultsets. Athena is not practical, in my opinion, compared to other ELT methods, when a higher number of partitions are needed.
10. It is commonly stated that “Scala is almost ten times faster than Python.” However, with Amazon EMR, jobs written in Python (PySpark) and Scala had almost identical execution times for all three test cases.
11. Using Amazon EMR with EC2 instances takes about 9 minutes to provision a new cluster for this comparison fully. Given nearly identical execution times to AWS Glue Studio with Apache Spark scripts, Glue has the clear advantage of nearly instantaneous startup times.
12. AWS recently announced Amazon EMR Serverless. Although this service is still in Preview, this new version of EMR could potentially reduce or eliminate the lengthy startup time for ephemeral clusters requirements.
13. Although not discussed, scheduling the data pipelines to run each night was a requirement for our use case. AWS Glue Studio jobs and AWS Glue DataBrew jobs are schedulable from those services. For Amazon EMR and Amazon Athena, we could use Amazon Managed Workflows for Apache Airflow (MWAA), AWS Data Pipeline, or AWS Step Functions combined with Amazon CloudWatch Events Rules to schedule the data pipelines.

Conclusion

Customers have many options for ELT — the cleansing, deduplication, refinement, and normalization of raw data. We examined chosen services on AWS, each capable of handling the analytics use case presented. The best choice of tools depends on your specific ELT use case and performance requirements.

---

This blog represents my own viewpoints and not of my employer, Amazon Web Services (AWS). All product names, logos, and brands are the property of their respective owners.