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

1. מבוא

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

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

בהלה ממזון במסעדה

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

מה תלמדו

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

הדרישות

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

עלות משוערת

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

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

פתיחת Cloud Shell

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

  1. נכנסים למסוף Google Cloud.
  2. לוחצים על הסמל Activate 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: טבלת החיתוך שמייצגת את מיקומי המלאי.
  • bom: מפרט החומרים (מגדיר את יחסי המשקל, למשל: פריט א' נכנס לפריט ב').
  • ‫makes: ממפה את itemlocation אל item.
  • ‫stored_at: ממפה את itemlocation ל-location.

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

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

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

  1. פותחים את מסוף BigQuery בכרטיסייה חדשה.
  2. מדביקים כל קטע קוד SQL מה-Lab הזה בעורך, ואז לוחצים על הלחצן 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: ‏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 הזה יהיה עצמאי לחלוטין, תאכלסו את הטבלאות בנתונים לדוגמה באמצעות הצהרות 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 להבין את המזהים של הצמתים ולקשר בין טבלאות הקצוות לטבלאות הצמתים.

יצירת תרשים נכסים

עכשיו אתם מאחדים את הטבלאות האלה למבנה גרף אחד שנקרא restaurant.bombod.

אתם מגדירים:

  • ‫Nodes:‏ 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. המחשה חזותית של שרשרת האספקה

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

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

הערה: כמו שצוין קודם, כדי להריץ את זה ב-Google Colab או ב-Colab Enterprise Notebooks, אפשר גם להשתמש בפקודות הקסם של 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

בגלל כיוון החץ בטבלת הקצוות (Ingredient -> Finished) של consists_of, חיפוש שזורם במעלה הזרם מניב קישורים שמבודדים במהירות חומרים תלויים ומיקומי אחסון.

פלט: ניווט אל &quot;מקור העוף&quot;

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

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

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

10. מחיקה

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

DROP SCHEMA `restaurant` CASCADE;

11. סיכום

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

סיכום

למדתם איך:

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

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