目標
在本教學課程中，您將瞭解以下內容：
費用
本教學課程使用 Google Cloud的計費元件，包括：
- Spanner
- Vertex AI
如要進一步瞭解 Spanner 費用，請參閱 Spanner 定價頁面。
如要進一步瞭解 Vertex AI 費用，請參閱 Vertex AI 定價頁面。
建立電子商務網站結構定義
在本教學課程中，我們使用下列結構定義和資料：
CREATE TABLE Products (
id INT64,
name STRING(MAX),
description STRING(MAX),
category_id INT64,
) PRIMARY KEY(id);
CREATE TABLE Categories (
id INT64,
name STRING(MAX)
) PRIMARY KEY(id);
CREATE TABLE Users (
id INT64,
age INT64,
likes STRING(MAX)
) PRIMARY KEY(id);
INSERT INTO Categories (id, name) VALUES
(1, "Toys"),
(2, "Tools");
INSERT INTO Products (id, name, description, category_id) VALUES
(1, "Plush Bear", "Really fluffy. Safe for infants.", 1),
(2, "Bike", "Bike for teenagers.", 1),
(3, "Drill", "Cordless.", 2);
INSERT INTO Users (id, age, likes) VALUES
(1, 30, "DIY"),
(2, 14, "Toys");
在 Spanner 結構定義中註冊生成式 AI 模型
在本教學課程中，我們將使用 Vertex AI text-bison 模型，為終端消費者提供個人化產品推薦。如要在 Spanner 資料庫中註冊這個模型，請執行下列 DDL 陳述式：
CREATE MODEL TextBison
INPUT (prompt STRING(MAX))
OUTPUT (content STRING(MAX))
REMOTE
OPTIONS (
endpoint = '//aiplatform.googleapis.com/projects/PROJECT/locations/LOCATION/publishers/google/models/text-bison'
);
更改下列內容：
PROJECT：專案 IDLOCATION：您使用 Vertex AI 的區域
生成式 AI 模型不支援結構定義探索和驗證。因此，您必須提供符合模型結構定義的 INPUT 和 OUTPUT 子句。如要查看模型結構定義的完整內容，請前往 Vertex AI Model API 參考資料頁面。text-bison
只要資料庫和端點位於同一專案，Spanner 就會自動授予適當的權限。否則，請參閱 CREATE MODEL 參考頁面的「模型端點存取權控管」一節。
如要確認模型是否已正確註冊，請使用 ML.PREDICT 函式查詢模型。模型預期會有一個名為 prompt 的 STRING 資料欄。您可以使用 Spanner 子查詢產生 prompt 欄。TextBison 模型需要指定 maxOutputTokens 模型參數。其他參數則為選用。Vertex AI text-bison 模型不支援批次處理，因此您必須使用 @{remote_udf_max_rows_per_rpc=1} 參數將批次大小設為 1。
SELECT content
FROM ML.PREDICT(
MODEL TextBison,
(SELECT "Is 13 prime?" AS prompt),
STRUCT(256 AS maxOutputTokens, 0.2 AS temperature, 40 as topK, 0.95 AS topP)
) @{remote_udf_max_rows_per_rpc=1};
+--------------------+
| content |
+--------------------+
| "Yes, 13 is prime" |
+--------------------+
使用 TextBison 模型回答顧客問題
生成式 AI 文字模型可解決各種問題。舉例來說，使用者在電子商務網站上瀏覽嬰兒適用產品時，只要一個查詢，就能將問題傳送給 TextBison 模型。我們只需要從資料庫擷取產品詳細資料，為問題提供相關背景資訊。
注意：為求簡潔，部分模型答案經過編輯。
SELECT product_id, product_name, content
FROM ML.PREDICT(
MODEL TextBison,
(SELECT
product.id as product_id,
product.name as product_name,
CONCAT("Is this product safe for infants?", "\n",
"Product Name: ", product.name, "\n",
"Category Name: ", category.name, "\n",
"Product Description:", product.description) AS prompt
FROM
Products AS product JOIN Categories AS category
ON product.category_id = category.id),
STRUCT(100 AS maxOutputTokens)
) @{remote_udf_max_rows_per_rpc=1};
-- The model correctly recommends a Plush Bear as safe for infants.
-- Other products are not safe and the model provides justification why.
+------------+-----------------+--------------------------------------------------------------------------------------------------+
| product_id | product_name | content |
+------------+-----------------+--------------------------------------------------------------------------------------------------+
| 1 | "Plush Bear" | "Yes, this product is infant safe. [...] " |
| | | "The product description says that the product is safe for infants. [...]" |
+------------+-----------------+--------------------------------------------------------------------------------------------------+
| 2 | "Bike" | "No, this product is not infant safe. [...] " |
| | | "It is not safe for infants because it is too big and heavy for them to use. [...]" |
+------------+-----------------+--------------------------------------------------------------------------------------------------+
| 3 | "Drill" | "No, this product is not infant safe. [...]" |
| | | " If an infant were to grab the drill, they could pull it on themselves and cause injury. [...]" |
+------------+-----------------+--------------------------------------------------------------------------------------------------+
如要直接填入顧客問題，可以將問題字面值替換為查詢參數，例如 @UserQuestion。為顧客提供 AI 輔助的線上購物體驗。
為顧客提供個人化的產品推薦
除了產品詳細資料，我們也可以在 prompt 中新增顧客資訊。模型會將使用者偏好納入考量，提供完全個人化的產品推薦內容。
SELECT product_id, product_name, content
FROM ML.PREDICT(
MODEL TextBison,
(SELECT
product.id as product_id,
product.name as product_name,
CONCAT(
"Answer with YES or NO only: Is this a good fit for me?",
"My age:", CAST(user.age AS STRING), "\n",
"I like:", user.likes, "\n",
"Product name: ", product.name, "\n",
"Category mame: ", category.name, "\n",
"Product description:", product.description) AS prompt,
FROM
Products AS product
JOIN Categories AS category ON product.category_id = category.id
JOIN Users AS user ON user.id = 1),
STRUCT(256 AS maxOutputTokens)
) @{remote_udf_max_rows_per_rpc=1};
-- The model correctly guessed that the user might be interested in a Drill
-- as they are interested in DIY.
+------------+-----------------+-------------+
| product_id | product_name | content |
+------------+-----------------+-------------+
| 1 | "Plush Bear" | "NO" |
+------------+-----------------+-------------+
| 2 | "Bike" | "NO" |
+------------+-----------------+-------------+
| 3 | "Drill" | "YES" |
+------------+-----------------+-------------+
如要為孩子尋找禮物，使用者可以為青少年建立個人資料，並查看不同的推薦清單：
SELECT product_id, product_name, content
FROM ML.PREDICT(
MODEL TextBison,
(SELECT
product.id as product_id,
product.name as product_name,
CONCAT(
"Answer with YES or NO only: Is this a good fit for me?",
"\nMy's age:", CAST(user.age AS STRING),
"\nI like:", user.likes,
"\nProduct Name: ", product.name,
"\nCategory Name: ", category.name,
"\nProduct Description:", product.description) AS prompt,
FROM
Products AS product
JOIN Categories AS category ON product.category_id = category.id
JOIN Users AS user ON user.id = 2),
STRUCT(40 AS maxOutputTokens)
) @{remote_udf_max_rows_per_rpc=1};
-- The model correctly guesses that a teenager is interested in a Bike,
-- but not a plush bear for infants or spicy peppers.
+------------+-----------------+---------+
| product_id | product_name | content |
+------------+-----------------+---------+
| 1 | "Plush Bear" | "NO" |
+------------+-----------------+---------+
| 2 | "Bike" | "YES" |
+------------+-----------------+---------+
| 3 | "Spicy peppers" | "NO" |
+------------+-----------------+---------+
您可以在提示中加入購物記錄或其他相關詳細資料，為顧客提供更個人化的體驗。
有了 Spanner Vertex AI 整合功能，您就能組合含有即時資料的複雜提示，並使用這些提示建構支援 AI 的應用程式。