项目目标:为 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 算法的核心,用于实现流关系连接操作,以完成 ETL 过程中的数据转换。
MESHJOIN 算法为在 ETL 转换过程中执行流关系连接操作提供了一种高效的解决方案。通过结合利用磁盘缓冲区、内存缓冲区、Mesh 网格和连接引擎,MESHJOIN 有效减少了 I/O 操作,提高了连接操作的性能。
作为一种数据建模技术,星型模式在该项目中用于将多维决策支持数据映射到关系型数据库中。星型模式为多维数据分析提供了一个易于实施的模型,同时保留了作为操作型数据库基础的关系型结构。
星型模式表示特定业务活动的聚合数据。使用该模式,可以创建多个聚合数据源,代表业务运营的不同方面,例如不同的产品层级、地理位置、时间维度和客户类型。
对应地,为 METRO 超市设计的星型模式,将包括以下几个维度:。
事实表将包含以下指标:
数据增强有助于发现隐藏的洞察力,提高数据准确性,增强决策能力。
| 以下说明将用于引导如何在本地机器上创建并运行项目副本,用于开发和测试。
首先需要在本地机器上安装 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
按照以下步骤运行项目:
数据加载到数据仓库后,下一步是对数据进行分析。项目的这一部分涉及应用 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 算法来构建数仓表。正如文中所述,这种方法可以快速获取分析数据和结果,但在处理大数据量时存在一些局限性,如对内存的高依赖以及缺乏索引支持。此外,当需要分析的数据表较多时,代码开发的成本也会很高。类似的能力可以参考新一代低代码数据平台 TapData 所提供的构建实时物化视图。
参照本文的指南,可以为任意一家商超落地一个准实时的数仓原型,并完成数仓分析。从而助力商超深入了解客户行为,优化销售策略,并改善整体业务运营规划。