AlloyDB AI の自然言語を使用して SQL を生成する

このチュートリアルでは、Google Cloud コンソールを使って AlloyDB AI 自然言語 API を設定し使用する方法について説明します。自然言語で質問して SQL クエリと結果を取得できるように、AlloyDB AI 自然言語 API を構成する方法について学びましょう。

目標

テーブルを作成してデータを入力し、自動生成を使用してコンテキストを作成します。
データベース内の列に値インデックスを作成します。
自然言語構成（nl_config）オブジェクトを作成して構成します。
アプリケーションのサンプルクエリのテンプレートを作成します。
get_sql() 関数を使用して、質問に回答する SQL クエリを生成します。
execute_nl_query() 関数を使用し、データベースを使って自然言語の質問に回答します。

費用

このドキュメントでは、課金対象である次の Google Cloudコンポーネントを使用します。

料金計算ツールを使うと、予想使用量に基づいて費用の見積もりを生成できます。新規の Google Cloud ユーザーは無料トライアルをご利用いただける場合があります。

このドキュメントに記載されているタスクの完了後、作成したリソースを削除すると、それ以上の請求は発生しません。詳細については、クリーンアップをご覧ください。

始める前に

アクセスをリクエストする

AlloyDB AI の自然言語を使用して SQL を生成する前に、AlloyDB AI の自然言語へのアクセスをリクエストし、有効化の確認が届くまで待ってから、このチュートリアルの手順に沿って操作してください。

課金と必要な API を有効にする

Google Cloud コンソールで、プロジェクトを選択します。

プロジェクトセレクタに移動
Google Cloud プロジェクトに対して課金が有効になっていることを確認します。
AlloyDB for PostgreSQL の作成と接続に必要な Cloud APIs を有効にします。

API を有効にする
1. [プロジェクトを確認] の手順で、[次へ] をクリックして、変更するプロジェクトの名前を確認します。
2. [API を有効にする] の手順で、[有効にする] をクリックして、次の機能を有効にします。
  - AlloyDB API

データベースを作成して接続する

クラスタとそのプライマリインスタンスを作成します。
インスタンスに接続してデータベースを作成します。
Vertex AI の統合を有効にします。詳細については、Vertex AI と統合するをご覧ください。

必要な拡張機能をインストールする

AlloyDB AI 自然言語サポート API である alloydb_ai_nl 拡張機能をインストールするには、次のクエリを実行します。

CREATE EXTENSION google_ml_integration;
CREATE EXTENSION alloydb_ai_nl cascade;

nla_demo スキーマとテーブルを作成する

次の手順では、nla_demo スキーマとスキーマ内のテーブルを作成します。テーブルに合成データを入力します。提供されるスキーマとデータは、オンライン小売ビジネスの基本的なオペレーションをサポートするように設計されており、顧客管理、分析、マーケティング、運用の各側面に応用できます。

サンプルデータは、開発、テスト、デモの目的で AlloyDB AI の自然言語を使用する方法を示しています。特に、自然言語インターフェースなどの機能に使用できます。

次のクエリを実行してスキーマを作成します。
```
CREATE SCHEMA nla_demo;
```

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)
);

次のクエリを実行して 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
);

商品カテゴリを格納する categories テーブルを作成します。

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

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
);

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)
);

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 スキーマのテーブルにデータを入力する

次のクエリを実行して、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');

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');

categories テーブルにデータを入力します。

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

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');

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');

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 でモデルを作成します。

自然言語の構成を作成します。

SELECT alloydb_ai_nl.g_create_configuration( 'nla_demo_cfg' );

テーブルを 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 自然言語 API を使用して、テーブル、ビュー、列に関するコンテキストを指定します。AlloyDB AI 自然言語 API の自動コンテキスト生成機能を使用すると、テーブルと列からコンテキストを生成し、テーブル、ビュー、列に接続された COMMENTS としてコンテキストを適用できます。

nla_demo_cfg 構成に登録されているテーブルとその列のスキーマコンテキストを生成するには、次のコマンドを実行します。
```
SELECT alloydb_ai_nl.generate_schema_context(
  'nla_demo_cfg',
  TRUE
);
```
上記のクエリでは、alloydb_ai_nl.generated_schema_context_view ビューにコンテキストが入力されます。TRUE を渡すと、このビューの以前の実行のコンテキストが上書きされます。

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).

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.

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.'
);

生成されたコンテキストを適用して、対応するオブジェクトに関連付けます。
```
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 自然言語 API は、値のリンクを使用して正確な SQL クエリを生成します。値のリンクでは、自然言語ステートメントの値のフレーズを、事前登録されたコンセプト型と列名に関連付けることで、自然言語の質問を拡充できます。

たとえば、「パーカーの価格を教えて」という質問には、nla_demo.products.name 列に関連付けられている product_name コンセプトに Hoodie が関連付けられている場合、より正確に回答できます。

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'
);

product_name コンセプトタイプがコンセプトタイプのリストに追加されていることを確認するには、次のクエリを実行して、このクエリの結果に product_name が含まれていることを確認します。
```
SELECT alloydb_ai_nl.list_concept_types();
```
nla_demo.products.name 列が product_name コンセプトタイプに関連付けられていることを確認するには、次のクエリを実行します。
```
SELECT *
FROM alloydb_ai_nl.value_index_columns
WHERE column_names = 'nla_demo.products.name';
```

コンセプトタイプを定義して列と関連付けたら、値インデックスを作成します。

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

クエリテンプレートを定義する

テンプレートを定義して、AlloyDB AI 自然言語 API によって生成される回答の品質を向上させることが可能です。

ビジネスクリティカルな質問のテンプレートの例を提供し、予想される質問（高い精度が求められる）を提供するには、次のクエリを実行してテンプレートを追加します。

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
);

追加されたテンプレートのリストを表示するには、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 列の値はパラメータ化されたインテントステートメントです。最近追加したテンプレートの id は、前に追加したテンプレートによって異なる場合があります。テンプレートは、質問に非常に正確な回答を提供するために使用されます。

自然言語による質問から SQL の結果を生成する

AlloyDB AI 自然言語 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 クエリです。

AlloyDB AI の自然言語 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 アカウントに課金されないようにするには、リソースを含むプロジェクトを削除するか、プロジェクトを維持して個々のリソースを削除します。

以降のセクションでは、これらのリソースとオブジェクトを削除する方法について説明します。

クラスタを削除する

始める前にで作成したクラスタを削除すると、作成したすべてのオブジェクトも削除されます。

Google Cloud コンソールで [クラスタ] ページに移動します。

[クラスタ] に移動
[リソース名] 列でクラスタ名 my-cluster をクリックします。
[クラスタを削除] をクリックします。
[Delete cluster my-cluster] で「my-cluster」と入力して、クラスタの削除を確認します。
[削除] をクリックします。
クラスタを作成するときにプライベート接続を作成した場合は、Google Cloud コンソールで [VPC ネットワーク] ページに移動し、[VPC ネットワークの削除] をクリックします。

オブジェクトを削除する

始める前にで設定したリソースは保持し、 Google Cloud プロジェクトで作成したオブジェクトのみを削除することもできます。

クエリテンプレートを定義するで定義したテンプレートを削除するには、次のクエリを実行します。
```
SELECT alloydb_ai_nl.drop_template(id)
FROM alloydb_ai_nl.template_store_view
WHERE config = 'nla_demo_cfg';
```
値インデックスを構築するで定義した product_name コンセプトタイプを削除するには、次のクエリを実行します。
```
SELECT alloydb_ai_nl.drop_concept_type('product_name');
```
product_name コンセプトタイプを削除した後に値インデックスを更新するには、次のクエリを実行します。
```
SELECT alloydb_ai_nl.refresh_value_index();
```
自然言語構成を作成するで作成した nla_demo_cfg 構成を削除するには、次のクエリを実行します。
```
SELECT
alloydb_ai_nl.g_manage_configuration(
    'drop_configuration',
    'nla_demo_cfg'
);
```
nla_demo スキーマとテーブルを作成するで作成し、nla_demo スキーマのテーブルにデータを入力するでデータを入力した nla_demo スキーマとテーブルを削除するには、次のクエリを実行します。
```
DROP SCHEMA nla_demo CASCADE;
```

次のステップ

AlloyDB AI の自然言語のユースケースと主な機能について確認する。
自然言語の質問に答える SQL クエリを生成する。

AlloyDB AI の自然言語を使用して SQL を生成する コレクションでコンテンツを整理 必要に応じて、コンテンツの保存と分類を行います。

目標

費用

始める前に

アクセスをリクエストする

課金と必要な API を有効にする

データベースを作成して接続する

必要な拡張機能をインストールする

nla_demo スキーマとテーブルを作成する

nla_demo スキーマのテーブルにデータを入力する

自然言語の構成を作成する

テーブルと列のコンテキストを作成して適用する

値インデックスを構築する

クエリ テンプレートを定義する

自然言語による質問から SQL の結果を生成する

クリーンアップ

クラスタを削除する

オブジェクトを削除する

次のステップ

AlloyDB AI の自然言語を使用して SQL を生成する

クエリテンプレートを定義する