dasini.net – Diary of a MySQL expert

This article builds upon the concepts introduced in my previous blog post, HeatWave GenAI: Your AI-Powered Content Creation Partner. For a deeper understanding, consider reading that article first.

HeatWave offered different solutions for your different workload:

• HeatWave MySQL: built on MySQL, for your transactional needs.
• HeatWave Analytics: get real-time analytics on your data.
• HeatWave Lakehouse: query data in various formats in object storage
• HeatWave AutoML: automate the pipeline to build, train, and explain ML models
• HeatWave GenAI: integrated & automated GenAI with in-database LLMs

In this article you’ll experience the power of In-database translation with HeatWave GenAI. I am using HeatWave 9.0.1.

As demonstrated in my previous article, HeatWave GenAI: Your AI-Powered Content Creation Partner, HeatWave GenAI streamlines content generation by seamlessly integrating LLMs into the database. This innovative approach eliminates the complexities of external connections, allowing for effortless AI integration via straightforward SQL queries.

While HeatWave GenAI excels at generating English text, its capabilities extend to translation as well. Let’s explore how we can effectively translate English content into French using this powerful tool.

Load the LLM in HeatWave memory

Use the ML_MODEL_LOAD stored procedure: CALL sys.ML_MODEL_LOAD(‘<LLM>’, NULL);
The model needs to be loaded only once before generating text. It remains accessible within HeatWave as long as the service is running. This means you don’t have to reload the model for each text generation.

At the time of writing (August 2024) the available models are Mistral 7B (mistral-7b-instruct-v1) and Llama 2 (llama2-7b-v1).

-- Load Mistral 7B model
mysql>
CALL sys.ML_MODEL_LOAD('mistral-7b-instruct-v1', NULL);

Define your prompt in natural language

We obviously need a text to translate:

mysql> 
-- Text for Translation
SET @ENtext = "Dracula is a gothic horror novel by Bram Stoker, published on 26 May 1897. An epistolary novel, the narrative is related through letters, diary entries, and newspaper articles. It has no single protagonist and opens with solicitor Jonathan Harker taking a business trip to stay at the castle of a Transylvanian nobleman, Count Dracula. Harker escapes the castle after discovering that Dracula is a vampire, and the Count moves to England and plagues the seaside town of Whitby. A small group, led by Abraham Van Helsing, investigate, hunt and kill Dracula.";

Text extracted from Wikipedia: https://en.wikipedia.org/wiki/Dracula

With the text ready for translation, we need to provide clear instructions to the model about the desired output.
Actually, the most difficult part is here. The quality of the translation will be highly impacted by the quality of your prompt.

For now, let’s try a very simple prompt like: “Translate the following text into French, ensuring natural and grammatically correct phrasing“.
It will be concatenated (CONCAT) with the provided English text :

mysql> 
-- Enhanced prompt
SET @finalPrompt = CONCAT("Translate the following text into French, ensuring natural and grammatically correct phrasing: ", @ENtext);

Inference setting

Here, you select the desired task, choose the appropriate model, and fine-tune parameters to influence the output.

mysql> 
-- Inference setting
SET @inferenceSetup  = '{"task": "generation", "model_id": "mistral-7b-instruct-v1"}';

For simplicity, this example uses default parameters. A more complex example is provided below.

Generate the translation using HeatWave GenAI

And finally, send the enhanced prompt to the LLM using the ML_GENERATE function. The second parameter of this function is a JSON object that allows you to specify the task, the LLM, and the tuning options.

mysql>
-- Translation
SELECT sys.ML_GENERATE(@finalPrompt, @inferenceSetup)\G

*************************** 1. row ***************************
sys.ML_GENERATE(@finalPrompt, @inferenceSetup): {"text": " Réponse : Le roman gothique horreur de Bram Stoker, intitulé « Dracula », a été publié le 26 mai 1897. C'est un roman épistolaire dont la narration se fait par les lettres, les entrées dans le journal et les articles de presse. Il n'a pas d'héroïne principale et commence avec l'avocat Jonathan Harker qui effectue une visite d'affaires au château d'un nobleman transylvanien, le comte Dracula. Harker s'évade du château après avoir découvert que Dracula est un vampire, et le comte se rend en Angleterre et pille la ville côtière de Whitby. Un petit groupe, dirigé par Abraham Van Helsing, enquête, chasse et tue Dracula."

The output in a JSON that is not really nice to read.
But SQL is very powerful and you can easily improve the output using JSON_TABLE function.

SELECT TranslationFR AS TranslationFR FROM ( SELECT sys.ML_GENERATE(@finalPrompt, @inferenceSetup) AS textGen ) AS dt INNER JOIN JSON_TABLE ( dt.textGen, "$" COLUMNS( TranslationFR text PATH "$.text") ) AS jt \G

*************************** 1. row ***************************
TranslationFR:  Réponse : Le roman gothique horreur de Bram Stoker, intitulé « Dracula », a été publié le 26 mai 1897. C'est un roman épistolaire dont la narration se fait par les lettres, les entrées dans le journal et les articles de presse. Il n'a pas d'héroïne principale et commence avec l'avocat Jonathan Harker qui effectue une visite d'affaires au château d'un nobleman transylvanien, le comte Dracula. Harker s'évade du château après avoir découvert que Dracula est un vampire, et le comte se rend en Angleterre et pille la ville côtière de Whitby. Un petit groupe, dirigé par Abraham Van Helsing, enquête, chasse et tue Dracula.

An alternative is the following query:

SELECT TranslationFR->>"$.text" AS "TranslationFR" FROM (SELECT sys.ML_GENERATE(@finalPrompt, @inferenceSetup) AS TranslationFR ) AS dt \G

*************************** 1. row ***************************
TranslationFR:  Réponse : Le roman gothique horreur de Bram Stoker, intitulé « Dracula », a été publié le 26 mai 1897. C'est un roman épistolaire dont la narration se fait par les lettres, les entrées dans le journal et les articles de presse. Il n'a pas d'héroïne principale et commence avec l'avocat Jonathan Harker qui effectue une visite d'affaires au château d'un nobleman transylvanien, le comte Dracula. Harker s'évade du château après avoir découvert que Dracula est un vampire, et le comte se rend en Angleterre et pille la ville côtière de Whitby. Un petit groupe, dirigé par Abraham Van Helsing, enquête, chasse et tue Dracula.

Although the translation isn’t perfect, French speakers should grasp the gist, even if it reads like a particularly polite Advance-fee scam. 😀

Let’s see how to improve that!

Areas for improvement

You now have all the essential components to implement real-time translation within your applications.

Although the generated output may not always align with expectations, you can refine results by adjusting the prompt or modifying inference parameters.

Inference parameters for text generation include:

• task
• model_id
• temperature
• max_tokens
• top_k
• top_p
• repeat_penalty
• stop_sequences
• token_likelihoods
• echo
• logprobs
• stream
• context

While prompt engineering is a critical skill, it’s beyond the scope of this article. I strongly recommend dedicating time to mastering this discipline for optimal results.

Let’s try to do some fine tuning.

The text for translation is the same:

-- Text for Translation
SET @ENtext = "Dracula is a gothic horror novel by Bram Stoker, published on 26 May 1897. An epistolary novel, the narrative is related through letters, diary entries, and newspaper articles. It has no single protagonist and opens with solicitor Jonathan Harker taking a business trip to stay at the castle of a Transylvanian nobleman, Count Dracula. Harker escapes the castle after discovering that Dracula is a vampire, and the Count moves to England and plagues the seaside town of Whitby. A small group, led by Abraham Van Helsing, investigate, hunt and kill Dracula.";

The enhanced prompt contains more information:
You are an expert translator from English to French. Your task is to translate the original text from English to French. Ensure the French translation is accurate, natural-sounding, and grammatically correct. Preserve the original text’s meaning, style, and context. Only generate the French translation of the original text. \n – Original Text: “, @ENtext, ” \n – French Translation:

-- Enhanced prompt
SET @finalPrompt = CONCAT("You are an expert translator from English to French. Your task is to translate the original text from English to French. Ensure the French translation is accurate, natural-sounding, and grammatically correct. Preserve the original text's meaning, style, and context. Only generate the French translation of the original text. \n  - Original Text: ", @ENtext, " \n  - French Translation: "); 

I modified the inference parameters – temperature, repeat_penalty, top_k and top_p:

-- Inference setting
SET @inferenceSetup  = '{"task": "generation", "temperature": 0.9, "repeat_penalty": 1, "top_k": 0, "top_p": 0.2, "model_id": "mistral-7b-instruct-v1"}';

And finally, generate the translation:

-- Translation
SELECT TranslationFR->>"$.text" AS "TranslationFR" FROM (SELECT sys.ML_GENERATE(@finalPrompt, @inferenceSetup) AS TranslationFR ) AS dt \G

*************************** 1. row ***************************
TranslationFR:  Dracula est un roman gothique d'horreur de Bram Stoker, publié le 26 mai 1897. C'est un roman épistolaire, la narration se fait par des lettres, des entrées de journal et des articles de journaux. Il n'a pas de héros principaux et commence par le soliciteur Jonathan Harker qui se rend à l'abbaye du comte Dracula, un nobleman de Transylvanie. Harker s'évade de l'abbaye après avoir découvert que Dracula est un vampire, et le comte s'installe en Angleterre et pille la ville côtière de Whitby. Un petit groupe, dirigé par Abraham Van Helsing, enquête, chasse et tue Dracula.

The results show an improvement. Further refinements can be achieved by optimizing inference parameters and prompt engineering.

Peroration

This article explored the use of HeatWave GenAI for text translation. By integrating LLMs into the database, users can efficiently translate text. While this article provided a basic overview, further experimentation with prompts and parameters can enhance results.

By leveraging the power of in-database AI, organizations can unlock new opportunities for global communication and collaboration.

Stay tuned for more insights!

Follow me on Linkedin

Watch my videos on my YouTube channel and subscribe.

My Slideshare account.

My Speaker Deck account.

Thanks for using HeatWave & MySQL!

Filed under:
AI, HeatWave by Olivier DASINI