מעקב אחר שרשרת האספקה באמצעות BigQuery Graph

1. מבוא

ב-Codelab הזה תלמדו איך להשתמש ב-BigQuery Graph כדי לפתור בעיות מורכבות בשרשרת האספקה ובלוגיסטיקה.

תבנו מודל של רשת אספקה למסעדה, עם דגש על בטיחות מזון ובקרת איכות. כשמתעוררת בעיה שקשורה לבטיחות מזון – כמו רכיב מזון מזוהם מספק – הזמן הוא גורם קריטי. זיהוי מהיר של 'רדיוס הפיצוץ' וביצוע מהיר של החזרה ממוקדת של מוצרים יכולים לחסוך בעלויות ולהגן על הלקוחות.

פחד ממזון במסעדה

מודלים רלציוניים מסורתיים דורשים פעולות מורכבות עם כמה שלבים של JOIN כדי לעקוב אחרי פריטים בכמה שלבים (ספק -> מרכז הפצה -> מטבח מרכזי -> חנות -> פריט מוכן). בעזרת BigQuery Graph, אנחנו יוצרים מודלים של הקשרים האלה ישירות, וכך מאפשרים לבצע שאילתות אינטואיטיביות ומהירות באמצעות תקן ה-GQL (Graph Query Language) של ISO.

מה תלמדו

  • איך מגדירים מודל גרף על טבלאות קיימות ב-BigQuery.
  • איך יוצרים גרף נכסים ב-BigQuery.
  • איך מריצים שאילתות מעבר כדי לעקוב אחרי ההשפעות במעלה ובמורד הזרם.

הדרישות

  • פרויקט ב-Google Cloud עם חיוב מופעל.
  • ‫Google Cloud Shell.

עלות משוערת

העלות של שיעור ה-Lab הזה צפויה להיות פחות מ-5 דולר ארה"ב בעמלות ניתוח ב-BigQuery, הרבה מתחת למכסות של התוכנית בחינם למשתמשים חדשים.

2. הגדרה ודרישות

פתיחת Cloud Shell

רוב העבודה תתבצע ב-Cloud Shell, סביבה עשירה שכוללת את כל מה שצריך כדי להשתמש ב-Google Cloud.

  1. נכנסים אל מסוף Google Cloud.
  2. בסרגל הכלים שבפינה השמאלית העליונה, לוחצים על הסמל הפעלת Cloud Shell.
  3. לוחצים על המשך אם מוצגת בקשה.

הגדרה של משתני סביבה

ב-Cloud Shell, מגדירים את מזהה הפרויקט כדי לפשט פקודות עתידיות.

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

הפעלת BigQuery API

מוודאים ש-BigQuery API מופעל. האפשרות הזו בדרך כלל מופעלת כברירת מחדל, אבל עדיף להיות בטוחים.

gcloud services enable bigquery.googleapis.com

3. יצירת הסכימה והטבלאות

תיצרו מערך נתונים וטבלאות שמייצגים את הרכיבים של שרשרת האספקה:

  • ‫item: ההגדרה הכללית של הפריט (למשל, עגבנייה, עוף).
  • ‫location: מתקנים (ספקים, מרכזי הפצה, בתי קפה).
  • ‫itemlocation: טבלת הצלבה שמייצגת מיקומי מלאי.
  • ‫bom: מפרט חומרים (מגדיר קשרי משקל, למשל: פריט א' נכנס לפריט ב').
  • ‫makes: המיפוי של Maps הוא itemlocation אל item.
  • ‫stored_at: מפות Google itemlocation עד location.

יצירת מערך נתונים

אפשר להריץ את פקודות ה-SQL בשיעור ה-Lab הזה באמצעות Cloud Shell או מסוף BigQuery.

כדי להשתמש במסוף BigQuery:

  1. פותחים את BigQuery Console בכרטיסייה חדשה.
  2. מדביקים כל קטע SQL מהמעבדה הזו בעורך, ואז לוחצים על הלחצן Run כדי להריץ אותו.

BigQuery Editor

מריצים את הפקודה הבאה ב-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, צריך להתקין כמה ספריות בהתאם לדרישות. אם אתם מתכוונים להשתמש בהדמיה של גרפים, הקפידו להשתמש בפקודה pip install כדי להתקין את הספרייה: spanner-graph-notebook==1.1.5

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. טעינת נתונים לדוגמה

כדי ששיעור ה-Lab הזה יהיה עצמאי לחלוטין, תאכלסו את הטבלאות בנתונים לדוגמה באמצעות הצהרות SQL LOAD DATA טהורות. התרשים הזה מייצג רשת שמתחילה בספק, עוברת דרך מרכז הפצה ומטבח מרכזי, ומגיעה אל בית קפה קמעונאי.

מריצים את שאילתות ה-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 להבין את מזהי הצמתים ולחבר בין טבלאות קצה לטבלאות צמתים.

יצירת גרף נכסים

עכשיו מאחדים את הטבלאות האלה למבנה גרף מגובש אחד שנקרא 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. הדמיה של שרשרת האספקה

אפשר להריץ שאילתת מעבר מלמעלה למטה כדי לראות את כל רשת שרשרת האספקה. במחברת רגילה או בממשק משתמש שתומך בכך (כמו %%bigquery --graph), הפונקציה הזו מחזירה מפה חזותית.

משתמשים בשאילתות גרף מוחלטות כדי להגדיר צמתים וקשתות.

הערה: כמו שצוין קודם, כדי להריץ את הפקודה הזו ב-notebooks של Google Colab או Colab Enterprise, אפשר גם להשתמש בפקודות הקסם של BigQuery: ‎%%bigquery. כדי להציג את הגרף ב-notebooks של 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: מעקב אחרי תלונה במעלה הזרם

תרחיש: לקוח מתלונן על איכות העוף בכריך שקנה בחנות בניו יורק. צריך לעקוב אחרי הפריט המוגמר אחורה כדי לראות את שלבי ההרכבה המיידיים שלו.

שאילתת מעבר

מריצים את השאילתה באמצעות פורמט השאילתה 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

בגלל כיוון החץ בconsists_ofטבלת Edge (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: זכירה במורד הזרם

תרחיש: ספק מודיע לכם על אצווה ספציפית של מוצר מזוהם: עגבניות שהבשילו על הגפן מהספק. צריך למצוא את כל הפריטים הסופיים בתפריט של בתי הקפה שהושפעו מהשינוי.

שאילתת מעבר

מחפשים את המיקום של חומר הגלם המזוהם, ואז מבצעים Path traversal במורד הזרם כדי למצוא את הפריטים שהושפעו בסופו של דבר.

%%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

השאילתה הזו מאתרת את כל הפריטים שתואמים לדפוס 'עגבנייה' ושקשורים לקשר במעלה הזרם, ולכן היא מיפוי יעיל שמאפשר לגלות אילו פריטים בבית הקפה צריך להחזיר.

פלט: ההשפעה בהמשך של Bad Tomatoes

10. הסרת המשאבים

כדי למנוע חיובים מיותרים ב-Workspace, מומלץ למחוק את המשאבים אחרי שמסיימים את השלבים במדריך.

DROP SCHEMA `restaurant` CASCADE;

11. סיכום

מעולה! יצרתם מודל של שרשרת אספקה והרצתם ניתוח השפעה באמצעות BigQuery Graph.

סיכום

למדתם:

  1. הצהרה על קשרים יחסיים שמתמקדים בגרף באמצעות מפתחות ראשיים ומפתחות זרים.
  2. יוצרים גרף נכסים מאוחד.
  3. ניווט יעיל בקשרים בין כמה צמתים באמצעות לוגיקת מעבר של שאילתת גרף.

מידע נוסף על ארכיטקטורת גרפים זמין במסמכי Google Cloud.