在 BigQuery 程式碼研究室中使用 AI 代理程式準備資料

1. 簡介

想像一下，您不必是程式設計專家，就能更快、更有效率地準備好資料以供分析。BigQuery 資料準備功能可協助您實現這個目標。這項強大功能可簡化資料擷取、轉換和清理作業，讓貴機構的所有資料從業人員都能準備資料。

準備好發掘產品資料中隱藏的秘密了嗎？

必要條件

• 對 Google Cloud 控制台有基本瞭解
• 對 SQL 有基本瞭解

課程內容

• 瞭解如何透過 BigQuery 資料準備功能，將原始資料清理及轉換為可做為行動依據的商業智慧資訊，並以時裝和美妝產業的實際案例為例。
• 如何執行及排定清理後資料的資料準備作業

軟硬體需求

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

2. 基本設定和需求條件

自修實驗室環境設定

1. 登入 Google Cloud 控制台，然後建立新專案或重複使用現有專案。如果沒有 Gmail 或 Google Workspace 帳戶，請先建立帳戶。

• 專案名稱是這個專案參與者的顯示名稱。這是 Google API 未使用的字元字串。你隨時可以更新。
• 專案 ID 在所有 Google Cloud 專案中都是不重複的，而且設定後即無法變更。Cloud 控制台會自動產生專屬字串，通常您不需要在意該字串為何。在大多數程式碼研究室中，您需要參照專案 ID (通常標示為 PROJECT_ID)。如果您不喜歡產生的 ID，可以產生另一個隨機 ID。你也可以嘗試使用自己的名稱，看看是否可用。完成這個步驟後就無法變更，且專案期間會維持不變。
• 請注意，部分 API 會使用第三個值，也就是「專案編號」。如要進一步瞭解這三種值，請參閱說明文件。

注意：專案 ID 在全域中是專屬 ID，選定後就無法變更，且其他使用者無法使用。您是該 ID 的唯一使用者。即使專案已刪除，ID 也無法再次使用

注意：如果您使用 Gmail 帳戶，可以將預設位置設為「沒有機構」。如果你使用 Google Workspace 帳戶，請選擇適合貴機構的位置。

2. 接著，您需要在 Cloud 控制台中啟用帳單，才能使用 Cloud 資源/API。完成這個程式碼研究室的費用不高，甚至可能完全免費。如要關閉資源，避免在本教學課程結束後繼續產生費用，請刪除您建立的資源或專案。Google Cloud 新使用者可參加價值$300 美元的免費試用計畫。

3. 事前準備

啟用 API

如要使用 Gemini in BigQuery，請啟用 Gemini for Google Cloud API。通常由具備 serviceusage.services.enable IAM 權限的服務管理員或專案擁有者執行這個步驟。

1. 如要啟用 Gemini for Google Cloud API，請前往 Google Cloud Marketplace 的 Gemini for Google Cloud 頁面。前往 Gemini 版 Google Cloud
2. 在專案選取器中選取專案。
3. 按一下「啟用」。頁面會更新並顯示「已啟用」狀態。現在，只要使用者具備必要的 IAM 權限，就能在所選的 Google Cloud 雲端專案使用 Gemini in BigQuery。

設定角色和權限，以開發資料準備作業

1. 在「IAM 與管理」中選取「IAM」

2. 選取使用者，然後按一下鉛筆圖示「編輯主體」

如要使用 BigQuery 資料準備功能，您需要下列角色和權限：

• BigQuery 資料編輯者 (roles/bigquery.dataEditor)
• 服務使用情形用戶 (roles/serviceusage.serviceUsageConsumer)

4. 在 BigQuery Analytics Hub 中尋找並訂閱「bq data preparation demo」項目

在本教學課程中，我們會使用 bq data preparation demo 資料集。這是 BigQuery Analytics Hub 中連結的資料集，我們會從中讀取資料。

資料準備作業絕不會寫回來源，我們會要求您定義要寫入的目的地資料表。我們將在本練習中使用只有 1,000 列的資料表，盡量減少費用，但資料準備作業會在 BigQuery 上執行，並隨之擴展。

請按照下列步驟尋找及訂閱連結的資料集：

1. 存取 Analytics Hub：前往 Google Cloud 控制台中的 BigQuery。
2. 在 BigQuery 導覽選單中，選取「控管」下方的「Analytics Hub」。

3. 搜尋項目：在 Analytics Hub 使用者介面中，按一下「搜尋項目」。
4. 在搜尋列中輸入 bq data preparation demo，然後按 Enter 鍵。

5. 訂閱房源：從搜尋結果中選取 bq data preparation demo 房源。
6. 在房源詳細資料頁面中，按一下「訂閱」按鈕。
7. 查看所有確認對話方塊，並視需要更新專案/資料集。預設值應該是正確的。

8. 在 BigQuery 中存取資料集：訂閱成功後，資訊中的資料集就會連結至您的 BigQuery 專案。

返回 BigQuery Studio。

5. 探索資料並啟動資料準備程序

1. 找出資料集和資料表：在「Explorer」面板中選取專案，然後找出 bq data preparation demo 清單中包含的資料集。選取「stg_product」資料表。
2. 在資料準備中開啟：點選資料表名稱旁的三點圖示，然後選取 Open in Data Preparation。

如果系統提示您啟用 Enable data preparation，請點選按鈕啟用，然後按照提示操作。

系統隨即會在「資料準備」介面中開啟資料表，方便您開始轉換資料。

如下方的資料預覽畫面所示，我們將解決一些資料問題，包括：

• 價格欄同時包含金額和幣別，因此難以分析。
• 產品資料欄會混合顯示產品名稱和類別 (以直立線符號「|」分隔)。

Gemini 會立即分析資料，並建議幾種轉換方式。在本例中，我們看到多項建議。在後續步驟中，我們會套用所需的項目。

6. 處理價格欄

現在來處理「價格」欄。如先前所見，這個結構體包含幣別和金額。我們的目標是將這些資料分成兩個不同的資料欄：幣別和金額。

Gemini 發現「價格」欄有幾項建議。

注意：如果找不到內含下列操作說明的建議資訊卡，請按照下列步驟建立：

1. 選取「新增步驟」>「轉換」
2. 在 Description 欄位中輸入要搜尋的說明。

1. 找出類似下列內容的建議：

說明：「這個運算式會從指定欄位中移除開頭的『USD '』」

REGEXP_REPLACE(Price,` `r'^USD\s',` `r'')

2. 選取「預覽」

3. 選取「套用」

接著，針對「Price」資料欄，將資料類型從 STRING 轉換為 NUMERIC。

1. 找出類似下列內容的建議：

說明：「將『Price』欄從字串類型轉換為 float64」

SAFE_CAST(Price AS float64)

2. 選取「套用」。

現在步驟清單中應該會顯示三個已套用的步驟。

注意：如果找不到上述建議，請使用上方提示中記錄的 Add Step 工作流程新增及套用建議。

7. 處理產品資料欄

產品資料欄包含產品名稱和類別，並以直立線 (|) 分隔。

雖然我們還是可以使用自然語言，但現在來探索 Gemini 的另一項強大功能。

清理產品名稱

1. 選取產品項目的類別部分 (包括 | 字元)，然後刪除。

Gemini 會智慧辨識這個模式，並建議套用至整欄的轉換。

2. 選取「編輯」。

Gemini 的建議非常準確：它會移除「|」字元後的所有內容，有效分離產品名稱。

但這次我們不想覆寫原始資料。

3. 在目標欄下拉式選單中，選取「建立新欄」。
4. 將名稱設為 ProductName。

4. 預覽變更，確認一切正常。
5. 套用轉換。

擷取產品類別

我們會以自然語言指示 Gemini 從「產品」欄中擷取管道符號 (|) 後方的字詞。系統會將擷取的值覆寫到現有的「產品」欄。

1. 按一下 Add Step 新增轉換步驟。

2. 從下拉式選單中選取 Transformation
3. 在自然語言提示欄位中，輸入「extract the word after the pipe (|) in the Product column.」(擷取「產品」欄位中管道符號 (|) 後方的字詞)，然後按下 Return 鍵生成 SQL。

4. 將目標資料欄保留為「產品」。
5. 按一下 [套用]。

轉換後應會得到下列結果。

8. 彙整資料以豐富內容

您通常會想從其他來源取得資訊，以擴充資料。在我們的範例中，我們會將產品資料與第三方表格中的擴充產品屬性 stg_extended_product 聯結。這個表格包含品牌和推出日期等詳細資料。

1. 按一下 Add Step
2. 選取「Join」
3. 瀏覽至 stg_extended_product 資料表。

Gemini in BigQuery 會自動為我們選取 productid 聯結鍵，並由於鍵名相同，因此會將左右兩側都設為合格。

注意：請確認說明欄位顯示「Join by productid」(依產品 ID 加入)。如果包含其他彙整鍵，請將說明欄位覆寫為「按 productid 彙整」，然後選取說明欄位中的生成按鈕，使用以下條件重新生成彙整運算式 L.。

productid

= R.

productid。

4. 視需要選取「預覽」，預覽結果。
5. 按一下 [Apply]。

清除擴充屬性

雖然聯結成功，但擴充屬性資料需要清理。「LaunchDate」欄的日期格式不一致，而「Brand」欄則含有部分遺漏值。

首先，我們將處理 LaunchDate 欄。

在建立任何轉換之前，請先查看 Gemini 的建議。

1. 按一下「LaunchDate」欄名稱。您應該會看到類似下圖的建議。

2. 如果看到包含下列 SQL 的最佳化建議，請套用該建議並略過後續步驟。

COALESCE(SAFE.PARSE_DATE('%Y-%m-%d',
LaunchDate),SAFE.PARSE_DATE('%Y/%m/%d', LaunchDate))

3. 如果沒有看到與上述 SQL 相符的建議，請按一下 Add Step。
4. 選取 Transformation。
5. 在 SQL 欄位中輸入下列內容：

COALESCE(SAFE.PARSE_DATE('%Y-%m-%d',
LaunchDate),SAFE.PARSE_DATE('%Y/%m/%d', LaunchDate))

6. 將 Target Columns 設為 LaunchDate。
7. 按一下 [Apply]。

LaunchDate 資料欄現在的日期格式一致。

9. 新增目的地資料表

資料集現在已清理完畢，可載入資料倉儲的維度資料表。

1. 按一下 [ADD STEP]。
2. 選取 Destination。
3. 填入必要參數：資料集：bq_data_preparation_demo 資料表：DimProduct
4. 按一下 [Save]。

我們現在已使用「資料」和「結構定義」分頁。此外，BigQuery 資料準備還提供「圖表」檢視畫面，以視覺化方式顯示管道中的轉換步驟順序。

注意：按一下標題中的資料準備名稱，即可查看圖表。

10. 獎勵 A：處理「製造商」欄並建立錯誤資料表

我們也發現「Manufacturer」欄中的值為空值。針對這些記錄，我們想實作資料品質檢查，並將記錄移至錯誤資料表以供進一步審查。

建立錯誤表格

1. 按一下「stg_product data preparation」標題旁的 More 按鈕。
2. 在「Setting」部分下方，選取「Error Table」。
3. 勾選「Enable error table」方塊，並按照下列方式設定：

• 資料集：選取 bq_data_preparation_demo
• 資料表：輸入 err_dataprep
• 在「Define duration for keeping errors」下方，選取「30 days (default)」

4. 按一下 [Save]。

在「製造商」欄中設定驗證

1. 選取「製造商」欄。
2. Gemini 可能已找出相關轉換。找出只保留「製造商」欄位不為空白的資料列的建議。SQL 類似於：

Manufacturer IS NOT NULL

2. 點選這項建議的「編輯」按鈕，即可查看建議。

3. 如果未勾選「驗證失敗的資料列移至錯誤表格」選項，請勾選該選項
4. 按一下 [Apply]。

您隨時可以點選「已套用的步驟」按鈕，查看、修改或刪除已套用的轉換。

清理多餘的 ProductID_1 欄

ProductID_1 資料欄與已聯結資料表中的 ProductID 重複，因此現在可以刪除。

1. 前往「Schema」分頁
2. 按一下「ProductID_1」欄旁邊的 3 點圖示。
3. 按一下 Drop。

現在我們準備執行資料準備工作，並驗證整個管道。對結果感到滿意後，我們就能安排工作自動執行。

4. 離開資料準備檢視畫面之前，請先儲存準備作業。「stg_product data preparation」標題旁應該會顯示 Save 按鈕。按一下按鈕即可儲存。

11. 清理環境

• 刪除 stg_product data preparation
• 刪除 bq data preparation demo 資料集

12. 恭喜

恭喜您完成本程式碼研究室。

涵蓋內容

• 設定資料準備作業
• 開啟表格及瀏覽資料準備作業
• 分割含有數值和單位描述元資料的欄
• 標準化日期格式
• 執行資料準備作業