电商数仓 V2.0 （03 电商数据仓库系统）

Simon

2021-01-24

教程

电商数仓 V2.0 （03 电商数据仓库系统）

一、数仓分层

1.1 为什么要分层

把复杂问题简单化
- 将复杂的任务分解成多层来完成，每一层只处理简单的任务，方便定位问题
减少重复开发
- 规范数据分层，通过的中间层数据，能够减少极大的重复计算，增加一次计算结果的复用性
隔离原始数据
- 不论是数据的异常还是数据的敏感性，使真实数据与统计数据解耦开

常见分层方式：

1.2 数据集市与数据仓库

数据仓库：企业级别的，包含企业内所有的数据

数据集市：可以理解为部门级别的，归属于数据仓库

二、数仓理论简介

2.1 范式理论

定义

范式可以理解为设计一张数据表的表结构，符合的标准级别，规范和要求

优点

减少数据冗余
保持数据一致性（只改其中一张表就可以了）

缺点

难以应对大数据量的计算，因为会频繁涉及表之间的 join 操作

2.2 关系建模和维度建模

数据处理主要分类：

联机事务处理 OLTP（on-line transaction processing）
- 基于事务型的数据增删改查，如银行交易
联机分析处理 OLAP（On-Line Analytical Processing）
- 基于分析型的数据处理，如计算pv uv

对比属性	OLTP（如 MySQL）	OLAP（如 Hive）
读特性	每次查询只返回少量记录	对大量记录进行汇总
写特性	随机、低延时写入用户的输入	批量导入
使用场景	用户，Java EE项目	内部分析师，为决策提供支持
数据表征	最新数据状态	随时间变化的历史状态
数据规模	GB	TB到PB

2.2.1 关系建模

基本严格遵循三范式
表较为零碎
数据冗余低

2.2.2 维度建模

主要应用于OLAP系统中
通常以某一个事实表为中心进行表的组织，主要面向业务
存在数据的冗余，但是能方便的得到数据。
在大规模数据，跨表分析统计查询过程中，不需要多表关联，提升效率
把相关各种表整理成两种：事实表和维度表

分类

星型模型
- 在一个事实表周围只会围绕一圈维度表
- 冗余可能会多一点，但是减少了 join 能提升计算效率
雪花模型
- 相对于星型模型，事实表周围会有多层维度表
- 会更灵活一些
星座模型
- 在前两种上的扩展，如果多于一个事实表，就是星座模型

2.3 维度表和事实表

2.3.1 维度表

定义：

一般是对事实的描述信息。每一张维表对应现实世界中的一个对象或者概念。例如：用户、商品、日期、地区等

特征：

维表的范围很宽（具有多个属性、列比较多）
跟事实表相比，行数相对较小：通常< 10万条
内容相对固定：编码表

2.3.2 事实表

定义：

描述一个业务过程，如下单、支付、退款、评价等。通常包含着可统计的列，如金额、数量等。

特征：

非常的大
内容相对的窄：列数较少
经常发生变化，每天会新增加很多

事实表分类：

1）事务型事实表

以每个事务或事件为单位，例如一个销售订单记录，一笔支付记录等，作为事实表里的一行数据。一旦事务被提交，事实表数据被插入，数据就不再进行更改，其更新方式为增量更新。

2）周期型快照事实表

周期型快照事实表中不会保留所有数据，只保留固定时间间隔的数据，例如每天或者每月的销售额。对中间过程不敏感，只关心到一个时间点时的状态。

3）累积型快照事实表

累计快照事实表用于跟踪业务事实的变化。例如，数据仓库中可能需要累积或者存储订单从下订单开始，到订单商品被打包、运输、和签收的各个业务阶段的时间点数据来跟踪订单声明周期的进展情况。当这个业务过程进行时，事实表的记录也要不断更新。

2.4 数据仓库建模

2.4.1 ODS层

保存着最原始的数据，起到数据备份的作用
采用数据压缩，减少磁盘占用（压缩到 1/10 ~ 1/20）
创建分区表，避免之后全表扫描

原始表之间关系：

绿色：维度表
深色：事实表
白色：会进行维度退化的表

2.4.2 DWD层

1、在这里使用维度建模，一般采用星型模型，展现出来为星座模型

2、维度建模大致分为以下四个步骤：

选择业务过程 -> 声明粒度 -> 确认维度 -> 确认事实

选择业务过程
- 挑选感兴趣的业务线，一条业务线对应一张事实表
声明最小粒度
- 无论统计啥都能从这个粒度进行计算
确认维度
- 谁、何处、何时
- 维度表：会根据需求进行维度退化
确认事实
- 即确定「度量值」，个数、次数、件数等信息

3、维度建模到这里已完毕

4、DWD 层以业务过程为驱动，后面DWS、DWT则以需求为驱动

	时间	用户	地区	商品	优惠券	活动	度量值
订单	√	√	√			√	件数/金额
订单详情	√	√	√	√			件数/金额
支付	√	√	√				金额
加购	√	√		√			件数/金额
收藏	√	√		√			个数
评价	√	√		√			个数
退款	√	√		√			件数/金额
优惠券领用	√	√			√		个数

2.4.3 DWS层

主要作用：将维度表和关联的事实表进行组合（join），每个维度表生成一个大宽表，避免后续重复join计算

粒度：统计各主题对象当天的行为

每个表包含哪些字段：以维度表为基准，关联相关事实表的度量值（如下图）

以用户表为例：

2.4.4 DWT层

作用：统计事情从第一次发生至今的累积度量值

例：

2.4.5 ADS层

根据实际业务需要在前面几张表中进行统计，如 pv，uv，新增，留存等。

三、ODS 层搭建

保持数据原貌不做任何修改，起到备份数据的作用（即直接以一串string的原始格式存进来，不作任何解析）
数据采用LZO压缩，减少磁盘存储空间。100G数据可以压缩到10G以内。
创建分区表，防止后续的全表扫描，在企业开发中大量使用分区表。
创建外部表。在企业开发中，除了自己用的临时表，创建内部表外，绝大多数场景都是创建外部表。

3.1 创建数据库

1 2	> create database gmall; > use gmall;

3.2 用户行为数据

3.2.1 启动日志表

创建表

USE gmall;
DROP TABLE IF EXISTS ods_start_log;
CREATE EXTERNAL TABLE ods_start_log (`line` string)
PARTITIONED BY (`dt` string)
STORED AS
  INPUTFORMAT 'com.hadoop.mapred.DeprecatedLzoTextInputFormat'
  OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
LOCATION '/warehouse/gmall/ods/ods_start_log';

加载数据

load 操作是剪切操作，相当于从 hdfs 的一个路径，移动到另一个路径

1
2

load data inpath '/origin_data/gmall/log/topic_start/2020-03-10' into table gmall.ods_start_log partition(dt='2020-03-10');
-- 注意：时间格式都配置成YYYY-MM-DD格式，这是Hive默认支持的时间格式

查看数据

1	select * from ods_start_log where dt='2020-03-10' limit 2;

创建 lzo 索引

1	hadoop jar /opt/module/hadoop-2.7.2/share/hadoop/common/hadoop-lzo-0.4.20.jar com.hadoop.compression.lzo.DistributedLzoIndexer /warehouse/gmall/ods/ods_start_log/dt=2020-03-10

3.2.2 事件日志表

创建表

USE gmall;
DROP TABLE IF EXISTS ods_event_log;
CREATE EXTERNAL TABLE ods_event_log(`line` string)
PARTITIONED BY (`dt` string)
STORED AS
  INPUTFORMAT 'com.hadoop.mapred.DeprecatedLzoTextInputFormat'
  OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
LOCATION '/warehouse/gmall/ods/ods_event_log';

加载数据

1	load data inpath '/origin_data/gmall/log/topic_event/2020-03-10' into table gmall.ods_event_log partition(dt='2020-03-10');

查看数据

1	select * from ods_event_log where dt="2020-03-10" limit 2;

创建索引

1	hadoop jar /opt/module/hadoop-2.7.2/share/hadoop/common/hadoop-lzo-0.4.20.jar com.hadoop.compression.lzo.DistributedLzoIndexer /warehouse/gmall/ods/ods_event_log/dt=2020-03-10

3.2.3 数据导入脚本

hdfs_to_ods_log.sh

#!/bin/bash

# 定义变量方便修改
APP=gmall
HIVE=/opt/module/hive-2.3.0-bin/bin/hive

# 如果是输入的日期按照取输入日期；如果没输入日期取当前时间的前一天
if [ -n "$1" ] ;then
   do_date=$1
else 
   do_date=`date -d "-1 day" +%F`
fi 

echo "=== 日志日期为 $do_date ==="
sql="
load data inpath '/origin_data/gmall/log/topic_start/$do_date' overwrite into table ${APP}.ods_start_log partition(dt='$do_date');

load data inpath '/origin_data/gmall/log/topic_event/$do_date' overwrite into table ${APP}.ods_event_log partition(dt='$do_date');
"

$HIVE -e "$sql"

hadoop jar /opt/module/hadoop-2.7.2/share/hadoop/common/hadoop-lzo-0.4.20.jar com.hadoop.compression.lzo.DistributedLzoIndexer /warehouse/gmall/ods/ods_start_log/dt=$do_date

hadoop jar /opt/module/hadoop-2.7.2/share/hadoop/common/hadoop-lzo-0.4.20.jar com.hadoop.compression.lzo.DistributedLzoIndexer /warehouse/gmall/ods/ods_event_log/dt=$do_date

3.3 业务数据

从 MySQL 导入 HDFS 的数据

3.3.1 创建表

一堆表，以 订单表 为例

-- 增量及更新
hive (gmall)>
use gmall;
drop table if exists ods_order_info;
create external table ods_order_info (
    `id` string COMMENT '订单号',
    `final_total_amount` decimal(10,2) COMMENT '订单金额',
    `order_status` string COMMENT '订单状态',
    `user_id` string COMMENT '用户id',
    `out_trade_no` string COMMENT '支付流水号',
    `create_time` string COMMENT '创建时间',
    `operate_time` string COMMENT '操作时间',
    `province_id` string COMMENT '省份ID',
    `benefit_reduce_amount` decimal(10,2) COMMENT '优惠金额',
    `original_total_amount` decimal(10,2)  COMMENT '原价金额',
    `feight_fee` decimal(10,2)  COMMENT '运费'
) COMMENT '订单表'
PARTITIONED BY (`dt` string)
row format delimited fields terminated by '\t'
STORED AS
  INPUTFORMAT 'com.hadoop.mapred.DeprecatedLzoTextInputFormat'
  OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
location '/warehouse/gmall/ods/ods_order_info/';

其他表的创建略。

3.3.2 数据导入脚本

hdfs_to_ods_db.sh

#!/bin/bash

APP=gmall
HIVE=/opt/module/hive-2.3.0-bin/bin/hive

# 如果是输入的日期按照取输入日期；如果没输入日期取当前时间的前一天
if [ -n "$2" ] ;then
    do_date=$2
else 
    do_date=`date -d "-1 day" +%F`
fi

sql1=" 
load data inpath '/origin_data/$APP/db/order_info/$do_date' OVERWRITE into table ${APP}.ods_order_info partition(dt='$do_date');

load data inpath '/origin_data/$APP/db/order_detail/$do_date' OVERWRITE into table ${APP}.ods_order_detail partition(dt='$do_date');

load data inpath '/origin_data/$APP/db/sku_info/$do_date' OVERWRITE into table ${APP}.ods_sku_info partition(dt='$do_date');

load data inpath '/origin_data/$APP/db/user_info/$do_date' OVERWRITE into table ${APP}.ods_user_info partition(dt='$do_date');

load data inpath '/origin_data/$APP/db/payment_info/$do_date' OVERWRITE into table ${APP}.ods_payment_info partition(dt='$do_date');

load data inpath '/origin_data/$APP/db/base_category1/$do_date' OVERWRITE into table ${APP}.ods_base_category1 partition(dt='$do_date');

load data inpath '/origin_data/$APP/db/base_category2/$do_date' OVERWRITE into table ${APP}.ods_base_category2 partition(dt='$do_date');

load data inpath '/origin_data/$APP/db/base_category3/$do_date' OVERWRITE into table ${APP}.ods_base_category3 partition(dt='$do_date'); 

load data inpath '/origin_data/$APP/db/base_trademark/$do_date' OVERWRITE into table ${APP}.ods_base_trademark partition(dt='$do_date'); 

load data inpath '/origin_data/$APP/db/activity_info/$do_date' OVERWRITE into table ${APP}.ods_activity_info partition(dt='$do_date'); 

load data inpath '/origin_data/$APP/db/activity_order/$do_date' OVERWRITE into table ${APP}.ods_activity_order partition(dt='$do_date'); 

load data inpath '/origin_data/$APP/db/cart_info/$do_date' OVERWRITE into table ${APP}.ods_cart_info partition(dt='$do_date'); 

load data inpath '/origin_data/$APP/db/comment_info/$do_date' OVERWRITE into table ${APP}.ods_comment_info partition(dt='$do_date'); 

load data inpath '/origin_data/$APP/db/coupon_info/$do_date' OVERWRITE into table ${APP}.ods_coupon_info partition(dt='$do_date'); 

load data inpath '/origin_data/$APP/db/coupon_use/$do_date' OVERWRITE into table ${APP}.ods_coupon_use partition(dt='$do_date'); 

load data inpath '/origin_data/$APP/db/favor_info/$do_date' OVERWRITE into table ${APP}.ods_favor_info partition(dt='$do_date'); 

load data inpath '/origin_data/$APP/db/order_refund_info/$do_date' OVERWRITE into table ${APP}.ods_order_refund_info partition(dt='$do_date'); 

load data inpath '/origin_data/$APP/db/order_status_log/$do_date' OVERWRITE into table ${APP}.ods_order_status_log partition(dt='$do_date'); 

load data inpath '/origin_data/$APP/db/spu_info/$do_date' OVERWRITE into table ${APP}.ods_spu_info partition(dt='$do_date'); 

load data inpath '/origin_data/$APP/db/activity_rule/$do_date' OVERWRITE into table ${APP}.ods_activity_rule partition(dt='$do_date'); 

load data inpath '/origin_data/$APP/db/base_dic/$do_date' OVERWRITE into table ${APP}.ods_base_dic partition(dt='$do_date'); 
"

sql2=" 
load data inpath '/origin_data/$APP/db/base_province/$do_date' OVERWRITE into table ${APP}.ods_base_province;

load data inpath '/origin_data/$APP/db/base_region/$do_date' OVERWRITE into table ${APP}.ods_base_region;
"
case $1 in
"first"){
    $HIVE -e "$sql1"
    $HIVE -e "$sql2"
};;
"all"){
    $HIVE -e "$sql1"
};;
esac

执行：

1 2	hdfs_to_ods_db.sh first 2020-03-10 hdfs_to_ods_db.sh all 2020-03-11

测试：

1	select * from ods_order_detail where dt='2020-03-11';

四、DWD 层搭建

4.1 用户行为数据

将 JSON 数据解析为标准的列数据
创建 UDF 函数（解析json列）
创建 UDTF 函数（一进多出，将一行数据拆分成多行）

4.2 业务数据

进行维度建模
join多张表，形成一个大宽表
拉链表
- 用于处理缓慢变化的数据
- 如果每日全量记录，太浪费
- 拉链表，有变化的行才会记录
- 两个特殊的列 start_time, end_time 分别记录这条的生效和结束时间，有变化时就会产生一个新的记录行

电商数仓 V2.0 （03 电商数据仓库系统）

电商数仓 V2.0 （03 电商数据仓库系统）

一、数仓分层

1.1 为什么要分层

1.2 数据集市与数据仓库

二、数仓理论简介

2.1 范式理论

2.2 关系建模和维度建模

2.2.1 关系建模

2.2.2 维度建模

2.3 维度表和事实表

2.3.1 维度表

2.3.2 事实表

2.4 数据仓库建模

2.4.1 ODS层

2.4.2 DWD层

2.4.3 DWS层

2.4.4 DWT层

2.4.5 ADS层

三、ODS 层搭建

3.1 创建数据库

3.2 用户行为数据

3.2.1 启动日志表

3.2.2 事件日志表

3.2.3 数据导入脚本

3.3 业务数据

3.3.1 创建表

3.3.2 数据导入脚本

四、DWD 层搭建

4.1 用户行为数据

4.2 业务数据

五、DWS 层搭建

上一页

raw.githubusercontent.com无法连接

教程

下一页

CentOS6 yum 404

yum