In this technical blog, we discuss how to access Oracle HeatWave with GenAI from a Visual Builder application using OCI API Gateway and Functions. This approach allows you to leverage the power of HeatWave and generate AI-powered insights without exposing your HeatWave cluster directly to the application. More specifically, we will integrate HeatWave GenAI services to:
- Add generative AI services like natural-language queries into your existing Visual Builder applications; and
- Use your domain specific content while preserving its privacy. Leveraging both unstructured (PDF, PPT, TXT, HTML, DOC documents) and structured content which you might already have in your existing Oracle MySQL database
In the example we see how the GenAI function takes a question and based on the information in the Visual Builder documentation set construct an explanation of what action chains are. We can then ask a follow up questions about testing action chains, which is taken in the context of the previous question and a detailed answer is generated for us.
This application source code can be found at: https://github.com/oracle-samples/vbcs-samples/tree/master/HeatWaveGenAI/resources or you download it as a ZIP file: heatwave-genai-ask-question.zip
Quick Links
- Introduction to HeatWave and GenAI
- Architecture Overview
- Setup HeatWave service
- Develop OCI Function and API Gateway
- Build Visual Builder application
Introduction to HeatWave and GenAI
Oracle HeatWave is a unique in-memory technology that is integrated with Oracle Database to provide high-performance analytics and machine learning capabilities. With HeatWave, you can achieve real-time insights and make data-driven decisions by querying your analytical data at unprecedented speed. GenAI is a set of SQL-based machine learning functions provided by Oracle Database that enable you to build and deploy advanced machine learning models directly within the database. With GenAI, you can harness the power of machine learning without needing to be an expert in the field, as it provides a simple and intuitive SQL interface. By combining HeatWave and GenAI, you accelerate your analytical queries and power your machine learning models, all within the same Oracle Database.
Architecture Overview
We will focus on how to access HeatWave with GenAI from a Visual Builder application. Here's an overview of the architecture:
- Visual Builder Application: This is the front-end application that users interact with. It could be a web or mobile application built using Oracle Visual Builder.
- OCI Functions: OCI Functions provide a serverless execution environment. We will use OCI Functions to create a layer that accesses the HeatWave cluster and exposes only the necessary functionality. Function will be written in NodeJS.
- OCI API Gateway: OCI API Gateway is used to expose OCI Functions to Visual Builder application as concrete REST endpoints. The endpoints support API validation, request and response transformation, CORS, authentication and authorization, and request limiting.
- HeatWave Cluster: This is your Oracle HeatWave cluster where your analytical data resides and where GenAI models are trained and deployed. By using OCI API Gateway and Functions as an intermediary layer, you can control and secure access to your HeatWave cluster. The Visual Builder application communicates only with the OCI API Gateway, which in turn communicates with the HeatWave cluster through OCI Functions.
Architecture diagram:
Note: HeatWave, Functions and API Gateway must be within the same VCN. That allows API Gateway access to Functions, and Functions access to HeatWave, using a private VCN subnet.
The API Gateway will also require a public VCN subnet. That will expose the API Gateway to the Internet and Visual Builder applications (running in the user's browser) will be able to access it.
The blog will demonstrate solution on a Chat application where natural-language queries will be answered from a model created using official Visual Builder product documentation:
Setup HeatWave service
Follow Getting Started with HeatWave GenAI to setup HeatWave DB system. It is as simple as creating a new DB System in your Oracle Cloud account, but pay special attention to the requirements section:
- HeatWave database system must be version 9.0.0 or newer
- add HeatWave Cluster with shape: HeatWave.512GB
- enable HeatWave Lakehouse
Note that the "Oracle Cloud Free Tier" does not support "HeatWave.512GB" cluster shape and therefore cannot be used. Using a wrong cluster shape leads to mysterious errors like
ERROR: 3877 (HY000): "ML003206: The LLM (mistral-7b-instruct-v1) you requested is not available in your account."
It is a good idea to follow the above mentioned document including the "Quickstart: Setting Up a Help Chat" chapter and to test and confirm that HeatWave is set up and can answer questions.
Write down for later use:
- your DB system admin username and password
- IP address of your DB
Accessing and testing HeatWave services
There are several ways to connect to HeatWave DB Systems and start issuing SQL commands. The DB system is not accessible externally to the Internet for security reasons. The easiest is to use Oracle Cloud Shell which is part of your Oracle Cloud console – how to use it and set it up is explained in detail in How To Use The Oracle Cloud Shell To Access MySQL HeatWave. That gives you SQL command line access.
If you want to interact with HeatWave generative AI chat via a graphical interface (instead of SQL command line), then another very convenient way to interact with HeatWave is to install the "MySQL Shell for VS Code" plugin into your VS Code. How to do that is explained in the first chapter of this article: A Quick Guide to MySQL HeatWave with MySQL Shell for VS Code – if steps are not clear watch the animated GIF at the end of the first chapter as it makes it very clear. The VS Code plugin allows users to upload unstructured content and interact with it using natural-language queries. The UI allows to switch models, language or drill into from which snippets of which documents the answer was built from:
Create a new database with your own domain specific content
Similarly like in the QuickStart chapter linked earlier, we will create a new DB and put unstructured content into its vector store and use this database to answer natural-language queries using HeatWave GenAI SQL commands.
Using SQL command line interface, create a new DB for your content and create the task management schema:
create database chat_db; use chat_db; SELECT mysql_task_management_ensure_schema();
In the OCI Console create a new object storage bucket with all content you want to use. That is under Storage -> Object Storage -> Buckets create a new bucket, open it, change its visibility to Public (and allow listing of objects), and upload your documents into the bucket. For example in my case I downloaded official VBS documentation as multiple PDF files and uploaded them into the bucket.
Back in the SQL console, start vector store creation from the files in this bucket. That is, use SQL command:
call sys.VECTOR_STORE_LOAD('oci://your-bucket-name@your-oci-tenancy/*', '{"schema_name": "chat_db", "table_name": "chat_embeddings"}');
And follow instructions printed on screen (and explained in the QuickStart page) to monitor progress. See also VECTOR_STORE_LOAD documentation.
When processing is finished, you can start testing what results the HeatWave GenAI can produce from your content. Try a variety of questions to assess benefits which HeatWave could provide in your specific application case. MySQL Shell for VS Code is very handy for this task. It is a good idea to evaluate the HeatWave at this point before progressing further and update the vector store with additional content if necessary.
HeatWave uses RAG (retrieval augmented generation). It uses the best matching segments from vector store to augment the prompt that is passed to the LLM, allowing the LLM to respond using proprietary information / domain specific content.
Note, that documents can be loaded into different tables and HeatWave chat can be asked to use specific tables to answer questions. This is useful for narrowing down the corpus from which answers will be provided.
Also note that created tables might have suffix "_pdf", "_txt", based on file types in your OCI bucket
Create a DB table to preserve conversation context
To preserve conversation context and allow follow up questions we need to establish a "session". That can be done in many ways and strategy used in this example app is:
- create a DB table and store session data into a row identified by a random unique session key and row id
- return the session key and row id to chat frontend UI so that it can use them in follow up calls
In you SQL console create the table:
CREATE TABLE chat_history ( id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, hash VARCHAR(32), history JSON );
Develop OCI Function and API Gateway
To make HeatWave functionality accessible to Visual Builder applications running in the browser we need to expose it via REST endpoint in OCI. This can be achieved by creating OCI Function. OCI Functions are serverless functions, exposed as REST endpoints, which can be written in many different languages (NodeJS, Python, etc). While OCI Function is a REST endpoint the endpoint is not configurable and it is necessary to create also OCI API Gateway endpoint which will be in front of the OCI Function and which provides configurable authentication, request and response transformation, CORS, routing, URL path, request limiting, etc.
Note: this blog assumes that you are using an existing OCI Comparment and VCN. If not, create those first. No specific configuration is required.
Create OCI Function
Let's start by creating a function. In the OCI console navigate to Developer Services -> Functions -> Applications. Create a new application accepting all the defaults. Inside the new application there is a "Getting started" section explaining individual steps which need to be taken to set up a new function. Follow all of these steps and create the hello-java function example and run it to familiarize yourself with the development process.
Note: the Cloud Shell/Code Editor network needs to be set to the Public. Otherwise function deployment won't be able to fetch dependent docker image(s). If you are using the Cloud Shell or Code Editor with a private network definition (in order to access HeatWave using command line MySQL Shell) you must switch back to the Public network.
When you are ready, follow the "Getting Started" steps to create a new function. That is:
- Launch Cloud Shell
- all the context from previously created hello-java function (steps 2 till 7) should be still set, so just run "fn list apps" to verify you can see your function
- create a new node function and give it a name (I used "ask_question" name): fn init --runtime node ask_question
- cd ask_questions
- deploy the function ("functions" is the name of your OCI Function application – the one returned by "fn list apps"): fn -v deploy --app functions
- open OCI Developer Tools -> Code Editor (instead of Code Shell)
- use Open on Welcome page to open folder corresponding to your newly created function
Result should be folder with 3 files:
Optionally, you could deploy and test that function runs. That is, in the Code Editor open Terminal and execute in it these commands:
- fn -v deploy --app functions
- fn invoke functions ask_question
Which should return "Hello" message JSON.
Implement communication with HeatWave
Now we are going to update the function files with code which calls the HeaWave backend. Pay attention to "xxx" in the code, which needs to be updated with your own values. Replace body of your function's files with this content:
package.json:
{
"name": "ask_question",
"version": "0.0.1",
"description": "xxx",
"main": "func.js",
"author": "xxx",
"license": "Apache-2.0",
"dependencies": {
"oci-common": "^2.96.0",
"oci-secrets": "^2.96.0",
"@fnproject/fdk": ">=0.0.72",
"mysql2": "^3.11.3"
}
}
func.js:
const fdk = require('@fnproject/fdk');
const common = require("oci-common");
const secrets = require("oci-secrets");
const mysql = require('mysql2/promise')
const crypto = require('crypto');
const sql_host = "10.0.1.xxx";
const sql_user = "xxx";
const sql_password_secret_ocid = "ocid1.vaultsecret.oc1.iad.xxx";
const sql_db = "xxx";
/** Fetch securelty stored password for SQL connection from OCI Vault */
async function getSecret(secret_ocid) {
const provider = common.ResourcePrincipalAuthenticationDetailsProvider.builder();
const client = new secrets.SecretsClient({authenticationDetailsProvider: provider});
const secret_content = await client.getSecretBundle({ secretId: secret_ocid});
return Buffer.from(secret_content.secretBundle.secretBundleContent.content, 'base64').toString()
}
/** Generated random unique hash to identity this session */
function generateUniqueId() {
const buf = crypto.randomBytes(16);
return buf.toString('hex');
}
/** Ask Heatwave a question */
async function ask(question, session) {
// fetch password:
const pwd = await getSecret(sql_password_secret_ocid);
// connect to SQL:
const conn = await mysql.createConnection({
host: sql_host,
user: sql_user,
password: pwd,
database: sql_db
});
// reset chat sesh:
let chat_options = {};
let set_chat_options = false;
let result = await conn.execute(`set @chat_options=NULL;`);
// is this is continuation of a previous session?
if (session.hash && session.id) {
// if so then fetch history from a DB table:
const [rows] = await conn.query('select `history` FROM `chat_history` where `id` = ? AND `hash` = ?', [session.id, session.hash]);
if (rows.length > 0) {
chat_options = rows[0].history;
set_chat_options = true;
}
} else {
// no, this is a new session; give it a unique ID and return it to caller:
session.hash = generateUniqueId();
}
// was list of tables to use explicitly specified?
if (session.tables) {
chat_options.tables = session.tables;
set_chat_options = true;
}
// was a specific ML model requested?
if (session.model) {
chat_options.model_options = session.model;
set_chat_options = true;
}
// does context for searching needs to be initialized?
if (set_chat_options) {
result = await conn.query('set @chat_options = ? ;', [JSON.stringify(chat_options)]);
}
// ask Heatwave a question:
result = await conn.execute(`call sys.HEATWAVE_CHAT("${question.replaceAll('"', '\\"')}");`);
session.answer = result[0][0][0].response;
// retrieve current session details:
result = await conn.execute(`select @chat_options;`);
chat_options = result[0][0]["@chat_options"];
// return session details to caller:
chat_options_parsed = JSON.parse(chat_options);
session.chat_history = chat_options_parsed.chat_history;
session.documents = chat_options_parsed.documents;
if (session.id) {
// update existing session record
[result] = await conn.query('REPLACE INTO `chat_history` (`id`, `hash`, `history`) VALUES (?, ?, ?)', [session.id, session.hash, chat_options]);
} else {
// or create a new session record
[result] = await conn.query('INSERT INTO `chat_history` (`hash`, `history`) VALUES (?, ?)', [session.hash, chat_options]);
// return session record id to caller:
session.id = result.insertId;
}
await conn.end();
return session;
}
fdk.handle(async function(input){
console.log("starting: ask");
console.log("input: "+JSON.stringify(input));
const ctx = {};
// initialize context with session attributes:
if (input.hash && input.id) {
ctx.hash = input.hash;
ctx.id = input.id;
console.log("session: "+JSON.stringify(ctx));
}
// initialize context with requested tables:
if (input.tables) {
// "tables": [{"table_name": "demo_embeddings", "schema_name": "demo_db"}]
ctx.tables = input.tables.map(table => {return {"schema_name": sql_db, "table_name": table}});
}
// initialize context with requested model:
if (input.model) {
// {"model_id": "mistral-7b-instruct-v1"}
ctx.model = {model_id: input.model};
}
// ask question:
let response = {};
try {
response = await ask(input.question, ctx);
response.status = 'ok';
} catch (error) {
console.error("caught an error "+error);
console.error(error);
response.stack = error.stack.split(/\r\n|\r|\n/g);
response.error = error.message;
response.details = error;
response.status = 'error';
}
console.log("response: "+JSON.stringify(response));
return response;
})
Note that the function code is illustrative rather than production quality. For example handling of special characters in answer text is very rudimentary and needs to be expanded upon.
There are several values which need to be updated:
- sql_host – IP address of your DB system
- sql_user – username of your admin account
- sql_db – db name (eg "chat_db")
- sql_password_secret_ocid – OCID representing a password secret. It is not a good idea to keep the admin password naked in the script. Instead use OCI Vault to store it safely. That is, in the OCI console open Identity & Security -> Key Management -> Vault and create new vault, open it, create new master encryption key, and under Secrets create a new Secret for your admin password (using master key and "Manual Secret Generation" and entering password as "Plain-Text"). The result will be a new secret with OCID to store in sql_password_secret_ocid variable
The function source code has comments explaining what it does. The main points are:
- the function accepts following input params:
{
question: "Question to be asked",
tables: ["array", "of", "vector store tables to use"], // optional
model: "model name to use", // optional
id: 123, // numeric ID returned from previous call identifying this session; optional
hash: "unique-hash-code" // unique secret hash returned from previous call identifying this session; optional
} - creates SQL connection to HeatWave DFB system
- creates @chat_options context (configure vector tables and model, restore session)
- calls sys.HEATWAVE_CHAT
- stores response in session table and returns results in this format:
{
answer: "answer generated",
chat_history: object[], // array of chat session history as kept by the HeatWave
documents: object[], // snippets of text which was used to compose answer and from which documents they originated
id: 123, // numeric ID representing this session
hash: "unique-hash-code" // unique secret hash identifying this session
}
Test OCI Function
After updating sources of your function you need to deploy it before it can be tested: fn -v deploy --app functions
Once changes were deployed there is one more thing to do: the function is using OCI Vault and we need to grant the function access to this OCI resources (called "secret-family"). To do that:
- as described here, go to Identity -> Domains -> select your identity to which your account belongs (eg Default; this may require switching to Root compartment) -> Dynamic Groups and create here new dynamic group with good descriptive name and matching rule like this:
ALL {resource.type = 'fnfunc', resource.compartment.id = 'ocid1.compartment.oc1..<your compartment id>'} - now, back in your compartment, go to Identity -> Policies and create a new policy granting your function access to OCI resources, eg:
Allow dynamic-group <name of above dynamic group> to use secret-family in compartment <name of your compartment>
Now you can test the function from the Cloud Shell console or the Code Editor terminal:
echo -n '{"question":"Your question placed here","model":"mistral-7b-instruct-v1","tables":["chat_embeddings"]}' | fn invoke functions ask_question
Miscellaneous tips for running OCI Function
Startup
To eliminate the function's slow cold start you can "Enable provisioned concurrency" which will keep the function loaded in memory and the function will respond immediately. Be aware that this will consume computational resources and will increase your OCI bill. So use with caution.
Logging
If function execution fails, you need to enable logger and then open it. There might be a few minutes delay between logs from OCI Function execution propagate into the OCI Console UI. Errors and anything logged from the function using console.log will be visible in the log.
Common Errors
OCI documentation has an excellent document helping with many common problems: Troubleshooting OCI Functions
Create OCI API Gateway
In the Cloud Console navigate to Developer Services -> API Management -> Gateways and create a new gateway (type public, using your existing VCN with a public subnet – if you don't have a VCN with public subnet you need to create one first). In created gateway navigate to Deployments and create a new one:
- Basic Information step:
- name: ask
- prefix: /v1
- CORS:
- Allowed Origins: add your VB instances, eg https://your-vb-instance.oraclecloud.com. If you are developing on a VBS and deploying your app as Web app to a standalone VB instance then you will need to whitelist both of these instances in the CORS list
- Methods: pick "* (wildcard)"
- Authentication step
- Routes
- path: ask
- methods: POST
- backend type: Oracle Functions
- application: your OCI function application
- function name: your function name
When gateway deployment is created, open it and in its detail section copy "Endpoint" URL. Appending routes' path to that URL becomes the URL which we will use from VB application, for example in this example it is: https://<an id>.apigateway.<region>.oci.customer-oci.com/v1/ask
In order for the gateway to be accessible and work we need to:
- open port 443 on VCN public subnet; and
- create policy granting the gateway access to OCI functions
To open port 443 navigate to Virtual Cloud Networks -> your VCN -> open public Subnet -> open Default Security List and add ingress rule for protocol TCP and destination port 443:
To grant the gateway access to OCI function navigate to Identity -> Policies and create a new policy as described in detail here:
Allow any-user to use functions-family in compartment <your compartment name> where
ALL { request.principal.type= 'ApiGateway', request.resource.compartment.id = '<api gateway compartment OCID>' }
Note: it may take sometimes up to 30 minutes for this policy to become effective and the gateway having access to OCI function and being able to return results and not fail.
You should be able to test the gateway URL from a REST client like PostMan using POST Method and JSON payload like this: {"question":"Your question placed here","model":"mistral-7b-instruct-v1","tables":["chat_embeddings"]}
Build Visual Builder application
Simple Visual Builder application to test the endpoint is attached at the end of this chapter. Two aspects will be explained in more detail:
- setting up REST endpoint
- keeping conversation context
Service Connection for API Gateway
In your VB app add a new Service Connection using "Define by Endpoint" option and enter your endpoint URL:
click Create Backend and enter a name (eg "OCI_Gateway"), authentication is None, connection type is "Dynamic, supports CORS".
On next page give service a name (eg "AskQuestion"), click Request -> Body and copy and paste JSON payload we used in earlier function testing:
{"question":"Your question placed here","model":"mistral-7b-instruct-v1","tables":["chat_embeddings"]}
and click Save Example.
Switch to the Test tab and press Send Request – the call should succeed and show a HeatWave response.
Press Create to finish creation of the service connection.
Conversation with HeatWave
This boils down to calling a REST call and storing returned session id and hash and passing them into consequent calls. That is a chain code like this:
const response = await Actions.callRest(context, {
endpoint: 'ask/postV1Ask',
body: {
question: $variables.question,
id: $variables.ask_id,
hash: $variables.ask_hash
},
});
let answer = [""];
if (response.ok) {
const ask = response.body;
if (ask.status === "ok") {
$variables.ask_id = ask.id;
$variables.ask_hash = ask.hash;
answer = ask.answer.split(/\r\n|\r|\n/g);
}
}
To preserve answer formatting the answer is broken by lines.
This application source code can be found at: https://github.com/oracle-samples/vbcs-samples/tree/master/HeatWaveGenAI/resources or you download it as a ZIP file: heatwave-genai-ask-question.zip