Having explored the innovative MySQL HeatWave technology that converts Natural Language into SQL (Ask Your Database Anything: Natural Language to SQL in MySQL HeatWave), our next article in this series, dives into a practical use case demonstrating how an AI DBA Assistant can significantly simplify your query generation workflow.
In MySQL, there are 3 specialized system schemas designed to give DBAs and developers deeper visibility and control over the server. Together, they provide the tools needed to monitor performance, inspect metadata, and simplify management tasks:
The Performance Schema is a powerful instrumentation framework designed for low-level monitoring of server execution, enabling administrators and developers to gain deep insights into how the database is running. Unlike general status metrics, it collects highly detailed statistics about server events and resource usage directly from the server internals in real time, with the data stored in memory. It is particularly useful for for live diagnostics and performance tuning. The primary purpose of the Performance Schema is to expose what is happening inside the MySQL server and, more importantly, why certain operations may be slow. This makes it an invaluable tool for tasks such as identifying poorly performing queries, diagnosing I/O wait bottlenecks, or analyzing mutex contention in multithreaded workloads. By surfacing these low-level insights, the Performance Schema empowers users to move beyond surface-level monitoring and perform precise root-cause analysis of performance issues.
The Information Schema is the SQL-standard-compliant interface for accessing metadata about the objects managed by the server. Acting as a central directory, it provides a structured view of databases, tables, columns, indexes, privileges, and overall server characteristics, making it the go-to source for understanding the logical organization of a MySQL instance. Its primary purpose is to expose database metadata—answering questions about what objects exist and how they are structured, rather than how they perform. While the metadata itself is stored on disk, MySQL presents it through in-memory tables that can be queried like regular tables. Typical use cases include retrieving a list of all tables in a specific database, checking column data types, or examining indexes and privileges. By adhering to the SQL standard, the Information Schema ensures portability and consistency, allowing users to interact with MySQL metadata in a way that aligns with other relational database systems.
The Sys Schema is a set of user-friendly views, functions, and procedures that sits on top of the Performance Schema and Information Schema, transforming their often complex and technical data into a more readable, actionable format. Its main purpose is to simplify the process of interpreting server metadata and performance statistics, making it much easier for DBAs and developers to diagnose issues and optimize workloads without having to manually parse through raw instrumentation data. By aggregating and presenting information from both underlying schemas, the Sys Schema provides clear insights into common administrative tasks, such as identifying the most time-consuming queries, monitoring active sessions, or detecting unused indexes. In essence, it acts as a usability layer, bridging the gap between MySQL’s powerful but intricate internal schemas and the practical needs of day-to-day database operations.
Together, these schemas form the foundation for effective MySQL performance tuning, troubleshooting, and administration.
However, fully leveraging the Performance Schema, Information Schema, and Sys Schema requires a solid command of SQL to query the data they expose. This is where AI can bridge the gap. As explored in my article Ask Your Database Anything: Natural Language to SQL in MySQL HeatWave, MySQL HeatWave’s Natural Language to SQL (NL2SQL) capabilities make it possible to interact with your database by simply asking questions in plain English, without writing complex queries.
Let’s walk through a concrete example (a special thanks to my colleague Ivan for inspiring this idea):
MySQL’s Performance, Information, and Sys Schemas give DBAs powerful tools to monitor, tune, and troubleshoot servers, while HeatWave NL2SQL makes those insights accessible in simple English.
The ability to query data efficiently has always been central to unlocking insights, but writing SQL can be a barrier for many users who aren’t fluent in the language of databases. Analysts, product managers, and business users often know the questions they want to ask—just not how to express them in SQL. With the rise of large language models (LLMs) and advancements in database technology, that gap is closing quickly.
MySQL HeatWave now brings Natural Language to SQL (NL2SQL) capabilities directly into the database engine, allowing users to generate SQL queries from plain English statements. Instead of wrestling with complex joins, filters, or aggregate functions, users can simply type a natural-language request—such as “Show me the top 10 products by revenue this quarter”—and HeatWave automatically translates it into an SQL query.
In this article, we’ll explore how to leverage LLM-powered NL2SQL in MySQL HeatWave, walk through practical examples, and show how this feature empowers both technical and non-technical users to interact with data more intuitively.
SQL>
-- HeatWave MySQL server version
SHOW VARIABLES WHERE Variable_name IN ('version_comment', 'version');
+-----------------+--------------------------+
| Variable_name | Value |
+-----------------+--------------------------+
| version | 9.4.1-cloud |
| version_comment | MySQL Enterprise - Cloud |
+-----------------+--------------------------+
Furthermore, I’m using a modified version (available on my GitHub account) of the well-known World database. Although the data hasn’t been updated in some time, it still serves as a useful tool for visualizing the results of SQL queries.
Here the schema:
CREATE TABLE `country` (
`code` char(3) NOT NULL DEFAULT '' COMMENT 'Primary key: three-letter country code (ISO standard).',
`country_name` char(52) NOT NULL DEFAULT '' COMMENT 'Official name of the country.',
`continent` enum('Asia','Europe','North America','Africa','Oceania','Antarctica','South America') NOT NULL DEFAULT 'Asia' COMMENT 'Continent where the country is located.',
`region` char(26) NOT NULL DEFAULT '' COMMENT 'Geographical region within the continent.',
`surface_area` decimal(10,2) NOT NULL DEFAULT '0.00' COMMENT 'Total surface area of the country in square kilometers.',
`independance_year` smallint DEFAULT NULL COMMENT 'Year the country achieved independence (NULL if unknown).',
`country_population` int NOT NULL DEFAULT '0' COMMENT 'Total population of the country.',
`life_expectancy` decimal(3,1) DEFAULT NULL COMMENT 'Average life expectancy of the population in years.',
`GNP` decimal(10,2) DEFAULT NULL COMMENT 'Gross National Product of the country in millions of USD.',
`GNPOld` decimal(10,2) DEFAULT NULL COMMENT 'Gross National Product in an earlier year for comparison.',
`local_name` char(45) NOT NULL DEFAULT '' COMMENT 'The country’s name in its local language.',
`government_form` char(45) NOT NULL DEFAULT '' COMMENT 'Description of the form of government (e.g., Republic, Monarchy).',
`head_of_state` char(60) DEFAULT NULL COMMENT 'Name of the current head of state (e.g., President, Monarch).',
`ID_capital` int DEFAULT NULL COMMENT 'ID of the capital city (foreign key reference to city table).',
`code2` char(2) NOT NULL DEFAULT '' COMMENT 'Two-letter country code (ISO standard).',
PRIMARY KEY (`code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='Stores detailed information about countries, including identifiers, demographics, economy, and government structure.'
;
CREATE TABLE `country_language` (
`country_code` char(3) NOT NULL DEFAULT '' COMMENT 'Three-letter country code referencing the country table. First part of the composite primary key.',
`language` char(30) NOT NULL DEFAULT '' COMMENT 'Name of the language spoken in the country. Second part of the composite primary key.',
`is_official` enum('T','F') NOT NULL DEFAULT 'F' COMMENT 'Indicates whether the language is an official language of the country (T = true, F = false).',
`percentage` decimal(4,1) NOT NULL DEFAULT '0.0' COMMENT 'Percentage of the country’s population that speaks this language.',
PRIMARY KEY (`country_code`,`language`),
KEY `country_code` (`country_code`),
CONSTRAINT `country_language_ibfk_1` FOREIGN KEY (`country_code`) REFERENCES `country` (`code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='Stores information about the languages spoken in each country, including whether they are official and the share of the population using them.'
;
CREATE TABLE `city` (
`ID_city` int NOT NULL AUTO_INCREMENT COMMENT 'Primary key: integer that uniquely identifies each city.',
`city_name` char(35) NOT NULL DEFAULT '' COMMENT 'Name of the city.',
`country_code` char(3) NOT NULL DEFAULT '' COMMENT 'Three-letter country code referencing the country table.',
`district` char(20) NOT NULL DEFAULT '' COMMENT 'District or administrative region where the city is located.',
`city_population` int NOT NULL DEFAULT '0' COMMENT 'Population count of the city.',
PRIMARY KEY (`ID_city`),
KEY `country_code` (`country_code`),
CONSTRAINT `city_ibfk_1` FOREIGN KEY (`country_code`) REFERENCES `country` (`code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='Stores information about cities, including their name, country, district, and population.'
;
As you can see, the main difference from the original version is that I embedded the documentation directly into the tables using COMMENT clauses. In addition, some column names have been renamed. The goal is to capture as much relevant information as possible to provide business context. This contextual information will then be used to augment the LLM.
Let’s run 3 queries on the data set:
MySQL>
-- Find the name, country code and the district of the city with ID 2989
SELECT city_name, country_code, district
FROM city
WHERE ID_city = 2989;
+-----------+--------------+-------------+
| city_name | country_code | district |
+-----------+--------------+-------------+
| Grenoble | FRA | Rhône-Alpes |
+-----------+--------------+-------------+
-- Find the government form of Germany
SELECT government_form
FROM country
WHERE code = 'FRA';
+-----------------+
| government_form |
+-----------------+
| Republic |
+-----------------+
-- List all official languages spoken in Canada
SELECT language
FROM country_language
WHERE country_code = 'CAN' AND is_official = 'T';
+----------+
| language |
+----------+
| English |
| French |
+----------+
These queries are fairly simple, but they still require some SQL skills. What if you could write in plain English and have MySQL HeatWave understand you? This is now possible with the NL_SQL routine, which generates and executes SQL queries directly from natural language statements.
Generate SQL Queries From Natural-Language Statements
Starting with MySQL HeatWave 9.4.1, MySQL HeatWave GenAI (on OCI, AWS, and Azure) allows you with the NL_SQL stored procedure, to generate SQL queries directly from natural language, making it easier to interact with your databases.
The feature gathers information about the schemas, tables, and columns you have access to, then leverages a Large Language Model to generate an appropriate SQL query based on your request. You can also execute the generated query and view the results instantly.
Also as of MySQL HeatWave 9.3.2, you can view the list of available Large Language Models and embedding models directly from the database using the sys.ML_SUPPORTED_LLMS view. This enhancement, part of the MySQL HeatWave GenAI feature, allows you to stay current with the available models, including in-database models and those from external services likeOracle Cloud Infrastructure (OCI) Generative AI.
To display the list of supported content generation LLMs and their provider, you can use the following simple query:
MySQL>
SELECT model_id, provider
FROM sys.ML_SUPPORTED_LLMS
WHERE capabilities LIKE '["GENERATION"]';
+-------------------------------+---------------------------+
| model_id | provider |
+-------------------------------+---------------------------+
| llama2-7b-v1 | HeatWave |
| llama3-8b-instruct-v1 | HeatWave |
| llama3.1-8b-instruct-v1 | HeatWave |
| llama3.2-1b-instruct-v1 | HeatWave |
| llama3.2-3b-instruct-v1 | HeatWave |
| mistral-7b-instruct-v1 | HeatWave |
| mistral-7b-instruct-v3 | HeatWave |
| cohere.command-latest | OCI Generative AI Service |
| cohere.command-plus-latest | OCI Generative AI Service |
| cohere.command-a-03-2025 | OCI Generative AI Service |
| meta.llama-3.3-70b-instruct | OCI Generative AI Service |
| cohere.command-r-08-2024 | OCI Generative AI Service |
| cohere.command-r-plus-08-2024 | OCI Generative AI Service |
+-------------------------------+---------------------------+
We can see here, the in-MySQL HeatWave models (provider HeatWave) as well as Oracle Cloud Infrastructure Generative AI service models (provider OCI Generative AI Service).
To view or use the OCI Generative AI Service models in this list, you need to enable the database system to access OCI services. For more information, see Authenticate OCI Generative AI Service.
When using the sys.NL_SQL routine in MySQL HeatWave, you have several options for the underlying (LLM) that translates your natural language queries into SQL. The available models are part of the MySQL HeatWave GenAI feature. You can select from in-database models or, like we have seen above, if your environment is configured for it, models from the Oracle Cloud Infrastructure (OCI) Generative AI Service.
In MySQL HeatWave 9.4.1, some of the available models for natural language to SQL tasks include:
meta.llama-3.3-70b-instruct (OCI Generative AI Service)
llama3.1-8b-instruct-v1 (In-database HeatWave)
llama3.2-3b-instruct-v1 (In-database HeatWave)
The specific models available may vary by MySQL HeatWave version and the cloud service region you are using. To get the most current list, don’t forget that you can always query the sys.ML_SUPPORTED_LLMS view.
What Can I Ask My Database? Example Queries
Now, let’s see how it works in action. We’ll start with our earlier queries and then try out a few new ones.
Find the name, country code and the district of the city with ID 2989:
MySQL> SET @nlq = "Find the name, country code and the district of the city with ID 2989";
Query OK, 0 rows affected (0.0004 sec)
MySQL> CALL sys.NL_SQL(@nlq, @output, '{"model_id": "llama3.2-3b-instruct-v1", "schemas":["nl2sql_world"]}');
+--------------------------------------------------------------------------------------------------+
| Executing generated SQL statement... |
+--------------------------------------------------------------------------------------------------+
| SELECT `city_name`, `country_code`, `district` FROM `nl2sql_world`.`city` WHERE `ID_city` = 2989 |
+--------------------------------------------------------------------------------------------------+
1 row in set (3.7089 sec)
+-----------+--------------+-------------+
| city_name | country_code | district |
+-----------+--------------+-------------+
| Grenoble | FRA | Rhône-Alpes |
+-----------+--------------+-------------+
1 row in set (3.7089 sec)
Find the government form of Germany:
MySQL> SET @nlq = "Find the government form of Germany";
Query OK, 0 rows affected (0.0003 sec)
MySQL> CALL sys.NL_SQL(@nlq, @output, '{"model_id": "llama3.1-8b-instruct-v1", "schemas":["nl2sql_world"]}');
+-----------------------------------------------------------------------------+
| Executing generated SQL statement... |
+-----------------------------------------------------------------------------+
| SELECT `government_form` FROM `nl2sql_world`.`country` WHERE `code` = 'DEU' |
+-----------------------------------------------------------------------------+
1 row in set (4.0998 sec)
+------------------+
| government_form |
+------------------+
| Federal Republic |
+------------------+
1 row in set (4.0998 sec)
List all official languages spoken in Canada:
MySQL> SET @nlq = "Find the top 5 most populated cities in the world";
Query OK, 0 rows affected (0.0002 sec)
MySQL> CALL sys.NL_SQL(@nlq, @output, '{"model_id": "meta.llama-3.3-70b-instruct", "schemas":["nl2sql_world"]}');
+----------------------------------------------------------------------------------------------------------+
| Executing generated SQL statement... |
+----------------------------------------------------------------------------------------------------------+
| SELECT `city_name`, `city_population` FROM `nl2sql_world`.`city` ORDER BY `city_population` DESC LIMIT 5 |
+----------------------------------------------------------------------------------------------------------+
1 row in set (2.6276 sec)
+-----------------+-----------------+
| city_name | city_population |
+-----------------+-----------------+
| Mumbai (Bombay) | 10500000 |
| Seoul | 9981619 |
| São Paulo | 9968485 |
| Shanghai | 9696300 |
| Jakarta | 9604900 |
+-----------------+-----------------+
5 rows in set (2.6276 sec)
Find the top 10 most multilingual countries (by number of languages spoken):
MySQL> SET @nlq = "Find the top 10 most multilingual countries (by number of languages spoken)";
Query OK, 0 rows affected (0.0004 sec)
MySQL> CALL sys.NL_SQL(@nlq, @output, '{"model_id": "meta.llama-3.3-70b-instruct", "schemas":["nl2sql_world"]}');
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Executing generated SQL statement... |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| SELECT `T1`.`country_name`, COUNT(`T2`.`language`) AS `num_languages` FROM `nl2sql_world`.`country` AS `T1` JOIN `nl2sql_world`.`country_language` AS `T2` ON `T1`.`code` = `T2`.`country_code` GROUP BY `T1`.`code` ORDER BY `num_languages` DESC LIMIT 10 |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (5.8831 sec)
+--------------------+---------------+
| country_name | num_languages |
+--------------------+---------------+
| United States | 12 |
| China | 12 |
| India | 12 |
| Canada | 12 |
| Russian Federation | 12 |
| Tanzania | 11 |
| South Africa | 11 |
| Philippines | 10 |
| Iran | 10 |
| Kenya | 10 |
+--------------------+---------------+
10 rows in set (5.8831 sec)
List all official languages spoken in Canada:
MySQL> SET @nlq = "List all official languages spoken in Canada";
Query OK, 0 rows affected (0.0003 sec)
MySQL> CALL sys.NL_SQL(@nlq, @output, '{"model_id": "llama3.2-3b-instruct-v1", "schemas":["nl2sql_world"]}');
+------------------------------------------------------------------------------------------------------------------+
| Executing generated SQL statement... |
+------------------------------------------------------------------------------------------------------------------+
| SELECT `language` FROM `nl2sql_world`.`country_language` WHERE `is_official` = 'T' AND `country_code` IN ('CAN') |
+------------------------------------------------------------------------------------------------------------------+
1 row in set (3.0730 sec)
+----------+
| language |
+----------+
| English |
| French |
+----------+
2 rows in set (3.0730 sec)
Pretty impressive, isn’t it? 🙂
Click the picture to enlarge
A word of caution: Natural Language to SQL is not foolproof. The LLM can make mistakes and may generate an incorrect query. Additionally, while there are no guarantees, a larger number of model parameters (the b in 70b) typically leads to better results.
The next section will discuss key considerations for ensuring optimal performance and accuracy when using the sys.NL_SQL routine.
Getting the Best Results from Natural Language to SQL
Best Practices for Optimal Results
To get the most out of the NL_SQL feature, follow these best practices for generating accurate and efficient queries:
Provide Specific Context: Help the LLM by restricting its focus. Use the schemas and tables parameters to specify only the relevant schemas or tables for your query.
Use Descriptive Names: Ensure your tables, columns, and views have clear, semantically meaningful names. Using descriptive views is especially helpful for improving the accuracy of complex JOIN operations.
Be Specific with Values: To avoid errors, provide exact values in your natural language input, rather than vague descriptions.
Ready to move from concept to code? See the practical use case where an AI DBA Assistant significantly simplifies your query generation workflow in the article, Let Your AI DBA Assistant Write Your MySQL Queries
Important Limitations and Considerations
While powerful, the NL_SQL feature does have a few limitations to keep in mind:
Query Accuracy: The generated SQL may not always be perfectly valid or optimal. Always review the output before executing it.
Performance: Generated queries can sometimes be complex, leading to unpredictable execution times and potentially large result sets that consume excessive resources.
Stateless Operations: The feature does not maintain the state of previous calls. Each invocation is independent and does not learn from prior interactions.
Metadata Volume: Accuracy can decrease if the database contains a large amount of metadata, as this can confuse the LLM.
Unsupported Features: The NL_SQL routine does not support temporary tables.
Peroraison
With the Natural Language to SQL feature in MySQL HeatWave, we’re witnessing a major shift in how we interact with databases. This technology empowers a broader range of users—from business analysts to product managers—to access and analyze data without needing deep SQL expertise. By simply using plain English, anyone can now translate a business question into an executable SQL query. This not only democratizes data access but also significantly accelerates the time from question to insight.
The sys.NL_SQL routine, backed by powerful LLMs, is a testament to the seamless integration of generative AI into core database services. While it’s not a silver bullet—and requires careful consideration of best practices like providing context and using descriptive names—it marks a fundamental step toward making data more accessible and intuitive.
By providing a bridge between human language and database queries, MySQL HeatWave is not just helping us write code; it’s enabling us to ask our databases anything, paving the way for a more natural and productive relationship with our data.
With the sys.NL_SQL routine, asking questions of your database has never been more natural.
Managing large volumes of data is a challenge every organization faces as applications grow. For MySQL users, this often means deciding what to do with historical or less frequently accessed data: keep it in production tables at the cost of performance, or archive it and lose the ability to query it efficiently. Traditionally, archiving has been a trade-off — helpful for keeping databases lean, but limiting when developers or analysts need to run queries across years of historical records.
By combining a high-performance in-memory query accelerator with a fully managed MySQL database service, HeatWave makes it possible to archive vast amounts of data while still enabling fast, interactive analytics. Instead of moving archived data into a separate system or relying on slow queries, developers and DBAs can continue to use familiar SQL while taking advantage of HeatWave’s speed and scale.
In this article, we’ll explore how to use HeatWave to archive MySQL data effectively and then run accelerated queries on that archived data — without compromising performance. Whether you’re managing billions of rows or just planning for future growth, this approach can help simplify your architecture while delivering the best of both worlds: efficient data storage and lightning-fast analytics.
Context
The database has been collecting and collecting daily time-stamped data for years.To optimize performance, we want to keep only the current year’s data plus the full year before it. All older data should be archived, yet still available for querying with very low latency. Let’s see how HeatWave can help us archive efficiently while still running lightning-fast queries on historical data.
SQL>
-- HeatWave MySQL server version
SHOW VARIABLES WHERE Variable_name IN ('version_comment', 'version');
+-----------------+--------------------------+
| Variable_name | Value |
+-----------------+--------------------------+
| version | 8.4.6-cloud |
| version_comment | MySQL Enterprise - Cloud |
+-----------------+--------------------------+
with a 1 node HeatWave Cluster enable.
SQL>
-- HeatWave cluster node number / HeatWave Cluster status
SHOW STATUS WHERE Variable_name IN ('rapid_cluster_ready_number', 'rapid_cluster_status');
+----------------------------+-------+
| Variable_name | Value |
+----------------------------+-------+
| rapid_cluster_ready_number | 1 |
| rapid_cluster_status | ON |
+----------------------------+-------+
We’ll illustrate this article’s examples using a dataset from Kaggle.
Exchanging Partitions and Subpartitions with Tables
In our case, a practical approach to archiving is to convert the partitions containing old data into regular tables. With {HeatWave} MySQL, this process is straightforward. Let’s take a look at how it works.
In MySQL, it is possible to exchange a table partition or subpartition with a table usingALTER TABLE. There are some requirements, so you may want to read the documentation, but in short we first need to create a similar table than the one that contains the partitions. But this table must not be partitioned. Then we’ll be able to transfer the data from the partition to this new table.
In MySQL, you can use the ALTER TABLE command to exchange a partition or subpartition with a regular table. There are a few requirements to meet — so it’s worth reviewing the documentation — but in short, the process starts by creating a table with the same structure as the partitioned one, except without partitions. Once that table is ready, you can transfer the data from the partition into it.
To create the table (min_temp_1981) that will store the data from partition p1981, you can do, a CREATE… SELECT:
SQL >
CREATE TABLE min_temp_1981 SELECT * FROM min_temp WHERE NULL;
Query OK, 0 rows affected (0.0126 sec)
Records: 0 Duplicates: 0 Warnings: 0
-- No data was copied
SELECT * FROM min_temp_1981 LIMIT 3;
Empty set (0.0005 sec)
SHOW CREATE TABLE min_temp_1981\G
*************************** 1. row ***************************
Table: min_temp_1981
Create Table: CREATE TABLE `min_temp_1981` (
`d` date DEFAULT NULL,
`temp` decimal(3,1) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
In order to have lightning-fast queries, the archived data must be loaded into the HeatWave cluster.
HeatWave cluster provides a distributed, scalable, shared-nothing, in-memory, hybrid columnar, query processing engine. It can accelerate analytic queries, query external data stored in object storage, and perform machine learning.
To successfully load a table into a HeatWave cluster, you must first ensure it has a primary key. If the table you are trying to archive lacks a primary key, the service will return an “Unable to load table without primary key“ error:
The workflow can be easily simplified using stored procedures (since we’re using HeatWave MySQL 8.4, we can’t take advantage of JavaScript stored procedures, which are only available starting from MySQL 9.2.).
Let’s review the 4 steps:
1. Clone table (Copy structure without partitions,… or data) CALL clone_table(<schema_name>, <partitioned_table>, <archived_table>);
3. Add a primary key CALL add_primary_key_column(<schema_name>, <archived_table>, <primary_key>);
4. Load the data into the HeatWave Cluster CALL sys.heatwave_load(JSON_ARRAY(<schema_name>), JSON_OBJECT(‘include_list’, JSON_ARRAY(<schema_name>.<archived_table>)));
While a robust production solution would be more complex, we can illustrate the core concepts with a basic implementation (these examples should only be used for testing and learning) :
Clone table
DELIMITER $$
CREATE PROCEDURE `clone_table`(
IN _source_schema VARCHAR(64),
IN _source_table VARCHAR(64),
IN _destination_table VARCHAR(64)
)
BEGIN
-- Build the ALTER TABLE statement in order to create the table with the data
SET @create_table_stmt = CONCAT(
' CREATE TABLE `',_source_schema, '`.`', _destination_table,
'` SELECT * FROM `',_source_schema, '`.`',_source_table, '`',
' WHERE NULL '
);
-- Execute
PREPARE stmt FROM @create_table_stmt;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END$$
DELIMITER ;
DELIMITER $$
CREATE PROCEDURE exchange_partition(
IN _source_schema VARCHAR(64),
IN _partitioned_table VARCHAR(64),
IN _partition_to_exchange VARCHAR(64),
IN _new_table_name VARCHAR(64)
)
BEGIN
-- Build the ALTER TABLE statement in order to exchange the partition with the newly created table
SET @exchange_stmt = CONCAT(
' ALTER TABLE `',_source_schema, '`.`', _partitioned_table, '` ',
' EXCHANGE PARTITION `', _partition_to_exchange, '` ',
' WITH TABLE `',_source_schema, '`.`', _new_table_name, '`'
);
-- Execute
PREPARE stmt FROM @exchange_stmt;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END$$
DELIMITER ;
DELIMITER $$
CREATE PROCEDURE add_primary_key_column(
IN _source_schema VARCHAR(64),
IN _table_name VARCHAR(64),
IN _column_with_PK VARCHAR(64)
)
BEGIN
-- Build the ALTER TABLE statement in order to add the PK
SET @alter_table_stmt = CONCAT(
' ALTER TABLE `',_source_schema, '`.`', _table_name, '` ',
' ADD COLUMN `', _column_with_PK, '` INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY FIRST'
);
-- Execute
PREPARE stmt FROM @alter_table_stmt;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END$$
DELIMITER ;
SQL>
SELECT name, load_progress, load_status, query_count
FROM performance_schema.rpd_tables JOIN performance_schema.rpd_table_id USING(id)
WHERE name LIKE 'temp_archiving%'
ORDER BY name;
+------------------------------+---------------+---------------------+-------------+
| name | load_progress | load_status | query_count |
+------------------------------+---------------+---------------------+-------------+
| temp_archiving.min_temp_1981 | 100 | AVAIL_RPDGSTABSTATE | 0 |
| temp_archiving.min_temp_1982 | 100 | AVAIL_RPDGSTABSTATE | 0 |
+------------------------------+---------------+---------------------+-------------+
Data has been offloaded to the HeatWave Cluster.
In our case, this workload would only run once a year after the catch-up phase. But in practice, you could schedule this kind of workflow to run more regularly — whether with cron (or the event scheduler if you are a player 🙂 , your favorite open source data orchestration tool like Airflow, Prefect, Dagster,… or a cloud native managed service like OCI Data Integration.
Peroraison
Archiving data no longer has to mean sacrificing accessibility or performance. With HeatWave MySQL, DBAs and developers can seamlessly move older partitions into regular tables, load them into the HeatWave cluster, and continue running queries at scale — all with the familiar MySQL syntax they already know.
By combining efficient data archiving with in-memory acceleration, HeatWave allows organizations to strike the balance between keeping production databases lean and still being able to analyze years of historical data instantly. What was once a trade-off between storage efficiency and query performance is now a streamlined workflow that can be automated and adapted to your needs.
With HeatWave, your archived data is no longer just stored; it’s ready to deliver actionable insights at the speed of thought.
A few years ago (in 2018), I wrote a MySQL Security series and one of the episode was about the MySQL Enterprise Audit feature — MySQL Security – MySQL Enterprise Audit — a powerful auditing capability that enables you to track and monitor database activity to ensure data integrity, strengthen security, and maintain compliance with regulatory requirements. This robust feature has also been available in HeatWave MySQL for the past few years (since 2023), bringing the same enterprise-grade auditing capabilities to the cloud.
HeatWave MySQL Database Audit is builds upon the established technology of MySQL Enterprise Audit, offering a comprehensive solution for tracking and analyzing database activities.
Key Benefits of HeatWave MySQLDatabase Audit
Rigorous Compliance & Forensics: Helps organizations meet stringent industry regulations (like FedRAMP, DISA STIG, PCI-DSS, HIPAA, SOX, GDPR, FERPA, and Center for Internet Security Benchmarks) by creating a detailed record of database events, essential for investigations and demonstrating adherence to policies.
Security Operations (SecOps): Enables real-time monitoring of user behaviors to detect and respond to potential security threats proactively.
Holistic Server Activity Tracking: Provides comprehensive auditing, from basic client connections and disconnections to more granular activities like interactions with specific schemas and tables, security changes, and errors.
Insights into Query and Statement Performance: Tracks query execution statistics, allowing for the identification of slow queries and performance bottlenecks, leading to database optimization.
Utilization & Optimization: Offers data-driven insights to pinpoint and streamline database operations and resource utilization.
“Trust but Verify” Security Principle: Allows for the monitoring of high-privilege users to prevent misuse of access.
Business Audit: Creates detailed records to prove data validity, accuracy, and integrity, demonstrating that no tampering has occurred.
Security Analysis: Serves as a vital component in a defense-in-depth strategy, facilitating both proactive (machine learning-based anomaly detection) and reactive (post-mortem analysis of attacks) security measures.
Feature Highlights:
Ready to Use: No installation steps are required, simplifying the process of securing and monitoring database activities.
Customizable Auditing: Allows Database Administrators to define filters to monitor specific operations, users, or broad activity categories, reducing audit noise and optimizing log size.
Real-time & Minimal Overhead: Provides instantaneous access to database activity with minimal impact on performance, supporting diverse use cases.
Automatic Log Rotation & Management: Includes built-in log rotation and automatic purging, ensuring optimal DB system performance without manual intervention. Audit logs are structured in JSON format, encrypted, and compressed for efficiency and security.
Multi-Instance Support: Works seamlessly with standalone, multi-instance, and High-Availability (HA) configurations, replicating audit filters and configurations to ensure no event is lost.
Access via SQL Interface: Enables querying of logs directly from the SQL interface using any MySQL client. Logs can also be channeled to OCI Logging Analytics, third-party monitoring tools, or SIEM systems for broader analysis.
Effortless Migration of Rules: Existing MySQL Enterprise Auditing rules (on-premise or other systems) can be easily migrated to HeatWave MySQL instances in the cloud, ensuring consistency.
Optional Query Execution Metrics: Can include details about query execution, such as slow queries, for performance analysis.
Option to Remove Sensitive Data: Allows for the omission of sensitive data from statements before logging.
Server Activity Tracking
The goal here is to track all DML (Data Manipulation Language) statements executed on a HeatWave MySQL instance, by an user account (assuming this user account it used by an application that requires audited). You can easily achieve this by creating a dedicated HeatWave MySQL Audit Log Filter that specifically logs:
INSERT
UPDATE
DELETE
TRUNCATE TABLE
REPLACE
LOAD DATA
LOAD XML
We’ll assign this filter to the dedicated user called auditee@%.
Workflow overview:
Verify that the HeatWave MySQL Database Audit is enabled
Create the DML specific audit filter
Register the filter
Assign the filter to the appropriate user account
Important: To utilize any filtering capabilities, the user performing these actions must possess the AUDIT_ADMIN privilege.
HeatWave MySQL Database Audit is enable by default. You can check using the following queries:
SQL>
-- Checks at the server plugins level
SELECT
PLUGIN_NAME,
PLUGIN_STATUS
FROM
INFORMATION_SCHEMA.PLUGINS
WHERE
PLUGIN_NAME LIKE 'audit%';
+-------------+---------------+
| PLUGIN_NAME | PLUGIN_STATUS |
+-------------+---------------+
| audit_log | ACTIVE |
+-------------+---------------+
-- Check at the component-based infrastructure level
SELECT
*
FROM
mysql.component
WHERE
component_urn LIKE '%audit%'\G
*************************** 1. row ***************************
component_id: 6
component_group_id: 6
component_urn: file://component_audit_api_message_emit
The user_defined_functions table contains a row for each loadable function registered automatically by a component or plugin, or manually by a CREATE FUNCTION statement.
Create the DML Audit Filter
The filer to log all DMLs running on HeatWave MySQL is quite simple. We are using a JSON syntax:
To access audit data, users can simply query it using standard SQL. The primary method for retrieving this information is audit_log_read(), which returns the audit records in JSON format.
For a basic example of how to extract audit log entries, use the following command:
SELECT audit_log_read(audit_log_read_bookmark());
To display the audit data in a more readable format, use theJSON_PRETTY() and CONVERT() functions:
SELECT JSON_PRETTY(CONVERT(audit_log_read(audit_log_read_bookmark()) USING UTF8MB4))\G
You can refine your audit data extraction by passing additional parameters to the audit_log_read() function. For example, to retrieve, 10 entries of audit logs starting from a specific timestamp, you can use:
Note. You can also use the MySQL’s JSON function JSON_TABLE, to transform audit data into a tabular format. For example to extract a subset of JSON name-value pairs and convert them into a structured table, making the data easier to work with and analyze.
Playground
My application uses the auditee user account, which has the necessary privileges on the s1 schema as well as the AUDIT_ADMIN privilege:
CREATE USER auditee@'%' IDENTIFIED BY 'My5up4rP@sS';
GRANT ALL ON s1.* TO auditee@'%';
GRANT AUDIT_ADMIN ON *.* TO auditee@'%';
and for this demo I’ll use the table s1.t1:
CREATE SCHEMA s1;
USE s1;
CREATE TABLE `t1` (
`id` int NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`id`)
);
Inside a session using the auditee@% user account:
HeatWave MySQL Database Audit offers a powerful, enterprise-grade auditing framework that seamlessly extends MySQL Enterprise Audit capabilities to the cloud. By enabling fine-grained filtering, real-time monitoring, and flexible log access via SQL, it empowers database administrators and security teams to ensure compliance, enhance visibility, and strengthen operational security.
In this article, we demonstrated how to track all DML operations executed by a specific application user using a dedicated audit filter. From enabling the audit plugin to querying structured audit logs, HeatWave makes it straightforward to implement robust auditing practices with minimal overhead.
Whether you’re working toward regulatory compliance, safeguarding sensitive data, or optimizing database performance, HeatWave MySQL Database Audit equips you with the tools needed to meet modern data governance and security demands—efficiently and effectively.
By leveraging HeatWave MySQL Database Audit, you’re not just logging data; you’re building a foundation of trust and accountability for your critical database operations.
When it comes to loading data from CSV files into your MySQL environment, there’s no shortage of options. In this post, I’ll walk you through two efficient, developer-friendly and MySQL-ish approaches:
SQL>
SELECT COUNT(*) FROM homestays.reviews_from_mysqlsh;
+----------+
| COUNT(*) |
+----------+
| 2068800 |
+----------+
1 row in set (0.5208 sec)
SQL>
EXPLAIN SELECT COUNT(*) FROM homestays.reviews_from_mysqlsh\G
*************************** 1. row ***************************
EXPLAIN: -> Aggregate: count(0) (cost=816457..816457 rows=1)
-> Table scan on reviews_from_mysqlsh (cost=0.267..551720 rows=2.06e+6)
1 row in set (0.0934 sec)
As a side note, you can even do some basic transformations on the fly before loading the data. See decodeColumns and columns options.
Import data from a local disk
If the file is on a “local” disk, the syntax is the following:
JS>
util.importTable("/Data/project/source/reviews.csv", {schema: "homestays", table: "reviews_from_mysqlsh", dialect: "csv-unix", skipRows: 1, showProgress: true})
Importing from file '/Data/project/source/reviews.csv' to table `homestays`.`reviews_from_mysqlsh` in MySQL Server at 10.0.1.2:3306 using 1 thread
[Worker000]: reviews.csv: Records: 2068800 Deleted: 0 Skipped: 0 Warnings: 0
99% (47.29 MB / 47.29 MB), 7.27 MB/s
File '/Data/project/source/reviews.csv' (47.29 MB) was imported in 6.9141 sec at 6.84 MB/s
Total rows affected in homestays.reviews_from_mysqlsh: Records: 2068800 Deleted: 0 Skipped: 0 Warnings: 0
Import data to a MySQL server
Obviously, util.importTable also works on a classic (I mean non HeatWave) MySQL instance. In this context, you will most likely need to set local_infile variable to 1. Its default value is OFF:
mysql>
SHOW GLOBAL VARIABLES LIKE 'local_infile';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| local_infile | OFF |
+---------------+-------+
It allows you to avoid the following error:
ERROR: The 'local_infile' global system variable must be set to ON in the target server, after the server is verified to be trusted.
Util.importTable: Invalid preconditions (RuntimeError)
So basically, you’ll need to do something like this:
JS>
\sql SET GLOBAL local_infile = 1;
util.importTable("/Data/project/source/reviews.csv", {schema: "homestays", table: "reviews_from_mysqlsh", dialect: "csv-unix", skipRows: 1, showProgress: true})
\sql SET GLOBAL local_infile = 0;
Turning our attention back to HeatWave — as you may already know, a HeatWave cluster can dramatically accelerate your queries, enabling you to use the familiar MySQL API in analytics scenarios such as data warehousing and lakehousing. To unlock these performance superpowers, you first need to load your data into the HeatWave cluster. Once that’s done, you can fully enjoy the incredible speed and efficiency it brings to your workloads!
Load data into the HeatWave Cluster
To load your data into your HeatWave Cluster from your MySQL table use thesys.heatwave_load stored procedure.
SQL>
CALL sys.heatwave_load(JSON_ARRAY("homestays"), JSON_OBJECT('include_list', JSON_ARRAY('homestays.reviews_from_mysqlsh')));
+------------------------------------------+
| INITIALIZING HEATWAVE AUTO PARALLEL LOAD |
+------------------------------------------+
| Version: 4.31 |
| |
| Load Mode: normal |
| Load Policy: disable_unsupported_columns |
| Output Mode: normal |
| |
+------------------------------------------+
6 rows in set (0.0158 sec)
+-----------------------------------------------------------------------------------------+
| OFFLOAD ANALYSIS |
+-----------------------------------------------------------------------------------------+
| Verifying input schemas: 1 |
| User excluded items: 0 |
| |
| SCHEMA OFFLOADABLE OFFLOADABLE SUMMARY OF |
| NAME TABLES COLUMNS ISSUES |
| ------ ----------- ----------- ---------- |
| `homestays` 0 0 1 table(s) are not loadable |
| |
| No offloadable schema found, HeatWave Auto Load terminating |
| |
| Total errors encountered: 1 |
| Total warnings encountered: 3 |
| Retrieve the associated logs from the report table using the query below: |
| SELECT log FROM sys.heatwave_autopilot_report WHERE type IN ('error', 'warn'); |
| |
+-----------------------------------------------------------------------------------------+
15 rows in set (0.0158 sec)
Oops!! It failed! There is an error. Let’s check it:
SQL>
SELECT log FROM sys.heatwave_autopilot_report WHERE type IN ('error', 'warn');
+-------------------------------------------------------------------------------------------------------------------------+
| log |
+-------------------------------------------------------------------------------------------------------------------------+
| {"error": "Unable to load table without primary key", "table_name": "reviews_from_mysqlsh", "schema_name": "homestays"} |
| {"warn": "1 table(s) are not loadable", "schema_name": "homestays"} |
| {"warn": "No offloadable tables found", "schema_name": "homestays"} |
| {"warn": "No offloadable tables found for given input target"} |
+-------------------------------------------------------------------------------------------------------------------------+
{“error”: “Unable to load table without primary key”, “table_name”: “reviews_from_mysqlsh”, “schema_name”: “homestays”}
Well, I conveniently forgot to mention one important requirement: the table must have a primary key. 😉
If your table doesn’t have a natural or meaningful primary key, no worries — one option is to use Generated Invisible Primary Keys (GIPKs). This allows MySQL to automatically add an invisible primary key behind the scenes.
Please note the SECONDARY_ENGINE=RAPID new clause.
And you can still query your table according to your needs:
SQL>
SELECT COUNT(*) FROM homestays.reviews_from_mysqlsh;
+----------+
| COUNT(*) |
+----------+
| 2068800 |
+----------+
1 row in set (0.1160 sec)
SQL>
EXPLAIN SELECT COUNT(*) FROM homestays.reviews_from_mysqlsh\G
*************************** 1. row ***************************
EXPLAIN: -> Aggregate: count(0) (cost=16.6e+6..16.6e+6 rows=1)
-> Table scan on reviews_from_mysqlsh in secondary engine RAPID (cost=0..0 rows=2.07e+6)
1 row in set, 1 warning (0.0935 sec)
Note (code 1003): Query is executed in secondary engine; the actual query plan may diverge from the printed one
HeatWave’s Auto Parallel Load
HeatWave’s Auto Parallel Load is a key feature within HeatWave that automatically loads data into the HeatWave cluster, without requiring manual intervention or tuning. Data loading is performed using multiple threads across nodes in the HeatWave cluster, significantly speeding up the operation.
And guess what? you already know the command, it is sys.heatwave_load.
To use it, first we need to define the command using JSON syntax. We recommend assigning this JSON structure to a variable, such as @input_list:
The dialect is CSV, so the only information HeatWave’s Auto Parallel Load requires from us is the presence of a header in the file.
And like we have seen previously, run the stored procedure using the CALL statement:
SQL>
CALL sys.heatwave_load(CAST(@input_list AS JSON), NULL);
+------------------------------------------+
| INITIALIZING HEATWAVE AUTO PARALLEL LOAD |
+------------------------------------------+
| Version: 4.31 |
| |
| Load Mode: normal |
| Load Policy: disable_unsupported_columns |
| Output Mode: normal |
| |
+------------------------------------------+
6 rows in set (0.0128 sec)
+--------------------------------------------------------------------------------------------------------------------+
| LAKEHOUSE AUTO SCHEMA INFERENCE |
+--------------------------------------------------------------------------------------------------------------------+
| Verifying external lakehouse tables: 1 |
| |
| SCHEMA TABLE TABLE IS RAW NUM. OF ESTIMATED SUMMARY OF |
| NAME NAME CREATED FILE SIZE COLUMNS ROW COUNT ISSUES |
| ------ ----- -------- --------- ------- --------- ---------- |
| `homestays` `reviews_from_HW_load` NO 45.10 MiB 2 2.07 M |
| |
| New schemas to be created: 0 |
| External lakehouse tables to be created: 1 |
| |
+--------------------------------------------------------------------------------------------------------------------+
10 rows in set (0.0128 sec)
+------------------------------------------------------------------------+
| OFFLOAD ANALYSIS |
+------------------------------------------------------------------------+
| Verifying input schemas: 1 |
| User excluded items: 0 |
| |
| SCHEMA OFFLOADABLE OFFLOADABLE SUMMARY OF |
| NAME TABLES COLUMNS ISSUES |
| ------ ----------- ----------- ---------- |
| `homestays` 1 2 |
| |
| Total offloadable schemas: 1 |
| |
+------------------------------------------------------------------------+
10 rows in set (0.0128 sec)
+-----------------------------------------------------------------------------------------------------------------------------+
| CAPACITY ESTIMATION |
+-----------------------------------------------------------------------------------------------------------------------------+
| Default encoding for string columns: VARLEN (unless specified in the schema) |
| Estimating memory footprint for 1 schema(s) |
| |
| TOTAL ESTIMATED ESTIMATED TOTAL DICTIONARY VARLEN ESTIMATED |
| SCHEMA OFFLOADABLE HEATWAVE NODE MYSQL NODE STRING ENCODED ENCODED LOAD |
| NAME TABLES FOOTPRINT FOOTPRINT COLUMNS COLUMNS COLUMNS TIME |
| ------ ----------- --------- --------- ------- ---------- ------- --------- |
| `homestays` 1 57.57 MiB 192.00 KiB 0 0 0 7.00 s |
| |
| Sufficient MySQL host memory available to load all tables. |
| Sufficient HeatWave cluster memory available to load all tables. |
| |
+-----------------------------------------------------------------------------------------------------------------------------+
12 rows in set (0.0128 sec)
+---------------------------------------------------------------------------------------------------------------------------------------+
| EXECUTING LOAD SCRIPT |
+---------------------------------------------------------------------------------------------------------------------------------------+
| HeatWave Load script generated |
| Retrieve load script containing 2 generated DDL command(s) using the query below: |
| Deprecation Notice: "heatwave_load_report" will be deprecated, please switch to "heatwave_autopilot_report" |
| SELECT log->>"$.sql" AS "Load Script" FROM sys.heatwave_autopilot_report WHERE type = "sql" ORDER BY id; |
| |
| Adjusting load parallelism dynamically per internal/external table. |
| Using current parallelism of 4 thread(s) as maximum for internal tables. |
| |
| Warning: Executing the generated script may alter column definitions and secondary engine flags in the schema |
| |
| Using SQL_MODE: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION |
| |
| Proceeding to load 1 table(s) into HeatWave. |
| |
| Applying changes will take approximately 7.01 s |
| |
+---------------------------------------------------------------------------------------------------------------------------------------+
16 rows in set (0.0128 sec)
+----------------------------------------------------+
| TABLE LOAD |
+----------------------------------------------------+
| TABLE (1 of 1): `homestays`.`reviews_from_HW_load` |
| Commands executed successfully: 2 of 2 |
| Warnings encountered: 0 |
| Table load succeeded! |
| Total columns loaded: 2 |
| Elapsed time: 30.95 s |
| |
+----------------------------------------------------+
7 rows in set (0.0128 sec)
+----------------------------------------------------------------------------------+
| LOAD SUMMARY |
+----------------------------------------------------------------------------------+
| |
| SCHEMA TABLES TABLES COLUMNS LOAD |
| NAME LOADED FAILED LOADED DURATION |
| ------ ------ ------ ------- -------- |
| `homestays` 1 0 2 30.95 s |
| |
| Total errors encountered: 0 |
| Total warnings encountered: 2 |
| Retrieve the associated logs from the report table using the query below: |
| SELECT log FROM sys.heatwave_autopilot_report WHERE type IN ('error', 'warn'); |
| |
+----------------------------------------------------------------------------------+
11 rows in set (0.0128 sec)
The operation completed successfully; however, two warnings were generated. Details regarding these warnings are available in the sys.heatwave_autopilot_report table:
SQL >
SELECT log FROM sys.heatwave_autopilot_report WHERE type IN ('error', 'warn')\G
*************************** 1. row ***************************
log: {"message": "[WARNINGS SUMMARY] Lakehouse Schema Inference had 1 warning(s) out of which 1 were not recorded (due to max_error_count limit or filtering rules)", "table_name": "reviews_from_HW_load", "schema_name": "homestays", "condition_no": 1}
*************************** 2. row ***************************
log: {"message": "[WARNINGS SUMMARY] 1 warning(s) with code: 6095(ER_LH_WARN_INFER_SKIPPED_LINES)", "table_name": "reviews_from_HW_load", "schema_name": "homestays", "condition_no": 2}
Fortunately, nothing critical.
And now you can query your table according to your needs:
SQL>
SELECT COUNT(*) FROM reviews_from_HW_load;
+----------+
| COUNT(*) |
+----------+
| 2068800 |
+----------+
1 row in set (0.1147 sec)
SQL>
EXPLAIN SELECT COUNT(*) FROM homestays.reviews_from_HW_load\G
*************************** 1. row ***************************
EXPLAIN: -> Aggregate: count(0) (cost=16.6e+6..16.6e+6 rows=1)
-> Table scan on reviews_from_HW_load in secondary engine RAPID (cost=0..0 rows=2.07e+6)
1 row in set, 1 warning (0.0933 sec)
Note (code 1003): Query is executed in secondary engine; the actual query plan may diverge from the printed on
Peroration
Whether you’re working with traditional MySQL or taking advantage of the blazing-fast analytics capabilities of HeatWave, importing CSV data doesn’t have to be a bottleneck. With tools like MySQL Shell’s parallel import utility and HeatWave’s Auto Parallel Load, you have flexible, scalable options that fit a variety of use cases — from local file loading to seamless integration with object storage.
By combining these tools with features like Generated Invisible Primary Keys, you can streamline the ingestion process and get your data ready for powerful, real-time analytics with minimal overhead.
So next time you’re staring at a CSV file and a big dataset to analyze, you’re fully equipped to handle it — the MySQL way.
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.
