מעקב אחר שרשרת האספקה באמצעות 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)

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

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

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

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

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

BigQuery Editor

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

סכימת נתונים של BigQuery

הערה: (1) כדי להריץ את הפקודה הזו ב-Google Colab, אפשר גם להשתמש בפקודות הקסם של BigQuery: ‎%%bigquery בקטע הקוד הבא נוצרת סכימת המסעדה בפרויקט שלכם כדי לאחסן את נתוני הגרף. ‫(2) תצטרכו להשתמש ב-%%bigquery –project <PROJECT_ID> אם אתם מפעילים את הפקודה מ-Google Colab. מוודאים שהשדה 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), הפונקציה הזו מחזירה מפה ויזואלית.

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

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

בגלל כיוון החץ בטבלת 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 Downstream Impact

10. מחיקה

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

DROP SCHEMA `restaurant` CASCADE;

11. סיכום

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

סיכום

למדתם איך:

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

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