การตรวจสอบย้อนกลับของซัพพลายเชนด้วยกราฟ BigQuery

1. บทนำ

ใน Codelab นี้ คุณจะได้เรียนรู้วิธีใช้ประโยชน์จาก BigQuery Graph เพื่อแก้ปัญหาซัพพลายเชนและโลจิสติกส์ที่ซับซ้อน

คุณจะสร้างโมเดลเครือข่ายซัพพลายเชนของร้านอาหารที่มุ่งเน้นด้านความปลอดภัยของอาหารและการควบคุมคุณภาพ เมื่อเกิดปัญหาด้านความปลอดภัยของอาหาร เช่น ส่วนผสมที่ปนเปื้อนจากซัพพลายเออร์ เวลาเป็นสิ่งสำคัญ การระบุ "ขอบเขตความเสียหาย" และการเรียกคืนสินค้าอย่างรวดเร็วและแม่นยำจะช่วยลดต้นทุนและปกป้องลูกค้าได้

ความหวาดกลัวเกี่ยวกับอาหารในร้านอาหาร

โมเดลเชิงสัมพันธ์แบบดั้งเดิมต้องใช้การดำเนินการ JOIN ที่ซับซ้อนและมีหลายขั้นตอนเพื่อติดตามสินค้าผ่านหลายขั้นตอน (ซัพพลายเออร์ -> ศูนย์กระจายสินค้า -> โรงครัวกลาง -> ร้านค้า -> สินค้าสำเร็จรูป) BigQuery Graph ช่วยให้เราสร้างโมเดลการเชื่อมต่อเหล่านี้ได้โดยตรง ซึ่งช่วยให้ทำการค้นหาได้อย่างรวดเร็วและเข้าใจง่ายโดยใช้มาตรฐาน ISO GQL (Graph Query Language)

สิ่งที่คุณจะได้เรียนรู้

  • วิธีกำหนดโมเดลกราฟบนตาราง BigQuery ที่มีอยู่
  • วิธีสร้างกราฟพร็อพเพอร์ตี้ภายใน BigQuery
  • วิธีเรียกใช้การค้นหาแบบข้ามผ่านเพื่อติดตามผลกระทบทั้งในระดับต้นน้ำและปลายน้ำ

สิ่งที่คุณต้องมี

  • โปรเจ็กต์ Google Cloud ที่เปิดใช้การเรียกเก็บเงิน
  • Google Cloud Shell

ค่าใช้จ่ายโดยประมาณ

ห้องทดลองนี้คาดว่าจะมีค่าใช้จ่ายในการวิเคราะห์ BigQuery น้อยกว่า $5 USD ซึ่งอยู่ในการจัดสรรรุ่นฟรีสำหรับผู้ใช้ใหม่

2. การตั้งค่าและข้อกำหนด

เปิด Cloud Shell

คุณจะทำงานส่วนใหญ่ใน Cloud Shell ซึ่งเป็นสภาพแวดล้อมที่โหลดไว้ซึ่งมาพร้อมกับทุกสิ่งที่คุณต้องการเพื่อใช้ Google Cloud

  1. ไปที่คอนโซล Google Cloud
  2. คลิกไอคอนเปิดใช้งาน Cloud Shell ในแถบเครื่องมือด้านขวาบน
  3. คลิกต่อไปหากได้รับข้อความแจ้ง

ตั้งค่าตัวแปรสภาพแวดล้อม

ใน Cloud Shell ให้ตั้งค่ารหัสโปรเจ็กต์เพื่อลดความซับซ้อนของคำสั่งในอนาคต

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

เปิดใช้ API

เรียกใช้คำสั่งนี้เพื่อเปิดใช้ BigQuery API ที่จำเป็น

gcloud services enable bigquery.googleapis.com

3. การสร้างสคีมาและตาราง

คุณจะสร้างชุดข้อมูลและตารางที่แสดงถึงองค์ประกอบของซัพพลายเชน ดังนี้

  • item: คำจำกัดความของสินค้าทั่วไป (เช่น มะเขือเทศ ไก่)
  • location: สถานที่ (ซัพพลายเออร์ ศูนย์กระจายสินค้า ร้านกาแฟ)
  • itemlocation: ตาราง Intersection ที่แสดงสถานที่ตั้งของสินค้าคงคลัง
  • bom: Bill of Materials (กำหนดความสัมพันธ์ของน้ำหนัก เช่น สินค้า ก อยู่ในสินค้า ข)
  • makes: แมป itemlocation กับ item
  • stored_at: แมป itemlocation กับ location

สร้างชุดข้อมูล

คุณสามารถเรียกใช้คำสั่ง SQL ในห้องทดลองนี้โดยใช้ Cloud Shell หรือคอนโซล BigQuery ก็ได้

วิธีใช้คอนโซล BigQuery

  1. เปิด BigQuery Console ในแท็บใหม่
  2. วางข้อมูลโค้ด SQL แต่ละรายการจากห้องทดลองนี้ลงในเครื่องมือแก้ไข แล้วคลิกปุ่มเรียกใช้เพื่อดำเนินการ

ผู้แก้ไข BigQuery

เรียกใช้คำสั่งต่อไปนี้ใน Cloud Shell หรือใช้คอนโซล BigQuery เพื่อสร้างสคีมา คุณจะใช้ตัวแปรโหนดใน SQL

สคีมาข้อมูล BigQuery

หมายเหตุ: (1) หากต้องการดำเนินการนี้ใน Google Colab คุณยังใช้คำสั่ง BigQuery Magic ได้ด้วย: %%bigquery ข้อมูลโค้ดต่อไปนี้จะสร้างสคีมาร้านอาหารภายในโปรเจ็กต์เพื่อเก็บข้อมูลกราฟ (2) คุณจะต้องใช้ %%bigquery –project <PROJECT_ID> หากเรียกใช้จาก Google Colab ตรวจสอบว่าช่อง PROJECT_ID แมปกับโปรเจ็กต์ที่เหมาะสมที่คุณต้องการใช้: PROJECT_ID = "argolis-project-340214" # @param {"type":"string"} (3) หากใช้ Colab คุณจะต้องติดตั้งไลบรารีบางรายการ ทั้งนี้ขึ้นอยู่กับข้อกำหนดของคุณ หากคุณจะใช้การแสดงภาพกราฟ ให้ตรวจสอบว่าคุณได้ติดตั้งไลบรารี spanner-graph-notebook==1.1.5 โดยใช้ pip

BigQuery Magic ใน Colab

%%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 ได้หากเรียกใช้ใน BigQuery Studio โดยตรง

%%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 เข้าใจตัวระบุโหนดและเชื่อมต่อตาราง Edge กับตารางโหนดได้

สร้างกราฟพร็อพเพอร์ตี้

ตอนนี้ คุณจะรวมตารางเหล่านี้เข้าเป็นโครงสร้างกราฟที่เชื่อมโยงกันเป็นหนึ่งเดียวที่เรียกว่า 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. การแสดงภาพซัพพลายเชน

คุณสามารถเรียกใช้การค้นหาการส่งผ่านจากบนลงล่างเพื่อดูเครือข่ายซัพพลายเชนทั้งหมด ใน Notebook มาตรฐานหรือ UI ที่รองรับ (เช่น %%bigquery --graph) ฟังก์ชันนี้จะแสดงแผนภาพ

ใช้การค้นหากราฟแบบสัมบูรณ์เพื่อตั้งค่าโหนดและขอบ

หมายเหตุ: ตามที่กล่าวไว้ก่อนหน้านี้ หากต้องการดำเนินการนี้ใน Google Colab หรือ Colab Enterprise Notebooks คุณยังใช้คำสั่ง Magic ของ BigQuery ได้ด้วย: %%bigquery นอกจากนี้ หากต้องการแสดงภาพกราฟใน Google Colab หรือ Colab Enterprise Notebooks ให้ใส่แฟล็ก –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: การติดตามการร้องเรียนจากต้นทาง

สถานการณ์: ลูกค้าร้องเรียนเกี่ยวกับคุณภาพของไก่ในแซนด์วิชที่ร้านสาขากรุงเทพฯ คุณต้องติดตามย้อนกลับจากสินค้าที่เสร็จสมบูรณ์เพื่อดูขั้นตอนการประกอบที่เกิดขึ้นก่อนหน้าทันที

การค้นหาการส่งผ่านข้อมูล

เรียกใช้การค้นหาโดยใช้รูปแบบการค้นหา Graph Traversal ซึ่งจะพิจารณาขอบ 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

เนื่องจากทิศทางของลูกศรในตาราง Edge 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

การค้นหานี้จะค้นหารายการทั้งหมดที่มีรูปแบบตรงกับ "มะเขือเทศ" และเชื่อมโยงกับความสัมพันธ์แบบอัปสตรีม ทำให้เป็นการแมปที่มีประสิทธิภาพซึ่งจะเผยแพร่เพื่อค้นหาว่าต้องเรียกคืนรายการใดในคาเฟ่

เอาต์พุต: ผลกระทบปลายน้ำของมะเขือเทศเน่า

10. ล้าง

ลบทรัพยากรเมื่อทำตามขั้นตอนในคำแนะนำแบบทีละขั้นเสร็จแล้วเพื่อหลีกเลี่ยงค่าใช้จ่ายที่ค้างอยู่ในพื้นที่ทำงาน

DROP SCHEMA `restaurant` CASCADE;

11. บทสรุป

ยินดีด้วย คุณได้สร้างโมเดลซัพพลายเชนและทำการวิเคราะห์ผลกระทบโดยใช้ BigQuery Graph แล้ว

สรุป

คุณได้เรียนรู้วิธีทำสิ่งต่อไปนี้

  1. ประกาศความสัมพันธ์เชิงสัมพันธ์ที่เน้นกราฟด้วยคีย์หลัก/คีย์นอก
  2. สร้างกราฟพร็อพเพอร์ตี้แบบรวม
  3. ไปยังส่วนต่างๆ ของความสัมพันธ์แบบหลายโหนดได้อย่างมีประสิทธิภาพโดยใช้ตรรกะการข้ามผ่าน Graph Query

หากต้องการดูข้อมูลเชิงลึกเพิ่มเติมเกี่ยวกับสถาปัตยกรรมกราฟ โปรดไปที่เอกสารประกอบของ Google Cloud