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
Data & Analytics

Using Python & AWS To Extract WordPress API Data

In this post, I use popular Python modules & AWS managed serverless services to extract WordPress API data.

Table of Contents

Introduction

Last year, I tested my Python skills by analysing amazonwebshark’s MySQL database with Python. I could have done the same in 2024, but I wouldn’t have learned anything new and it felt a bit pointless. One of my YearCompass 2023-2024 goals is to build more, so I instead decided to create a data pipeline using popular Python modules & AWS services to extract my WordPress data using their API.

A data pipeline involves many aspects, which future posts will explore. This post focuses on extracting data from my WordPress database and storing it as flat files in AWS.

Firstly, I’ll discuss my architectural decisions for this part of the pipeline. Then I’ll examine the functions in my Python script that interact with AWS and perform data extraction. Finally, I’ll bring everything together and explain how it all works.

Architectural Decisions

In this section, I examine my architectural decisions and outline the pipeline’s processes.

Programming Language

My first decision concerned which programming language to use. I’m using Python here for several reasons:

  • I use Python at work and am always looking to refine my skills.
  • Several AWS services natively support Python.
  • Python SDKs like Boto3 and awswrangler support my use case.

Data Extraction

Next, I chose what data to extract from my WordPress MySQL database. I’m interested in the following tables, which are explained in greater detail in 2023’s Deep Dive post:

In November I migrated amazonwebshark to Hostinger, whose MySQL remote access policy requires an IP address. While this isn’t a problem locally, AWS is a different story. I’d either need an EC2 instance with a static IP, or a Lambda function with several networking components. These are time and money costs I’d prefer to avoid, so no calling the database.

Fortunately, WordPress has an API!

WordPress API

The WordPress REST API lets applications interact with WordPress sites by sending and receiving data as JSON objects. Public content like posts and comments are publicly accessible via the API, while private and password-protected content requires authentication.

While researching options, I stumbled across MiniOrange‘s Custom API for WordPress plugin. It has a simple interface and a good feature list:

Custom API for WordPress plugin allows you to create WordPress APIs / custom endpoints / REST APIs. You can Fetch / Modify / Create / Delete data with an easy-to-use graphical interface. You can also build custom APIs by writing custom SQL queries for your WP APIs.

https://plugins.miniorange.com/custom-api-for-wordpress

This meant I could start using it straight away!

The free plan lets users create as many endpoints as needed. But it also has a pretty vital limitation – API key authentication is only possible on their Premium plan. In the free plan, all endpoints are public!

Now let me be clear – this isn’t necessarily a problem. After all, the WordPress API is public! And my WordPress data doesn’t contain any PII or sensitive data. No – the risk I’m trying to address here isn’t a security one.

Public endpoints can be called by anyone or anything at any time. With WordPress, they have dedicated, optimised resources that auto-scale on demand. Whereas I have one Hostinger server that is doing every site process. Could it be DDoSed into oblivion by tons of API calls from bad actors? Do I want to find out?

As I’m using the plugin’s free tier here, I’ll mitigate my risks by:

  • Adding random strings to the endpoints to make them less guessable.
  • Not showing the endpoints in my script or this post.

So ok – how will I get the API endpoints then?

Parameters

Next, I need to decide how my script will get the endpoints to query and the S3 bucket name to store the results.

With previous scripts, I’ve used features like gitignore and dot sourcing to hide parameters I don’t want to expose. While this works, it isn’t ideal. Dot sourcing breaks if the file paths change, and even with gitignore any credentials are still hardcoded into the script locally.

A better approach is to use a process similar to a password manager, where an authenticated user or role can request and receive credentials using secure channels. AWS has two services for this requirement: AWS Secrets Manager and AWS Systems Manager Parameter Store.

Secrets Manager Vs Parameter Store

Secrets Manager is designed for managing and rotating sensitive information like database credentials, API keys, and other types of secrets. Conversely, Parameter Store is designed for storing configuration data, including plaintext or sensitive information, in a hierarchical structure.

I’m using Parameter Store here for two reasons:

Storage

Next, I need to decide where to store the API data. And I’m already using AWS for parameters, so I was always going to end up using S3. But what makes S3 an obvious fit here?

  • Integration: S3 is one of the oldest and most mature AWS services. It is well supported by both the Python SDK and other AWS services like EventBridge, Glue and Athena for processing and analysis.
  • Scalability: S3 will accept objects from a couple of bytes to terabytes in size (although if I’m generating terabytes of data here something is very wrong!). I can run my script at any time and as often as I want, and S3 will handle all the data it receives.
  • Cost: S3 won’t be entirely free here because I’ll be creating and accessing lots of data during testing. But even so, I expect it to cost me pence. I’m not keeping versions at this stage either, so my costs will only be for the current objects.

Much has been written about S3 over the years, so I’ll leave it at this.

Use Of Flat Files

Finally, let’s examine the decision to store flat files in the first place. The data is already in a database – why duplicate it?

Decoupling: Putting raw data into S3 at an early stage of the pipeline decouples the database at that point. Databases can become inaccessible, corrupted or restricted. The S3 data would be completely unaffected by these database issues, allowing the pipeline to persist with the available data.

Reduced Server Load: Storing data in S3 means the rest of the pipeline reads the S3 objects instead of the database tables. This reduces the Hostinger server’s load, letting it focus on transactional queries and site processes. S3 is almost serving as a read replica here.

Security: It is simpler for AWS services to access data stored in S3 than the same data stored on Hostinger’s server. AWS services accessing server data require MySQL credentials and a whitelisted IP. In contrast, AWS services accessing S3 data require…an IAM policy.

Architectural Diagram

This is an architectural diagram of the expected process:

  1. User triggers the Python function.
  2. Python interacts with AWS Python SDK.
  3. SDK calls Parameter Store for WordPress & S3 parameters. These are returned to Python via the SDK.
  4. Python calls WordPress API. WordPress API returns data.
  5. Python writes API data to S3 bucket via the SDK.

Setup & Config

I completed some local and cloud configurations before I started writing my Python script to extract WordPress API data. This section explores my laptop setup and AWS infrastructure.

Local Machine

I’m using Windows 10 and WSL version 2 to create a Linux environment with the Ubuntu 22.04.3 LTS distribution. I’m using Python 3.12, with a fresh Python virtual environment for installing my dependencies.

AWS Data Storage

I already have an S3 bucket for ingesting raw data, so that’s sorted. I made a wordpress-api prefix in that bucket to partition the uploaded data.

This bucket doesn’t have versioning enabled because it has a high object turnover. Versioning is unneeded and could get very expensive without a good lifecycle policy! While this would be simple to do, it’s a wasted effort at this point in the pipeline.

Another factor against versioning is that I can recreate S3 objects from the MySQL database. As objects are reproducible, there is no need for the delete protection offered by versioning.

AWS Parameters

I’m using Parameter Store to hold two parameters: my S3 bucket name and my WordPress API endpoints. Each of these uses a different parameter type.

The S3 bucket name is a simple string that uses the String Parameter Type. This is intended for blocks of text up to 4096 characters (4kb). The API endpoints are a collection of strings generated by the WordPress plugin. I use the StringList Parameter Type here, which is intended for comma-separated lists of values. This lets me store all the endpoints in a single parameter, optimising my code and reducing my AWS API calls.

Python Script

In this section, I examine the various parts of my Python script that will extract data from the WordPress API. This includes functions, methods and intended functionality.

Advisory

Before continuing I want to make something clear. This advisory is on my amazonwebshark artefacts GitHub repo, but it bears repeating here too:

Artefacts within this post have been created at a certain point in my learning journey. They do not represent best practices, may be poorly optimised or include unexpected bugs, and may become obsolete.

If I find better ways of doing these processes in future then I’ll link to or update posts where appropriate.

Logging

Firstly, I’ll sort out some logging.

The logging module is a core Python library, so I can import it without a pip install command. I then use logging‘s basicConfig function to set my desired parameters:

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

level sets the logging level to start at. logging.INFO records information about events like authentications, conversions and confirmations.

format sets how the logs will appear in the console. Sections enclosed by % and ( )s are placeholders that will be formatted as strings. Other characters are printed as-is. Here, my logs will return as Date/Time [Log Level]: Log Message.

datefmt sets the date/time format for format‘s asctime using the same directives as time.strftime().

These settings will give me logs in the style of:

2024-01-11 09:44:39 [INFO]: Parameter found.
2024-01-11 09:44:39 [INFO]: API endpoints returned.
2024-01-11 09:44:39 [INFO]: Getting S3 parameter...
2024-01-11 09:44:39 [WARNING]: S3 parameter not found!

This lets me keep track of what stage Python is at when I extract WordPress API data.

boto3 Session

To call the AWS services I want to use, I need to create a boto3 session. This object represents a single connection to AWS, encapsulating options including the configuration settings and credentials. Without this, Python cannot access AWS Parameter Store, and so cannot extract WordPress API data.

To begin, I run pip install boto3 in the terminal. I then script the following:

Python
import logging
import boto3

session = boto3.Session()

This code snippet performs two new actions:

  • Imports the boto3 module
  • Instantiates an instance of the boto3 module’s Session class.

As Session has no arguments, it will use the first AWS credentials it finds. In AWS, these will be from the Lambda function’s IAM role. No problems there. But I have several AWS profiles on my laptop, and my default profile is for a different AWS account!

In response, I can set an AWS profile using VSCode’s launch.json debugging object. By adding "env": {"AWS_PROFILE": "{my_profile_name}"} to the end of the configurations list, I can specify which local AWS profile to use without altering the Python script itself:

JSON
{
	"version": "0.2.0",
    "configurations": [
        {
            "name": "Python: Current File",
            "type": "python",
            "request": "launch",
            "program": "${file}",
            "console": "integratedTerminal",
            "justMyCode": true,
            "env": {"AWS_PROFILE": "profile"}
        }
    ]
}

Functions

This section examines my Python functions that extract WordPress API data. Each function has an embedded GitHub Gist and an explanation of the arguments and processes.

Get Parameters Function

Firstly, I need to get my parameter values from AWS Parameter Store.

Here, I define a get_parameter_from_ssm function that expects two arguments:

  • ssm_client: the boto3 client used to contact AWS.
  • parameter_name: the name of the required parameter.

I use type hints to annotate parameter_name and the returned object type as strings. For a great introduction to type hints, take a look at this short video from AWS Mad Lad Matheus Guimaraes:

I then create a try except block containing a response object which uses the ssm_client.get_parameter function to try getting the requested parameter. If this fails, the AWS error is logged and a blank string is returned. The parameter value is returned if successful.

I am capturing the AWS exceptions using the botocore module because it provides access to the underlying error information returned by AWS services. When an AWS service operation fails, it usually returns an error response that includes details about what went wrong. botocore can access these responses programmatically and log more exception details than the Python default.

I now have two additional changes to my main script:

Python
import logging
import boto3
import botocore

session = boto3.Session()
client_ssm = session.client('ssm')
  • botocore needs to be imported, so I add import botocore to the script. I don’t need to install botocore because it was installed with boto3.
  • I need a Simple Systems Manager (SSM) client to interact with AWS Systems Manager Parameter Store. I create an instance of the SSM client using my existing session and assign it to client_ssm. I can now use client_ssm throughout my script.

Get Filename Function

Next, I want to get each API endpoint’s filename. The filename has some important uses:

  • Logging processes without using the full endpoint.
  • Creating S3 objects.

A typical endpoint has the schema https://site/endpointname_12345/. There are two challenges here:

  • Extracting the name from the string.
  • Removing the name’s random characters.

I define a get_filename_from_endpoint function, which expects an endpoint argument with a string type hint and returns a new string.

Firstly, my name_full variable uses the rsplit method to capture the substring I need, using forward slashes as separators. This converts https://site/endpointname_12345/ to endpointname_12345.

Next, my name_full_last_underscore_index variable uses the rfind method to find the last occurrence of the underscore character in the name_full string.

Finally, my name_partial variable uses slicing to extract a substring from the beginning of the name_full string up to (but not including) the index specified by name_full_last_underscore_index. This converts endpointname_12345 to endpointname.

If the function is unable to return a string, an exception is logged and a blank string is returned instead.

No new imports are needed here. So let’s move on!

Call WordPress API Function

My next function queries a given API endpoint and handles the response.

Here, I define a get_wordpress_api_json function that expects three arguments:

  • requests_session
  • api_url: the WordPress API URL with a string type hint.
  • api_call_timeout: the number of seconds to wait for a response before timing out.

requests.Session is a part of the Requests library, and creates a session object that persists across multiple requests. I can now use the same session throughout the script instead of constantly creating new ones.

I open a try except block and create a response object. requests.Session attempts to call the API URL. If the response status code is 200 OK then the response is returned as a raw JSON dictionary.

This function can fail in three ways:

  • The status code isn’t 200. While this includes 3xx, 4xx and 5xx codes, it also includes the other 2xx codes. This was deliberate, as any 2xx responses other than 200 are still unusual, and something I want to know about.
  • The API call times out.
  • Requests throws an exception.

In all cases, the function raises an exception and doesn’t proceed. This was a conscious choice, as an API call failure represents a critical and unrecoverable problem with the WordPress API that should ring alarm bells.

As I’m using the Requests module now, I need to run pip install requests in the terminal and add import requests to my script. I then create my requests session in the same way as my boto3 session.

I’m also now using json – another pre-installed core Python module ready for import:

Python
import logging
import json
import requests
import boto3
import botocore

session = boto3.Session()
client_ssm = session.client('ssm')
requests_session = requests.Session()

S3 Upload Function

Finally, I need to put my JSON data into S3

I define a put_s3_object function that expects four arguments:

  • s3_client: the boto3 client used to contact AWS.
  • bucket: the S3 bucket to create the new object in
  • name: the name to use for the new object
  • json_data: the data to upload

I give string type hints to the bucket, name and json_data arguments. This is especially important for json_data because of what I plan to do with it.

I open a try except block and try to use put_s3_object to upload the JSON data to S3. In this context:

  • Body is the JSON data I want to store.
  • Bucket is the S3 bucket name from AWS Parameter Store.
  • Key is the S3 object key, using an f-string that includes the name from my get_filename_from_endpoint function.

The JSON data is created by my get_wordpress_api_json function, which returns that data as a dictionary. Passing a dictionary to put_s3_object‘s Body argument will throw a parameter validation error because its type is invalid for the Body parameter. json_data‘s string type hint will help prevent this scenario.

Moving on, the S3 client’s put_object function attempts to upload the data to the S3 bucket’s wordpress-api prefix as a new JSON object. If this operation succeeds, the function returns True. If it fails, a botocore exception is logged and the function returns False.

While no new imports are needed, I do now need an S3 client alongside the SSM one to allow S3 interactions:

Python
session = boto3.Session()
client_ssm = session.client('ssm')
client_s3 = session.client('s3')
requests_session = requests.Session()

Script Body

This section examines the body of my Python script. I look at the script’s flow, the objects passed to the functions and the responses to successful and failed processes.

Variables

In addition to the imports and sessions already listed, I have some additions:

  • The S3 bucket and WordPress API Parameter Store names.
  • An api_call_timeout value for the WordPress API requests in seconds.
  • Three endpoint counts used for monitoring failures, successes and overall progress.
Python
# Parameter Names
parametername_s3bucket = '/s3/lakehouse/name/raw'
parametername_wordpressapi = '/wordpress/amazonwebshark/api/mysqlendpoints'

# Counters
api_call_timeout = 30
endpoint_count_all = 0
endpoint_count_failure = 0
endpoint_count_success = 0

Getting The Parameters

The first part of the script’s body handles getting the AWS parameters.

Firstly, I pass my SSM client and WordPress API parameter name to my get_parameter_from_ssm function.

If successful, the function returns a comma-separated string of API endpoints. I transform this string into a list using .split(",") and assign the list to api_endpoints_list. Otherwise, an empty string is returned.

This empty string is unchanged by .split(",") and is assigned to api_endpoints_list. This is why get_parameter_from_ssm returns a blank string if it hits an exception. split(",") has no issues with a blank string, but throws attribute errors with returns like False and None.

I then check if api_endpoints_list contains anything using if not any(api_endpoints_list). return ends the script execution if the list contains no values, otherwise the number of endpoints is recorded.

A similar process happens with the S3 bucket parameter. My get_parameter_from_ssm function is called with the same SSM client and the S3 parameter name. This time a simple string is returned, so no splitting is needed. This string is assigned to s3_bucket, and if it’s found to be empty then return ends the current execution.

If both api_endpoints_list and s3_bucket pass their tests, the script moves on to the next section.

Getting The Data

The second part of the script’s body handles getting data from the API endpoints.

Firstly, I open a for loop for each endpoint in api_endpoints_list. I pass each endpoint to my get_filename_from_endpoint function to get the name to use for logging and object creation. This name is assigned to object_name.

object_name is then checked. If found to be empty, the loop skips that endpoint to prevent any useless API calls and to preserve the existing S3 data. The failure counter increments by 1, and continue ends the current iteration of the for loop.

Once the name is parsed, my Requests session, timeout values and current API endpoint are passed to the get_wordpress_api_json function. This function returns a JSON dictionary that I assign to api_json. api_json is then checked and, if empty, skipped from the loop using continue.

Next, I need to transform the api_json dictionary object before an S3 upload attempt. If I pass api_json to S3’s put_object as is, the Body parameter throws a ParamValidationError because it can’t accept dictionaries. I use the json.dumps function to transform api_json to a JSON-formatted string and assign it to api_json_string, which put_object‘s Body parameter can accept.

I can now pass my S3 client, S3 bucket name, object_name and api_json_string to my put_s3_object function. This function’s output is assigned to ok, which is then checked and updates the success or failure counter as appropriate.

Once all APIs are processed, the loop ends and the final success and failure totals are logged.

Adding A Handler

Finally, I encapsulate the script’s body into a lambda_handler function. Handlers let AWS Lambda run invoked functions, so I’ll need one when I deploy my script to the cloud.

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.

GitHub-BannerSmall

Summary

In this post, I used popular Python modules & AWS managed serverless services to extract WordPress API data.

I took a lot away from this! The script was a good opportunity to practise my Python skills and try out unfamiliar features like type hints, continue and requests.Session. Additionally, I made several revisions to control flows, logging and error handling that were triggered by writing this post. The script is clearer and faster as a result.

With the script complete, my next step will be deploying it to AWS Lambda and automating its execution. So keep an eye out for that! If this post has been useful, the button below has links for contact, socials, projects and sessions:

SharkLinkButton 1

Thanks for reading ~~^~~

Categories
Data & Analytics

WordPress MySQL Database Tables Deep Dive

In this post, I do a deep dive into some of the amazonwebshark WordPress MySQL database tables following the journey of a recent post.

Table of Contents

Introduction

In January I used Python and Matplotlib to create some visualisations using the WordPress amazonwebshark MySQL database.

Since then I’ve been doing a lot with Power BI at work, so I’ve created a Power BI connection to the amazonwebshark database to reacquaint myself with some features and experiment with a familiar dataset.

I talked about doing a views analysis in January’s post. While some of the 2022 data is missing, I can still accurately analyse 2023 data. I plan to measure:

  • Total views for each post.
  • Total views for each category.

I’ll use this post to examine some of the MySQL tables, and link back to it in future analysis posts.

Let’s begin with a brief WordPress database overview.

WordPress Database 101

In this section, I take a high-level view of a typical WordPress database and identify the tables I’ll need.

There’s plenty of great documentation online about typical WordPress installations. I’m particularly keen on The Ultimate Developer’s Guide to the WordPress Database by DeliciousBrains, which includes an in-depth tour of the various tables.

As for table relationships, this WordPress ERD shows object names, primary keys and relationship types:

I’ll be concentrating on these WordPress tables:

And the wp_statistics_pages table used by WPStatistics.

I’ll examine each table in the context of a recent post: DBeaver OpenAI ChatGPT Integration.

wp_posts

In this section of my WordPress database deep dive, I examine the most important WordPress database table: wp_posts.

Table Purpose

WordPress uses wp_posts to manage a site’s content. Each row in the table is an event relating to a piece of content, like a post, page or attachment. Examples of these events in the context of a blog post are:

  • Creating A New Draft: A new row is created with a post_status of draft. This row is the parent of all future activity for the blog post.
  • Updating A Draft: A new row is created with details of the update. The new row’s post_parent is set to the initial post’s ID.
  • Publishing A Draft: The initial row’s post_status is changed to publish, and the post_date is changed to the publication date. WordPress finds revisions to the post by filtering rows with a post_parent matching the initial row’s ID.

Post Journey

Let’s start by finding DBeaver OpenAI ChatGPT Integration‘s parent row, which is its earliest record. The following query finds rows where the post_title is DBeaver OpenAI ChatGPT Integration, then orders by ID and returns the first result.

SELECT 
  id, 
  post_date, 
  post_title, 
  post_status, 
  post_name, 
  post_parent, 
  post_type 
FROM 
  `wp_posts` 
WHERE 
  post_title = 'DBeaver OpenAI ChatGPT Integration' 
ORDER BY 
  id 
LIMIT 
  1

Note that I order by ID, not post_date. The publication process changes the parent post’s post_date, so I must use ID to find the earliest post.

This record is returned:

Name Value
ID 1902
post_date 2023-02-19 20:28:22
post_title DBeaver OpenAI ChatGPT Integration
post_status publish
post_name dbeaver-openai-chatgpt-integration
post_parent 0
post_type post

So the DBeaver OpenAI ChatGPT Integration parent row is ID 1902. I can use this to count the number of changes to this post by searching for wp_posts rows with a post_parent of 1902:

SELECT 
  COUNT(*) 
FROM 
  `wp_posts`
WHERE 
  post_parent = 1902

81 rows are returned:

Name    |Value|
--------+-----+
COUNT(*)|81   |

Now let’s examine these results more closely.

In the following query, I get all rows relating to DBeaver OpenAI ChatGPT Integration and then group the results by:

  • Date the post was made (using the MySQL DATE function to remove the time values for more meaningful aggregation).
  • Status of the post.
  • Post’s parent post.
  • Type of post.

I also count the rows that match each group and order the results by ID to preserve the event order:

SELECT 
  COUNT(*) AS ID_count, 
  DATE(post_date) AS post_date, 
  post_status, 
  post_parent, 
  post_type 
FROM 
  `wp_posts`
WHERE 
  ID = 1902 
  OR post_parent = 1902 
GROUP BY 
  DATE(post_date), 
  post_status, 
  post_parent, 
  post_type 
ORDER BY 
  ID

The query results are below. A couple of things to note:

  • The first two columns show what happens when a post is published. Row 1 is ID 1902 as it has no post_parent, and it has a post_status of publish and a post_date of 2023-02-19.
  • Row 2 is the first revision of ID 1902, and it has a post_status of inherit and a post_date of 2023-02-15. This is why I order by ID instead of post_date – ordering by post_date would show the revisions before the parent post in the results.
  • There are various post_type valves – revisions are text updates and attachments are image updates.
ID_count post_date post_status post_parent post_type
1 2023-02-19 publish 0 post
1 2023-02-15 inherit 1902 revision
19 2023-02-16 inherit 1902 revision
7 2023-02-16 inherit 1902 attachment
24 2023-02-17 inherit 1902 revision
1 2023-02-17 inherit 1902 attachment
7 2023-02-18 inherit 1902 revision
21 2023-02-19 inherit 1902 revision
1 2023-02-26 inherit 1902 revision

Spotlighting some of these results for context:

  • On 2023-02-16 there were 19 text revisions and 7 images attached. I save a lot!
  • On 2023-02-19 there were 21 text revisions and then the post was published.
  • There was a further text revision on 2023-02-26 in response to a DBeaver software update.

That’s enough about wp_posts for now. Next, let’s start examining how WordPress groups content.

wp_term_relationships

In this section, I examine the first of the WordPress taxonomy tables: wp_term_relationships.

Table Purpose

wp_term_relationships stores information about the relationship between posts and their associated taxonomy terms (More on taxonomies in the next section). WordPress uses it as a bridge table between wp_posts and the various taxonomy tables.

Post Journey

In this query, I join wp_term_relationships to wp_posts on object_id (this is ID in wp_posts), then find the rows where either wp_posts.id or wp_posts.post_parent is 1902:

SELECT 
  yjp.ID, 
  DATE(yjp.post_date) AS post_date, 
  yjp.post_type, 
  yjp.post_status,
  yjtr.object_id, 
  yjtr.term_taxonomy_id 
FROM 
  `wp_posts` AS yjp 
  INNER JOIN `wp_term_relationships` AS yjtr 
    ON yjtr.object_id = yjp.ID 
WHERE 
  yjp.ID = 1902 
  OR yjp.post_parent = 1902

wp_term_relationships only contains published posts, so the only rows returned concern the parent ID 1902:

ID post_date post_type post_status object_id term_taxonomy_id
1902 2023-02-19 post publish 1902 2
1902 2023-02-19 post publish 1902 69
1902 2023-02-19 post publish 1902 71
1902 2023-02-19 post publish 1902 74
1902 2023-02-19 post publish 1902 76
1902 2023-02-19 post publish 1902 77

The query returned six distinct wp_term_relationships.term_taxonomy_id values. My next step is to establish what these IDs relate to.

wp_term_taxonomy

In this section, I examine the table that groups term_taxonomy_id values into taxonomy types: wp_term_taxonomy.

Table Purpose

WordPress uses the wp_term_taxonomy table to store the taxonomy data for terms. Taxonomies in WordPress are used to group posts and custom post types together. Examples of WordPress taxonomies are category, post_tag and nav_menu.

Post Journey

In this query, I add a new join to the previous query, joining wp_term_taxonomy to wp_term_relationships on term_taxonomy_id. Some of the wp_posts columns have been removed from the query to save space.

SELECT 
  yjp.ID,  
  yjtr.term_taxonomy_id, 
  yjtt.taxonomy
FROM 
  `wp_posts` AS yjp 
  INNER JOIN `wp_term_relationships` AS yjtr 
    ON yjtr.object_id = yjp.ID 
  INNER JOIN `wp_term_taxonomy` AS yjtt 
    ON yjtr.term_taxonomy_id = yjtt.term_taxonomy_id 
WHERE 
  yjp.ID = 1902 
  OR yjp.post_parent = 1902

These results give some content to the previous results. I can now see that wp_posts.id 1902 has one category and five tags.

ID term_taxonomy_id taxonomy
1902 2 category
1902 69 post_tag
1902 71 post_tag
1902 74 post_tag
1902 76 post_tag
1902 77 post_tag

To get the names of the categories and tags, I must bring one more table into play…

wp_terms

In this section of my WordPress database deep dive, I examine the table that holds the names and details of the taxonomy terms used on amazonwebshark: wp_terms.

Table Purpose

The wp_terms table stores all of the terms that are used across all taxonomies on a WordPress site. Each row represents a single term, and the columns in the table contain information about that term, including name and ID.

Post Journey

In this query, I add another join to the previous query, joining wp_terms to wp_term_taxonomy on term_id.

SELECT 
  yjp.ID, 
  yjtr.term_taxonomy_id, 
  yjtt.taxonomy,
  yjt.name 
FROM 
  `wp_posts` AS yjp 
  INNER JOIN `wp_term_relationships` AS yjtr 
    ON yjtr.object_id = yjp.ID 
  INNER JOIN `wp_term_taxonomy` AS yjtt 
    ON yjtr.term_taxonomy_id = yjtt.term_taxonomy_id 
  INNER JOIN `wp_terms` AS yjt 
    ON yjtt.term_id = yjt.term_id 
WHERE 
  yjp.ID = 1902 
  OR yjp.post_parent = 1902

The results now identify the category and each of the five tags by name:

ID term_taxonomy_id taxonomy name
1902 2 category AI & Machine Learning
1902 69 post_tag WordPress
1902 71 post_tag DBeaver
1902 74 post_tag MySQL
1902 76 post_tag OpenAI
1902 77 post_tag ChatGPT

This is a perfect match for the post’s taxonomy in the WordPress portal:

2023 03 10 WordPressPanelChatGPT

So that’s the categories. What about the views?

wp_statistics_pages

In this final section, I examine the WPStatistics table that holds view counts: wp_statistics_pages.

Table Purpose

WPStatistics uses wp_statistics_pages to store data about page views. Each row shows a URI’s total views on the date specified.

WPStatistics documentation isn’t as in-depth as WordPress, so here are the table’s DDL and column descriptions:

CREATE TABLE `1yJ_statistics_pages` (
  `page_id` bigint(20) NOT NULL AUTO_INCREMENT,
  `uri` varchar(190) NOT NULL,
  `type` varchar(180) NOT NULL,
  `date` date NOT NULL,
  `count` int(11) NOT NULL,
  `id` int(11) NOT NULL,
  PRIMARY KEY (`page_id`),
  UNIQUE KEY `date_2` (`date`,`uri`),
  KEY `url` (`uri`),
  KEY `date` (`date`),
  KEY `id` (`id`),
  KEY `uri` (`uri`,`count`,`id`)
)
Table NameDescription
page_idPrimary key. Unique identifier for the table.
uriUniform Resource Identifier used to access a page.
typeuri type: home / page / post
dateDate the uri was viewed
counturi total views on the specified date
iduri ID in wp_posts.ID

Post Journey

As wp_statistics_pages.id is the same as wp_posts.id, I can use id 1902 in a query knowing it will still refer to DBeaver OpenAI ChatGPT Integration.

For example, this query counts the number of rows in wp_statistics_pages relating to id 1902:

SELECT 
  COUNT(*) 
FROM 
  `wp_statistics_pages` 
WHERE 
  id = 1902
COUNT(*)|
--------+
      14|

I can also calculate how many visits DBeaver OpenAI ChatGPT Integration has received by using SUM on all wp_statistics_pages.count values for id 1902:

SELECT 
  SUM(yjsp.count) 
FROM 
  `wp_statistics_pages` AS yjsp
WHERE 
  yjsp.id = 1902
SUM(count)|
----------+
        40|

So the page currently has 40 views. I can see how these views are made up by selecting and ordering by wp_statistics_pages.date:

SELECT 
  yjsp.date, 
  yjsp.count 
FROM 
  `wp_statistics_pages` AS yjsp
WHERE 
  yjsp.id = 1902 
ORDER BY 
  yjsp.date

date count
2023-02-19 1
2023-02-20 5
2023-02-21 1
2023-02-22 4
2023-03-07 6
2023-03-08 3
2023-03-09 2
2023-03-10 1

I can also join wp_posts to wp_statistics_pages on their id columns, bridging the gap between the WPStatistics table and the standard WordPress tables:

SELECT 
  yjsp.date, 
  yjsp.count, 
  yjp.post_title 
FROM 
  `wp_statistics_pages` AS yjsp 
  INNER JOIN `wp_posts` AS yjp 
    ON yjsp.id = yjp.id 
WHERE 
  yjsp.id = 1902 
ORDER BY 
  yjsp.date
date count post_title
2023-02-19 1 DBeaver OpenAI ChatGPT Integration
2023-02-20 5 DBeaver OpenAI ChatGPT Integration
2023-02-21 1 DBeaver OpenAI ChatGPT Integration
2023-02-22 4 DBeaver OpenAI ChatGPT Integration
2023-03-07 6 DBeaver OpenAI ChatGPT Integration
2023-03-08 3 DBeaver OpenAI ChatGPT Integration
2023-03-09 2 DBeaver OpenAI ChatGPT Integration
2023-03-10 1 DBeaver OpenAI ChatGPT Integration

Summary

In this post, I did a deep dive into some of the amazonwebshark WordPress MySQL database tables following the journey of a recent post.

I’ve used this post to present the journey a typical post goes through in the WordPress database. Future posts will use this knowledge and the WordPress database as a data source for various dashboards, scripting and processes. Watch this space!

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

Thanks for reading ~~^~~