使用 AlloyDB AI 自然语言生成 SQL

本教程介绍了如何使用 Google Cloud 控制台设置和使用 AlloyDB AI Natural Language API。您将了解如何配置 AlloyDB AI Natural Language API，以便您可以提出自然语言问题并接收 SQL 查询和结果。

目标

  • 创建和填充表格，并使用自动生成来创建上下文。
  • 为数据库中的列创建值索引。
  • 创建并配置自然语言配置 (nl_config) 对象。
  • 为应用中的示例查询创建模板。
  • 使用 get_sql() 函数来生成可回答问题的 SQL 查询。
  • 使用 execute_nl_query() 函数来通过数据库回答自然语言问题。

费用

在本文档中，您将使用 Google Cloud 的以下收费组件：

您可使用价格计算器根据您的预计使用情况来估算费用。 Google Cloud 新用户可能有资格申请免费试用。

完成本文档中描述的任务后，您可以通过删除所创建的资源来避免继续计费。如需了解详情，请参阅清理。

准备工作

请求访问权限

在使用 AlloyDB AI 自然语言生成 SQL 之前，您必须先请求访问 AlloyDB AI 自然语言，并等到收到启用确认后再按照本教程中的说明操作。

启用结算功能和所需的 API

  1. 在 Google Cloud 控制台中，选择一个项目。

    转到“项目选择器”

  2. 确保您的 Google Cloud 项目已启用结算功能。

  3. 启用创建和连接到 AlloyDB for PostgreSQL 所需的 Cloud API。

    启用该 API

    1. 在确认项目步骤中，点击下一步以确认您要更改的项目的名称。

    2. 在启用 API 步骤中，点击启用以启用以下内容：

      • AlloyDB API

创建并连接到数据库

  1. 创建集群及其主实例。
  2. 连接到您的实例并创建数据库。
  3. 启用 Vertex AI 集成。如需了解详情，请参阅与 Vertex AI 集成。

安装所需的扩展程序

如需安装 alloydb_ai_nl 扩展程序（即 AlloyDB AI 自然语言支持 API），请运行以下查询：

CREATE EXTENSION google_ml_integration;
CREATE EXTENSION alloydb_ai_nl cascade;

创建 nla_demo 架构和表

在以下步骤中，您将创建 nla_demo 架构以及该架构中的表。您可以使用合成数据填充表格。所提供的架构和数据旨在支持线上零售业务的基本运营，其潜在应用范围可扩展到客户管理、分析、营销和运营方面。

示例数据展示了如何使用 AlloyDB AI 自然语言进行开发、测试和演示，尤其是自然语言界面等功能。

  1. 通过运行以下查询来创建架构：

    CREATE SCHEMA nla_demo;

  2. 在 nla_demo 架构中创建表。addresses 表存储客户和订单的地址信息。

    CREATE TABLE nla_demo.addresses (
    address_id      SERIAL         PRIMARY KEY,
    street_address  VARCHAR(255)   NOT NULL,
    city            VARCHAR(255)   NOT NULL,
    country         VARCHAR(255)
);

  3. 通过运行以下查询，来创建 customers 表。此表存储客户信息，包括客户 ID、姓名、详细联系信息、地址参考信息、出生日期和记录创建时间。

    CREATE TABLE nla_demo.customers (
    customer_id     SERIAL         PRIMARY KEY,
    first_name      VARCHAR(255)   NOT NULL,
    last_name       VARCHAR(255)   NOT NULL,
    email           VARCHAR(255)   UNIQUE NOT NULL,
    address_id      INTEGER        REFERENCES nla_demo.addresses(address_id),
    date_of_birth   DATE,
    created_at      TIMESTAMP      DEFAULT CURRENT_TIMESTAMP
);

  4. 创建用于存储商品类别的 categories 表。

    CREATE TABLE nla_demo.categories (
    category_id     INTEGER        PRIMARY KEY,
    category_name   VARCHAR(255)   UNIQUE NOT NULL
);

  5. 创建 products 表。此表存储商品信息，例如商品 ID、名称、说明、品牌、类别关联和记录创建时间。

    CREATE TABLE nla_demo.products (
    product_id    INTEGER        PRIMARY KEY,
    name          VARCHAR(255)   NOT NULL,
    description   TEXT           DEFAULT 'Not available',
    brand_id      INTEGER,
    category_id   INTEGER        REFERENCES nla_demo.categories(category_id),
    created_at    TIMESTAMP      DEFAULT CURRENT_TIMESTAMP
);

  6. 创建 orders 表。此表存储有关客户订单的信息，包括客户、日期、总金额、送货地址和账单邮寄地址以及订单状态。

    CREATE TABLE nla_demo.orders (
    order_id            INTEGER        PRIMARY KEY,
    customer_id         INTEGER        REFERENCES nla_demo.customers(customer_id),
    order_date          TIMESTAMP      DEFAULT CURRENT_TIMESTAMP,
    total_amount        DECIMAL(10, 2) NOT NULL,
    shipping_address_id INTEGER        REFERENCES nla_demo.addresses(address_id),
    billing_address_id  INTEGER        REFERENCES nla_demo.addresses(address_id),
    order_status        VARCHAR(50)
);

  7. 创建 order_items 表。此表记录订单中的各个商品，链接到订单和商品款式，并指定数量和价格。

    CREATE TABLE nla_demo.order_items (
    order_item_id   SERIAL         PRIMARY KEY,
    order_id        INTEGER        REFERENCES nla_demo.orders(order_id),
    product_id      INTEGER        REFERENCES nla_demo.products(product_id),
    quantity        INTEGER        NOT NULL,
    price           DECIMAL(10, 2) NOT NULL
);

填充 nla_demo 架构中的表

  1. 通过运行以下查询，来填充 addresses 表。

    INSERT INTO nla_demo.addresses (street_address, city, country)
VALUES
    ('1800 Amphibious Blvd', 'Mountain View', 'USA'),
    ('Avenida da Pastelaria, 1903', 'Lisbon', 'Portugal'),
    ('8 Rue du Nom Fictif 341', 'Paris', 'France');

  2. 填充 customers 表。

    INSERT INTO nla_demo.customers (first_name, last_name, email, address_id, date_of_birth)
VALUES
    ('Alex', 'B.', 'alex.b@example.com', 1, '2003-02-20'),
    ('Amal', 'M.', 'amal.m@example.com', 2, '1998-11-08'),
    ('Dani', 'G.', 'dani.g@example.com', 3, '2002-07-25');

  3. 填充 categories 表。

    INSERT INTO nla_demo.categories (category_id, category_name)
VALUES
    (1, 'Accessories'),
    (2, 'Apparel'),
    (3, 'Footwear'),
    (4, 'Swimwear');

  4. 填充 products 表。

    INSERT INTO nla_demo.products (product_id, brand_id, category_id, name)
VALUES
    (1, 1, 2, 'Hoodie'),
    (2, 1, 3, 'Running Shoes'),
    (3, 2, 4, 'Swimsuit'),
    (4, 3, 1, 'Tote Bag');

  5. 填充 orders 表。

    INSERT INTO nla_demo.orders (order_id, customer_id, total_amount, shipping_address_id, billing_address_id, order_status)
VALUES
    (1, 1, 99.99, 1, 1, 'Shipped'),
    (2, 1, 69.99, 1, 1, 'Delivered'),
    (3, 2, 20.99, 2, 2, 'Processing'),
    (4, 3, 79.99, 3, 3, 'Shipped');

  6. 填充 order_items 表。

    INSERT INTO nla_demo.order_items (order_id, product_id, quantity, price)
VALUES
    (1, 1, 1, 79.99),
    (1, 3, 1, 20.00),
    (2, 4, 1, 69.99),
    (3, 3, 1, 20.00),
    (4, 2, 1, 79.99);

创建自然语言配置

如需使用 AlloyDB AI 自然语言，请确保配置 Vertex AI 端点。然后，创建配置并注册架构。 g_alloydb_ai_nl.g_create_configuration 会创建模型。

  1. 创建自然语言配置。

    SELECT alloydb_ai_nl.g_create_configuration( 'nla_demo_cfg' );

  2. 将表注册到 nla_demo_cfg 配置。

    SELECT alloydb_ai_nl.g_manage_configuration(
    operation => 'register_table_view',
    configuration_id_in => 'nla_demo_cfg',
    table_views_in=>'{nla_demo.customers, nla_demo.addresses, nla_demo.products, nla_demo.categories, nla_demo.orders, nla_demo.order_items}'
);

为表和列创建并应用上下文

为了针对自然语言问题提供准确的答案，您可以使用 AlloyDB AI Natural Language API 提供有关表、视图和列的上下文。您可以使用 AlloyDB AI Natural Language API 的自动上下文生成功能从表和列生成上下文，并应用作为 COMMENTS 附加到表、视图和列的上下文。

  1. 如需为在 nla_demo_cfg 配置中注册的表及其列生成架构上下文，请运行以下命令：

    SELECT alloydb_ai_nl.generate_schema_context(
  'nla_demo_cfg',
  TRUE
);

    上述查询会使用上下文填充 alloydb_ai_nl.generated_schema_context_view 视图。传递 TRUE 会覆盖此视图中之前运行的上下文。

  2. 如需验证为 nla_demo.products 表生成的上下文，请运行以下查询：

    SELECT object_context
FROM alloydb_ai_nl.generated_schema_context_view
WHERE schema_object = 'nla_demo.products';

    生成的上下文类似于以下内容：

    The products table stores information about products, including their name,
a brief description, the brand they belong to (referenced by brand_id),
and the category they fall under (referenced by category_id). Each product
has a unique identifier (product_id) and a timestamp indicating its creation
time (created_at).

  3. 如需验证为列（例如 nla_demo.products.name）生成的上下文，请运行以下命令：

    SELECT object_context
FROM alloydb_ai_nl.generated_schema_context_view
WHERE schema_object = 'nla_demo.products.name';

    查询输出类似于以下内容：

    The name column in the nla_demo.products table contains the specific
name or title of each product. This is a short, descriptive text string
that clearly identifies the product, like "Hoodie," "Tote Bag,"
"Running Shoes," or "Swimsuit." It helps distinguish individual products
within the broader context of their brand and category. The name column
specifies the exact product. This column is essential for users and
systems to identify and refer to specific products within the database.

  4. 在 alloydb_ai_nl.generated_schema_context_view 视图中查看生成的上下文，并更新需要修订的上下文。

    SELECT alloydb_ai_nl.update_generated_relation_context(
  'nla_demo.products',
  'The "nla_demo.products" table stores product details such as ID, name, description, brand, category linkage, and record creation time.'
);

SELECT alloydb_ai_nl.update_generated_column_context(
  'nla_demo.products.name',
  'The "name" column in the "nla_demo.products" table contains the specific name or title of each product.'
);

  5. 应用将附加到相应对象的生成的上下文：

    SELECT alloydb_ai_nl.apply_generated_relation_context(
  'nla_demo.products', true
);

SELECT alloydb_ai_nl.apply_generated_column_context(
  'nla_demo.products.name',
  true
);

    alloydb_ai_nl.generated_schema_context_view 视图中生成的上下文条目会应用于相应的架构对象，并且注释会被覆盖。

构建值索引

AlloyDB AI Natural Language API 使用值关联来生成准确的 SQL 查询。值关联会将自然语言语句中的值短语与预注册的概念类型和列名称关联，从而可以丰富自然语言问题。

例如，如果 Hoodie 与 product_name 概念关联，而后者又与 nla_demo.products.name 关联，那么系统可以更准确地回答“告诉我连帽衫的价格”这一问题。 。

  1. 如需定义 product_name 概念类型并将其与 nla_demo.products.name 列关联，请运行以下查询：

    SELECT alloydb_ai_nl.add_concept_type(
    concept_type_in => 'product_name',
    match_function_in => 'alloydb_ai_nl.get_concept_and_value_generic_entity_name',
    additional_info_in => '{
      "description": "Concept type for product name.",
      "examples": "SELECT alloydb_ai_nl.get_concept_and_value_generic_entity_name(''Camera'')" }'::jsonb
);
SELECT alloydb_ai_nl.associate_concept_type(
    'nla_demo.products.name',
    'product_name'
);

  2. 如需验证 product_name 概念类型是否已添加到概念类型列表中，请运行以下查询，以确保 product_name 包含在此查询的结果中：

    SELECT alloydb_ai_nl.list_concept_types();

  3. 如需验证 nla_demo.products.name 列是否与 product_name 概念类型关联，请运行以下查询：

    SELECT *
FROM alloydb_ai_nl.value_index_columns
WHERE column_names = 'nla_demo.products.name';

  4. 定义概念类型并将列与其关联后，请创建值索引。

    SELECT alloydb_ai_nl.create_value_index('nla_demo_cfg');
SELECT alloydb_ai_nl.refresh_value_index('nla_demo_cfg');

定义查询模板

您可以定义模板，以提高 AlloyDB AI Natural Language API 生成的答案的质量。

  1. 如需为业务关键问题提供示例模板，并提供预期高度准确的预期问题，请运行以下查询以添加模板：

    SELECT alloydb_ai_nl.add_template(
    nl_config_id => 'nla_demo_cfg',
    intent => 'List the first names and the last names of all customers who ordered Swimsuit.',
    sql => 'SELECT c.first_name, c.last_name FROM nla_demo.Customers c JOIN nla_demo.orders o ON c.customer_id = o.customer_id JOIN nla_demo.order_items oi ON o.order_id = oi.order_id JOIN nla_demo.products p ON oi.product_id = p.product_id  AND p.name = ''Swimsuit''',
    sql_explanation => 'To answer this question, JOIN `nla_demo.Customers` with `nla_demo.orders` on having the same `customer_id`, and JOIN the result with nla_demo.order_items on having the same `order_id`. Then JOIN the result with `nla_demo.products` on having the same `product_id`, and filter rwos that with p.name = ''Swimsuit''. Return the `first_name` and the `last_name` of the customers with matching records.',
    check_intent => TRUE
);

  2. 如需查看已添加的模板列表，请查询 alloydb_ai_nl.template_store_view：

    SELECT nl, sql, intent, psql, pintent
FROM alloydb_ai_nl.template_store_view
WHERE config = 'nla_demo_cfg';

    系统会返回以下输出：

    nl      | List the first names and the last names of all customers who ordered Swimsuit.
sql     | SELECT c.first_name, c.last_name
        | FROM nla_demo.Customers c
        | JOIN nla_demo.orders o ON c.customer_id = o.customer_id
        | JOIN nla_demo.order_items oi ON o.order_id = oi.order_id
        | JOIN nla_demo.products p ON oi.product_id = p.product_id
        | AND p.name = 'Swimsuit'
intent  | List the first names and the last names of all customers who ordered
        | Swimsuit.
psql    | SELECT c.first_name, c.last_name
        | FROM nla_demo.Customers c JOIN nla_demo.orders o
        | ON c.customer_id = o.customer_id 
        | JOIN nla_demo.order_items oi ON o.order_id = oi.order_id
        | JOIN nla_demo.products p ON oi.product_id = p.product_id
        | AND p.name = $1
pintent | List the first names and the last names of all customers who ordered
        | $1.

    在此模板中，与 psql 属性对应的值是参数化 SQL 查询，而 pintent 列的值是参数化 intent 语句。最近添加的模板的 id 可能会有所不同，具体取决于之前添加的模板。模板用于针对问题提供高度准确的答案。

通过自然语言问题生成 SQL 结果

  1. 如需使用 AlloyDB AI Natural Language API 生成 SQL 查询和结果集，请运行以下查询：

    SELECT
    alloydb_ai_nl.get_sql(
        'nla_demo_cfg',
        'Find the customers who purchased Tote Bag.'
    ) ->> 'sql';

    系统会返回以下输出：

    SELECT DISTINCT "c"."first_name", "c"."last_name"
FROM "nla_demo"."customers" AS "c"
JOIN "nla_demo"."orders" AS "o" ON "c"."customer_id" = "o"."customer_id"
JOIN "nla_demo"."order_items" AS "oi" ON "o"."order_id" = "oi"."order_id"
JOIN "nla_demo"."products" AS "p" ON "oi"."product_id" = "p"."product_id"
WHERE "p"."name" = 'Tote Bag';

    JSON 输出是使用您在定义查询模板中添加的模板的 SQL 查询。

  2. 如需使用 AlloyDB AI Natural Language API 生成问题的结果，请运行以下查询：

    SELECT
alloydb_ai_nl.execute_nl_query(
    'Find the last name of the customers who live in Lisbon.',
    'nla_demo_cfg'
);

    系统会返回以下输出：

    execute_nl_query     
--------------------------
{"last_name":"M."}

清理

为避免因本教程中使用的资源导致您的 Google Cloud 账号产生费用，请删除包含这些资源的项目，或者保留项目但删除各个资源。

以下部分介绍了如何删除这些资源和对象。

删除集群

删除您在准备工作中创建的集群时，您创建的所有对象也会删除。

  1. 在 Google Cloud 控制台中，前往集群页面。

    转到集群

  2. 点击资源名称列中集群 my-cluster 的名称。

  3. 点击 删除集群。

  4. 在删除集群 my-cluster 中，输入 my-cluster 以确认您要删除集群。

  5. 点击删除。

  6. 如果您在创建集群时创建了专用连接，请前往 Google Cloud 控制台的 VPC 网络页面，然后点击删除 VPC 网络。

删除对象

您可以选择保留您在准备工作中设置的资源，并且可以仅删除您在 Google Cloud 项目中创建的对象。

  1. 如需移除您在定义查询模板中定义的模板，请运行以下查询：

    SELECT alloydb_ai_nl.drop_template(id)
FROM alloydb_ai_nl.template_store_view
WHERE config = 'nla_demo_cfg';

  2. 如需移除您在构建值索引中定义的 product_name 概念类型，请运行以下查询：

    SELECT alloydb_ai_nl.drop_concept_type('product_name');

  3. 如需在移除 product_name 概念类型后刷新值索引，请运行以下查询：

    SELECT alloydb_ai_nl.refresh_value_index();

  4. 如需移除您在创建自然语言配置中创建的 nla_demo_cfg 配置，请运行以下查询：

    SELECT
alloydb_ai_nl.g_manage_configuration(
    'drop_configuration',
    'nla_demo_cfg'
);

  5. 如需移除您在创建 nla_demo 架构和表和在 nla_demo 架构中填充表中创建和填充的 nla_demo 架构和表，请运行以下查询：

    DROP SCHEMA nla_demo CASCADE;

后续步骤