Tag: SQL

Building A Basic Amazon Bedrock Structured Data Knowledge Base

Post author By Damien Jones
Post date December 31, 2025

In this post, I create a structured data knowledge base using Amazon Bedrock and test it by allowing it to query a database directly. Here’s what worked, what didn’t, and what it cost.

Introduction

In my last post, I built an unstructured Bedrock knowledge base using an S3-backed vector store. I now want to see how Bedrock behaves when it can use structured, relational data instead.

A Bedrock structured knowledge base doesn’t use embeddings or vector search. Instead, it relies on the database schema. Bedrock generates SQL queries from natural language prompts, executes them directly against the data source and then formats the results into a user response.

While researching November’s post, I checked the query engine options for a Bedrock structured data knowledge base out of curiosity. I was greeted by a choice of…Redshift:

So fair enough – Redshift it is!

In this post, I will first load curated Parquet data into Redshift Serverless via S3. Next, I’ll create a Bedrock structured data knowledge base and synchronise it with my data.

Then I’ll pose increasingly challenging questions to the knowledge base, ranging from simple counts to complex aggregations. I’ll assess Bedrock’s strengths, analyse any misconceptions, and examine how prompt phrasing can affect results. Finally, I’ll review the costs associated with both Bedrock and Redshift.

To manage expectations, this post is essentially a POC. As such, the following are out of scope:

Benchmarking SQL or model performance.
Evaluating general model accuracy.
Production hardening.

Now, let’s get some data into AWS.

Data Preparation

In this section, I’ll create a minimal data pipeline using Parquet, S3 and Redshift Serverless.

Dataset Context

Let’s begin by talking about the data.

The data is sourced from my personal iTunes library and represents a snapshot of track-level metadata at a specific time. Each row corresponds to a single track, with columns capturing both standard attributes and user events.

Some columns mentioned later should be explicitly defined:

mtg_artist – The artist associated with the track.
mtg_publisher – The record label or publisher associated with the track.
my_rating – A personal track rating assigned in iTunes, on a fixed numeric scale.
plays – The total number of times each track was played on iTunes at the time of the snapshot.

File Creation

This process starts by creating a Parquet file. Parquet has been an industry standard for a while now, has many benefits and is ideal for this use case.

Now – full disclosure: this process is currently a bit hacky and uses an ad-hoc local Python script. I will write a proper ETL process as part of Project Wolfie. For now, it’s a combination of pandas .join and .to_parquet functions.

As for the data itself, I did mention it briefly in my February 2025 and May 2025 posts, but admittedly not in much depth. Project Wolfie’s Roadmap does include data dictionaries for all data sources, so they will exist – they just don’t currently. That said, the absence of data dictionaries won’t significantly impact this post, as my main focus is on how the Bedrock knowledge base interacts with the structured data.

S3 Setup

Next, let’s create an S3 bucket. I’ve created a temporary new bedrock-test general-purpose bucket for this post with default config. I then created an iTunesTest folder and uploaded my curated_data.parquet file. The names of the folders and buckets aren’t mega important here; what matters most is knowing the S3 path, as I’ll need it soon.

With the S3 bucket taken care of, it’s time to sort out Redshift. But which Redshift?

Redshift Provisioned Vs Redshift Serverless

There are currently two Redshift offerings, each with its own implementation.

Redshift Provisioned is the original deployment model, in which users create and manage a fixed-size cluster with specific node types. Capacity is chosen upfront, and the cluster is always available.

Redshift Provisioned is ideal for steady workloads that need consistent performance and precise control. However, it requires capacity planning and incurs charges even when not in use, making it less suitable for small or irregular workloads.

Redshift Serverless removes the need for cluster management by automatically adjusting compute capacity in response to demand. It mostly behaves like a standard Redshift endpoint, while AWS oversees the underlying infrastructure.

Redshift Serverless is ideal for low-volume or infrequent workloads, as you only pay for the compute power you consume and don’t need to manage capacity. However, it offers less control than provisioned clusters, and costs may exceed those of provisioned clusters if usage goes beyond a certain threshold.

Here, I’m using Redshift Serverless because:

Intermittent Workload: The data will be queried infrequently, so an always-on cluster is unnecessary.
Operational Simplicity: I want to focus on the AI POC, not on managing and sizing infrastructure.
Free Trial: Redshift Serverless offers a free trial for new users. It’d be rude not to…

Decision made. Next, let’s configure Redshift Serverless.

Redshift Serverless Setup

One of Redshift’s Serverless perks is its simple setup. Instead of explaining this myself, I’ll refer to two AWS videos. The first focuses mainly on the settings:

And the second explores Redshift’s Query Editor:

Here, I use the defaults again. I end up with a workgroup for controlling resources, scaling and execution prioritisation, and a namespace for holding tables, workgroups, and other database resources:

Next, I need to put my Parquet data into a Redshift table. I’ll be using Redshift’s COPY command for this, but as the target table must already exist in the database, I need to write a Data Definition Language (DDL) command first.

To make the process as painless as possible, I write a quick pandas Python script to get the curated_data.parquet file schema:

Python

import pandas as pd

# Use .read_parquet to capture the data in a DataFrame
df = pd.read_parquet("curated_data.parquet")

# Use .dtypes to get the schema
print(df.dtypes)

import pandas as pd

# Use .read_parquet to capture the data in a DataFrame
df = pd.read_parquet("curated_data.parquet")

# Use .dtypes to get the schema
print(df.dtypes)

Plaintext

Name object
Artist object
Album object
Work object
Genre object
Size int64
Time int64
...

Name object
Artist object
Album object
Work object
Genre object
Size int64
Time int64
...

And then pass the output to Amazon Q Developer to write a DDL statement for Redshift:

Convert this Parquet schema into a Redshift DDL statement:

Name object Artist object Album object Work object Genre object Size int64 Time int64

…

Plaintext

Here's the Redshift DDL statement for your Parquet schema:

CREATE TABLE itunes_data(
    Name VARCHAR(500),
    Artist VARCHAR(500),
    Album VARCHAR(500),
    Work VARCHAR(500),
    Genre VARCHAR(100),
    Size BIGINT,
    Time BIGINT
    ...

Key mappings:

int32 → INTEGER
int64 → BIGINT
float64 → DOUBLE PRECISION
datetime64 → TIMESTAMP
object → VARCHAR(500) (adjust size as needed for your data)

Here's the Redshift DDL statement for your Parquet schema:

CREATE TABLE itunes_data(
    Name VARCHAR(500),
    Artist VARCHAR(500),
    Album VARCHAR(500),
    Work VARCHAR(500),
    Genre VARCHAR(100),
    Size BIGINT,
    Time BIGINT
    ...

Key mappings:

int32 → INTEGER
int64 → BIGINT
float64 → DOUBLE PRECISION
datetime64 → TIMESTAMP
object → VARCHAR(500) (adjust size as needed for your data)

I then run this script in Redshift to create an empty itunes_data table. COPY now has everything it needs to put the data into Redshift:

SQL

COPY dev.public.itunes_data
FROM 's3://bedrock-test/iTunesTest/curated_data.parquet'
IAM_ROLE 'arn:aws:iam::XXXXXXXXXX:role/service-role/AmazonRedshift-CommandsAccessRole-20251225T125455'
PARQUET

COPY dev.public.itunes_data
FROM 's3://bedrock-test/iTunesTest/curated_data.parquet'
IAM_ROLE 'arn:aws:iam::XXXXXXXXXX:role/service-role/AmazonRedshift-CommandsAccessRole-20251225T125455'
PARQUET

In this command:

COPY is the name of the target Redshift table.
FROM is the path to the S3 objects containing my data.
IAM_ROLE is the method the cluster uses to authenticate with, in this case, S3. This role was created during the Redshift Serverless default setup process.
PARQUET tells Redshift what format the data is in.

Full documentation is at the COPY from Amazon S3 page. The end result is a Redshift table containing the Parquet data:

I have now prepared my data. Now I can start on the knowledge base!

Knowledge Base Build

In this section, I’ll build a Bedrock structured data knowledge base. Firstly, I’ll set up my query engine and permissions. Next, I’ll create a Bedrock knowledge base and connect it to my Redshift structured data store. Finally, I’ll synchronise the Rdshift structured data store with the Bedrock knowledge base to enable querying.

Knowledge Base Setup

Configuring a Bedrock structured data knowledge base starts out very similarly to my recent unstructured one, albeit with one noticeable difference at the start:

This leads to the Query Engine selection screen shown earlier. After selecting Redshift and either creating or selecting an IAM service role for Bedrock, I must then configure the connection options for my chosen Redshift offering:

The next step identifies where the structured data is stored. In this case, my itunes_data table is located in the dev database’s pubic schema, so I select dev from the database list:

I must now give authentication information to connect to my Redshift database. I can choose between the IAM role created in the previous step and AWS Secrets Manager credentials for authentication.

With this completed, the knowledge base has been created. The next step is the synchronisation process.

Data Store Sync

Currently, Bedrock has visibility into the schema but not into the data itself. As with an unstructured data knowledge base, I must synchronise the Redshift structured data with my Bedrock knowledge base to make the data queryable:

If the credentials are incorrect or lack proper database permissions, the sync will fail:

Bedrock logs issues for review. Typically, IAM updates and/or SQL grants are needed to resolve them.. Here, my Bedrock Execution Role needed access to the public Redshift schema:

SQL

GRANT USAGE ON SCHEMA public TO "IAMR:AmazonBedrockExecutionRoleForKnowledgeBase";

GRANT SELECT ON ALL TABLES IN SCHEMA public TO "IAMR:AmazonBedrockExecutionRoleForKnowledgeBase";

GRANT USAGE ON SCHEMA public TO "IAMR:AmazonBedrockExecutionRoleForKnowledgeBase";

GRANT SELECT ON ALL TABLES IN SCHEMA public TO "IAMR:AmazonBedrockExecutionRoleForKnowledgeBase";

With this done, my sync succeeds and I can start testing my knowledge base.

Knowledge Base Testing

In this section, I will evaluate the Bedrock knowledge base by asking increasingly complex queries. I will start with simple retrievals and then introduce complexity and analytical challenges through ranking, filtering, and aggregation. My goal is to assess comprehension, schema understanding and SQL correctness.

Bedrock isn’t just suggesting SQL here. Instead, it generates and executes SQL directly in Redshift using the knowledge base’s execution role. The queries presented are part of an active execution path rather than just illustrative pseudocode. If the SQL generated is invalid or poorly structured, the query will fail just as it would if I ran it myself.

For each query, I will provide the question I submitted to Bedrock, the SQL that Bedrock generated, and a screenshot of the results. The SQL here is formatted by the excellent CodeBeautify SQL Formatter.

Initial Config

Before I can give queries to the knowledge base, I must configure two settings. Firstly, I must identify the type of response I want:

Retrieval Only: Bedrock provides the results of executing an SQL query.
Retrieval and Generation: Bedrock generates a response based on the outcome of executing the SQL query.
Generate SQL Query: Bedrock transforms the query into SQL.

I’ll use Retrieval and Generation since it returns both the response and the generated SQL. I also need to select a model for Bedrock to use for response generation. In this case, Nova Lite is a suitable option:

The knowledge base can now respond to queries.

Simple Queries

Let’s start by asking about the data as a whole:

How many tracks are in the collection?

Bedrock handles this easily. It generates a straightforward COUNT(*) query and gets the correct number:

SQL

SELECT 
  COUNT(*) AS "total_tracks" 
FROM 
  public.itunes_data;

SELECT 
  COUNT(*) AS "total_tracks" 
FROM 
  public.itunes_data;

Next, let’s dig a little deeper. This time I ask about a specific record label:

How many tracks are produced by Anjunabeats?

This should be in the hundreds, but Bedrock returns a far different number:

SQL

SELECT 
  COUNT(*) AS "track_count" 
FROM 
  public.itunes_data 
WHERE 
  artist = 'Anjunabeats';

SELECT 
  COUNT(*) AS "track_count" 
FROM 
  public.itunes_data 
WHERE 
  artist = 'Anjunabeats';

This isn’t a problem with Bedrock. Rather, this is ambiguity within my data. The first Anjunabeats release was Anjunabeats – Volume One, meaning this track and its remixes have both an artist and a publisher of Anjunabeats.

*Yes ok this is ANJ003 but the cover is nicer – Ed*

Here, Bedrock interprets ‘produced’ in the artist sense and queries on the artist column. Instead, it should use the mtg-publisher column, but it has no way to know that. In response, I change the prompt to use ‘published’ instead of ‘produced’ and try again.

How many tracks are published by Anjunabeats?

This time, Bedrock uses my signposting and gets the correct answer by querying mtg-publisher:

SQL

SELECT 
  COUNT(*) AS "track_count" 
FROM 
  public.itunes_data 
WHERE 
  "mtg-publisher" = 'Anjunabeats';

SELECT 
  COUNT(*) AS "track_count" 
FROM 
  public.itunes_data 
WHERE 
  "mtg-publisher" = 'Anjunabeats';

So the first issue I encountered wasn’t related to SQL at all; it was the ambiguity in how Bedrock interpreted my prompt. There are more effective ways to address this ambiguity that I will explore later in this section.

Multi-Constraint Queries

Now let’s try some more demanding questions. I’m still asking about Anjunabeats releases, but I’m also now asking about track ratings:

What are the highest-rated tracks published by Anjunabeats?

Bedrock correctly selects the name and my_rating columns, and also decides to order the results despite not being asked to – nice touch!

Unfortunately, Bedrock then slips back into old habits and, despite my prompt continuing to use published, assumes I’m asking about Anjunabeats – Volume One again and queries one of the artist columns:

SQL

SELECT 
  "name", 
  "my_rating" 
FROM 
  public.itunes_data 
WHERE 
  "mtg-albumartist" = 'Anjunabeats' 
ORDER BY 
  "my_rating" DESC 
LIMIT 10;

SELECT 
  "name", 
  "my_rating" 
FROM 
  public.itunes_data 
WHERE 
  "mtg-albumartist" = 'Anjunabeats' 
ORDER BY 
  "my_rating" DESC 
LIMIT 10;

So Bedrock tells me about the various Volume One remixes I own:

Ok – let’s try changing the prompt. Now, I’m asking firstly about the record label, and then the ratings:

What are the tracks published by Anjunabeats with the highest rating?

This time Bedrock queries on mtg-publisher and the results are correct:

SQL

SELECT 
  "name", 
  "my_rating" 
FROM 
  public.itunes_data 
WHERE 
  "mtg-publisher" = 'Anjunabeats' 
ORDER BY 
  "my_rating" DESC 
LIMIT 10;

SELECT 
  "name", 
  "my_rating" 
FROM 
  public.itunes_data 
WHERE 
  "mtg-publisher" = 'Anjunabeats' 
ORDER BY 
  "my_rating" DESC 
LIMIT 10;

My collection has many top-rated Anjunabeats tracks, so let’s narrow it down by asking about play counts:

Tell me about the tracks published by Anjunabeats with the highest rating. I want to know which tracks have the most plays. Give me the artist, title and year.

Bedrock modifies its query. The artist, title and year columns are all correctly selected, and it makes sense to remove my_rating since the tracks I’m asking about all have the same rating value. The query also now has i aliases – not much use for this query but helpful for joins and window functions.

Unfortunately, although I asked for tracks in the plural, Bedrock returns only 1 result:

SQL

SELECT 
  i.artist, 
  i.name, 
  i.year 
FROM 
  public.itunes_data i 
WHERE 
  i."mtg-publisher" = 'Anjunabeats' 
ORDER BY 
  i.my_rating DESC, 
  i.plays DESC 
LIMIT 1;

SELECT 
  i.artist, 
  i.name, 
  i.year 
FROM 
  public.itunes_data i 
WHERE 
  i."mtg-publisher" = 'Anjunabeats' 
ORDER BY 
  i.my_rating DESC, 
  i.plays DESC 
LIMIT 1;

Why? No idea. To prevent this from happening again, I request five tracks along with some additional fields.

Tell me about the tracks published by Anjunabeats with the highest rating. I want to know the 5 tracks with the most plays. Give me the artist, title, year, initial key and BPM for each track.

Bedrock responds by…talking about Volume One again:

SQL

SELECT 
  "artist", 
  "name", 
  "mtg-year", 
  "mtg-initialkey", 
  "mtg-bpm" 
FROM 
  public.itunes_data 
WHERE 
  "mtg-albumartist" = 'Anjunabeats' 
ORDER BY 
  "plays" DESC 
LIMIT 5;

SELECT 
  "artist", 
  "name", 
  "mtg-year", 
  "mtg-initialkey", 
  "mtg-bpm" 
FROM 
  public.itunes_data 
WHERE 
  "mtg-albumartist" = 'Anjunabeats' 
ORDER BY 
  "plays" DESC 
LIMIT 5;

This is definitely something to consider for the future. Anjunabeats isn’t the only Artist/Publisher combo in this dataset! And while I love me some Volume One, it’s not this query’s focus.

So I spell it out in the prompt’s first line:

Tell me about the tracks where the publisher is Anjunabeats. I want to know the 5 tracks with the highest rating and the most plays. Give me the artist, title, year, initial key, BPM and play count for each track.

Success!

SQL

SELECT 
  "artist", 
  "name", 
  "mtg-year", 
  "mtg-initialkey", 
  "mtg-bpm", 
  "plays" 
FROM 
  public.itunes_data 
WHERE 
  "mtg-publisher" = 'Anjunabeats' 
ORDER BY 
  "my_rating" DESC, 
  "plays" DESC 
LIMIT 5;

SELECT 
  "artist", 
  "name", 
  "mtg-year", 
  "mtg-initialkey", 
  "mtg-bpm", 
  "plays" 
FROM 
  public.itunes_data 
WHERE 
  "mtg-publisher" = 'Anjunabeats' 
ORDER BY 
  "my_rating" DESC, 
  "plays" DESC 
LIMIT 5;

2025 12 29 1607 FIveTracksMostPlayedRight

(If these play counts seem low for two-decade-old tracks, all I can say is that record decks don’t collect metadata – Ed)

Complex Query

Let’s see how far we can go! For the last test, I started with a fairly hefty prompt:

Tell me about the tracks where the publisher is Anjunabeats. I want to know about the tracks with the highest rating. I want the following about the tracks meeting these criteria:

– An overall total of tracks

– Totals for each initial key, ordered by initial key

– Totals for each decade (eg 2000 to 2009, 2010 to 2019 etc)

And, well, I broke it:

Bedrock gave it a good go, but the SQL it sent to Redshift was invalid:

SQL

SELECT 
  COUNT(*) AS "Total Tracks", 
  COUNT(DISTINCT "mtg-initialkey") AS "Total Tracks with Initial Key", 
  COUNT(
    CASE WHEN "mtg-publisher" = 'Anjunabeats' THEN 1 END
  ) AS "Total Anjunabeats Tracks", 
  COUNT(
    CASE WHEN "mtg-publisher" = 'Anjunabeats' 
    AND my_rating IS NOT NULL THEN 1 END
  ) AS "Total Anjunabeats Tracks with Rating", 
  (
    SELECT 
      MAX(my_rating) 
    FROM 
      public.itunes_data 
    WHERE 
      "mtg-publisher" = 'Anjunabeats' 
      AND my_rating IS NOT NULL
  ) AS "Highest Rated Anjunabeats Track", 
  SUM(
    CASE WHEN "mtg-publisher" = 'Anjunabeats' 
    AND my_rating IS NOT NULL THEN 1 ELSE 0 END
  ) OVER (
    PARTITION BY FLOOR("mtg-year" / 10)
  ) AS "Totals by Decade" 
FROM 
  public.itunes_data 
WHERE 
  "mtg-publisher" = 'Anjunabeats' 
  AND my_rating IS NOT NULL 
ORDER BY 
  "mtg-initialkey";

SELECT 
  COUNT(*) AS "Total Tracks", 
  COUNT(DISTINCT "mtg-initialkey") AS "Total Tracks with Initial Key", 
  COUNT(
    CASE WHEN "mtg-publisher" = 'Anjunabeats' THEN 1 END
  ) AS "Total Anjunabeats Tracks", 
  COUNT(
    CASE WHEN "mtg-publisher" = 'Anjunabeats' 
    AND my_rating IS NOT NULL THEN 1 END
  ) AS "Total Anjunabeats Tracks with Rating", 
  (
    SELECT 
      MAX(my_rating) 
    FROM 
      public.itunes_data 
    WHERE 
      "mtg-publisher" = 'Anjunabeats' 
      AND my_rating IS NOT NULL
  ) AS "Highest Rated Anjunabeats Track", 
  SUM(
    CASE WHEN "mtg-publisher" = 'Anjunabeats' 
    AND my_rating IS NOT NULL THEN 1 ELSE 0 END
  ) OVER (
    PARTITION BY FLOOR("mtg-year" / 10)
  ) AS "Totals by Decade" 
FROM 
  public.itunes_data 
WHERE 
  "mtg-publisher" = 'Anjunabeats' 
  AND my_rating IS NOT NULL 
ORDER BY 
  "mtg-initialkey";

Plaintext

Redshift query execution failed with error: 
ERROR: column "itunes_data."mtg-publisher"" must appear 
in the GROUP BY clause or be used in an aggregate function.

Redshift query execution failed with error: 
ERROR: column "itunes_data."mtg-publisher"" must appear 
in the GROUP BY clause or be used in an aggregate function.

Fair enough. I would probably break this into multiple queries as well. Let’s ease the pressure a bit by splitting the prompt.

Complex Query: Totals

Firstly, let’s request the overall total of tracks published by Anjunabeats with the highest rating:

Tell me about the tracks where the publisher is Anjunabeats. I want to know about the tracks with the highest rating. I want the following about the tracks meeting these criteria:

– An overall total of tracks

This is a COUNT(*) like the initial query. Bedrock also adds a subquery to get the Anjunabeats MAX(my_rating) value instead of the dataset MAX(my_rating) value:

SQL

SELECT 
  COUNT(*) AS "Total Tracks" 
FROM 
  public.itunes_data 
WHERE 
  "mtg-publisher" = 'Anjunabeats' 
  AND my_rating = (
    SELECT 
      MAX(my_rating) 
    FROM 
      public.itunes_data 
    WHERE 
      "mtg-publisher" = 'Anjunabeats'
  );

SELECT 
  COUNT(*) AS "Total Tracks" 
FROM 
  public.itunes_data 
WHERE 
  "mtg-publisher" = 'Anjunabeats' 
  AND my_rating = (
    SELECT 
      MAX(my_rating) 
    FROM 
      public.itunes_data 
    WHERE 
      "mtg-publisher" = 'Anjunabeats'
  );

This is sensible. While the MAX(my_rating) value is 100 for both the Anjunabeats record label and the whole dataset, this isn’t true for other labels in the dataset. This version of the query is easier to reuse.

The answer is correct, and I have my baseline value for the other queries – 44 tracks:

You might think 44/539 is a small number. But I don’t hand out top ratings lightly!

Complex Query: Initial Key Ordering

Now let’s try the second part of the prompt that introduces key aggregation:

Tell me about the tracks where the publisher is Anjunabeats. I want to know about the tracks with the highest rating. I want the following about the tracks meeting these criteria:

– Totals for each initial key, ordered by initial key

And, uh…

This response feels like a placeholder. I repeated the same query multiple times, and each time I got the same result. I’ll revisit this later.

Complex Query: Decade Grouping

Finally, let’s try the last part of the initial prompt that introduces decade aggregation:

Tell me about the tracks where the publisher is Anjunabeats. I want to know about the tracks with the highest rating. I want the following about the tracks meeting these criteria:

– Totals for each decade (eg 2000 to 2009, 2010 to 2019 etc)

Bedrock creates a new calculated column for this, using an interesting method to calculate decades that I hadn’t considered! For example, take 2005:

2005 / 10 = 200.5
FLOOR(200.5) = 200
200 * 10 = 2000

Much better than the CASE statement I was expecting:

SQL

SELECT 
  FLOOR("mtg-year" / 10) * 10 AS decade, 
  COUNT(*) AS total_tracks 
FROM 
  public.itunes_data 
WHERE 
  "mtg-publisher" = 'Anjunabeats' 
  AND my_rating = (
    SELECT 
      MAX(my_rating) 
    FROM 
      public.itunes_data 
    WHERE 
      "mtg-publisher" = 'Anjunabeats'
  ) 
GROUP BY 
  FLOOR("mtg-year" / 10) * 10 
ORDER BY 
  decade;

SELECT 
  FLOOR("mtg-year" / 10) * 10 AS decade, 
  COUNT(*) AS total_tracks 
FROM 
  public.itunes_data 
WHERE 
  "mtg-publisher" = 'Anjunabeats' 
  AND my_rating = (
    SELECT 
      MAX(my_rating) 
    FROM 
      public.itunes_data 
    WHERE 
      "mtg-publisher" = 'Anjunabeats'
  ) 
GROUP BY 
  FLOOR("mtg-year" / 10) * 10 
ORDER BY 
  decade;

Bedrock confirms what I already knew – that the 2000s were a great time and I miss them terribly:

Also, as in the first query, these results add up to 44.

Complex Query: Initial Key Ordering Retry

Let’s go back to that failing query from earlier. How about I just ask for the totals and drop the ordering requirement:

Tell me about the tracks where the publisher is Anjunabeats. I want to know about the tracks with the highest rating. I want the following about the tracks meeting these criteria:

– Totals for each key

Bedrock apparently has no issues with this:

SQL

SELECT 
  "mtg-initialkey", 
  COUNT(*) AS "Totals" 
FROM 
  public.itunes_data 
WHERE 
  "mtg-publisher" = 'Anjunabeats' 
  AND "my_rating" = (
    SELECT 
      MAX("my_rating") 
    FROM 
      public.itunes_data 
    WHERE 
      "mtg-publisher" = 'Anjunabeats'
  ) 
GROUP BY 
  "mtg-initialkey";

SELECT 
  "mtg-initialkey", 
  COUNT(*) AS "Totals" 
FROM 
  public.itunes_data 
WHERE 
  "mtg-publisher" = 'Anjunabeats' 
  AND "my_rating" = (
    SELECT 
      MAX("my_rating") 
    FROM 
      public.itunes_data 
    WHERE 
      "mtg-publisher" = 'Anjunabeats'
  ) 
GROUP BY 
  "mtg-initialkey";

But this doesn’t add up to 44! This time I was curious to see what data Bedrock was getting, so I added an ORDER BY clause to the query and ran it in Redshift:

So the SQL is valid, and the data is correct and equals 44. The issue here is that Bedrock is summarising the results. In an attempt to stop this, I asked Bedrock to order the results by totals like I had just done:

Tell me about the tracks where the publisher is Anjunabeats. I want to know about the tracks with the highest rating. I want the following about the tracks meeting these criteria:

– Totals for each key, ordered by totals

Bedrock’s SQL is fine again:

SQL

SELECT 
  "mtg-initialkey", 
  COUNT(*) AS "Totals" 
FROM 
  public.itunes_data 
WHERE 
  "mtg-publisher" = 'Anjunabeats' 
  AND "my_rating" = (
    SELECT 
      MAX("my_rating") 
    FROM 
      public.itunes_data 
    WHERE 
      "mtg-publisher" = 'Anjunabeats'
  ) 
GROUP BY 
  "mtg-initialkey" 
ORDER BY 
  "Totals" DESC;

SELECT 
  "mtg-initialkey", 
  COUNT(*) AS "Totals" 
FROM 
  public.itunes_data 
WHERE 
  "mtg-publisher" = 'Anjunabeats' 
  AND "my_rating" = (
    SELECT 
      MAX("my_rating") 
    FROM 
      public.itunes_data 
    WHERE 
      "mtg-publisher" = 'Anjunabeats'
  ) 
GROUP BY 
  "mtg-initialkey" 
ORDER BY 
  "Totals" DESC;

But this response loses precision after the Gm and Bm keys. Bedrock correctly says 19 keys are in the full list, but is vague beyond this:

In one final attempt to get the kind of response I’m after, I tell Bedrock not to summarise the data:

Tell me about the tracks where the publisher is Anjunabeats. I want to know about the tracks with the highest rating. I want the following about the tracks meeting these criteria:

– Totals for each key, ordered by totals. do not summarise the data

Bedrock runs the same query as last time…

SQL

SELECT 
  "mtg-initialkey", 
  COUNT(*) AS "Totals" 
FROM 
  public.itunes_data 
WHERE 
  "mtg-publisher" = 'Anjunabeats' 
  AND "my_rating" = (
    SELECT 
      MAX("my_rating") 
    FROM 
      public.itunes_data 
    WHERE 
      "mtg-publisher" = 'Anjunabeats'
  ) 
GROUP BY 
  "mtg-initialkey" 
ORDER BY 
  "Totals" DESC;

SELECT 
  "mtg-initialkey", 
  COUNT(*) AS "Totals" 
FROM 
  public.itunes_data 
WHERE 
  "mtg-publisher" = 'Anjunabeats' 
  AND "my_rating" = (
    SELECT 
      MAX("my_rating") 
    FROM 
      public.itunes_data 
    WHERE 
      "mtg-publisher" = 'Anjunabeats'
  ) 
GROUP BY 
  "mtg-initialkey" 
ORDER BY 
  "Totals" DESC;

…and kind of gives me what I want. Bedrock doesn’t give me all of the keys with 2 tracks and gives me none of the keys with 1 track:

I decided that was far enough. This is now crossing into prompt engineering, and the whole point of all this is to test the knowledge base’s ability to use structured data. In that respect, these tests have been successful.

But could I have done things differently?

Future Improvements

I want to end this section by holding my hands up. While Bedrock struggled with some of the tasks I gave it, there were several ways I could have made the process easier for it. As this experience was mainly a proof-of-concept and a learning opportunity, I didn’t implement those measures here.

So instead of concluding with what Bedrock couldn’t achieve, I will outline how I’ll improve future Bedrock implementations as Project Wolfie develops. The following list is not exhaustive – there are definitely unknown unknowns here!

Improved Data Schema: The Parquet file containing my data was created ad hoc, resulting in an unrefined schema with duplicate data and unclear column names. Validation and normalisation processes were also minimal. The Wolfie Data Pipeline will generate a much-improved dataset, which will aid Bedrock’s understanding.

Supportive Descriptions: When creating Bedrock knowledge bases, table and column descriptions, usage notes and additional attributes can be included. These enhance SQL query generation by providing extra context and information about table and column structures. For example, I could have given mtg-publisher a description of ‘record label’ and that might have solved the Volume One issue.

Curated Queries: I can provide predefined examples of questions and answers for the knowledge base, where the questions are natural language queries and the answers are corresponding SQL queries. This gives Bedrock more context for generating SQL. For example, a possible solution to the Anjunabeats confusion could have been:

Plaintext

Q: Tell me about the tracks where the publisher is Anjunabeats.
A: SELECT * FROM public.itunes_data WHERE "mtg-publisher" = 'Anjunabeats'

Q: Tell me about the tracks where the publisher is Anjunabeats.
A: SELECT * FROM public.itunes_data WHERE "mtg-publisher" = 'Anjunabeats'

All things to try out in future versions! Next, let’s talk about costs.

Costs Analysis

This section covers the costs incurred during one week of experimentation in eu-west-1. All figures shown in screenshots are actual charges as reported by AWS. Firstly I’ll examine Bedrock’s costs, followed by Redshift’s.

This account is within my AWS Organisation, and so has access to my Community Builder credits. Some costs will therefore appear as zero until I filter out these credits.

Bedrock Costs

Looking first at Bedrock, my December 2025 bill shows 26 GenerateSQL-StructuredRetrieve requests, totalling USD 0.05:

At the billing level, Bedrock displays a total cost of zero. This indicates how AWS credits are shown, appearing as ‘No region’ credit lines. This information will be relevant shortly!

Now, let’s go to Cost Explorer and adjust the cost granularity. Here are the daily Bedrock expenses for my AWS data sandbox account:

2025 12 30 20 55 36 COstUsageServiceGraph

And this is a table showing the costs filtered by API:

Minor rounding artefacts appear at this scale – I’m assuming the 25 December costs are USD 0.00999 and one of the other days is USD 2.00111 or something similar.

Overall, Bedrock costs were low, visible, and easy to reason about. But something is missing…

Redshift Costs

Despite Redshift being central to the architecture, no Redshift charges appear in the billing console during this period. Not even with the debit/credit method shown above. This raises an important question: how exactly does the free trial work?

Well, it turns out that this is by design:

Amazon Redshift Serverless offers a free trial. If you participate in the free trial, you can view the free trial credit balance in the Redshift console, and check free trial usage in the SYS_SERVERLESS_USAGE system view. Note that billing details for free trial usage does not appear in the billing console. You can only view usage in the billing console after the free trial ends.
Billing for Amazon Redshift Serverless

I am NOT a fan of this! I love Cost Explorer for its visibility; everyone with access can view, slice and aggregate the data, quickly identifying trends and hotspots. Not being able to do this with the Redshift Serverless trial feels like a misstep. Surely part of the trial involves understanding Redshift costs like and alongside other AWS services, in order to avoid a big financial shock when the trial ends! Having them separate isn’t great.

Anyway.

There are ways to monitor the costs of a Redshift Serverless trial, but they’re not as intuitive as Cost Explorer. The most obvious way to see how many credits have been used is through the Free Trial widget on the Redshift Serverless dashboard.

Redshift Serverless is billed using Redshift Processing Units (RPUs). An RPU represents a bundle of compute, memory, and networking capacity, and charges are primarily driven by:

The workgroup’s configured Base Capacity.
The duration this capacity remains active.

The widget shows USD 56.98 used – higher than I had expected! The billing page mentions SYS_SERVERLESS_USAGE so let’s see what that has to offer:

SQL

SELECT
  * 
FROM
  SYS_SERVERLESS_USAGE

SELECT
  * 
FROM
  SYS_SERVERLESS_USAGE

Of particular interest here is the charged_seconds column: the accumulated RPU seconds charged between the start_time and end_time. Handily, the Redshift docs include a query returning aggregated daily costs:

SQL

SELECT
  TRUNC(start_time) "Day",
  (
    sum(charged_seconds)/ 3600 :: DOUBLE PRECISION
  ) * 0.387 as cost_incurred 
FROM
  SYS_SERVERLESS_USAGE
GROUP BY
  1 
ORDER BY
  1

SELECT
  TRUNC(start_time) "Day",
  (
    sum(charged_seconds)/ 3600 :: DOUBLE PRECISION
  ) * 0.387 as cost_incurred 
FROM
  SYS_SERVERLESS_USAGE
GROUP BY
  1 
ORDER BY
  1

That’s… actually more than the widget ($58.62), but I can understand the widget having some lag. So what’s driving these costs?

Redshift Cost Review

Firstly, my workgroup is massively overprovisioned, as the Base Capacity was using the default 128 RPU. Great for performance, but uses a ton of resources and is total overkill for my use case!

Fortunately, Base Capacity can be changed. In Considerations and limitations for Amazon Redshift Serverless capacity, AWS states that configurations with 4 base RPU support managed storage capacity of up to 32 TB. And I have… 2.7GB of total storage used.

I clearly don’t need 128 RPU, so I reduce my Base Capacity to 4 RPUs:

While this change led to longer query execution times, expenses quickly stabilised and stopped rising.

Additionally, the Redshift Serverless usage shown is not limited to the Bedrock processes mentioned in this post. During the same time, I was also trying out different features in the Redshift environment, such as:

Executing queries manually using my IAM user to explore and test various features of Redshift Serverless.
Creating and testing two separate Bedrock knowledge bases (both visible in the Redshift console).
Performing ad-hoc exploratory analysis via the Redshift query editor.

Redshift monitors and reports on this activity in the console:

My exploratory use of Redshift prolonged workgroup activity, consumed additional RPU and increased costs. As I reduced my exploratory use, the Redshift costs began to align more closely with my expectations.

Summary

In this post, I created a structured data knowledge base using Amazon Bedrock and tested it by allowing it to query a database directly.

Setup Thoughts

Firstly, I was impressed with the respective Redshift and Bedrock setup processes. Although there were some challenges, they primarily involved ensuring services communicated effectively and were resolved quickly. And while some default settings were more helpful than others, they at least provided a starting point for making informed configuration decisions.

Bedrock’s costing is transparent, so I could also make informed cost decisions. And while I’m a big fan of the Redshift Serverless free trial, I wish it was trackable within Cost Explorer. In the meantime, I’ll keep a close eye on SYS_SERVERLESS_USAGE!

Testing Thoughts

Testing the knowledge base was straightforward, and I quickly understood how Bedrock interacted with Redshift. Seeing the actual queries being executed not only helps optimise the knowledge base but also informs potential architectural changes to the database.

The results emphasise the importance of data structure and quality. I’ve worked with data for almost a decade now, and have seen many sales pitches claiming to remove the need for SQL, data modelling or normalisation. The reality is that, especially in the current AI era, well-structured and properly modelled data and an understanding of how to query it effectively remain crucial.

As I’ve shown here, without these data controls, an AI attempting to use such data is likely to produce incorrect, inconsistent or inaccurate outputs. While simple queries and explicit prompts can help mitigate these issues, it is more beneficial to start with clean, well-structured data.

A knowledge base that can access clean, normalised data stores lets users focus on interpreting answers rather than rephrasing questions. This will be a key consideration for the architecture of the Project Wolfie data pipelines.

Finally, I’ll leave you with an AWS demo video in which Lester Sim creates his own Bedrock structured data knowledge base:

Like this post? Click the button below for links to contact, socials, projects and sessions:

Thanks for reading ~~^~~

Tags Amazon Bedrock, Amazon Q, Amazon Redshift, Amazon S3, Amazon Web Services, Apache Parquet, AWS Billing And Cost Management, Project Wolfie: Data Lakehouse (2025), Python: Pandas, SQL

Data & Analytics

SQL Workbench: On-Demand DuckDB-Wasm With No Bill

Post author By Damien Jones
Post date June 27, 2024

In this post, I try Tobias Müller‘s free SQL Workbench tool powered by DuckDB-Wasm. And maybe make one or two gratuitous duck puns.

Introduction

Hopefully like many people in tech, I have a collection of articles, repos, projects and whatnot saved under the banner of “That sounds cool / looks interesting / feels useful – I should check that out at some point.” Sometimes things even come off that list…

DuckDB was on my list, and went to the top when I heard about Tobias Müller’s free SQL Workbench tool powered by DuckDB-Wasm. What I heard was very impressive and pushed me to finally examine DuckDB up close.

So what is DuckDB?

About DuckDB

This section examines DuckDB and DuckDB-Wasm – core components of SQL Workbench.

DuckDB

DuckDB is an open-source SQL Online Analytical Processing (OLAP) database management system. It is intended for analytical workloads, and common use cases include in-process analytics, exploration of large datasets and machine learning model prototyping. DuckDB was released in 2019 and reached v1.0.0 on June 03 2024.

DuckDB is an embedded database that runs within a host process. It is lightweight, portable and requires no separate server. It can be embedded into applications, scripts, and notebooks without bespoke infrastructure.

DuckDB uses a columnar storage format and supports standard SQL features like complex queries, joins, aggregations and window functions. It can be used with programming languages like Python and R.

In this Python example, DuckDB:

Creates an in-memory database
Defines a users table.
Inserts data into users.
Runs a query to fetch results.

Python

import duckdb

# Create a new DuckDB database in memory
con = duckdb.connect(database=':memory:')

# Create a table and insert some data
con.execute("""
CREATE TABLE users (
    user_id INTEGER,
    user_name VARCHAR,
    age INTEGER
);
""")

con.execute("INSERT INTO users VALUES (1, 'Alice', 30), (2, 'Bob', 25), (3, 'Charlie', 35)")

# Run a query
results = con.execute("SELECT * FROM users WHERE age > 25").fetchall()
print(results)

DuckDB-Wasm

Launched in 2021, DuckDB WebAssembly (Wasm) is a version of the DuckDB database that has been compiled to run in WebAssembly. This lets DuckDB run in web browser processes and other environments with WebAssembly support.

So what’s WebAssembly? Don’t worry – I didn’t know either and so deferred to an expert:

Since all data processing happens in the browser, DuckDB-Wasm brings new benefits to DuckDB. In-browser dashboards and data analysis tools with DuckDB-Wasm enabled can operate without any server-side processing, improving their speed and security. If data is being sent somewhere then DuckDB-Wasm can handle ETL operations first, reducing both processing time and cost.

Running in-browser SQL queries also removes the need for setting up database servers and IDEs, reducing patching and maintenance (but don’t forget about the browser!) while increasing availability and convenience for remote work and educational purposes.

Like DuckDB, it is open-source and the code is on GitHub. A DuckDB Web Shell is available at shell.duckdb.org and there are also more technical details on DuckDB’s blog.

SQL Workbench

This section examines SQL Workbench and tries out some of its core features.

About SQL Workbench

Tobias Müller produced SQL Workbench in January 2024. He integrated DuckDB-Wasm into an AWS serverless static site and built an online interactive tool for querying data, showing results and generating visualizations.

SQL Workbench is available at sql-workbench.com. Note that it doesn’t currently support mobile browsers. Tobias has also written a great tutorial post that includes:

and loads more content in addition that I won’t reproduce here. So go and give Tobias some traffic!

Layout

The core SQL Workbench components are:

The Object Explorer section shows databases, schema and tables:

The Tools section shows links, settings and a drag-and-drop section for adding files (more on that later):

The Query Pane for writing SQL, which preloads with the below script upon each browser refresh:

And finally, the Results Pane shows query results and visuals:

Next, let’s try it out!

Sample Queries

SQL Workbench opens with a pre-loaded script that features:

Instructions:

SQL

-- WELCOME TO THE ONLINE SQL WORKBENCH!
-- To run a SQL query in your browser, select the query text and press:
-- CTRL + Enter (Windows/Linux) / CMD + Enter (Mac OS)

-- See https://duckdb.org/docs/sql/introduction for more info about DuckDB SQL syntax

Example queries using Parquet files:

SQL

-- Remote Parquet scans:
SELECT * FROM 'https://shell.duckdb.org/data/tpch/0_01/parquet/orders.parquet' LIMIT 1000;

SELECT avg(c_acctbal) FROM 'https://shell.duckdb.org/data/tpch/0_01/parquet/customer.parquet';

SELECT count(*)::int as aws_service_cnt FROM 'https://raw.githubusercontent.com/tobilg/aws-iam-data/main/data/parquet/aws_services.parquet';

SELECT * FROM 'https://raw.githubusercontent.com/tobilg/aws-edge-locations/main/data/aws-edge-locations.parquet';

SELECT cloud_provider, sum(ip_address_cnt)::int as cnt FROM 'https://raw.githubusercontent.com/tobilg/public-cloud-provider-ip-ranges/main/data/providers/all.parquet' GROUP BY cloud_provider;

SELECT * FROM 'https://raw.githubusercontent.com/tripl-ai/tpch/main/parquet/lineitem/part-0.parquet';

Example query using a CSV file:

SQL

-- Remote CSV scan
SELECT * FROM read_csv_auto('https://raw.githubusercontent.com/tobilg/public-cloud-provider-ip-ranges/main/data/providers/all.csv');

These queries are designed to show SQL Workbench’s capabilities and the style of SQL queries it can run (expanded on further here). They can also be used for experimentation, so here goes!

Firstly, I’ve added an ORDER BY to the first Parquet query to make sure the first 1000 orders are returned:

SQL

-- Show first 1000 orders for Parquet file
SELECT * 
FROM 'https://shell.duckdb.org/data/tpch/0_01/parquet/orders.parquet'
ORDER BY o_orderkey
LIMIT 1000;

Secondly, I’ve used the COUNT and CAST functions to count the total orders and return the value as an integer:

SQL

-- Show number of orders for Parquet file
SELECT CAST(COUNT(o_orderkey) AS INT) AS orderkeycount 
FROM 'https://shell.duckdb.org/data/tpch/0_01/parquet/orders.parquet';

Finally, this query captures all 1996 orders in a CTE and uses it to calculate the 1996 order price totals grouped by order priority:

SQL

-- Show 1996 order totals in priority order
WITH cte_orders1996 AS (
    SELECT o_orderkey, o_custkey, o_totalprice, o_orderdate, o_orderpriority
    FROM 'https://shell.duckdb.org/data/tpch/0_01/parquet/orders.parquet'
    WHERE YEAR(o_orderdate) = 1996
)

SELECT SUM(o_totalprice) AS totalpriorityprice, o_orderpriority 
FROM cte_orders1996 
GROUP BY o_orderpriority 
ORDER BY o_orderpriority;

Now let’s try some of my data!

File Imports

SQL Workbench can also query data stored locally (as well as remote data which is out of this post’s scope). For this section I’ll be using some Parquet files generated by my WordPress bronze data orchestration process: posts.parquet and statistics_pages.parquet.

After dragging each file onto the assigned section they quickly appear as new tables:

These tables show column names and inferred data types when expanded:

2024 06 26 SQLWorkbenchSchemaWordPressExpanded

Queries can be written manually or by right-clicking the tables in the Object Explorer. A statistics_pages SELECT * test query shows the expected results:

As posts.parquet‘s ID primary key column matches statistics_pages.parquet‘s ID foreign key column, the two files can be joined on this column using an INNER JOIN:

SQL

SELECT 
  p.post_title,
  sp.type, 
  sp.date as viewdate,
  sp.count
FROM 
  'statistics_pages.parquet' AS sp
  INNER JOIN 'posts.parquet' AS p ON sp.id = p.id
 ORDER BY sp.date

I can use this query to start making visuals!

Visuals

Next, let’s examine SQL Workbench’s visualisation capabilities. To help things along, I’ve written a new query showing the cumulative view count for my Using Athena To Query S3 Inventory Parquet Objects post (ID 92):

SQL

 SELECT 
  sp.date AS viewdate,
  SUM(sp.count) OVER (PARTITION BY sp.id ORDER BY sp.date) AS cumulative_views
FROM 
  'statistics_pages.parquet' AS sp
  INNER JOIN 'posts.parquet' AS p ON sp.id = p.id
WHERE p.id = 92
ORDER BY sp.date;

(On reflection the join wasn’t necessary for this output, but anyway… – Ed)

Tobias has written visualisation instructions here so I’ll focus more on how my visual turns out. After the query finishes running, several visual types can be selected:

2024 06 26 SQLWorkbenchVisualizationsOptions

Selecting a visual quickly renders it in the browser. Customisation options are also available. That said, in my case the first chart was mostly fine!

2024 06 26 SQLWorkbenchVisualizationsLineChart

All I need to do is move viewdata (bottom right) to the Group By bin (top right) to add dates to the X-axis.

Exports & Cleanup

Finally, let’s examine SQL Workbench’s export options. And there’s plenty to choose from!

The CSV and JSON options export the query results in their respective formats. The Arrow option exports query results in Apache Arrow format, which is designed for high-speed in-memory data processing and so compliments DuckDB very well. Dremio wrote a great Arrow technical guide for those curious.

The HTML option produces an interactive graph with tooltips and configuration options. Handy for sharing and I’m sure there’ll be a way to use the backend code for embedding if I take a closer look. The PNG has me covered in the meantime, immediately producing this image:

athenainventorycumulativeCropped — *(Image cropped for visibility – Ed)*

Finally there’s the Config.JSON option, which exports the visual’s settings for version control and reproducibility:

JSON

{"version":"2.10.0","plugin":"Y Line","plugin_config":{},"columns_config":{},"settings":true,"theme":"Pro Light","title":"Using Athena To Query S3 Inventory Parquet Objects Cumulative Views","group_by":["viewdate"],"split_by":[],"columns":["cumulative_views"],"filter":[],"sort":[],"expressions":{},"aggregates":{}}

All done! So how do I remove my data from SQL Workbench? I…refresh the site! Because SQL Workbench uses DuckDB, my data has only ever been stored locally and in memory. This means the data only persists until the SQL Workbench page is closed or reloaded.

Summary

In this post, I tried Tobias Müller’s free SQL Workbench tool powered by DuckDB-Wasm.

SQL Workbench and DuckDB are both very impressive! The results they produce with no servers or data movement are remarkable, and their tooling greatly simplifies working with modern data formats.

DuckDB makes lots of sense as an AWS Lambda layer, and DuckDB-Wasm simplifies in-browser analytics at a time when being data-driven and latency-averse are constantly gaining importance. I don’t feel like I’ve even scratched the surface of SQL Workbench either, so go and check it out!

Phew – no duck puns. No one has time for that quack. If this post has been useful then the button below has links for contact, socials, projects and sessions:

Thanks for reading ~~^~~

Tags Apache Parquet, DuckDB, GitHub, Open Source, SQL, WebAssembly, WordPress amazonwebshark MySQL Database

Data & Analytics

WordPress MySQL Database Tables Deep Dive

Post author By Damien Jones
Post date May 6, 2023

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

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:

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 Name	Description
page_id	Primary key. Unique identifier for the table.
uri	Uniform Resource Identifier used to access a page.
type	uri type: `home` / `page` / `post`
date	Date the uri was viewed
count	uri total views on the specified date
id	uri 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 ~~^~~

Tags DBeaver, MySQL, Open Source, SQL, WordPress, WordPress amazonwebshark MySQL Database