Talk to Your Data: A UDF That Speaks Your Language

SELECT u.name, COUNT(o.id) AS total_orders
FROM `commerce`.sales.users AS u
JOIN `commerce`.sales.orders AS o ON u.id = o.user_id
WHERE o.status = "completed"
AND DATE_DIFF_STR(NOW_STR(), o.order_date, "day") <= 30
GROUP BY u.name
ORDER BY total_orders DESC
LIMIT 5;

SELECT u.name, COUNT(o.id) AS total_orders

FROM `commerce`.sales.users AS u

JOIN `commerce`.sales.orders AS o ON u.id = o.user_id

WHERE o.status = "completed"

AND DATE_DIFF_STR(NOW_STR(), o.order_date, "day") <= 30

GROUP BY u.name

ORDER BY total_orders DESC

LIMIT 5;

La consulta anterior proporciona información valiosa a partir de los datos almacenados en Couchbase sobre los cinco usuarios que han generado más pedidos completados en los últimos 30 días. Pero, ¿qué pasa si no eres un desarrollador avanzado de SQL++ y necesitas las respuestas antes de las 11 de la noche para un informe? Entonces tendrás que esperar a que un desarrollador escriba una consulta SQL++ y te dé las respuestas.

Como alternativa, considere un caso en el que necesite realizar una depuración ad hoc para abordar cuestiones como:

¿Hay algún documento en el que falte la fecha de entrega del pedido?
¿Significa eso que el pedido se canceló? ¿O es que extraviamos el pedido y nunca se entregó? ¿O todo estaba bien, pero simplemente se nos olvidó añadir el valor order_delivered en el campo?

En este caso, no solo hay que buscar en el campo order_delivered, sino también en order_cancelled o investigar los comentarios para averiguar si se extravió, etc. Por lo tanto, la consulta que hay que escribir no es sencilla ni directa.

SELECT 
    o.orderId,
    o.orderDate,
    o.order_cancelled,
    o.order_delivered,
    o.comments,
    
    CASE
        WHEN o.order_cancelled = TRUE THEN "Order was cancelled"
        WHEN ANY c IN o.comments SATISFIES LOWER(c) LIKE "%misplac%" 
                                    OR LOWER(c) LIKE "%lost%" THEN "Order may have been misplaced"
        WHEN ANY c IN o.comments SATISFIES LOWER(c) LIKE "%deliver%" THEN "Delivered but field not updated"
        ELSE "Reason unknown — investigate"
    END AS reason
FROM `commerce`.`sales`.`orders` AS o
WHERE o.order_delivered IS MISSING OR o.order_delivered IS NULL;

SELECT

o.orderId,

o.orderDate,

o.order_cancelled,

o.order_delivered,

o.comments,

CASE

WHEN o.order_cancelled = TRUE THEN "Order was cancelled"

WHEN ANY c IN o.comments SATISFIES LOWER(c) LIKE "%misplac%"

OR LOWER(c) LIKE "%lost%" THEN "Order may have been misplaced"

WHEN ANY c IN o.comments SATISFIES LOWER(c) LIKE "%deliver%" THEN "Delivered but field not updated"

ELSE "Reason unknown — investigate"

END AS reason

FROM `commerce`.`sales`.`orders` AS o

WHERE o.order_delivered IS MISSING OR o.order_delivered IS NULL;

En tales casos, sería útil contar con un asistente confiable disponible las 24 horas del día, los 7 días de la semana, para obtener todas estas respuestas. La UDF descrita en este blog es ese asistente. Acepta sus preguntas de la forma más natural y devuelve los resultados en JSON. Entre bastidores, se conecta al modelo que usted elija, junto con su clave API, para convertir sus pensamientos en SQL++ y luego ejecutarlos. Y todo lo que necesita para invocar a este asistente es utilizar la UDF.

SELECT NL2SQL(
  [“`commerce`.`sales`.`orders`”],
  "Are there any documents where the order_delivered date is missing?and if so why?",
  "",
  "https://api.openai.com/v1/chat/completions",
  "gpt-4o-2024-05-13"
) ;

SELECT NL2SQL(

[“`commerce`.`sales`.`orders`”],

"Are there any documents where the order_delivered date is missing?and if so why?",

"",

"https://api.openai.com/v1/chat/completions",

"gpt-4o-2024-05-13"

) ;

Cómo funciona

1. Configure la biblioteca.
Primero, cree una biblioteca JavaScript utilizada por la UDF.

Biblioteca:

/*
input:

keyspaces: an array of strings, each string represents a keyspaces "bucket.scope.collection" with proper escaping using grave-accent quote wherever required
prompt: users natural language request
apikey: your openai api key
model: string representing the model's name, see https://platform.openai.com/docs/api-reference/completions/create#completions-create-model for more details
 
output:
chat-completions api response with the generated sql statement
*/
 
 
 
function inferencer(k) {
   var infq = N1QL("SELECT t.properties FROM(INFER "+k+ ") as t") ;
   var res=[]
   for(const doc of infq) {
       res.push(doc)
   }
  
    return res[0];
}
 
 
function nl2sql(keyspaces, prompt, apikey, modelapi, model) {
  
   
   collectionSchema = {}
   for(const k in keyspaces) {
       c = inferencer(keyspaces[k])
       collectionSchema[keyspaces[k]] = c
   }
  
   collectionSchemaStr = JSON.stringify(collectionSchema)
  
   promptContent = `Information:\nCollection's schema: ${collectionSchemaStr}\n\nPrompt: \"${prompt}\"\n\nThe query context is set.\n\nBased on the above Information, write valid SQL++ and return only the statement and no explanation. For retrieval, use aliases. Use UNNEST from the FROM clause when appropriate. \n\nIf you're sure the Prompt can't be used to generate a query, first say \"#ERR:\" and then explain why not.`
  
    
    data = {"messages":[{"role":"system","content":"You are a Couchbase Capella expert. Your task is to create valid queries to retrieve or create data based on the provided Information.\n\nApproach this task step-by-step and take your time."},{"role":"user","content":promptContent}],
        "model": model,
        "temperature":0,
        "max_tokens":1024,
        "stream":false}

    
    
var dataStr = JSON.stringify(data)
    .replace(/\\/g, "\\\\")    // escape backslashes
    .replace(/"/g, '\\"');     // escape quotes
   
    var completionsurl = modelapi
  
   var q= `SELECT CURL("${completionsurl}", {
        "request": "POST",
        "header": ["Authorization: Bearer ${apikey}", "Content-type: application/json"],
        "data": "${dataStr}"
    }) AS result;`
   
    var completionsq = N1QL(q);
       
    
 
    var res = []
   for(const doc of completionsq) {
       res.push(doc);
     }
   
  try {
    content = res[0].result.choices[0].message.content    
  } catch(e) {
    return res;  
  } 
   
  stmt = content.trim().substring(7, content.length-4)
   
  isSelect = (stmt.substring(0,6).toLowerCase())==="select"
  if(isSelect === false){
      return {
          "generated_statement": stmt
      }
  }
  
  var runq = N1QL(stmt);
   
   var rrun = []
  for(const doc of runq) {
      rrun.push(doc)
  }
  
  return {
      "generated_statement": stmt,
      "results": rrun
  }
    
}

input:

keyspaces: an array of strings, each string represents a keyspaces "bucket.scope.collection" with proper escaping using grave-accent quote wherever required

prompt: users natural language request

apikey: your openai api key

model: string representing the model's name, see https://platform.openai.com/docs/api-reference/completions/create#completions-create-model for more details

output:

chat-completions api response with the generated sql statement

function inferencer(k) {

var infq = N1QL("SELECT t.properties FROM(INFER "+k+ ") as t") ;

var res=[]

for(const doc of infq) {

res.push(doc)

}

return res[0];

}

function nl2sql(keyspaces, prompt, apikey, modelapi, model) {

collectionSchema = {}

for(const k in keyspaces) {

c = inferencer(keyspaces[k])

collectionSchema[keyspaces[k]] = c

}

collectionSchemaStr = JSON.stringify(collectionSchema)

promptContent = `Information:\nCollection's schema: ${collectionSchemaStr}\n\nPrompt: \"${prompt}\"\n\nThe query context is set.\n\nBased on the above Information, write valid SQL++ and return only the statement and no explanation. For retrieval, use aliases. Use UNNEST from the FROM clause when appropriate. \n\nIf you're sure the Prompt can't be used to generate a query, first say \"#ERR:\" and then explain why not.`

data = {"messages":[{"role":"system","content":"You are a Couchbase Capella expert. Your task is to create valid queries to retrieve or create data based on the provided Information.\n\nApproach this task step-by-step and take your time."},{"role":"user","content":promptContent}],

"model": model,

"temperature":0,

"max_tokens":1024,

"stream":false}

var dataStr = JSON.stringify(data)

.replace(/\\/g, "\\\\") // escape backslashes

.replace(/"/g, '\\"'); // escape quotes

var completionsurl = modelapi

var q= `SELECT CURL("${completionsurl}", {

"request": "POST",

"header": ["Authorization: Bearer ${apikey}", "Content-type: application/json"],

"data": "${dataStr}"

}) AS result;`

var completionsq = N1QL(q);

var res = []

for(const doc of completionsq) {

res.push(doc);

}

try {

content = res[0].result.choices[0].message.content

} catch(e) {

return res;

}

stmt = content.trim().substring(7, content.length-4)

isSelect = (stmt.substring(0,6).toLowerCase())==="select"

if(isSelect === false){

return {

"generated_statement": stmt

}

var runq = N1QL(stmt);

var rrun = []

for(const doc of runq) {

rrun.push(doc)

}

return {

"generated_statement": stmt,

"results": rrun

}

2. Sube la biblioteca.
Ejecute el comando curl después de copiar el código de la biblioteca proporcionado en un archivo, es decir, utilizando ailib.js.

curl -X POST https://localhost:9499/evaluator/v1/libraries/usingailib 
--data-binary @usingailib.js -u Administrator:password

1 2	curl -X POST https://localhost:9499/evaluator/v1/libraries/usingailib --data-binary @usingailib.js -u Administrator:password

3. Cree la función definida por el usuario (UDF).
Una vez creada la biblioteca, utilice la instrucción de función create que se muestra a continuación para crear la UDF:

CREATE OR REPLACE FUNCTION NL2SQL(keyspaces, prompt, apikey, modelapi, model) 
LANGUAGE JAVASCRIPT AS "nl2sql" AT "usingailib";

1 2	CREATE OR REPLACE FUNCTION NL2SQL(keyspaces, prompt, apikey, modelapi, model) LANGUAGE JAVASCRIPT AS "nl2sql" AT "usingailib";

NL2SQL() ahora actúa como su traductor multilingüe entre el lenguaje humano y el motor de consultas de Couchbase. Solo tiene que proporcionarle un contexto y una solicitud en lenguaje natural, y él le devolverá una respuesta.

Cómo piensa la UDF

En segundo plano, utiliza su modelo preferido al invocar la UDF para comprender su intención y generar una consulta que Couchbase pueda ejecutar.

La ventaja de utilizar la API de finalización de chat es que puedes conectar fácilmente un modelo de otros proveedores que cumplan con las mismas especificaciones de la API. Puedes utilizar tu propio LLM privado o los conocidos de Open AI, Gemini, Claude, etc.

La función definida por el usuario invocada requiere la siguiente información por su parte:

espacios clave – Una matriz de cadenas, cada una de las cuales representa un espacio de claves de Couchbase (bucket.scope.collection). Utilice comillas graves cuando sea necesario para escapar nombres especiales (como viaje-muestra.inventario.ruta). Esto le indica a la UDF dónde buscar sus datos.
consulte – Su solicitud en español sencillo (o cualquier otro idioma).
Ejemplo: “Muéstrame todos los usuarios que han realizado una compra en las últimas 24 horas”.”
clave API – Su clave API utilizada para autenticarse con el punto final del modelo.
punto final del modelo – Por ejemplo, URL de completados de chat compatibles con Open AI.
modelo – El nombre del modelo que desea utilizar del proveedor.
Por ejemplo, “gpt-4o-2024-05-13”.”

También hay varias funciones disponibles en la biblioteca:

inferencer()

Antes de generar una consulta, la UDF primero intenta comprender sus datos. La función auxiliar inferencer() llama a la instrucción INFER de Couchbase para recuperar el esquema de una colección:

function inferencer(k) {
   var infq = N1QL("SELECT t.properties FROM (INFER " + k + ") AS t");
   var res = [];
   for (const doc of infq) {
       res.push(doc);
   }
   return res[0];
}

function inferencer(k) {

var infq = N1QL("SELECT t.properties FROM (INFER " + k + ") AS t");

var res = [];

for (const doc of infq) {

res.push(doc);

}

return res[0];

}

Este esquema se utiliza para ayudar a la IA a comprender qué tipo de datos hay dentro de cada colección.

La función principal: nl2sql()

Recopila todos los esquemas para los espacios de claves dados utilizando el inferencer(). Construye un mensaje que incluye: el esquema inferido, su consulta en lenguaje natural y un mensaje de Couchbase para activar el LLM.
Lo envía al LLM.
Extrae el SQL++ generado de la respuesta del modelo.
Lo ejecuta directamente si se trata de una instrucción SELECT y devuelve tanto la instrucción SQL++ generada como los resultados de la consulta.

La razón para no ejecutar sentencias no select es que no desea que esta UDF inserte, actualice o elimine documentos en una colección sin que usted lo verifique. Por lo tanto, la sentencia SQL++ le permite ejecutarla después de que haya sido verificada.

Ejemplo de caso de uso:

SELECT default:NL2SQL(
  ["`travel-sample`.inventory.hotel"],
  "Give me hotels in San Francisco that have free parking and free breakfast and a rating of more than 3",  "",
  "https://api.openai.com/v1/chat/completions",
  "gpt-4o-2024-05-13"
);

SELECT default:NL2SQL(

["`travel-sample`.inventory.hotel"],

"Give me hotels in San Francisco that have free parking and free breakfast and a rating of more than 3", "",

"https://api.openai.com/v1/chat/completions",

"gpt-4o-2024-05-13"

);

Result:
[{
    "$1": {
        "generated_statement": "SELECT h.name, h.address, h.city, h.state, h.country, h.free_parking, h.free_breakfast, r.ratings.Overall\nFROM `travel-sample`.inventory.hotel AS h\nUNNEST h.reviews AS r\nWHERE h.city = \"San Francisco\"\n  AND h.free_parking = true\n  AND h.free_breakfast = true\n  AND r.ratings.Overall > 3;",
        "results": [{
                "Overall": 4,
                "address": "520 Church St",
                "city": "San Francisco",
                "country": "United States",
                "free_breakfast": true,
                "free_parking": true,
                "name": "Parker House",
                "state": "California"
            },
            {
                "Overall": 4,
                "address": "520 Church St",
                "city": "San Francisco",
                "country": "United States",
                "free_breakfast": true,
                "free_parking": true,
                "name": "Parker House",
                "state": "California"
            },
            {
                "Overall": 5,
                "address": "520 Church St",
                "city": "San Francisco",
                "country": "United States",
                "free_breakfast": true,
                "free_parking": true,
                "name": "Parker House",
                "state": "California"
            },
            {
                "Overall": 4,
                "address": "520 Church St",
                "city": "San Francisco",
                "country": "United States",
                "free_breakfast": true,
                "free_parking": true,
                "name": "Parker House",
                "state": "California"
            },
            {
                "Overall": 5,
                "address": "465 Grant Ave",
                "city": "San Francisco",
                "country": "United States",
                "free_breakfast": true,
                "free_parking": true,
                "name": "Grant Plaza Hotel",
                "state": "California"
            },
            {
                "Overall": 5,
                "address": "465 Grant Ave",
                "city": "San Francisco",
                "country": "United States",
                "free_breakfast": true,
                "free_parking": true,
                "name": "Grant Plaza Hotel",
                "state": "California"
            },
...

Result:

[{

"$1": {

"generated_statement": "SELECT h.name, h.address, h.city, h.state, h.country, h.free_parking, h.free_breakfast, r.ratings.Overall\nFROM `travel-sample`.inventory.hotel AS h\nUNNEST h.reviews AS r\nWHERE h.city = \"San Francisco\"\n AND h.free_parking = true\n AND h.free_breakfast = true\n AND r.ratings.Overall > 3;",

"results": [{

"Overall": 4,

"address": "520 Church St",

"city": "San Francisco",

"country": "United States",

"free_breakfast": true,

"free_parking": true,

"name": "Parker House",

"state": "California"

{

"Overall": 4,

"address": "520 Church St",

"city": "San Francisco",

"country": "United States",

"free_breakfast": true,

"free_parking": true,

"name": "Parker House",

"state": "California"

{

"Overall": 5,

"address": "520 Church St",

"city": "San Francisco",

"country": "United States",

"free_breakfast": true,

"free_parking": true,

"name": "Parker House",

"state": "California"

{

"Overall": 4,

"address": "520 Church St",

"city": "San Francisco",

"country": "United States",

"free_breakfast": true,

"free_parking": true,

"name": "Parker House",

"state": "California"

{

"Overall": 5,

"address": "465 Grant Ave",

"city": "San Francisco",

"country": "United States",

"free_breakfast": true,

"free_parking": true,

"name": "Grant Plaza Hotel",

"state": "California"

{

"Overall": 5,

"address": "465 Grant Ave",

"city": "San Francisco",

"country": "United States",

"free_breakfast": true,

"free_parking": true,

"name": "Grant Plaza Hotel",

"state": "California"

...

Experimentar con modelos de otros proveedores

El siguiente ejemplo utiliza la API compatible con Open AI de Gemini. Solo tienes que cambiar la URL del proveedor del modelo de la API Open AI anterior a la API de Gemini. Además, asegúrate de cambiar el parámetro del modelo a uno que reconozca. Por supuesto, también debes actualizar la clave API de Open AI a la clave de Gemini.

SELECT NL2SQL(
  ["`travel-sample`.inventory.hotel"],
  "Show me hotels in France",
  "",
  "https://generativelanguage.googleapis.com/v1beta/openai/chat/completions",
  "gemini-2.0-flash"
)as p;

SELECT NL2SQL(

["`travel-sample`.inventory.hotel"],

"Show me hotels in France",

"",

"https://generativelanguage.googleapis.com/v1beta/openai/chat/completions",

"gemini-2.0-flash"

)as p;

A continuación se muestra el resultado:

[{
    "p": {
        "generated_statement": "SELECT h.name AS hotel_name, h.city AS hotel_city\nFROM `travel-sample`.inventory.hotel AS h\nWHERE h.country = \"France\";",
        "results": [{
                "hotel_city": "Giverny",
                "hotel_name": "The Robins"
            },
            {
                "hotel_city": "Giverny",
                "hotel_name": "Le Clos Fleuri"
            },
 	…
	     {
                "hotel_city": "Ferney-Voltaire",
                "hotel_name": "Hotel Formule 1"
            }
        ]
    }
}]

[{

"p": {

"generated_statement": "SELECT h.name AS hotel_name, h.city AS hotel_city\nFROM `travel-sample`.inventory.hotel AS h\nWHERE h.country = \"France\";",

"results": [{

"hotel_city": "Giverny",

"hotel_name": "The Robins"

{

"hotel_city": "Giverny",

"hotel_name": "Le Clos Fleuri"

…

{

"hotel_city": "Ferney-Voltaire",

"hotel_name": "Hotel Formule 1"

}

]

}

}]

Conclusión

Este blog ofrece una visión general de cómo se puede aprovechar la IA para interactuar con los datos en Couchbase. Con esta UDF, las consultas en lenguaje natural se convierten en una realidad, sin necesidad de tener conocimientos de SQL++. Es independiente del modelo y segura para consultas de producción.

Y esto es solo el principio. En el futuro, esperamos ampliarlo a:

Imagen → SQL++
Voz → SQL++
Tuberías similares a las de los agentes

... todo ello ejecutándose dentro de los flujos de trabajo de Couchbase.

Referencias
Capella IQ: https://docs.couchbase.com/cloud/get-started/capella-iq/get-started-with-iq.html
API de finalización de chats:
https://platform.openai.com/docs/api-reference/chat
https://ai.google.dev/gemini-api/docs/openai#rest

Gaurav Jayaraj - Ingeniero de software

Comparte este artículo

Platform

Self-Managed

Services

Capabilities

Why Couchbase?

Migrate to Capella

By Use Case

By Industry

By Application Need

Popular Docs

By Developer Role

Quickstart

Resource Center

About

Partnerships

Our Services

Partners: Register a Deal

Ready to register a deal with Couchbase?

Marriott

Hable con sus datos: una función definida por el usuario (UDF) que habla su idioma

Cómo funciona

Cómo piensa la UDF

Experimentar con modelos de otros proveedores

Conclusión

Recibe actualizaciones del blog de Couchbase en tu bandeja de entrada

Autor

Publicado por Gaurav Jayaraj - Ingeniero de software

Deja un comentario Cancelar respuesta

¿Listo para empezar con Couchbase Capella?

Empezar a construir

Utilizar Capella gratis

Póngase en contacto