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.
- Войдите в Google Cloud Console.
- Нажмите на значок Activate Cloud Shell (Активировать Cloud Shell) на панели инструментов в правом верхнем углу.
- При необходимости нажмите Продолжить.
Настройте переменные среды
В 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, выполните следующие действия:
- Откройте консоль BigQuery в новой вкладке.
- Вставьте каждый фрагмент SQL-кода из этой лабораторной работы в редактор, а затем нажмите кнопку Выполнить, чтобы запустить его.

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

Примечание. 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 --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 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, и связывает их с вышестоящими элементами. Это позволяет определить, какие продукты в кафе нужно отозвать.
Выходные данные: 
10. Убирать
Чтобы избежать дополнительных расходов в рабочей области, удалите ресурсы после выполнения всех шагов.
DROP SCHEMA `restaurant` CASCADE;
11. Заключение
Поздравляем! Вы смоделировали цепочку поставок и провели анализ влияния с помощью BigQuery Graph.
Итоги
Вы научились:
- Объявляйте реляционные отношения, ориентированные на граф, с помощью первичных и внешних ключей.
- Создайте единый граф свойств.
- Эффективно перемещайтесь по связям между несколькими узлами, используя логику обхода Graph Query.
Чтобы узнать больше об архитектуре графов, ознакомьтесь с документацией Google Cloud.