使用 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 文档。

使用 BigQuery Graph 实现供应链可追溯性 使用集合让一切井井有条 根据您的偏好保存内容并对其进行分类。

1. 简介

学习内容

所需条件

费用估算值

2. 设置和要求

打开 Cloud Shell

设置环境变量

启用 BigQuery API

3. 创建架构和表

创建数据集

创建表

4. 加载示例数据

5. 添加限制条件和定义图表

创建属性图表

6. 直观呈现供应链

7. 使用情形 1：跟踪上游投诉

遍历查询

8. 应用场景 2：影响分析

遍历查询

9. 使用情形 3：下游召回

遍历查询

10. 清理

11. 总结

小结

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