Śledzenie łańcucha dostaw za pomocą BigQuery Graph

1. Wprowadzenie

W tym ćwiczeniu z programowania dowiesz się, jak wykorzystać BigQuery Graph do rozwiązywania złożonych problemów związanych z łańcuchem dostaw i logistyką.

Będziesz modelować sieć łańcucha dostaw restauracji, koncentrując się na bezpieczeństwie żywności i kontroli jakości. Gdy pojawi się problem z bezpieczeństwem żywności, np. zanieczyszczony składnik od dostawcy, czas jest najważniejszy. Szybkie określenie „promienia rażenia” i przeprowadzenie precyzyjnego wycofania produktu może pomóc zaoszczędzić koszty i chronić klientów.

Tradycyjne modele relacyjne wymagają złożonych, wieloetapowych operacji JOIN, aby śledzić produkty na różnych etapach (dostawca -> centrum dystrybucji -> magazyn -> sklep -> produkt gotowy). W BigQuery Graph modelujemy te połączenia bezpośrednio, co umożliwia intuicyjne i szybkie wykonywanie zapytań za pomocą standardu ISO GQL (Graph Query Language).

Czego się nauczysz

• Jak zdefiniować model grafu na podstawie istniejących tabel BigQuery.
• Jak utworzyć graf właściwości w BigQuery.
• Jak uruchamiać zapytania dotyczące przechodzenia, aby śledzić wpływ na dalsze i wcześniejsze etapy.

Czego potrzebujesz

• Projekt Google Cloud z włączonymi płatnościami.
• Google Cloud Shell.

Szacowane koszty

Szacujemy, że to laboratorium będzie kosztować mniej niż 5 USD w opłatach za analizę w BigQuery, co mieści się w limitach poziomu bezpłatnego dla nowych użytkowników.

2. Konfiguracja i wymagania

Otwórz Cloud Shell

Większość pracy wykonasz w Cloud Shell, czyli w środowisku z preinstalowanymi narzędziami, które zawiera wszystko, czego potrzebujesz do korzystania z Google Cloud.

1. Otwórz konsolę Google Cloud.
2. Na pasku narzędzi w prawym górnym rogu kliknij ikonę Aktywuj Cloud Shell.
3. Jeśli pojawi się prośba, kliknij Dalej.

Konfigurowanie zmiennych środowiskowych

W Cloud Shell ustaw identyfikator projektu, aby uprościć przyszłe polecenia.

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

Włącz interfejsy API

Aby włączyć wymagany interfejs BigQuery API, uruchom to polecenie:

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

Tworzenie rezerwacji i przypisania BigQuery

Aby uruchamiać zapytania GQL, musisz mieć rezerwację, która korzysta z wersji Enterprise lub Enterprise Plus. Utwórz rezerwację w wersji Enterprise z autoskalowaniem w 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}

Oszczędne autoskalowanie zasobów obliczeniowych:
skonfiguruj --slots=0 (0 przedziałów podstawowych) i --autoscale_max_slots=100. Dzięki temu rezerwacja BigQuery nie będzie generować żadnych kosztów podstawowych, gdy nie będzie używana. BigQuery automatycznie skaluje się do 100 dedykowanych przedziałów tylko wtedy, gdy aktywnie działają zapytania lub wyszukiwania wektorowe, a po zakończeniu wraca do 0.

3. Tworzenie schematu i tabel

Utworzysz zbiór danych i tabele reprezentujące komponenty łańcucha dostaw:

• item: ogólna definicja produktu (np. pomidor, kurczak).
• location: obiekty (dostawcy, centra dystrybucji, kawiarnie).
• itemlocation: tabela przecięcia reprezentująca lokalizacje zapasów.
• bom: Lista materiałów (BoM) (określa relacje wagowe, np. produkt A wchodzi w skład produktu B).
• makes: mapuje itemlocation na item.
• stored_at: mapuje itemlocation na location.

Utwórz zbiór danych

Polecenia SQL w tym ćwiczeniu z programowania możesz uruchamiać w Cloud Shell lub w konsoli BigQuery.

Aby użyć konsoli BigQuery:

1. Otwórz konsolę BigQuery w nowej karcie.
2. Wklej każdy fragment kodu SQL z tego ćwiczenia z programowania do edytora, a następnie kliknij przycisk Uruchom , aby go wykonać.

Aby utworzyć schemat, uruchom to polecenie w Cloud Shell lub użyj konsoli BigQuery. W kodzie SQL będziesz używać zmiennych węzłów.

Uwaga: (1) Aby wykonać to w Google Colab, możesz też użyć poleceń magicznych BigQuery: %%bigquery Poniższy fragment kodu tworzy w Twoim projekcie schemat restauracji, w którym będą przechowywane dane grafu. (2) Jeśli uruchamiasz kod w Google Colab, musisz użyć %%bigquery –project <PROJECT_ID>. Upewnij się, że pole PROJECT_ID jest mapowane na odpowiedni projekt, którego chcesz użyć: PROJECT_ID = "argolis-project-340214" # @param {"type":"string"} (3) Jeśli używasz Colab, w zależności od wymagań musisz zainstalować niektóre biblioteki. Jeśli zamierzasz używać wizualizacji grafu, zainstaluj bibliotekę: spanner-graph-notebook==1.1.5.

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

Tworzenie tabel

Aby utworzyć tabele, wykonaj ten kod 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. Wczytywanie przykładowych danych

Aby to ćwiczenie z programowania było w pełni samodzielne, wypełnisz tabele przykładowymi danymi za pomocą instrukcji LOAD DATA w czystym SQL. Reprezentuje to sieć zaczynającą się od dostawcy, przechodzącą przez centrum dystrybucji i kuchnię, a kończącą się w kawiarni.

Aby wczytać dane, uruchom te zapytania SQL:

Uwaga: jeśli uruchamiasz kod bezpośrednio w BigQuery Studio, możesz pominąć %%bigquery.

%%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. Dodawanie ograniczeń i definiowanie grafu

Zanim utworzysz graf, zadeklaruj relacje semantyczne za pomocą standardowych ograniczeń SQL Primary Key i Foreign Key. Pomagają one BigQuery w zrozumieniu identyfikatorów węzłów i łączeniu tabel krawędzi z tabelami węzłów.

Utwórz graf właściwości

Teraz połącz te tabele w jedną spójną strukturę grafu o nazwie restaurant.bombod.

Zdefiniujesz:

• Węzły: item, location, itemlocation
• Krawędzie: makes, stored_at i consists_of (zestawienie materiałów)

%%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. Wizualizacja łańcucha dostaw

Aby zobaczyć całą sieć łańcucha dostaw, możesz uruchomić zapytanie dotyczące przechodzenia od góry do dołu. W standardowym notatniku lub interfejsie, który obsługuje tę funkcję (np. %%bigquery --graph), zwraca to mapę wizualną.

Aby skonfigurować węzły i krawędzie, użyj zapytań dotyczących grafu bezwzględnego.

Uwaga: jak wspomnieliśmy wcześniej, aby wykonać to w Google Colab lub notatnikach Colab Enterprise, możesz też użyć poleceń magicznych BigQuery: %%bigquery Aby wizualizować graf w Google Colab lub notatnikach Colab Enterprise, dodaj też flagę –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

Dane wyjściowe:

7. Przypadek użycia 1. Śledzenie skargi na wcześniejszym etapie

Scenariusz: klient skarży się na jakość kurczaka w kanapce w sklepie w Krakowie. Musisz prześledzić produkt gotowy wstecz, aby zobaczyć jego bezpośrednie etapy montażu.

Zapytanie dotyczące przechodzenia

Uruchom zapytanie w formacie zapytania dotyczącego przechodzenia po grafie. Sprawdza ono krawędzie consists_of, które łączą zespoły na dalszych etapach z wcześniejszymi składnikami.

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

Ze względu na kierunek strzałki w tabeli krawędzi consists_of (Ingredient -> Finished) wyszukiwanie w kierunku wcześniejszych etapów szybko znajduje powiązane materiały i lokalizacje przechowywania.

Dane wyjściowe:

8. Przypadek użycia 2. Analiza wpływu

Scenariusz: burza śnieżna spowodowała zamknięcie centrum dystrybucji w Columbus w stanie Ohio. Musisz wiedzieć, które przygotowania na dalszych etapach lub produkty gotowe są bezpośrednio dotknięte.

Zapytanie dotyczące przechodzenia

Zacznij od konkretnej location reprezentującej centrum dystrybucji, zidentyfikuj przechowywane tam zapasy i sprawdź, które produkty gotowe ich wymagają.

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

Dane wyjściowe:

9. Przypadek użycia 3. Wycofanie produktu na dalszych etapach

Scenariusz: dostawca informuje o konkretnej partii zanieczyszczonego produktu: pomidorach dojrzewających w słońcu. Musisz znaleźć wszystkie produkty w menu kawiarni, których to dotyczy.

Zapytanie dotyczące przechodzenia

Wyszukaj lokalizację zanieczyszczonego surowca, a następnie wykonaj [atak typu] path traversal w kierunku dalszych etapów, aby znaleźć produkty, których to dotyczy.

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

To zapytanie znajduje wszystkie produkty, które pasują do wzorca „Tomato” i są powiązane z relacją na wcześniejszym etapie, co sprawia, że jest to skuteczne mapowanie, które pozwala odkryć, które produkty w kawiarni należy wycofać.

Dane wyjściowe:

10. Czyszczenie

Po wykonaniu wszystkich czynności usuń zasoby, aby uniknąć dodatkowych opłat w obszarze roboczym.

DROP SCHEMA `restaurant` CASCADE;

11. Podsumowanie

Gratulacje! Udało Ci się modelować łańcuch dostaw i przeprowadzić analizę wpływu za pomocą BigQuery Graph.

Podsumowanie

Wiesz już:

1. Jak deklarować relacje relacyjne skoncentrowane na grafie za pomocą kluczy podstawowych i obcych.
2. Jak utworzyć ujednolicony graf właściwości.
3. Jak wydajnie poruszać się po relacjach między wieloma węzłami za pomocą logiki przechodzenia zapytań dotyczących grafu.

Więcej informacji o architekturze grafu znajdziesz w dokumentacji Google Cloud.