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

1. บทนำ

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

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

การกลัวอาหารในร้านอาหาร

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

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

  • วิธีกำหนดแบบจำลองกราฟบนตาราง BigQuery ที่มีอยู่
  • วิธีสร้าง Property Graph ภายใน 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 \
  bigqueryreservation.googleapis.com

สร้างการจองและการมอบหมาย BigQuery

หากต้องการเรียกใช้การค้นหา GQL คุณต้องมีการจองที่ใช้รุ่น Enterprise หรือ Enterprise Plus โดยสร้างการจองรุ่น Enterprise ที่มีการปรับขนาดอัตโนมัติใน Cloud Shell ดังนี้

# 1. Create a BigQuery Enterprise reservation with 0 baseline slots and 100 max autoscaling slots
bq mk --reservation \
  --project_id=${GCP_PROJECT} \
  --location=US \
  --edition=ENTERPRISE \
  --slots=0 \
  --autoscale_max_slots=100 \
  --ignore_idle_slots=true \
  supplychaingraph-reservation

# 2. Assign your Cloud project to the newly created reservation for query execution
bq mk --reservation_assignment \
  --project_id=${GCP_PROJECT} \
  --location=US \
  --reservation_id=supplychaingraph-reservation \
  --job_type=QUERY \
  --assignee_type=PROJECT \
  --assignee_id=${GCP_PROJECT}

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

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

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

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

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

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

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

ผู้แก้ไข BigQuery

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

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

หมายเหตุ: (1) หากต้องการเรียกใช้คำสั่งนี้ใน Google Colab คุณสามารถใช้คำสั่ง Magic ของ BigQuery ได้ด้วย เช่น %%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 แล้ว

Magic ของ BigQuery ใน 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. การโหลดข้อมูลตัวอย่าง

เพื่อให้แล็บนี้มีข้อมูลครบถ้วน คุณจะต้องป้อนข้อมูลตัวอย่างลงในตารางโดยใช้คำสั่ง LOAD DATA ของ SQL ล้วนๆ ซึ่งแสดงถึงเครือข่ายที่เริ่มต้นจากซัพพลายเออร์ ผ่านศูนย์กระจายสินค้า (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 เข้าใจตัวระบุโหนดและเชื่อมต่อตารางขอบกับตารางโหนด

สร้าง Property Graph

ตอนนี้คุณจะรวมตารางเหล่านี้เข้าด้วยกันเป็นโครงสร้างกราฟที่สอดคล้องกันเพียงโครงสร้างเดียวที่เรียกว่า 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 คุณสามารถใช้คำสั่ง Magic ของ BigQuery ได้ด้วย เช่น %%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

สรุป

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

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

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