本頁面由 Cloud Translation API 翻譯而成。

在 BigQuery 中輕鬆準備資料：低程式碼開發人員指南

1. 簡介

資料分析師經常會遇到以半結構化格式 (例如 JSON 酬載) 鎖定的寶貴資料。以往要擷取及準備這類資料以供分析和機器學習，是相當大的技術障礙，通常需要複雜的 ETL 指令碼，並由資料工程團隊介入處理。

本程式碼研究室提供技術藍圖，協助資料分析師獨立克服這項挑戰。這項範例會以「低程式碼」方式，建構端對端 AI 管道。您將瞭解如何使用 BigQuery Studio 提供的工具，從 Google Cloud Storage 中的原始 CSV 檔案，到支援 AI 驅動的建議功能。

主要目標是展示強大、快速且適合分析師的工作流程，擺脫複雜且需要大量程式碼的程序，從資料中產生實際的業務價值。

必要條件

Google Cloud 控制台的基本知識
指令列介面和 Google Cloud Shell 的基本技能

課程內容

瞭解如何使用 BigQuery 資料準備功能，直接從 Google Cloud Storage 擷取及轉換 CSV 檔案。
瞭解如何使用免程式碼轉換功能，剖析及扁平化資料中的巢狀 JSON 字串。
如何建立 BigQuery ML 遠端模型，連線至 Vertex AI 基礎模型以進行文字嵌入。
如何使用 ML.GENERATE_TEXT_EMBEDDING 函式將文字資料轉換為數值向量。
如何使用 ML.DISTANCE 函式計算餘弦相似度，並在資料集中找出最相似的項目。

軟硬體需求

Google Cloud 帳戶和 Google Cloud 專案
網路瀏覽器，例如 Chrome

重要概念

BigQuery 資料準備：BigQuery Studio 內建的工具，提供互動式視覺化介面，方便您清理及準備資料。並建議轉換方式，讓使用者以最少的程式碼建構資料管道。
BQML 遠端模型：BigQuery ML 物件，可做為 Vertex AI (例如 Gemini) 託管模型的 Proxy。您可以使用熟悉的 SQL 語法，叫用強大的預先訓練 AI 模型。
向量嵌入：以數值表示資料，例如文字或圖片。在本程式碼研究室中，我們會將藝術作品的文字說明轉換為向量，類似的說明會產生在多維空間中「更接近」的向量。
餘弦相似度：用來判斷兩個向量相似程度的數學測量值。這是建議引擎邏輯的核心，ML.DISTANCE 函式會使用這項功能找出「最接近」(最相似) 的作品。

2. 設定和需求條件

啟動 Cloud Shell

雖然您可以透過筆電遠端操作 Google Cloud，但在本程式碼研究室中，您將使用 Google Cloud Shell，這是可在雲端執行的指令列環境。

在 Google Cloud 控制台中，點選右上工具列的 Cloud Shell 圖示：

佈建並連線至環境的作業很快就能完成。完成後，您應該會看到如下的內容：

這部虛擬機器搭載各種您需要的開發工具，提供永久的 5 GB 主目錄，而且在 Google Cloud 中運作，可大幅提升網路效能和驗證。您可以在瀏覽器中完成本程式碼研究室的所有作業。您不需要安裝任何軟體。

啟用必要的 API 並設定環境

在 Cloud Shell 中執行下列指令，設定專案 ID、定義環境變數，並啟用本程式碼研究室的所有必要 API。

export PROJECT_ID=$(gcloud config get-value project)
gcloud config set project $PROJECT_ID
export LOCATION="US"
export GCS_BUCKET_NAME="met-artworks-source-${PROJECT_ID}" # Must be a globally unique name

gcloud services enable bigquery.googleapis.com \
                       storage.googleapis.com \
                       aiplatform.googleapis.com \
                       bigqueryconnection.googleapis.com

建立 BigQuery 資料集和 GCS Bucket

建立新的 BigQuery 資料集來存放資料表，以及 Google Cloud Storage bucket 來儲存來源 CSV 檔案。

# Create the BigQuery Dataset in the US multi-region
bq --location=$LOCATION mk --dataset $PROJECT_ID:met_art_dataset

# Create the GCS Bucket
gcloud storage buckets create gs://$GCS_BUCKET_NAME --project=$PROJECT_ID --location=$LOCATION

準備並上傳範例資料

複製包含範例 CSV 檔案的 GitHub 存放區，然後上傳至您剛建立的 GCS bucket。

# Clone the repository
git clone https://github.com/GoogleCloudPlatform/devrel-demos.git

# Navigate to the correct directory
cd devrel-demos/data-analytics/dataprep

# Upload the CSV file to your GCS bucket
gsutil cp dataprep-met-bqml.csv gs://$GCS_BUCKET_NAME/

3. 使用資料準備功能將資料從 GCS 移至 BigQuery

在本節中，我們將使用無程式碼的視覺化介面，從 GCS 擷取 CSV 檔案、清除檔案，然後載入至新的 BigQuery 資料表。

啟動資料準備作業並連線至來源

在 Google Cloud 控制台中，前往 BigQuery Studio。

在歡迎頁面中，按一下「資料準備」資訊卡即可開始。

如果是第一次使用，可能需要啟用必要的 API。點選「Gemini for Google Cloud API」和「BigQuery Unified API」的「啟用」。啟用後，即可關閉這個面板。

在主要的「資料準備」視窗中，按一下「選擇其他資料來源」下方的「Google Cloud Storage」。右側會開啟「準備資料」面板。

按一下「瀏覽」按鈕，選取來源檔案。

前往先前建立的 GCS bucket (met-artworks-source-...)，然後選取 dataprep-met-bqml.csv 檔案。按一下「選取」。

接著，您需要設定暫存表。
在「資料集」部分，選取您建立的 met_art_dataset。
在「Table name」(資料表名稱) 部分輸入名稱，例如 temp。
按一下「建立」。

轉換及清除資料

BigQuery 的資料準備功能現在會載入 CSV 檔案的預覽畫面。找出 label_details_json 欄，其中包含長 JSON 字串。按一下欄標題即可選取。

在右側的「建議」面板中，Gemini in BigQuery 會自動建議相關轉換。按一下「扁平化資料欄 label_details_json」資訊卡上的「套用」按鈕。這會將巢狀欄位 (description、score 等) 擷取至各自的頂層資料欄。

按一下 object_id 欄，然後按一下「將欄 object_id 從類型 string 轉換為 int64」的套用按鈕。

定義目的地並執行工作

在右側面板中，按一下「目的地」按鈕，設定轉換的輸出內容。

設定目的地詳細資料：
資料集應預先填入 met_art_dataset。
輸入輸出內容的新資料表名稱：met_art_flatten_table。
按一下「儲存」。

按一下「執行」按鈕，然後等待資料準備工作完成。

您可以在頁面底部的「執行」分頁中監控工作進度。經過一段時間後，這項工作就會完成。

4. 使用 BQML 生成向量嵌入

現在資料已清理完畢並完成結構化，我們將使用 BigQuery ML 執行核心 AI 工作：將藝術品的文字說明轉換為數值向量嵌入。

建立 BigQuery 連線

如要允許 BigQuery 與 Vertex AI 服務通訊，請先建立 BigQuery 連線。

在 BigQuery Studio 的「Explorer」面板中，按一下「+ 新增資料」按鈕。

在右側面板中，使用搜尋列輸入 Vertex AI。選取該項目，然後從篩選後的清單中選取 BigQuery 聯盟。

系統隨即會開啟「外部資料來源」表單。填寫下列詳細資料：

連線 ID：輸入連線 ID (例如 bqml-vertex-connection)
位置類型：確認已選取「多區域」。
位置：選取位置 (例如 US)。

連線建立完成後，系統會顯示確認對話方塊。在「Explorer」分頁中，按一下「前往連線」或「外部連線」。在連線詳細資料頁面，將完整 ID 複製到剪貼簿。這是 BigQuery 用來呼叫 Vertex AI 的服務帳戶身分。

在 Google Cloud 控制台導覽選單中，依序前往「IAM 與管理」>「IAM」。

按一下「授予存取權」按鈕
將上一個步驟複製的服務帳戶貼到「新主體」欄位。
在「角色」下拉式選單中指派「Vertex AI 使用者」，然後按一下「儲存」。

這個重要步驟可確保 BigQuery 獲得適當授權，代表您使用 Vertex AI 模型。

建立遠端模型

在 BigQuery Studio 中開啟新的 SQL 編輯器分頁。您將在此定義連結至 Gemini 的 BQML 模型。

這個陳述式不會訓練新模型，這項作業只會在 BigQuery 中建立參照，並使用您剛授權的連線，指向功能強大的預先訓練 gemini-embedding-001 模型。

複製下列整個 SQL 指令碼，並貼到 BigQuery 編輯器。

CREATE OR REPLACE MODEL `met_art_dataset.embedding_model`
REMOTE WITH CONNECTION `US.bqml-vertex-connection`
OPTIONS (endpoint = 'gemini-embedding-001');

生成嵌入

現在，我們將使用 BQML 模型生成向量嵌入。我們不會只是轉換每列的單一文字標籤，而是會採用更精細的方法，為每件作品建立更豐富、更有意義的「語意摘要」。這有助於產生更高品質的嵌入內容，並提供更準確的建議。

這項查詢會執行重要的預先處理步驟：

這項查詢會先使用 WITH 子句建立暫存資料表。
在其中，我們會使用 GROUP BY object_id，將單一藝術作品的所有資訊合併為一列。
我們使用 STRING_AGG 函式，將所有個別文字說明 (例如「肖像」、「女性」、「布面油畫」) 合併為單一綜合文字字串，並依據相關分數排序。

這段合併文字可為 AI 提供更豐富的脈絡資訊，進而產生更細緻且強大的向量嵌入。

在新 SQL 編輯器分頁中，貼上並執行下列查詢：

CREATE OR REPLACE TABLE `met_art_dataset.artwork_embeddings` AS
WITH artwork_semantic_text AS (
  -- First, we group all text labels for each artwork into a single row.
  SELECT
    object_id,
    ANY_VALUE(title) AS title,
    ANY_VALUE(artist_display_name) AS artist_display_name,
    -- STRING_AGG combines all descriptions into one comma-separated string,
    -- ordering them by score to put the most relevant labels first.
    STRING_AGG(description, ', ' ORDER BY score DESC) AS aggregated_labels
  FROM
    `met_art_dataset.met_art_flatten_table`
  GROUP BY
    object_id
)
SELECT
  *
FROM ML.GENERATE_TEXT_EMBEDDING(
  MODEL `met_art_dataset.embedding_model`,
  (
    -- We pass the new, combined string as the content to be embedded.
    SELECT
      object_id,
      title,
      artist_display_name,
      aggregated_labels AS content
    FROM
      artwork_semantic_text
  )
);

這項查詢大約需要 10 分鐘。查詢完成後，請確認結果。在「Explorer」面板中找到新的 artwork_embeddings 資料表，然後點選該資料表。在資料表結構定義檢視器中，您會看到 object_id、包含向量的新 ml_generate_text_embedding_result 資料欄，以及做為來源文字的 aggregated_labels 資料欄。

5. 使用 SQL 尋找類似的藝術品

建立高品質且內容豐富的向量嵌入後，只要執行 SQL 查詢，就能輕鬆找出主題相似的藝術作品。我們使用 ML.DISTANCE 函式計算向量之間的餘弦相似度。由於我們的嵌入內容是從匯總文字產生，相似度結果會更準確且相關。

在新 SQL 編輯器分頁中，貼上下列查詢。這項查詢會模擬推薦應用程式的核心邏輯：

首先，系統會選取單一特定藝術作品的向量 (在本例中為梵谷的「絲柏樹」，其 object_id 為 436535)。
然後計算該單一向量與表格中所有其他向量之間的距離。
最後，系統會依距離排序結果 (距離越小表示越相似)，找出最接近的前 10 個相符項目。

WITH selected_artwork AS (
  SELECT text_embedding
  FROM `met_art_dataset.artwork_embeddings`
  WHERE object_id = 436535
)
SELECT
  base.object_id,
  base.title,
  base.artist_display_name,
  -- ML.DISTANCE calculates the cosine distance between the two vectors.
  -- A smaller distance means the items are more similar.
  ML.DISTANCE(base.text_embedding, (SELECT text_embedding FROM selected_artwork), 'COSINE') AS similarity_distance
FROM
  `met_art_dataset.artwork_embeddings` AS base, selected_artwork
ORDER BY
  similarity_distance
LIMIT 10;

執行查詢。結果會列出 object_id，最相符的結果會顯示在頂端。來源圖片會先顯示，距離為 0。這是 AI 推薦引擎的核心邏輯，您完全是在 BigQuery 中使用 SQL 建立這項邏輯。

6. (選用) 在 Cloud Shell 中執行範例

為了讓您實際運用本程式碼研究室的概念，您複製的存放區包含簡單的網頁應用程式。這項選用示範會使用您建立的 artwork_embeddings 表格，為圖像搜尋引擎提供資料，讓您瞭解 AI 輔助建議的實際運作方式。

如要在 Cloud Shell 中執行試用版，請按照下列步驟操作：

設定環境變數：執行應用程式前，請先設定 PROJECT_ID 和 BIGQUERY_DATASET 環境變數。

export PROJECT_ID=$(gcloud config get-value project)
export BIGQUERY_DATASET=met_art_dataset
export REGION='us-central1'
bq cp bigquery-public-data:the_met.images $PROJECT_ID:met_art_dataset.images

安裝依附元件並啟動後端伺服器。

cd ~/devrel-demos/data-analytics/dataprep/backend/ && npm install
node server.js

您需要第二個終端機分頁，才能執行前端應用程式。按一下「+」圖示，開啟新的 Cloud Shell 分頁。

現在，請在新分頁中執行下列指令，安裝依附元件並執行前端伺服器

cd ~/devrel-demos/data-analytics/dataprep/frontend/ && npm install
npm run dev

預覽應用程式：在 Cloud Shell 工具列中，按一下「網頁預覽」圖示，然後選取「透過以下通訊埠預覽：5173」。瀏覽器會開啟新分頁並執行應用程式。您現在可以使用應用程式搜尋藝術作品，並查看相似度搜尋的實際運作情形。

如要將這個視覺化示範連結回您在 BigQuery SQL 編輯器中執行的工作，請在搜尋列中輸入「Cypresses」。這與您在 ML.DISTANCE 查詢中使用的藝術品(object_id=436535) 相同。接著，在左側面板中點選「Cypresses」圖片，右側就會顯示結果。應用程式會顯示最相似的藝術作品，以視覺化方式呈現您建構的向量相似度搜尋功能。

7. 清除環境

如要避免系統日後向您的 Google Cloud 帳戶收取本程式碼研究室所用資源的費用，請刪除您建立的資源。

在 Cloud Shell 終端機中執行下列指令，移除服務帳戶、BigQuery 連線、GCS Bucket 和 BigQuery 資料集。

# Re-run these exports if your Cloud Shell session timed out
export PROJECT_ID=$(gcloud config get-value project)
export LOCATION="US"
export GCS_BUCKET_NAME="met-artworks-source-${PROJECT_ID}"
export BQ_CONNECTION_ID="bqml-vertex-connection"

移除 BigQuery 連線和 GCS Bucket

# Delete the BigQuery connection
bq rm --connection $LOCATION.$BQ_CONNECTION_ID

# Delete the GCS bucket and its contents
gcloud storage rm --recursive gs://$GCS_BUCKET_NAME

刪除 BigQuery 資料集

最後，刪除 BigQuery 資料集。這項指令無法復原。-f (強制) 旗標會移除資料集和所有資料表，且不會提示確認。

# Manually type this command to confirm you are deleting the correct dataset
bq rm -r -f --dataset $PROJECT_ID:met_art_dataset

8. 恭喜！

您已成功建構端對端 AI 輔助資料管道。

您從 GCS 儲存空間中的原始 CSV 檔案開始，使用 BigQuery Data Prep 的低程式碼介面擷取並扁平化複雜的 JSON 資料，建立強大的 BQML 遠端模型，透過 Gemini 模型生成高品質的向量嵌入，並執行相似度搜尋查詢來尋找相關項目。

您現在已掌握在 Google Cloud 上建構 AI 輔助工作流程的基本模式，可快速輕鬆地將原始資料轉換為智慧型應用程式。

後續步驟

在 Looker Studio 中將結果視覺化：直接將 artwork_embeddings BigQuery 資料表連結至 Looker Studio (免費！)。您可以建構互動式資訊主頁，讓使用者選取藝術品，並查看最相似作品的視覺藝廊，完全不必編寫任何前端程式碼。
使用排定時間的查詢自動執行作業：您不需要複雜的自動化調度管理工具，就能讓嵌入內容保持在最新狀態。使用 BigQuery 內建的「已排定的查詢」功能，每天或每週自動重新執行 ML.GENERATE_TEXT_EMBEDDING 查詢。
使用 Gemini CLI 生成應用程式：只要用純文字描述需求，Gemini CLI 就會生成完整的應用程式。這樣一來，您就能快速建構相似性搜尋的工作原型，不必手動撰寫 Python 程式碼。
閱讀說明文件：
- BigQuery 資料準備功能簡介
- GitHub 上的程式碼範例