使用 BigQuery Graph 实现供应链可追溯性

1. 简介

在此 Codelab 中，您将学习如何利用 BigQuery 图来解决复杂的供应链和物流问题。

您将构建一个以食品安全和质量控制为重点的餐厅供应链网络模型。当出现食品安全问题（例如供应商提供的食材受到污染）时，时间至关重要。确定“影响范围”并快速执行精准召回可以节省成本并保护客户。

餐厅食品安全恐慌

传统的关系模型需要复杂的、多步骤的 JOIN 操作才能跟踪多个阶段（供应商 -> 配送中心 -> 食品加工中心 -> 商店 -> 成品）中的商品。借助 BigQuery Graph，我们可以直接对这些关联进行建模，从而使用 ISO GQL（Graph Query Language）标准实现直观快速的查询。

学习内容

如何基于现有 BigQuery 表定义图模型。
如何在 BigQuery 中创建属性图。
如何运行遍历查询来跟踪上游和下游影响。

所需条件

启用了结算功能的 Google Cloud 项目。
Google Cloud Shell。

费用估算值

本实验预计会产生不到 5 美元的 BigQuery 分析费用，远低于新用户的免费层级分配额度。

2. 设置和要求

打开 Cloud Shell

您将在 Cloud Shell 中完成大部分工作，这是一个预加载的环境，其中包含使用 Google Cloud 所需的一切。

前往 Google Cloud 控制台。
点击右上角工具栏中的激活 Cloud Shell 图标。
如果出现提示，请点击继续。

设置环境变量

在 Cloud Shell 中，设置项目 ID 以简化未来的命令。

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

启用 BigQuery API

确保已启用 BigQuery API。此功能通常默认处于启用状态，但最好还是确认一下。

gcloud services enable bigquery.googleapis.com

3. 创建架构和表

您将创建一个数据集和一些表格来表示供应链组件：

item：一般商品定义（例如，西红柿、鸡肉）。
location：设施（供应商、配送中心、咖啡馆）。
itemlocation：表示库存位置的交集表。
bom：物料清单（定义重量关系，例如，物料 A 进入物料 B）。
makes：将 itemlocation 映射到 item。
stored_at：将 itemlocation 映射到 location。

创建数据集

您可以使用 Cloud Shell 或 BigQuery 控制台运行本实验中的 SQL 命令。

如需使用 BigQuery 控制台，请执行以下操作：

在新标签页中打开 BigQuery 控制台。
将本实验中的每个 SQL 代码段粘贴到编辑器中，然后点击运行按钮以执行该代码段。

BigQuery 编辑器

在 Cloud Shell 中运行以下命令，或使用 BigQuery 控制台创建架构。您将在 SQL 中使用节点变量。

BigQuery 数据架构

注意：(1) 如需在 Google Colab 中执行此操作，您还可以使用 BigQuery magic 命令：%%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

Colab 中的 BigQuery 魔法命令

%%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 语句填充包含示例数据的表。此图表示一个从供应商开始，经过配送中心 (DC) 和中央厨房，最终到达零售咖啡馆的网络。

运行以下 SQL 查询以加载数据：

BigQuery 数据加载

注意：如果您直接在 BigQuery 工作室中运行，则可以省略 %%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 笔记本中执行此操作，您还可以使用 BigQuery magic 命令：%%bigquery。此外，若要在 Google Colab 或 Colab Enterprise 笔记本中直观呈现图表，请添加“–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

此查询会查找与“Tomato”模式匹配的所有商品，并与上游关系交织在一起，从而形成强大的映射，用于传播以发现必须召回哪些咖啡馆商品。

输出：不良番茄的下游影响

10. 清理

完成演练步骤后，请删除资源，以免工作区中产生任何剩余费用。

DROP SCHEMA `restaurant` CASCADE;

11. 总结

恭喜！您已使用 BigQuery Graph 对供应链进行了建模并执行了影响分析。

小结

您已学会：

使用主键/外键声明以图为中心的关系型关系。
创建统一的属性图表。
使用图表查询遍历逻辑高效浏览多节点关系。

如需深入了解图架构，请访问 Google Cloud 文档。