Genera consultas en SQL con preguntas en lenguaje natural

En esta página, se describe cómo configurar y generar sentencias SQL con el lenguaje natural de IA de AlloyDB. El lenguaje natural te permite crear aplicaciones de IA generativa orientadas al usuario con lenguaje natural para consultar bases de datos.

Para habilitar la extensión alloydb_ai_nl, que es la API de compatibilidad con lenguaje natural de AlloyDB para PostgreSQL, sigue estos pasos de alto nivel:

  1. Instala la extensión alloydb_ai_nl.
  2. Define una configuración de lenguaje natural para tu aplicación.
  3. Registrar un esquema
  4. Agrega contexto.
  5. Agrega plantillas de consulta.
  6. Define los tipos de conceptos y crea un índice de valores.
  7. Genera instrucciones SQL con una interfaz de lenguaje natural.

Antes de comenzar

Crea un clúster y habilita la integración de Vertex AI

  1. Crea un clúster y una instancia de AlloyDB. Usas la instancia de AlloyDB para crear la base de datos y el esquema de la aplicación.
  2. Habilita la integración en Vertex AI. Para obtener más información, consulta Cómo realizar la integración con Vertex AI.

Roles obligatorios

Para instalar la extensión alloydb_ai_nl y otorgar acceso a otros usuarios, debes tener el siguiente rol de Identity and Access Management (IAM) en el proyecto de Google Cloud que usas:

Para obtener más información, consulta Administra usuarios de PostgreSQL con autenticación estándar.

Prepara el entorno

Para prepararte para generar consultas de lenguaje natural, debes instalar la extensión requerida, crear una configuración y registrar un esquema.

Instala la extensión alloydb_nl_ai

La extensión alloydb_ai_nl usa la extensión google_ml_integration, que interactúa con modelos de lenguaje grandes (LLM), incluidos los modelos de Gemini en Vertex AI.

Para instalar la extensión alloydb_ai_nl, conéctate a la base de datos y ejecuta el siguiente comando, que habilita las extensiones google_ml_integration y alloydb_ai_nl.

CREATE EXTENSION google_ml_integration;
CREATE EXTENSION alloydb_ai_nl cascade;

Crea una configuración de lenguaje natural y registra un esquema

El lenguaje natural de IA de AlloyDB usa nl_config para asociar aplicaciones a ciertos esquemas, plantillas de consulta y extremos de modelos. nl_config es una configuración que asocia una aplicación con el esquema, las plantillas y otros contextos. Una aplicación grande también puede usar diferentes configuraciones para diferentes partes de la aplicación, siempre y cuando especifiques la configuración correcta cuando se envíe una pregunta desde esa parte de la aplicación. Puedes registrar un esquema completo o registrar objetos de esquema específicos, como tablas, vistas y columnas.

  1. Para crear una configuración de lenguaje natural, usa el siguiente ejemplo:

    SELECT
  alloydb_ai_nl.g_create_configuration(
    'my_app_config'        -- configuration_id
  );

    "gemini-2.0-flash:generateContent" es el extremo del modelo.

  2. Registra un esquema para una configuración especificada con el siguiente ejemplo:

    SELECT
  alloydb_ai_nl.g_manage_configuration(
    operation => 'register_schema',
    configuration_id_in => 'my_app_config',
    schema_names_in => '{my_schema}'
  );

Agregar contexto

El contexto incluye cualquier tipo de información que puedas usar para responder una pregunta del usuario final. El contexto incluye la estructura y las relaciones del esquema, los resúmenes y las descripciones de las columnas, los valores de las columnas y su semántica, y las reglas o sentencias de lógica empresarial específicas de la aplicación o el dominio.

Agrega contexto general para reglas específicas de la aplicación

Los elementos de contexto general incluyen reglas específicas de la aplicación, sentencias de lógica empresarial o cualquier terminología específica de la aplicación y el dominio que no esté vinculada a un objeto de esquema específico.

Para agregar contexto general para las reglas específicas de la aplicación y la terminología específica de la aplicación o el dominio, sigue estos pasos:

  1. Para agregar un elemento de contexto general para la configuración especificada, usa el siguiente ejemplo:

    SELECT
  alloydb_ai_nl.g_manage_configuration(
    'add_general_context',
    'my_app_config',
    general_context_in => '{"If the user asks for a good seat, assume that means a window or aisle seat."}'
  );

    La sentencia anterior ayuda al lenguaje natural de la IA de AlloyDB a proporcionar respuestas de mayor calidad a las preguntas en lenguaje natural de los usuarios.

  2. Para ver los contextos generales de la configuración especificada, ejecuta la siguiente sentencia:

    SELECT alloydb_ai_nl.list_general_context(nl_config TEXT);

Genera y revisa el contexto del esquema

El contexto del esquema describe los objetos del esquema, incluidas las tablas, las vistas, las vistas materializadas y las columnas. Este contexto se almacena como el COMMENT de cada objeto del esquema.

  1. Para generar contextos para objetos de esquemas, llama a las siguientes APIs. Para obtener los mejores resultados, asegúrate de que las tablas de la base de datos contengan datos representativos.

    -- For all schema objects (tables, views, materialized views and columns)
-- within the scope of a provided nl_config.
SELECT
  alloydb_ai_nl.generate_schema_context(
    'my_app_config' -- nl_config
  );

  2. Ejecuta la siguiente sentencia para revisar los contextos de esquemas generados.

    SELECT schema_object, object_context
FROM alloydb_ai_nl.generated_schema_context_view;

    Los contextos de esquemas generados se almacenan en la vista anterior.

  3. Opcional: Actualiza los contextos de esquemas generados.

    SELECT
  alloydb_ai_nl.update_generated_relation_context(
    'my_schema.my_table',
    'This table contains archival records, if you need latest records use records_new table.'
  );

SELECT
  alloydb_ai_nl.update_generated_column_context(
    'my_schema.my_table.column1',
    'The seat_class column takes single letters like "E" for economy, "P" for premium economy, "B" for business and "F" for First.'
  );

  4. Aplica el contexto. Cuando aplicas el contexto, este se aplica de inmediato y se borra de generated_schema_context_view.

    -- For all schema objects (tables, views, materialized views and columns) 
-- within the scope of nl_config.
SELECT 
  alloydb_ai_nl.apply_generated_schema_context(
    'my_app_config' --nl_config
  );

  5. Opcional: Verifica el contexto generado. La siguiente API te permite verificar los contextos de esquema, que se usan cuando generas instrucciones SQL.

    -- For table, view or materialized view.
SELECT
  alloydb_ai_nl.get_relation_context(
    'my_schema.my_table'
  );

-- For column.
SELECT
  alloydb_ai_nl.get_column_context(
    'my_schema.my_table.column1'
  );

  6. Opcional: Establece el contexto del esquema de forma manual.

    -- For table, view or materialized view.
SELECT 
  alloydb_ai_nl.set_relation_context(
    'my_schema.my_table',
    'One-to-many mapping from product to categories'
  );

-- For column.
SELECT 
  alloydb_ai_nl.set_column_context(
    'my_schema.my_table.column1',
    'This column provides additional tagged info for the product in  Json format, e.g., additional color or size information of the product - tags: { "color": "red", "size": "XL"}'
  );

Crea plantillas de consulta

Para mejorar la calidad de las aplicaciones de IA generativa compiladas con LLM, puedes agregar plantillas. Una plantilla de consulta es un conjunto seleccionado de preguntas representativas o comunes en lenguaje natural, con las consultas de SQL correspondientes, así como explicaciones para proporcionar una justificación declarativa para la generación de lenguaje natural a SQL (NL2SQL). El objetivo principal de las plantillas es que las especifique la aplicación, pero la extensión alloydb_ai_nl también puede generarlas automáticamente en función de las consultas de SQL que se usan con frecuencia. Cada plantilla debe estar asociada con un nl_config.

La extensión alloydb_ai_nl usa un template_store para incorporar de forma dinámica plantillas de SQL relevantes en el proceso de generar una sentencia de SQL para responder la pregunta del usuario. template_store identifica plantillas con intenciones similares a la pregunta en lenguaje natural que se hizo, identifica la sentencia SQL parametrizada correspondiente y sintetiza una sentencia SQL creando instancias de parámetros con valores de la pregunta en lenguaje natural. Sin embargo, si no hay una plantilla con la misma intención que la pregunta que hizo el usuario, alloydb_ai_nl usa cada plantilla y contexto relevantes para componer una sentencia SQL.

Para agregar plantillas, especifica la pregunta (con un parámetro llamado intent) y la consulta de SQL.

Para agregar una plantilla al almacén de plantillas, ejecuta la siguiente sentencia:

SELECT
  alloydb_ai_nl.add_template(
    nl_config => 'my_app_config',
    intent => 'How many accounts associated with loans are located in the Prague region?',
    sql => 'SELECT COUNT(T1.account_id)
            FROM bird_dev_financial.account AS T1
            INNER JOIN bird_dev_financial.loan AS T2
              ON T1.account_id = T2.account_id
            INNER JOIN bird_dev_financial.district AS T3
              ON T1.district_id = T3.district_id
            WHERE T3."A3" = ''Prague''',
    check_intent => TRUE
  );

Cuando check_intent es TRUE, alloydb_ai_nl realiza una verificación semántica para confirmar que el intent proporcionado coincida con la sentencia SQL pasada. Si el intent no coincide con la sentencia SQL, no se agrega la plantilla.

Cómo generar plantillas automáticamente

Una vez que tengas un conjunto de datos representativo en tus tablas, te recomendamos que ejecutes consultas de SQL que correspondan a las preguntas comunes que es probable que hagan tus usuarios finales. Es importante que te asegures de que las consultas tengan buenos planes de consulta y que tengan un buen rendimiento.

Después de ejecutar las consultas, el lenguaje natural de AlloyDB AI puede generar automáticamente plantillas según el historial de consultas. Puedes llamar a las siguientes APIs para generar plantillas. Debes revisar y aplicar las plantillas generadas antes de que tengan efecto.

La generación automática de plantillas se basa en las consultas que se usan con más frecuencia en el registro de consultas, google_db_advisor_workload_statements. Las consultas se filtran según los siguientes criterios:

  • Declaraciones SELECT
  • Archivos ejecutables: El comando EXPLAIN puede procesar correctamente la consulta.
  • No hay duplicación: la consulta no se usó anteriormente para generar plantillas.
  • Todas las tablas y vistas a las que se hace referencia están dentro del alcance de nl_config.

Para generar, revisar y aplicar plantillas automáticamente, sigue estos pasos:

  1. Solicita a AlloyDB que genere plantillas según tu historial de consultas:

    SELECT 
  alloydb_ai_nl.generate_templates(
    'my_app_config', 
);

    Usa la vista proporcionada, alloydb_ai_nl.generated_templates_view, para revisar el generated_templates.

    En el siguiente resultado, se muestra la cantidad de plantillas generadas:

    -[ RECORD 1 ]------+--
generate_templates | 1

  2. Revisa las plantillas generadas con la vista generated_templates_view.

    SELECT * 
FROM alloydb_ai_nl.generated_templates_view;

    El siguiente es un ejemplo del resultado que se muestra:

    -[ RECORD 1 ]----------------------------------------------------------------
id          | 1
config      | my_app_config
type        | Template
manifest    | How many clients have a birth year of a given number?
nl          | How many clients have a birth year of 1997?
sql         | select count(*) from public.client as T where  
             to_char(T.birth_date::timestamp, 'YYYY') = '1997';
intent      | How many clients have a birth year of 1997?
psql        | select count(*) from public.client as T where  
             to_char(T.birth_date::timestamp, 'YYYY') = $1;
pintent     | How many clients have a birth year of $1?
comment     |
explanation |
weight      | 1

  3. Para actualizar una plantilla generada, ejecuta la siguiente sentencia de ejemplo:

    SELECT alloydb_ai_nl.update_generated_template(
  id => 1,
  manifest => 'How many clients are born in a given year?',
  nl => 'How many clients are born in 1997?',
  intent => 'How many clients are born in 1997?',
  pintent => 'How many clients are born in $1?'

);

  4. Aplica las plantillas. Las plantillas que apliques se agregarán de inmediato al almacén de plantillas y se borrarán de la vista de revisión.

    -- For all templates generated under the nl config.
SELECT
  alloydb_ai_nl.apply_generated_templates('my_app_config');

Configura la seguridad para el lenguaje natural

Para configurar la seguridad del lenguaje natural de la IA de AlloyDB, consulta Cómo administrar la seguridad de la aplicación de datos con vistas seguras parametrizadas.

Define los tipos de conceptos y el índice de valores

Define los tipos de conceptos y los índices de valor para proporcionar una comprensión más profunda de las preguntas que se hacen. Un tipo de concepto es una categoría o clase de entidades que identifica el significado semántico de las palabras y las frases, en lugar de solo su forma literal.

Por ejemplo, dos nombres de países pueden ser iguales, incluso si uno está en mayúsculas, por ejemplo, USA, y el otro está en minúsculas, por ejemplo, usa. En este caso, el nombre del país es el tipo de concepto. Otros ejemplos de tipos de conceptos incluyen el nombre de una persona, el nombre de una ciudad y la fecha.

Un índice de valor es un índice sobre los valores de las columnas que forman parte de la configuración de lenguaje natural nl_config, según los tipos de conceptos asociados con cada columna. Un índice de valores permite la coincidencia eficiente de frases de valor para la pregunta que se hace y los valores de la base de datos.

Para definir tipos de conceptos y un índice de valores, sigue estos pasos con los ejemplos proporcionados. En los ejemplos, se asocia una columna a un tipo de concepto, se crea y se actualiza un índice de valores, y se usa un conjunto de sinónimos para realizar una búsqueda de valores.

  1. Para asociar una columna con un tipo de concepto, ejecuta la siguiente consulta:

    SELECT
  alloydb_ai_nl.associate_concept_type(
    column_names_in => 'my_schema.country.country_name',
    concept_type_in => 'country_name',
    nl_config_id_in => 'my_app_config'
  );

  2. Para crear un índice de valores basado en todas las columnas que forman parte de una configuración de lenguaje natural y están asociadas con un tipo de concepto, ejecuta la siguiente sentencia:

    SELECT
  alloydb_ai_nl.create_value_index(
    nl_config_id_in => 'my_app_config'
  );

  3. Cuando asocies tipos de conceptos a columnas nuevas, actualiza el índice de valores para que refleje los cambios.

    SELECT
  alloydb_ai_nl.refresh_value_index(
    nl_config_id_in => 'my_app_config'
  );

  4. Para habilitar el lenguaje natural de la IA de AlloyDB para que coincida con los sinónimos de un valor, ejecuta la siguiente sentencia de ejemplo:

    SELECT
  alloydb_ai_nl.insert_synonym_set(
    ARRAY [
      'USA',
      'US',
      'United States',
      'United States of America'
    ]
  );

    Aunque los datos de tus tablas puedan usar un valor específico (por ejemplo, si se usa United States para identificar un país), puedes definir un conjunto de sinónimos que contenga todos los sinónimos de United States. Si alguno de los sinónimos aparece en la pregunta en lenguaje natural, el lenguaje natural de IA de AlloyDB hace coincidir los sinónimos con los valores de tus tablas.

  5. Realiza una búsqueda de valores para encontrar los valores correctos de la base de datos, según un arreglo de frases de valor.

    SELECT
  alloydb_ai_nl.get_concept_and_value(
    value_phrases_in => ARRAY['United States'],
    nl_config_id_in  => 'my_app_config'
  );

    Por ejemplo, si un usuario hace una pregunta como "¿Cuál es la población de Estados Unidos?" que usa la siguiente consulta get_sql, el lenguaje natural de la IA de AlloyDB usa la función get_concept_and_value con la frase de valor United States para realizar una búsqueda difusa en los índices de valor. Una búsqueda parcial es una técnica de búsqueda que encuentra coincidencias incluso cuando la búsqueda no coincide exactamente con los datos correspondientes.

    El lenguaje natural encuentra un resultado (el valor USA) que está cerca de la búsqueda y usa ese resultado para generar la consulta de SQL.

    SELECT
  alloydb_ai_nl.get_sql(
    nl_config_id    => 'my_app_config',
    nl_question     => 'What is the population of the United States?',
    additional_info => json_build_object('enrich_nl_question', TRUE)
  ) ->> 'sql';

    En la siguiente tabla, se enumeran los tipos de conceptos integrados que define el lenguaje natural de AlloyDB AI.

    Nombre del concepto Descripción
    generic_entity_name Se puede usar una sola columna de tipo de cadena para un nombre de entidad genérico. Por ejemplo:
      SELECT alloydb_ai_nl.associate_concept_type('public.item.item_name', 'generic_entity_name')
    country_name, city_name, region_name Nombres de países, ciudades y regiones El uso es exactamente el mismo que el tipo de concepto generic_entity_name.
    full_person_name Es el nombre de la persona, que consta del nombre, el apellido y el segundo nombre. Se pueden usar hasta tres columnas de tipo de cadena para un nombre completo de persona. Se puede omitir cualquiera de las columnas cuando se asocian columnas de nombre a full_person_name. Por ejemplo:
      SELECT alloydb_ai_nl.associate_concept_type('public.person.last_name,public.person.first_name,public.person.middle_name','full_person_name')
    ssn Una sola columna de cadena que contiene un número de seguridad social. Por ejemplo:
      SELECT alloydb_ai_nl.associate_concept_type('public.person.ssn','ssn')
    date Una fecha o una marca de tiempo Por ejemplo:
     SELECT alloydb_ai_nl.associate_concept_type('public.person.date_col','date')

Genera instrucciones de SQL a partir de entradas de lenguaje natural

Puedes usar el lenguaje natural de IA de AlloyDB para generar instrucciones SQL a partir de entradas de lenguaje natural. Cuando ejecutas la sentencia SQL generada, esta proporciona los datos de la base de datos que necesitas para responder la pregunta de lenguaje natural.

  1. Para usar el lenguaje natural y obtener resultados de tu base de datos con la función alloydb_ai_nl.get_sql, usa el siguiente ejemplo:

    SELECT
  alloydb_ai_nl.get_sql(
    'my_app_config', -- nl_config
    'What is the sum that client number 4''s account has following transaction 851?' -- nl question
  );

    Se muestra el siguiente resultado JSON:

    {
  "sql": "SELECT T3.balance FROM public.client AS T1 INNER JOIN public.account AS T2 ON T1.district_id = T2.district_id INNER JOIN public.trans AS T3 ON T2.account_id = T3.account_id WHERE T1.client_id = 4 AND T3.trans_id = 851",
  "prompt": "",
  "retries": 0,
  "error_msg": "",
  "nl_question": "What is the sum that client number 4's account has following transaction 851?"
}

  2. Opcional: Para extraer la consulta de SQL generada como una cadena de texto, agrega ->>'sql':

    SELECT
  alloydb_ai_nl.get_sql(
    'my_app_config', -- nl_config
    'What is the sum that client number 4''s account has following transaction 851?' -- nl question
  ) ->> 'sql';

    El operador ->> se usa para extraer un valor JSON como texto. La función alloydb_ai_nl.get_sql muestra un objeto JSON, que es la parte de la sentencia que recupera el valor asociado con la clave sql. Este valor es la consulta en SQL generada.

Prueba y define mejor

Para obtener consultas generadas automáticamente mejoradas, agrega mejor contexto, plantillas de consulta y índices de valor, y luego itera hasta que obtengas los resultados que deseas.

¿Qué sigue?