BigQuery Graph を使用したサプライチェーンのトレーサビリティ

1. はじめに

この Codelab では、BigQuery Graph を活用して、複雑なサプライチェーンとロジスティクスの問題を解決する方法を学びます。

食品の安全性と品質管理に焦点を当てたレストランのサプライチェーンネットワークをモデル化します。食品の安全性に関する問題（サプライヤーから汚染された食材が納入されたなど）が発生した場合は、迅速な対応が不可欠です。「影響範囲」を特定し、外科手術のようなリコールを迅速に実施することで、コストを削減し、顧客を保護できます。

レストランの食品に関するスキャンダル

従来のリレーショナルモデルでは、複数の段階（サプライヤー -> DC -> 委託販売 -> 店舗 -> 完成品）でアイテムを追跡するために、複雑な複数ステップの JOIN オペレーションが必要です。BigQuery Graph では、これらの接続を直接モデル化し、ISO GQL（Graph Query Language）標準を使用して直感的で高速なクエリを実行できます。

学習内容

既存の BigQuery テーブルの上にグラフモデルを定義する方法。
BigQuery 内にプロパティグラフを作成する方法。
トラバーサルクエリを実行して、アップストリームとダウンストリームの影響を追跡する方法。

必要なもの

課金が有効な Google Cloud プロジェクトが用意されていること。
Google Cloud Shell。

費用の見積もり

このラボの BigQuery 分析料金は $5 USD 未満になる見込みです。これは、新規ユーザーの無料枠の範囲内です。

2. 設定と要件

Cloud Shell を開く

ほとんどの作業は、Google Cloud の使用に必要なものがすべて含まれている環境である Cloud Shell で行います。

Google Cloud Console に移動します。
右上にある [Cloud Shell をアクティブにする] アイコンをクリックします。
プロンプトが表示されたら、[続行] をクリックします。

環境変数を設定する

Cloud Shell で、プロジェクト ID を設定して、今後のコマンドを簡素化します。

export PROJECT_ID=$(gcloud config get-value project)

BigQuery API を有効化

BigQuery API が有効になっていることを確認します。通常はデフォルトで有効になっていますが、安全のためにも確認することをおすすめします。

gcloud services enable bigquery.googleapis.com

3. スキーマとテーブルの作成

サプライチェーンコンポーネントを表すデータセットとテーブルを作成します。

item: 汎用アイテムの定義（トマト、鶏肉など）。
location: 施設（サプライヤー、配送センター、カフェ）。
itemlocation: 在庫の場所を表す交差テーブル。
bom: 部品構成表（重み関係を定義します。たとえば、アイテム A がアイテム B に含まれるなど）。
makes: itemlocation を item にマッピングします。
stored_at: itemlocation を location にマッピングします。

データセットの作成

このラボの SQL コマンドは、Cloud Shell または BigQuery コンソールを使用して実行できます。

BigQuery コンソールを使用するには:

新しいタブで BigQuery コンソールを開きます。
このラボの各 SQL スニペットをエディタに貼り付け、[実行] ボタンをクリックして実行します。

BigQuery エディタ

Cloud Shell で次のコマンドを実行するか、BigQuery コンソールを使用してスキーマを作成します。SQL でノード変数を使用します。

BigQuery データスキーマ

注: （1）Google Colab でこれを実行するには、BigQuery マジックコマンド（%%bigquery）を使用することもできます。次のスニペットは、グラフデータを格納するレストランスキーマをプロジェクト内に作成します。（2）Google Colab から実行する場合は、%%bigquery –project <PROJECT_ID> を使用する必要があります。フィールド PROJECT_ID が、使用する予定の適切なプロジェクトにマッピングされていることを確認します。PROJECT_ID = "argolis-project-340214" # @param {"type":"string"} (3) Colab を使用している場合は、要件に応じてライブラリをインストールする必要があります。グラフの可視化を使用する場合は、ライブラリ spanner-graph-notebook==1.1.5 を pip でインストールしてください。

Colab の BigQuery マジック

%%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 がノード識別子を理解し、エッジテーブルをノードテーブルに接続する際に役立ちます。

プロパティグラフを作成する

次に、これらのテーブルを restaurant.bombod という単一のまとまりのあるグラフ構造に統合します。

定義する内容は次のとおりです。

ノード: item、location、itemlocation
Edges: 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 マジックコマンド %%bigquery を使用することもできます。また、Google Colab または Colab Enterprise ノートブックでグラフを可視化するには、%%bigquery –graph のように –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: ダウンストリームリコール

シナリオ: サプライヤーから、サプライヤーの Vine-Ripened Tomatoes という特定のバッチの汚染された製品について通知を受けました。カフェで影響を受ける最終メニュー項目をすべて見つける必要があります。

走査クエリ

汚染された原材料の場所を探し、下流に流れるパストラバーサルを実行して、最終的に影響を受けるアイテムを見つけます。

%%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 を使用してサプライチェーンをモデル化し、影響分析を実行しました。

まとめ

学習内容:

主キー/外部キーを使用して、グラフ中心のリレーショナル関係を宣言します。
統合されたプロパティグラフを作成します。
グラフクエリの走査ロジックを使用して、マルチノードの関係を効率的にナビゲートします。

グラフアーキテクチャの詳細については、Google Cloud ドキュメントをご覧ください。

BigQuery Graph を使用したサプライ チェーンのトレーサビリティ

1. はじめに

学習内容

必要なもの

費用の見積もり

2. 設定と要件

Cloud Shell を開く

環境変数を設定する

BigQuery API を有効化

3. スキーマとテーブルの作成

データセットの作成

テーブルを作成する

4. サンプルデータの読み込み

5. 制約の追加とグラフの定義

プロパティ グラフを作成する

6. サプライ チェーンの可視化

7. ユースケース 1: 上流の申し立てを追跡する

走査クエリ

8. ユースケース 2: インパクト分析

走査クエリ

9. ユースケース 3: ダウンストリーム リコール

走査クエリ

10. クリーンアップ

11. まとめ

まとめ

BigQuery Graph を使用したサプライチェーンのトレーサビリティ

プロパティグラフを作成する

6. サプライチェーンの可視化

9. ユースケース 3: ダウンストリームリコール