AlloyDB AI 자연어를 사용하여 SQL 생성

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 자연어 API를 사용하여 테이블, 뷰, 열에 관한 컨텍스트를 제공합니다. AlloyDB AI 자연어 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 자연어 API는 값 연결을 사용하여 정확한 SQL 쿼리를 생성합니다. 값 연결은 자연어 문장의 값 구문을 사전 등록된 개념 유형 및 열 이름과 연결하여 자연어 질문을 보강할 수 있습니다.

예를 들어 '후드티 가격 알려 줘'라는 질문에 Hoodie가 nla_demo.products.name와 연결된 product_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 자연어 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 열의 값은 매개변수가 지정된 인텐트 문입니다. 최근에 추가된 템플릿의 id는 이전에 추가된 템플릿에 따라 다를 수 있습니다. 템플릿은 질문에 매우 정확한 답변을 제공하는 데 사용됩니다.

자연어 질문에서 SQL 결과 생성

1. 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 쿼리입니다.

2. 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."}