重要提醒：

請務必使用桌機/筆電完成這個實作實驗室。

每個實驗室都只有 5 次嘗試機會。

提醒：第一次嘗試時，不一定能全部答對，甚至可能需要重做，這是正常的過程。

實驗室活動開始後，計時器無法暫停。實驗室會在 1 小時 30 分鐘後結束，如果您沒做完，就必須重新開始。

您可查看實驗室技術提示瞭解詳情。

活動總覽

資料管道是指將不同來源的資料傳輸至目的地，以利儲存和分析的一系列程序。

資料管道可將資料流程自動化並簡化處理方式，進而提升資料轉換的效能和效率。

使用 SQL 管道可去除不必要的步驟，進而提升成效，因為這種管道可以直接讀取及寫入目的地資料，不用建立中繼資料集，可以節省時間和資源，並提高結果的準確度。

此外，SQL 管道可協助您最佳化資料儲存空間容量，因為您可以透過管道刪除或封存不再需要的資料，有助於釋出儲存系統空間並提升效能。

建構有效率且彈性的 SQL 管道會是一大挑戰，尤其組織需求快速變化時更是如此。不過，只要仔細規劃及設計，就能建立可因應極複雜資料需求的管道。

在本實驗室中，您將管理資料並使用 SQL 建構有效率且彈性的管道，滿足業務需求。

情境

近年來 TheLook eCommerce 因線上購物，利潤大幅上揚，但配送時間卻跟不上銷售速度，顧客滿意度因而下降。

您是 TheLook eCommerce 的雲端資料分析師，要與物流團隊主管阿凱合作開發資料管道，用來收集、清理、轉換及載入顧客商品配送資料，包括商品從配送中心送達每位顧客手上的移動距離。

這項資訊可幫忙物流團隊判斷如何縮短運送時間及提高顧客滿意度，例如是否要設置新的配送中心、搬遷現有中心，或是投資新運輸方式。

您將運用 BigQuery 和 SQL 技能設計彈性管道，為物流團隊提供可靠資料，以更有效監控運送成效，並在物流團隊資料需變動時輕鬆更新。

第一，您要建立資料集，並為要擷取的資料建立資料表結構定義。第二，您要執行並探索一系列轉換作業。第三，您要轉換資料，然後載入到新定義的資料表。最後，您要組織這些查詢，編成一套預存程序。

設定

點選「Start Lab」之前

請詳閱下列操作說明。實驗室活動會計時，中途無法暫停。點選「Start Lab」後就會開始計時，顯示可使用 Google Cloud 資源的時間。

您會在實際雲端環境完成實驗室活動，而非模擬或示範環境。因此，我們會提供新的臨時憑證，讓您在實驗室活動期間登入及存取 Google Cloud。

如要順利完成這個實驗室活動，請先確認：

• 可以使用標準的網路瀏覽器 (Chrome 瀏覽器為佳)。

注意：請使用無痕模式或私密瀏覽視窗執行此實驗室，防止個人帳戶和學生帳戶之間的衝突，避免個人帳戶產生額外費用。

• 該來完成實驗室活動了！別忘了，活動開始後就無法暫停。

注意：如有個人 Google Cloud 帳戶或專案，請勿用於本實驗室，以免產生額外費用。

如何啟動實驗室環境及登入 Google Cloud 控制台

1. 點選「Start Lab」按鈕。左側的「Lab Details」面板會顯示下列項目：

   • 剩餘時間
   • 「Open Google Cloud console」按鈕
   • 這個實驗室中應使用的暫時憑證
   • 完成這個實驗室所需的其他資訊 (如有)
   注意：如果實驗室會產生費用，畫面中會出現選擇付款方式的彈出式視窗。

2. 點選「Open Google Cloud console」；如果使用 Chrome 瀏覽器，也可以按一下滑鼠右鍵，選取「在無痕視窗中開啟連結」。「登入」頁面會在新的瀏覽器分頁開啟。

   提示：為方便切換，可以將分頁安排在不同的視窗並排顯示。

   注意：如果顯示「選擇帳戶」對話方塊，請點選「使用其他帳戶」。

3. 如有需要，請複製下方的 Google Cloud 使用者名稱，然後貼到「登入」對話方塊。點選「下一步」。

{{{user_0.username | "Google Cloud username"}}}

您也可以在「Lab Details」面板找到 Google Cloud 使用者名稱。

4. 複製下方的 Google Cloud 密碼，並貼到「歡迎使用」對話方塊。點選「下一步」。

{{{user_0.password | "Google Cloud password"}}}

您也可以在「Lab Details」面板找到 Google Cloud 密碼。

重要事項：請務必使用實驗室提供的憑證，而非自己的 Google Cloud 帳戶憑證。 注意：在這個實驗室中使用自己的 Google Cloud 帳戶，可能會產生額外費用。

5. 繼續點選後續頁面：
   • 接受條款及細則。
   • 由於這是臨時帳戶，請勿新增救援選項或雙重驗證機制。
   • 請勿申請免費試用。

Cloud 控制台稍後會在這個分頁中開啟。

注意事項：點選畫面左上方的導覽選單，即可查看 Google Cloud 產品與服務清單。

工作 1：建立資料集

在這項工作，您將在現有專案中建立資料集，並建立結構定義和資料類型。

1. 前往 Cloud 控制台，然後依序選取「導覽選單」() 和「BigQuery」。

注意：系統可能會顯示「歡迎使用 Cloud 控制台中的 BigQuery」訊息方塊，並附上快速入門導覽課程和使用者介面更新版本資訊的連結。點按「完成」即可繼續操作。

系統會顯示「導覽選單」、「Explorer」窗格和「查詢編輯器」。

2. 依序點按專案 ID 旁邊的「查看動作」() 和「建立資料集」。

注意：您可能需要先選取專案，方法是在 Google Cloud 控制台標題列點按「選取專案」，然後在「選取專案」對話方塊中選取專案連結。

3. 在「建立資料集」窗格中，將「資料集 ID」設為 thelook_ecommerce。請確認位置已設為「美國 (多區域)」，其他欄位則保留預設值。

4. 點按「建立資料集」。

專案名稱下方現在會顯示 thelook_ecommerce 資料集。

5. 在「查詢編輯器」中，點按「SQL 查詢」圖示 (+)，開啟新的「未命名的查詢」分頁，即可執行查詢。

注意：每次在「查詢編輯器」中執行新查詢時，您可以複製新查詢並貼到同一個「未命名的查詢」分頁來直接取代舊查詢，或是點按「SQL 查詢」(+)圖示，開啟新的「未命名的查詢」分頁來執行查詢。

6. 複製下方查詢並貼到「未命名的查詢」分頁，即可建立 product_order_fulfillment 資料表：

--Create empty product_orders_fulfillment table CREATE OR REPLACE TABLE `thelook_ecommerce.product_orders_fulfillment` ( order_id INT64, user_id INT64, status STRING, product_id INT64, created_at TIMESTAMP, returned_at TIMESTAMP, shipped_at TIMESTAMP, delivered_at TIMESTAMP, cost NUMERIC, sale_price NUMERIC, retail_price NUMERIC, category STRING, name STRING, brand STRING, department STRING, sku STRING, distribution_center_id INT64);

7. 點按「執行」。

注意：這項查詢會新建名為「product_orders_fulfillment」的資料表 (如果沒有該資料表的話)，或是將現有資料表替換成新的資料表定義。

8. 點按「前往資料表」，即可探索已建立的資料表結構定義和資料類型。

這個資料集目前是空的，資料載入後就會填到這裡。

點按「Check my progress」，確認工作已正確完成。

建立資料集

工作 2：從查詢結果建立資料表

您的分析範圍需要擴大至分析配送中心與下單顧客間的距離，所以您要根據地理位置計算距離的值。

在這項工作中，您將根據 original users 和 distribution_centers 資料表提供的經緯度值，建立幾何點。

1. 將下列查詢複製到「查詢編輯器」：

--Create empty customers table CREATE OR REPLACE TABLE `thelook_ecommerce.customers` ( id INT64, first_name STRING, last_name STRING, email STRING, age INT64, gender STRING, state STRING, street_address STRING, postal_code STRING, city STRING, country STRING, traffic_source STRING, created_at TIMESTAMP, latitude FLOAT64, longitude FLOAT64, point_location GEOGRAPHY); --Create empty centers table CREATE OR REPLACE TABLE `thelook_ecommerce.centers` ( id INT64, name STRING, latitude FLOAT64, longitude FLOAT64, point_location GEOGRAPHY);

這項查詢會為 customers 和 centers 資料表建立資料表定義，其中包含使用者和 distribution_centers 資料表的原始資料欄。這項查詢也會在兩個資料表中新增 point_location 資料欄；這些資料欄的類型會設為「Geography」。

2. 點按「執行」。

點按「Check my progress」，確認工作已正確完成。

建立新的資料表來儲存相關資料

工作 3：轉換 BigQuery 資料

在這項工作中，您將使用 ST_GEOGPOINT 地理位置函式呼叫 ST_GEOGPOINT(lon, lat)，以建立點幾何圖形。

首先，請執行下列 SQL 查詢，建立並填入 centers 和 customers 資料表。這些查詢會從 SELECT 陳述式載入資料，因為資料的原始位置 (longitude, latitude) 位於 BigQuery thelook_ecommerce 公開資料集中。

Google 公開資料集是儲存在 BigQuery 中，透過 Google Cloud 公開資料集計畫提供給一般大眾使用的任何資料集。您可以用公開資料集輕鬆在 BigQuery 使用現成的公開資料，不須載入及維護。

1. 將下列查詢複製到「查詢編輯器」：

--load the centers table from public dataset and include geography transformation CREATE OR REPLACE TABLE `thelook_ecommerce.centers` AS SELECT id, name, latitude, longitude, ST_GEOGPOINT(dcenters.longitude, dcenters.latitude) AS point_location FROM `bigquery-public-data.thelook_ecommerce.distribution_centers` AS dcenters;

這項查詢會載入 centers 資料表，包括地理位置轉換。

2. 點按「執行」。

附註：您可以點按「前往資料表」，查看已建立的資料表結構定義和資料類型。

3. 現在，將下列查詢複製到「查詢編輯器」：

--load the customers table from public dataset and include geography transformation CREATE OR REPLACE TABLE `thelook_ecommerce.customers` AS SELECT id, first_name, last_name, email, age, gender, state, street_address, postal_code, city, country, traffic_source, created_at, latitude, longitude, ST_GEOGPOINT(users.longitude, users.latitude) AS point_location FROM `bigquery-public-data.thelook_ecommerce.users` AS users;

這項查詢會載入 customers 資料表，包括地理位置轉換。

4. 點按「執行」。

接著，您需要計算資料值以進行分析。

由於顧客與配送中心之間的關係非常重要，因此您會使用這些地理位置標示點，計算出離每個顧客最近的配送中心及距離。

您需要使用地理位置函式 ST_DISTANCE，計算顧客位置與中心之間的最小距離。

5. 將下列純量子查詢複製到「查詢編輯器」：

SELECT customers.id as customer_id, ( SELECT MIN(ST_DISTANCE(centers.point_location, customers.point_location))/1000, FROM `thelook_ecommerce.centers` AS centers) AS distance_to_closest_center FROM `thelook_ecommerce.customers` AS customers ;

這項查詢會以公里為單位計算距離。

6. 點按「執行」。

如螢幕截圖所示，查詢會傳回每位顧客與最近配送中心之間的距離。請花點時間查看 SELECT 陳述式，瞭解系統如何計算距離。

7. 展開「儲存」下拉式選單，然後選取「儲存查詢」。

系統會開啟「儲存查詢」對話方塊。

8. 在「名稱」欄位中，輸入「Calculate Customer Distance to Closest Center」。
9. 「區域」部分選取「」。
10. 點按「儲存」。

查詢會儲存到「查詢」清單。

點按「Check my progress」，確認工作已正確完成。

轉換 BQ 資料

工作 4：建立預存程序，確保更新作業更輕鬆

預存程序是一組儲存在資料庫的 SQL 陳述式，可做為一個單元執行。

在這項工作中，您將建立預存程序，納入您在工作 1 至 3 中手動依序產生的所有資料表定義、轉換和擷取陳述式。

請在指令碼開頭加入 CREATE OR REPLACE PROCEDURE 陳述式，然後依序加上預存程序名稱和 BEGIN 陳述式，以建立預存程序 (本例中為「sp_create_load_tables」)。加入程序所有元件後，請新增 END 陳述式。

您可以加入資料表定義和資料擷取部分，並新增至程序中。資料表定義可以全部放在最前面，也可以依資料表分組，視個人偏好而定。不過在本實驗室中，每個資料表定義後都是資料擷取部分，以及先前步驟完成的所有轉換。

您也可以在顧客資料表中建立資料欄，儲存計算出的 distance_to_closest_center 和 closest_centervalues 值。這個程序的優點在於，假如結構定義有異動，或是您想加入其他資料欄或產生計算結果欄，可以輕鬆新增這些項目或更新預存程序。更新後，即可設定預存程序，使其定期執行。

現在，請建立預存程序，以一個單元的形式執行您先前在工作 1 至 3 操作的所有步驟。執行下列程式碼後，系統會更新資料表並填入適當的資料。

1. 將以下查詢複製到新的「未命名」查詢分頁：

CREATE OR REPLACE PROCEDURE `thelook_ecommerce.sp_create_load_tables`() BEGIN --Create empty product_orders_fulfillment table CREATE OR REPLACE TABLE `thelook_ecommerce.product_orders_fulfillment` ( order_id INT64, user_id INT64, status STRING, product_id INT64, created_at TIMESTAMP, returned_at TIMESTAMP, shipped_at TIMESTAMP, delivered_at TIMESTAMP, cost NUMERIC, sale_price NUMERIC, retail_price NUMERIC, category STRING, name STRING, brand STRING, department STRING, sku STRING, distribution_center_id INT64) ; --load the product_order_fulfillment table from public dataset CREATE OR REPLACE TABLE thelook_ecommerce.product_orders_fulfillment AS SELECT items.*, products.id AS product_id_products, products.name AS product_name, products.category AS product_category, FROM bigquery-public-data.thelook_ecommerce.order_items AS items JOIN bigquery-public-data.thelook_ecommerce.products AS products ON (items.product_id = products.id); --Create empty centers table CREATE OR REPLACE TABLE `thelook_ecommerce.centers` ( id INT64, name STRING, latitude FLOAT64, longitude FLOAT64, point_location GEOGRAPHY); --load the centers table from public dataset and include geography transformation CREATE OR REPLACE TABLE `thelook_ecommerce.centers` AS SELECT id, name, latitude, longitude, ST_GEOGPOINT(dcenters.longitude, dcenters.latitude) AS point_location FROM `bigquery-public-data.thelook_ecommerce.distribution_centers` AS dcenters ; --Create empty customers table CREATE OR REPLACE TABLE `thelook_ecommerce.customers` ( id INT64, first_name STRING, last_name STRING, email STRING, age INT64, gender STRING, state STRING, street_address STRING, postal_code STRING, city STRING, country STRING, traffic_source STRING, created_at TIMESTAMP, latitude FLOAT64, longitude FLOAT64, point_location GEOGRAPHY); --load the customers table from public dataset and include geography transformation CREATE OR REPLACE TABLE `thelook_ecommerce.customers` AS SELECT id, first_name, last_name, email, age, gender, state, street_address, postal_code, city, country, traffic_source, created_at, latitude, longitude, ST_GEOGPOINT(users.longitude, users.latitude) AS point_location FROM `bigquery-public-data.thelook_ecommerce.users` AS users ; END ;

2. 點按「執行」。

請注意：預存程序會建立所有必要的資料表，並填入轉換後的資料，但不會傳回查詢結果，顯示顧客與最近配送中心之間的距離。如要這麼做，請前往下方的步驟 7。

3. 展開「儲存」下拉式選單，然後選取「儲存查詢」。系統會開啟「儲存查詢」對話方塊。
4. 在「名稱」欄位輸入 sp_create_load_tables。
5. 「區域」部分選取「」。
6. 點按「儲存」。這個預存程序會顯示在資料集的「處理常式」部分：
7. 點按「執行」，執行這個預存程序。

按照上方工作 3 的步驟 5，執行 SELECT SQL 陳述式，取得每位顧客與最近配送中心的距離。

8. 將下列純量子查詢複製到「查詢編輯器」：

SELECT customers.id as customer_id, ( SELECT MIN(ST_DISTANCE(centers.point_location, customers.point_location))/1000, FROM `thelook_ecommerce.centers` AS centers) AS distance_to_closest_center FROM `thelook_ecommerce.customers` AS customers ;

這項查詢會以公里為單位計算距離。

9. 點按「執行」。

點按「Check my progress」，確認工作已正確完成。

建立預存程序，確保更新作業更輕鬆 注意：您不會在本實驗室執行排定的查詢，但請務必瞭解更新資料和維護 SQL 管道的各種方法。

如要設定排定的查詢，只要在「查詢編輯器」中點按「排程」即可。系統會開啟「排定的新查詢」視窗，您可以在這裡指定多項查詢詳細資料。只要有需要定期更新的查詢，都可以執行這項操作。

結論

做得好！

您已成功使用 SQL 和 BigQuery，建立簡單且彈性的資料管道，滿足業務需求。

第一，您建立了資料集，並為要擷取的資料建立資料表結構定義。第二，您執行並探索了一系列轉換作業。

第三，您成功轉換並將資料載入新定義的資料表。

最後，您組織這些查詢並編成一套預存程序，納入計算過後的相關值，並瞭解如何排定查詢以定期更新資料。

在本實驗室，您瞭解了建立資料集和資料表結構定義的重要性。透過執行及檢查一系列轉換作業，您培養了新技能，能夠先轉換再將資料載入新定義的資料表。您也學到如何組織這些查詢並編成一套預存程序，以便納入計算過後的相關值，外加如何排定查詢以定期更新資料。

現在，您已瞭解如何運用 BigQuery 建立和管理 SQL 管道了。

關閉實驗室

結束實驗室前，請確認已完成所有工作。如果已確定完成，請依序點選「End Lab」和「Submit」。

關閉實驗室後，就無法進入實驗室環境，也無法再次存取在實驗室完成的工作。

Copyright 2026 Google LLC 保留所有權利。Google 和 Google 標誌是 Google LLC 的商標，其他公司和產品名稱則有可能是其關聯公司的商標。