Bookshelf 數據分析：透過 BigQuery 和生成式 AI 運用 Gemini 建構 SQL 應用程式

1. 簡介

你喜歡深入瞭解書籍，但選擇太多讓你不知從何下手嗎？想像一下，如果有一款 AI 輔助應用程式，不僅能推薦最適合你的讀物，還能根據你選擇的類型提供簡要摘要，讓你一窺書中精髓。在本程式碼研究室中，我將逐步說明如何使用 BigQuery、Gemini 和 Gemini 支援的 Cloud Functions 建構這類應用程式。

專案總覽

我們的用途圍繞以下 4 個主要元件：

書籍資料庫：網際網路檔案館的龐大 BigQuery 公開資料集將做為我們完整的書籍目錄。
AI 摘要引擎：Google Cloud Functions 搭載 Gemini 1.0 Pro 語言模型，可根據使用者要求生成實用摘要。
BigQuery 整合：BigQuery 中的遠端函式，可呼叫我們的 Cloud Function，提供隨選書籍摘要和主題。
使用者介面：託管在 Cloud Run 上的網頁應用程式，可為使用者提供網頁應用程式，方便查看結果。

我們會將實作項目分成 3 個程式碼研究室：

程式碼研究室 1：使用 Gemini 為 Gemini 應用程式建構 Java Cloud Function。

程式碼實驗室 2：使用 Gemini 搭配 BigQuery 和 Generative AI 建構 SQL 應用程式。

程式碼實驗室 3：使用 Gemini 建立可與 BigQuery 互動的 Java Spring Boot 網頁應用程式。

2. 使用 Gemini 搭配 BigQuery 和生成式 AI 建構 SQL 應用程式

建構項目

您將建立

BigQuery 中的遠端模型會叫用 Vertex AI text-bison-32k 端點，從表格中以「;」分隔的關鍵字清單，識別書籍的類型 (或主題)。
BigQuery 中的遠端函式，可遠端叫用已部署的生成式 AI Cloud Function。
使用遠端模型和函式，透過 SQL 查詢摘要說明書籍的主題和內容，並將結果寫入書架資料集的新資料表。
您將在 Gemini 的協助下完成這些步驟

3. 需求條件

Chrome 或 Firefox 瀏覽器
已啟用計費功能的 Google Cloud 專案
如果您已部署 Cloud Function，這對您很有幫助。這項部署作業是使用 Gemini 建構 Gemini 應用程式的 Java Cloud Function 程式碼研究室第 1 部分的一部分。
視情況：如果此時您有權存取免費的 Google Cloud 抵免額連結 (可能由研討會主辦單位提供)，請先按照下方頁面的操作說明完成「啟用抵免額和建立專案」步驟。如果沒有這個連結，請繼續執行下列專案和帳單先決條件步驟：

建立專案

如果您已啟用帳單帳戶，並使用上述條件步驟中提及的連結建立專案，則可略過下列步驟。

在 Google Cloud 控制台的專案選取器頁面中，選取或建立 Google Cloud 專案。
確認 Cloud 專案已啟用計費功能。瞭解如何檢查專案是否已啟用計費功能。

啟用 Cloud Shell

您將使用 Cloud Shell，這是 Google Cloud 中執行的指令列環境，已預先載入 bq：

在 Cloud 控制台，按一下右上角的「啟用 Cloud Shell」：

連至 Cloud Shell 後，您應該會看到驗證已完成，專案也已設為獲派的專案 ID。在 Cloud Shell 中執行下列指令，確認您已通過驗證：

gcloud auth list

在 Cloud Shell 中執行下列指令，確認 gcloud 指令已瞭解您的專案。

gcloud config list project

如果未設定專案，請使用下列指令來設定：

gcloud config set project <YOUR_PROJECT_ID>

如要瞭解 gcloud 指令和用法，請參閱說明文件。

4. 啟用 Gemini for Google Cloud 和必要的 API

啟用 Gemini

前往 Gemini for Google Cloud Marketplace 啟用 API。您也可以使用下列指令：

gcloud services enable cloudaicompanion.googleapis.com --project PROJECT_ID

前往 Gemini 頁面，然後按一下「開始對話」。

啟用其他必要的 API

我們該怎麼做？我們來問問 Gemini 吧！但請先注意以下事項：

注意：LLM 具有非決定性。因此，您嘗試這些提示時，收到的回覆可能與我螢幕截圖中的回覆不同。

在 Google Cloud 控制台的搜尋列旁，按一下右上角的「開啟 Gemini」圖示，前往 Gemini 對話控制台。

在「在這裡輸入提示」部分輸入以下問題：

如何使用 gcloud 指令啟用 BigQuery 和 Vertex AI API？

您會收到如下圖所示的回應：

複製該指令 (您可以使用指令程式碼片段頂端的複製圖示)，並在 Cloud Shell 終端機中執行，以啟用相關服務：

bigquery.googleapis.com
aiplatform.googleapis.com

5. 探索 BigQuery 書籍資料公開資料集

首先，請熟悉 BigQuery 公開資料集，其中包含大量網際網路檔案館書籍的相關資訊。

您可以在 BigQuery 探索器窗格中找到這個公開資料集。登陸 BigQuery 控制台後，即可在左側找到這個選項。

在搜尋列中輸入「gdelt-bq」或「internetarchivebooks」，然後按一下「SEARCH ALL PROJECTS」(搜尋所有專案)。在結果中展開並為網際網路檔案館書籍加上星號，如下圖所示：

。

展開資料集，按一下 gdelt-bq.internetarchivebooks，然後預覽 1920 資料表中的資料。這個表格包含 1920 年封存的書籍。

如要查看後續章節中使用的結構定義，請執行下列查詢：

select * from  `gdelt-bq.internetarchivebooks.1920` limit 5;

在本程式碼研究室中，我們會使用下列三個欄位：

BookMeta_Title (title)
主題 (以「;'」分隔主題)
BookMeta_FullText (書籍全文)

6. 建立名為 bookshelf 的新 BigQuery 資料集

我們要在專案下建立資料集，儲存這個實驗室中建立的所有資料庫和分析物件。我們來詢問 Gemini 如何建立 BigQuery 資料集。在啟用 API 的步驟中，您應該已在瀏覽器的另一個分頁中開啟 Gemini 對話。如果尚未啟用，請立即啟用。前往 Google Cloud 控制台 (https://console.cloud.google.com)，您應該會在頂端的搜尋列旁看到 Gemini 圖示。按一下該訊息，即可開啟對話。

輸入下列提示詞。

我的提示如下：

How to create a BigQuery dataset?

回覆內容如下：

請按照回覆中列出的步驟，在有效專案中建立名為「bookshelf」的資料集。

如要建立 BigQuery 資料集，請按照下列步驟操作：

前往 Google Cloud 控制台的 BigQuery 頁面。
在「Explorer」面板中，按一下專案 ID。
按一下「建立資料集」(點選專案 ID 旁的三點圖示時，應該會列出這個選項)
輸入資料集名稱「bookshelf」。
將位置設為「美國(多區域)」。
在步驟 3、4、5 和 6 中，保留預設選項。
按一下「建立資料集」。

系統會建立資料集，並顯示在「Explorer」面板中。「書架」資料集如下所示：

7. 建立遠端模型來叫用 Vertex AI LLM (text-bison-32k)

接著，我們需要在 BigQuery 中建立模型，叫用「text-bison-32k」Vertex AI 模型。模型會根據資料集中每本書的關鍵字清單，找出書籍的共同主題和背景資訊。

我們來問問 Gemini 這個問題。如要這麼做，請前往開啟 Gemini 對話控制台的分頁，然後輸入下列提示：

您要如何連結 BigQuery 和 Vertex AI，以便在 BigQuery 中呼叫 LLM 端點 (text-bison-32k)？

回覆如下：

回應內容包含準確資訊，例如使用 CREATE MODEL 陳述式、使用 BigQuery 連線，以及定義端點的步驟。查詢陳述式對我來說很準確，但這不代表您會收到完全相同的輸出內容，因為這是大型語言模型，您可能會收到不同格式、數量和深度的回覆。如果沒有看到我收到的所有詳細資料，歡迎在對話中追問。例如：提供如何建立連線資源的詳細資訊、說明連線屬性為何遺失，或說明如何從 BigQuery 連線至 Vertex AI 等。

以下是後續追蹤提示範例 (只有在需要後續追蹤時才使用，如果收到的第一則回覆已足夠，請直接採用)：

What about the connection? How will I connect from BigQuery to Vertex AI?

回覆內容如下：

請按照回覆中的步驟建立 BigQuery 連線：

前往 BigQuery 主控台。
在 BigQuery Explorer 窗格中，按一下「+ 新增」按鈕，然後按一下「連線至外部資料來源」。
此時，系統會要求您啟用 BigQuery Connection API。按一下「啟用 API」：

點選「連線至外部資料來源」，您應該會看到「外部資料來源」滑桿，如下所示。從外部來源清單中，選取「Vertex AI」來源。

輸入連線 ID (可選擇 ID，但目前請設為「bq-vx」) 和區域 (多區域「US」)。
按一下「建立連線」。

建立連線後，按一下「前往連線」。
在連線資訊頁面中，複製服務帳戶 ID，因為我們會在後續步驟中使用這個 ID。
連線建立完成後，請為複製的服務帳戶 ID 指派權限，以便使用 Vertex AI。
在 Google Cloud 控制台頁面中，開啟 Google Cloud IAM 或前往該連結。
在「按照主體查看」部分下方，點選「授予存取權」。

在「授予存取權」對話方塊中，於「新增主體」文字方塊內輸入先前記下的服務帳戶 ID。
將角色設為「Vertex AI 使用者」。

系統會建立必要的連線。主體 (連線的服務帳戶) 具備必要權限，可從 BigQuery 使用 Vertex AI。

在 BigQuery 查詢編輯器中，執行下列 DDL(資料定義語言) 陳述式，代表建立資料庫物件，在本例中為 MODEL。

CREATE OR REPLACE MODEL bookshelf.llm_model
  REMOTE WITH CONNECTION `us.bq-vx`
  OPTIONS (ENDPOINT = 'text-bison-32k');

除了上述步驟，你也可以嘗試要求 Gemini 建議查詢，藉此建立模型來叫用「text-bison-32k」模型。

注意：如果您使用不同的連線名稱，請在先前的 DDL 陳述式中，將「us.bq-vx」替換為該名稱。這項查詢會在先前建立的「bookshelf」資料集中建立遠端模型。

8. 建立可叫用 Java Cloud Functions 的遠端函式

我們現在將使用本系列 Codelab 1 中建立的 Java Cloud Function，在 BigQuery 中建立遠端函式，以實作 Gemini 模型。這個遠端函式將用於總結書籍內容。

注意：如果您錯過這個 codelab，或尚未部署這項 Cloud Function，可以略過這個步驟，直接前往下一個主題 (也就是使用遠端模型摘要說明書籍主題)。

前往 BigQuery 控制台，然後將下列 DDL 陳述式貼到查詢編輯器 (點選「+」按鈕即可建立新的查詢編輯器分頁)

您可以複製下列 DDL。請記得將端點替換為您部署的 Cloud Functions 端點 (在程式碼研究室 1 中建立)。如果沒有端點，您可以將下方 DDL 中的遮蓋字元替換為「abis-345004」，以進行示範。

CREATE OR REPLACE FUNCTION
  `bookshelf.GEMINI_REMOTE_CALL` (context STRING) RETURNS STRING
  REMOTE WITH CONNECTION `us.bq-vx`
  OPTIONS (
    endpoint = 'https://us-central1-****-******.cloudfunctions.net/remote-gemini-call'  );

前往 Google Cloud Platform 的 BigQuery 控制台，然後開啟新的查詢編輯器分頁。在 BigQuery 查詢編輯器中，貼上上述 DDL 陳述式。執行查詢後，您會看到以下回應：

模型和函式都已建立完成，現在請在 SELECT 查詢中執行這兩個 BigQuery 物件，測試是否正常運作。

9. 使用遠端模型歸納主題

讓我們使用建立的遠端模型「bookshelf.llm_model」，從指定的主題清單為書籍產生整合關鍵字：

SELECT * FROM ML.GENERATE_TEXT(MODEL `bookshelf.llm_model`,
(
SELECT
     CONCAT('Read all the words in the following text separated by ";" and come up with one single (most relating) theme that is applicable : ', Themes, '. Take the title of the book from the following value', BookMeta_Title, '. Return a meaningful and concise theme with one or two words, not more than that. If a theme is not possible, return a context from its title. But DO NOT return empty or null. ') AS prompt
    FROM `gdelt-bq.internetarchivebooks.1920` LIMIT 1
));

選用步驟：LLM 生成結果欄位的結果會巢狀化。讓我們在查詢中新增一些 LLM 參數和「flatten_json_output」屬性。使用「flatten_json_output」屬性，有助於從 LLM 生成的結果欄位中移除巢狀結構。

SELECT * FROM ML.GENERATE_TEXT ( MODEL `bookshelf.llm_model`, (
 SELECT CONCAT('Read all the words in the following text separated by ";" and come up with one single (most relating) theme that is applicable : ', Themes, '. Take the title of the book from the following value', BookMeta_Title, '. Return a meaningful and concise theme with one or two words, not more than that. If a theme is not possible, return a context from its title. But DO NOT return empty or null. ') AS prompt FROM `gdelt-bq.internetarchivebooks.1920` limit 1),
    STRUCT( 0.2 AS temperature, 100 AS max_output_tokens, TRUE AS flatten_json_output));

現在，請在 BigQuery 編輯器中執行 SELECT 查詢，並驗證結果。為進行測試，我們將查詢結果限制為 1 個。結果如下所示：

10. 使用遠端函式生成書籍全文摘要

現在，我們要執行先前建立的 bookshelf.GEMINI_REMOTE_CALL Cloud Function，嘗試摘要書籍內容。

注意：如果您跳過遠端 FUNCTION 的建立作業 (本程式碼研究室的前一個主題)，請記得在 SELECT 查詢中也跳過 bookshelf.GEMINI_REMOTE_CALL 函式呼叫。

使用 SELECT 查詢呼叫我們稍早建立的遠端函式 (GEMINI_REMOTE_CALL)。對這個函式 GEMINI_REMOTE_CALL 的呼叫包含提示，要求提供書籍文字摘要：

select BookMeta_Title, `bookshelf.GEMINI_REMOTE_CALL` (
  CONCAT('This is about a book. The title of the book is this: ', BookMeta_Title,  ' . The theme of the book is as follows: ', Themes, 'The following text contains an excerpt from the book. Summarize it in less than 5 lines to create the gist. If you do not have a summary, use the title and themes to make up a short summary. DO NOT RETURN EMPTY RESPONSE. ', SUBSTR(BookMeta_FullText, 5000, 10000))) Summary 
  from `gdelt-bq.internetarchivebooks.1920` where Themes like '%EDUCATION%' limit 1;

請注意，我們是擷取書籍全文的子字串來生成摘要。

查詢結果如下：

11. 將書籍資料儲存在資料表中

我們已僅使用 SQL 查詢，從 BigQuery 測試 LLM 呼叫 (遠端模型和函式)，現在要建立 BigQuery 資料表，在與遠端模型和函式相同的資料集中，儲存含有主題洞察資料的「書架」資料。

我們可以在這個步驟中同時納入 LLM 模型呼叫和遠端函式呼叫。不過，由於我們已將 (叫用 Cloud Function 的) 遠端函式呼叫標示為選用步驟，因此只會使用遠端模型的洞察資料。

我們將使用下列查詢：

SELECT
  BookMeta_Title, Themes, ml_generate_text_llm_result Context 
   FROM
  ML.GENERATE_TEXT (
    MODEL `bookshelf.llm_model`,
    (
 SELECT
      BookMeta_Title,
      Themes,BookMeta_FullText,
      CONCAT('Read all the words in the following text separated by ";" and come up with one single (most relating) theme that is applicable : ', Themes, '. Take the title of the book from the following value', BookMeta_Title, '. Return a meaningful and concise theme with one or two words, not more than that. If a theme is not possible, return a context from its title. But DO NOT return empty or null. ') AS prompt
    FROM `gdelt-bq.internetarchivebooks.1920` limit 5),
    STRUCT(
      0.2 AS temperature,
      100 AS max_output_tokens,
      TRUE AS flatten_json_output));

在 BigQuery 編輯器中執行查詢時，結果如下：

接著要求 Gemini 從上述查詢建立名為「bookshelf.books」的資料表。前往 Google Cloud 控制台的 Gemini 對話主控台，然後輸入下列提示。

我們將使用以下提示：

Create a BigQuery table named bookshelf.books from this SELECT query: 
SELECT
  BookMeta_Title, Themes, ml_generate_text_llm_result Context 
   FROM
  ML.GENERATE_TEXT (
    MODEL `bookshelf.llm_model`,
    (
 SELECT
      BookMeta_Title,
      Themes,BookMeta_FullText,
      CONCAT('Read all the words in the following text separated by ";" and come up with one single (most relating) theme that is applicable : ', Themes, '. Take the title of the book from the following value', BookMeta_Title, '. Return a meaningful and concise theme with one or two words, not more than that. If a theme is not possible, return a context from its title. But DO NOT return empty or null. ') AS prompt
    FROM `gdelt-bq.internetarchivebooks.1920` limit 5),
    STRUCT(
      0.2 AS temperature,
      100 AS max_output_tokens,
      TRUE AS flatten_json_output));

Gemini Chat 的回覆如下：

如要直接從這裡複製查詢，請參閱以下內容：

CREATE TABLE bookshelf.books (
  BookMeta_Title STRING,
  Themes STRING,
  ml_generate_text_llm_result STRING
) AS (
  SELECT
    BookMeta_Title,
    Themes,
    ml_generate_text_llm_result Context
  FROM
    ML.GENERATE_TEXT (
      MODEL `bookshelf.llm_model`,
      (
        SELECT
          BookMeta_Title,
          Themes,
          BookMeta_FullText,
          CONCAT('Read all the words in the following text separated by ";" and come up with one single (most relating) theme that is applicable : ', Themes, '. Take the title of the book from the following value', BookMeta_Title, '. Return a meaningful and concise theme with one or two words, not more than that. If a theme is not possible, return a context from its title. But DO NOT return empty or null. ') AS prompt
        FROM `gdelt-bq.internetarchivebooks.1920`
        LIMIT 5
      ),
      STRUCT(
        0.2 AS temperature,
        100 AS max_output_tokens,
        TRUE AS flatten_json_output
      )
    )
);

在 BigQuery 編輯器中執行查詢後，結果如下：

大功告成！現在查詢資料表，並試用資料，取得更多洞察資料。

12. 恭喜

恭喜！我們已順利完成下列事項，並在部分步驟中使用了 Gemini：

在 BigQuery 中建立遠端模型，叫用 Vertex AI「text-bison-32k」端點，從資料表中以「;」分隔的關鍵字清單中，找出書籍的類型 (或主題)。
在 BigQuery 中建立遠端函式，以遠端叫用這個已部署的生成式 AI Cloud Functions。這項函式會將提示詞做為輸入內容，並輸出 5 行的字串，做為書籍摘要。
使用遠端模型和函式，透過 SQL 查詢摘要說明書籍的主題和內容，並將結果寫入書架資料集的新資料表。
請嘗試使用 Gemini 取得 SQL，刪除在 BigQuery 中建立的物件。這會涵蓋清除步驟。