Categories
Developing & Application Integration

WordPress Data Extraction Automation With AWS

In this post, I set up the automation of my WordPress API data extraction Python script with AWS managed serverless services.

Table of Contents

Introduction

In my previous post, I wrote a Python script for extracting WordPress API data. While it works fine, it relies on me logging in and pressing buttons. This isn’t convenient, and would be completely out of the question in a commercial use case. Wouldn’t it be great if something could run the script for me?

Enter some AWS managed serverless services that are very adept at automation! In this post, I’ll integrate these services into my existing architecture, test that everything works and see what my AWS costs are to date.

A gentle reminder: this is my first time setting up some of these services from scratch. This post doesn’t represent best practices, may be poorly optimised or include unexpected bugs, and may become obsolete. I expect to find better ways of doing these processes in the coming months and will link updates where appropriate.

Architectural Decisions

In this section, I examine my architectural decisions before starting work. Which AWS services will perform my WordPress data extraction automation? Note that these decisions are in addition to my previous ones.

AWS Lambda

Probably no surprises here. Whenever AWS and serverless come up, Lambda is usually the first service that comes to mind.

And with good reason! AWS Lambda deploys quickly and scales on demand. It supports several programming languages and practically every AWS service. It also has a generous free tier and requires no infrastructure management.

Lambda will provide my compute resources. This includes the runtime, execution environment and network connectivity for my Python script.

Amazon Cloudwatch

Amazon CloudWatch is a monitoring service that can collect and track performance data, generate insights and respond to resource state changes. It provides features such as metrics, alarms, and logs, letting users monitor and troubleshoot their applications and infrastructure in real time.

CloudWatch will record and store my Lambda function’s logs. I can see when my function is invoked, how long it takes to run and any errors that may occur.

So if something does go wrong, how will I know?

Amazon SNS

Amazon Simple Notification Service (SNS) is a messaging service that delivers notifications to a set of recipients or endpoints. It supports various messaging protocols like SMS, email and HTTP, making it helpful for building scalable and decoupled applications.

SNS will be the link between AWS and my email inbox. It will deliver messages from AWS about my Lambda function.

So that’s my alerting sorted. How does the function get invoked?

Amazon EventBridge

Amazon EventBridge is an event bus service that enables communication between different services using events. It offers a serverless and scalable platform with advanced event routing, integration capabilities and, crucially, scheduling and time expression functionality.

EventBridge is here to handle my automation requirements. Using a CRON expression, it’ll invoke my Lambda function regularly with no user input required.

Architectural Diagram

This is an architectural diagram of the AWS automation of my WordPress data extraction process:

  1. EventBridge invokes AWS Lambda function.
  2. AWS Lambda calls Parameter Store for WordPress, S3 and SNS parameters. Parameter Store returns these to AWS Lambda.
  3. Lambda Function calls WordPress API. WordPress API returns data.
  4. API data is written to S3 bucket.

If there’s a failure, the Lambda function publishes a message to an SNS topic. SNS then delivers this message to the user’s subscribed email address.

Meanwhile, Lambda is writing to a CloudWatch Log Group throughout its invocation.

SNS & Parameter Store

In this section, I configure Amazon SNS and update AWS Parameter Store to enable my WordPress data extraction automation alerting. This won’t take long!

SNS Configuration

SNS has two fundamental concepts:

  • Topics: communication channels for publishing messages.
  • Subscriptions: endpoints to send messages to.

Firstly, I create a new wordpress-api-raw standard SNS Topic. This topic doesn’t need encryption or delivery policies, so all the defaults are fine. An Amazon Resource Name (ARN) is assigned to the new SNS Topic, which I’ll put into Parameter Store.

Next, I create a new SNS Subscription for my SNS Topic that emails me when invoked.

There’s not much else to add here! That said, SNS can do far more than this. Check out SNS’s features and capabilities in the Developer Guide.

Parameter Store Configuration

Next, I need to add the new SNS Topic ARN to AWS Parameter Store.

I create a new string parameter, and assign the SNS Topic’s ARN as the value. That’s….it! With some changes, my Python script can now get the SNS parameter in the same way as the S3 and WordPress parameters.

Speaking of changing the Python script…

Python

In this section, I integrate SNS into my existing Python script and test the new outputs.

Function Updates

My script now has a new send_sns_message function:

It expects four arguments:

  • sns_client: the boto3 client used to contact AWS.
  • topic_arn: the SNS topic to use for the message.
  • subject: the message’s subject.
  • message: the message to send.

Everything bar sns_client has string type hints. No return value is needed.

I create a try except block that attempts to send a message using the sns_client’s publish method and the supplied values. The log is updated with publish‘s success or failure.

Separately, I’ve also added a ParamValidationError exception to my get_parameter_from_ssm function. Previously the exceptions were:

Python
    except ssm_client.exceptions.ParameterNotFound:
        logging.warning(f"Parameter {parameter_name} not found.")
        return ""

    except botocore.exceptions.ClientError as e:
        logging.error(f"Error getting parameter {parameter_name}: {e}")
        return ""

They are now:

Python
    except ssm_client.exceptions.ParameterNotFound as pnf:
        logging.warning(f"Parameter {parameter_name} not found: {pnf}")
        return ""

    except botocore.exceptions.ParamValidationError as epv:
        logging.error(f"Error getting parameter {parameter_name}: {epv}")
        return ""

    except botocore.exceptions.ClientError as ec:
        logging.error(f"Error getting parameter {parameter_name}: {ec}")
        return ""

Variable Updates

My send_sns_message function needs some new variables. Firstly, I create an SNS Client using my existing boto3 session and assign it to client_sns:

Python
    # AWS sessions and clients
    session = boto3.Session()
    client_ssm = session.client('ssm')
    client_s3 = session.client('s3')
    client_sns = session.client('sns')
    requests_session = requests.Session()

Next, I assign the new SNS parameter name to a parametername_snstopic object:

Python
    # AWS Parameter Store Names
    parametername_s3bucket = '/s3/lakehouse/name/raw'
    parametername_snstopic = '/sns/pipeline/wordpressapi/raw'
    parametername_wordpressapi = '/wordpress/amazonwebshark/api/mysqlendpoints'

Finally, I create a new lambdaname object which I’ll use for SNS notifications in my Python script’s body.

Python
    # Lambda name for messages
    lambdaname = 'wordpress_api_raw.py'

Script Body Updates

These changes integrate SNS failure messages into my script. There are no success messages…because I get enough emails as it is.

SNS Parameter Retrieval & Check

There’s now a third use of get_parameter_from_ssm, using parametername_snstopic to get the SNS topic ARN from AWS Parameter Store:

Python
    # Get SNS topic from Parameter Store
    logging.info("Getting SNS parameter...")
    sns_topic = get_parameter_from_ssm(client_ssm, parametername_snstopic)

I’ve also added an SNS parameter check. It behaves differently to the other checks, as it’ll raise a ValueError if nothing is found:

Python
    # Check an SNS topic has been returned.
    if not sns_topic:
        message = "No SNS topic returned."
        logging.warning(message)
        raise ValueError(message)

I want to cause an invocation failure in this situation, as not having the SNS topic ARN is a critical and unrecoverable problem which the automation process will have no way to alert me about.

However, the AWS Lambda service can warn me about invocation failures. This is something I’ll set up later on.

Failure Getting Other Parameters

The get_parameter_from_ssm response checks have changed. Previously, if a parameter request (the API endpoints in this case) returns a blank string then a warning is logged and the invocation ends:

Python
    # Check the API list isn't empty
    if not any(api_endpoints_list):
        logging.warning("No API endpoints returned.")
        return

Now, new subject and message objects are created with details about the error. The message string is added to the log, and both objects are passed to send_sns_message along with the SNS client and SNS topic ARN:

Python
    # Check the API list isn't empty
    if not any(api_endpoints_list):
        message = "No API endpoints returned."
        subject = f"{lambdaname}: Failed"

        logging.warning(message)
        send_sns_message(client_sns, sns_topic, subject, message)
        return

The S3 check now works similarly:

Python
    # Check an S3 bucket has been returned.
    if not s3_bucket:
        message = "No S3 bucket returned."
        subject = f"{lambdaname}: Failed"

        logging.warning(message)
        send_sns_message(client_sns, sns_topic, subject, message)
        return

If either of these checks fail, no WordPress API calls are made and the invocation stops.

Failure During For Loop

Previously, the script’s final output was a log entry showing the endpoint_count_success and endpoint_count_failure values:

Python
    logging.info("WordPress API Raw process complete: " \
                 f"{endpoint_count_success} Successful | {endpoint_count_failure} Failed.")

This section has now been expanded. If endpoint_count_failure is greater than zero, a message object is created including the number of failures.

message is then written to the log, and is passed to send_sns_message with a subject and the SNS client and SNS topic ARN:

Python
    logging.info("WordPress API Raw process complete: " \
                 f"{endpoint_count_success} Successful | {endpoint_count_failure} Failed.")

    # Send SNS notification if any failures found
    if endpoint_count_failure > 0:
        message = f"{lambdaname} ran with {endpoint_count_failure} errors.  Please check logs."
        subject = f"{lambdaname}: Ran With Failures"

        logging.warning(message)
        send_sns_message(client_sns, sns_topic, subject, message)

If a loop iteration fails, the script ends it and starts the next. One or more loop iterations can fail while the others succeed.

That completes the script changes. Next, I’ll test the failure responses.

SNS Notification Testing

SNS should now send me one of two emails depending on which failure occurs. I can test these locally by inverting the logic of some if conditions.

Firstly, I set the S3 bucket check to fail if a bucket name is returned:

Python
    # Check an S3 bucket has been returned.
    if s3_bucket:
        message = "No S3 bucket returned."
        subject = f"{lambdaname}: Failed"

        logging.warning(message)
        send_sns_message(client_sns, sns_topic, subject, message)
        return

Upon invocation, an email arrives with details of the failure:

2024 02 06 wordpress api raw.py Failed

Secondly, I change the loop’s data check condition to fail if data is returned:

Python
        # If no data returned, record failure & end current iteration
        if api_json:
            logging.warning("Skipping attempt due to API call failure.")
            endpoint_count_failure += 1
            continue

This ends the current loop iteration and increments the endpoint_count_failure value. Then, in a check after the loop, an SNS message is triggered when endpoint_count_failure is greater than 0:

Python
    # Send SNS notification if any failures found
    if endpoint_count_failure > 0:
        message = f"{lambdaname} ran with {endpoint_count_failure} errors.  Please check logs."
        subject = f"{lambdaname}: Ran With Failures"

        logging.warning(message)
        send_sns_message(client_sns, sns_topic, subject, message)

Now, a different email arrives with the number of failures:

2024 02 06 wordpress api raw.py RanWithFailures

Success! Now the Python script is working as intended, it’s time to deploy it to AWS.

Lambda & CloudWatch

In this section, I start creating the automation of my WordPress data extraction process by creating and configuring a new AWS Lambda function. Then I deploy my Python script, set some error handling and test everything works.

I made extensive use of Martyn Kilbryde‘s AWS Lambda Deep Dive A Cloud Guru course while completing this section. It was exactly the kind of course I needed – a bridge between theoretical certification content and hands-on experience in my own account.

This section is the result of my first pass through the course. There are better ways of doing what I’ve done here, but ultimately I have to start somewhere. I have several course sections to revisit, so watch this space!

Let’s begin with creating a new Lambda function.

Function Creation

Lambda function creation steps vary depending on whether the function is being written from scratch, or if it uses a blueprint or container image. I’m writing from scratch, so after choosing a name I must choose the function’s runtime. Runtimes consist of the programming language and the specific version. In my case, this is Python 3.12.

Next are the permissions. By design, AWS services need permissive IAM roles to interact with other services. A Lambda function with no IAM role cannot complete actions like S3 reads or CloudWatch writes.

Thankfully, AWS are one step ahead. By default, Lambda creates a basic execution role for each new function with some essential Amazon CloudWatch actions. With this role, the function can record invocations, resource utilization and billing details in a log stream. Additional IAM actions can be assigned to the role as needed.

Script Deployment

Now I have a function, I need to upload my Python script. There are many ways of doing this! I followed the virtual environment process, as I already had one from developing the script in VSCode. This environment’s contents are in the requirements.txt file listed in the Resources section.

While this was successful, the resulting deployment package is probably far bigger than it needs to be. Additionally, I didn’t make use of any of the toolkits, frameworks or pipelines with Lambda functionality. I expect my future deployments to improve!

Lambda Destination

There’s one more Lambda feature I want to use: a Lambda Destination.

From the AWS Compute blog:

With Destinations, you can route asynchronous function results as an execution record to a destination resource without writing additional code. An execution record contains details about the request and response in JSON format including version, timestamp, request context, request payload, response context, and response payload.

https://aws.amazon.com/blogs/compute/introducing-aws-lambda-destinations/

Here, I want a destination that will email me if my Lambda function fails to run. This helps with visibility, and will be vital if the SNS parameter isn’t returned!

With no Destination, the failure would only appear in the function’s log and I might not know about it for days. With a Destination enabled, I’ll know about the failure as soon as the email comes through.

My destination uses the following config:

  • Invocation Type: Asynchronous
  • Condition: On Failure
  • Destination Type: SNS topic

The SNS topic is a general Failed Lambda one that I already have. The Lambda service can use this SNS topic regardless of any script problems.

Lambda & CloudWatch Testing

With the function created and deployed, it’s testing time! Does my function work and log as intended?

Error: Timeout Exceeded

It doesn’t take long to hit my first problem:

Task timed out after 3.02 seconds

All Lambdas created in the console start with a three-second timeout. This is great at preventing runaway invocations, but I clearly need longer than three seconds.

After some local testing, I increased the timeout to two minutes in the function’s config:

2023 12 19 LambdaTimeout

Error: Access Denied

Next, I start hitting permission errors:

An error occurred (AccessDeniedException) when calling the GetParameter operation: User is not authorized to perform: ssm:GetParameter on resource because no identity-based policy allows the ssm:GetParameter action.

My Lambda’s basic execution role can interact with CloudWatch, but nothing else. This is by design in the interests of security. However, this IAM role is currently too restrictive for my needs.

The role’s policy needs to allow additional actions:

To follow IAM best practise, I should also apply least-privilege permissions. Instead of a wildcard character, I should restrict the policy to the specific ARNs of my AWS resources.

For example, this IAM policy is too permissive as it allows access to all parameters in Parameter Store:

JSON
{
	"Version": "2012-10-17",
	"Statement": [
		{
			"Sid": "Statement1",
			"Effect": "Allow",
			"Action": [
				"ssm:GetParameter"
			],
			"Resource": [
				"*"
			]
		}
	]
}

Conversely, this IAM policy allows access to specific parameter ARNs only.

(Well, it did before the ARNs were redacted – Ed.)

JSON
"Version": "2012-10-17",
    "Statement": [
        {
            "Sid": "VisualEditor0",
            "Effect": "Allow",
            "Action": [
                "ssm:GetParameter"
            ],
            "Resource": [
                "arn:aws:ssm:REDACTED",
                "arn:aws:ssm:REDACTED",
                "arn:aws:ssm:REDACTED"
            ]
        }

My S3 policy does have a wildcard value, but it’s at the prefix level:

JSON
{
    "Version": "2012-10-17",
    "Statement": [
        {
            "Effect": "Allow",
            "Action": [
                "s3:PutObject"
            ],
            "Resource": [
                "arn:aws:s3:::REDACTED/wordpress-api/*"
            ]
        }

My Lambda function can now write to my bucket, but only to the wordpress-api prefix. A good way to understand the distinction is to look at an AWS example:

arn:aws:s3:::my_corporate_bucket/*
arn:aws:s3:::my_corporate_bucket/Development/*

In this example, line 1 covers the entire my_corporate_bucket S3 bucket. Line 2 is more focused, only covering all objects in the Development prefix of the my_corporate_bucket bucket.

Error: Memory Exceeded

With the new policy, my function runs smoothly. Until:

Runtime exited with error: signal: killed Runtime.ExitError

This one was weird because the function kept suddenly stopping at different points! I then checked further down the test summary:

2023 12 19 LambdaMaxMemoryHighlight

It’s running out of memory! Lambda assigns a default 128MB RAM to each function, and here my function was hitting 129MB. RAM can be changed in the function’s general configuration. But changed to what?

When a Lambda function runs successfully, it logs memory metrics:

Memory Size: 500 MB	Max Memory Used: 197 MB

After some trial and error, I set the function’s RAM to 250MB and have had no problems since.

Incomplete CloudWatch Logs

The last issue wasn’t an error so much as a bug. CloudWatch was showing my Lambda invocation start and end, but none of the function’s logs:

2023 12 22 LambdaNoLogs

The solution was found in Python’s basicConfig‘s docstring:

This function does nothing if the root logger already has handlers configured, unless the keyword argument force is set to True.

basicConfig docstring

Well, AWS Lambda does have built-in logging. And my basicConfig isn’t forcing anything! One swift update and redeployment later:

Python
    logging.basicConfig(
        level = logging.INFO,
        format = "%(asctime)s [%(levelname)s]: %(message)s",
        datefmt = "%Y-%m-%d %H:%M:%S",
        force = True
        )

And my CloudWatch Log Stream is now far more descriptive!

2023 12 22 LambdaLogs

In the long run I plan to investigate Lamba’s logging abilities, but for now this does what I need.

SNS Destination Email

Finally, I want to make sure my Lambda Destination is working as expected. My function works now, so I need to force a failure. There are many ways of doing this. In this case, I used three steps:

  • Temporarily alter the function’s timeout to 3 seconds.
  • Reconfigure the function’s Asynchronous Invocation retry attempts to zero.
  • Invoke the function with a one-time EventBridge Schedule.

The low timeout guarantees a function invocation failure. Setting zero retries prevents unnecessary retries (because I want the failure to happen!) Finally, the one-time schedule will asynchronously invoke my function, which is what the Destination is looking for.

And…(redacted) success!

2024 02 09 DestinationEmail

I could clean this email up with an EventBridge Input Path (which I’ve done before), but that’s mostly cosmetic in this case.

EventBridge

In this section I configure EventBridge – the AWS service that schedules the automation of my WordPress data extraction process. While I’ve used EventBridge Rules before, this is my first time using EventBridge Scheduler. So what’s the difference?

EventBridge Scheduler 101

From the AWS EventBridge product page:

Amazon EventBridge Scheduler is a serverless scheduler that enables you to schedule tasks and events at scale. With EventBridge Scheduler you have the flexibility to configure scheduling patterns, set a delivery window, and define retry policies to ensure your critical tasks and events are reliably triggered right when you need them.

https://aws.amazon.com/eventbridge/scheduler/

EventBridge Scheduler is a fully managed service that integrates with over 200 AWS services. It supports one-time schedules and start and end dates, and can account for daylight saving time.

Cost-wise, EventBridge Schedules are changed per invocation. EventBridge’s free tier covers the first 14 million(!) invocations each month, after which each further million currently costs $1.00. These invocations can be staggered using Flexible Time Windows to avoid throttling.

AWS has published a table showing the main differences between EventBridge Scheduler and Eventbridge Rules. Essentially, Eventbridge Rules are best suited for event-based activity, while EventBridge Scheduler is best suited for time-based activity.

Schedule Setup

Let’s create a new EventBridge Schedule. After choosing a name, I need a schedule pattern. Here, I want a recurring CRON-based schedule that runs at a specific time.

EventBridge Cron expressions have six required fields which are separated by white space. My cron expression is 0 7 * * ? * which translates to:

  • The 0th minute
  • Of the seventh hour
  • On every day of the month
  • Every month,
  • Day of the week,
  • And year

In response, EventBridge shows some of the future trigger dates so I can check my expression is correct:

Sat, 02 Feb 2024 07:00:00 (UTC+00:00)
Sun, 03 Feb 2024 07:00:00 (UTC+00:00)
Mon, 04 Feb 2024 07:00:00 (UTC+00:00)
Tue, 05 Feb 2024 07:00:00 (UTC+00:00)
Wed, 06 Feb 2024 07:00:00 (UTC+00:00)

I then need to choose a flexible time window setting. This setting distributes AWS service API calls to help prevent throttling, but that’s not a problem here so I select Off.

Next, I choose the target. I have two choices: templated targets or universal targets. Templated targets are a set of popular AWS service operations, needing only the relevant ARN during setup. Universal targets can target any AWS service but require more configuration details. Lambda’s Invoke operation is a targeted template, so I use that.

Next are some optional encryption, retry and state settings. EventBridge Scheduler IAM roles are handled here too, allowing EventBridge to send events to the targeted AWS services. Finally, a summary screen shows the full schedule before creation.

The schedule then appears on the EventBridge console:

2024 02 09 AmazonEventBridgeScheduler

EventBridge Testing

Testing time! Does CloudWatch show Lambda function invocations at 07:00?

It does!

2024 02 08 CloudWatchLogs

While I’m in CloudWatch, I’ll change the log group’s retention setting. It defaults to Never Expire, but I don’t need an indefinite history for this function! Three months is fine – long enough to troubleshoot any errors, but not so long that I’m storing and potentially paying for logs I’ll never need.

Costs

In this section, I examine the current AWS costs for my WordPress data extraction and automation processes using the Billing & Cost Management console.

I began creating pipeline resources in December 2023 using various workshops and tutorials. This table shows my AWS service costs (excluding tax) accrued over December 2023 and January 2024 (the months I currently have full billing periods for):

2024 02 09 Cost Explorer

I’ll examine these costs in two parts:

  • S3 Costs: my AWS costs are all storage-based. I’ll examine my S3 API calls and how each S3 API contributes to my bill.
  • Free Tier Usage: everything else has zero cost. I’ll examine what I used and how it compares to the free tier allowances.

I’ll also take a quick look at February’s costs to date. I’ve not tagged any of the pipeline resources, so these figures are for all activity in this AWS account.

S3 Costs

S3 is the only AWS service in my WordPress data extraction and automation processes that is generating a cost. This Cost Explorer chart shows my S3 API usage over the last two full months:

2024 02 10 Cost ExplorerS3APICalls

PutObject is clearly the most used S3 API, which isn’t surprising given S3’s storage nature. Cost Explorer can also show API request totals, as shown below:

2024 02 10 Cost ExplorerS3APICallsDec23
2024 02 10 Cost ExplorerS3APICallsJan24

Remember that this includes S3 API calls from other services like S3 Inventory, CloudTrail Log Steams and Athena queries.

AWS bills summarise these figures for easier reading. This is my December 2023 S3 bill, where S3 PUT, COPY, POST and LIST requests are grouped:

2024 02 09 Billing202312

January 2024’s bill:

2024 02 09 Billing202401

Going into this depth for $0.08 might not seem worth it. But if the bill suddenly becomes $8 or $80 then having this knowledge is very useful!

The AWS Storage blog has a great post on analyzing S3 API operations that really helped here.

Free Tier Usage

The following services had no cost because my usage fell within their free tier allowances. For each zero cost on the bill, I’ll show the service and, where appropriate, the respective free tier allowance.

CloudTrail:

  • 2023-12: 7970 Events recorded.
  • 2024-01: 6605 Events recorded.

CloudWatch was the same for both months:

  • Sub 1GB-Mo log storage used of 5GB-mo log storage free tier
  • Sub 1GB log data ingested of 5GB log data ingestion free tier

Lambda 2023-12:

  • 36.976 GB-Seconds used of 400,000 GB-seconds Compute free tier
  • 47 Requests used of 1,000,000 Request free tier

Lambda 2024-01:

  • 9.572 GB-Seconds used of 400,000 GB-seconds Compute free tier
  • 8 Requests used of 1,000,000 Request free tier

Parameter Store (billed as Secrets Manager):

  • 2023-12: 31 API Requests used of 10,000 API Request free tier
  • 2024-01: 41 API Requests used of 10,000 API Request free tier

February 2024 Costs

At this time I don’t have full billing data for February, but I wanted to show the EventBridge and SNS usage to date:

EventBridge (billed as CloudWatch Events):

  • 16 Invocations used of 14 million free tier

SNS:

  • 3 Notifications used of 1,000 Email-JSON Notification free tier
  • 227 API Requests used of 1,000,000 API Request free tier

As of Feb 15, Lambda is on 71.742 GB-Seconds and 34 Requests while S3 is on 8,821 PCPL requests, 3,764 GET+ requests and 0.0052 GB-Mo storage.

Resources

The full Python script has been checked into the amazonwebshark GitHub repo, available via the button below. Included is a requirements.txt file for the Python libraries used to extract the WordPress API data. This file is unchanged from last time but is included for completeness.

GitHub-BannerSmall

Summary

In this post, I set up the automation of my WordPress API data extraction script with AWS managed serverless services.

On the one hand, there’s plenty more to do here. I have lots to learn about Lambda, like deployment improvement and resource optimisation. This will improve with time and experience.

However, my function’s logging and alerting are in place, my IAM policies meet AWS standards and I’m using the optimal services for my compute and scheduling. And, most importantly, my automation pipeline works!

My attention now turns to the data itself. My next WordPress Data Pipeline post will look at transforming and loading the data so I can put it to use! If this post has been useful, the button below has links for contact, socials, projects and sessions:

SharkLinkButton 1

Thanks for reading ~~^~~

Categories
Developing & Application Integration

Stress-Free AWS Invoice PDF Administration With Power Automate

In this post, I create a stress-free AWS invoice PDF administration workflow with Microsoft Power Automate.

Table of Contents

Introduction

Each month, AWS sends me VAT invoices for all of my AWS accounts. Their filenames aren’t descriptive, with names like EUINGB21-2079861, EUINGB22-3276756 and EUINGB23-2216483. As a result, I rename them to make the contents clearer.

Now while I have the best intentions in the world, life happens and the PDFs usually end up in a to-do folder. As a result, I now have a folder like this:

2023 05 08 LakeFolder

I don’t want to sort through that. No one wants to sort through that. There must be another way! After successfully automating my application management with Winget, I’m keen to see what options I have here.

Firstly, let’s examine what currently happens with my new AWS invoice PDFs.

Manual Solution

This section examines my manual AWS invoice PDF workflow.

Here are several sample AWS invoices for reference. Although they’re from 2018, the 2023 bills are mostly the same. Let’s focus on a document header:

2023 05 08 AWSSampleInvoice

I copy the account number and VAT invoice date from a bill and combine them with some extra strings to produce a filename schema of:

AWS-{VAT Invoice Date}-{Account Number}-VATInvoice

In this example, that would produce:

AWS-2018-08-03-292122068034-VATInvoice

There are several pain points here:

  • The PDF must be opened to view the data.
  • The invoice date needs converting to ISO 8601 standard.
  • An open file can’t be renamed, so the new filename must be created in Notepad and then copy/pasted over.
  • Everything is manual and needs my full attention.

Wouldn’t it be nice if all this could be automated?

Microsoft Power Automate

This section examines Microsoft Power Automate and how it works.

What Is Power Automate?

Microsoft Power Automate is an automation tool. It uses triggers and actions to create workflows that complete manual, repetitive, and time-consuming tasks without human involvement. Power Automate is part of the Microsoft Power Platform.

Benefits of Power Automate include:

  • A no-code GUI with drag-and-drop functionality.
  • An extensive library of pre-defined templates.
  • Onboard testing and deployment features.
  • A free plan for work or school accounts.
  • Integration with the other Power Platform applications, Office 365 and other Microsoft and third-party services.

Power Automate Versions

Power Automate has two versions:

  • Power Automate Desktop focuses on desktop flows like file & folder manipulation, user input and script triggering. I’ll be using Power Automate Desktop in this post.

It’s worth pointing out that there are several visual differences between the two versions. This confused me when researching this post, as most screenshots I found were from the cloud service!

Power Automate Flows

Central to Power Automate is the concept of Flows. A Power Automate flow is a sequence of actions that are triggered by an event, performing a series of operations to achieve the desired results.

Flows are created through a visual interface that defines triggers, actions, and conditions using a drag-and-drop approach. Code can be written, but is optional. Completed flows can then be saved, published and run as needed.

Creating A Power Automate Flow

In this section, I create my AWS invoice Power Automate flow.

Getting All Files In The Folder

Firstly, I capture the AWS invoice PDFs using the Get Files In Folder action:

2023 05 11 PAGetFilesInFolder

Here, I specify the folder containing the invoices. I tell Power Automate to only get PDF files, to stop any other file types causing errors.

Power Automate stores a list of these files in a %Files% variable. I want to work on each file separately, so I pass the %Files% variable to a For Each action:

2023 05 11 PAForEach

This creates a loop that lets Power Automate capture each file in the %Files% variable. Each time the For Each action triggers, Power Automate stores a file from %Files% in a new %CurrentItem% variable.

Each subsequent action within the loop will then be applied to the %CurrentItem% file. At the end of the loop, Power Automate captures the next file from %Files% and overwrites the %CurrentItem% variable with it. This continues until all %Files% files are processed and the loop ends.

Getting The PDF Text

Secondly, I need to get the text from each %CurrentItem% PDF. There’s a great Extract Text From PDF action that can do this:

2023 05 11 PAExtractTextPDF

The PDF text is stored in an %ExtractedPDFText% variable that is going to be very useful for the rest of this flow!

So far, the flow looks like this:

2023 05 11 PAFlowFolderAndFiles

With the PDF text captured, the rest of the automation can begin!

Getting The Account Number

Next, let’s examine the PDF text more closely. This is part of %ExtractedPDFText% for an AWS invoice:

VAT Invoice
Email or talk to us about your AWS account or bill, visit console.aws.amazon.com/support
More information regarding your service charges is available by accessing your Billing Management Console
Account number:
[REDACTED]
Address:
[REDACTED]
Invoice Summary
VAT Invoice Number: EUINGB22-3617620
VAT Invoice Date: September 2, 2022
TOTAL AMOUNT GBP 0.34

On AWS invoices, the account number is always between ‘Account number:’ and ‘Address:’. Knowing this, I can use Crop Text to get the text between these strings:

2023 05 11 PACropTextAWSAccNo

This action produces two new variables:

  • %CroppedText%
  • %IsFlagFound%

Usually, these names would be fine. This isn’t the only text I’m going to be cropping though, so I change these to %AWSAccNoCroppedText% and %AWSAccNoIsFlagFound% respectively.

Before continuing, I’d like to check that %AWSAccNoCroppedText% contains the expected value. A good way to test this is to use the Display Message action:

2023 05 11 PAFlowCropAcctNo

(The account number is obfuscated here, but it was correct!)

Getting The Invoice Date

Now to get the invoice date! This is similar to getting the account number, using Crop Text to capture the date between ‘VAT Invoice Date:’ and ‘TOTAL AMOUNT’:

2023 05 11 PAAWSBillCropText

This produces variables %CroppedText% and %IsFlagFound% again, which I then change to %AWSBillDateCroppedText% and %AWSBillDateIsFlagFound%.

This isn’t enough this time though. Currently, the %AWSBillDateCroppedText% value is a date formatted as August 3, 2018. This won’t produce the filename I want, as I need the date to be in the ISO format of 2018-08-03.

To do this, I use the Convert Text To Datetime action. This converts a text representation of a date value to a datetime value:

2023 05 11 PAAWSBillTextoDatetime

I supply %AWSBillDateCroppedText% to convert. A datetime value of 03/08/2018 00:00:00 is returned, which I then assign to a %AWSBillDateTextAsDateTime% variable.

This is still unacceptable though! The date format doesn’t match the requirement and the slashes and colons are not legal filename characters.

To get the required date format, I use the Convert Datetime to Text action to convert 03/08/2018 00:00:00 to the custom format yyyy-MM-dd, in which:

  • yyyy is the year as a four-digit number.
  • MM is the month as a two-digit number, from 01 through 12.
  • dd is the day of the month as a two-digit number, from 01 through 31.
  • - is…a hyphen.
2023 05 11 AWSBillDateDatetimeToText

There is a table describing the custom date and time format specifiers in the Microsoft documentation.

This action produces a %AWSBillDateFormattedDateTime% variable with a value of 2018-08-03. Success!

Creating The New Filename

Now I can start thinking about the filename! To recap, I currently have:

  • A %AWSAccNoCroppedText% variable with a value of (for example) 1234123412341234.
  • A %AWSBillDateFormattedDateTime% variable with a value of 2018-08-03.

To create the filename, I need to combine my variables with some additional strings. For this, I use the Set Variable action to create a %AWSFilename% variable with the following value:

AWS-%AWSBillDateFormattedDateTime%-%AWSAccNoCroppedText%-VATInvoice

This breaks down to:

  • AWS- string
  • The %AWSBillDateFormattedDateTime% variable
  • A hyphen: -
  • The %AWSAccNoCroppedText% variable
  • -VATInvoice string

This should produce a string like AWS-2018-08-03-123456781234-VATInvoice. However, this was %AWSFilename%‘s initial output:

AWS-2018-08-03-
123456781234
-VATInvoice

This is unacceptable as a filename, but why is it happening?

It turns out that the Crop Text action capturing the account number is also capturing the line breaks on either side of it. I remove these with the Trim Text action:

2023 05 13 PATrimText

This creates a new %AWSAccNoTrimmedAndCroppedText% variable. Replacing %AWSAccNoCroppedText% with this new variable when setting the %AWSFilename% variable produces the required output:

AWS-2018-08-03-123456781234-VATInvoice

The %AWSFilename% variable can then be used by the Rename File(s) action to rename the file assigned to %CurrentItem% by the earlier For Each action:

2023 05 14 PARenameFiles

The updated file is assigned to a %AWSRenamedFiles% variable, which I use in the final part of the loop.

Moving The File

I usually move renamed invoices to a different path manually. I might as well add this process to my Power Automate flow since it will save me even more time!

Here, I move the file assigned to the %AWSRenamedFiles% variable to my destination folder using the Move File(s) action:

2023 05 14 PAMoveFIles

This is the last action in the loop. Or at least it was, until I reviewed the flow and identified a few improvements.

Enhancements

Originally, this AWS invoice Power Automate flow was just for AWS invoices. However, with some additional Power Automate actions the flow could easily extend to other kinds of invoices. This would allow one flow to administrate multiple invoice types instead of each type needing its own flow.

To begin, I limit the scope of the flow’s actions by adding an If action inside the For Each loop. It will now only apply the AWS invoice flow actions if %ExtractedPDFText% contains the string ‘AWS account or bill’:

2023 05 11 PAIf

Non-AWS invoices will not meet this condition, and will not be mislabeled by the flow or cause the flow to fail.

So what will happen to non-AWS invoices then? Currently, they’ll just be left in the source folder. Ideally, something should happen to them too, so let’s add a dead letter queue to the flow.

I add two new actions to the very end of the flow, positioned after the For Each loop has processed each file in the source folder:

2023 05 14 PAFlowDLQ

Both actions have already been used in this flow, but this time their focus has changed. By the time Power Automate reaches these actions, all files in the source folder will have gone through the For Each loop.

At this point, one of two sequences has happened:

  • The file did meet the AWS invoice condition, is renamed and then moved to the destination folder.
  • The file didn’t meet the AWS invoice condition and is still in the source folder.

So the only files now in the source folder are not AWS invoices.

The Get File In Folder action will get these files and store them in a %DLQFiles% variable. This is then passed to the Move File(s) action, which moves the unprocessed files to a PowerAutomateDLQ folder.

The end result is that:

  • The source folder is empty.
  • AWS invoices are renamed and then moved to the destination folder.
  • Any other files are moved to the PowerAutomateDLQ folder.

So, does it all work?

Testing

This section tests my AWS invoice Power Automate flow.

I put three files into the source folder and then ran the Power Automate flow:

  • A DALL-E PNG.
  • An AWS invoice PDF.
  • A utility bill PDF.
2023 05 14 TestFiles

The AWS invoice is renamed and then moved to the destination folder:

2023 05 13 TestAfter

The remaining files are unaltered and moved to the DLQ folder.

The flow also works far faster than my manual workflow. The old process – from opening the PDF to moving the renamed folder – took about 2 minutes each on average. Conversely, the flow processed 12 files in 2 seconds – this would have taken me 24 minutes!

Summary

In this post, I created a stress-free AWS invoice PDF administration workflow with Microsoft Power Automate.

I’m very impressed by Power Automate! It can certainly save me a ton of time with tasks I currently do, and with other actions like the ability to run Powershell and Python scripts, execute SQL statements and emulate the terminal, I feel like I haven’t even scratched the surface yet!

If this post has been useful, please feel free to follow me on the following platforms for future updates:

Thanks for reading ~~^~~

Categories
Developing & Application Integration

Production Code Qualities

In this post, I respond to November 2022’s T-SQL Tuesday #156 Invitation and give my thoughts on some production code qualities.

tsql tuesday

Table of Contents

Introduction

This month, Tomáš Zíka’s T-SQL Tuesday invitation was as follows:

Which quality makes code production grade?

Please be as specific as possible with your examples and include your reasoning.

Good question!

In each section, I’ll use a different language. Firstly I’ll create a script, and then show a problem the script could encounter in production. Finally, I’ll show how a different approach can prevent that problem from occurring.

I’m limiting myself to three production code qualities to keep the post at a reasonable length, and so I can show some good examples.

Precision

In this section, I use T-SQL to show how precise code in production can save a data pipeline from unintended failure.

Setting The Scene

Consider the following SQL table:

USE [amazonwebshark]
GO

CREATE TABLE [2022].[sharkspecies](
	[shark_id] [int] IDENTITY(1,1) NOT NULL,
	[name_english] [varchar](100) NOT NULL,
	[name_scientific] [varchar](100) NOT NULL,
	[length_max_cm] [int] NULL,
	[url_source] [varchar](1000) NULL
)
GO

This table contains a list of sharks, courtesy of the Shark Foundation.

Now, let’s say that I have a data pipeline that uses data in amazonwebshark.2022.sharkspecies for transformations further down the pipeline.

No problem – I create a #tempsharks temp table and insert everything from amazonwebshark.2022.sharkspecies using SELECT *:

When this script runs in production, I get two tables with the same data:

2022 11 02 SQLResults1

What’s The Problem?

One day a new last_evaluated column is needed in the amazonwebshark.2022.sharkspecies table. I add the new column and backfill it with 2019:

ALTER TABLE [2022].sharkspecies
ADD last_evaluated INT DEFAULT 2019 WITH VALUES
GO

However, my script now fails when trying to insert data into #tempsharks:

2022 11 02 SQLResults2Sharp
(1 row affected)

(4 rows affected)

Msg 213, Level 16, State 1, Line 17
Column name or number of supplied values does not match table definition.

Completion time: 2022-11-02T18:00:43.5997476+00:00

#tempsharks has five columns but amazonwebshark.2022.sharkspecies now has six. My script is now trying to insert all six sharkspecies columns into the temp table, causing the msg 213 error.

Doing Things Differently

The solution here is to replace row 21’s SELECT * with the precise columns to insert from amazonwebshark.2022.sharkspecies:

While amazonwebshark.2022.sharkspecies now has six columns, my script is only inserting five of them into the temp table:

2022 11 02 SQLResults3Sharp

I can add the last_evaluated column into #tempsharks in future, but its absence in the temp table isn’t causing any immediate problems.

Works The Same In Other Environments

In this section, I use Python to show the value of production code that works the same in non-production.

Setting The Scene

Here I have a Python script that reads data from an Amazon S3 bucket using a boto3 session. I pass my AWS_ACCESSKEY and AWS_SECRET credentials in from a secrets manager, and create an s3bucket variable for the S3 bucket path:

When I deploy this script to my dev environment it works fine.

What’s The Problem?

When I deploy this script to production, s3bucket will still be s3://dev-bucket. The potential impact of this depends on the AWS environment setup:

Different AWS account for each environment:

  • dev-bucket doesn’t exist in Production. The script fails.

Same AWS account for all environments:

  • Production IAM roles might not have any permissions for dev-bucket. The script fails.
  • Production processes might start using a dev resource. The script succeeds but now data has unintentionally crossed environment boundaries.

Doing Things Differently

A solution here is to dynamically set the s3bucket variable based on the ID of the AWS account the script is running in.

I can get the AccountID using AWS STS. I’m already using boto3, so can use it to initiate an STS client with my AWS credentials.

STS then has a GetCallerIdentity action that returns the AWS AccountID linked to the AWS credentials. I capture this AccountID in an account_id variable, then use that to set s3bucket‘s value:

More details about get_caller_identity can be found in the AWS Boto3 documentation.

For bonus points, I can terminate the script if the AWS AccountID isn’t defined. This prevents undesirable states if the script is run in an unexpected account.

Speaking of which…

Prevents Undesirable States

In this section, I use PowerShell to demonstrate how to stop production code from doing unintended things.

Setting The Scene

In June I started writing a PowerShell script to upload lossless music files from my laptop to one of my S3 buckets.

I worked on it in stages. This made it easier to script and test the features I wanted. By the end of Version 1, I had a script that dot-sourced its variables and wrote everything in my local folder $ExternalLocalSource to my S3 bucket $ExternalS3BucketName:

#Load Variables Via Dot Sourcing
. .\EDMTracksLosslessS3Upload-Variables.ps1


#Upload File To S3
Write-S3Object -BucketName $ExternalS3BucketName -Folder $ExternalLocalSource -KeyPrefix $ExternalS3KeyPrefix -StorageClass $ExternalS3StorageClass

What’s The Problem?

NOTE: There were several problems with Version 1, all of which were fixed in Version 2. In the interests of simplicity, I’ll focus on a single one here.

In this script, Write-S3Object will upload everything in the local folder $ExternalLocalSource to the S3 bucket $ExternalS3BucketName.

Problem is, the $ExternalS3BucketName S3 bucket isn’t for everything! It should only contain lossless music files!

At best, Write-S3Object will upload everything in the local folder to S3 whether it’s music or not.

At worst, if the script is pointing at a different folder it will start uploading everything there instead! PowerShell commonly defaults to C:\Windows, so this could cause all kinds of problems.

Doing Things Differently

I decided to limit the extensions that the PowerShell script could upload.

Firstly, the script captures the extensions for each file in the local folder $ExternalLocalSource using Get-ChildItem and [System.IO.Path]::GetExtension:

$LocalSourceObjectFileExtensions = Get-ChildItem -Path $ExternalLocalSource | ForEach-Object -Process { [System.IO.Path]::GetExtension($_) }

Then it checks each extension using a ForEach loop. If an extension isn’t in the list, PowerShell reports this and terminates the script:

ForEach ($LocalSourceObjectFileExtension In $LocalSourceObjectFileExtensions) 

{
If ($LocalSourceObjectFileExtension -NotIn ".flac", ".wav", ".aif", ".aiff") 
{
Write-Output "Unacceptable $LocalSourceObjectFileExtension file found.  Exiting."
Start-Sleep -Seconds 10
Exit
}

So now, if I attempt to upload an unacceptable .log file, PowerShell raises an exception and terminates the script:

**********************
Transcript started, output file is C:\Files\EDMTracksLosslessS3Upload.log

Checking extensions are valid for each local file.
Unacceptable .log file found.  Exiting.
**********************

While an acceptable .flac file will produce this message:

**********************
Transcript started, output file is C:\Files\EDMTracksLosslessS3Upload.log

Checking extensions are valid for each local file.
Acceptable .flac file.
**********************

To see the code in full, as well as the other problems I solved, please check out my post from June.

Summary

In this post, I responded to November 2022’s T-SQL Tuesday #156 Invitation and gave my thoughts on some production code qualities. I gave examples of each quality and showed how they could save time and prevent unintended problems in a production environment.

Thanks to Tomáš for this month’s topic! My previous T-SQL Tuesday posts are here.

If this post has been useful, please feel free to follow me on the following platforms for future updates:

Thanks for reading ~~^~~