Rückverfolgbarkeit der Lieferkette mit BigQuery Graph

1. Einführung

In diesem Codelab erfahren Sie, wie Sie BigQuery Graph nutzen können, um komplexe Probleme in der Lieferkette und Logistik zu lösen.

Sie modellieren ein Lieferkettennetzwerk für Restaurants mit Schwerpunkt auf Lebensmittelsicherheit und Qualitätskontrolle. Wenn ein Problem mit der Lebensmittelsicherheit auftritt, z. B. eine kontaminierte Zutat von einem Lieferanten, ist Eile geboten. Wenn Sie den „Blast Radius“ identifizieren und schnell einen gezielten Rückruf durchführen, können Sie Kosten sparen und Kunden schützen.

Lebensmittelvergiftung im Restaurant

Bei herkömmlichen relationalen Modellen sind komplexe, mehrstufige JOIN-Vorgänge erforderlich, um Artikel durch mehrere Phasen zu verfolgen (Lieferant -> Verteilzentrum -> Großküche -> Geschäft -> Fertigprodukt). Mit BigQuery Graph modellieren wir diese Verbindungen direkt und ermöglichen so intuitive und schnelle Abfragen mit dem ISO-Standard GQL (Graph Query Language).

Lerninhalte

  • Definieren eines Graphmodells auf Basis vorhandener BigQuery-Tabellen
  • Erstellen eines Property Graph in BigQuery
  • Ausführen von Traversal-Abfragen, um Auswirkungen vor- und nachgelagerter Prozesse zu verfolgen

Voraussetzungen

  • Google Cloud-Projekt mit aktivierter Abrechnungsfunktion
  • Google Cloud Shell

Kostenschätzung

Die Kosten für dieses Lab betragen voraussichtlich weniger als 5$ an BigQuery-Analysegebühren und liegen damit deutlich innerhalb der Zuweisungen des kostenlosen Kontingents für neue Nutzer.

2. Einrichtung und Anforderungen

Cloud Shell öffnen

Sie führen die meisten Aufgaben in der Cloud Shell aus, einer geladenen Umgebung, die alles enthält, was Sie für die Verwendung von Google Cloud benötigen.

  1. Gehen Sie zur Google Cloud Console.
  2. Klicken Sie in der Symbolleiste rechts oben auf das Symbol Cloud Shell aktivieren.
  3. Klicken Sie bei Aufforderung auf Weiter.

Umgebungsvariablen einrichten

Legen Sie in Cloud Shell Ihre Projekt-ID fest, um zukünftige Befehle zu vereinfachen.

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

BigQuery API aktivieren

Prüfen Sie, ob die BigQuery API aktiviert ist. Sie ist in der Regel standardmäßig aktiviert, aber es ist besser, auf Nummer sicher zu gehen.

gcloud services enable bigquery.googleapis.com

3. Schema und Tabellen erstellen

Sie erstellen ein Dataset und Tabellen, die Ihre Lieferkettenkomponenten darstellen:

  • item: Die generische Artikeldefinition (z. B. Tomate, Hähnchen)
  • location: Einrichtungen (Lieferanten, Verteilzentren, Cafés)
  • itemlocation: Die Schnittstellentabelle, die Lagerorte darstellt
  • bom: Stückliste (definiert Gewichtsbeziehungen, z. B. Artikel A ist in Artikel B enthalten)
  • makes: Ordnet itemlocation dem item zu
  • stored_at: Ordnet itemlocation dem location zu

Dataset erstellen

Sie können die SQL-Befehle in diesem Lab entweder in der Cloud Shell oder in der BigQuery-Konsole ausführen.

So verwenden Sie die BigQuery-Konsole:

  1. Öffnen Sie die BigQuery-Konsole in einem neuen Tab.
  2. Fügen Sie jeden SQL-Code aus diesem Lab in den Editor ein und klicken Sie dann auf die Schaltfläche Ausführen, um ihn auszuführen.

BigQuery-Editor

Führen Sie den folgenden Befehl in Cloud Shell aus oder verwenden Sie die BigQuery-Konsole, um das Schema zu erstellen. Sie verwenden Knotenvariablen in Ihrem SQL-Code.

BigQuery-Datenschema

Hinweis: (1) Wenn Sie diesen Code in Google Colab ausführen möchten, können Sie auch die BigQuery-Magic-Befehle verwenden: %%bigquery Mit dem folgenden Code wird das Restaurantschema in Ihrem Projekt erstellt, um Ihre Graphdaten zu speichern. (2) Sie müssen %%bigquery –project <PROJECT_ID> verwenden, wenn Sie den Code in Google Colab ausführen. Achten Sie darauf, dass das Feld PROJECT_ID dem entsprechenden Projekt zugeordnet ist, das Sie verwenden möchten: PROJECT_ID = "argolis-project-340214" # @param {"type":"string"} (3) Wenn Sie Colab verwenden, müssen Sie je nach Ihren Anforderungen einige Bibliotheken installieren. Wenn Sie die Graphvisualisierung verwenden möchten, installieren Sie die Bibliothek mit pip: spanner-graph-notebook==1.1.5

BigQuery-Magie in Colab

%%bigquery --project=$PROJECT_ID
CREATE SCHEMA IF NOT EXISTS restaurant ;

Tabellen erstellen

Führen Sie den folgenden SQL-Code aus, um die Tabellen zu erstellen.

%%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. Beispieldaten werden geladen

Damit dieses Lab vollständig in sich abgeschlossen ist, füllen Sie die Tabellen mit Beispieldaten mithilfe von reinen SQL-Anweisungen LOAD DATA. Dies stellt ein Netzwerk dar, das mit einem Lieferanten beginnt, über ein Verteilzentrum und eine Großküche verläuft und in einem Einzelhandelscafé endet.

Führen Sie die folgenden SQL-Abfragen aus, um die Daten zu laden:

BigQuery-Daten laden

Hinweis: Sie können %%bigquery weglassen, wenn Sie den Code direkt in BigQuery Studio ausführen.

%%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. Einschränkungen hinzufügen und Graph definieren

Bevor Sie den Graph erstellen, deklarieren Sie die semantischen Beziehungen mit den Standard-SQL-Einschränkungen für Primär- und Fremdschlüssel. Diese helfen BigQuery, Knoten-IDs zu verstehen und Edge-Tabellen mit Knotentabellen zu verbinden.

Property Graph erstellen

Jetzt führen Sie diese Tabellen zu einer einzigen zusammenhängenden Graphstruktur namens restaurant.bombod zusammen.

Sie definieren:

  • Knoten: item, location, itemlocation
  • Edges: makes, stored_at und consists_of (Stückliste)
%%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. Lieferkette visualisieren

Sie können eine Top-down-Traversal-Abfrage ausführen, um das gesamte Lieferkettennetzwerk zu sehen. In einem Standard-Notebook oder einer UI, die dies unterstützt (z. B. %%bigquery --graph), wird eine visuelle Karte zurückgegeben.

Verwenden Sie absolute Graphabfragen, um Knoten und Edges einzurichten.

Hinweis: Wie bereits erwähnt, können Sie diesen Code in Google Colab- oder Colab Enterprise-Notebooks auch mit den BigQuery-Magic-Befehlen ausführen: %%bigquery Wenn Sie den Graph in Google Colab- oder Colab Enterprise-Notebooks visualisieren möchten, fügen Sie das Flag „–graph“ hinzu: %%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

Ausgabe:

Grafik zum Gastronomiebedarf

7. Anwendungsfall 1: Beschwerde nachverfolgen

Szenario: Ein Kunde beschwert sich über die Qualität des Hähnchens in seinem Sandwich im Geschäft in New York. Sie müssen das Fertigprodukt zurückverfolgen, um die unmittelbaren Montageschritte zu sehen.

Traversal-Abfrage

Führen Sie die Abfrage im Graph Traversal-Abfrageformat aus. Dabei werden die consists_of-Edges betrachtet, die Baugruppen nachgelagert bis zu den vorgelagerten Zutaten in Beziehung setzen.

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

Aufgrund der Pfeilrichtung in der consists_of-Edge-Tabelle (Ingredient -> Finished) führt eine Suche in vorgelagerter Richtung schnell zu Links, die abhängige Materialien und Lagerorte isolieren.

Ausgabe: Herkunft des Hähnchens

8. Anwendungsfall 2: Auswirkungen analysieren

Szenario: Ein Schneesturm hat das Verteilzentrum in Columbus, Ohio, lahmgelegt. Sie müssen wissen, welche nachgelagerten Vorbereitungen oder Fertigprodukte unmittelbar betroffen sind.

Traversal-Abfrage

Sie beginnen am spezifischen location, der das Verteilzentrum darstellt, ermitteln den dort gelagerten Bestand und sehen, für welche Fertigprodukte er benötigt wird.

# @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

Ausgabe: Auswirkungen von Stürmen

9. Anwendungsfall 3: Rückruf nachgelagerter Prozesse

Szenario: Ein Lieferant informiert Sie über eine bestimmte Charge eines kontaminierten Produkts: sonnengereifte Tomaten vom Lieferanten. Sie müssen alle betroffenen Menüpunkte in den Cafés finden.

Traversal-Abfrage

Sie suchen nach dem Standort des kontaminierten Rohmaterials und führen dann eine Pfad-Traversal-Abfrage in nachgelagerter Richtung aus, um die letztendlich betroffenen Artikel zu finden.

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

Diese Abfrage findet alle Artikel, die mit „Tomate“ übereinstimmen und mit der vorgelagerten Beziehung verknüpft sind. Dadurch wird eine leistungsstarke Zuordnung erstellt, mit der Sie ermitteln können, welche Café-Artikel zurückgerufen werden müssen.

Ausgabe: Nachgelagerte Auswirkungen von „Bad Tomatoes“

10. Bereinigen

Löschen Sie die Ressourcen, nachdem Sie die Schritte der Anleitung ausgeführt haben, um Restkosten in Ihrem Arbeitsbereich zu vermeiden.

DROP SCHEMA `restaurant` CASCADE;

11. Fazit

Glückwunsch! Sie haben eine Lieferkette modelliert und eine Auswirkungsanalyse mit BigQuery Graph durchgeführt.

Zusammenfassung

Sie haben Folgendes gelernt:

  1. Graphzentrierte relationale Beziehungen mit Primär- und Fremdschlüsseln deklarieren
  2. Ein einheitliches Property Graph erstellen
  3. Effizient durch Beziehungen mit mehreren Knoten navigieren mithilfe der Graph Query-Traversal-Logik

Weitere Informationen zur Grapharchitektur finden Sie in der Google Cloud-Dokumentation.