Отслеживание цепочки поставок с помощью 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 — полностью оборудованной среде, в которой есть все необходимое для работы с Google Cloud.

  1. Перейдите в консоль Google Cloud .
  2. Нажмите на значок «Активировать Cloud Shell» на панели инструментов в правом верхнем углу.
  3. При появлении соответствующего запроса нажмите «Продолжить» .

Настройка переменных среды

В Cloud Shell укажите идентификатор проекта, чтобы упростить выполнение команд в будущем.

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

Включить API BigQuery

Убедитесь, что API BigQuery включен. Обычно он включен по умолчанию, но лучше перестраховаться.

gcloud services enable bigquery.googleapis.com

3. Создание схемы и таблиц

Вы создадите набор данных и таблицы, представляющие компоненты вашей цепочки поставок:

  • item : Общее определение товара (например, помидор, курица).
  • location : Объекты инфраструктуры (поставщики, распределительные центры, кафе).
  • itemlocation : Таблица пересечения, представляющая места хранения запасов.
  • bom : Спецификация материалов (определяет весовые соотношения, например, изделие A входит в изделие B).
  • makes : Сопоставляет itemlocation с самим item .
  • stored_at : Сопоставляет itemlocation с location .

Создать набор данных

Вы можете выполнять команды SQL в этой лабораторной работе, используя либо Cloud Shell , либо консоль BigQuery .

Чтобы использовать консоль BigQuery:

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

Редактор BigQuery

Выполните следующую команду в Cloud Shell или используйте консоль BigQuery для создания схемы. В вашем SQL-запросе будут использоваться переменные Node.js.

Схема данных 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: 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, если запускаете 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: Отслеживание жалобы от вышестоящего звена.

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

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

Выполните запрос, используя формат запроса «Обход графа». Он анализирует ребра 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

Этот запрос находит все элементы, которые соответствуют шаблону «Помидор» и которые связаны с исходными данными, что делает его мощным инструментом сопоставления, позволяющим определить, какие позиции в кафе необходимо отозвать.

Выход: Плохие помидоры: последствия для производственных процессов

10. Уборка

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

DROP SCHEMA `restaurant` CASCADE;

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

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

Заворачивать

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

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

Для получения более подробной информации об архитектуре графов посетите документацию Google Cloud.