准实时数仓搭建指南：以仓储式会员商超为模拟场景

项目背景

项目目标：为 METRO 巴基斯坦店设计、落地并分析一个准实时数仓原型。

作为巴基斯坦最大的连锁超市之一，METRO 坐拥上万客流，因此实时分析顾客的购物行为至关重要。商铺可以在此基础上持续优化销售策略，例如针对不同产品门类设计更有效的促销活动。

为了切实落地这样的购物行为分析，需要构建一个准实时的数仓，并将数据源中的顾客交易信息即时反映到该数仓中。而要想成功构建这样一个准实时数仓，就需要实施准实时的 ETL（Extraction, Transformation, and Loading，数据的提取、转换和加载）。因为顾客生成的数据往往并不完整，无法满足数仓的数据要求，所以需要在 ETL 转换层中加以完善，像是从主数据（MD, Master Data）中富集一些信息，如上图所示。

实施步骤

1. 理解项目需求：上手项目的第一步无疑是彻底理解项目需求，包括目标、可交付成功和时间节点规划。必须详细了解项目概况、MESHJOIN 算法、星形模式和数据规范。

2. 设置开发环境：这一步涉及安装和配置必要的软件，如 Eclipse IDE 和 MySQL 数据库，并创建所需的项目文件夹和文件。

3. 实现 MESHJOIN 算法：本项目的核心是通过 Java 和 Eclipse IDE 来实现 MESHJOIN 算法，包括：编写代码将磁盘分区加载到内存中；将客户交易存储到哈希表中；将传入的数据组织成队列，以及探测匹配的元组以生成连接输出。

4. 设计星型模式：星型模式（Star Schema）用于将多维决策支持数据映射到关系型数据库中。在这一步中，我们根据项目需求设计了星型模式，包括识别事实、维度、属性和分类级别。我们将使用该模式创建多个聚合数据源，用以代表业务运营的不同方面

5. 创建并填充数据库：这一步需要创建一个 MySQL 数据库，并使用提供的 SQL 脚本创建用于交易数据和主数据的表。同时根据脚本提供的规范，填充表格数据。

6. 构建准实时数仓原型：借助 MESHJOIN 算法和星型模式，为 METRO 巴基斯坦店构建一个准实时的数仓原型。包括实施一个准实时的 ETL 工具，以便将数据源中产生的客户交易即时反映到数仓之中。需要对数仓原型进行全面测试，以确保其满足项目需求，并按照预期完成交付。

7. 分析数仓原型：对数仓原型进行分析，以评估其在优化商店销售策略方面的性能和效果。我们将使用数据可视化和报告等工具，从数仓中提取有价值的见解，为改进商店运营提供建议。

8. 完成项目并进行演示：最后一步是完成项目，包括文档编写、代码清理和演示准备。我们需要确保按时、高标准地完成所有成果的交付，并向所有利益相关方展示最终的项目成果。

MESHJOIN 算法

MESHJOIN（Mesh Join）算法由计算机科学家 Polyzotis 于 2008 年提出，旨在实现 ETL 转换阶段的流关系连接操作。

MESHJOIN 的主要组成部分包括：

• 磁盘缓冲区：本质是一个数组，用于从流关系中加载输入的流元组。
• 内存缓冲区：本质是一个哈希表，用于加载将要与输入流进行连接的关系中的元组。
• Mesh（网格）：是一种图形结构，用于将输入的流元组映射到关系缓冲区中可能与之连接的元组。
• 连接引擎：负责在输入的流元组与由 Mesh 映射到的关系元组之间执行实际连接操作。

这些组件共同构成了 MESHJOIN 算法的核心，用于实现流关系连接操作，以完成 ETL 过程中的数据转换。

MESHJOIN 算法的工作原理如下：

• 将输入的流元组加载到磁盘缓冲区。
• 将与流进行连接的关系元组加载到内存缓冲区。
• 通过将内存缓冲区中的关系元组哈希到相应的 Mesh 节点上，构建 Mesh 网格。
• 处理传入的流元组，并将其映射到 Mesh 节点上。
• 连接引擎使用 Mesh 来识别可与传入流元组连接的关系元组。
• 连接引擎在传入的流元组和识别出的关系元组之间执行连接操作。
• 将连接后的元组发送到输出端。

MESHJOIN 算法为在 ETL 转换过程中执行流关系连接操作提供了一种高效的解决方案。通过结合利用磁盘缓冲区、内存缓冲区、Mesh 网格和连接引擎，MESHJOIN 有效减少了 I/O 操作，提高了连接操作的性能。

星型模式

作为一种数据建模技术，星型模式在该项目中用于将多维决策支持数据映射到关系型数据库中。星型模式为多维数据分析提供了一个易于实施的模型，同时保留了作为操作型数据库基础的关系型结构。

星型模式表示特定业务活动的聚合数据。使用该模式，可以创建多个聚合数据源，代表业务运营的不同方面，例如不同的产品层级、地理位置、时间维度和客户类型。

对应地，为 METRO 超市设计的星型模式，将包括以下几个维度：。

• 时间：包括每笔交易的年、月、日、时、分、秒
• 产品：包括产品ID、名称、供应商 ID 和供应商名称。
• 客户：包括客户 ID 和姓名。
• 店铺：包括店铺 ID 和名称。

事实表将包含以下指标：

• 数量：每种产品的购买数量
• 价格：每种产品的价格
• 销售额：每笔交易的总销售金额（数量 × 价格）

数据增强

完成 ETL 和数据仓库分析之后，下一步就是通过添加额外信息来丰富数据，从而提高数据质量和可用性。数据增强阶段将执行以下步骤：‍

• ‍‍‍‍‍‍‍‍‍‍‍‍‍‍数据剖析，以识别数据质量问题和不一致之处。
• 数据清洗，以删除重复项、缺失值和不一致之处。
• 数据转换，以创建新特性或修改现有特性。
• ‍数据增强，以从外部来源添加新数据。
• 数据集成，以整合来自多个来源的数据

数据增强有助于发现隐藏的洞察力，提高数据准确性，增强决策能力。

‍

实施

| 以下说明将用于引导如何在本地机器上创建并运行项目副本，用于开发和测试。

首先需要在本地机器上安装 MySQL 和 Java。

此处下载 MySQL 最新版本：

https://www.mysql.com/downloads/

此处下载 Java 最新版本：

https://www.oracle.com/pk/java/technologies/javase/javase8-archive-downloads.html

将此 Git 存储库克隆到我们的本地机器上：

https://github.com/MuhammadAhmedSuhail/Near-Real-Time-DataWarehouse-Analysis.git

按照以下步骤运行项目：

1. 运行 Transactional_MasterData Generator.sql 来创建主数据。

2. 运行 createDW.sql 来创建数据仓库。

3. 运行 mj.java，运行时会要求输入数据库凭据，默认为数据库名称：db，用户名：root，密码："" 。该文件将在执行 meshjoin 后将数据填充到数据仓库中

4. 运行 queriesDW.sql，使用 OLAP 查询从数据仓库中提取的信息

5. 打开报告，查看项目概述、mesh-join 算法、该算法的缺陷以及本项目的学习成果。

数据仓库分析

数据加载到数据仓库后，下一步是对数据进行分析。项目的这一部分涉及应用 OLAP 查询来分析数仓中的数据。

在数仓中应用到的 OLAP 查询如下：

Query1：2017 年 9 月销售额最高的前3家店铺名称

此查询旨在确定 2017 年 9 月销售额最高的前 3 家店铺。查询的输出应显示店铺名称及其各自的销售额。

SELECT store.store_name,round(SUM(total_sale)) as revenue FROM sales 
JOIN store on store.store_id = sales.store_id 
JOIN date on date.time_id = sales.time_id where date.month = "september" 
and date.year = 2017 group by sales.store_id order by revenue desc LIMIT 3;

Query2：在周末创收最高的前 10 家供应商

此查询的目的是找出在周末产生最多收入的前 10 家供应商。

除此之外，查询还应说明我们将如何预测下一个周末的前十供应商。

SELECT supplier.supplier_name,round(SUM(total_sale)) as Revenue FROM sales 
JOIN date on date.time_id = sales.time_id 
JOIN supplier on supplier.supplier_id = sales.supplier_id where date.weekend = 1 
group by supplier.supplier_id order by Revenue desc LIMIT 10;

Query3：各供应商供应的所有产品的季度和月份总销售额

此查询旨在按季度和月份分别统计每个供应商供应的所有产品的总销售额。

查询结果应按供应商排列，并显示每个季度和月份的总销售额。

SELECT sales.product_id,product.product_name,date.month,date.quarter,SUM(total_sale) as Revenue 
FROM sales JOIN product on product.product_id = sales.product_id 
JOIN date on date.time_id = sales.time_id 
group by sales.product_id, date.quarter,date.msonth;

Query4：每家店铺销售的每种产品的总销售额

此查询旨在呈现每家店铺销售的每种产品各自的总销售额。

查询结果应按商店及产品分类，显示每家商店和每种产品的销售额。

SELECT store.store_name,product.product_name,round(SUM(total_sale)) as Revenue FROM sales 
JOIN store on store.store_id = sales.store_id JOIN product on product.product_id = sales.product_id 
group by sales.store_id,sales.product_id;

Query5：使用下钻查询（Drill Down Query）概念对所有店铺进行季度销售分析

此查询的目的是通过下钻查询概念，呈现所有店铺的季度销售分析。

查询的输出结果应显示每家店铺的季度销售额，同时可以通过逐级查询数据，查看每家店铺的月度销售额。

SELECT store.store_name,date.quarter,round(SUM(total_sale)) as Revenue FROM sales 
JOIN date on date.time_id = sales.time_id 
JOIN store on store.store_id = sales.store_id group by store.store_id,date.quarter;

Query6：周末最畅销的 5 种产品

此查询的目标是找出在周末销售成绩最漂亮的的前 5 种产品。

查询的输出结果应显示产品名称及其各自的销售额。

SELECT product.product_name,round(SUM(total_sale)) as Revenue FROM sales 
JOIN date on date.time_id = sales.time_id 
JOIN product on product.product_id = sales.product_id where date.weekend = 1 group by sales.product_id order by Revenue desc limit 5;

Query7：对店铺、供应商和产品执行 ROLLUP 操作

此查询涉及店铺、供应商和产品的 ROLLUP 操作。

查询结果应显示每家店铺、供应商和产品的总销售额。

SELECT store_id,supplier_id,product_id from sales
group by store_id,supplier_id,product_id with rollup;

Query8：2017 年上半年和下半年以及全年每种产品的总销售额

该查询旨在提取 2017 年上半年和下半年各产品的总销售额及其全年总销售额。

查询结果应显示每种产品在上下半年和全年的总销售额。

SELECT product.product_name,date.half_of_year,round(SUM(total_sale)) as Revenue FROM sales 
JOIN product on product.product_id = sales.product_id 
JOIN date on date.time_id = sales.time_id where date.year = 2017 group by sales.product_id, half_of_year;

Query9：在数仓数据集中查找异常值

此查询主要用于在数据仓库数据集中发现异常问题。

查询结果应显示异常值，并在项目报告中对此进行解释说明。

SELECT * from STORE_PRODUCT_ANALYSIS;

Query10：创建名为“STORE_PRODUCT_ANALYSIS”的物化视图

此查询将创建一个名为“STORE_PRODUCT_ANALYSIS”的物化视图，用于呈现店铺和产品的销售情况。

查询结果应先按店铺名称，后按产品名称排序。查询还应解释物化视图如何帮助优化 OLAP 查询。

CREATE TABLE `STORE_PRODUCT_ANALYSIS` AS 
SELECT store_name,product_name,total_sale FROM sales JOIN store on sales.store_id = store.store_id 
JOIN product on sales.product_id = product.product_id order by store_name,product_name;

所用技术

本项目用到了以下技术：

• Java：用于实现 MESHJOIN 算法和其他组件。
• Eclipse IDE：用于开发和测试 Java 代码。
• MySQL：用于存储和分析数据仓库。

MESH-JOIN 的局限性

1. 内存依赖严重，难以达到最佳效果。
2. 几乎要为每个元组搜索整个主数据，因此成本很高（不使用索引）。
3. 如果主数据中的表数量增加，则对资源不友好。

结论

在这个方案中，我们使用 Java 代码实现了 MESHJOIN 算法来构建数仓表。正如文中所述，这种方法可以快速获取分析数据和结果，但在处理大数据量时存在一些局限性，如对内存的高依赖以及缺乏索引支持。此外，当需要分析的数据表较多时，代码开发的成本也会很高。类似的能力可以参考新一代低代码数据平台 TapData 所提供的构建实时物化视图。

参照本文的指南，可以为任意一家商超落地一个准实时的数仓原型，并完成数仓分析。从而助力商超深入了解客户行为，优化销售策略，并改善整体业务运营规划。