# SQL QnA

***

A diferencia de ejemplos anteriores como [QnA con Web Scraping](https://gabriels-organization-67.gitbook.io/curso-de-flowise-or-gabriel-merlo/documentacion-oficial/use-cases/web-scrape-qna) y [QnA con Múltiples Documentos](https://gabriels-organization-67.gitbook.io/curso-de-flowise-or-gabriel-merlo/documentacion-oficial/use-cases/multiple-documents-qna), consultar datos estructurados no requiere una base de datos vectorial. A alto nivel, esto se puede lograr con los siguientes pasos:

1. Proporcionar al LLM:
   * visión general del esquema de la base de datos SQL
   * datos de ejemplo de filas
2. Devolver una consulta SQL con few shot prompting
3. Validar la consulta SQL usando un nodo [If Else](https://github.com/codermtk/FlowiseDocs/blob/espanolflowise/integrations/utilities/if-else.md)
4. Crear una función personalizada para ejecutar la consulta SQL y obtener la respuesta
5. Devolver una respuesta natural de la respuesta SQL ejecutada

<figure><img src="https://769538227-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FjyGndXAZESKnWvU2qnmw%2Fuploads%2Fgit-blob-36fcbd165af247cc5f7fd7e67dcf463e991a2459%2Fimage%20(113).png?alt=media" alt=""><figcaption></figcaption></figure>

En este ejemplo, vamos a crear un chatbot QnA que pueda interactuar con una base de datos SQL almacenada en SingleStore

<figure><img src="https://769538227-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FjyGndXAZESKnWvU2qnmw%2Fuploads%2Fgit-blob-a060a0a1eb1c8ea8d2f97f4f5afc853cb4c9fef9%2Fimage%20(116).png?alt=media" alt=""><figcaption></figcaption></figure>

## TL;DR

Puedes encontrar la plantilla del chatflow:

{% file src="<https://769538227-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FjyGndXAZESKnWvU2qnmw%2Fuploads%2Fgit-blob-3ce7859009d54c0c503e4df22550ae978e97009b%2FSQL%20Chatflow.json?alt=media>" %}

## 1. Esquema de Base de Datos SQL + Filas de Ejemplo

Usa un nodo Custom JS Function para conectarte a SingleStore, recuperar el esquema de la base de datos y las 3 primeras filas.

Según el [paper de investigación](https://arxiv.org/abs/2204.00498), se recomienda generar un prompt con el siguiente formato de ejemplo:

```
CREATE TABLE samples (firstName varchar NOT NULL, lastName varchar)
SELECT * FROM samples LIMIT 3
firstName lastName
Stephen Tyler
Jack McGinnis
Steven Repici
```

<figure><img src="https://769538227-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FjyGndXAZESKnWvU2qnmw%2Fuploads%2Fgit-blob-5d881f9312edab55432c9d8741eac9985f00a8ac%2Fimage%20(114).png?alt=media" alt=""><figcaption></figcaption></figure>

<details>

<summary>Código Javascript Completo</summary>

```javascript
const HOST = 'singlestore-host.com';
const USER = 'admin';
const PASSWORD = 'mypassword';
const DATABASE = 'mydb';
const TABLE = 'samples';
const mysql = require('mysql2/promise');

let sqlSchemaPrompt;

function getSQLPrompt() {
  return new Promise(async (resolve, reject) => {
    try {
      const singleStoreConnection = mysql.createPool({
        host: HOST,
        user: USER,
        password: PASSWORD,
        database: DATABASE,
      });
  
      // Get schema info
      const [schemaInfo] = await singleStoreConnection.execute(
        `SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE table_name = "${TABLE}"`
      );
  
      const createColumns = [];
      const columnNames = [];
  
      for (const schemaData of schemaInfo) {
        columnNames.push(`${schemaData['COLUMN_NAME']}`);
        createColumns.push(`${schemaData['COLUMN_NAME']} ${schemaData['COLUMN_TYPE']} ${schemaData['IS_NULLABLE'] === 'NO' ? 'NOT NULL' : ''}`);
      }
  
      const sqlCreateTableQuery = `CREATE TABLE samples (${createColumns.join(', ')})`;
      const sqlSelectTableQuery = `SELECT * FROM samples LIMIT 3`;
  
      // Get first 3 rows
      const [rows] = await singleStoreConnection.execute(
          sqlSelectTableQuery,
      );
      
      const allValues = [];
      for (const row of rows) {
          const rowValues = [];
          for (const colName in row) {
              rowValues.push(row[colName]);
          }
          allValues.push(rowValues.join(' '));
      }
  
      sqlSchemaPrompt = sqlCreateTableQuery + '\n' + sqlSelectTableQuery + '\n' + columnNames.join(' ') + '\n' + allValues.join('\n');
      
      resolve();
    } catch (e) {
      console.error(e);
      return reject(e);
    }
  });
}

async function main() {
    await getSQLPrompt();
}

await main();

return sqlSchemaPrompt;
```

</details>

Puedes encontrar más información sobre cómo obtener el `HOST`, `USER`, `PASSWORD` en esta [guía](https://github.com/codermtk/FlowiseDocs/blob/espanolflowise/use-cases/broken-reference/README.md). Una vez terminado, haz clic en Execute:

<figure><img src="https://769538227-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FjyGndXAZESKnWvU2qnmw%2Fuploads%2Fgit-blob-32990a7dcbbe8946f01238907d2250d3cb8d449b%2Fimage%20(117).png?alt=media" alt=""><figcaption></figcaption></figure>

Ahora podemos ver que se ha generado el formato correcto. El siguiente paso es llevarlo al Prompt Template.

## 2. Devolver una consulta SQL con few shot prompting

Crea un nuevo Chat Model + Prompt Template + LLMChain

<figure><img src="https://769538227-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FjyGndXAZESKnWvU2qnmw%2Fuploads%2Fgit-blob-15ac7fea5dad0668afbe269c3921c4e6a3dddd93%2Fimage%20(118).png?alt=media" alt=""><figcaption></figcaption></figure>

Especifica el siguiente prompt en el Prompt Template:

```
Basado en el esquema de tabla SQL proporcionado y la pregunta a continuación, devuelve una consulta SQL SELECT ALL que respondería la pregunta del usuario. Por ejemplo: SELECT * FROM table WHERE id = '1'.
------------
SCHEMA: {schema}
------------
QUESTION: {question}
------------
SQL QUERY:
```

Como estamos usando 2 variables: {schema} y {question}, especifica sus valores en **Format Prompt Values**:

<figure><img src="https://769538227-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FjyGndXAZESKnWvU2qnmw%2Fuploads%2Fgit-blob-7a519c935b81fc9eada4679787a7671e199870af%2Fimage%20(122).png?alt=media" alt="" width="563"><figcaption></figcaption></figure>

{% hint style="info" %}
Puedes proporcionar más ejemplos al prompt (es decir, few-shot prompting) para que el LLM aprenda mejor. O toma referencia del [dialect-specific prompting](https://js.langchain.com/docs/use_cases/sql/prompting#dialect-specific-prompting)
{% endhint %}

## 3. Validar la consulta SQL usando el nodo [If Else](https://github.com/codermtk/FlowiseDocs/blob/espanolflowise/integrations/utilities/if-else.md)

A veces la consulta SQL es inválida, y no queremos desperdiciar recursos ejecutando una consulta SQL inválida. Por ejemplo, si un usuario está haciendo una pregunta general que es irrelevante para la base de datos SQL. Podemos usar un nodo `If Else` para enrutar a diferentes caminos.

Por ejemplo, podemos realizar una verificación básica para ver si SELECT y WHERE están incluidos en la consulta SQL dada por el LLM.

{% tabs %}
{% tab title="If Function" %}

```javascript
const sqlQuery = $sqlQuery.trim();

const regex = /SELECT\s.*?(?:\n|$)/gi;

// Extracting the SQL part
const matches = sqlQuery.match(regex);
const cleanSql = matches ? matches[0].trim() : "";

if (cleanSql.includes("SELECT") && cleanSql.includes("WHERE")) {
    return cleanSql;
}
```

{% endtab %}

{% tab title="Else Function" %}

```javascript
return $sqlQuery;
```

{% endtab %}
{% endtabs %}

<figure><img src="https://769538227-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FjyGndXAZESKnWvU2qnmw%2Fuploads%2Fgit-blob-34cc00e178344f081b21cf14f1d70edabb61826a%2Fimage%20(119).png?alt=media" alt="" width="327"><figcaption></figcaption></figure>

En la Else Function, enrutaremos a un Prompt Template + LLMChain que básicamente le dice al LLM que no puede responder la consulta del usuario:

<figure><img src="https://769538227-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FjyGndXAZESKnWvU2qnmw%2Fuploads%2Fgit-blob-67d36adc95cc87398015805e7896442f96cbfa1b%2Fimage%20(120).png?alt=media" alt=""><figcaption></figcaption></figure>

## 4. Función personalizada para ejecutar la consulta SQL y obtener la respuesta

Si es una consulta SQL válida, necesitamos ejecutarla. Conecta la salida ***True*** del nodo **If Else** a un nodo **Custom JS Function**:

<figure><img src="https://769538227-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FjyGndXAZESKnWvU2qnmw%2Fuploads%2Fgit-blob-d60b14967d367635d818d5c0ca3f2bca35cea616%2Fimage%20(123).png?alt=media" alt="" width="563"><figcaption></figcaption></figure>

<details>

<summary>Código Javascript Completo</summary>

```javascript
const HOST = 'singlestore-host.com';
const USER = 'admin';
const PASSWORD = 'mypassword';
const DATABASE = 'mydb';
const TABLE = 'samples';
const mysql = require('mysql2/promise');

let result;

function getSQLResult() {
  return new Promise(async (resolve, reject) => {
    try {
      const singleStoreConnection = mysql.createPool({
        host: HOST,
        user: USER,
        password: PASSWORD,
        database: DATABASE,
      });
     
      const [rows] = await singleStoreConnection.execute(
        $sqlQuery
      );
  
      result = JSON.stringify(rows)
      
      resolve();
    } catch (e) {
      console.error(e);
      return reject(e);
    }
  });
}

async function main() {
    await getSQLResult();
}

await main();

return result;
```

</details>

## 5. Devolver una respuesta natural de la respuesta SQL ejecutada

Crea un nuevo Chat Model + Prompt Template + LLMChain

<figure><img src="https://769538227-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FjyGndXAZESKnWvU2qnmw%2Fuploads%2Fgit-blob-33cfd22cf3f4095b5be15ec47821550c7c5ea3dd%2Fimage%20(124).png?alt=media" alt=""><figcaption></figcaption></figure>

Write the following prompt in the Prompt Template:

```
Based on the question, and SQL response, write a natural language response, be details as possible:
------------
QUESTION: {question}
------------
SQL RESPONSE: {sqlResponse}
------------
NATURAL LANGUAGE RESPONSE:
```

Specify the variables in **Format Prompt Values**:

<figure><img src="https://769538227-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FjyGndXAZESKnWvU2qnmw%2Fuploads%2Fgit-blob-a871375670760ed6d60e1bbb5caf2c05512b97ab%2Fimage%20(125).png?alt=media" alt="" width="563"><figcaption></figcaption></figure>

Voila! Your SQL chatbot is now ready for testing!

## Query

First, let's ask something related to the database.

<figure><img src="https://769538227-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FjyGndXAZESKnWvU2qnmw%2Fuploads%2Fgit-blob-83583e6258d4bc1637a94dbc4d5285af07e5319d%2Fimage%20(128).png?alt=media" alt="" width="434"><figcaption></figcaption></figure>

Looking at the logs, we can see the first LLMChain is able to give us a SQL query:

**Input:**

{% code overflow="wrap" %}

```
Based on the provided SQL table schema and question below, return a SQL SELECT ALL query that would answer the user's question. For example: SELECT * FROM table WHERE id = '1'.\n------------\nSCHEMA: CREATE TABLE samples (id bigint(20) NOT NULL, firstName varchar(300) NOT NULL, lastName varchar(300) NOT NULL, userAddress varchar(300) NOT NULL, userState varchar(300) NOT NULL, userCode varchar(300) NOT NULL, userPostal varchar(300) NOT NULL, createdate timestamp(6) NOT NULL)\nSELECT * FROM samples LIMIT 3\nid firstName lastName userAddress userState userCode userPostal createdate\n1125899906842627 Steven Repici 14 Kingston St. Oregon NJ 5578 Thu Dec 14 2023 13:06:17 GMT+0800 (Singapore Standard Time)\n1125899906842625 John Doe 120 jefferson st. Riverside NJ 8075 Thu Dec 14 2023 13:04:32 GMT+0800 (Singapore Standard Time)\n1125899906842629 Bert Jet 9th, at Terrace plc Desert City CO 8576 Thu Dec 14 2023 13:07:11 GMT+0800 (Singapore Standard Time)\n------------\nQUESTION: what is the address of John\n------------\nSQL QUERY:
```

{% endcode %}

**Output**

<pre class="language-sql"><code class="lang-sql"><strong>SELECT userAddress FROM samples WHERE firstName = 'John'
</strong></code></pre>

After executing the SQL query, the result is passed to the 2nd LLMChain:

**Input**

{% code overflow="wrap" %}

```
Based on the question, and SQL response, write a natural language response, be details as possible:\n------------\nQUESTION: what is the address of John\n------------\nSQL RESPONSE: [{\"userAddress\":\"120 jefferson st.\"}]\n------------\nNATURAL LANGUAGE RESPONSE:
```

{% endcode %}

**Output**

```
The address of John is 120 Jefferson St.
```

Now, we if ask something that is irrelevant to the SQL database, the Else route is taken.

<figure><img src="https://769538227-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FjyGndXAZESKnWvU2qnmw%2Fuploads%2Fgit-blob-71ec75684e981853681557a1ee885d922ecbf6f1%2Fimage%20(132).png?alt=media" alt="" width="428"><figcaption></figcaption></figure>

For first LLMChain, a SQL query is generated as below:

```sql
SELECT * FROM samples LIMIT 3
```

However, it fails the `If Else` check because it doesn't contains both `SELECT` and `WHERE`, hence entering the Else route that has a prompt that says:

```
Politely say "I'm not able to answer query"
```

And the final output is:

```
I apologize, but I'm not able to answer your query at the moment.
```

## Conclusion

In this example, we have successfully created a SQL chatbot that can interact with your database, and is also able to handle questions that are irrelevant to database. Further improvement includes adding memory to provide conversation history.

You can find the chatflow below:

{% file src="<https://769538227-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FjyGndXAZESKnWvU2qnmw%2Fuploads%2Fgit-blob-89bb5965cdb9d1b2c9dbfca8dcee658951e77255%2FSQL%20Chatflow%20(1).json?alt=media>" %}
