电商离线数仓项目实战（下）

最新推荐文章于 2024-04-08 21:36:18 发布

taoshengyijiu_

最新推荐文章于 2024-04-08 21:36:18 发布

阅读量634

点赞数 1

分类专栏：笔记文章标签： hive 数据仓库 hadoop spark kafka

本文链接：https://blog.csdn.net/taoshengyijiu_/article/details/120261396

版权

电商离线数仓项目实战（下）

电商分析——核心交易

文章目录

电商离线数仓项目实战（下）
- 电商分析——核心交易

一、业务需求

选取指标：订单数、商品数、支付金额，并对这些指标按销售区域、商品类型进行分析。

二、业务数据库表结构

1. 数据库表之间的联系

2. 业务数据库——数据源

交易订单表（trade_orders）
订单产品表（order_product）
产品信息表（product_info）
产品分类表（product_category）
商家店铺表（shops）
地域组织表（shop_admin_org）
支付方式表（payments）

3. 数据库表结构设计

3.1 交易订单表

DROP TABLE IF EXISTS lagou_trade_orders;
CREATE TABLE `lagou_trade_orders` (
	`orderId` bigint(11) NOT NULL AUTO_INCREMENT COMMENT '订单 id',
	`orderNo` varchar(20) NOT NULL COMMENT '订单编号',
	`userId` bigint(11) NOT NULL COMMENT '用户id',
	`status` tinyint(4) NOT NULL DEFAULT '-2' COMMENT '订单状态 -3:用户拒收 -2:未付款的订单 -1：用户取消 0:待发货 1:配送中 2:用户确认收 货',
	`productMoney` decimal(11, 2) NOT NULL COMMENT '商品金额',
	`totalMoney` decimal(11, 2) NOT NULL COMMENT '订单金额（包括运 费）',
	`payMethod` tinyint(4) NOT NULL DEFAULT '0' COMMENT '支付方 式,0:未知;1:支付宝，2：微信;3、现金；4、其他',
	`isPay` tinyint(4) NOT NULL DEFAULT '0' COMMENT '是否支付 0:未 支付 1:已支付',
	`areaId` int(11) NOT NULL COMMENT '区域最低一级',
	`tradeSrc` tinyint(4) NOT NULL DEFAULT '0' COMMENT '订单来源 0:商城 1:微信 2:手机版 3:安卓App 4:苹果App',
	`tradeType` int(11) DEFAULT '0' COMMENT '订单类型',
	`isRefund` tinyint(4) NOT NULL DEFAULT '0' COMMENT '是否退款 0:否 1：是',
	`dataFlag` tinyint(4) NOT NULL DEFAULT '1' COMMENT '订单有效标 志 -1：删除 1:有效',
	`createTime` varchar(25) NOT NULL COMMENT '下单时间',
	`payTime` varchar(25) DEFAULT NULL COMMENT '支付时间',
	`modifiedTime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP  COMMENT '订单更新时间',
	PRIMARY KEY (`orderId`)
) ENGINE = InnoDB AUTO_INCREMENT = 355 CHARSET = utf8;

备注：

记录订单的信息

status：订单状态

createTime、payTime、modifiedTime。创建时间、支付时间、修改时间

3.2 订单产品表

DROP TABLE IF EXISTS lagou_order_produc;
CREATE TABLE `lagou_order_product` (
	`id` bigint(11) NOT NULL AUTO_INCREMENT,
	`orderId` bigint(11) NOT NULL COMMENT '订单id',
	`productId` bigint(11) NOT NULL COMMENT '商品id',
	`productNum` bigint(11) NOT NULL DEFAULT '0' COMMENT '商品数 量',
	`productPrice` decimal(11, 2) NOT NULL DEFAULT '0.00' COMMENT '商品价格',
	`money` decimal(11, 2) DEFAULT '0.00' COMMENT '付款金额',
	`extra` text COMMENT '额外信息',
	`createTime` varchar(25) DEFAULT NULL COMMENT '创建时间',
	PRIMARY KEY (`id`),
	KEY `orderId` (`orderId`),
	KEY `goodsId` (`productId`)
) ENGINE = InnoDB AUTO_INCREMENT = 1260 CHARSET = utf8;

备注：

记录订单中购买产品的信息，包括产品的数量、单价等

3.3 产品信息表

DROP TABLE IF EXISTS lagou_product_info;
CREATE TABLE `lagou_product_info` (    
    `productId` bigint(11) NOT NULL AUTO_INCREMENT COMMENT '商品 id',    
    `productName` varchar(200) NOT NULL COMMENT '商品名称',    
    `shopId` bigint(11) NOT NULL COMMENT '门店ID',    
    `price` decimal(11, 2) NOT NULL DEFAULT '0.00' COMMENT '门店 价',    
    `isSale` tinyint(4) NOT NULL DEFAULT '1' COMMENT '是否上架 0:不上架 1:上架',    
    `status` tinyint(4) NOT NULL DEFAULT '0' COMMENT '是否新品 0:否 1:是',    
    `categoryId` int(11) NOT NULL COMMENT 'goodsCatId 最后一级商品 分类ID',    
    `createTime` varchar(25) NOT NULL,    
    `modifyTime` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间',    
    PRIMARY KEY (`productId`),    
    KEY `shopId` USING BTREE (`shopId`),    
    KEY `goodsStatus` (`isSale`)
) ENGINE = InnoDB AUTO_INCREMENT = 115909 CHARSET = utf8;

备注：

记录产品的详细信息，对应商家 ID、商品属性（是否新品，是否上架）

3.4 产品分类表

DROP TABLE IF EXISTS lagou_product_category;
CREATE TABLE `lagou_product_category` (
	`catId` int(11) NOT NULL AUTO_INCREMENT COMMENT '品类ID',
	`parentId` int(11) NOT NULL COMMENT '父ID',
	`catName` varchar(20) NOT NULL COMMENT '分类名称',
	`isShow` tinyint(4) NOT NULL DEFAULT '1' COMMENT '是否显示 0:隐藏 1:显示',
	`sortNum` int(11) NOT NULL DEFAULT '0' COMMENT '排序号',
	`isDel` tinyint(4) NOT NULL DEFAULT '1' COMMENT '删除标志 1:有 效 -1：删除',
	`createTime` varchar(25) NOT NULL COMMENT '建立时间',
	`level` tinyint(4) DEFAULT '0' COMMENT '分类级别，共3级',
	PRIMARY KEY (`catId`),
	KEY `parentId` (`parentId`, `isShow`, `isDel`)
) ENGINE = InnoDB AUTO_INCREMENT = 10442 CHARSET = utf8;

备注：

产品分类表，共分为三个级别

-- 第一级产品目录
select catName, catid from lagou_product_category where level =1;
-- 查看电脑、办公的子类（查看二级目录）
select catName, catid from lagou_product_category where level =2 and parentId = 32;
-- 查看电脑整机的子类（查看三级目录）
select catName, catid from lagou_product_category where level =3 and parentId = 10250;

3.5 商家店铺表

DROP TABLE IF EXISTS lagou_shops;
CREATE TABLE `lagou_shops` (
	`shopId` int(11) NOT NULL AUTO_INCREMENT COMMENT '商铺ID，自 增',
	`userId` int(11) NOT NULL COMMENT '商铺联系人ID',
	`areaId` int(11) DEFAULT '0',
	`shopName` varchar(100) DEFAULT '' COMMENT '商铺名称',
	`shopLevel` tinyint(4) NOT NULL DEFAULT '1' COMMENT '店铺等 级',
	`status` tinyint(4) NOT NULL DEFAULT '1' COMMENT '商铺状态',
	`createTime` date DEFAULT NULL,
	`modifyTime` datetime DEFAULT NULL COMMENT '修改时间',
	PRIMARY KEY (`shopId`),
	KEY `shopStatus` (`status`)
) ENGINE = InnoDB AUTO_INCREMENT = 105317 CHARSET = utf8;

备注：

记录店铺的详细信息

3.6 地域组织表

DROP TABLE IF EXISTS lagou_shops;
CREATE TABLE `lagou_shop_admin_org` (
	`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '组织ID',
	`parentId` int(11) NOT NULL COMMENT '父ID',
	`orgName` varchar(100) NOT NULL COMMENT '组织名称',
	`orgLevel` tinyint(4) NOT NULL DEFAULT '1' COMMENT '组织级别 1;总部及大区级部门;2：总部下属的各个部门及基部门;3:具体工作部门',
	`isDelete` tinyint(4) NOT NULL DEFAULT '0' COMMENT '删除标 志,1:删除;0:有效',
	`createTime` varchar(25) DEFAULT NULL COMMENT '创建时间',
	`updateTime` varchar(25) DEFAULT NULL COMMENT '最后修改时间',
	`isShow` tinyint(4) NOT NULL DEFAULT '1' COMMENT '是否显示,0: 是 1:否',
	`orgType` tinyint(4) NOT NULL DEFAULT '1' COMMENT '组织类 型,0:总裁办;1:研发;2:销售;3:运营;4:产品',
	PRIMARY KEY (`id`),
	KEY `parentId` (`parentId`)
) ENGINE = InnoDB AUTO_INCREMENT = 100332 CHARSET = utf8;

备注：

记录店铺所属区域

3.7 支付方式表

DROP TABLE IF EXISTS lagou_payments;
CREATE TABLE `lagou_payments` (
	`id` int(11) NOT NULL,
	`payMethod` varchar(20) DEFAULT NULL,
	`payName` varchar(255) DEFAULT NULL,
	`description` varchar(255) DEFAULT NULL,
	`payOrder` int(11) DEFAULT '0',
	`online` tinyint(4) DEFAULT NULL,
	PRIMARY KEY (`id`),
	KEY `payCode` (`payMethod`)
) ENGINE = InnoDB CHARSET = utf8;

备注：

记录支付方式

三、数据导入

MYSQL 导出：

全量导出
增量导出（导出前一天的数据）

业务数据保存在MySQL中，每日凌晨导入上一天的表数据。

表数据量少，采用全量方式导出 MySQL
表数据量大，而且根据字段能区分出每天新增数据，采用增量方式导出MySQL。
三张增量表
- 订单表 lagou_trade_orders
- 订单产品表 lagou_order_product
- 产品信息表 lagou_product_info
四张全量表
- 产品分类表 lagou_product_category
- 商家店铺表 lagou_shops
- 商家地域组织表 lagou_shop_admin_org
- 支付方式表 lagou_payment

3.1 全量数据导入

MYSQL => HDFS => Hive

每日加载全量数据，形成新的分区；（对 ODS 如何建表有指导作用）

MySQLReader =====> HdfsReader

ebiz.lagou_product_category ===> ods.ods_trade_product_category

3.1.1 产品分类表

使用 DataX 导出时，需要进行 json 文件的编写。

/root/data/lagoudw/json/product_category.json

{
   
	"job": {
   
		"setting": {
   
			"speed": {
   
				"channel": 1
			}
		},
		"content": [{
   
			"reader": {
   
				"name": "mysqlreader",
				"parameter": {
   
					"username": "root",
					"password": "12345678",
					"column": [
						"catId",
						"parentId",
						"catName",
						"isShow",
						"sortNum",
						"isDel",
						"createTime",
						"level"
					],
					"connection": [{
   
						"table": [
							"lagou_product_category"
						],
						"jdbcUrl": [
							"jdbc:mysql://linux123:3306/ebiz"
						]
					}]
				}
			},
			"writer": {
   
				"name": "hdfswriter",
				"parameter": {
   
					"defaultFS": "hdfs://linux121:9000",
					"fi

最低0.47元/天解锁文章

taoshengyijiu_

关注

1
点赞
踩
7

收藏

觉得还不错? 一键收藏
0
评论
电商离线数仓项目实战（下）

电商离线数仓项目实战（下）电商分析——核心交易文章目录电商离线数仓项目实战（下）电商分析——核心交易一、业务需求二、业务数据库表结构1. 数据库表之间的联系img2. 业务数据库——数据源3. 数据库表结构设计3.1 交易订单表3.2 订单产品表3.3 产品信息表3.4 产品分类表3.5 商家店铺表3.6 地域组织表3.7 支付方式表三、数据导入3.1 全量数据导入3.1.1 产品分类表3.1.2 商家店铺表3.1.3 商家地域组织表3.1.4 支付方式表3.2 增量数据导入3.2.1 订单表3.2.
复制链接

扫一扫