Use OCI GenAI, and MySQL HeatWave to interact with your WordPress content

MySQL HeatWave 9.0 was released under the banner of artificial intelligence. It includes a VECTOR datatype and can easily process and analyze vast amounts of proprietary unstructured documents in object storage, using HeatWave GenAI and Lakehouse.

Oracle Cloud Infrastructure also provides a wonderful GenAI Service, and in this post, we will see how to use OCI GenAI with MySQL HeatWave to build a custom RAG solution using the content of a WordPress site.

This article was written with the help of my colleague Ivan Ma and based on the original blog of Ashul Dupare.

The Plan

The plan is to generate embeddings from the content stored in WordPress. For this example, I will use all the content of my blog: https://lefred.be.

The content of the articles is stored in the table wp_posts and in the field post_content (a LONGTEXT).

We will use Python, and we will retrieve the content of each article, and clean them up, meaning removing HTML tags, and special characters, … Then generate embedding using OCI GenAI’s service and store the vectors in MySQL HeatWave DB System.

This process will take some time… then we will be able to generate embedding for any question and compare it with the content we have in our database and generate an answer, this will be cool!

Prerequisites

Of course, we need to have a WordPress database, an OCI account to access the GenAI service, and a MySQL HeatWave DB System. Currently, Chicago is the region providing most of the GenAI services. It’s not needed to have your DB System in the same region.

I use a compute instance, but this is not mandatory for your Python code can reach our MySQL HeatWave DB System using a bastion host, a VPN, or the load balancer service for example.

Verify that your DB System is using at least MySQL 9.0.0:

Getting Started

We need to create a table in the WordPress schema to store our embeddings:

create table wp_embeddings(
   id bigint unsigned auto_increment,
   content varchar(4000), 
   vec vector(1024), 
   wp_post_id bigint unsigned, 
   primary key(id)
);

The code is available on GitHub: here.

Then, you also need to edit the wp_config.py file with your credentials, the endpoint of the GenAI compartment ID:

COMPARTMENT = "<ENTER HERE THE GENAI COMPARTMENT ID>"
CONFIG_FILE = "~/.oci/config"
CONFIG_PROFILE = "DEFAULT"

# Service endpoint
# ENDPOINT = "https://inference.generativeai.eu-frankfurt-1.oci.oraclecloud.com"
ENDPOINT = "https://inference.generativeai.us-chicago-1.oci.oraclecloud.com"

DB_USER = "<YOUR DB USER ACCOUNT>"
DB_PASSWORD = "<YOUR DB USER PASSWORD>"
DB_HOST = "<YOUR DB HOST IP>"
DB_PORT = 3306
DB_SCHEMA = "<WORDPRESS SCHEMA>"

# Not mandatory, only if you plan to use reranking
COHERE_API_KEY = "<COHERE_API_KEY>"

To find the COMPARTMENT ID, you need to connect to the Oracle Cloud console, and choose a region where you see the GenAI Service (US-Midwest, Chicago is recommended):

Start a random chat:

And view the code, select Python:

And cut the comparment_id value to use in your wp_config.py.

Embeddings

You are now ready to start the embedding of all the content of your WordPress and store everything in MySQL HeatWave using STRING_TO_VECTOR() function:

This process will populate the wp_embeddings table:

select count(*) from wp_embeddings;
+----------+
| count(*) |
+----------+
|    19726 |
+----------+
1 row in set (0.0119 sec)

Let’s have a look at one record in this table:

select id, content, wp_post_id, VECTOR_TO_STRING(vec) 
from wp_embeddings order by id desc limit 1\G
*************************** 1. row ***************************
                   id: 19726
              content: P ;-) Enjoy coding in PHP and storing your data in MySQL!
           wp_post_id: 7538
VECTOR_TO_STRING(vec):[1.47247e-02,1.53656e-02,-7.51953e-02,-6.26221e-02,...]

RAG

Now that all articles have been parsed and the vector stored in the database, let’s try it!

In the illustration above, we use the vector search but we don’t rerank the results. To be able to rerank to have a better result, it’s then mandatory to get an API Key for Cohere (you can get a free one). I didn’t find that feature in the OCI GenAI capabilities yet.

See below, the same question but this time using “reranking”:

It’s recommended to use rerank to avoid some strange results on more complex queries.

This is another overview of how it works:

Conclusion

Thanks to the OCI GenAI services and MySQL HeatWave’s vector capabilities, it’s now so easy to “ask” anything related to the data stored in your MySQL.

It doesn’t require too much coding skills and the result is stunning!

Welcome to a new world of GenAI, LLMS, RAG for your data, where cutting-edge technologies transform the way we interact with information… and with your own already stored in MySQL information!

Do not hesitate to dive into this revolutionary landscape and discover endless possibilities for innovation and efficiency.