使用 BigQuery 圖表追蹤供應鏈

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 所需的一切。

  1. 前往 Google Cloud 控制台。
  2. 點選右上方工具列中的「啟用 Cloud Shell」圖示。
  3. 如果出現提示,請點選「繼續」。

設定環境變數

在 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 控制台,請按照下列步驟操作:

  1. 在新分頁開啟 BigQuery 控制台。
  2. 將本實驗室的各個 SQL 程式碼片段貼到編輯器,然後點選「執行」按鈕。

BigQuery 編輯器

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

BigQuery 資料結構定義

注意:(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

Colab 中的 BigQuery Magic

%%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 資料載入

注意:如果直接在 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」模式相符的項目,並與上游關係交錯,形成強大的對應關係,進而找出必須召回的咖啡館項目。

輸出:Bad Tomatoes 後續影響

10. 清除

完成逐步操作說明後,請刪除資源,以免工作區產生任何剩餘費用。

DROP SCHEMA `restaurant` CASCADE;

11. 結語

恭喜!您已使用 BigQuery Graph 建立供應鏈模型,並執行影響分析。

總結

您學到了下列內容:

  1. 使用主鍵/外鍵宣告以圖表為主的關聯關係。
  2. 建立統一的屬性圖譜。
  3. 使用圖形查詢遍歷邏輯,有效率地瀏覽多節點關係。

如要進一步瞭解圖表架構,請參閱 Google Cloud 說明文件。