Using SQL Gateway with Python, Vector Search, and Interoperability in InterSystems Iris
Part 3 – REST and Interoperability
Now that we have finished the configuration of the SQL Gateway and we have been able to access the data from the external database via python, and we have set up our vectorized base, we can perform some queries. For this in this part of the article we will use an application developed with CSP, HTML and Javascript that will access an integration in Iris, which then performs the search for data similarity, sends it to LLM and finally returns the generated SQL. The CSP page calls an API in Iris that receives the data to be used in the query, calling the integration. For more information about REST in the Iris see the documentation available at https://docs.intersystems.com/irislatest/csp/docbook/DocBook.UI.Page.cls...
The following is the code of the REST API created:
Class Rest.Vector Extends %CSP.REST
{
XData UrlMap
{
<Routes>
<Route Url="/buscar" Method="GET" Call="Buscar" Cors="true"/>
</Routes>
}
ClassMethod Buscar() As %Status
{
set arg1 = %request.Get("arg1")
set sessionID = %request.Get("sessionID")
Set saida = {}
Set obj=##Class(ws.rag.msg.Request).%New()
Set obj.question=arg1
Set obj.sessionId=sessionID
Set tSC=##Class(Ens.Director).CreateBusinessService("ws.rag.bs.Service",.tService)
If tSC
{
Set resp=tService.entrada(obj)
} Else {
Set resp=##Class(ws.rag.msg.Response).%New()
Set resp.resposta=$SYSTEM.Status.GetErrorText(tSC)
}
Set saida = {}
Set saida.resposta=resp.resposta
Set saida.sessionId=sessionID // Devolve o SessionId que chegou
//
Write saida.%ToJSON()
Quit $$$OK
}
}
Once the REST API code is created, we need to create the application configuration in the Administration->System Administration->Security->Web Applications Portal:
In the CSP application, in Javascript, we then have the API call:
...
async function chamaAPI(url, sessionID)
{
var div = document.getElementById('loader');
div.style.opacity=1;
fetch(url)
.then(response => {
if (!response.ok) {
throw new Error('Erro na resposta da API');
}
return response.json();
})
.then(data => {
incluiDIVRobot(data.resposta, data.sessionID);
})
.catch(error => {
incluiDIVRobot('Erro na chamada da API:: ' + error, sessionID);
});
}
//
const url = 'http://' + 'localhost' + '/api/vector/buscar?arg1=' + texto + '&sessionID=' + sessionID;
chamaAPI(url, sessionID);
...
The CSP application then receives the user's request (e.g., "what's the lowest temperature recorded?") and calls the REST API.
The REST API in turn calls an integration in Iris composed of Service, Process and Operation. In the Operation layer we have the LLM call, which is made by a python method of a class. By seeing the integration trace we can see the whole process taking place.
For more information on using productions in Iris, see the documentation available at https://docs.intersystems.com/irislatest/csp/docbook/DocBook.UI.Page.cls...
Below are the codes used in the BS, BP and BO layers:
The following is the BS (Service) code:
Class ws.rag.bs.Service Extends Ens.BusinessService
{
Parameter SERVICENAME = "entrada";
Method entrada(pInput As ws.rag.msg.Request) As ws.rag.msg.Response [ WebMethod ]
{
Set tSC=..SendRequestSync("bpRag",pInput,.tResponse)
Quit tResponse
}
}
And the BP Code (Process)
Class ws.rag.bp.Process Extends Ens.BusinessProcessBPL [ ClassType = persistent, ProcedureBlock ]
{
/// BPL Definition
XData BPL [ XMLNamespace = "http://www.intersystems.com/bpl" ]
{
<process language='objectscript' request='ws.rag.msg.Request' response='ws.rag.msg.Response' height='2000' width='2000' >
<sequence xend='200' yend='350' >
<call name='boRag' target='boRag' async='0' xpos='200' ypos='250' >
<request type='ws.rag.msg.Request' >
<assign property="callrequest" value="request" action="set" languageOverride="" />
</request>
<response type='ws.rag.msg.Response' >
<assign property="response" value="callresponse" action="set" languageOverride="" />
</response>
</call>
</sequence>
</process>
}
Storage Default
{
<Type>%Storage.Persistent</Type>
}
}
And the BO Code (Operation):
Class ws.rag.bo.Operation Extends Ens.BusinessOperation [ ProcedureBlock ]
{
Method retrieve(pRequest As ws.rag.msg.Request, Output pResponse As ws.rag.msg.Response) As %Library.Status
{
Set pResponse=##Class(ws.rag.msg.Response).%New()
Set pResponse.status=1
Set pResponse.mensagem=”OK”
Set pResponse.sessionId=..%SessionId
Set st=##Class(Vector.Util).RetrieveRelacional(“odbc_work”,pRequest.question,pRequest.sessionId)
Set pResponse.resposta=st
Quit $$$OK
}
XData MessageMap
{
<MapItems>
<MapItem MessageType=”ws.rag.msg.Request”>
<Method>retrieve</Method>
</MapItem>
</MapItems>
}
}
And the Request and Response classes:
Request:
Class ws.rag.msg.Request Extends Ens.Request
{
Property collectionName As %String;
Property question As %String(MAXLEN = "");
Property sessionId As %String;
}
Response:
Class ws.rag.msg.Response Extends Ens.Response
{
Property resposta As %String(MAXLEN = "");
Property status As %Boolean;
Property mensagem As %String(MAXLEN = "");
Property sessionId As %Integer;
}
And the Production class:
Class ws.rag.Production Extends Ens.Production
{
XData ProductionDefinition
{
<Production Name="ws.rag.Production" LogGeneralTraceEvents="false">
<Description>Produção do Rag DEMO</Description>
<ActorPoolSize>2</ActorPoolSize>
<Item Name="ws.rag.bs.Service" Category="rag" ClassName="ws.rag.bs.Service" PoolSize="0" Enabled="true" Foreground="false" Comment="" LogTraceEvents="false" Schedule="">
</Item>
<Item Name="bpRag" Category="rag" ClassName="ws.rag.bp.Process" PoolSize="1" Enabled="true" Foreground="false" Comment="" LogTraceEvents="false" Schedule="">
</Item>
<Item Name="boRag" Category="rag" ClassName="ws.rag.bo.Operation" PoolSize="1" Enabled="true" Foreground="false" Comment="" LogTraceEvents="false" Schedule="">
</Item>
</Production>
}
}
When executed, the integration keeps the Requests and Responses stored, allowing traceability on the bus, as we see in the following trace:
We can see in the trace, for example, that the time it took for the LLM call to send the data and return the requested SQL was approximately 10s:
We can see the return of the BO after treating the LLM's response in the python code, to our question:
Thus, through the traceability of the Iris interoperability layer, we can see the entire flow of information trafficked, the elapsed times, any failures and, if applicable, reprocess any call, if necessary.
The call from the BO to the python method passes the request made by the user. The python code through vector search finds the most similar records and sends them to LLM along with the user's request (in this case the model in our table), the conversation history (if it exists) and the prompt that are the guidelines to guide the LLM's performance.
The LLM then generates the SQL which is returned to the method in python. The code then executes SQL and formats the response to the expected pattern, creating the presentation lists, charts, or downloads according to the user's expected feedback.
Thus, through the created CSP application, we can request various information, such as answer tables:
Or graphics:
Or even, the download of information:
To download, download and open the file we have the requested data:
These examples show reading the data from the external table with the Iris SQL Gateway and using it with code written in python. In this way we can use the full potential of the data, which does not need to be stored inside Iris. Imagine being able to set up an analysis station that collects data from various systems and provides information for decision making.
We can, for example, have dashboards visualizing data from the various environments that make up a company's ecosystem, predictions based on ML algorithms, RAG to facilitate data collection, and much more.
Iris can be responsible for accessing, processing and making available the data of the various environments, with control, security and traceability, thanks to the interoperability characteristics, being able to use code in COS and python, and be accessed through codes in R, C, Java and much more. And all this within the same product and without the need to duplicate or move data between environments.
