Отслеживание цепочек поставок с помощью BigQuery Graph

1. Введение

В этой практической работе вы узнаете, как использовать BigQuery Graph для решения сложных задач в сфере логистики и цепей поставок.

Вы смоделируете сеть поставок ресторана, сосредоточившись на безопасности пищевых продуктов и контроле качества. Когда возникает проблема с безопасностью пищевых продуктов, например, загрязненный ингредиент от поставщика, время имеет решающее значение. Определение "радиуса поражения" и быстрое проведение хирургического отзыва могут сэкономить средства и защитить клиентов.

Опасная еда в ресторане

Традиционные реляционные модели требуют сложных многоэтапных операций JOIN для отслеживания товаров на нескольких этапах (поставщик -> распределительный центр -> магазин -> готовый товар). BigQuery Graph позволяет моделировать эти связи напрямую, обеспечивая интуитивно понятные и быстрые запросы с использованием стандарта ISO GQL (Graph Query Language).

Что вы узнаете

  • Как определить модель графа на основе существующих таблиц BigQuery.
  • Как создать граф свойств в BigQuery.
  • Как выполнять запросы обхода, чтобы отслеживать влияние на вышестоящие и нижестоящие ресурсы.

Что вам понадобится

  • Проект Google Cloud с включенными платежными функциями.
  • Google Cloud Shell.

Расчетная стоимость

Ожидается, что стоимость анализа BigQuery в этой лабораторной работе составит менее 5 долларов США, что вполне укладывается в рамки бесплатного плана для новых пользователей.

2. Настройка и требования

Открыть Cloud Shell

Большую часть работы вы будете выполнять в Cloud Shell – среде, в которой есть все необходимое для использования Google Cloud.

  1. Войдите в Google Cloud Console.
  2. Нажмите на значок Activate Cloud Shell (Активировать 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-команды в этой лабораторной работе, используя Cloud Shell или BigQuery Console.

Чтобы использовать консоль BigQuery, выполните следующие действия:

  1. Откройте консоль BigQuery в новой вкладке.
  2. Вставьте каждый фрагмент SQL-кода из этой лабораторной работы в редактор, а затем нажмите кнопку Выполнить, чтобы запустить его.

Редактор BigQuery

Чтобы создать схему, выполните следующую команду в Cloud Shell или используйте BigQuery Console. В 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 в 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. Загрузка образца данных

Чтобы сделать эту лабораторную работу полностью автономной, вы заполните таблицы образцами данных, используя чистые инструкции SQL LOAD DATA. Это сеть, которая начинается с поставщика, проходит через распределительный центр и кухню и заканчивается в кафе.

Чтобы загрузить данные, выполните следующие SQL-запросы:

Загрузка данных BigQuery

Примечание. Если вы работаете непосредственно в BigQuery Studio, то можете опустить %%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. Добавление ограничений и определение графика

Перед созданием графа вы объявляете семантические отношения, используя стандартные ограничения первичного и внешнего ключей 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 (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. Отзыв последующих версий

Сценарий. Поставщик уведомляет вас о конкретной партии зараженного продукта: помидоры, созревшие на лозе, от поставщика. Вам нужно найти все затронутые конечные пункты меню в кафе.

Запрос на обход

Вы ищете местоположение загрязненного сырья, а затем выполняете обход пути вниз по течению, чтобы найти конечные затронутые элементы.

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

Этот запрос находит все элементы, соответствующие шаблону Tomato, и связывает их с вышестоящими элементами. Это позволяет определить, какие продукты в кафе нужно отозвать.

Выходные данные: Bad Tomatoes Downstream Impact

10. Убирать

Чтобы избежать дополнительных расходов в рабочей области, удалите ресурсы после выполнения всех шагов.

DROP SCHEMA `restaurant` CASCADE;

11. Заключение

Поздравляем! Вы смоделировали цепочку поставок и провели анализ влияния с помощью BigQuery Graph.

Итоги

Вы научились:

  1. Объявляйте реляционные отношения, ориентированные на граф, с помощью первичных и внешних ключей.
  2. Создайте единый граф свойств.
  3. Эффективно перемещайтесь по связям между несколькими узлами, используя логику обхода Graph Query.

Чтобы узнать больше об архитектуре графов, ознакомьтесь с документацией Google Cloud.