1. 簡介
在本程式碼實驗室中,您將瞭解如何運用 BigQuery Graph 解決複雜的供應鏈和物流問題。
您將建立餐廳供應鏈網路模型,著重於食品安全和品質驗證 (QC)。發生食品安全問題時 (例如供應商提供的食材受到汙染),時間就是關鍵。找出「影響範圍」並迅速執行精準召回,可節省成本並保護客戶。

傳統關聯式模型需要複雜的多步驟 JOIN 作業,才能追蹤多個階段的項目 (供應商 -> 配送中心 -> 販賣部 -> 商店 -> 成品)。有了 BigQuery Graph,我們可以直接建立這些關聯的模型,並使用 ISO GQL (Graph Query Language) 標準,直覺快速地執行查詢。
課程內容
- 如何根據現有 BigQuery 資料表定義圖表模型。
- 如何在 BigQuery 建立屬性圖譜。
- 如何執行遍歷查詢,追蹤上下游影響。
軟硬體需求
- 已啟用計費功能的 Google Cloud 專案。
- Google Cloud Shell。
預估費用
本實驗室的 BigQuery 分析費用預計低於 $5 美元,遠低於新使用者的免費方案配額。
2. 設定和需求
開啟 Cloud Shell
您將在 Cloud Shell 中進行大部分的工作。Cloud Shell 是預先載入的環境,提供使用 Google Cloud 所需的一切。
- 前往 Google Cloud 控制台。
- 點選右上方工具列中的「啟用 Cloud Shell」圖示。
- 如果出現提示,請點選「繼續」。
設定環境變數
在 Cloud Shell 設定專案 ID,簡化日後的指令。
export PROJECT_ID=$(gcloud config get-value project)
啟用 API
執行下列指令,啟用必要的 BigQuery API:
gcloud services enable bigquery.googleapis.com
3. 建立結構定義和資料表
您將建立資料集和資料表,代表供應鏈元件:
item:一般商品定義 (例如番茄、雞肉)。location:設施 (供應商、配送中心、咖啡廳)。itemlocation:代表商品目錄位置的交集資料表。bom:物料清單 (定義重量關係,例如品項 A 加進品項 B)。makes:對應itemlocation至item。stored_at:將itemlocation對應至location。
建立資料集
您可以使用 Cloud Shell 或 BigQuery 控制台,執行本實驗室的 SQL 指令。
如要使用 BigQuery 控制台,請按照下列步驟操作:
- 在新分頁開啟 BigQuery 控制台。
- 將本實驗室的各個 SQL 程式碼片段貼到編輯器,然後點選「執行」按鈕。

在 Cloud Shell 執行下列指令,或使用 BigQuery 控制台建立結構定義。您會在 SQL 中使用節點變數。

注意:(1) 如要在 Google Colab 執行這項操作,也可以使用 BigQuery magic 指令:%%bigquery。下列程式碼片段會在專案中建立餐廳結構定義,用於存放圖譜資料。(2) 如果您是從 Google Colab 執行,則需要使用 %%bigquery –project <PROJECT_ID>。請確認 PROJECT_ID 欄位對應至您要使用的專案:PROJECT_ID = "argolis-project-340214" # @param {"type":"string"} (3) 如果您使用 Colab,則須視需求安裝一些程式庫。如要使用圖表視覺化功能,請務必透過 pip 安裝程式庫:spanner-graph-notebook==1.1.5

%%bigquery --project=$PROJECT_ID
CREATE SCHEMA IF NOT EXISTS restaurant ;
建立資料表
執行下列 SQL 程式碼來建立資料表。
%%bigquery --project=$PROJECT_ID
-- 1. Item Table
DROP TABLE IF EXISTS `restaurant.item`;
CREATE TABLE `restaurant.item` (
itemKey STRING,
itemName STRING,
itemCategory STRING,
shelfLifeDays INT64,
PRIMARY KEY (itemKey) NOT ENFORCED
);
-- 2. Location Table
DROP TABLE IF EXISTS `restaurant.location`;
CREATE TABLE `restaurant.location` (
locationKey STRING,
locationType STRING,
locationCity STRING,
locationState STRING,
dunsNumber INT64,
PRIMARY KEY (locationKey) NOT ENFORCED
);
-- 3. ItemLocation Table
DROP TABLE IF EXISTS `restaurant.itemlocation`;
CREATE TABLE `restaurant.itemlocation` (
itemLocationKey STRING,
itemKey STRING,
locationKey STRING,
variants INT64,
PRIMARY KEY (itemLocationKey) NOT ENFORCED,
-- Foreign Key Definitions
FOREIGN KEY (itemKey) REFERENCES `restaurant.item`(itemKey) NOT ENFORCED,
FOREIGN KEY (locationKey) REFERENCES `restaurant.location`(locationKey) NOT ENFORCED
);
-- 4. BOM Table
DROP TABLE IF EXISTS `restaurant.bom`;
CREATE TABLE `restaurant.bom` (
bomKey INT64,
parentItemLocation STRING,
childItemLocation STRING,
childQuantity FLOAT64,
PRIMARY KEY (bomKey) NOT ENFORCED
);
-- 5. Makes Table
DROP TABLE IF EXISTS `restaurant.makes`;
CREATE TABLE `restaurant.makes` (
itemLocationKey STRING,
itemKey STRING,
locationKey STRING,
variants INT64,
PRIMARY KEY (itemLocationKey) NOT ENFORCED
);
DROP TABLE IF EXISTS `restaurant.stored_at`;
CREATE TABLE `restaurant.stored_at` (
itemLocationKey STRING,
itemKey STRING,
locationKey STRING,
variants INT64,
PRIMARY KEY (itemLocationKey) NOT ENFORCED
);
4. 載入範例資料
為確保本實驗室完全獨立,您將使用純 SQL LOAD DATA 陳述式,在資料表中填入範例資料。這個網路從「供應商」開始,經過「配送中心 (DC)」和「中央廚房」,最後到達「零售咖啡廳」。
執行下列 SQL 查詢來載入資料:

注意:如果直接在 BigQuery Studio 中執行,可以省略 %%bigquery
%%bigquery --project=$PROJECT_ID
-- Load Item
LOAD DATA OVERWRITE `restaurant.item`
FROM FILES (format = 'CSV', uris = ['gs://supply_chain_demo/item2.csv'], skip_leading_rows = 1);
-- Load Location
LOAD DATA OVERWRITE `restaurant.location`
FROM FILES (format = 'CSV', uris = ['gs://supply_chain_demo/location.csv'], skip_leading_rows = 1);
-- Load ItemLocation
LOAD DATA OVERWRITE `restaurant.itemlocation`
FROM FILES (format = 'CSV', uris = ['gs://supply_chain_demo/itemlocation.csv'], skip_leading_rows = 1);
-- Load BOM
LOAD DATA OVERWRITE `restaurant.bom`
FROM FILES (format = 'CSV', uris = ['gs://supply_chain_demo/bom2.csv'], skip_leading_rows = 1);
-- Load Makes
LOAD DATA OVERWRITE `restaurant.makes`
FROM FILES (format = 'CSV', uris = ['gs://supply_chain_demo/makes.csv'], skip_leading_rows = 1);
-- Load StoredAt
LOAD DATA OVERWRITE `restaurant.stored_at`
FROM FILES (format = 'CSV', uris = ['gs://supply_chain_demo/itemlocation.csv'], skip_leading_rows = 1);
5. 新增限制和定義圖表
建構圖譜前,請使用標準 SQL 主鍵和外鍵限制,宣告語意關係。這些設定可引導 BigQuery 瞭解節點 ID,並將邊緣資料表連結至節點資料表。
建立屬性圖譜
現在,您要將這些資料表彙整成單一、連貫的圖譜結構,名為 restaurant.bombod。
您定義:
- 節點:
item、location、itemlocation - 邊緣:
makes、stored_at和consists_of(BOM)
%%bigquery --project=$PROJECT_ID
CREATE OR REPLACE PROPERTY GRAPH `restaurant.bombod`
NODE TABLES (
`restaurant.item` KEY (itemKey) LABEL item PROPERTIES ALL COLUMNS,
`restaurant.location` KEY (locationKey) LABEL location PROPERTIES ALL COLUMNS,
`restaurant.itemlocation` KEY (itemLocationKey) LABEL itemlocation PROPERTIES ALL COLUMNS
)
EDGE TABLES (
`restaurant.makes`
KEY (itemLocationKey)
SOURCE KEY (itemLocationKey) REFERENCES `restaurant.itemlocation`(itemLocationKey)
DESTINATION KEY (itemKey) REFERENCES `restaurant.item`(itemKey)
LABEL makes PROPERTIES ALL COLUMNS,
`restaurant.bom`
KEY (bomKey)
SOURCE KEY (childItemLocation) REFERENCES `restaurant.itemlocation`(itemLocationKey)
DESTINATION KEY (parentItemLocation) REFERENCES `restaurant.itemlocation`(itemLocationKey)
LABEL consists_of PROPERTIES ALL COLUMNS,
`restaurant.stored_at`
KEY (itemLocationKey)
SOURCE KEY (itemLocationKey) REFERENCES `restaurant.itemlocation`(itemLocationKey)
DESTINATION KEY (locationKey) REFERENCES `restaurant.location`(locationKey)
LABEL stored_at PROPERTIES ALL COLUMNS
);
6. 以視覺化方式呈現供應鏈
您可以執行由上而下的遍歷查詢,查看整個供應鏈網路。在標準筆記本或支援的 UI (例如 %%bigquery --graph) 中,這會傳回視覺化地圖。
使用絕對圖表查詢,取得節點和邊緣設定。
注意:如前所述,如要在 Google Colab 或 Colab Enterprise 筆記本中執行這項操作,也可以使用 BigQuery magic 指令:%%bigquery。此外,如要在 Google Colab 或 Colab Enterprise 筆記本中以圖表呈現,請加入 –graph 旗標,例如:%%bigquery –graph
%%bigquery --project=$PROJECT_ID --graph output
Graph restaurant.bombod
match p=(a:itemlocation)-[c:consists_of]->(b:itemlocation)
match q=(a)-[d:stored_at]->(e:location)
optional match z=(f)-[g:makes]-(b)
return to_json(p) as ppath, to_json(q) as qpath, to_json(z) as zpath
輸出內容:

7. 應用實例 1:追蹤上游申訴
情境:顧客抱怨紐約分店的三明治雞肉品質不佳。您需要回溯成品,查看其直接組裝階段。
周遊查詢
使用圖譜遍歷查詢格式執行查詢。這會查看 consists_of 邊緣,這些邊緣會將下游的組件與上游的成分建立關聯。
%%bigquery --project=$PROJECT_ID --graph
GRAPH restaurant.bombod
MATCH p=(a:itemlocation)-[c:consists_of]->(b:itemlocation)
OPTIONAL MATCH q=(b)-[d:stored_at]-(e)
return to_json(p) as ppath, to_json(q) as qpath
由於 consists_of 邊緣資料表 (Ingredient -> Finished) 中的箭頭方向,向上游搜尋的連結可快速隔離依附的材料和儲存位置。
輸出:
8. 應用實例 2:影響分析
情境:暴風雪導致俄亥俄州哥倫布市的配送中心關閉。您需要知道哪些下游準備工作或成品會立即受到影響。
周遊查詢
您會從代表配送中心的特定 location 開始,找出該處存放的庫存,並查看哪些成品需要這些庫存。
# @title Impact of a storm on a DC
%%bigquery --project=$PROJECT_ID --graph
Graph restaurant.bombod
match path1=(z:itemlocation)-[m:stored_at]->(dc:location) where dc.locationKey like '%DC-Sysco-Columbus-OH%'
match path2=(z:itemlocation)-[c:consists_of]->(b:itemlocation)
match path3=(b:itemlocation)-[n:makes]->(item:item)
optional match path4=(b)-[p:stored_at]->(q:location)
return to_json(path1) as path1, to_json(path2) as path2,to_json(path3) as path3, to_json(path4) as path4
輸出:
9. 應用實例 3:下游召回
情境:供應商通知你,某批產品受到汙染:供應商的藤蔓熟成番茄。您需要找出咖啡廳所有受影響的最終菜單項目。
周遊查詢
您會尋找受污染原料的位置,然後執行向下游流動的路徑遍歷,找出最終受影響的項目。
%%bigquery --project=$PROJECT_ID --graph
Graph restaurant.bombod
match path1=(a:itemlocation)-[c:consists_of]->(b:itemlocation)-[e:makes]->(f:item) where f.itemKey like '%Tomato%'
return to_json(path1) as result
這項查詢會找出所有與「Tomato」模式相符的項目,並與上游關係交錯,形成強大的對應關係,進而找出必須召回的咖啡館項目。
輸出:
10. 清除
完成逐步操作說明後,請刪除資源,以免工作區產生任何剩餘費用。
DROP SCHEMA `restaurant` CASCADE;
11. 結語
恭喜!您已使用 BigQuery Graph 建立供應鏈模型,並執行影響分析。
總結
您學到了下列內容:
- 使用主鍵/外鍵宣告以圖表為主的關聯關係。
- 建立統一的屬性圖譜。
- 使用圖形查詢遍歷邏輯,有效率地瀏覽多節點關係。
如要進一步瞭解圖表架構,請參閱 Google Cloud 說明文件。