Tracciabilità della supply chain con BigQuery Graph

1. Introduzione

In questo codelab imparerai a utilizzare BigQuery Graph per risolvere problemi complessi di catena di fornitura e logistica.

Modellerai una rete di catena di fornitura di un ristorante concentrandoti sulla sicurezza alimentare e sul controllo della qualità. Quando si verifica un problema di sicurezza alimentare, ad esempio un ingrediente contaminato di un fornitore, il tempo è essenziale. Identificare il "raggio di impatto" ed eseguire rapidamente un richiamo chirurgico può ridurre i costi e proteggere i clienti.

I modelli relazionali tradizionali richiedono operazioni JOIN complesse e in più passaggi per tracciare gli articoli in più fasi (Fornitore -> DC -> Cucina -> Negozio -> Articolo finito). Con BigQuery Graph, modelliamo queste connessioni direttamente, consentendo query intuitive e veloci utilizzando lo standard ISO GQL (Graph Query Language).

Obiettivi didattici

• Come definire un modello di grafico sulle tabelle BigQuery esistenti.
• Come creare un grafico delle proprietà in BigQuery.
• Come eseguire query di attraversamento per tracciare gli impatti a monte e a valle.

Che cosa ti serve

• Un progetto Google Cloud con la fatturazione abilitata.
• Google Cloud Shell.

Stima dei costi

Si prevede che questo lab costi meno di 5$ in commissioni di analisi di BigQuery, ben al di sotto delle allocazioni del Livello senza costi per i nuovi utenti.

2. Configurazione e requisiti

Apri Cloud Shell

Svolgerai la maggior parte del lavoro in Cloud Shell, un ambiente precaricato con tutto il necessario per utilizzare Google Cloud.

1. Vai alla console Google Cloud.
2. Fai clic sull'icona Attiva Cloud Shell nella barra degli strumenti in alto a destra.
3. Se richiesto, fai clic su Continua.

Imposta le variabili di ambiente

In Cloud Shell, imposta l'ID progetto per semplificare i comandi futuri.

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

Abilita API

Esegui questo comando per abilitare l'API BigQuery richiesta:

gcloud services enable bigquery.googleapis.com \
  bigqueryreservation.googleapis.com 

Crea la prenotazione e l'assegnazione di BigQuery

Per eseguire query GQL, devi avere una prenotazione che utilizzi la versione Enterprise o Enterprise Plus. Crea una prenotazione Enterprise Edition con scalabilità automatica in 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}

Computing con scalabilità automatica efficiente in termini di costi:
Se configuri --slots=0 (0 slot di base) e --autoscale_max_slots=100, la prenotazione BigQuery non comporta costi di base quando è inattiva. BigQuery esegue automaticamente lo scale up fino a 100 slot dedicati solo durante l'esecuzione attiva delle query o delle ricerche vettoriali, quindi esegue lo scale down a 0 al termine.

3. Creazione dello schema e delle tabelle

Creerai un set di dati e tabelle che rappresentano i componenti della catena di fornitura:

• item: la definizione generica dell'articolo (ad es. Pomodoro, Pollo).
• location: le strutture (fornitori, centri di distribuzione, bar).
• itemlocation: la tabella di intersezione che rappresenta le posizioni dell'inventario.
• bom: la distinta base (definisce le relazioni di peso, ad es. l'articolo A va nell'articolo B).
• makes: mappa itemlocation a item.
• stored_at: mappa itemlocation a location.

Crea set di dati

Puoi eseguire i comandi SQL in questo lab utilizzando Cloud Shell o la console BigQuery.

Per utilizzare la console BigQuery:

1. Apri la console BigQuery in una nuova scheda.
2. Incolla ogni snippet SQL di questo lab nell'editor, quindi fai clic sul pulsante Esegui per eseguirlo.

Esegui il seguente comando in Cloud Shell o utilizza la console BigQuery per creare lo schema. Utilizzerai le variabili dei nodi in SQL.

Nota: (1) per eseguire questa operazione in Google Colab, puoi utilizzare anche i comandi magici di BigQuery: %%bigquery Il seguente snippet crea lo schema del ristorante all'interno del progetto per ospitare i dati del grafico. (2) Dovrai utilizzare %%bigquery –project <PROJECT_ID> se esegui l'operazione da Google Colab. Assicurati che il campo PROJECT_ID sia mappato al progetto appropriato che intendi utilizzare: PROJECT_ID = "argolis-project-340214" # @param {"type":"string"} (3) Se utilizzi Colab, a seconda dei requisiti dovrai installare alcune librerie. Se intendi utilizzare la visualizzazione del grafico, assicurati di installare la libreria tramite pip: spanner-graph-notebook==1.1.5

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

Crea tabelle

Esegui il seguente codice SQL per creare le tabelle.

%%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. Caricamento dei dati di esempio in corso…

Per rendere questo lab completamente autonomo, popolerai le tabelle con dati di esempio utilizzando le istruzioni SQL LOAD DATA pure. Questo rappresenta una rete che inizia con un fornitore, attraversa un centro di distribuzione (DC) e una cucina e arriva a un bar al dettaglio.

Esegui le seguenti query SQL per caricare i dati:

Nota: puoi omettere %%bigquery se esegui l'operazione direttamente in 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. Aggiunta di vincoli e definizione del grafico

Prima di creare il grafico, dichiara le relazioni semantiche utilizzando i vincoli di chiave primaria e chiave esterna SQL standard. Questi vincoli aiutano BigQuery a comprendere gli identificatori dei nodi e a collegare le tabelle dei bordi alle tabelle dei nodi.

Crea grafico delle proprietà

Ora unisci queste tabelle in una singola struttura di grafico coesa denominata restaurant.bombod.

Definisci:

• Nodi: item, location, itemlocation
• Bordi: makes, stored_at e 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. Visualizzazione della supply chain

Puoi eseguire una query di attraversamento dall'alto verso il basso per visualizzare l'intera rete della catena di fornitura. In un notebook standard o in un'interfaccia utente che lo supporta (ad esempio %%bigquery --graph), viene restituita una mappa visiva.

Utilizza le query di grafico assoluto per configurare nodi e bordi.

Nota: come accennato in precedenza, per eseguire questa operazione in Google Colab o nei notebook Colab Enterprise, puoi utilizzare anche i comandi magici di BigQuery: %%bigquery Inoltre, per visualizzare il grafico in Google Colab o nei notebook Colab Enterprise, includi il flag –graph come: %%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

Output:

7. Caso d'uso 1: tracciare un reclamo a monte

Scenario: un cliente si lamenta della qualità del pollo nel suo panino nel negozio di New York. Devi tracciare l'articolo finito all'indietro per visualizzare le fasi di assemblaggio immediate.

Query di attraversamento

Esegui la query utilizzando il formato della query di attraversamento del grafico. Questa query esamina i bordi consists_of che mettono in relazione gli assemblaggi a valle con gli ingredienti a monte.

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

A causa della direzione della freccia nella tabella dei bordi consists_of (Ingredient -> Finished), una ricerca che scorre a monte produce rapidamente link che isolano i materiali dipendenti e le posizioni di archiviazione.

Output:

8. Caso d'uso 2: analisi dell'impatto

Scenario: una tempesta di neve ha chiuso il centro di distribuzione di Columbus, Ohio. Devi sapere quali preparazioni o articoli finiti a valle sono immediatamente interessati.

Query di attraversamento

Inizia dalla location specifica che rappresenta il centro di distribuzione, identifica l'inventario archiviato e vedi quali articoli finiti lo richiedono.

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

Output:

9. Caso d'uso 3: richiamo a valle

Scenario: un fornitore ti comunica un lotto specifico di prodotto contaminato: pomodori maturati sulla pianta del fornitore. Devi trovare tutti gli articoli del menu finale interessati nei bar.

Query di attraversamento

Cerca la posizione della materia prima contaminata, quindi esegui un path traversal a valle per trovare gli articoli finali interessati.

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

Questa query individua tutti gli articoli che corrispondono al pattern "Tomato" e che sono interconnessi con la relazione a monte, il che la rende una mappatura potente che si propaga per scoprire quali articoli del bar devono essere richiamati.

Output:

10. Elimina

Elimina le risorse al termine dei passaggi della procedura dettagliata per evitare addebiti residui nello spazio di lavoro.

DROP SCHEMA `restaurant` CASCADE;

11. Conclusione

Complimenti! Hai modellato una catena di fornitura ed eseguito l'analisi dell'impatto utilizzando BigQuery Graph.

Conclusione

Hai imparato a:

1. Dichiarare le relazioni relazionali incentrate sul grafico con chiavi primarie/esterne.
2. Creare un grafico delle proprietà unificato.
3. Navigare in modo efficiente nelle relazioni tra più nodi utilizzando la logica di attraversamento delle query di grafico.

Per ulteriori informazioni sull'architettura dei grafici, consulta la documentazione di Google Cloud.