Oracle HeatWave has announced a very exciting new feature – HeatWave GenAI which brings LLMs right to the database. Because the LLM is available right in the database developers can seamlessly apply GenAI capabilities to structured and unstructured data with just a simple SQL query.

In this blog we review the e-commerce demo created to show just how this easily this can be done. The demo application covers two critical e-commerce scenarios:

  1. An automatically generated product review summary
  2. Natural language translations for product descriptions and the product review summary

Before reading further, feel free to watch the demo

The Application

The following screenshots show the main store front of the application and the product description page in both English and Spanish translation versions:

The Architecture

The architecture for this application is very simple. HeatWave GenAI does all of the heavy lifting so we only need to pick a front-end web server and a REST middleware layer.  HeatWave with GenAI does the rest.  The components in this demo are:

  • Next.js Front-End
  • Strapi Middleware
  • HeatWave with GenAI

The resulting architecture is depicted below:

HeatWave GenAI e-commerce architecture diagram

Having HeatWave GenAI right in our database eliminates a whole host of problems that might otherwise arise. Our architecture is simplified radically. We don’t need to worry about how to access an external LLM, or how to transfer the data to the LLM or how we will scale the solution. These are all easily accomplished with HeatWave.  And once in production, with so many fewer moving parts our overall Devops cost is significantly reduced due to the reduced  footprint.

DEFINE the schema

The demo application has the following schema:

  • Languages
  • Products
  • Product Descriptions
  • Customers
  • Reviews
  • Orders
  • Order Items

It also has the following stored procedures for generating sample data, providing the review summary and translation features:

  • INSERT_REVIEWS
  • SUMMARIZE_REVIEWS
  • TRANSLATE_DESCRIPTIONS
  • TRANSLATE

 

INSERT_REVIEWS

To make a realistic demo, we needed plenty of reviews for the summary task. But to generate all the reviews a problem quickly surfaces. We need “real” reviews and even with our trivial dataset of 20 customers and 10 products writing a realistic review for every customer for every product means we’d have to write as many as 200 reviews! This is not something we can accomplish easily.

It turns out that HeatWave GenAI can be enlisted in our endeavor.  To generate all of the reviews in our sample data set we use a simple SQL stored procedure that gathers all the orders per customer joined with the products and customers table. 

From this we generate a review for each unique [customer, product] pair. So we created a temporary table with one row per review and insert a prompt for the LLM.  The query and insertion details are not important, but the prompt that generates the review is.  Here it is:

 


 CONCAT(
    'You are ',
    c.first_name, ' ', c.last_name, '.',
    '\nWe have the following observations about them: ',
    c.personality,
    '\nYou are asked to review a product on their behalf with the following details: ',
        '\nProduct name: ', p.name, ' of size: ', pd.size,
        ', of material: ', pd.material, ', and has washing instructions like: ',
        pd.washing_instructions,
        '. Additionally the description of the product is as follows: ', pdg.description
    '\nWrite the review as if you rate it ',  FLOOR (1 + 5 * RAND(51)), ' out of 5 stars',
    '\nEnsure that the review length is no longer than 2 sentences.',
    '\nDo not include any product details in the review or mention the company',
    '\nin the review and do not reveal that you are writing the review on their behalf.'
)

Where:

  • c.first_name and c.last_name correspond to the customer name
  • p.name the product name
  • pd.material the product description material
  • pd.washing_instructions the product washing instructions
  • and FLOOR(1+5*RAND(51)) a randomized rating from 1-5

 

 

SUMMARIZE_REVIEWS

One of the most exciting features in the demo is the automatically generated product review summary.  It also happens to be one of the easier parts to construct too!

The stored procedure that accomplishes this task is pretty simple. 

First we retrieve all the reviews for the given product  and run a cohere summarization task across them. 

Next if the requested language is not in English, then use the TRANSLATE routine to translate the output into the requested language (we’ll look at the TRANSLATE function shortly). Here’s the result:

 

 

-- PROCEDURE SUMMARIZE_REVIEWS
--
-- Create a summary from product reviews and translate into the requested language
--

DROP PROCEDURE IF EXISTS SUMMARIZE_REVIEWS;
DELIMITER //
CREATE PROCEDURE SUMMARIZE_REVIEWS(
    IN product_id_requested INT,
    IN lang VARCHAR(64)
)

BEGIN
    DECLARE summary VARCHAR(1024);
    SET SESSION group_concat_max_len = 4000;

    -- Load the cohere model
    CALL sys.ML_MODEL_LOAD('cohere.command', NULL);
   
    -- Define the LLM prompt
    SET @prompt = GROUP_CONCAT(review_text);

    -- Define the cohere task
    SET @task = '{"task": "summarization", "temperature": 0, "extractiveness": "LOW", "format": "PARAGRAPH", "length": "AUTO", "model_id": "cohere.command"}';

    -- Call the LLM and trim the response
    SELECT JSON_UNQUOTE(JSON_EXTRACT(sys.ML_GENERATE(@prompt, @task), '$.text'))
        INTO summary
        FROM reviews
        WHERE product_id = product_id_requested
        GROUP BY product_id;

    -- If the requested language is 'en' return it directly
    IF (lang = 'en') THEN
        SELECT summary as summary;

    -- Otherwise translate into the requested language
    ELSE
        CALL TRANSLATE(summary, lang, @translated);
        SELECT @translated as summary;
    END IF;
END //
DELIMITER ;

TRANSLATE_DESCRIPTIONS

Since product descriptions rarely change, we model product descriptions as statically defined data. We start by defining the product descriptions in English and then enlist HeatWave GenAI to translate them all into the target languages.  The following is the stored procedure for translating descriptions:

-- PROCEDURE TRANSLATE_DESCRIPTIONS
--
-- Generate all product descriptions starting with the
-- english product descriptions and call translate on each
--
DROP PROCEDURE IF EXISTS TRANSLATE_DESCRIPTIONS;
DELIMITER //
CREATE PROCEDURE TRANSLATE_DESCRIPTIONS()
BEGIN
    DECLARE c VARCHAR(64);
    DECLARE d VARCHAR(104);

    -- Load the LLM model
    CALL sys.ML_MODEL_LOAD('mistral-7b-instruct-v1', NULL);

    -- Remove any prior translations
    DELETE from productdescriptions where language_code <> 'en';

    -- Select all descriptions into a materialized view
    DROP TABLE IF EXISTS description_translations;
    CREATE TEMPORARY TABLE description_translations AS
        SELECT DISTINCT p.id as product_id, l.language_code, pdg.description
        FROM languages l, products p
        LEFT JOIN productdescriptions pdg ON p.id = pdg.product_id;

    -- Remove all the product descriptions so we can re-insert
    DELETE from productdescriptions;
    ALTER TABLE productdescriptions AUTO_INCREMENT = 1;

    -- Translate all the rows with the LLM and insert back into original table
    INSERT INTO productdescriptions (product_id, language_code, description)
    SELECT
        product_id,
        language_code,
        TRANSLATE_PROCESS_JSON(sys.ML_GENERATE(
            TRANSLATE_PROMPT(description, language_code),
            @TRANSLATE_TASK
        )) AS description
    FROM description_translations;
END //
DELIMITER ;

TRANSLATE

The translation routine simply calls the HeatWave GenAI capability with a prompt that instructs it to translate the input text for us. 

-- PROCEDURE TRANSLATE
--
-- Translate the input text into the requested language
--
DROP PROCEDURE IF EXISTS TRANSLATE;
DELIMITER //
CREATE PROCEDURE TRANSLATE(
    IN in_text VARCHAR(1024),
    IN lang VARCHAR(64),
    OUT translated VARCHAR(1024)
)
BEGIN
    -- Load the mistral model
    CALL sys.ML_MODEL_LOAD('mistral-7b-instruct-v1', NULL);

    -- Invoke the LLM
    SELECT TRANSLATE_PROCESS_JSON(sys.ML_GENERATE(
        TRANSLATE_PROMPT(in_text, lang),
        @TRANSLATE_TASK
    )) INTO translated;
END //
DELIMITER ;

We separately define the translation prompt and task so that they can be re-used in other stored procedures:

-- FUNCTION TRANSLATE_PROMPT
--
-- Defines the prompt used for translation
--

DROP FUNCTION IF EXISTS TRANSLATE_PROMPT;
CREATE FUNCTION TRANSLATE_PROMPT(in_text VARCHAR(1024), lang VARCHAR(64))
RETURNS VARCHAR(1024)
RETURN CONCAT(
    'Translate the Original Text to ', lang, '.',
    'The translation needs to be formal and meaningful,',
    ' it needs to have the right grammar and spelling.',
    '\n  - Original Text: "', in_text, '"',
    '\n  - ', lang, ' Translation:'
);

-- TRANSLATE TASK
--
SET @TRANSLATE_TASK = '{"task": "generation", "temperature": 0, "repeat_penalty": 1, "top_k": 0, "max_tokens": 800, "model_id": "mistral-7b-instruct-v1"}';

 

BENEFITS

Key benefits of the HeatWave GenAI capability include:

  • Easily incorporate LLM models into the application with just simple SQL queries

  • Seamless integration with the database means no complex moving parts

  • The system architecture is simplified and easily managed

  • The application is easily scaled for performance by taking advantage of HeatWave’s scale-out architecture

  • Data provided to the LLMs is always up to date, thanks to the in-DB LLMs, which colocate data and processing

CONCLUSION

The architectural simplicity and scalability that HeatWave GenAI provides is transformative. Having the power of LLMs right in the database means that developers can quickly and easily adopt GenAI into existing or new applications without having to build out complex or expensive architectures.

Since the HeatWave GenAI capability scales easily to match both large data and high-performance requirements the feature is enterprise ready out of the box.

 

For more information, check out the following: