Categories
Data & Analytics

Bespoke Veracity Checks With AWS Glue Data Quality

In this post, I use AWS Glue Data Quality checks and rulesets to apply bespoke veracity checks to my WordPress datasets.

Table of Contents

Introduction

In my last post, I used the AWS Glue ETL Job Script Editor to write a Silver ETL Python script. Within that script and the ones prior, there are checks like:

If these checks all pass then datasets are created in various S3 buckets. But before I use these datasets for reporting and analytics, I should check their quality first.

In my Building And Automating Serverless Auto-Scaling Data Pipelines In AWS session, I talk about the Four V’s of Big Data. One of these Vs is Veracity – the measure of data’s truthfulness, accuracy and quality. AWS Glue offers veracity checks with AWS Glue Data Quality.

Launched in 2023, AWS Glue Data Quality measures and monitors data quality and veracity. It is built on top of an open-source framework, and provides a managed, serverless experience with machine learning augmentation.

Firstly, I’ll examine AWS Glue Data Quality and some of its features. Then I’ll use it to recommend rules for some of my Silver layer data objects, and then customise those recommendations as needed. Next, I’ll create and test a Glue Data Quality job using those rules. Finally, I’ll examine my Glue Data Quality costs.

AWS Glue Data Quality

This section introduces AWS Glue Data Quality and examines some of its features.

What Is Glue Data Quality?

From AWS:

AWS Glue Data Quality evaluates and monitors the quality of your data based on rules that you define. This makes it easy to identify the data that needs action. You can then monitor and evaluate changes to your datasets as they evolve over time.

https://docs.aws.amazon.com/glue/latest/dg/data-quality-gs-studio.html

AWS Glue Data Quality features include:

  • Creating & recommending sets of data quality rules.
  • Running the data quality rules as on-demand and scheduled jobs.
  • Monitoring and reporting against the data quality results.

So how do data quality rules work?

Glue Data Quality Rules

From AWS:

AWS Glue Data Quality currently supports 18 built-in rule types under four categories:

  1. Consistency rules check if data across different columns agrees by looking at column correlations.
  2. Accuracy rules check if record counts meet a set threshold and if columns are not empty, match certain patterns, have valid data types, and have valid values.
  3. Integrity rules check if duplicates exist in a dataset.
  4. Completeness rules check if data in your datasets do not have missing values.
https://aws.amazon.com/glue/faqs/

AWS Glue Data Quality rules are defined using Data Quality Definition Language (DQDL). DQDL uses a Rules list containing comma-separated rules in square brackets.

For example, this DQLD rule checks for missing values in customer-id and unique values in order-id:

Plaintext
Rules = [
   IsComplete "customer-id",
   IsUnique "order-id"
]

AWS maintains a DQDL reference that advises about syntax, structure, expressions, and rule types. Now all of this can be a lot to take in, so a good way of getting started is to get AWS to do some of the heavy lifting…

Glue Data Quality Rule Recommendations

Getting to grips with new features can be daunting. To help out, AWS Glue Data Quality can analyse data in the Glue Data Catalog. This process uses machine learning to identify and recommend rules for the analysed data. These rules can then be used and changed as needed.

Glue Data Quality recommendations are generated using Amazon’s Deequ open-source framework, which is tested on Amazon’s own petabyte-scale datasets. AWS has documented the recommendation generation process, and has released supporting videos like:

So that’s enough theory – let’s build something!

Ruleset Creation

In this section, I create a Glue Data Quality veracity ruleset for my silver-statistcs_pages dataset by generating and customising Glue’s recommendations.

Generating Recommendations

Firstly, I told Glue Data Quality to scan the dataset and recommend some rules. Two minutes later, Glue returned these:

Plaintext
Rules = [
    RowCount between 4452 and 17810,
    IsComplete "page_id",
    StandardDeviation "page_id" between 2444.94 and 2702.3,
    Uniqueness "page_id" > 0.95,
    ColumnValues "page_id" <= 8925,
    IsComplete "uri",
    ColumnLength "uri" <= 190,
    IsComplete "type",
    ColumnValues "type" in ["post","home","page","category","post_tag","archive","author","search"],
    ColumnValues "type" in ["post","home"] with threshold >= 0.94,
    ColumnLength "type" between 3 and 9,
    IsComplete "date",
    IsComplete "count",
    ColumnValues "count" in ["1","2","3","4","5","6"] with threshold >= 0.9,
    StandardDeviation "count" between 3.89 and 4.3,
    ColumnValues "count" <= 93,
    IsComplete "id",
    ColumnValues "id" in ["92","11","281","7","1143","1902","770","1217","721","1660","2169","589","371","67","484","4","898","0","691","2029","1606","2686","1020","2643","2993","1400","30","167","2394"] with threshold >= 0.89,
    StandardDeviation "id" between 820.3 and 906.65,
    ColumnValues "id" <= 3532,
    IsComplete "date_todate",
    IsComplete "date_year",
    ColumnValues "date_year" in ["2023","2024","2022"],
    ColumnValues "date_year" between 2021 and 2025,
    IsComplete "date_month",
    ColumnValues "date_month" in ["6","7","5","4","3","8","2","1","11","12","10","9"],
    ColumnValues "date_month" in ["6","7","5","4","3","8","2","1","11","12","10"] with threshold >= 0.94,
    StandardDeviation "date_month" between 3.09 and 3.41,
    ColumnValues "date_month" <= 12,
    IsComplete "date_day",
    ColumnValues "date_day" in ["13","7","12","8","6","3","19","20","17","4","9","14","1","16","2","11","5","15","10","26","21","25","24","18","27","22","28","30","23","29","31"],
    ColumnValues "date_day" in ["13","7","12","8","6","3","19","20","17","4","9","14","1","16","2","11","5","15","10","26","21","25","24","18","27","22","28","30"] with threshold >= 0.91,
    StandardDeviation "date_day" between 8.3 and 9.18,
    ColumnValues "date_day" <= 31
]

A lot is going on here, so let’s deep a little deeper.

Recommendations Analysis

As with many machine learning processes, some human validation of the results is wise before moving forward.

While Glue Data Quality can predict rules based on its ML model and the data supplied, I have years of familiarity with the data and can intuit likely future trends and patterns. As Glue currently lacks this intuition, some recommendations are more useful than others. Let’s examine some of them and I’ll elaborate.

Firstly, these recommendations are totally fine:

Plaintext
IsComplete "page_id",
IsComplete "uri",
IsComplete "date",
IsComplete "count",

IsComplete checks whether all of the values in a column are complete with no NULL values present. This is completely reasonable and should apply to all columns in the silver-statistics_pages data. An easy win.

However, some recommendations need work:

Plaintext
ColumnValues "date_year" in ["2023","2024","2022"],
ColumnValues "date_year" between 2021 and 2025,

ColumnValues runs an expression against the values in a column. These rules (which are both checking the same thing as DQDL’s BETWEEN is exclusive) state that:

date_year must be 2022, 2023 or 2024

This is fine for now, as 2024 is the current year and the first statistics are from 2022. But a post published next year will cause this rule to fail. And not because of incorrect data – because of incorrect rule configuration. Hello false positives!

Finally, some suggestions are outright wrong. For example:

Plaintext
ColumnValues "page_id" <= 8925,

This rule checks that the page_id column doesn’t exceed 8925. But page_id is a primary key! It auto-increments with every new row! So this rule will fail almost immediately, and so is completely unsuitable.

Ok so let’s fix them!

Recommendations Modifications

Firstly, let’s fix the date_year rule by replacing the range with a minimum value:

Plaintext
ColumnValues "date_year" >= 2021,

Now let’s fix the page_id rule. This column is a primary key in the WordPress MySQL database, so every value should be unique. Therefore the ruleset should check page_id for uniqueness.

As it turns out I’m spoilt for choice here! There are (at least) three relevant rules I can use:

Plaintext
IsUnique "page_id",
IsPrimaryKey "page_id",
Uniqueness "page_id" = 1.0,

Let’s examine them:

  • IsUnique checks whether all of the values in a column are unique. Exactly what I’m after.
  • IsPrimaryKey goes a step further, verifying that a column contains a primary key by checking if all of the values in the column are unique and complete (non-null).
  • Finally, Uniqueness checks the percentage of unique values in a column against a given expression. In my example, "page_id" = 1.0 states that each page_id column value must be 100% unique.

So why not use them all? Well, besides that being overkill there is a cost implication. Like many Glue services, AWS Glue Data Quality is billed by job duration (per DPU hour). If I keep all three rules then I’m doing the same check three times. This is wasteful and creates unnecessary costs.

Here, the IsPrimaryKey check most closely matches the source column (itself a primary key) so I’ll use that.

Elsewhere, I’m simplifying date_month and date_day. While these are correct:

Plaintext
ColumnValues "date_month" in ["6","7","5","4","3","8","2","1","11","12","10","9"],
ColumnValues "date_day" in ["13","7","12","8","6","3","19","20","17","4","9","14","1","16","2","11","5","15","10","26","21","25","24","18","27","22","28","30","23","29","31"],

It’s far simpler to read as:

Plaintext
ColumnValues "date_month" between 0 and 13,
ColumnValues "date_day" between 0 and 32,

Finally, I did some housekeeping to reduce the ruleset’s duration:

  • Removed all the duplicate checks. IsComplete was fine for most.
  • ColumnLength checks are gone as the WordPress database already enforces character limits.
  • StandardDeviation checks are also gone as they don’t add any value here.

Now let’s use these suggestions as a starting point for my own ruleset.

Customising A Ruleset

In addition to the above rules and changes, the following rules have been added to the silver-statistics_pages ruleset:

ColumnCount checks the dataset’s column count against a given expression. This checks there are ten columns in silver-statistics_pages:

Plaintext
ColumnCount = 10

RowCount checks a dataset’s row count against a given expression. This checks there are more than zero rows in silver-statistics_pages:

Plaintext
RowCount > 0

RowCountMatch checks the ratio of the primary dataset’s row count and a reference dataset’s row count against the given expression. This checks that the row count of silver-statistics_pages and bronze-statistics_pages are the same (100%):

Plaintext
RowCountMatch "wordpress_api.bronze-statistics_pages" = 1.0

ReferentialIntegrity checks to what extent the values of a set of columns in the primary dataset are a subset of the values of a set of columns in a reference dataset. This checks that each silver-statistics_pages ID value is present in silver-posts:

Plaintext
ReferentialIntegrity "id" "wordpress_api.silver-posts.id" = 1.0

Finally, here is my finished silver-statistics_pages ruleset:

Plaintext
# silver-statistics_pages data quality rules

Rules = [
    # all data
    ColumnCount = 10,
    RowCount > 0,
    RowCountMatch "wordpress_api.bronze-statistics_pages" = 1.0,
    
    # page_id
    IsPrimaryKey "page_id",
    
    # uri
    IsComplete "uri",
    
    # type
    IsComplete "type",
    
    # date
    IsComplete "date",
    ColumnValues "date_todate" <= now(),
    
    # count
    IsComplete "count",
    ColumnValues "count" between 0 and 1000,
    
    # id
    IsComplete "id",
    ReferentialIntegrity "id" "wordpress_api.silver-posts.id" = 1.0,
    
    # date_todate
    IsComplete "date_todate",
    ColumnValues "date_todate" <= now(),
    
    # date_year
    IsComplete "date_year",
    ColumnValues "date_year" >= 2021,
    
    # date_month
    IsComplete "date_month",
    ColumnValues "date_month" between 0 and 13,
    
    # date_day
    IsComplete "date_day",
    ColumnValues "date_day" between 0 and 32

]

Once a ruleset is created, it can be edited, cloned and run. So let’s test it out!

Ruleset Testing

In this section, I test my Glue Data Quality veracity ruleset and act on its findings. But first I need to get it running…

Job Test: Data Fetch Fail

Running my ruleset for the first time, it didn’t take long for a problem to appear:

Plaintext
Exception in User Class: java.lang.RuntimeException : 
Failed to fetch data. 
Please check the logs in CloudWatch to get more details.

Uh oh. Guess it’s time to check CloudWatch. This wasn’t an easy task the first time round!

Glue Data Quality generates two new log groups:

  • aws-glue/data-quality/error
  • aws-glue/data-quality/output

And each Data Quality job run creates five log streams:

2024 08 23 LogStream

But there’s no clear hint of where to start! So I dived in and started reading the error logs. After some time, it turned out I actually needed the output logs. Oh well.

And in an output log stream’s massive stack trace:

2024 08 23 Stacktrace
This isn’t even half of it – Ed

Was my problem:

Plaintext
Caused by:
java.io.FileNotFoundException:
No such file or directory
's3://data-lakehouse-bronze/wordpress_api/statistics_pages/statistics_pages.parquet'

No such directory? Well, there definitely is! Sounds like a permissions issue. What gives?

So, remember my RowCountMatch check? It’s trying to compare the silver-statistics_pages row count to the bronze-statistics_pages row count. Like most AWS services, AWS Glue uses an IAM role to interact with AWS resources – in this case the Bronze and Silver Lakehouse S3 buckets.

So let’s check:

  • Can the Glue Data Quality check’s IAM role read from the Silver Lakehouse S3 bucket? Yup!
  • Can it read from the Bronze one? Ah…

Adding s3:GetObject for the bronze S3 path to the Glue Data Quality check’s IAM role fixed this error. Now the job runs and returns results!

Job Test: Constraint Not Met

Next up, I got an interesting message from my ColumnValues "count" rule:

Plaintext
ColumnValues "count" between 0 and 1000

Value: 93.0 does not meet the constraint requirement!

That’s…a lot! Then I realised I’d set the rule conditions to between 0 and 1 instead of between 0 and 1000. Oops…

Then I got a confusing result from my ReferentialIntegrity "id" "wordpress_api.silver-posts.id" = 1.0 rule:

Plaintext
ReferentialIntegrity "id" "wordpress_api.silver-posts.id" = 1.0 

Value: 0.9763982102908277 does not meet the constraint requirement.

As a reminder, ReferentialIntegrity checks to what extent the values of a set of columns in the primary dataset are a subset of the values of a set of columns in a reference dataset. And because "wordpress_api.silver-statistics_pages.id" values are based entirely on "wordpress_api.silver-posts.id" values, they should be a perfect match!

Time to investigate. I launched Athena and put this query together:

SQL
SELECT 
sp.id AS stats_id
,p.id AS post_id
FROM "wordpress_api"."silver-statistics_pages" AS sp
LEFT JOIN "wordpress_api"."silver-posts" AS p 
ON sp.id = p.id

And the results quickly highlighted a problem:

2024 08 23 AthenaResults

Here, the LEFT JOIN retrieves all silver-statistics_pages IDs and each row’s matching ID from silver-posts. The empty spaces represent NULLs, where no matching silver-posts ID was found. So what’s going on? What is stats_id zero in silver-statistics_pages?

Reviewing the silver-statistics_pages uri column shows that ID zero is amazonwebshark’s home page. As the WordPress posts table doesn’t record anything about the home page, the statistics_pages table can’t link to anything in posts. So ID zero is used to prevent missing data.

Knowing this, how can I update the rule? In June 2024 AWS added DQDL WHERE clause support, so I tried to add a “where statistics_pages ID isn’t zero” condition. But in testing the editor either didn’t run the check properly or rejected my syntax entirely. So eventually I settled for changing the check’s threshold from = 1.0 to >= 0.9. Maybe something to revisit in a few months.

Run History & Reporting

So now all my rules are working, what benefits do I get? Firstly, AWS Glue shows the job’s run history including status, result and start/stop times:

2024 08 23 DQHistory

Each run is expandable, showing details like duration, overall score and each check’s output. Results are also downloadable – in testing this gave me an unreadable file but adding a JSON suffix let me view the contents:

JSON
{
	"ResultId": "dqresult-e76896fe1ab1dd3436cf12b719da726416d4e64e",
	"Score": 0.95,
	"DataSource": {
		"GlueTable": {
			"DatabaseName": "wordpress_api",
			"TableName": "silver-statistics_pages",
			"CatalogId": "973122011240"
		}
	},
	"RulesetName": "silver-statistics_pages",
	"StartedOn": "2024-08-22T17:23:20.468Z",
	"CompletedOn": "2024-08-22T17:23:45.680Z",
	"RulesetEvaluationRunId": "dqrun-a94651ef8547f426cb977c9451c39061c68aefbd",
	"RuleResults": [
		{
			"Name": "Rule_1",
			"Description": "ColumnCount = 10",
			"Result": "PASS",
			"EvaluatedMetrics": {
				"Dataset.*.ColumnCount": 10
			}
		},
		{
			"Name": "Rule_2",
			"Description": "RowCount > 0",
			"Result": "PASS",
			"EvaluatedMetrics": {
				"Dataset.*.RowCount": 8940
			}
		},
		{
			"Name": "Rule_3",
			"Description": "RowCountMatch \"wordpress_api.bronze-statistics_pages\" = 1.0",
			"Result": "PASS",
			"EvaluatedMetrics": {
				"Dataset.wordpress_api.bronze-statistics_pages.RowCountMatch": 1
			}
		},
		{
			"Name": "Rule_4",
			"Description": "IsPrimaryKey \"page_id\"",
			"Result": "PASS",
			"EvaluatedMetrics": {
				"Column.page_id.Completeness": 1,
				"Column.page_id.Uniqueness": 1
			}
		},

[REDACTED Rules 5 to 19 for space - Ed]

		{
			"Name": "Rule_20",
			"Description": "ColumnValues \"date_day\" between 0 and 32",
			"Result": "PASS",
			"EvaluatedMetrics": {
				"Column.date_day.Maximum": 31,
				"Column.date_day.Minimum": 1
			}
		}
	]
}

Finally, there’s a snapshot chart showing the results trend of the last ten runs:

2024 08 23 DQSnapshot

Although not downloadable, this can still be screen-grabbed and used to certify the data’s quality to stakeholders. Additionally, AWS has documented a visualisation solution using Lambda, S3 and Athena.

Additional Data Quality Ruleset

With the silver-statistics_pages ruleset testing complete, I added a second dataset check before I moved on. This ruleset is applied to silver-posts.

The checks are very similar to silver-statistics_pages in terms of rules and criteria. So in the interests of space I’ve committed it to my GitHub repo.

Now, let’s add my Glue Data Quality checks into my WordPress pipeline.

Ruleset Orchestration

In this section, I integrate my Glue Data Quality veracity checks into my existing WordPress Data Pipeline Step Function workflow.

Step Function Integration

As a quick reminder, here’s how the Step Function workflow currently looks:

2024 08 09 stepfunctions graph

This workflow controls the ingestion, validation, crawling and ETL processes associated with my WordPress API data. I’ll insert the quality checks between the Silver ETL job and the Silver crawler.

AWS Step Functions runs Glue Data Quality checks using the StartDataQualityRulesetEvaluationRun task. This task uses an AWS SDK integration, calling the StartDataQualityRulesetEvaluationRun API with the following parameters:

  • Data source (AWS Glue table) associated with the run.
  • IAM role to run the checks with.
  • Ruleset(s) to run.

Optional parameters are also available. In the case of my silver-statistics_pages ruleset, the API parameters are as follows:

JSON
{
  "DataSource": {
    "GlueTable": {
      "DatabaseName": "wordpress_api",
      "TableName": "silver-statistics_pages"
    }
  },
  "Role": "Glue-S3ReadOnly",
  "RulesetNames": [
    "silver-statistics_pages"
  ]
}

Because the TableName parameter is different for the silver-posts checks, each check needs a separate action. However, I can use a Parallel state because both actions can run simultaneously. This will take full advantage of AWS’s systems, yielding faster execution times for my workflow.

Here is how my Step Function workflow looks with these changes:

stepfunctions graph

This workflow is executed by an EventBridge Schedule running daily at 07:00.

Step Function Testing

Testing time! My workflow needs new IAM permissions to perform its new tasks. These are:

  • glue:StartDataQualityRulesetEvaluationRun

This lets the workflow start the silver-statistics_pages and silver-posts Data Quality jobs.

  • iam:PassRole

A Glue Data Quality job must assume an IAM role to access AWS resources successfully. Without iam:PassRole the workflow can’t do this and the check fails.

  • glue:GetTable

The workflow must access the Glue Data Catalog while running, requiring glue:GetTable on the desired region’s Data Catalog ARN to get the required metadata.

With these updates, the workflow executes successfully:

2024 08 28 SFExec

During the parallel state, both Data Quality jobs successfully start and finish within milliseconds of each other instead of running sequentially:

2024 08 28 SFResults

Cost Analysis

In this section, I examine my costs for the updated Step Function workflow.

This Cost Explorer chart runs from 16 August to the end of August. It is grouped by API Operation and excludes some APIs that aren’t part of this workload.

2024 09 01 Costs

Some notes:

  • I was experimenting with Glue Data Quality from 19 August to 22 August. This period generates the highest Glue Jobrun costs – $0.23 on the 20th and $0.27 on the 22nd.
  • The silver-statistics_pages ruleset was added to the Step Function workflow on the 26th. The silver-posts ruleset was then added on the 27th.
  • The CrawlerRun daily costs are usually $0.04, with some experiments generating higher costs.

My main costs are from Glue’s Jobrun and CrawlerRun operations, which was expected. Each ruleset costs around $0.09 a day to run, while each crawler continues to cost $0.02 a day. Beyond that I’m paying for some S3 PutObject calls, and everything else is within the free tier.

Separately, AWS has tested Data Quality rulesets of varying complexity. Their accrued costs ranged from $0.18 for the least complex to $0.54 for the most complex. So on par with mine!

Summary

In this post, I used AWS Glue Data Quality checks and rulesets to apply bespoke veracity checks to my WordPress datasets.

I think AWS Glue Data Quality is a very effective veracity tool. The simple syntax, quick execution and deep AWS integration offer a good solution to a core Data Engineering issue. It’s great that datasets can be compared with other datasets in the Glue Data Catalog, and the baked-in reporting and visuals make Glue’s findings immediately accessible to both technical engineers and non-technical stakeholders. I look forward to seeing what future Glue Data Quality releases will offer!

If this post has been useful then the button below has links for contact, socials, projects and sessions:

SharkLinkButton 1

Thanks for reading ~~^~~

Categories
Data & Analytics

Silver Layer Python ETL With The AWS Glue ETL Job Script Editor

In this post, I create my WordPress data pipeline’s Silver ETL process using Python and the AWS Glue ETL Job Script Editor.

Table of Contents

Introduction

Last time I worked on my WordPress AWS data pipeline, I produced my Bronze layer data and created a Glue Crawler to derive the schema of the Bronze S3 objects. It’s now time to start cleaning that data to prepare it for reporting, aggregation and consumption.

I’m also currently studying for the AWS Certified Data Engineer – Associate certification. While revising for this I learned the capabilities of the AWS Glue ETL Job Script Editor, and it seemed an ideal fit for my Silver ETL process. So I decided to make a post out of it and see how things went!

Firstly, I’ll examine the AWS Glue ETL Job Script Editor and how it will benefit my Silver ETL process. Then I’ll define the architecture of the Silver ETL job and how it fits into the existing data pipeline. Next, I’ll script and test the job. Finally, I’ll integrate it into the pipeline and explore the job’s costs.

Glue ETL Job Script Editor

This section examines the AWS Glue ETL Script Editor and Python Shell and considers some of Python Shell’s benefits and limitations.

Script Editor & Python Shell

Script Editor is a feature of AWS Glue. It offers serverless Spark, Ray and Python shells, enabling data transformation, preparation and cleaning with no infrastructure management. Scripts can be both uploaded and created from scratch, and version control is configurable to several Git services.

This post focuses on AWS Glue Python Shell. Introduced in 2019, Python Shell jobs suit small to medium-sized tasks as part of an ETL workflow.

Python Shell Pros

This section examines some of Python Shell’s benefits.

Low Cost

Python Shell jobs are the cheapest of the Glue job types to run. Glue charges are based on data processing units (DPUs). A single standard DPU currently provides 4 vCPU and 16 GB of memory. While regular Glue ETL jobs using Apache Spark need at least 2 DPUs, Python Shell jobs default to using only 1/16 (or 0.0625) DPU!

This can also be extended to 1 DPU, resulting in faster completion times. Like AWS Lambda, charges accrue based on resource usage and duration. So increased resource allocation can potentially create further savings.

This section was correct as of August 2024 – the latest pricing data is on the AWS Glue pricing site.

Low Barrier To Entry

Python Shell jobs offer accessibility for those from a scripting background. When creating a new script in the console, users only need to choose the engine (in this case Python) and whether the script is being uploaded or created fresh. And that’s it! No configuring interpreters, environments or dependencies.

Python Shell jobs also integrate with other AWS services. They can easily connect to data sources like S3, RDS and DynamoDB. They can be automated with Glue Workflows and Triggers. IAM can also control access to both the Python Shell job and the AWS services it interacts with.

Included Python Libraries

AWS Glue Python Shell includes a variety of built-in Python libraries that are useful for ETL tasks. These libraries cover a range of functionalities such as data processing, machine learning, and interacting with AWS services.

They include:

This AWS post has a full table of included libraries and their versions. Additional libraries can be installed and imported using PIP.

Some people will quickly see issues with this list though…

Python Shell Cons

This section examines some of Python Shell’s limitations.

Outdated Python Versions & Libraries

While the included libraries are welcome, they are also quite outdated. For example, boto3‘s included version is 1.21.21 while the current version is 1.34.150. pandas is at 1.4.2 in the table and 2.2.2 online.

This is likely due to the supported Python versions – currently Python 3.6 and Python 3.9. Now, while Python 3.9 isn’t out of support until October 2025, it was released back in October 2020 and has had three major upgrades since. Worse, Python 3.6 ended life at Christmas 2021!

With the Data Engineer Associate certification drawing attention to various AWS data services, it’s a shame that this feature is so far behind. This would be a great modernisation tool for importing legacy Python scripts into Glue, but the last feature update was in 2022 and it’s really starting to lag behind now.

No Visual Editor

Yes I know it’s a script editor but hear me out.

Let’s briefly segue to AWS IAM. In the early days, updating IAM policies had the potential of losing afternoons to missing braces or errant commas. There was no native AWS validation tooling and the whole thing felt like a dark art for those less experienced.

Then AWS released an IAM visual policy editor. And things went from this:

2024 07 30 IAMPolicyJSON

To this!

2024 07 30 IAMPolicyDown

This transformed the IAM policy-writing process. The guesswork was gone – new policies could be written using dropdowns and checkboxes. And AWS would generate the same code each time, in the same way and to the same standard.

In today’s AWS console, IAM can be administrated both visually and as JSON. Updates made in the visual editor reflect in the code in real-time, and vice versa. And the IAM IDE immediately flags syntax issues, unclosed keypairs and whatnot.

This interface would work so well with Glue Script Editor. It would simplify and encourage using Script Editor, creating standardised code by default and reducing development time. No more syntax violations, verbose comments or missing dependencies – AWS could handle all that.

This doesn’t even need AI – it would just be procedural code generation. Something like selecting awswrangler from a dropdown list, then selecting an S3 location to read or write and a file type to expect. Or even a list of code snippets for the included libraries. These features could all lighten the dev load.

Limited IDE

Let’s consider AWS Lambda’s IDE:

2024 07 30 LambdaIDE

Its benefits include:

  • Code autocompletion
  • Integrated testing
  • Integrated monitoring

And tons of other user-focused functionality. Conversely, this is the Glue Script Editor IDE:

2024 07 30 GlueIDE

Hmm.

Now don’t get me wrong – I’m not asking for Lambda Lite. But something a bit more than Notepad would be nice. AWS are currently making a massive deal of Amazon CodeWhisperer and Amazon Q Developer‘s autocomplete actions, but here pandas isn’t even suggested when I type import pan. And it’s an included library!

The obvious solution is to just use Lambda. But Glue Script Editor offers a sweet spot where it runs custom Python while operating entirely within the AWS Glue service. This is helpful for features like Glue Triggers and Workflows that can’t currently trigger Lambda functions. It’s also helpful with AWS Organisations, where using Glue Script Editor for Python ETL can enable SCPs that entirely block access to AWS Lambda for data-centric accounts.

So Why Use It?

So are Glue Python Shell jobs worth considering with these limitations? Definately! There are several use cases favouring them:

  • Legacy ETL jobs that either can’t use recent Python versions and libraries, or simply don’t need them.
  • Simple, lightweight tasks that don’t require the more advanced (and expensive) features of Apache Spark or Ray.
  • Tasks that need to run quickly, as Python Shells have faster startup times than the Spark environments used by regular Glue ETL jobs.
  • Long-running ETL tasks unsuitable for AWS Lambda, as Python Shell jobs can run for up to 48 hours compared to Lambda’s 15 minutes. Thanks to Yan Cui‘s blog for that one!

For my requirements, a Python Shell job makes sense because I’m doing simple transformations on small volumes of data.

Architecture

This section examines the architecture of my proposed solution. Much of this architecture is similar to the Bronze layer. I’ll examine the new Silver ELT job, followed by the updated data pipeline Step Function workflow.

Glue Silver ETL Job

Firstly, this is the Glue Silver ETL job:

Amazon S3
Bronze Bucket
Amazon S3…
Amazon S3
Silver Bucket
Amazon S3…
AWS Glue
Silver ETL Job
AWS Glue…
Amazon CloudWatch
Logs
Amazon CloudWatch…
1
1
2
2
AWS Cloud
AWS Cloud
Text is not SVG – cannot display

While updating CloudWatch Logs throughout:

  1. Silver Glue ETL job extracts data from wordpress-api Bronze S3 objects and performs Python transformations.
  2. Silver Glue ETL job loads the transformed data into Silver S3 bucket as Parquet objects.

Step Function Workflow

Next, the updated Step Function workflow:

AWS Cloud
AWS Cloud
EventBridge
Schedule
EventBridge…
AWS Step Functions workflow
AWS Step Functions workflow
3
3
AWS Lambda Raw Function
AWS Lambda Ra…
AWS SNS Topic
AWS SNS Topic
2
2
State
Machine
State…
AWS Lambda Bronze Function
AWS Lambda Br…
F
F
5
5
AWS Glue
Bronze Crawler
AWS Glue…
4
4
AWS Glue
Silver ETL Job
AWS Glue…
F
F
F
F
1
1
F
F
EventBridge
Scheduler
EventBridge…
AWS SNS Topic
AWS SNS Topic
User
User
CloudWatch Logs
CloudWatch Lo…
F
F
F
F
Text is not SVG – cannot display

While updating the workflow’s CloudWatch Log Group throughout:

  1. An EventBridge Schedule executes the Step Functions workflow.
  2. Raw Lambda function is invoked.
    • Invocation Fails: Publish SNS message. Workflow ends.
    • Invocation Succeeds: Invoke Bronze Lambda function.
  3. Bronze Lambda function is invoked.
    • Invocation Fails: Publish SNS message. Workflow ends.
    • Invocation Succeeds: Run Glue Crawler.
  4. Glue Crawler runs.
    • Run Fails: Publish SNS message. Workflow ends.
    • Run Succeeds: Update Glue Data Catalog. Run Glue Silver ETL job.
  5. Glue Silver ETL job runs.
    • Run Fails: Publish SNS message. Workflow ends.
    • Run Succeeds: Workflow ends.

An SNS message is published if the Step Functions workflow fails.

Silver ETL Job

In this section, I create the Silver ETL Python script for the AWS Glue Script Editor. Firstly I’ll define the script’s requirements. Next, I’ll translate them into Python code, and finally I’ll create the ETL script and upload it to Git.

Requirements

Firstly, let’s define the requirements for this data pipeline layer. So what does a typical Silver ETL process involve?

Databricks defines the Silver layer as cleansed and conformed data:

In the Silver layer of the lakehouse, the data from the Bronze layer is matched, merged, conformed and cleansed (“just-enough”) so that the Silver layer can provide an “Enterprise view” of all its key business entities, concepts and transactions. (e.g. master customers, stores, non-duplicated transactions and cross-reference tables).

https://www.databricks.com/glossary/medallion-architecture

Because my data source is a WordPress MySQL database, most of the cleansing and conforming work I’d expect to do has already been done there! That said, there’s data that I definitely won’t need, as well as other transformations I can apply to help downstream reporting.

Some of the following transformations can be done at the SQL reporting level with date and string functions. However, these add repetitive load and complexity to queries, which can be avoided by some cleaning transformations. Roche’s Maxim of Data Transformation applies here:

Data should be transformed as far upstream as possible, and as far downstream as necessary.

https://ssbipolar.com/2021/05/31/roches-maxim/

The Silver layer transformations I’m doing here are:

Column Removal

Many columns are empty or unneeded, so now is the time to remove them. This will reduce the data held in the Silver objects, making them cheaper to store and faster to query.

My script uses the pandas.DataFrame.drop function to remove columns by specifying column names. Here, a term_order column is dropped from the DataFrame df:

Python
df = df.drop(columns=['term_order'])

Date Splitting

Dates are tough to analyse and don’t aggregate well, as each date is effectively three different data points in one field. Splitting dates into years, months and days improves data bucketing, query granularity and time series analytics.

My script uses the pandas to_datetime function to convert scalar, array-like, Series or DataFrame/dict-like objects to pandas datetime objects.

Here, values in the date column of the DataFrame df are converted from strings to datetime objects and stored in a new date_todate column. Next, the year attribute of each date_todate column object is extracted and stored in a new date_year column. Finally, the same happens for month and day attributes:

Python
df['date_todate'] = pd.to_datetime(df['date'])

df['date_year'] = df['date_todate'].dt.year
df['date_month'] = df['date_todate'].dt.month
df['date_day'] = df['date_todate'].dt.day

String Editing

Some columns use HTML character entity names for reserved characters. For example, &amp; in place of &. This is great for rendering HTML but not great for analytics.

My script uses the str.replace string method to return a copy of each string with all occurrences of the specified substring replaced by a new one. Here, all instances of & amp; in the name column are overwritten with &:

Python
df['name'] = df['name'].str.replace('& amp;','&')

So that’s the transformations. What else is the script doing?

Python Script

Most of the Silver script processes are similar to the Bronze script ones, including:

  • Logging
  • Getting parameters
  • Accessing S3 objects

So most functionality is reused from my Bronze Lambda function, which is fully documented in this post. To summarise the imports:

Python
import logging                          # Logging
import boto3                            # AWS Interactions
import botocore                         # AWS Exceptions
import awswrangler as wr                # S3 Interactions
import pandas as pd                     # Data Manipulation
from botocore.client import BaseClient  # AWS Type Hints

Some changes have been made for the Silver script:

  • Parameters, object names and logs have been updated from Bronze to Silver:
Python
parametername_snstopic: str = '/sns/data/lakehouse/silver'

logging.info("Getting S3 Silver parameter...")

s3_bucket_silver = get_parameter_from_ssm(client_ssm, parametername_s3bucket_silver)
  • New functionality identifies the AWS AccountID the script is running in:
Python
# Get & display AWS AccountID
identity = client_sts.get_caller_identity()
account_id = identity['Account']
logging.info(f"Starting in AWS Account ID {account_id}")

This is more of a sanity check for me – I have several AWS accounts and want to check I’ve accessed the right one!

  • A test that stops the current loop interaction if the object name doesn’t match one of the expected ones:
Python
# Check if object is mapped and bypass if not.
if object_name not in {'posts', 'statistics_pages', 'term_relationship', 'term_taxonomy', 'terms'}:

logging.warning(f'{object_name} is not currently mapped.  Skipping transform...')

object_count_failure += 1
continue

Finally, I wrote a new function for my Silver transformation logic. This isn’t included here (although it is in my repo) because it’s long. Very long! My first thought was to decouple the ETL processes from each other and write separate scripts for each object. So 5 in total.

However, Python Shell jobs are billed per second with a 1-minute minimum. So 5 jobs = 5 minutes billed. But the job only takes around 60 seconds to process all five objects! I’d have run up 5 times the usage and 5 times the cost for no real benefit.

The full script is in my Github repo.

Testing was quick because it was effectively repeating the Bronze script tests with new parameters. After successfully testing the script locally, it’s time to get it working in AWS!

Uploading & Testing

In this section I upload my Silver ETL script, integrate it with AWS Glue Script Editor and AWS Step Functions and test everything works as expected.

Creating The Python Shell Job

Firstly, let’s get my script into AWS Glue. There are several ways of doing this. If the script is uploaded to S3 then AWS can create a Glue ETL job with the AWS CLI create-job command:

Bash

 aws glue create-job --name python-job-cli --role Glue_DefaultRole 
     --command '{"Name" :  "pythonshell", "PythonVersion": "3.9", "ScriptLocation" : "s3://DOC-EXAMPLE-BUCKET/scriptname.py"}'  
     --max-capacity 0.0625

And with the AWS CloudFormation AWS::Glue::Job resource:

YAML
AWSTemplateFormatVersion: 2010-09-09
Resources:
  Python39Job:
    Type: 'AWS::Glue::Job'
    Properties:
      Command:
        Name: pythonshell
        PythonVersion: '3.9'
        ScriptLocation: 's3://DOC-EXAMPLE-BUCKET/scriptname.py'
      MaxRetries: 0
      Name: python-39-job
      Role: RoleName           
        

Scripts can also be pulled from Git repositories. Here I’ll create my Silver ETL job in the Glue Script Editor console. This creates a new Python script in an S3 bucket location of s3://aws-glue-assets-[AWSAccountID]-[Region]/scripts/.

Next, the new job needs an IAM role with appropriate permissions for the AWS services the script interacts with. Other parameters, including maximum DPU, job timeout value and Python version, can also be set. In addition, Glue Data Quality checks are also supported. And, once saved, the Glue job can have a schedule applied.

Testing Job Execution

AWS Glue records data for each job execution and publishes extensive details and logs:

2024 08 09 AWSGlueJobRun

Glue stores details about the job and Python environment, and logs are published and stored in Amazon Cloudwatch.

And so begins the testing! Initially, I was getting one of my own Python boto3 exceptions:

ValueError: No SNS topic returned.

Easy to fix. This IAM policy was based on the same one that my Bronze Lambda function uses. But the Silver ETL script uses different AWS resources so some IAM policy ARNs need to change. Specifically, the Silver ETL job’s IAM role needs to allow:

  • ssm:GetParameter on the required Parameter Store parameters.
  • sns:Publish on the required SNS topics.
  • s3:GetObject on the data-lakehouse-silver/wordpress_api/* objects.

With these changes, the Silver ETL job runs perfectly and creates new objects in the Silver S3 bucket:

2024 08 09 MonitoringTimeline

With the Glue job running and S3 object creation verified successfully, it’s time to validate the data.

Data Integration & Validation

Validating the data involves two processes:

  • Integrating the data into the Glue Data Catalog.
  • Querying the data with Amazon Athena.

There are several ways to update the Glue Data Catalog, and here I’ll create a new Glue Crawler using a similar setup to my Bronze Crawler. This time the crawler is reading objects from the Silver S3 bucket instead of the Bronze one, and the new Glue Data Catalog tables are prefixed with silver- instead of bronze-.

The Silver crawler creates these new tables in the Glue Data Catalog’s wordpress_api database:

2024 08 06 GlueDataCatalog

This gives Athena visibility of the tables, enabling data validation via SQL query execution. Querying wordpress_api.silver-terms shows the removed column and updated strings:

2024 08 06 AthenaSilverTerms

And querying wordpress_api.silver-statistics_pages shows the split dates:

2024 08 06 AthenaSilverStatistics pages

Looks good! Now that everything has been validated, let’s add these steps to the WordPress Data Pipeline.

Step Function Update

The WordPress Data Pipeline Step Function workflow that I started back in March continues to grow. There’s a new job and a second crawler to add to it now!

The Silver crawler is added in the same way as the Bronze one (including the IAM changes) so let’s focus on adding the new Glue Python Shell ETL job.

Adding Glue ETL jobs to a Step Function workflow is well documented The task uses the StartJobRun Glue API action under the hood and has an optimized integration that enables the .sync integration pattern. Enabling this means the Step Functions workflow waits for the StartJobRun request to complete before progressing to the next state.

However, my workflow currently lacks IAM permissions to run the Silver Glue ETL job. So I make a new IAM policy that allows the glue:StartJobRun action on the Silver Glue ETL job and attach it to the workflow’s IAM role:

JSON
{
	"Version": "2012-10-17",
	"Statement": [
		{
			"Sid": "VisualEditor0",
			"Effect": "Allow",
			"Action": [
				"glue:StartJobRun"
			],
			"Resource": "arn:aws:glue:eu-west-1:[REDACTED]:job/wordpressapi_silver"
		}
	]
}

My Step Function workflow now looks like this:

2024 08 09 stepfunctions graph

Let’s execute the Step Function workflow and check it works.

Step Function Test

Upon execution, everything works as intended. The new StartGlueJob action is triggered and the Glue ETL job is successful:

2024 08 06 GlueJobDetails

But the Step Function doesn’t transition to the next step. In fact it continued running to the point I had to stop it myself after several minutes:

2024 08 06 StepFunctionsStop

So what’s going on? I asked Amazon Q about this behaviour, and in its response were the following points:

  1. Step Functions uses a “sync” integration with AWS Glue, which means it relies on polling the status of the Glue job using the GetJobRun API call.
  2. The polling schedule is designed to be once per minute for the first 10 minutes, and then every 5 minutes thereafter. This is to avoid excessive API calls to Glue.
Amazon Q

Q also linked to this AWS repost answer with further details:

This is an expected behavior in case of .sync integration with AWS Glue. Service integrations that use the .sync pattern require additional IAM permissions where Step Functions will make use of a managed Eventbridge rule to monitor the status of the job. However, AWS Glue does not support Eventbridge integration and thus, Step Functions polls the job status using the GetJobRun API call to fetch the status of the job.

https://repost.aws/questions/QUFFlHcbvIQFe-bS3RAi7TWA/a-glue-job-in-a-step-function-is-taking-so-long-to-continue-the-next-step

This made things clearer. When Step Functions starts a Glue ETL job using a StartGlueJob action with optimized integration, Step Functions determines that job’s status (and thus when to transition to the next action) by calling Glue’s GetJobRun API.

However, my workflow’s IAM role doesn’t have permission to do that! And because Step Functions can’t determine the ETL job’s status, it doesn’t know that the job has finished and the next state transition never happens! Everything stops!

This is resolved by adding the glue:GetJobRun action to the workflow’s IAM policy:

JSON
{
	"Version": "2012-10-17",
	"Statement": [
		{
			"Sid": "VisualEditor0",
			"Effect": "Allow",
			"Action": [
				"glue:StartJobRun",
				"glue:GetJobRun"
			],
			"Resource": "arn:aws:glue:eu-west-1:REDACTED:job/wordpressapi_silver"
		}
	]
}

This time, the Glue GetJobRun API calls are successful. The Step Functions workflow validates that the ETL job has finished, moves to the next state as intended and ultimately completes successfully:

2024 08 06 ExecutionSuccessFull

Thanks Amazon Q!

Costs

Finally, let’s look at the costs for my Glue Script Editor Silver ETL Job resources.

This graph shows all Glue API costs between 2024-07-31 (first AWS job execution) and 2024-09-09:

2024 08 09 CostExplorerGlue

Of the $0.38:

  • $0.37 is the CrawlerRun API for the two Glue Crawlers I’m running.
  • $0.01 is the Jobrun API for the 15 job runs between 2024-07-31 and 2024-09-09.

So all things considered, very manageable!

Summary

In this post, I created my WordPress data pipeline’s Silver ETL process using Python and the AWS Glue ETL Job Script Editor.

I found the Script Editor jobs very useful. They offer Lambda’s benefits of scalability, managed infrastructure and integration with other AWS services, combined with data-centric libraries and features that make it easier to hit the ground running development-wise. It has clear limitations and could do with some AWS TLC, but it was a good fit here and rivals Lambda for some future ETL processes I have planned.

If this post has been useful then the button below has links for contact, socials, projects and sessions:

SharkLinkButton 1

Thanks for reading ~~^~~

Categories
Data & Analytics

Discovering Data With AWS Glue

In this post, I use the data discovering features of AWS Glue to crawl and catalogue my WordPress API pipeline data.

Table of Contents

Introduction

By the end of my WordPress Bronze Data Orchestration post, I had created an AWS Step Function workflow that invokes two AWS Lambda functions:

stepfunctions graph

The data_wordpressapi_raw function gets data from the WordPress API and stores it as CSV objects in Amazon S3. The data_wordpressapi_bronze function transforms these objects to Parquet and stores them in a separate bucket. If either function fails, AWS SNS publishes an alert.

While this process works fine, the extracted data is not currently utilized. To derive value from this data, I need to consider transforming it. Several options are available, such as:

  • Creating new Lambda functions.
  • Importing the data into a database.
  • Third-party solutions like Databricks.

Here, I’ve chosen to use AWS Glue. As a fully managed ETL service, Glue automates various data processes in a low-code environment. I’ve not written much about it, so it’s time that changed!

Firstly, I’ll examine AWS Glue and some of its concepts. Next, I’ll create some Glue resources that interact with my WordPress S3 objects. Finally, I’ll integrate those resources into my existing Step Function workflow and examine their costs.

Let’s begin with some information about AWS Glue.

AWS Glue Concepts

This section explores AWS Glue and some of its data discovering features.

AWS Glue

From the AWS Glue User Guide:

AWS Glue is a serverless data integration service that makes it easy for analytics users to discover, prepare, move, and integrate data from multiple sources. You can use it for analytics, machine learning, and application development. It also includes additional productivity and data ops tooling for authoring, running jobs, and implementing business workflows.

https://docs.aws.amazon.com/glue/latest/dg/what-is-glue.html

Glue can be accessed using the AWS Glue console web interface and the AWS Glue Studio graphical interface. It can also be accessed programmatically via the AWS Glue CLI and the AWS Glue SDK.

Benefits of AWS Glue include:

  • Data-specific features like data cataloguing, schema discovery, and automatic ETL code generation.
  • Infrastructure optimised for ETL tasks and data processes.
  • Built-in scheduling capabilities and job execution.
  • Integration with other AWS services like Athena and Redshift.

AWS Glue’s features fall into Discover, Prepare, Integrate and Transform categories. The Glue features used in this post come from the data discovering category.

Glue Data Catalog

An AWS Glue Data Catalog is a managed repository serving as a central hub for storing metadata about data assets. It includes table and job definitions, and other control information for managing an AWS Glue environment. Each AWS account has a dedicated AWS Glue Data Catalog for each region.

The Data Catalog stores information as metadata tables, with each table representing a specific data store and its schema. Glue tables can serve as sources or targets in job definitions. Tables are organized into databases, which are logically grouped collections of related table definitions.

Each table contains column names, data type definitions, partition information, and other metadata about a base dataset. Data Catalog tables can be populated either manually or using Glue Crawlers.

Glue Crawler

A Glue Crawler connects to a data store, analyzes and determines its schema, and then creates metadata tables in the AWS Glue Data Catalog. They can run on-demand, be automated by services like Amazon EventBridge Scheduler and AWS Step Functions, and be started by AWS Glue Triggers.

Crawlers can crawl several data stores including:

  • Amazon S3 buckets via native client.
  • Amazon RDS databases via JDBC.
  • Amazon DocumentDB via MongoDB client.

An activated Glue Crawler performs the following processes on the chosen data store:

  • Firstly, data within the store is classified to determine its format, schema and properties.
  • Secondly, data is grouped into tables or partitions.
  • Finally, the Glue Data Catalog is updated. Glue creates, updates and deletes tables and partitions, and then writes the metadata to the Data Catalog accordingly.

Now let’s create a Glue Crawler!

Creating A Glue Crawler

In this section, I use the AWS Glue console to create and run a Glue Crawler for discovering my WordPress data.

Crawler Properties & Sources

There are four steps to creating a Glue Crawler. Step One involves setting the crawler’s properties. Each crawler needs a name and can have optional descriptions and tags. This crawler’s name is wordpress_bronze.

Step Two sets the crawler’s data sources, which is greatly influenced by whether the data is already mapped in Glue. If it is then the desired Glue Data Catalog tables must be selected. Since my WordPress data isn’t mapped yet, I need to add the data sources instead.

My Step Function workflow puts the data in S3, so I select S3 as my data source and supply the path of my bronze S3 bucket’s wordpress_api folder. The crawler will process all folders and files contained in this S3 path.

Finally, I need to configure the crawler’s behaviour for subsequent runs. I keep the default setting, which re-crawls all folders with each run. Other options include crawling only folders added since the last crawl or using S3 Events to control which folders to crawl.

Classifiers are also set here but are out of scope for this post.

Crawler Security & Targets

Step Three configures security settings. While most of these are optional, the crawler needs an IAM role to interact with other AWS services. This role consists of two IAM policies:

  • An AWSGlueServiceRole AWS managed policy which allows access to related services including EC2, S3, and Cloudwatch Logs.
  • A customer-managed policy with s3:GetObject and s3:PutObject actions allowed on the S3 path given in Step Two.

This role can be chosen from existing roles or created with the crawler.

Step Four begins with setting the crawler’s output. The Crawler creates new tables, requiring the selection of a target database for these tables. This database can be pre-existing or created with the crawler.

An optional table name prefix can also be set, which enables easy table identification. I create a wordpress_api database in the Glue Data Catalog, and set a bronze- prefix for the new tables.

The Crawler’s schedule is also set here. The default is On Demand, which I keep as my Step Function workflow will start this crawler. Besides this, there are choices for Hourly, Daily, Weekly, Monthly or Custom cron expressions.

Advanced options including how the crawler should handle detected schema changes and deleted objects in the data store are also available in Step Four, although I’m not using those here.

And with that, my crawler is ready to try out!

Running The Crawler

My crawler can be tested by accessing it in the Glue console and selecting Run Crawler:

2024 05 23 RunCrawler

The crawler’s properties include run history. Each row corresponds to a crawler execution, recording data including:

  • Start time, end time and duration.
  • Execution status.
  • DPU hours for billing.
  • Changes to tables and partitions.
2024 05 23 GlueCrawlerRuns

AWS stores the logs in an aws-glue/crawlers CloudWatch Log Group, in which each crawler has a dedicated log stream. Logs include messages like the crawler’s configuration settings at execution:

Crawler configured with Configuration 
{
    "Version": 1,
    "CreatePartitionIndex": true
}
 and SchemaChangePolicy 
{
    "UpdateBehavior": "UPDATE_IN_DATABASE",
    "DeleteBehavior": "DEPRECATE_IN_DATABASE"
}

And details of what was changed and where:

Table bronze-statistics_pages in database wordpress_api has been updated with new schema

Checking The Data Catalog

So what impact has this had on the Data Catalog? Accessing it and selecting the wordpress_api database now shows five tables, each matching S3 objects created by the Step Functions workflow:

2024 05 23 GlueDataCatalogTables

Data can be viewed by selecting Table Data on the desired row. This action executes an Athena query, triggering a message about the cost implications:

You will be taken to Athena to preview data, and you will be charged separately for Athena queries.

If accepted, Athena generates and executes a SQL query in a new tab. In this example, the first ten rows have been selected from the wordpress_api database’s bronze-posts table:

SQL
SELECT * 
FROM "AwsDataCatalog"."wordpress_api"."bronze-posts" 
LIMIT 10;

When this query is executed, Athena checks the Glue Data Catalog for the bronze-posts table in the wordpress_api database. The Data Catalog provides the S3 location for the data, which Athena reads and displays successfully:

2024 05 23 AthenaBronzeQueryResults

Now that the crawler works, I’ll integrate it into my Step Function workflow.

Crawler Integration & Costs

In this section, I integrate my Glue Crawler into my existing Step Function workflow and examine its costs.

Architectural Diagrams

Let’s start with some diagrams. This is how the crawler will behave:

While updating the crawler’s wordpress_bronze CloudWatch Log Stream throughout:

  1. The wordpress_bronze Glue Crawler crawls the bronze S3 bucket’s wordpress-api folder.
  2. The crawler updates the Glue Data Catalog’s wordpress-api database.

This is how the Crawler will fit into my existing Step Functions workflow:

wordpress api stepfunction rawbronze

While updating the workflow’s CloudWatch Log Group throughout:

  1. An EventBridge Schedule executes the Step Functions workflow.
  2. Raw Lambda function is invoked.
    • Invocation Fails: Publish SNS message. Workflow ends.
    • Invocation Succeeds: Invoke Bronze Lambda function.
  3. Bronze Lambda function is invoked.
    • Invocation Fails: Publish SNS message. Workflow ends.
    • Invocation Succeeds: Run Glue Crawler.
  4. Glue Crawler runs.
    • Run Fails: Publish SNS message. Workflow ends.
    • Run Succeeds: Update Glue Data Catalog. Workflow ends.

An SNS message is published if the Step Functions workflow fails.

Step Function Integration

Time to build! Let’s begin with the crawler’s requirements:

  • The crawler must only run after both Lambda functions.
  • It must also only run if both functions invoke successfully first.
  • If the crawler fails it must alert via the existing PublishFailure SNS topic.

This requires adding an AWS Glue: StartCrawler action to the workflow after the second AWS Lambda: Invoke action:

2024 05 23 StepFunctionStartCrawler

This action differs from the ones I’ve used so far. The existing actions all use optimized integrations that provide special Step Functions workflow functionality.

Conversely, StartCrawler uses an SDK service integration. These integrations behave like a standard AWS SDK API call, enabling more fine-grained control and flexibility than optimised integrations at the cost of needing more configuration and management.

Here, the Step Functions StartCrawler action calls the Glue API StartCrawler action. After adding it to my workflow, I update the action’s API parameters with the desired crawler’s name:

JSON
{
  "Name": "wordpress_bronze"
}

Next, I update the action’s error handling to catch all errors and pass them to the PublishFailure task. These actions produce these additions to the workflow’s ASL code:

JSON
  "Start Bronze Crawler": {
      "Type": "Task",
      "End": true,
      "Parameters": {
        "Name": "wordpress_bronze"
      },
      "Resource": "arn:aws:states:::aws-sdk:glue:startCrawler",
      "Catch": [
        {
          "ErrorEquals": [
            "States.ALL"
          ],
          "Next": "PublishFailure"
        }
      ]
    },

And result in an updated workflow graph:

stepfunctions graph gluecrawler

Additionally, the fully updated Step Functions workflow ASL script can be viewed on my GitHub.

Finally, I need to update the Step Function workflow IAM role’s policy so that it can start the crawler. This involves allowing the glue:StartCrawler action on the crawler’s ARN:

JSON
{
    "Version": "2012-10-17",
    "Statement": [
        {
            "Sid": "AllowBronzeGlueCrawler",
            "Effect": "Allow",
            "Action": [
                "glue:StartCrawler"
            ],
            "Resource": [
                "arn:aws:glue:eu-west-1:[REDACTED]:crawler/wordpress_bronze"
            ]
        }

My Step Functions workflow is now orchestrating the Glue Crawler, which will only run once both Lambda functions are successfully invoked. If either function fails, the SNS topic is published and the crawler does not run. If the crawler fails, the SNS topic is published. Otherwise, if everything runs successfully, the crawler updates the Data Catalog as needed.

So how much does discovering data with AWS Glue cost?

Glue Costs

This is from AWS Glue’s pricing page for crawlers:

There is an hourly rate for AWS Glue crawler runtime to discover data and populate the AWS Glue Data Catalog. You are charged an hourly rate based on the number of Data Processing Units (or DPUs) used to run your crawler. A single DPU provides 4 vCPU and 16 GB of memory. You are billed in increments of 1 second, rounded up to the nearest second, with a 10-minute minimum duration for each crawl.

$0.44 per DPU-Hour, billed per second, with a 10-minute minimum per crawler run

https://aws.amazon.com/glue/pricing/

And for the Data Catalog:

With the AWS Glue Data Catalog, you can store up to a million objects for free. If you store more than a million objects, you will be charged $1.00 per 100,000 objects over a million, per month. An object in the Data Catalog is a table, table version, partition, partition indexes, statistics or database.

The first million access requests to the Data Catalog per month are free. If you exceed a million requests in a month, you will be charged $1.00 per million requests over the first million. Some of the common requests are CreateTable, CreatePartition, GetTable , GetPartitions, and GetColumnStatisticsForTable.

https://aws.amazon.com/glue/pricing/

So how does this relate to my workflow? The below Cost Explorer chart shows my AWS Glue API costs from 01 May to 28 May. Only the CrawlerRun API operation has generated charges, with a daily average of $0.02:

2024 05 28 GlueAPICostMay28

My May 2024 AWS bill shows further details on the requests and storage items. The Glue Data Catalog’s free tier covers my usage:

2024 05 28 GlueCostsMay28

Finally, let’s review the entire pipeline’s costs for April and May. Besides Glue, my only other cost remains S3:

2024 05 28 CostExplorerAprMay

Summary

In this post, I used the data discovering features of AWS Glue to crawl and catalogue my WordPress API pipeline data.

Glue’s native features and integration with other AWS services make it a great fit for my WordPress pipeline’s pending processes. I’ll be using additional Glue features in future posts, and wanted to spotlight the Data Catalog early on as it’ll become increasingly helpful as my use of AWS Glue increases.

If this post has been useful then the button below has links for contact, socials, projects and sessions:

SharkLinkButton 1

Thanks for reading ~~^~~