Join the Oracle Dev Days – French Edition, from May 20 to 22, 2025! This must-attend event (in French) offers a rich program exploring the latest advancements in AI, databases, cloud, and Java.
Join me on May 21 at 2:00 PM for the day dedicated to “Database & AI.” I’ll be presenting “Building an AI-Powered Search Engine with HeatWave GenAI.” I’ll show you how to go beyond the limits of traditional SQL to harness the power of LLM-driven semantic search.
This approach significantly enhances the relevance of search results by understanding context, interpreting user intent, and handling synonyms.
During this session, we’ll cover:
The technology stack used: SQL, Python, and JavaScript-based stored procedures
The architecture of a complete RAG (retrieval-augmented generation) pipeline, including data extraction, vectorization, storage, and querying within the database
The process of building a chatbot for natural language interaction with the AI
Discover how to implement a powerful, AI-enhanced semantic search engine directly within Oracle HeatWave GenAI.
In Build an AI-Powered Search Engine with HeatWave GenAI (part 1), we introduced the fundamentals of creating an AI-powered search engine using HeatWave GenAI. We highlighted the advantages of semantic search powered by large language models over traditional SQL-based approaches and provided a hands-on guide for generating embeddings and running similarity searches — key techniques that significantly improve the retrieval of relevant content.
In the second opus — Build an AI-Powered Search Engine with HeatWave GenAI (part 2) — we shifted our focus to improving search result quality through reranking strategies and the use of article summaries for embedding generation. We demonstrated how to implement these enhancements entirely within HeatWave using JavaScript-based stored procedures. By assigning different weights to title and excerpt distances, and generating embeddings from sanitized summaries, we boosted the precision and relevance of search results. This approach showcases HeatWave GenAI’s ability to embed advanced AI capabilities directly within the database layer.
In this third installment, we’ll take it a step further by incorporating full article content into the search engine. While titles, excerpts, or even summaries may work well in many cases, there are situations where deeper, more detailed information is needed to return truly relevant answers.
What are we going to do?
The process is slightly more complex than what we’ve covered so far (in part 1 & part 2). In WordPress, article content is stored in HTML format within the post_content column. This will be our starting point, and the goal is to generate embeddings.
To achieve this, we’ll need to write a few lines of code. While this could be done directly within HeatWave using JavaScript stored procedures — as we saw in part 2: A Javascript, stored procedure & AI story — I’ll instead use the unofficial language of data: Python. Please bear in mind that I’m not a developer, so this code is provided for illustrative purposes only. It may contain errors or limitations. Please use it at your own risk and adapt it to your specific needs (also feel free to share back).
Below are the steps we’ll follow to move forward:
Defining the embeddings storage table
Fetch articles from the database.
Remove HTML tags and normalize whitespace.
Split articles into overlapping chunks of words.
Generate embeddings for a given article.
Insert article chunks with their embeddings into HeatWave.
I created a new table named wp_post_chunks_embeddings_minilm to store the embeddings generated from article chunks.
chunk_embedding: embedding of the articles
chunk_text: contains the part of the articles. Mostly useful for debug
and some IDs,
chunk_id: id of each chunk
post_id: useful to join the wp_posts table and get article information
and finally, the table primary key: id
From wp_posts table, I’ll use the ID (the primary key) and post_content which contains the article in HTML format. All these information will be used by the semantic AI-powered search engine.
First, we need to grab a batch of published blog articles from HeatWave MySQL. Think of it as scooping out a small chunk of posts at a time from a big pool of content. Pagination is used in order to manage the memory consumption.
def fetch_articles_batch(db_config: Dict[str, Any], limit: int = 10, offset: int = 0) -> List[Dict[str, Any]]:
"""Fetch a batch of articles from the database"""
with mysql.connector.connect(**db_config) as conn:
with conn.cursor(dictionary=True) as cursor:
cursor.execute("""
SELECT ID, post_content FROM wp_posts
WHERE post_status = 'publish' AND post_type = 'post'
LIMIT %s OFFSET %s
""", (limit, offset))
return cursor.fetchall()
Remove HTML tags and normalize whitespace.
Articles are stored in HTML format in the database. So the content must be cleaned because we need the raw text ie without all the formatting tags. With this function we:
Remove all the HTML tags
Replace them with spaces (so you don’t accidentally smash words together)
Collapse extra whitespace
Trimming off any leading or trailing spaces
def strip_html_tags_with_space(html_string: str) -> str:
"""Remove HTML tags and normalize whitespace"""
if not html_string:
return ""
text = re.sub(r'<[^>]+>', ' ', html_string)
text = re.sub(r'\s+', ' ', text)
return text.strip()
Please also note that, depending of the context, extra processing can be done like Stemming or Lemmatization.
Split articles into overlapping chunks of words
Each article is a big block of text and it must be split it into smaller overlapping chunks. It is like slicing a loaf of bread, where each slice overlaps a bit with the one before it.
Chunking in RAG systems presents several challenges, including issues with chunk size (too small or too large), semantic coherence, and context understanding. These challenges can negatively impact retrieval accuracy, efficiency, and precision. Addressing them requires a carefully balanced approach to optimize chunking strategies while preserving computational efficiency and maintaining nuanced contextual understanding.
chunk_size: Defines the number of words per chunk. In this case, I’ve chosen 400 words. The ideal value can vary depending on your specific use case and content structure.
overlap: Specifies how much each chunk overlaps with the next one—here, it’s set to 20% of the chunk size. This overlap helps preserve context across chunks, improving continuity and retrieval accuracy.
Each chunk contains the id of the article (post_id), the id of the chunk (chunk_id) and the chunk itself (test). char_start & char_end are only there for debug purpose.
Generate embeddings for a given article
Now it is time to generate an embedding (aka a numerical vector) for each chunk. Think of an embedding as a way to turn text into numbers so that machines can understand and compare it.
We are using the HeatWave’s ML_EMBED_ROW stored function to generate embeddings:
def embed_row(db_config: Dict[str, Any], search_item: str, embed_options: str = '{"model_id": "minilm"}') -> str:
"""Generate an embedding for a given text using the ML_EMBED_ROW function"""
with mysql.connector.connect(**db_config) as conn:
with conn.cursor() as cursor:
sql_embed_row = 'SELECT VECTOR_TO_STRING(vect) FROM (SELECT sys.ML_EMBED_ROW(%s, %s) AS vect) AS dt;'
cursor.execute(sql_embed_row, (search_item, embed_options))
result = cursor.fetchone()
return "".join(result) if result else ""
The trick here in Python is obtaining the string representation of the vector, which we can do using the VECTOR_TO_STRING function.
embed_options: specifies the settings for the embedding model. Here we are using "minilm".
Insert article chunks with their embeddings into HeatWave
Now it is time to store the text chunks and their corresponding embeddings in the vector store — HeatWave — specifically into the wp_post_chunks_embeddings_minilm table designed for this purpose:
def insert_chunks_into_mysql(chunks: List[Dict[str, Any]], db_config: Dict[str, Any], batch_size: int = 900):
"""Insert text chunks with embeddings into the database"""
with mysql.connector.connect(**db_config) as conn:
with conn.cursor() as cursor:
insert_query = """
INSERT INTO wp_post_chunks_embeddings_minilm (post_id, chunk_id, chunk_text, chunk_embedding)
VALUES (%s, %s, %s, STRING_TO_VECTOR(%s))
"""
buffer = []
for i, chunk in enumerate(chunks, 1):
chunk_embedding = embed_row(db_config, chunk['text'])
buffer.append((
chunk['post_id'],
chunk['chunk_id'],
chunk['text'],
chunk_embedding
))
if i % batch_size == 0:
cursor.executemany(insert_query, buffer)
conn.commit()
buffer.clear()
if buffer:
cursor.executemany(insert_query, buffer)
conn.commit()
print(f"Inserted {len(chunks)} chunks into database.")
batch_size: defines how many chunks are inserted into the database at once. In this case, once the buffer reaches 900 chunks, the data is flushed in bulk — a much more efficient approach than inserting each chunk individually, both in terms of performance and memory usage.
A key step here is converting the embeddings into HeatWave’s native vector type before insertion. This is done using the STRING_TO_VECTOR function.
Query Encoding and Vector Similarity Operations
Now it’s time to transform the user’s search query into a vector embedding, a numerical representation that captures its underlying meaning. This process, known as query encoding, allows us to go beyond simple keyword matching. Once the query is encoded as a vector, we can then perform a similarity search by comparing it against the precomputed vector embeddings of our documents within HeatWave. This comparison, often using metrics like cosine similarity, identifies the documents whose vector representations are most closely aligned with the query vector, thus retrieving the most semantically relevant results.
Using it is straightforward. Let’s define two variables: @searchItem (the text to encode) and @embeddOptions (the embedding model used for encoding):
SET @embeddOptions = '{"model_id": "minilm"}';
SET @searchItem = "Generative artificial intelligence";
-- Encode the query using the embedding model
SELECT sys.ML_EMBED_ROW(@searchItem, @embeddOptions) into @searchItemEmbedding;
Similarity search
To retrieve relevant blog content, we perform vector similarity calculations using the DISTANCE function. This function computes the distance between two vectors using COSINE, DOT, or EUCLIDEAN distance metrics. Here, the two vectors being compared are the encoded query (@searchItemEmbedding) and the precomputed embeddings stored in the wp_posts & wp_post_chunks_embeddings_minilm tables.
You can now perform a cosine similarity search on the full articles using the following approach:
SQL >
-- Find the most semantically relevant WordPress posts
SELECT
post_title,
post_excerpt,
guid,
min_distance
FROM (
SELECT
p.post_title,
p.post_excerpt,
p.guid,
DISTANCE(@searchItemEmbedding, e.chunk_embedding, 'COSINE') AS min_distance,
ROW_NUMBER() OVER (PARTITION BY p.post_title ORDER BY DISTANCE(@searchItemEmbedding, e.chunk_embedding, 'COSINE')) AS rn
FROM wp_posts p
INNER JOIN wp_post_chunks_embeddings_minilm e ON p.ID = e.post_id
) AS ranked
WHERE rn = 1
ORDER BY min_distance ASC
LIMIT 5\G
*************************** 1. row ***************************
post_title: HeatWave GenAI: Sentiment Analysis Made Easy-Peasy
post_excerpt: This new AI tech, called generative AI (or GenAI), can dive deep into what people are saying and tell us if they’re feeling positive, negative, or neutral.
Let’s see how HeatWave GenAI, can help you to enhance your understanding of customer sentiment, improve decision-making, and drive business success.
guid: https://dasini.net/blog/?p=3456
min_distance: 0.4673360586166382
*************************** 2. row ***************************
post_title: HeatWave GenAI: Your AI-Powered Content Creation Partner
post_excerpt: Generative artificial intelligence (GenAI) is reshaping the content creation landscape. By training on vast datasets, these "intelligent" systems can produce new, human-quality content across a multitude of domains.
Oracle's HeatWave GenAI (starting with version 9.0.1) is at the forefront of this revolution, offering an integrated platform that combines in-database large language models (LLMs), vector stores, and scale-out vector processing to streamline content generation.
This article explores how HeatWave GenAI is empowering businesses to produce high-quality content rapidly and effectively, making it an indispensable tool for industries demanding speed, accuracy, and security.
guid: https://dasini.net/blog/?p=1234
min_distance: 0.4789550304412842
*************************** 3. row ***************************
post_title: Simplifying AI Development: A Practical Guide to HeatWave GenAI’s RAG & Vector Store Features
post_excerpt: This tutorial explores HeatWave GenAI, a cloud service that simplifies interacting with unstructured data using natural language. It combines large language models, vector stores, and SQL queries to enable tasks like content generation, chatbot, and retrieval-augmented generation (RAG). The focus is on RAG and how HeatWave GenAI’s architecture helps users gain insights from their data.
guid: https://dasini.net/blog/?p=2345
min_distance: 0.5191197395324707
*************************** 4. row ***************************
post_title: Building an Interactive LLM Chatbot with HeatWave Using Python
post_excerpt: AI-powered applications require robust and scalable database solutions to manage and process large amounts of data efficiently. HeatWave is an excellent choice for such applications, providing high-performance OLTP, analytics, machine learning and generative artificial intelligence capabilities.
In this article, we will explore a Python 3 script that connects to an HeatWave instance and enables users to interact with different large language models (LLMs) dynamically.
guid: https://dasini.net/blog/?p=5678
min_distance: 0.6004981994628906
*************************** 5. row ***************************
post_title: In-Database LLMs for Efficient Text Translation with HeatWave GenAI
post_excerpt: While HeatWave GenAI excels at generating English text, its capabilities extend to translation as well. Let's explore how we can effectively translate English content into French using this powerful tool.
guid: https://dasini.net/blog/?p=9876
min_distance: 0.6385803818702698
This SQL query is designed to retrieve the top 5 WordPress posts that are most semantically similar to a given embedding vector (@searchItemEmbedding), using cosine distance for comparison — but with a twist: it only considers the single best-matching chunk per post.
Of course, you can also apply the techniques from part 1 and part 2 to further improve the results. Methods like reranking or incorporating additional fields—such as the article title—while fine-tuning the weights can significantly enhance result relevance.
Peroration
In this third and final installment of our series on building an AI-powered search engine with HeatWave GenAI, we’ve successfully expanded our capabilities to incorporate the full content of articles into the semantic search process.
By implementing a more intricate pipeline involving fetching, cleaning, chunking, embedding, and storing article text, we’ve aimed for a deeper level of understanding and more relevant search results. This evolution, building upon the foundations laid in Parts 1 and 2 with basic embedding and similarity searches, and refined with reranking and summary utilization, now leverages the comprehensive information within each article.
The demonstrated SQL queries and Python code illustrate how HeatWave GenAI can efficiently manage and query vector embeddings derived from large text datasets, paving the way for a highly sophisticated and context-aware search experience.
While further optimizations are always possible, this three-part journey showcases the powerful potential of HeatWave GenAI for creating intelligent, in-database search solutions.
In Build an AI-Powered Search Engine with HeatWave GenAI (part 1), we explored how to build an AI-powered search engine using HeatWave GenAI. We highlighted the advantages of AI-driven semantic search over traditional SQL-based methods and provided a detailed guide on generating embeddings and conducting similarity searches. These techniques enhance the retrieval of relevant articles, improving the user’s ability to find information efficiently.
In this second part, we will explore how to enhance the relevance of our answers using reranking techniques. Next, we will further refine our results by instructing the model to generate embeddings based on article summaries. All these steps will be performed within HeatWave, leveraging its capability to writeStored Programs in JavaScript.
if you remember, in example 3.1, we saw how to run a similarity search on title & excerpt (post_title_embedding & post_excerpt_embedding), using an elegant CTE:
SQL >
-- Ex 3.1 Similarity search on title & excerpt (post_title_embedding & post_excerpt_embedding)
WITH distances AS (
SELECT
ID,
post_title,
post_excerpt,
(
DISTANCE(post_title_embedding, @searchItemEmbedding, 'COSINE') +
DISTANCE(post_excerpt_embedding, @searchItemEmbedding, 'COSINE')
) / 2 AS avg_distance
FROM WP_embeddings.wp_posts_embeddings_minilm
)
SELECT *
FROM distances
ORDER BY avg_distance
LIMIT 5\G
*************************** 1. row ***************************
ID: 1234
post_title: HeatWave GenAI: Your AI-Powered Content Creation Partner
post_excerpt: Generative artificial intelligence (GenAI) is reshaping the content creation landscape. By training on vast datasets, these "intelligent" systems can produce new, human-quality content across a multitude of domains.
Oracle's HeatWave GenAI (starting with version 9.0.1) is at the forefront of this revolution, offering an integrated platform that combines in-database large language models (LLMs), vector stores, and scale-out vector processing to streamline content generation.
This article explores how HeatWave GenAI is empowering businesses to produce high-quality content rapidly and effectively, making it an indispensable tool for industries demanding speed, accuracy, and security.
avg_distance: 0.5131600499153137
...
This result can be further improved using reranking techniques. Reranking involves reordering or refining an initial set of retrieved documents to enhance their relevance to a user’s query. This step is essential for optimizing search quality, often leading to a significant boost in the relevance of the retrieved information.
Reranking
Here we setting up the same context (ie embedding model, natural language query encoding and vector similarity operations) than in part 1:
SQL >
-- Set variables
SET @embeddOptions = '{"model_id": "minilm"}';
SET @searchItem = "Generative artificial intelligence";
-- Encode the query using the embedding model
SELECT sys.ML_EMBED_ROW(@searchItem, @embeddOptions) into @searchItemEmbedding;
The retrieved results is now sorted using weights on title and excerpt distances:
-- Ex 3.2 Similarity search on title & excerpt (post_title_embedding & post_excerpt_embedding) with weights
WITH initial_results AS (
SELECT
ID,
post_title,
post_excerpt,
DISTANCE(post_title_embedding, @searchItemEmbedding, 'COSINE') AS title_distance,
DISTANCE(post_excerpt_embedding, @searchItemEmbedding, 'COSINE') AS excerpt_distance,
guid
FROM WP_embeddings.wp_posts_embeddings_minilm
ORDER BY title_distance + excerpt_distance -- Simple combination
LIMIT 15 -- Retrieve a larger initial set
),
reranked_results AS (
SELECT
ID,
post_title,
post_excerpt,
(0.3 * title_distance + 0.7 * excerpt_distance) AS combined_distance, -- Weighted combination
guid
FROM initial_results
)
SELECT post_title, post_excerpt, combined_distance, guid
FROM reranked_results
ORDER BY combined_distance ASC
LIMIT 5\G
*************************** 1. row ***************************
post_title: HeatWave GenAI: Sentiment Analysis Made Easy-Peasy
post_excerpt: This new AI tech, called generative AI (or GenAI), can dive deep into what people are saying and tell us if they’re feeling positive, negative, or neutral.
Let’s see how HeatWave GenAI, can help you to enhance your understanding of customer sentiment, improve decision-making, and drive business success.
combined_distance: 0.49683985114097595
guid: https://dasini.net/blog/?p=3456
*************************** 2. row ***************************
post_title: HeatWave GenAI: Your AI-Powered Content Creation Partner
post_excerpt: Generative artificial intelligence (GenAI) is reshaping the content creation landscape. By training on vast datasets, these "intelligent" systems can produce new, human-quality content across a multitude of domains.
Oracle's HeatWave GenAI (starting with version 9.0.1) is at the forefront of this revolution, offering an integrated platform that combines in-database large language models (LLMs), vector stores, and scale-out vector processing to streamline content generation.
This article explores how HeatWave GenAI is empowering businesses to produce high-quality content rapidly and effectively, making it an indispensable tool for industries demanding speed, accuracy, and security.
combined_distance: 0.4994780898094177
guid: https://dasini.net/blog/?p=1234
*************************** 3. row ***************************
post_title: Simplifying AI Development: A Practical Guide to HeatWave GenAI’s RAG & Vector Store Features
post_excerpt: This tutorial explores HeatWave GenAI, a cloud service that simplifies interacting with unstructured data using natural language. It combines large language models, vector stores, and SQL queries to enable tasks like content generation, chatbot, and retrieval-augmented generation (RAG). The focus is on RAG and how HeatWave GenAI’s architecture helps users gain insights from their data.
combined_distance: 0.6582363367080688
guid: https://dasini.net/blog/?p=2345
*************************** 4. row ***************************
post_title: Webinar - Apprentissage automatique avec MySQL HeatWave
post_excerpt: HeatWave Machine Learning (ML) inclut tout ce dont les utilisateurs ont besoin pour créer, former, déployer et expliquer des modèles d’apprentissage automatique dans MySQL HeatWave, sans coût supplémentaire.
Dans ce webinaire vous apprendrez...
combined_distance: 0.694593733549118
guid: https://dasini.net/blog/?p=6789
*************************** 5. row ***************************
post_title: Building an Interactive LLM Chatbot with HeatWave Using Python
post_excerpt: AI-powered applications require robust and scalable database solutions to manage and process large amounts of data efficiently. HeatWave is an excellent choice for such applications, providing high-performance OLTP, analytics, machine learning and generative artificial intelligence capabilities.
In this article, we will explore a Python 3 script that connects to an HeatWave instance and enables users to interact with different large language models (LLMs) dynamically.
combined_distance: 0.7135995388031006
guid: https://dasini.net/blog/?p=5678
In the example above, the excerpt is given more than twice the weight of the title (0.7 vs 0.3), based on the assumption that the excerpt holds more relevant information in this context. Depending on your use case, you may want to fine-tune these weights, as adjusting them can significantly improve the quality of search results.
Now, let’s explore how we can further refine our results by leveraging article summaries along with JavaScript-based stored procedures and functions.
A Javascript, stored procedure & AI story
When searching through articles, relying solely on the title and excerpt may not yield the most relevant results. What if we used a summary instead? This approach can strike an excellent balance between relevance and implementation simplicity. With HeatWave GenAI, the entire workflow can be handled directly within the database using SQL and JavaScript-based stored procedures.
In WordPress, article content is stored as HTML in the post_content column. To make it suitable for processing by a large language model (LLM), this content must first be sanitized — that is, all HTML tags need to be removed, as they are not meaningful to the LLM.
Table preparation
As a preparation step, I added a new column (post_content_text longtext NOT NULL) to the wp_posts_embeddings_minilm table that we have used in part 1.
ALTER TABLE wp_posts_embeddings_minilm ADD COLUMN post_content_text longtext NOT NULL;
The structure of the table is now:
CREATE TABLE `wp_posts_embeddings_minilm` (
`ID` bigint unsigned NOT NULL AUTO_INCREMENT,
`post_title` text NOT NULL,
`post_excerpt` text NOT NULL,
`guid` varchar(255) NOT NULL DEFAULT '',
`post_title_embedding` vector(2048) NOT NULL COMMENT 'GENAI_OPTIONS=EMBED_MODEL_ID=minilm',
`post_excerpt_embedding` vector(2048) NOT NULL COMMENT 'GENAI_OPTIONS=EMBED_MODEL_ID=minilm',
`post_content_text` longtext NOT NULL,
PRIMARY KEY (`ID`),
KEY `post_title` (`post_title`(255)),
KEY `post_excerpt` (`post_excerpt`(255)),
KEY `guid` (`guid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
This new column — post_content_text — will be populated with the sanitized content of post_content.
For example, original HTML in post_content:
<!-- wp:paragraph --> <p>HeatWave GenAI brings LLMs directly into your database, enabling powerful AI capabilities and natural language processing.</p> <!-- /wp:paragraph -->
Should be stored in post_content_text as:
HeatWave GenAI brings LLMs directly into your database, enabling powerful AI capabilities and natural language processing
Strip HTML tags with a javascript stored routine
This could be done in Javascript, inside HeatWave, as a stored program. Isn’t it magnificent? HeatWave supports stored routines written in JavaScript, since version 9.0.
A simple implementation could be the following:
CREATE FUNCTION sp.stripHtmlTags(htmlString LONGTEXT) RETURNS LONGTEXT NO SQL LANGUAGE JAVASCRIPT AS
$$
if (!htmlString) {
return "";
}
// Replace HTML tags with a space
return htmlString
.replace(/<[^>]+>/g, " ") // Replace all tags with a space
.replace(/\s+/g, " ") // Replace multiple spaces with a single space
.trim();
$$
;
Please bear in mind that I’m not a developer, so this code is provided for illustrative purposes only. It may contain errors or limitations. Please use it at your own risk and adapt it to your specific needs (also feel free to share back).
Let’s see if it works:
SELECT sp.stripHtmlTags('<!-- wp:paragraph --><p>HeatWave GenAI brings LLMs directly into your database, enabling powerful AI capabilities and natural language processing.</p><!-- /wp:paragraph -->') ;
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| sp.stripHtmlTags('<!-- wp:paragraph --><p>HeatWave GenAI brings LLMs directly into your database, enabling powerful AI capabilities and natural language processing.</p><!-- /wp:paragraph -->') |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| HeatWave GenAI brings LLMs directly into your database, enabling powerful AI capabilities and natural language processing. |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
Looks like this Javascript stored function is doing the job.
We can now uses it to sanitized all the articles.
UPDATE wp_posts_embeddings_minilm
INNER JOIN wp_posts USING (ID)
SET post_content_text = sp.stripHtmlTags(post_content)
WHERE post_status = 'publish'
AND post_type = 'post' ;
Now all the published posts have a sanitized text only version in the database. We can now uses it to generate article summaries.
Generating summaries with the JavaScript HeatWave GenAI API
HeatWave offers a JavaScript API that enables seamless integration with HeatWave GenAI, allowing you to perform natural language searches powered by LLMs. You can find more details in the official documentation.
To generate summaries for all my articles, I’ll create a stored procedure using the ml.generate method. This method supports two modes: single invocation and batch processing. While single invocation is ideal for handling new articles individually, we’ll focus on the batch mode here to process all existing articles efficiently:
SQL >
CREATE PROCEDURE sp.summarizePostBatch() LANGUAGE JAVASCRIPT AS
$$
let schema = session.getSchema("wordpress");
let table = schema.getTable("wp_posts_embeddings_minilm");
ml.generate(table, "post_content_text", "post_summary_json", {model_id: "mistral-7b-instruct-v1", task: "summarization"});
$$
;
ml.generate loads the model (mistral-7b-instruct-v1), generates a response (article summary) inside the post_summary_json column (automatically created), based on the prompt (article) from the post_content_text column and returns the response (a summary).
The process successfully created a new JSON column named post_summary_json:
SQL > SHOW CREATE TABLE wp_posts_embeddings_minilm;
CREATE TABLE `wp_posts_embeddings_minilm` (
`ID` bigint unsigned NOT NULL AUTO_INCREMENT,
`post_title` text NOT NULL,
`post_excerpt` text NOT NULL,
`guid` varchar(255) NOT NULL DEFAULT '',
`post_title_embedding` vector(2048) NOT NULL COMMENT 'GENAI_OPTIONS=EMBED_MODEL_ID=minilm',
`post_excerpt_embedding` vector(2048) NOT NULL COMMENT 'GENAI_OPTIONS=EMBED_MODEL_ID=minilm',
`post_content_text` longtext NOT NULL,
`post_summary_json` json DEFAULT NULL,
PRIMARY KEY (`ID`),
KEY `post_title` (`post_title`(255)),
KEY `post_excerpt` (`post_excerpt`(255)),
KEY `guid` (`guid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
Using the HeatWave MySQL JSON functions, we can check the content of this new column:
SELECT LEFT(post_summary_json->>"$.text", 100) AS json FROM wp_posts_embeddings_minilm WHERE ID = 1234\G
*************************** 1. row ***************************
json: Generative artificial intelligence (GenAI) is reshaping the content creation landscape. By training
Now let’s see how to create embeddings for all the summaries
Before implementing the routine, we need to do some modification on the table. The reason is that in HeatWave 9.2.1, the table columns must be in one of the following format: varchar, tinytext, text, mediumtext and longtext. If it is not the case you’ll trigger the ERROR: 1644 (45000): ML006093: Type of 'post_summary_json' must be in ["varchar", "tinytext", "text", "mediumtext", "longtext"] Quite explicit though!
We’ll transfer the content of post_summary_json into a text column named post_summary:
SQL >
ALTER TABLE wp_posts_embeddings_minilm ADD COLUMN post_summary text;
UPDATE wp_posts_embeddings_minilm SET post_summary = post_summary_json->>"$.text";
And finally to avoid error ERROR: 1644 (45000): ML006093, we must drop post_summary_json:
ALTER TABLE wp_posts_embeddings_minilm DROP COLUMN post_summary_json;
ml.embed also supports two variants, one for a single invocation, and one for batch processing.
Single invocation of ml.embed
The goal is to create a stored procedure that encodes a summarized article into a vector embedding using the minilm embedding model:
SQL >
CREATE PROCEDURE sp.createEmbeddings(IN text2embed LONGTEXT, OUT vect VECTOR) LANGUAGE JAVASCRIPT AS
$$
let embedding = ml.embed(text2embed, {model_id: "minilm"});
vect = embedding;
$$
;
The input parameter is the summarized article:
SQL >
CALL sp.createEmbeddings(" This article explores the integration of HeatWave GenAI with Python for AI-driven applications. It provides a step-by-step guide to building a simple chatbot system that interacts with HeatWave using its in-database LLMs and external APIs from OCI Generative AI Service. The script demonstrates how to establish a connection to the HeatWave MySQL database, load and manage multiple LLMs within HeatWave, allow users to select their preferred model dynamically, facilitate chatbot interactions using HeatWave Chat, and retrieve and manage chat options. The article also discusses the benefits of using HeatWave GenAI for AI-driven applications, including its modular and scalable design, dynamic LLM selection, and powerful capabilities in AI.", @myVect);
The output parameter — @myVect — contains the embedding:
To generate embeddings for all current posts at once, batch processing is the most efficient approach. Let’s create a new stored procedure in JavaScript:
SQL >
CREATE PROCEDURE sp.createEmbeddingsBatch() LANGUAGE JAVASCRIPT AS
$$
let schema = session.getSchema("wordpress");
let table = schema.getTable("wp_posts_embeddings_minilm");
ml.embed(table, "post_summary", "post_summary_embedding", {model_id: "minilm"});
$$
;
ml.embed loads the model (minilm), generates an embedding inside the post_summary_embedding column (automatically created), based on the post summaries from the post_summary column.
The process successfully created a new VECTOR column named post_summary_embedding:
SQL > SHOW CREATE TABLE wp_posts_embeddings_minilm;
CREATE TABLE `wp_posts_embeddings_minilm` (
`ID` bigint unsigned NOT NULL AUTO_INCREMENT,
`post_title` text NOT NULL,
`post_excerpt` text NOT NULL,
`guid` varchar(255) NOT NULL DEFAULT '',
`post_title_embedding` vector(2048) NOT NULL COMMENT 'GENAI_OPTIONS=EMBED_MODEL_ID=minilm',
`post_excerpt_embedding` vector(2048) NOT NULL COMMENT 'GENAI_OPTIONS=EMBED_MODEL_ID=minilm',
`post_content_text` longtext NOT NULL,
`post_summary` text,
`post_summary_embedding` vector(2048) NOT NULL COMMENT 'GENAI_OPTIONS=EMBED_MODEL_ID=minilm',
PRIMARY KEY (`ID`),
KEY `post_title` (`post_title`(255)),
KEY `post_excerpt` (`post_excerpt`(255)),
KEY `guid` (`guid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
Query Encoding and Vector Similarity Operations
The penultimate step is to convert the user’s query into a vector representation that captures its semantic meaning. This process, known as query encoding, transforms text into numerical embeddings. Once encoded, we will be able to perform a similarity search to find the most relevant results by comparing the query’s embedding with precomputed vectors from HeatWave.
Encode the query into a vector embedding
Like we have seen in part1, to generate a vector embedding for the query, we use the ML_EMBED_ROW routine. This function applies the specified embedding model to encode the given text into a vector representation. The routine returns a VECTOR containing the numerical embedding of the text.
Using it is straightforward. Let’s define two variables: @searchItem (the text to encode) and @embeddOptions (the embedding model used for encoding):
SQL >
-- Set variables
SET @embeddOptions = '{"model_id": "minilm"}';
SET @searchItem = "Generative artificial intelligence";
-- Encode the query using the embedding model
SELECT sys.ML_EMBED_ROW(@searchItem, @embeddOptions) into @searchItemEmbedding;
You can print the vector using the following query;
SELECT FROM_VECTOR(vect)
FROM (
SELECT sys.ML_EMBED_ROW(@searchItem, @embeddOptions) AS vect
) AS dt;
[-6.21515e-02,1.61460e-02,1.25987e-02,-1.98096e-02,... (truncated)
Similarity search
To retrieve relevant blog content, we perform vector similarity calculations using the DISTANCE function. This function computes the distance between two vectors using COSINE, DOT, or EUCLIDEAN distance metrics. Here, the two vectors being compared are the encoded query (@searchItemEmbedding) and the precomputed embeddings stored in the wp_posts_embeddings_minilm table (post_summary_embedding)
A cosine similarity search for article summaries can be conducted using:
-- Ex 4.1. Similarity search only on the post summaries (post_summary_embedding)
WITH distances AS (
SELECT
ID,
post_title,
post_excerpt,
DISTANCE(@searchItemEmbedding, post_summary_embedding, 'COSINE') AS min_distance
FROM WP_embeddings.wp_posts_embeddings_minilm
)
SELECT *
FROM distances
ORDER BY min_distance
LIMIT 5\G
A cosine similarity search for article titles, excerpts and summaries can be conducted using:
-- Ex 5.1 Similarity search on title, excerpt & post summary (post_title_embedding, post_excerpt_embedding & post_summary_embedding)
WITH distances AS (
SELECT
post_title,
post_excerpt,
(
DISTANCE(post_title_embedding, @searchItemEmbedding, 'COSINE') +
DISTANCE(post_excerpt_embedding, @searchItemEmbedding, 'COSINE') +
DISTANCE(post_summary_embedding, @searchItemEmbedding, 'COSINE')
) / 3 AS avg_distance,
guid
FROM WP_embeddings.wp_posts_embeddings_minilm
)
SELECT *
FROM distances
ORDER BY avg_distance
LIMIT 5\G
Finally, you can (try to) improve the results using a reranking technique:
-- Ex 5.2 Weighted Similarity search on title, excerpt & post summary (post_title_embedding, post_excerpt_embedding & post_summary_embedding)
WITH initial_results AS (
SELECT
post_title,
post_excerpt,
DISTANCE(post_title_embedding, @searchItemEmbedding, 'COSINE') AS title_distance,
DISTANCE(post_excerpt_embedding, @searchItemEmbedding, 'COSINE') AS excerpt_distance,
DISTANCE(post_summary_embedding, @searchItemEmbedding, 'COSINE') AS summary_distance,
guid
FROM WP_embeddings.wp_posts_embeddings_minilm
ORDER BY title_distance + excerpt_distance + summary_distance ASC -- Simple combination
LIMIT 15 -- Retrieve a larger initial set
),
reranked_results AS (
SELECT
ID,
post_title,
post_excerpt,
(0.2 * title_distance + 0.3 * excerpt_distance + 0.5 * summary_distance) AS combined_distance, -- Weighted combination
guid
FROM initial_results
)
SELECT post_title, post_excerpt, combined_distance, guid
FROM reranked_results
ORDER BY combined_distance ASC
LIMIT 5\G
*************************** 1. row ***************************
post_title: HeatWave GenAI: Your AI-Powered Content Creation Partner
post_excerpt: Generative artificial intelligence (GenAI) is reshaping the content creation landscape. By training on vast datasets, these "intelligent" systems can produce new, human-quality content across a multitude of domains.
Oracle's HeatWave GenAI (starting with version 9.0.1) is at the forefront of this revolution, offering an integrated platform that combines in-database large language models (LLMs), vector stores, and scale-out vector processing to streamline content generation.
This article explores how HeatWave GenAI is empowering businesses to produce high-quality content rapidly and effectively, making it an indispensable tool for industries demanding speed, accuracy, and security.
combined_distance: 0.5093500733375549
guid: https://dasini.net/blog/?p=1234
*************************** 2. row ***************************
post_title: Simplifying AI Development: A Practical Guide to HeatWave GenAI’s RAG & Vector Store Features
post_excerpt: This tutorial explores HeatWave GenAI, a cloud service that simplifies interacting with unstructured data using natural language. It combines large language models, vector stores, and SQL queries to enable tasks like content generation, chatbot, and retrieval-augmented generation (RAG). The focus is on RAG and how HeatWave GenAI’s architecture helps users gain insights from their data.
combined_distance: 0.637738311290741
guid: https://dasini.net/blog/?p=2345
*************************** 3. row ***************************
post_title: HeatWave GenAI: Sentiment Analysis Made Easy-Peasy
post_excerpt: This new AI tech, called generative AI (or GenAI), can dive deep into what people are saying and tell us if they’re feeling positive, negative, or neutral.
Let’s see how HeatWave GenAI, can help you to enhance your understanding of customer sentiment, improve decision-making, and drive business success.
combined_distance: 0.6417026937007904
guid: https://dasini.net/blog/?p=3456
*************************** 4. row ***************************
post_title: Building an Interactive LLM Chatbot with HeatWave Using Python
post_excerpt: AI-powered applications require robust and scalable database solutions to manage and process large amounts of data efficiently. HeatWave is an excellent choice for such applications, providing high-performance OLTP, analytics, machine learning and generative artificial intelligence capabilities.
In this article, we will explore a Python 3 script that connects to an HeatWave instance and enables users to interact with different large language models (LLMs) dynamically.
combined_distance: 0.6545232772827148
guid: https://dasini.net/blog/?p=5678
*************************** 5. row ***************************
post_title: Webinar - Apprentissage automatique avec MySQL HeatWave
post_excerpt: HeatWave Machine Learning (ML) inclut tout ce dont les utilisateurs ont besoin pour créer, former, déployer et expliquer des modèles d’apprentissage automatique dans MySQL HeatWave, sans coût supplémentaire.
Dans ce webinaire vous apprendrez...
combined_distance: 0.7031511843204499
guid: https://dasini.net/blog/?p=6789
Peroration
In this second part of our journey into building an AI-powered search engine with HeatWave GenAI, we explored advanced techniques to refine search relevance. By incorporating reranking strategies and leveraging article summaries for embedding generation, we significantly improved the quality of retrieved results.
Furthermore, we demonstrated how to harness HeatWave’s support for JavaScript-based stored programs to sanitize content, generate summaries, and compute embeddings — all within the database. This seamless integration of AI-powered search within HeatWave showcases its potential for efficient, scalable, and intelligent information retrieval.
With these enhancements, our search engine is now more capable of understanding and delivering relevant results, although we still can further optimize performance by experimenting with different embedding models, or even integrating additional AI-driven ranking techniques.
For a deeper understanding, also consider reading these articles.
Traditional SQL search relies on structured queries (SELECT, WHERE, JOIN, …) and exact or partial matches based on conditions (e.g., WHERE name = 'Olivier' / WHERE name LIKE '%Olivier%').
A typical query may look like:
SELECT title
FROM articles
WHERE Category = 'HeatWave' OR tag LIKE "%AI%";
While efficient for structured data, it has limited flexibility for search variations and fails to grasp context or intent.
An alternative is SQL Full-Text Search (FTS), which enables efficient keyword-based searches across large text datasets. For example, MySQL implements FTS using MATCH and AGAINST (e.g. MATCH(name) AGAINST(‘Olivier’)). This feature indexes text content within database columns, allowing for advanced search capabilities such as phrase matching, proximity searches, and relevance scoring.
A typical query may look like:
SELECT * FROM articles
WHERE MATCH (title, body)
AGAINST ('HeatWave' IN NATURAL LANGUAGE MODE);
FTS is usually faster and more relevant than basic SQL searches, efficiently handling large text fields. However, it remains keyword-based rather than semantic, meaning it may overlook context-based variations.
Another option is AI-powered search using large language models (LLMs), also known as semantic search. Unlike keyword-based methods, it leverages embeddings — vector representations of words or sentences — to understand meaning. This enables it to handle synonyms, paraphrasing, and contextual relationships (e.g., searching for ‘AI’ may also return articles on ‘machine learning’). Additionally, it often integrates retrieval-augmented generation(RAG) to enhance responses with external knowledge.
In this article, we’ll dive deeper into AI-powered search using an LLM with the help of HeatWave GenAI…
Key features of HeatWave GenAI
What we want to do?
The goal is to build an AI-powered search engine for an application, designed to provide users with the most relevant articles based on their queries, using semantic search. I’ll be using the data from my WordPress-based blog — https://dasini.net/blog/ — with the AI component powered by HeatWave GenAI. This leverages its in-database large language models and vector store capabilities (In-Database Embedding Models, The VECTOR Type, Vector Functions). To ensure a focused and concise presentation, I’ll simplify this implementation by limiting the search to post titles and excerpts rather than the full article. Although this will significantly reduce the relevance of the results, it provides an opportunity to explore more comprehensive solutions in a future article.
Create the table which will contain the embeddings
In WordPress the blog posts are stored in the table: wp_posts. The important columns to reach our goal are:
ID bigint unsigned NOT NULL AUTO_INCREMENT,
post_title text NOT NULL
post_excerpt text NOT NULL
guid varchar(255) NOT NULL DEFAULT ”
The columns contains respectively, the unique identifier of the post, its title, a short excerpt (hopefully) of the post and the URL of the article. Example:
wordpress SQL>
SELECT ID, post_title, post_excerpt, guid from wp_posts WHERE post_status = 'publish' AND post_type = 'post' AND ID = 1234\G
*************************** 1. row ***************************
ID: 1234
post_title: HeatWave GenAI: Your AI-Powered Content Creation Partner
post_excerpt: Generative artificial intelligence (GenAI) is reshaping the content creation landscape. By training on vast datasets, these "intelligent" systems can produce new, human-quality content across a multitude of domains.
Oracle's HeatWave GenAI (starting with version 9.0.1) is at the forefront of this revolution, offering an integrated platform that combines in-database large language models (LLMs), vector stores, and scale-out vector processing to streamline content generation.
This article explores how HeatWave GenAI is empowering businesses to produce high-quality content rapidly and effectively, making it an indispensable tool for industries demanding speed, accuracy, and security.
guid: https://dasini.net/blog/?p=1234
Based on these information, I created a table, wp_posts_embeddings_minilm, that contains the 4 columns:
wordpress SQL>
CREATE TABLE `wp_posts_embeddings_minilm` (
`ID` bigint unsigned NOT NULL AUTO_INCREMENT,
`post_title` text NOT NULL,
`post_excerpt` text NOT NULL,
`guid` varchar(255) NOT NULL DEFAULT '',
PRIMARY KEY (`ID`),
KEY `post_title` (`post_title`(255)),
KEY `post_excerpt` (`post_excerpt`(255)),
KEY `guid` (`guid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
Then I populate this new table with my published posts:
wordpress SQL>
INSERT INTO wp_posts_embeddings_minilm SELECT ID, post_title, post_excerpt, guid FROM wp_posts WHERE post_status = 'publish' AND post_type = 'post';
End of the first step. Now we need to create the embeddings for each published posts.
Create the embeddings
We can query HeatWave’s sys.ML_SUPPORTED_LLMS table to know what embedding model we can use:
The 3 firsts (minilm, all_minilm_l12_v2 & multilingual-e5-small) are HeatWave’s in-database embedding models, there is no extra cost to use them (minilm and all_minilm_l12_v2 are 2 different names for the same model). Both are for encoding text or files in any supported language. HeatWave GenAI uses minilm, by default, for encoding English documents. While my blog also contains French article I’ll use this LLM.
The other models are from OCI Generative AI Service. They can also be used in the HeatWave workflow however their use will incur additional costs.
The comprehensive list of the languages, embedding models, and large language models (LLMs) that HeatWave GenAI supports is available here.
We are going to use the minilm model. The idea is to use this embedding model to encode the rows into a vector embedding. Embeddings creation is very easy with HeatWave, we only need to use 1 routine: sys.ML_EMBED_TABLE. This stored procedure runs multiple embedding generations in a batch, in parallel.
As a reminder this is what the wp_posts_embeddings_minilm table looks like:
DESC wp_posts_embeddings_minilm;
+--------------+-----------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------------+-----------------+------+-----+---------+----------------+
| ID | bigint unsigned | NO | PRI | NULL | auto_increment |
| post_title | text | NO | MUL | NULL | |
| post_excerpt | text | NO | MUL | NULL | |
| guid | varchar(255) | NO | MUL | | |
+--------------+-----------------+------+-----+---------+----------------+
DESC wp_posts_embeddings_minilm;
+------------------------+-----------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------------------+-----------------+------+-----+---------+----------------+
| ID | bigint unsigned | NO | PRI | NULL | auto_increment |
| post_title | text | NO | MUL | NULL | |
| post_excerpt | text | NO | MUL | NULL | |
| guid | varchar(255) | NO | MUL | | |
| post_title_embedding | vector(2048) | NO | | NULL | |
| post_excerpt_embedding | vector(2048) | NO | | NULL | |
+------------------------+-----------------+------+-----+---------+----------------+
Title and excerpt embeddings have been created!
SHOW CREATE TABLE wp_posts_embeddings_minilm\G
*************************** 1. row ***************************
Table: wp_posts_embeddings_minilm
Create Table: CREATE TABLE `wp_posts_embeddings_minilm` (
`ID` bigint unsigned NOT NULL AUTO_INCREMENT,
`post_title` text NOT NULL,
`post_excerpt` text NOT NULL,
`guid` varchar(255) NOT NULL DEFAULT '',
`post_title_embedding` vector(2048) NOT NULL COMMENT 'GENAI_OPTIONS=EMBED_MODEL_ID=minilm',
`post_excerpt_embedding` vector(2048) NOT NULL COMMENT 'GENAI_OPTIONS=EMBED_MODEL_ID=minilm',
PRIMARY KEY (`ID`),
KEY `post_title` (`post_title`(255)),
KEY `post_excerpt` (`post_excerpt`(255)),
KEY `guid` (`guid`)
) ENGINE=InnoDB AUTO_INCREMENT=7059 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
Query Encoding and Vector Similarity Operations
To retrieve the most relevant articles, we first need to convert the user’s query into a vector representation that captures its semantic meaning. This process, known as query encoding, transforms text into numerical embeddings. Once encoded, we can perform a similarity search to find the most relevant results by comparing the query’s embedding with precomputed vectors from our database.
Encode the query into a vector embedding
To generate a vector embedding for the query, we use the ML_EMBED_ROW routine. This function applies the specified embedding model to encode the given text into a vector representation. The routine returns a VECTOR containing the numerical embedding of the text.
Using it is straightforward. I define two variables: @searchItem (the text to encode) and @embeddOptions (the embedding model used for encoding):
wordpress SQL>
-- Set variables
SET @embeddOptions = '{"model_id": "minilm"}';
SET @searchItem = "Generative artificial intelligence";
-- Encode the query using the embedding model
SELECT sys.ML_EMBED_ROW(@searchItem, @embeddOptions) into @searchItemEmbedding;
If you want to see the content of the variable (not sure it is a good idea):
You can print the vector using the following query;
SELECT FROM_VECTOR(vect)
FROM (
SELECT sys.ML_EMBED_ROW(@searchItem, @embeddOptions) AS vect
) AS dt;
[-6.21515e-02,1.61460e-02,1.25987e-02,-1.98096e-02,... (truncated)
Similarity search
To retrieve relevant blog content, we perform vector similarity calculations using the DISTANCE function. This function computes the distance between two vectors using COSINE, DOT, or EUCLIDEAN distance metrics. In our case, the two vectors being compared are the encoded query (@searchItemEmbedding) and the precomputed embeddings stored in the wp_posts_embeddings_minilm table (post_title_embedding and post_excerpt_embedding)
A cosine similarity search for article titles can be conducted using:
-- Ex 1.0. Similarity search only on titles (post_title_embedding)
SELECT ID, post_title, post_excerpt, DISTANCE(@searchItemEmbedding, post_title_embedding, 'COSINE') AS min_distance
FROM WP_embeddings.wp_posts_embeddings_minilm
ORDER BY min_distance ASC
LIMIT 3\G
*************************** 1. row ***************************
ID: 2345
post_title: Simplifying AI Development: A Practical Guide to HeatWave GenAI’s RAG & Vector Store Features
post_excerpt: This tutorial explores HeatWave GenAI, a cloud service that simplifies interacting with unstructured data using natural language. It combines large language models, vector stores, and SQL queries to enable tasks like content generation, chatbot, and retrieval-augmented generation (RAG). The focus is on RAG and how HeatWave GenAI’s architecture helps users gain insights from their data.
min_distance: 0.1232912540435791
*************************** 2. row ***************************
ID: 1234
post_title: HeatWave GenAI: Your AI-Powered Content Creation Partner
post_excerpt: Generative artificial intelligence (GenAI) is reshaping the content creation landscape. By training on vast datasets, these "intelligent" systems can produce new, human-quality content across a multitude of domains.
Oracle's HeatWave GenAI (starting with version 9.0.1) is at the forefront of this revolution, offering an integrated platform that combines in-database large language models (LLMs), vector stores, and scale-out vector processing to streamline content generation.
This article explores how HeatWave GenAI is empowering businesses to produce high-quality content rapidly and effectively, making it an indispensable tool for industries demanding speed, accuracy, and security.
min_distance: 0.1264844536781311
*************************** 3. row ***************************
ID: 3456
post_title: HeatWave GenAI: Sentiment Analysis Made Easy-Peasy
post_excerpt: This new AI tech, called generative AI (or GenAI), can dive deep into what people are saying and tell us if they’re feeling positive, negative, or neutral.
Let’s see how HeatWave GenAI, can help you to enhance your understanding of customer sentiment, improve decision-making, and drive business success.
min_distance: 0.12810611724853516
A probably more elegant query, using Common Table Expression (CTE), is:
-- Ex 1.1. Similarity search only on titles (post_title_embedding) w/ CTE
WITH distances AS (
SELECT
ID,
post_title,
post_excerpt,
DISTANCE(@searchItemEmbedding, post_title_embedding, 'COSINE') AS min_distance
FROM WP_embeddings.wp_posts_embeddings_minilm
)
SELECT *
FROM distances
ORDER BY min_distance
LIMIT 3\G
*************************** 1. row ***************************
ID: 2345
post_title: Simplifying AI Development: A Practical Guide to HeatWave GenAI’s RAG & Vector Store Features
post_excerpt: This tutorial explores HeatWave GenAI, a cloud service that simplifies interacting with unstructured data using natural language. It combines large language models, vector stores, and SQL queries to enable tasks like content generation, chatbot, and retrieval-augmented generation (RAG). The focus is on RAG and how HeatWave GenAI’s architecture helps users gain insights from their data.
min_distance: 0.1232912540435791
*************************** 2. row ***************************
ID: 1234
post_title: HeatWave GenAI: Your AI-Powered Content Creation Partner
post_excerpt: Generative artificial intelligence (GenAI) is reshaping the content creation landscape. By training on vast datasets, these "intelligent" systems can produce new, human-quality content across a multitude of domains.
Oracle's HeatWave GenAI (starting with version 9.0.1) is at the forefront of this revolution, offering an integrated platform that combines in-database large language models (LLMs), vector stores, and scale-out vector processing to streamline content generation.
This article explores how HeatWave GenAI is empowering businesses to produce high-quality content rapidly and effectively, making it an indispensable tool for industries demanding speed, accuracy, and security.
min_distance: 0.1264844536781311
*************************** 3. row ***************************
ID: 3456
post_title: HeatWave GenAI: Sentiment Analysis Made Easy-Peasy
post_excerpt: This new AI tech, called generative AI (or GenAI), can dive deep into what people are saying and tell us if they’re feeling positive, negative, or neutral.
Let’s see how HeatWave GenAI, can help you to enhance your understanding of customer sentiment, improve decision-making, and drive business success.
min_distance: 0.12810611724853516
Of course, you can perform the same search on the excerpt alone. Alternatively, you can run a similarity search across both columns for more comprehensive results:
-- Ex 3.1 Similarity search on title & excerpt (post_title_embedding & post_excerpt_embedding)
WITH distances AS (
SELECT
ID,
post_title,
post_excerpt,
(
DISTANCE(post_title_embedding, @searchItemEmbedding, 'COSINE') +
DISTANCE(post_excerpt_embedding, @searchItemEmbedding, 'COSINE')
) / 2 AS avg_distance
FROM WP_embeddings.wp_posts_embeddings_minilm
)
SELECT *
FROM distances
ORDER BY avg_distance
LIMIT 5\G
*************************** 1. row ***************************
ID: 1234
post_title: HeatWave GenAI: Your AI-Powered Content Creation Partner
post_excerpt: Generative artificial intelligence (GenAI) is reshaping the content creation landscape. By training on vast datasets, these "intelligent" systems can produce new, human-quality content across a multitude of domains.
Oracle's HeatWave GenAI (starting with version 9.0.1) is at the forefront of this revolution, offering an integrated platform that combines in-database large language models (LLMs), vector stores, and scale-out vector processing to streamline content generation.
This article explores how HeatWave GenAI is empowering businesses to produce high-quality content rapidly and effectively, making it an indispensable tool for industries demanding speed, accuracy, and security.
avg_distance: 0.5131600499153137
*************************** 2. row ***************************
ID: 3456
post_title: HeatWave GenAI: Sentiment Analysis Made Easy-Peasy
post_excerpt: This new AI tech, called generative AI (or GenAI), can dive deep into what people are saying and tell us if they’re feeling positive, negative, or neutral.
Let’s see how HeatWave GenAI, can help you to enhance your understanding of customer sentiment, improve decision-making, and drive business success.
avg_distance: 0.5587222874164581
*************************** 3. row ***************************
ID: 2345
post_title: Simplifying AI Development: A Practical Guide to HeatWave GenAI’s RAG & Vector Store Features
post_excerpt: This tutorial explores HeatWave GenAI, a cloud service that simplifies interacting with unstructured data using natural language. It combines large language models, vector stores, and SQL queries to enable tasks like content generation, chatbot, and retrieval-augmented generation (RAG). The focus is on RAG and how HeatWave GenAI’s architecture helps users gain insights from their data.
avg_distance: 0.6403274536132812
*************************** 4. row ***************************
ID: 6789
post_title: Webinar - Apprentissage automatique avec MySQL HeatWave
post_excerpt: HeatWave Machine Learning (ML) inclut tout ce dont les utilisateurs ont besoin pour créer, former, déployer et expliquer des modèles d’apprentissage automatique dans MySQL HeatWave, sans coût supplémentaire.
Dans ce webinaire vous apprendrez...
avg_distance: 0.7226708233356476
*************************** 72525. row ***************************
ID: 5678
post_title: Building an Interactive LLM Chatbot with HeatWave Using Python
post_excerpt: AI-powered applications require robust and scalable database solutions to manage and process large amounts of data efficiently. HeatWave is an excellent choice for such applications, providing high-performance OLTP, analytics, machine learning and generative artificial intelligence capabilities.
In this article, we will explore a Python 3 script that connects to an HeatWave instance and enables users to interact with different large language models (LLMs) dynamically.
avg_distance: 0.736954927444458
For this example, the average distance was selected to combine title and excerpt similarity. However, alternative aggregation techniques can be employed.
wordpress SQL>
SELECT ID, post_title, post_excerpt, guid FROM wp_posts WHERE ID = 4567\G
*************************** 1. row ***************************
ID: 4567
post_title: Building an Interactive LLM Chatbot with HeatWave Using Python
post_excerpt: AI-powered applications require robust and scalable database solutions to manage and process large amounts of data efficiently. HeatWave is an excellent choice for such applications, providing high-performance OLTP, analytics, machine learning and generative artificial intelligence capabilities.
In this article, we will explore a Python 3 script that connects to an HeatWave instance and enables users to interact with different large language models (LLMs) dynamically.
guid: https://dasini.net/blog/?p=4567
To enable this article in the AI-powered search engine, I need to generate embeddings for both the title (post_title) and excerpt (post_excerpt). This is accomplished using sys.ML_EMBED_ROW, which encodes text into a vector embedding based on a specified model, returning a VECTOR data type.
These embeddings will then be inserted into the wp_posts_embeddings_minilm table, utilizing the same minilm embedding model:
wordpress SQL>
INSERT INTO wp_posts_embeddings_minilm (ID, post_title, post_excerpt, guid, post_title_embedding, post_excerpt_embedding)
SELECT ID, post_title, post_excerpt, guid, sys.ML_EMBED_ROW(post_title, '{"model_id": "minilm"}'), sys.ML_EMBED_ROW(post_excerpt,'{"model_id": "minilm"}')
FROM wordpress.wp_posts
WHERE ID = 4567;
Et voilà! This implementation focused on post titles and excerpts for brevity, it lays a solid foundation for more comprehensive searches across entire article content, a topic to be explored in a future article.
Peroration
In this article, we explored how HeatWave GenAI enables AI-powered semantic search by leveraging in-database embeddings and vector similarity operations. Unlike traditional SQL search and full-text search, which rely on keyword matching, HeatWave GenAI provides deeper contextual understanding by transforming text into vector representations and performing similarity searches using the DISTANCE function.
The power of HeatWave GenAI’s in-database LLMs and vector store features was highlighted, showcasing its efficiency in handling both embedding generation and similarity calculations. Furthermore, the process of integrating new articles into the search engine by generating and inserting their embeddings was outlined, ensuring the search remains up-to-date. This approach not only enhances content discovery but also lays the groundwork for more advanced applications, such as personalized recommendations and intelligent query responses.
By adopting AI-powered search, we empower users with a more intuitive and effective way to discover relevant information, ultimately improving the overall user experience. Thanks to HeatWave GenAI which provides a robust and scalable solution for integrating advanced AI capabilities directly within the database.
For a deeper understanding, also consider reading these articles.
Building an Interactive LLM Chatbot with HeatWave Using Python
AI-powered applications require robust and scalable database solutions to manage and process large amounts of data efficiently. HeatWave is an excellent choice for such applications, providing high-performance OLTP, analytics, machine learning and generative artificial intelligence capabilities.
In this article, we will explore a Python 3 script that connects to an HeatWave instance and enables users to interact with different large language models (LLMs) dynamically. This script demonstrates how to:
Establish a connection with HeatWave using the mysql.connectormodule.
By the end of this article, you’ll have a deep understanding of how to integrate HeatWave with Python for AI-driven applications.
HeatWave Chat
HeatWave Chat enables you to engage in human-like conversations with an AI. Within a single session, you can ask multiple queries and receive relevant responses. This conversational agent leverages powerful LLMs to understand your input and generate natural-sounding replies. HeatWave Chat enhances the conversation by utilizing a chat history, allowing you to ask follow-up questions seamlessly. Furthermore, it employs vector search to access and utilize knowledge stored within its built-in vector store. All communication and processing occur securely within the HeatWave service, ensuring fast and reliable responses.
In this article I’m using HeatWave 9.2.0-u1-cloud.
I use this quote from (apparently) Linus Torvald to warn you that I’m not a developer, so this code, available on my Github, is provided for illustrative purposes only. It may contain errors or limitations. Please use it at your own risk and adapt it to your specific needs (also feel free to share back).
HeatWave GenAI and in-database Mistral LLM
Code Explanation
This Python script is a complete implementation of a chatbot system that interacts with HeatWave. It allows users to select and interact with different LLMs (Large Language Models).
1. Importing Required Modules
import mysql.connector
from typing import Tuple, Optional
from mysql.connector.cursor import MySQLCursor
from config.config_heatwave import DB_CONFIG # Import the MySQL configuration
mysql.connector: Used to interact with the HeatWave MySQL database.
typing (Tuple, Optional): Provides type hints for better code readability and maintainability.
MySQLCursor: A specific cursor class from mysql.connector for executing MySQL queries.
DB_CONFIG: Imported from the external config file, storing database credentials and settings.
2. Connecting to the HeatWave MySQL Database
def connect_to_mysql() -> mysql.connector.MySQLConnection:
"""Establish a connection to the MySQL database."""
return mysql.connector.connect(**DB_CONFIG)
Establishes a MySQL connection using DB_CONFIG.
Returns a MySQLConnection object that will be used to execute queries.
3. Loading LLM Models into HeatWave
def load_llm(cursor: MySQLCursor, llm_options: Tuple[str, ...]) -> None:
"""Load language models into HeatWave."""
sql_load_llm = 'sys.ML_MODEL_LOAD'
for llm in llm_options:
args = (llm, 'NULL')
cursor.callproc(sql_load_llm, args)
print(f"LLM Loaded: {llm}")
print("All LLMs loaded successfully.")
sys.ML_MODEL_LOAD loads a large language model into the HeatWave Cluster.
callproc() executes the stored procedure for each LLM in llm_options.
Confirms successful loading with print statements.
4. Selecting an LLM Model
def select_llm(llm_options: Tuple[str, ...]) -> str:
"""
Prompt the user to select an LLM from the provided options.
Supports up to 4 choices.
"""
option_map = {str(i + 1): llm for i, llm in enumerate(llm_options)}
while True:
# Dynamically build the prompt based on available options
prompt = "Choose your LLM:\n"
for i, llm in enumerate(llm_options):
prompt += f"{i + 1}-({llm})\n"
prompt += "Enter your choice: "
choice = input(prompt)
# Validate user input
if choice in option_map:
return option_map[choice]
print(f"Invalid choice. Please select a number between 1 and {len(llm_options)}.")
Fetches the session variable @chat_options that holds chat configurations.
Set Chat Options
def set_chat_options(cursor: MySQLCursor, llm: str) -> None:
"""Initialize or update the session variable 'chat_options'."""
chat_options = get_chat_options(cursor)
if not chat_options:
# Initialize @chat_options if not set
options = f'{{"model_options": {{"model_id": "{llm}"}}}}'
sql = f"SET @chat_options = '{options}'"
else:
# Update @chat_options if already exists
sql = f"SET @chat_options = JSON_SET(@chat_options, '$.model_options.model_id', '{llm}')"
cursor.execute(sql)
print(f"Using model: {llm}")
print("-" * 40)
Initializes or updates the @chat_options session variable with the selected LLM.
Uses JSON_SET() to update an existing chat session.
8. Main Function Execution
def main() -> None:
"""Main function to run the LLM interaction."""
try:
with connect_to_mysql() as connection:
with connection.cursor() as cursor:
# Define available LLM options
llm_options = ("llama3-8b-instruct-v1", "mistral-7b-instruct-v1", "cohere.command-r-plus-08-2024", "meta.llama-3.1-70b-instruct")
# Load LLMs
load_llm(cursor, llm_options)
# Prompt user to select an LLM
selected_llm = select_llm(llm_options)
# Set chat options for the selected LLM
set_chat_options(cursor, selected_llm)
# Begin chat loop
while True:
chat(cursor, selected_llm)
except mysql.connector.Error as err:
print(f"Database error: {err}")
except KeyboardInterrupt:
print("\nExiting the application.")
finally:
print("Goodbye!")
Uses context managers (with statement) to manage MySQL connections safely.
Calls load_llm(), select_llm(), and set_chat_options() before starting the chat loop.
Handles database errors and user interruption (Ctrl+C).
9. Running the Script
if __name__ == "__main__":
main()
Ensures the script executes main() only when run directly.
Final Thoughts
You can further extend this solution to use HeatWave Chat with Retrieval-Augmented Generation (RAG). RAG is a technique that combines the power of Large Language Models with external knowledge bases. The primary goal of RAG is to enhance the quality, relevance, and accuracy of AI-generated responses by retrieving relevant information from these sources. You can find more information and a practical example in this article: Simplifying AI Development: A Practical Guide to HeatWave GenAI’s RAG & Vector Store Features
You can also support additional LLMs, APIs, or advanced user interfaces. To summarize, developers and data scientists can easily build intelligent applications that efficiently handle real-time AI interactions while leveraging the power of HeatWave.
Peroration
This Python script demonstrates that with a few lines of codes, you can easily build a simple but robust chatbot system leveraging HeatWave GenAI and its in-database or external (from OCI Generative AI Service) LLMs.
It’s an effective way to integrate HeatWave (and its MySQL API) with Python for AI-driven apps like chatbot interactions. By leveraging stored procedures and session variables, it allows seamless LLM management and user interaction.
For a deeper understanding, also consider reading these articles.
This tutorial explores HeatWave GenAI, a cloud service that simplifies interacting with unstructured data using natural language. It combines large language models, vector stores, and SQL queries to enable tasks like content generation, chatbot, and retrieval-augmented generation (RAG). The focus is on RAG and how HeatWave GenAI’s architecture helps users gain insights from their data. By the end, you’ll learn how to implement these features effectively to unlock the full potential of generative AI for your applications.
HeatWave GenAI empowers you to interact with unstructured data seamlessly using natural language. Whether you’re performing content generation, summarization, sentiment analysis, or retrieval-augmented generation (RAG), HeatWave GenAI simplifies the process with its intuitive design and robust features.
Here’s why HeatWave GenAI stands out:
Natural-Language Queries: Communicate with your data effortlessly by running natural-language searches using in-database or external large language models (LLMs).
SQL Familiarity: The system leverages a SQL-based interface, making it accessible and easy to adopt for users already familiar with relational databases.
Integrated and Optimized Pipeline: All the necessary components—vector store generation, RAG-based vector search, and chatbot capabilities—are seamlessly integrated, ensuring smooth performance and ease of use.
End-to-End Generative AI Capabilities: HeatWave GenAI supports direct integration of generative AI into your applications, providing a complete pipeline for data retrieval, analysis, and interaction.
What is RAG?
RAG stands for Retrieval-Augmented Generation. It’s a technique that combines the power of Large Language Models (LLMs) with external knowledge bases. The primary goal of RAG is to enhance the quality, relevance, and accuracy of AI-generated responses by retrieving relevant information from these sources.
Essentially, it’s like giving an LLM access to a vast library, allowing it to provide more informative and contextually relevant answers.
How RAG Works
Data Retrieval: The system retrieves relevant information from a connected knowledge base, vector store, or database using a query.
Generation: The retrieved data is then used as context to guide the language model in generating a response.
Output: The result is a response that blends the creative capabilities of the LLM with factual, targeted insights from the retrieved data.
Key Features of RAG
Accuracy: Responses are grounded in real-world data, reducing errors and hallucinations typical of standalone LLMs.
Relevance: Ensures the output directly addresses user queries by relying on contextually relevant information.
Scalability: Can handle large datasets stored in vector databases for quick, efficient searches.
Some use Cases for RAG
Customer Support: Enhancing chatbot responses with real-time company knowledge or product details.
Content Creation: Generating articles or summaries based on specific datasets.
Research: Assisting with document retrieval and summarization for academic or industry research.
Personalization: Tailoring recommendations or messages based on customer data.
What is a Vector Store?
Vectors are numerical representations that capture the meaning, context, or characteristics of data, often used to represent text, images, or other complex data types. By storing these vectors, vector stores enable efficient similarity search, making it possible to find similar items based on their underlying semantic meaning.
Vector stores are crucial for modern AI applications like recommendation systems, natural language processing, and retrieval-augmented generation (RAG). They provide a powerful tool for understanding and analyzing complex data.
How a Vector Store Works
Data Vectorization: Input data, such as text or images, is transformed into vector embeddings using a model like a large language model (LLM) or an image encoder.
Storage: These embeddings are stored in the vector store, often alongside metadata about the original data.
Search and Retrieval: When a query is made, it is also converted into a vector, and the store performs similarity searches to retrieve the most relevant results based on distance measures like cosine similarity or Euclidean distance.
Use Cases for Vector Stores
Search and Retrieval: Powering AI-driven search engines that find relevant documents, images, or videos based on content rather than keywords.
Recommendation Systems: Suggesting products, movies, or music by comparing user preferences to stored vectors.
Chatbots and Virtual Assistants: Retrieving knowledge or context to answer user queries more accurately.
Content Matching: Identifying similar or related content, such as plagiarism detection or duplicate image recognition.
What is HeatWave?
Oracle’s HeatWave is a fully-managed Oracle Cloud Infrastructure service. It offered different solutions for different workloads:
HeatWave MySQL: built on MySQL, for your transactional needs.
HeatWave Analytics: get real-time analytics on your data.
HeatWave Lakehouse: query data in various formats in object storage
HeatWave AutoML: automate the pipeline to build, train, and explain ML models
HeatWave GenAI: integrated & automated GenAI with in-database LLMs
HeatWave GenAI (from version 9.0.1 onward) is an powerful integrated platform that combines automated in-database large language models (LLMs), vector stores, and scalable vector processing. This simplifies content generation, enabling businesses to harness generative AI without requiring AI expertise, data transfer, or extra costs.
The key benefits of such architecture are:
Effortless LLM Integration: Simple SQL queries unlock AI power.
Seamless Database Integration: No complex external connections.
Simplified Architecture: Easy management and scalability.
Scalable Performance: Handles large datasets and high traffic.
Always Up-to-Date Data: In-database LLMs ensure data freshness.
HeatWave GenAI is available on major cloud platforms: Oracle Cloud Infrastructure (OCI), Amazon Web Services (AWS), and Microsoft Azure.
I’ll leverage HeatWaveLakehouse and GenAI capabilities.
HeatWave Lakehouse Allows to process data stored in OCI Object Storage. Data are retrieved from Object Storage, transformed into a memory-optimized format, and stored in the HeatWave persistence layer. This ensures efficient data processing, with the data loaded into the HeatWave cluster for real-time analytics and insights.
HeatWave Generative AI Enables natural-language interactions with unstructured data, using SQL-based queries for content generation, summarization, and retrieval-augmented generation (RAG). This makes it easy to generate and extract valuable insights from your data with minimal complexity.
Preparation
The data sources that will augmented the LLM are 6 pdf files based on the following Wikipedia pages:
In short, the PDF files (stored in the Object Storage bucket) will be uploaded to the HeatWave cluster, where they will be split into smaller segments, transformed into numerical representations (vector embeddings), and stored in a dedicated vector store table for efficient search and retrieval.
-- Load unstructured data (pdf) to HeatWave Lakehouse.
-- Ingesting Files Using Auto Parallel Load
SET @dl_tables = '[ { "db_name": "Iris_AI",
"tables": [
{"table_name": "IrisAIembeddings", "engine_attribute": { "dialect": {"format": "pdf", "language": "en"},
"file": [ {"par": "https://objectstorage.eu-frankfurt.../Iris_AI/o/"}
]}}] }]';
SET @options = JSON_OBJECT('mode', 'normal');
CALL sys.HEATWAVE_LOAD(CAST(@dl_tables AS JSON), @options);
-- Checks (count embeddings):
SELECT count(*) FROM Iris_AI.IrisAIembeddings;
The vector store table is: Iris_AI.IrisAIembeddings. Its structure is:
SQL >
SHOW CREATE TABLE Iris_AI.IrisAIembeddings\G
*************************** 1. row ***************************
Table: IrisAIembeddings
Create Table: CREATE TABLE `IrisAIembeddings` (
`document_name` varchar(1024) NOT NULL COMMENT 'RAPID_COLUMN=ENCODING=VARLEN',
`metadata` json NOT NULL COMMENT 'RAPID_COLUMN=ENCODING=VARLEN',
`document_id` int unsigned NOT NULL,
`segment_number` int unsigned NOT NULL,
`segment` varchar(1024) NOT NULL COMMENT 'RAPID_COLUMN=ENCODING=VARLEN',
`segment_embedding` vector(384) NOT NULL COMMENT 'RAPID_COLUMN=ENCODING=VARLEN' /*!80021 ENGINE_ATTRIBUTE '{"model": "minilm"}' */,
PRIMARY KEY (`document_id`,`segment_number`)
) ENGINE=Lakehouse DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci SECONDARY_ENGINE=RAPID /*!80021 ENGINE_ATTRIBUTE='{"file": [{"par": "https://objectstorage.eu-frankfurt.../Iris_AI/o/"}], "dialect": {"format": "pdf", "language": "en", "is_strict_mode": false}}' */
Some data:
SELECT document_name, metadata, document_id, segment_number, segment FROM Iris_AI.IrisAIembeddings ORDER BY RAND() LIMIT 2\G
*************************** 1. row ***************************
document_name: https://objectstorage.eu-frankfurt.../Iris_AI/o/Iris_versicolor_Wikipedia.pdf
metadata: {"TITLE": "Iris versicolor - Wikipedia", "PUBLISHER": "Skia/PDF m128", "CREATIONDATE": " 9/14/2024 02:36:16 PM", "REVISIONDATE": " 9/14/2024 02:36:16 PM", "USERDEFINEDPROP": "Creator Mozilla/5.0 (X11; Linux x86_64) AppleWebKit/537.36 (KHTML, like Gecko) HeadlessChrome/128.0.0.0 Safari/537.36"}
document_id: 2
segment_number: 8
segment: The purple iris is the official flower of Kappa Pi International Honorary Art Fraternity.[13]
Iris versicolor 'Blue Flag'
Iris versicolor
Iris versicolor – botanical illustration in American Medicinal Plants, 1887
Saint-Prosper-de-Champlain, Quebec, Canada
Batiscan, Quebec floral emblem, Canada[14]
*************************** 2. row ***************************
document_name: https://objectstorage.eu-frankfurt.../Iris_versicolor_Wikispecies.pdf
metadata: {"TITLE": "Iris versicolor - Wikispecies", "PUBLISHER": "Skia/PDF m128", "CREATIONDATE": " 9/14/2024 02:39:44 PM", "REVISIONDATE": " 9/14/2024 02:39:44 PM", "USERDEFINEDPROP": "Creator Mozilla/5.0 (X11; Linux x86_64) AppleWebKit/537.36 (KHTML, like Gecko) HeadlessChrome/128.0.0.0 Safari/537.36"}
document_id: 3
segment_number: 6
segment: References: BRUMMITT, R.K. 2001. TDWG – World Geographical Scheme for Recording Plant Distributions, 2nd Edition (https://github.com/tdwg/wgsrpd/blob/master/109-488-1 -ED/2nd%20Edition/TDWG_geo2.pdf)
LINNAEUS, C. 1753. Species Plantarum. Tomus I: 39 (http://www.biodiversitylibrary.org/ite m/13829#page/51/mode/1up). Reference page.
Flora of North America Editorial Committee (2002). Flora of North America North of Mexico 26: 1-723. Oxford University Press, New York, Oxford.
You can update your vector store by either drop the table and recreate it or by using the refresh_external_tables clause.
-- Updating the Vector Store using Auto Parallel Load
SET @dl_tables = '[
{ "db_name": "DBName", "tables": [ { "table_name": "VectorStoreTableName" }] }
]';
SET @options = JSON_OBJECT('mode', 'normal', 'refresh_external_tables', true);
CALL sys.HEATWAVE_LOAD(CAST(@dl_tables AS JSON), @options);
Running Retrieval-Augmented Generation
The ML_RAG routine performs retrieval-augmented generation (RAG), but the LLM that you will use must be loaded. You can get the list of the supported LLMs within HeatWave GenAI by querying the ML_SUPPORTED_LLMS table in sys schema:
At the time of writing – December 2024 – the HeatWave in-database LLMs are:
mistral-7b-instruct-v1
llama3-8b-instruct-v1
llama2-7b-v1 is deprecated
the HeatWave in-Database Embedding models are:
all_minilm_l12_v2
multilingual-e5-small
The other models are from OCI Generative AI Service. They can also be used in the HeatWave workflow however their use will incur additional costs.
The comprehensive list of the languages, embedding models, and large language models (LLMs) that HeatWave GenAI supports is available here.
In order to run your RAG, you should load the model, specify some options like the vector store table, the distance metric, the language, and also options like temperature, top_k, top_p, repeat_penalty, etc…), then define your natural language query and finally run the sys.ML_RAG stored procedure:
-- Load the model: ex. Llama 3 8B
CALL sys.ML_MODEL_LOAD("llama3-8b-instruct-v1", NULL);
-- Specify the table for retrieving the vector embeddings, / ['distance_metric', {'COSINE'|'DOT'|'EUCLIDEAN'}]
SET @irisAI_model_info = JSON_OBJECT("vector_store", JSON_ARRAY("Iris_AI.IrisAIembeddings"), "distance_metric",
"COSINE", "model_options", JSON_OBJECT("model_id", "llama3-8b-instruct-v1", "language", "en", "temperature",0));
-- Define your natural language query
SET @query="Describe the appearance of the Iris versicolor flower";
-- Retrieve the prompt and print the output:
CALL sys.ML_RAG(@query, @output, @irisAI_model_info);
HeatWave GenAI currently supports 3 distance metrics: COSINE, DOT & EUCLIDEAN. Other options are available here.
The result of the query is stored in JSON format in the variable @output (in this example). This result includes the answer and also citations referring to segments from the loaded documents (my PDF files). In MySQL, when dealing with JSON documents, it is usually important to know the following functions: JSON_PRETTY, JSON_UNQUOTE & JSON_EXTRACT
-- Print the outputs
SELECT JSON_PRETTY(@output)\G
*************************** 1. row ***************************
JSON_PRETTY(@output): {
"text": "\n\nAccording to the provided context, the well-developed blue flower of Iris versicolor has 6 petals and sepals that are spread out nearly flat. The longer sepals are hairless and have a greenish-yellow blotch at their base.",
"citations": [
{
"segment": "Iris versicolor is a flowering herbaceous perennial plant, growing 10–80 cm (4–31 in) high. It tends to form large clumps from thick, creeping rhizomes. The unwinged, erect stems generally have basal leaves that are more than 1 cm (1?2 in) wide. Leaves are folded on the midribs so that they form an overlapping flat fan. The well developed blue flower has 6 petals and sepals spread out nearly flat and have two forms. The longer sepals are hairless and have a greenish-yellow blotch at their base.",
"distance": 0.1099172830581665,
"document_name": "https://objectstorage.eu-frankfurt.../Iris_versicolor_Wikipedia.pdf"
},
{
"segment": "Iris versicolor\nIris versicolor\nTaxonavigation: Asparagales \nClassification System: APG IV Superregnum: Eukaryota Regnum: Plantae Cladus: Angiosperms Cladus: Monocots Ordo: Asparagales\nFamilia: Iridaceae Subfamilia: Iridoideae Tribus: Irideae Genus: Iris Subgenus: I. subg. Limniris Sectio: I. sect. Limniris Series: Iris ser. Laevigatae Species: Iris versicolor\nIris versicolor L., Sp. Pl. 1: 39 (1753).",
"distance": 0.2635544538497925,
"document_name": "https://objectstorage.eu-frankfurt.../Iris_versicolor_Wikispecies.pdf"
},
{
"segment": "Homotypic Iris versicolor f. virginica (L.) VOSS, Vilm. Blumengaertn., ed. 3. 1: 978. 1895. Iris versicolor var. virginica (L.) BAKER, J. Linn. Soc., Bot. 16: 141 (1877). Xiphion virginicum (L.) ALEF., Bot. Zeitung (Berlin) 21: 297 (1863).\nHeterotypic Iris albilinea ALEXANDER SMALL & ALEXANDER, Contr. New York Bot. Gard. 327: 349 (1931).\nTaxonavigation\nName\nSynonyms",
"distance": 0.27319884300231934,
"document_name": "https://objectstorage.eu-frankfurt.../Iris_virginica_Wikispecies.pdf"
}
],
"vector_store": [
"Iris_AI.IrisAIembeddings"
]
}
To display only the answer:
-- Get the answer from the generated JSON result.
SELECT JSON_UNQUOTE(JSON_EXTRACT(@output, '$.text')) AS answer\G
*************************** 1. row ***************************
answer:
According to the provided context, the well-developed blue flower of Iris versicolor has 6 petals and sepals that are spread out nearly flat. The longer sepals are hairless and have a greenish-yellow blotch at their base.
To display only the citations:
-- You can also get the citations referring to segments from the loaded documents.
SELECT JSON_UNQUOTE(JSON_EXTRACT(@output, '$.citations')) AS citations\G
*************************** 1. row ***************************
citations: [{"segment": "Iris versicolor is a flowering herbaceous perennial plant, growing 10–80 cm (4–31 in) high. It tends to form large clumps from thick, creeping rhizomes. The unwinged, erect stems generally have basal leaves that are more than 1 cm (1?2 in) wide. Leaves are folded on the midribs so that they form an overlapping flat fan. The well developed blue flower has 6 petals and sepals spread out nearly flat and have two forms. The longer sepals are hairless and have a greenish-yellow blotch at their base.", "distance": 0.1099172830581665, "document_name": "https://objectstorage.eu-frankfurt.../Iris_versicolor_Wikipedia.pdf"}, {"segment": "Iris versicolor\nIris versicolor\nTaxonavigation: Asparagales \nClassification System: APG IV Superregnum: Eukaryota Regnum: Plantae Cladus: Angiosperms Cladus: Monocots Ordo: Asparagales\nFamilia: Iridaceae Subfamilia: Iridoideae Tribus: Irideae Genus: Iris Subgenus: I. subg. Limniris Sectio: I. sect. Limniris Series: Iris ser. Laevigatae Species: Iris versicolor\nIris versicolor L., Sp. Pl. 1: 39 (1753).", "distance": 0.2635544538497925, "document_name": "https://objectstorage.eu-frankfurt.../Iris_versicolor_Wikispecies.pdf"}, {"segment": "Homotypic Iris versicolor f. virginica (L.) VOSS, Vilm. Blumengaertn., ed. 3. 1: 978. 1895. Iris versicolor var. virginica (L.) BAKER, J. Linn. Soc., Bot. 16: 141 (1877). Xiphion virginicum (L.) ALEF., Bot. Zeitung (Berlin) 21: 297 (1863).\nHeterotypic Iris albilinea ALEXANDER SMALL & ALEXANDER, Contr. New York Bot. Gard. 327: 349 (1931).\nTaxonavigation\nName\nSynonyms", "distance": 0.27319884300231934, "document_name": "https://objectstorage.eu-frankfurt.../Iris_virginica_Wikispecies.pdf"}]
Et voilà! As simple as that.
Create your chatbot with HeatWave Chat
HeatWave Chat is an advanced conversational agent powered by LLMs. It utilizes RAG and a vector store to deliver accurate, data-driven insights. By simulating human-like conversations, it provides users with a dynamic and informative experience, offering deep knowledge and meaningful insights in a conversational format. HeatWave Chat is designed to understand user inputs and respond naturally. It enhances text generation by leveraging chat history for follow-up questions and utilizes vector search to access knowledge stored in its built-in vector store. With all communication and processing securely managed within the HeatWave service, HeatWave Chat delivers fast and reliable responses.
In order to run your chat, you should load the model, specify some chat options like the vector store table, the distance metric, the language, and also options like temperature, top_k, top_p, repeat_penalty, etc…), then define your natural language query and finally run the sys.HEATWAVE_CHAT stored procedure:
-- Load the model: ex. Mistral 7B
CALL sys.ML_MODEL_LOAD("mistral-7b-instruct-v1", NULL);
-- Setup HeatWave Chat options
SET @chat_options = JSON_OBJECT("tables", JSON_ARRAY(JSON_OBJECT("schema_name", "`Iris_AI`",
"table_name","`IrisAIembeddings`")), "model_options", JSON_OBJECT("model_id", "mistral-7b-instruct-v1", "language",
"en", "temperature", 0));
-- Query in natural language (en, fr, de, es, hi...)
SET @question = "Where is Iris setosa native to?";
-- Add your query to HeatWave Chat
CALL sys.HEATWAVE_CHAT(@question);
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| response |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Iris setosa is native to a circum-arctic distribution ranging from coastal Aleutian islands, Alaska, Maine in USA, within Canada (including British Columbia, Newfoundland, Quebec, and the Yukon), within Russia (including near to the lower Lena River, Siberia), northeastern Asia, China (including Manchuria and east Jilin), Korea and southwards to Japan (including Honshu and Hokkaido). |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
-- Another query in natural language (en, fr, de, es, hi...)
SET @question = "How is Iris virginica used in Seminole culture?";
-- Add your query to HeatWave Chat
CALL sys.HEATWAVE_CHAT(@question);
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| response |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Answer: Iris virginica has a cultural significance in Seminole culture as it is one of the Iris species used to treat "shock following alligator-bite". However, there is limited information available on how Iris virginica is specifically used in Seminole culture for this purpose. |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
The HEATWAVE_CHAT routine leverages the @chat_options variable as a dynamic parameter store. This variable is populated with additional context and configuration settings, which are subsequently utilized to refine the response generation process.
SELECT JSON_PRETTY(@chat_options)\G
*************************** 1. row ***************************
JSON_PRETTY(@chat_options): {
"tables": [
{
"table_name": "`IrisAIembeddings`",
"schema_name": "`Iris_AI`"
}
],
"response": "\n\nAccording to the provided context, it is mentioned that Iris virginica may be one of the Iris species used by the Seminole tribe to treat \"shock following alligator-bite\". This suggests that the plant has been traditionally used by the Seminole people for medicinal purposes in treating a specific condition related to alligator bites.",
"documents": [
{
"id": "https://objectstorage.eu-frankfurt.../Iris_virginica_Wikipedia.pdf",
"title": "Iris_virginica_Wikipedia.pdf",
"segment": "p://herb.umd.umich.edu/herb/search.pl?searchstring=Iris+virginica) 12. Hamel, Paul B. and Mary U. Chiltoskey 1975 Cherokee Plants and Their Uses -- A 400 Year History. Sylva, N.C. Herald Publishing Co. (p. 41) 13. \"USDA Plants Database\" (https://plants.usda.gov/home/plantProfile?symbol=IRVI).\nplants.usda.gov.\nKemper Center for Home Gardening.org: Iris virginica (Virginia iris) (http://www.missouribota nicalgarden.org/PlantFinder/PlantFinderDetails.aspx?kempercode=y390)",
"distance": 0.2979528307914734
},
{
"id": "https://objectstorage.eu-frankfurt.../Iris_virginica_Wikipedia.pdf",
"title": "Iris_virginica_Wikipedia.pdf",
"segment": "It may be one of the Iris species used by the Seminole to treat \"shock following alligator-bite\".[13]\nIris virginica is one of three iris species in Ronald Fisher's Iris flower data set.",
"distance": 0.3149242401123047
},
{
"id": "https://objectstorage.eu-frankfurt.../Iris_virginica_Wikispecies.pdf",
"title": "Iris_virginica_Wikispecies.pdf",
"segment": "Retrieved from \"https://species.wikimedia.org/w/index.php?title=Iris_virginica&oldid=8735713\"\nThis page was last edited on 5 April 2022, at 21:56.\nText is available under the Creative Commons Attribution-ShareAlike License; additional terms may apply. By using this site, you agree to the Terms of Use and Privacy Policy.\nVernacular names",
"distance": 0.3498268127441406
}
],
"chat_history": [
{
"user_message": "Where is Iris setosa native to?",
"chat_query_id": "d2dd2c8e-b3e8-11ef-bdb3-020017081917",
"chat_bot_message": " Iris setosa is native to a circum-arctic distribution ranging from coastal Aleutian islands, Alaska, Maine in USA, within Canada (including British Columbia, Newfoundland, Quebec, and the Yukon), within Russia (including near to the lower Lena River, Siberia), northeastern Asia, China (including Manchuria and east Jilin), Korea and southwards to Japan (including Honshu and Hokkaido)."
},
{
"user_message": "How is Iris virginica used in Seminole culture?",
"chat_query_id": "7d4ea547-b3e9-11ef-bdb3-020017081917",
"chat_bot_message": " Answer: Iris virginica has a cultural significance in Seminole culture as it is one of the Iris species used to treat \"shock following alligator-bite\". However, there is limited information available on how Iris virginica is specifically used in Seminole culture for this purpose."
}
],
"model_options": {
"language": "en",
"model_id": "mistral-7b-instruct-v1",
"temperature": 0
},
"request_completed": true
}
The list of all the parameters that you can set in the @chat_options variable is available here and here.
You can modify chat parameters using the @chat_options variable, without resetting the chat session:
-- Modifying a chat parameter, here the model, without resetting the chat session:
-- Load the new model if needed
CALL sys.ML_MODEL_LOAD("llama3-8b-instruct-v1", NULL);
SET @chat_options = JSON_SET(@chat_options, '$.model_options.model_id', "llama3-8b-instruct-v1");
or by resetting the chat session:
-- Modifying a chat parameter, here the model, and resetting the chat session:
-- Load the new model if needed
CALL sys.ML_MODEL_LOAD("llama3-8b-instruct-v1", NULL);
SET @chat_options = JSON_OBJECT("model_options", JSON_OBJECT("model_id", "llama3-8b-instruct-v1", "language", "en", "temperature", 0));
As you have seen, HeatWave GenAI simplifies LLM integration with SQL queries, streamlines database connections, and offers a user-friendly architecture that lets you quickly develop applications.
That’s all for today—it’s time to wrap things up! 🙂
Peroration
HeatWave GenAI offers a seamless and powerful platform for integrating generative AI capabilities into your data workflows. By combining advanced features like retrieval-augmented generation (RAG), an integrated vector store, and user-friendly SQL-based interfaces, HeatWave GenAI empowers users to unlock insights from unstructured data with minimal complexity. Its efficient and secure in-database architecture ensures real-time performance, scalability, and always up-to-date data access.
Whether you aim to streamline content generation, enhance search and retrieval tasks, or build interactive chatbots, HeatWave GenAI provides all the tools you need within a single ecosystem. By leveraging the tutorials and techniques shared in this guide, you can fully harness the capabilities of HeatWave GenAI to transform how you interact with data and elevate your applications to new heights.
Key Features, Unmatched Benefits Here’s what sets HeatWave GenAI apart:
Effortless LLM Integration: Harness the power of large language models with simple SQL queries.
Seamless Database Integration: No need for intricate external connections, simplifying your workflow.
Simplified Architecture: Easy to manage and scale, ensuring a smooth user experience.
Always Up-to-Date Data: In-database LLMs guarantee data freshness for accurate results.
So, you’ve probably heard about AI getting “smarter” by the day. Well, one cool thing it can do now is read between the lines of what people say. It’s like having a mind-reading machine for text! This new AI tech, called generative AI (or GenAI), can dive deep into what people are saying and tell us if they’re feeling positive, negative, or neutral. Let’s see how HeatWave GenAI, can help you to enhance your understanding of customer sentiment, improve decision-making, and drive business success.
What is AI sentiment Analysis?
You can find a more formal and complete definition on Wikipedia, but in few words, it is like having a computer that can understand human emotions. This technology can analyze text data and thus dive deep into what people are saying and tell us if they’re happy, sad, or even mad.
By understanding the sentiment behind words, AI can help businesses gauge customer satisfaction, track brand reputation, and even predict market trends. It’s a powerful tool that has a wide range of applications in fields like marketing, social media, and customer service.
What is HeatWave?
HeatWave is a fully-managed Oracle Cloud Infrastructure service. It offered different solutions for different workloads:
HeatWave MySQL: built on MySQL, for your transactional needs.
HeatWave Analytics: get real-time analytics on your data.
HeatWave Lakehouse: query data in various formats in object storage
HeatWave AutoML: automate the pipeline to build, train, and explain ML models
HeatWave GenAI: integrated & automated GenAI with in-database LLMs
Oracle’s HeatWave GenAI (starting with version 9.0.1) is at the forefront of this revolution, offering an integrated platform that combines in-database large language models (LLMs), vector stores, and scale-out vector processing to streamline content generation.
The key benefits of such architecture are:
Effortless LLM Integration: Simple SQL queries unlock AI power.
Seamless Database Integration: No complex external connections.
Simplified Architecture: Easy management and scalability.
Scalable Performance: Handles large datasets and high traffic.
Always Up-to-Date Data: In-database LLMs ensure data freshness.
In this article I’m using HeatWave 9.0.1.
For the purpose of this article, let’s consider a scenario where you’re a mobile phone manufacturer. In today’s digital age, it’s crucial to keep a pulse on customer sentiment. Your company actively monitors various social networks to stay informed about what customers are saying about your products. For example, short messages like:
Just got my hands on the latest mobile phone and I’m loving it! Thanks for creating such an awesome product!
A mobile phone that prioritizes functionality and reliability over flashy features.
Why does your mobile phone company insist on selling devices that are constantly glitching and breaking? Do better!
Use the ML_MODEL_LOAD stored procedure: CALL sys.ML_MODEL_LOAD(‘<LLM>’, NULL); The model needs to be loaded only once before generating text. It remains accessible within HeatWave as long as the service is running. This means you don’t have to reload the model for each text generation.
At the time of writing (beginning of September 2024) the available models are Mistral 7B (mistral-7b-instruct-v1) and Llama 2 (llama2-7b-v1).
Mistral is loaded. Now you can think about defining your prompt.
Define your prompt in natural language
To make it more convenient, let’s use some variables. The sort messages above are now:
mysql>
SET @customer_msgA="Just got my hands on the latest mobile phone and I'm loving it! Thanks for creating such an awesome product!" ;
SET @customer_msgB="A mobile phone that prioritizes functionality and reliability over flashy features." ;
SET @customer_msgC="Why does your mobile phone company insist on selling devices that are constantly glitching and breaking? Do better!" ;
The prompt contains a message and the inference instructions. Let’s start with a very simple instruction text, something like: What is the feeling from the text above?
mysql>
SET @instructions=" What is the feeling from the text above?";
SET @prompt=CONCAT(@customer_msgA, @instructions);
The final prompt is the concatenation of 2 variables: @customer_msg{X} and @instructions.
In order to have the optimal result, maybe you’ll need to do some fine tuning to set up the inference.
Inference Setting
I use generation as a task and the Mistral model and I put temperature, top-k and top-p to zero because I do not want any creativity from the model 🙂 .
One way to display the result is to use the following query:
SELECT Answer->>"$.text" AS "Answer" FROM (SELECT sys.ML_GENERATE(@prompt, @inferenceSetup) AS Answer ) AS dt;
Result with: SET @customer_msgA=”Just got my hands on the latest mobile phone and I’m loving it! Thanks for creating such an awesome product!” ;
mysql>
/* msg: "Just got my hands on the latest mobile phone and I'm loving it! Thanks for creating such an awesome product!" */
SELECT Answer->>"$.text" AS "Answer" FROM (SELECT sys.ML_GENERATE(@prompt, @inferenceSetup) AS Answer ) AS dt;
+---------------------------------------------------------+
| Answer |
+---------------------------------------------------------+
| The feeling expressed in the text is happiness or joy. |
+---------------------------------------------------------+
With SET @customer_msgB=”A mobile phone that prioritizes functionality and reliability over flashy features.” ;
mysql>
/* msg: "A mobile phone that prioritizes functionality and reliability over flashy features." */
SELECT Answer->>"$.text" AS "Answer" FROM (SELECT sys.ML_GENERATE(@prompt, @inferenceSetup) AS Answer ) AS dt\G
*************************** 1. row ***************************
Answer: The feeling conveyed in the text is one of practicality and pragmatism, with a focus on the importance of functionality and reliability in a mobile phone.
With SET @customer_msgC=”Why does your mobile phone company insist on selling devices that are constantly glitching and breaking? Do better!” ;
mysql>
/* msg: "Why does your mobile phone company insist on selling devices that are constantly glitching and breaking? Do better!" */
SELECT Answer->>"$.text" AS "Answer" FROM (SELECT sys.ML_GENERATE(@prompt, @inferenceSetup) AS Answer ) AS dt\G
*************************** 1. row ***************************
Answer: The feeling conveyed in the text above is frustration and disappointment.
Amazing! The model provide a summary of the sentiment expressed in the message.
But what if we want to use this feature more programmatically? Well we need to improve our prompt.
Sentiment Analysis in Code
Basically we can ask the LLM to generate the result in a specific format, other than raw text like JSON, CSV, … or an integer.
Let’s say we have the following code:
Positive feeling: 1.
Negative feeling: -1
Neutral feeling or unknown: 0
The goal is to automatically identify problematic messages — those that express non positive sentiments — and then take action.
To do this, we only need to rewrite our prompt, improving the instruction part. For example we can have something like: What is the feeling from the text above? if it is positive write 1. If it is negative write -1. if it is neutral or if you don’t know write 0. Regarding to your analysis, write only -1 or 0 or 1
Still a basic prompt, far from being optimal, but good enough to keep this article simple and most important, to do the job.
mysql>
SET @instructions=" What is the feeling from the text above? if it is positive write 1. If it is negative write -1. if it is neutral or if you don't know write 0. Regarding to your analysis, write only -1 or 0 or 1";
All the other variables are the same than the previous use case. Let’s see the result:
Result with: SET @customer_msgA=”Just got my hands on the latest mobile phone and I’m loving it! Thanks for creating such an awesome product!” ;
mysql>
/* msg: "Just got my hands on the latest mobile phone and I'm loving it! Thanks for creating such an awesome product!" */
SELECT Answer->>"$.text" AS "Answer" FROM (SELECT sys.ML_GENERATE(@prompt, @inferenceSetup) AS Answer ) AS dt;
+--------+
| Answer |
+--------+
| 1 |
+--------+
With SET @customer_msgB=”A mobile phone that prioritizes functionality and reliability over flashy features.” ;
mysql>
/* msg: "A mobile phone that prioritizes functionality and reliability over flashy features." */
SELECT Answer->>"$.text" AS "Answer" FROM (SELECT sys.ML_GENERATE(@prompt, @inferenceSetup) AS Answer ) AS dt;
+--------+
| Answer |
+--------+
| 0 |
+--------+
With SET @customer_msgC=”Why does your mobile phone company insist on selling devices that are constantly glitching and breaking? Do better!” ;
mysql>
/* msg: "Why does your mobile phone company insist on selling devices that are constantly glitching and breaking? Do better!" */
SELECT Answer->>"$.text" AS "Answer" FROM (SELECT sys.ML_GENERATE(@prompt, @inferenceSetup) AS Answer ) AS dt;
+--------+
| Answer |
+--------+
| -1 |
+--------+
Awesome! The model produces results based on the format chosen and needed in our code. It could have been a JSON format for example and the logic would have been the same.
With such output you can easily integrate HeatWave in your data pipeline.
Sentiment Analysis into your data workflow with HeatWave
Imagine we store our customer messages in a (MySQL) table. A possible workflow involves regularly scanning this table for negative feedback. Once identified, these messages can be referenced in ( or be moved to) a separate table. This new table can then be used to populate the customer service team’s dashboard. By tracking unsatisfied customers in this way, the team can work to improve overall customer satisfaction.
A simplified data architecture could be:
mysql>
CREATE SCHEMA sentimentAnalysis;
CREATE TABLE `sentimentAnalysis`.`tweet` (
`tweet_id` int unsigned NOT NULL AUTO_INCREMENT,
`customer_msg` varchar(4000) DEFAULT NULL,
PRIMARY KEY (`tweet_id`)
);
CREATE TABLE `sentimentAnalysis`.`tweet_to_review` (
`tweet_to_review_id` int unsigned NOT NULL AUTO_INCREMENT,
`tweet_id_to_check` int unsigned NOT NULL,
`analysis_result` tinyint NOT NULL,
PRIMARY KEY (`tweet_to_review_id`)
);
Table tweet contains the customer messages and a message ID.. The problematic messages (ie status 0 and -1) are referenced into table tweet_to_review, using the message ID. We also store the result of the sentiment analysis (0 or -1). Support may prefer to focus first on messages that are definitely negative (status -1).
mysql>
INSERT `sentimentAnalysis`.`tweet` (customer_msg) VALUES("Just got my hands on the latest mobile phone and I'm loving it! Thanks for creating such an awesome product!");
INSERT `sentimentAnalysis`.`tweet` (customer_msg) VALUES("Loving the features on my new mobile phone! It's like a whole new world in the palm of my hand.");
INSERT `sentimentAnalysis`.`tweet` (customer_msg) VALUES("Your mobile phone company is a joke. Can't believe I wasted my hard-earned money on your crappy products.");
INSERT `sentimentAnalysis`.`tweet` (customer_msg) VALUES("Hey, just wanted to say how much I love your product! It's been a game changer for me.");
INSERT `sentimentAnalysis`.`tweet` (customer_msg) VALUES("Recently upgraded my old mobile phone to something newer - let's see how it goes");
INSERT `sentimentAnalysis`.`tweet` (customer_msg) VALUES("A mobile phone that prioritizes functionality and reliability over flashy features.");
INSERT `sentimentAnalysis`.`tweet` (customer_msg) VALUES("Why does your mobile phone company insist on selling devices that are constantly glitching and breaking? Do better!");
INSERT `sentimentAnalysis`.`tweet` (customer_msg) VALUES("Shoutout for making such an awesome product. I couldn't imagine my life without it!");
INSERT `sentimentAnalysis`.`tweet` (customer_msg) VALUES("Just another day dealing with the incompetence of my mobile phone company. Seriously considering switching to a different provider.");
INSERT `sentimentAnalysis`.`tweet` (customer_msg) VALUES("For those who want a device that just works.");
INSERT `sentimentAnalysis`.`tweet` (customer_msg) VALUES("Big thanks for creating a top-notch mobile phone. You guys never disappoint!");
INSERT `sentimentAnalysis`.`tweet` (customer_msg) VALUES("Just got my hands on a new mobile phone and I'm excited to see what it can do!");
Some data inserted in tweet table. Most of these data were generated using AI Tweet Generator.
mysql>
SELECT * FROM `sentimentAnalysis`.`tweet` LIMIT 5\G
*************************** 1. row ***************************
tweet_id: 1
customer_msg: Just got my hands on the latest mobile phone and I'm loving it! Thanks for creating such an awesome product!
*************************** 2. row ***************************
tweet_id: 2
customer_msg: Loving the features on my new mobile phone! It's like a whole new world in the palm of my hand.
*************************** 3. row ***************************
tweet_id: 3
customer_msg: Your mobile phone company is a joke. Can't believe I wasted my hard-earned money on your crappy products.
*************************** 4. row ***************************
tweet_id: 4
customer_msg: Hey, just wanted to say how much I love your product! It's been a game changer for me.
*************************** 5. row ***************************
tweet_id: 5
customer_msg: Recently upgraded my old mobile phone to something newer - let's see how it goes
Sample of the data.
The workflow of our data pipeline is quite simple:
Get the tweet content from the source (table tweet)
Do the sentiment analysis using HeatWave GenAI
Write problematic tweets (status <> 0) into the target (table tweet_to_review) for further processing
It can be done with your favourite programing language. It can also be done on the database side using HeatWave MySQL stored routines capabilities. If you are a javascript fan, you can now write your stored routine in that language since HeatWave MySQL 9.0: JavaScript Stored Programs
That said, for the love of the (SQL) art, let’s try something in SQL. But before diving into the code, let’s break down the 3 steps above.
Get the tweet content from the source (table tweet)
In order to have a flexible stored procedure, the input parameters are:
target column result name: VARCHAR (analysis_result)
To get the tweet content from the source the query is:
SELECT <source column message> FROM <source schema name>.<source table name> WHERE <source column primary key name>=<tweet ID> INTO @customer_msg
Do the sentiment analysis using HeatWave GenAI
We’ve already seen this part:
Prompt instruction: instructions=” What is the feeling from the text above? if it is positive write 1. If it is negative write -1. if it is neutral or if you don’t know write 0. Regarding to your analysis, write only -1 or 0 or 1″;
Add the tweet to the prompt: prompt=CONCAT(‘”‘, @customer_msg, instructions, ‘”‘);
And the Inference: SELECT Answer->>”$.text” AS “Answer” FROM (SELECT sys.ML_GENERATE(prompt, inferenceSetup) AS Answer ) AS dt INTO @answer ;
Write problematic tweets into the target for further processing
And finally store the ID of the problematic message (tweet ID) and its status (-1 or 0):
INSERT INTO <target schema name>.<target table name> (<target column message ID name>, <target column result name>) VALUES (<tweetID>, <@answer>)
How it works?
The stored routine is called: genai_sentimentAnalysis. In the following examples, it is run with tweet ID: 1, 3 and 10:
mysql>
CALL genai_sentimentAnalysis("sentimentAnalysis", "tweet", "customer_msg", "tweet_id", 1, "sentimentAnalysis", "tweet_to_review", "analysis_result");
+-----------------------------------------------+
| INFO |
+-----------------------------------------------+
| Tweet number 1 as been analyzed as POSITIVE |
+-----------------------------------------------+
1 row in set (2.4341 sec)
Query OK, 0 rows affected (2.4341 sec)
CALL genai_sentimentAnalysis("sentimentAnalysis", "tweet", "customer_msg", "tweet_id", 3, "sentimentAnalysis", "tweet_to_review", "analysis_result");
+-----------------------------------------------+
| INFO |
+-----------------------------------------------+
| Tweet number 3 as been analyzed as NEGATIVE |
+-----------------------------------------------+
1 row in set (1.9574 sec)
Query OK, 0 rows affected (1.9574 sec)
CALL genai_sentimentAnalysis("sentimentAnalysis", "tweet", "customer_msg", "tweet_id", 10, "sentimentAnalysis", "tweet_to_review", "analysis_result");
+-----------------------------------------------+
| INFO |
+-----------------------------------------------+
| Tweet number 10 as been analyzed as NEUTRAL |
+-----------------------------------------------+
1 row in set (1.8339 sec)
Query OK, 0 rows affected (1.8339 sec)
The stored procedure output is a log that is displayed on the console. The first call generates 1 (tweet ID 1 is positive), the second -1 (tweet ID 3 is negative) and the third 0 (tweet ID 10 is neutral).
Only , tweet 3 and 10 are selected for the customer service team.
For their dashboard you can provide more details with a query like:
mysql>
SELECT tweet_id_to_check, analysis_result, customer_msg
FROM tweet_to_review INNER JOIN tweet ON tweet_id=tweet_id_to_check
ORDER BY analysis_result\G
*************************** 1. row ***************************
tweet_id_to_check: 3
analysis_result: -1
customer_msg: Your mobile phone company is a joke. Can't believe I wasted my hard-earned money on your crappy products.
*************************** 2. row ***************************
tweet_id_to_check: 10
analysis_result: 0
customer_msg: For those who want a device that just works.
Show me the code
Here the stored procedure used. As a disclaimer, this code is not meant to run in production, it is for demonstration purpose only 🙂
mysql>
DELIMITER $$
CREATE DEFINER=`MyHeatwaveUser`@`%` PROCEDURE `genai_sentimentAnalysis`(IN _sourceSchema VARCHAR(64), IN _sourceTable VARCHAR(64), IN _sourceColMsg VARCHAR(64), IN _sourceColPK VARCHAR(64), IN _tweetID INTEGER UNSIGNED, IN _targetSchema VARCHAR(64), IN _targetTable VARCHAR(64), IN _targetColResult VARCHAR(64))
SQL SECURITY INVOKER
BEGIN
DECLARE sourceTableName, targetTableName, targetCols VARCHAR(129);
DECLARE instructions, inferenceSetup VARCHAR(512);
DECLARE prompt VARCHAR(5000);
DECLARE rep VARCHAR(100);
/* Get the tweet content from source table */
SET sourceTableName = CONCAT("`",_sourceSchema, "`.`", _sourceTable, "`");
SET @txtStmt = CONCAT("SELECT ", _sourceColMsg, " INTO @customer_msg FROM ", sourceTableName, " WHERE ", _sourceColPK, " = ", _tweetID);
PREPARE getTweet FROM @txtStmt;
EXECUTE getTweet;
DEALLOCATE PREPARE getTweet;
/* Sentiment analysis using HeatWave GenAI */
-- Prompt instruction
SET instructions=" What is the feeling from the text above? if it is positive write 1. If it is negative write -1. if it is neutral or if you don't know write 0. Regarding to your analysis, write only -1 or 0 or 1";
-- Add the tweet to the prompt
SET prompt=CONCAT('"', @customer_msg, instructions, '"');
-- Inference setup
SET inferenceSetup = '\'{"task": "generation", "temperature": 0, "top_k": 0, "top_p": 0, "model_id": "mistral-7b-instruct-v1"}\'';
-- Inference: if positive retunr 1 / if negative return -1 / if neutral or unknown return 0
SET @txtStmt = CONCAT(' SELECT Answer->>"$.text" AS Answer FROM (SELECT sys.ML_GENERATE(', prompt, ' , ', inferenceSetup, ' ) AS Answer ) AS dt INTO @answer');
PREPARE analysis FROM @txtStmt;
EXECUTE analysis;
DEALLOCATE PREPARE analysis;
/* Write problematic tweets (status <> 0) into the target table for further processing */
SET rep = CONCAT('Tweet number ', _tweetID, ' as been analyzed as ');
IF @answer < 1 THEN
BEGIN
SET targetTableName = CONCAT("`",_targetSchema, "`.`", _targetTable, "`");
SET targetCols = CONCAT("(tweet_id_to_check, ", _targetColResult,")");
SET @txtStmt = CONCAT('INSERT INTO ', targetTableName, targetCols, ' VALUES (', _tweetID, ', ', @answer, ')');
PREPARE ins FROM @txtStmt;
EXECUTE ins;
DEALLOCATE PREPARE ins;
/* Logging */
CASE @answer
WHEN 0 THEN SELECT CONCAT(rep, ' NEUTRAL ') AS 'INFO';
WHEN -1 THEN SELECT CONCAT(rep, ' NEGATIVE ') AS 'INFO';
END CASE;
END;
ELSE SELECT CONCAT(rep, ' POSITIVE ') AS 'INFO';
END IF;
END$$
DELIMITER ;
As you see, all the logic is coded inside the procedure. It would probably make sense to break the different tasks to separate procedures/functions.
Areas for improvement
As you can imagine, the prompt is crucial. While I’ve tried kept things simple for this tutorial, effective prompt engineering is essential for getting the desired results from an LLM.
Remember to review the LLM’s output carefully. Even though we expect an integer, the model might generate something unexpected (not handle in the stored procedure).
As of {HeatWave} MySQL 9.0.0, you can now write stored routines in JavaScript. However, stored procedures aren’t mandatory. Choose the language that best suits your needs and start experimenting! 🙂
Peroration
In this article, we’ve explored how to leverage Oracle HeatWave GenAI to perform sentiment analysis on text data. By integrating in-database large language models HeatWave offers an easy, streamlined and efficient solution for understanding customer sentiment.
We’ve covered the essential steps involved, from loading the LLM into HeatWave memory to crafting effective prompts and analyzing the model’s output. By following these guidelines, you can easily incorporate sentiment analysis into your data pipeline and gain valuable insights into customer feedback.
Remember that effective prompt engineering is key to obtaining accurate and meaningful results. Experiment with different prompts and refine your approach to achieve the desired outcomes.
With HeatWave GenAI, you have a powerful tool at your disposal for extracting valuable information from text data. By harnessing its capabilities, you can enhance your understanding of customer sentiment, improve decision-making, and drive business success.
HeatWave offered different solutions for your different workload:
HeatWave MySQL: built on MySQL, for your transactional needs.
HeatWave Analytics: get real-time analytics on your data.
HeatWave Lakehouse: query data in various formats in object storage
HeatWave AutoML: automate the pipeline to build, train, and explain ML models
HeatWave GenAI: integrated & automated GenAI with in-database LLMs
In this article you’ll experience the power of In-database translation with HeatWave GenAI. I am using HeatWave 9.0.1.
As demonstrated in my previous article, HeatWave GenAI: Your AI-Powered Content Creation Partner, HeatWave GenAI streamlines content generation by seamlessly integrating LLMs into the database. This innovative approach eliminates the complexities of external connections, allowing for effortless AI integration via straightforward SQL queries.
While HeatWave GenAI excels at generating English text, its capabilities extend to translation as well. Let’s explore how we can effectively translate English content into French using this powerful tool.
Load the LLM in HeatWave memory
Use the ML_MODEL_LOAD stored procedure: CALL sys.ML_MODEL_LOAD(‘<LLM>’, NULL); The model needs to be loaded only once before generating text. It remains accessible within HeatWave as long as the service is running. This means you don’t have to reload the model for each text generation.
At the time of writing (August 2024) the available models are Mistral 7B (mistral-7b-instruct-v1) and Llama 2 (llama2-7b-v1).
-- Load Mistral 7B model
mysql>
CALL sys.ML_MODEL_LOAD('mistral-7b-instruct-v1', NULL);
Define your prompt in natural language
We obviously need a text to translate:
mysql>
-- Text for Translation
SET @ENtext = "Dracula is a gothic horror novel by Bram Stoker, published on 26 May 1897. An epistolary novel, the narrative is related through letters, diary entries, and newspaper articles. It has no single protagonist and opens with solicitor Jonathan Harker taking a business trip to stay at the castle of a Transylvanian nobleman, Count Dracula. Harker escapes the castle after discovering that Dracula is a vampire, and the Count moves to England and plagues the seaside town of Whitby. A small group, led by Abraham Van Helsing, investigate, hunt and kill Dracula.";
With the text ready for translation, we need to provide clear instructions to the model about the desired output. Actually, the most difficult part is here. The quality of the translation will be highly impacted by the quality of your prompt.
For now, let’s try a very simple prompt like: “Translate the following text into French, ensuring natural and grammatically correct phrasing“. It will be concatenated (CONCAT) with the provided English text :
mysql>
-- Enhanced prompt
SET @finalPrompt = CONCAT("Translate the following text into French, ensuring natural and grammatically correct phrasing: ", @ENtext);
Inference setting
Here, you select the desired task, choose the appropriate model, and fine-tune parameters to influence the output.
For simplicity, this example uses default parameters. A more complex example is provided below.
Generate the translation using HeatWave GenAI
And finally, send the enhanced prompt to the LLM using the ML_GENERATE function. The second parameter of this function is a JSON object that allows you to specify the task, the LLM, and the tuning options.
mysql>
-- Translation
SELECT sys.ML_GENERATE(@finalPrompt, @inferenceSetup)\G
*************************** 1. row ***************************
sys.ML_GENERATE(@finalPrompt, @inferenceSetup): {"text": " Réponse : Le roman gothique horreur de Bram Stoker, intitulé « Dracula », a été publié le 26 mai 1897. C'est un roman épistolaire dont la narration se fait par les lettres, les entrées dans le journal et les articles de presse. Il n'a pas d'héroïne principale et commence avec l'avocat Jonathan Harker qui effectue une visite d'affaires au château d'un nobleman transylvanien, le comte Dracula. Harker s'évade du château après avoir découvert que Dracula est un vampire, et le comte se rend en Angleterre et pille la ville côtière de Whitby. Un petit groupe, dirigé par Abraham Van Helsing, enquête, chasse et tue Dracula."
The output in a JSON that is not really nice to read. But SQL is very powerful and you can easily improve the output using JSON_TABLE function.
SELECT TranslationFR AS TranslationFR FROM ( SELECT sys.ML_GENERATE(@finalPrompt, @inferenceSetup) AS textGen ) AS dt INNER JOIN JSON_TABLE ( dt.textGen, "$" COLUMNS( TranslationFR text PATH "$.text") ) AS jt \G
*************************** 1. row ***************************
TranslationFR: Réponse : Le roman gothique horreur de Bram Stoker, intitulé « Dracula », a été publié le 26 mai 1897. C'est un roman épistolaire dont la narration se fait par les lettres, les entrées dans le journal et les articles de presse. Il n'a pas d'héroïne principale et commence avec l'avocat Jonathan Harker qui effectue une visite d'affaires au château d'un nobleman transylvanien, le comte Dracula. Harker s'évade du château après avoir découvert que Dracula est un vampire, et le comte se rend en Angleterre et pille la ville côtière de Whitby. Un petit groupe, dirigé par Abraham Van Helsing, enquête, chasse et tue Dracula.
An alternative is the following query:
SELECT TranslationFR->>"$.text" AS "TranslationFR" FROM (SELECT sys.ML_GENERATE(@finalPrompt, @inferenceSetup) AS TranslationFR ) AS dt \G
*************************** 1. row ***************************
TranslationFR: Réponse : Le roman gothique horreur de Bram Stoker, intitulé « Dracula », a été publié le 26 mai 1897. C'est un roman épistolaire dont la narration se fait par les lettres, les entrées dans le journal et les articles de presse. Il n'a pas d'héroïne principale et commence avec l'avocat Jonathan Harker qui effectue une visite d'affaires au château d'un nobleman transylvanien, le comte Dracula. Harker s'évade du château après avoir découvert que Dracula est un vampire, et le comte se rend en Angleterre et pille la ville côtière de Whitby. Un petit groupe, dirigé par Abraham Van Helsing, enquête, chasse et tue Dracula.
Although the translation isn’t perfect, French speakers should grasp the gist, even if it reads like a particularly polite Advance-fee scam. 😀
Let’s see how to improve that!
Areas for improvement
You now have all the essential components to implement real-time translation within your applications.
Although the generated output may not always align with expectations, you can refine results by adjusting the prompt or modifying inference parameters.
Inference parameters for text generation include:
task
model_id
temperature
max_tokens
top_k
top_p
repeat_penalty
stop_sequences
token_likelihoods
echo
logprobs
stream
context
While prompt engineering is a critical skill, it’s beyond the scope of this article. I strongly recommend dedicating time to mastering this discipline for optimal results.
Let’s try to do some fine tuning.
The text for translation is the same:
-- Text for Translation
SET @ENtext = "Dracula is a gothic horror novel by Bram Stoker, published on 26 May 1897. An epistolary novel, the narrative is related through letters, diary entries, and newspaper articles. It has no single protagonist and opens with solicitor Jonathan Harker taking a business trip to stay at the castle of a Transylvanian nobleman, Count Dracula. Harker escapes the castle after discovering that Dracula is a vampire, and the Count moves to England and plagues the seaside town of Whitby. A small group, led by Abraham Van Helsing, investigate, hunt and kill Dracula.";
The enhanced prompt contains more information: You are an expert translator from English to French. Your task is to translate the original text from English to French. Ensure the French translation is accurate, natural-sounding, and grammatically correct. Preserve the original text’s meaning, style, and context. Only generate the French translation of the original text. \n – Original Text: “, @ENtext, ” \n – French Translation:
-- Enhanced prompt
SET @finalPrompt = CONCAT("You are an expert translator from English to French. Your task is to translate the original text from English to French. Ensure the French translation is accurate, natural-sounding, and grammatically correct. Preserve the original text's meaning, style, and context. Only generate the French translation of the original text. \n - Original Text: ", @ENtext, " \n - French Translation: ");
I modified the inference parameters – temperature, repeat_penalty, top_k and top_p:
-- Translation
SELECT TranslationFR->>"$.text" AS "TranslationFR" FROM (SELECT sys.ML_GENERATE(@finalPrompt, @inferenceSetup) AS TranslationFR ) AS dt \G
*************************** 1. row ***************************
TranslationFR: Dracula est un roman gothique d'horreur de Bram Stoker, publié le 26 mai 1897. C'est un roman épistolaire, la narration se fait par des lettres, des entrées de journal et des articles de journaux. Il n'a pas de héros principaux et commence par le soliciteur Jonathan Harker qui se rend à l'abbaye du comte Dracula, un nobleman de Transylvanie. Harker s'évade de l'abbaye après avoir découvert que Dracula est un vampire, et le comte s'installe en Angleterre et pille la ville côtière de Whitby. Un petit groupe, dirigé par Abraham Van Helsing, enquête, chasse et tue Dracula.
The results show an improvement. Further refinements can be achieved by optimizing inference parameters and prompt engineering.
Peroration
This article explored the use of HeatWave GenAI for text translation. By integrating LLMs into the database, users can efficiently translate text. While this article provided a basic overview, further experimentation with prompts and parameters can enhance results.
By leveraging the power of in-database AI, organizations can unlock new opportunities for global communication and collaboration.
Generative artificial intelligence (GenAI) is reshaping the content creation landscape. By training on vast datasets, these “intelligent” systems can produce new, human-quality content across a multitude of domains.
Oracle’s HeatWave GenAI (starting with version 9.0.1) is at the forefront of this revolution, offering an integrated platform that combines in-database large language models (LLMs), vector stores, and scale-out vector processing to streamline content generation.
This article explores how HeatWave GenAI is empowering businesses to produce high-quality content rapidly and effectively, making it an indispensable tool for industries demanding speed, accuracy, and security.
HeatWave GenAI brings LLMs directly into your database, enabling powerful AI capabilities and natural language processing.
HeatWave GenAI Content Generation
The key benefits of such architecture are:
Effortless LLM Integration: Simple SQL queries unlock AI power.
Seamless Database Integration: No complex external connections.
Simplified Architecture: Easy management and scalability.
Scalable Performance: Handles large datasets and high traffic.
Always Up-to-Date Data: In-database LLMs ensure data freshness.
Generate text-based content using HeatWave GenAI is a very simple 4 steps process:
Load the LLM in HeatWave memory
Define your prompt in natural language
Inference setting
Generate text-based content using HeatWave GenAI
Load the LLM in HeatWave memory
Use the ML_MODEL_LOAD stored procedure: CALL sys.ML_MODEL_LOAD(‘<LLM>’, NULL); The model needs to be loaded only once before generating text. It remains accessible within HeatWave as long as the service is running. This means you don’t have to reload the model for each text generation.
At the time of writing (August 2024) the available models are Mistral 7B (mistral-7b-instruct-v1) and Llama 2 (llama2-7b-v1).
-- Load Mistral 7B model
mysql>
CALL sys.ML_MODEL_LOAD('mistral-7b-instruct-v1', NULL);
Define your prompt in natural language
Like with you prefered programming language, it is more convenient to use a session variable: SET @userPrompt=”My prompt”;
mysql>
SET @userPrompt="Explain what is Generative Artificial intelligence in one sentence";
or whatever your needs:
SET @userPrompt="How to connect to MySQL using Python 3?";
SET @userPrompt="Write a short email to schedule a meeting.";
...
As you likely know, the quality of the prompt significantly impacts the generated text.
Inference setting
Here, you select the desired task, choose the appropriate model, and fine-tune parameters to influence the output.
For simplicity, this example uses default parameters. A more complex example is provided below.
Generate text-based content using HeatWave GenAI
Last step is to pass the user prompt to the LLM using the ML_GENERATE function. The second parameter of this function is a JSON object that allows you to specify the task, the LLM, and some tuning options like the temperature.
The output in a JSON that is not really nice to read. But SQL is very powerful and you can easily improve the output using JSON_TABLE function.
mysql>
SELECT GenContent AS GenContent FROM ( SELECT sys.ML_GENERATE(@userPrompt, @inferenceSetup) AS textGen ) AS dt INNER JOIN JSON_TABLE ( dt.textGen, "$" COLUMNS( GenContent text PATH "$.text") ) AS jt \G
HeatWave GenAI Content Generation
You can influence LLM outputs by adjusting parameters like temperature, number of tokens, stop sequences, top_k, top_p, etc…
Text generation options include:
task
model_id
temperature
max_tokens
top_k
top_p
repeat_penalty
stop_sequences
token_likelihoods
echo
logprobs
stream
context
Below is a quick example:
mysql>
-- Define your prompt in natural language
SET @userPrompt="Explain what is Generative Artificial intelligence in one sentence";
-- Inference setting
SET @inferenceSetup = '{"task": "generation", "temperature": 3, "repeat_penalty": 1, "top_k": 1, "max_tokens": 500, "model_id": "mistral-7b-instruct-v1"}';
-- Generate text-based content
SELECT GenContent AS GenContent FROM ( SELECT sys.ML_GENERATE(@userPrompt, @inferenceSetup) AS textGen ) AS dt INNER JOIN JSON_TABLE ( dt.textGen, "$" COLUMNS( GenContent text PATH "$.text") ) AS jt \G
*************************** 1. row ***************************
GenContent: Generative Artificial Intelligence (AI) refers to a subset of AI that involves the creation of new, original content, such as images, music, or text, through the use of algorithms and machine learning models that can learn from and generate data.
HeatWave GenAI Content Generation with fine tuning
And that’s all! As you can see, it’s very simple and easy with HeatWave GenAI to create some exciting GenAI based applications like:
Enhancing Customer Experience
Intelligent Chatbots: Provide real-time, personalized customer support, answering queries, and resolving issues efficiently.
Content Generation: Create engaging blog articles, product descriptions, and social media posts at scale.
Personalized Recommendations: Offer tailored product suggestions based on customer preferences and behavior.
Social Media Management: Generate engaging content, schedule posts, and monitor performance.
Driving Business Insights
Text Classification: Categorize vast amounts of text data for efficient analysis and decision-making.
Market Research: Analyze customer sentiment, identify trends, and gain competitive insights.
Generative AI is a game-changer, offering endless possibilities to improve efficiency, enhance customer experiences, and drive business growth.
HeatWave GenAI: Your AI-Powered Content Creation Partner demo
Moving forward
While the examples provided offer a glimpse of generative AI’s potential, it’s important to note that significant improvements can be achieved through advanced prompt engineering techniques. Exploring these techniques in depth is beyond the scope of this article.
In upcoming articles, we’ll delve deeper into HeatWave GenAI’s capabilities, showcasing its application in text translation and Retrieval Augmented Generation (RAG).
MySQL Replication exist for centuries… OK, I’m exaggerating a little bit 🙂 but this feature is as much appreciated as it is useful, because is helping DBAs to easily build scalable and redundant architectures:
MySQL 3.23.15 and up features support for one-way, asynchronous replication, in which one server acts as the master, while one or more other servers act as slaves
But what does replication do? Quoting the documentation again (the most recent this time)
Replication enables data from one MySQL database server (the source) to be copied to one or more MySQL database servers (the replicas). Replication is asynchronous by default; replicas do not need to be connected permanently to receive updates from a source.
MySQL 8.4 Reference Manual
In this article we will see how to replicate data from HeatWave MySQL to a on-premise standalone MySQL Instance.
Source is HeatWave MySQL 8.4.1
IP: 137.235.181.51
Port: 3306
Replica is MySQL 8.4.1
IP: 88.65.229.255
Port: 3306
Replication user
‘rplUser’@’88.65.229.%’
We will use 3 main components:
HeatWave MySQL as a source
The asynchronous replication capability of MySQL
A MySQL instance as a read replica
HeatWave MySQL
HeatWave is a fully-managed database service, powered by the integrated in-memory query accelerator. It is the only cloud-native database service that combines transactions, analytics, machine learning and generative AI services into HeatWave, delivering real-time, secure analytics without the complexity, latency, and cost of ETL duplication. It also includes HeatWave Lakehouse which allows users to query data stored in object storage in a variety of file formats. HeatWave is developed, managed, and supported by the MySQL team in Oracle.
HeatWave MySQL accelerate MySQL query performance by orders of magnitude and get real-time analytics on your transactional data. HeatWave MySQL is built on MySQL Enterprise Edition.
The HeatWave MySQL endpoints are not directly accessible from the internet (i.e. private IP). That said, there are different possibilities to connect such as a compute instance, VPN connection, Bastion session, ornetwork load balancer. In this article I’ll use the latter, which is present on a public subnet, enables you to connect to HeatWave MySQL over the internet. However please note that it is not recommended, to make a database accessible over the internet, because it is a security risk. You must restrict the authorized public IP addresses to a single IP address or a small range of IP addresses, and use in-transit encryption. It is recommended to use a VPN connection. You are warned!
I assuming your HeatWave MySQL instance is already created. If not, you can use the Console or the command-line interface to create an instance. Using the console is pretty straightforward, you can find the details in the OCI documentation.
Since MySQL replication is a familiar concept, widely covered over the years, let’s get right to the practical details that will help you set up your replication efficiently. You’ll find all the juicy details in the MySQL 8.4 Reference Manual and some extra here and here.
On the source, create the replication user then grant that user the REPLICATION SLAVE privilege. If you want to ensure that the source only accepts replicas that connect using encrypted connections (and btw you really want that), use the REQUIRE SSL option
-- On the Source
-- Create the replication user
CREATE USER 'rplUser'@'88.65.229.%' IDENTIFIED BY 'Q{P6@EH$L!YFje^9' REQUIRE SSL;
GRANT REPLICATION SLAVE ON *.* TO 'rplUser'@'88.65.229.%';
If the HeatWave MySQL instance (the source) contains existing data it is necessary to copy this data to the replica. There are different ways to dump the data from the source database, the recommended way in that context is to use MySQL Shell dump and load utilities.
You will most likely need dumpInstance and the relevant options are dependent of your context, the most important is usually compatibility. MySQL Shell allows you to store your backup into an object store bucket and load from there, this is very convenient. For this article, the size of my dataset allows me to store it locally, and the only relevant option is dryRun. More details in the documentation: Instance Dump Utility, Schema Dump Utility, and Table Dump Utility.
$ mysqlsh --js admin@137.235.181.51
JS> util.dumpInstance("/backup/20240714", { dryRun: true })
dryRun enabled, no locks will be acquired and no files will be created.
...
If the result is satisfactory, then disable dry run:
JS> util.dumpInstance("/backup/20240714")
...
For your record, below an example of a dump that is stored in an object Storage bucket named MyBck_07 on Oracle Cloud Infrastructure:
-- On the Replica
\sql SET GLOBAL local_infile=1;
util.loadDump("/backup/20240714", {updateGtidSet:"replace", skipBinlog:true})
\sql SET GLOBAL local_infile=0;
All the option details are available in the documentation. Please note that you can test you command with the dryRun option.
Also for your record, if you want to restore a dump for a bucket, your command will look like:
On the source, you can check the status of connected replicas:
-- Check the status of connected replicas
SQL > SHOW PROCESSLIST \G
*************************** 6. row ***************************
Id: 547
User: rplUser
Host: nlb-mhs-paris-dba.sub03221692542.vcnparisdba.oraclevcn.com:1234
db: NULL
Command: Binlog Dump GTID
Time: 428
State: Source has sent all binlog to replica; waiting for more updates
Info: NULL
Basic information about the replicas that were started with the --report-host option and are connected to the source:
Other useful commands on the source, using performance_schema are:
SQL > SELECT
SERVICE_STATE,
HOST,
USER,
PORT
FROM performance_schema.replication_connection_configuration
INNER JOIN performance_schema.replication_applier_status
USING (CHANNEL_NAME) \G
*************************** 1. row ***************************
SERVICE_STATE: ON
HOST: 137.235.181.51
USER: rplUser
PORT: 3306
SELECT
concat(conn_status.channel_name, ' (', worker_id,')') AS channel,
conn_status.service_state AS io_state,
applier_status.service_state AS sql_state,
format_pico_time(if(GTID_SUBTRACT(LAST_QUEUED_TRANSACTION, LAST_APPLIED_TRANSACTION) = "","0",
abs(time_to_sec(if(time_to_sec(APPLYING_TRANSACTION_ORIGINAL_COMMIT_TIMESTAMP)=0,0,
timediff(APPLYING_TRANSACTION_ORIGINAL_COMMIT_TIMESTAMP,now()))))) * 1000000000000) latency,
format_pico_time((LAST_QUEUED_TRANSACTION_START_QUEUE_TIMESTAMP -
LAST_QUEUED_TRANSACTION_ORIGINAL_COMMIT_TIMESTAMP) * 100000000000) transport_time,
format_pico_time((LAST_QUEUED_TRANSACTION_END_QUEUE_TIMESTAMP -
LAST_QUEUED_TRANSACTION_START_QUEUE_TIMESTAMP) * 1000000000000) time_to_relay_log,
format_pico_time((LAST_APPLIED_TRANSACTION_END_APPLY_TIMESTAMP -
LAST_APPLIED_TRANSACTION_START_APPLY_TIMESTAMP) * 1000000000000) apply_time,
conn_status.LAST_QUEUED_TRANSACTION AS last_queued_transaction,
applier_status.LAST_APPLIED_TRANSACTION AS last_applied_transaction
FROM
performance_schema.replication_connection_status AS conn_status JOIN performance_schema.replication_applier_status_by_worker AS applier_status
ON applier_status.channel_name = conn_status.channel_name
\G
*************************** 1. row ***************************
channel: (1)
io_state: ON
sql_state: ON
latency: 0 ps
transport_time: 0 ps
time_to_relay_log: 0 ps
apply_time: 0 ps
last_queued_transaction:
last_applied_transaction:
...
Peroration
This article guides you through setting up replication between a HeatWave MySQL instance (source) and an on-premise standalone MySQL instance (replica). It highlights key steps like creating a replication user, securing the connection, and using MySQL Shell utilities for data transfer. While a Network Load Balancer can be used for the connection, a VPN is recommended for better security.
