常见问题
    当前位置：宁波企业邮 » 企业目录 » 温州 » 文章详细

    【项目】数仓项目（六）
    <>

     

    一、数仓分层

    1.1 为什么要分层

    在这里插入图片描述

    1.2 数据集市与数据仓库概念

    在这里插入图片描述

    1.3 数仓命名规范

    1.3.1 表命名

    • ODS层命名为ods_表名
    • DWD层命名为dwd_dim/fact_表名
    • DWS层命名为dws_表名
    • DWT层命名为dwt_购物车
    • ADS层命名为ads_表名
    • 临时表命名为xxx_tmp
    • 用户行为表，以log为后缀

    1.3.2 脚本命名

    • 数据源_to_目标_db/log.sh
    • 用户行为脚本以log为后缀；业务数据脚本以db为后缀

    二、数仓理论

    2.1 范式理论

    2.1.1 范式概念

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

    2）优点
    关系型数据库设计时，遵照一定的规范要求，目的在于降低数据的冗余性

    为什么要降低数据冗余性？

    • （1）十几年前，磁盘很贵，为了减少磁盘存储。
    • （2）以前没有分布式系统，都是单机，只能增加磁盘，磁盘个数也是有限的
    • （3）一次修改，需要修改多个表，很难保证数据一致性

    3）缺点
    范式的缺点是获取数据时，需要通过 Join 拼接出最后的数据

    4）分类
    目前业界范式有：第一范式(1NF)、第二范式(2NF)、第三范式(3NF)、巴斯-科德范式(BCNF)、第四范式(4NF)、第五范式(5NF)

    2.1.2 函数依赖

    在这里插入图片描述

    2.1.3 三范式区分

    在这里插入图片描述
    在这里插入图片描述
    在这里插入图片描述

    2.2 关系建模与维度建模

    当今的数据处理大致可以分成两大类：联机事务处理 OLTP（on-line transactionprocessing）、联机分析处理 OLAP（On-Line Analytical Processing）

    • OLTP 是传统的关系型数据库的主要应用，主要是基本的、日常的事务处理，例如银行交易

    • OLAP 是数据仓库系统的主要应用，支持复杂的分析操作，侧重决策支持，并且提供直观易懂的查询结果

    二者的主要区别对比如下表所示?
    在这里插入图片描述

    2.2.1 关系建模

    关系模型如下图所示，严格遵循第三范式（3NF），从图中可以看出，较为松散、零碎，
    物理表数量多，而数据冗余程度低
    由于数据分布于众多的表中，这些数据可以更为灵活地被应用，功能性较强。关系模型主要应用与 OLTP 系统中，为了保证数据的一致性以及避免冗余，所以大部分业务系统的表都是遵循第三范式的
    在这里插入图片描述

    维度模型如下图所示，主要应用于 OLAP 系统中，通常以某一个事实表为中心进行表的组织，主要面向业务，特征是可能存在数据的冗余，但是能方便的得到数据

    关系模型虽然冗余少，但是在大规模数据，跨表分析统计查询过程中，会造成多表关
    联，这会大大降低执行效率。所以通常我们采用维度模型建模，把相关各种表整理成两种：
    事实表和维度表两种
    在这里插入图片描述

    2.2.2 维度建模

    在维度建模的基础上又分为三种模型：星型模型、雪花模型、星座模型
    在这里插入图片描述
    在这里插入图片描述

    2.3 维度表和事实表

    2.3.1 维度表

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

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

    时间维度表：
    在这里插入图片描述

    2.3.2 事实表

    事实表中的每行数据代表一个业务事件（下单、支付、退款、评价等）。
    “事实”这个术语表示的是业务事件的度量值（可统计次数、个数、件数、金额等）
    例如，订单事件中的下单金额

    每一个事实表的行包括：具有可加性的数值型的度量值、与维表相连接的外键、通常具
    有两个和两个以上的外键、外键之间表示维表之间多对多的关系

    事实表的特征：

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

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

    2）周期型快照事实表
    周期型快照事实表中不会保留所有数据，只保留固定时间间隔的数据，例如每天或者每月的销售额，或每月的账户余额等

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

    2.4 数据仓库建模（绝对重点）

    2.4.1 ODS 层

    • （1）保持数据原貌不做任何修改，起到备份数据的作用。
    • （2）数据采用压缩，减少磁盘存储空间（例如：原始数据 100G，可以压缩到 10G 左
      右）
    • （3）创建分区表，防止后续的全表扫描

    2.4.2 DWD 层

    DWD 层需构建维度模型，一般采用星型模型，呈现的状态一般为星座模型

    维度建模一般按照以下四个步骤：
    选择业务过程 → 声明粒度 → 确认维度 → 确认事实

    • （1）选择业务过程
      在业务系统中，挑选我们感兴趣的业务线，比如下单业务，支付业务，退款业务，物流业务，一条业务线对应一张事实表

    • （2）声明粒度
      数据粒度指数据仓库的数据中保存数据的细化程度或综合程度的级别。
      声明粒度意味着精确定义事实表中的一行数据表示什么，应该尽可能选择最小粒度，以此来应各种各样的需求
      典型的粒度声明如下：
      订单中，每个商品项作为下单事实表中的一行，粒度为每次下单
      每周的订单次数作为一行，粒度就是每周下单
      每月的订单次数作为一行，粒度就是每月下单

    • （3）确定维度
      维度的主要作用是描述业务是事实，主要表示的是“谁，何处，何时”等信息

    • （4）确定事实
      此处的“事实”一词，指的是业务中的度量值，例如订单金额、下单次数等
      在 DWD 层，以业务过程为建模驱动，基于每个具体业务过程的特点，构建最细粒度的明细层事实表。事实表可做适当的宽表化处理

    在这里插入图片描述
    在这里插入图片描述
    至此，数仓的维度建模已经完毕，DWS、DWT 和 ADS 和 维度建模已经没有关系了
    DWS 和 DWT 都是建宽表，宽表都是按照主题去建。主题相当于观察问题的角度，对应着维度表

    2.4.3 DWS 层

    统计各个主题对象的当天行为，服务于 DWT 层的主题宽表，以及一些业务明细数据，应对特殊需求（例如，购买行为，统计商品复购率）
    在这里插入图片描述

    2.4.4 DWT 层

    以分析的主题对象为建模驱动，基于上层的应用和产品的指标需求，构建主题对象的全量宽表
    在这里插入图片描述

    2.4.5 ADS 层

    对电商系统各大主题指标分别进行分析

    三、数仓搭建 - ODS 层

    • 1）保持数据原貌不做任何修改，起到备份数据的作用
    • 2）数据采用 LZO 压缩，减少磁盘存储空间。100G 数据可以压缩到 10G 以内
    • 3）创建分区表，防止后续的全表扫描，在企业开发中大量使用分区表。
    • 4）创建外部表。在企业开发中，除了自己用的临时表，创建内部表外，绝大多数场景都是创建外部表

    3.1 创建数据库

    1）显示数据库

    show databases;

    2）创建数据库

    create database gmall;

    3）使用数据库

    use gmall;

    3.2 ODS 层（用户行为数据）

    在这里插入图片描述

    3.2.1 创建启动日志表 ods_start_log

    在这里插入图片描述
    1）创建输入数据是 lzo， 输出是 text，支持 json 解析的分区表

    1. drop table if exists ods_start_log;
    2. create external table ods_start_log (`line` string)
    3. PARTITIONED BY (`dt` string)
    4. STORED AS
    5. INPUTFORMAT 'com.hadoop.mapred.DeprecatedLzoTextInputFormat'
    6. OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
    7. LOCATION '/warehouse/gmall/ods/ods_start_log';
    8.  
    9. 说明 Hive 的 LZO 压缩：https://cwiki.apache.org/confluence/display/Hive/LanguageManual+LZO

    2）加载数据

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

    3）查看是否加载成功

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

    4）为 lzo 压缩文件创建索引

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

    3.2.2 创建事件日志表 ods_event_log

    在这里插入图片描述
    1）创建输入数据是 lzo ，输出是 text，支持 json 解析的分区表

    1. drop table if exists ods_event_log;
    2. create external table ods_event_log(`line` string)
    3. PARTITIONED BY (`dt` string)
    4. STORED AS
    5. INPUTFORMAT 'com.hadoop.mapred.DeprecatedLzoTextInputFormat'
    6. OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
    7. LOCATION '/warehouse/gmall/ods/ods_event_log';

    2）加载数据

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

    3）查看是否加载成功

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

    4）为 lzo 压缩文件创建索引

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

    3.2.3 Shell 中单引号和双引号区别

    （1）单引号不取变量值
    （2）双引号取变量值
    （3）反引号 ` ，执行引号中命令
    （4）双引号内部嵌套单引号，取出变量值
    （5）单引号内部嵌套双引号，不取出变量值

    3.2.4 ODS 层加载数据脚本

    1） vim hdfs_to_ods_log.sh
    在脚本中编写如下内容

    1. #!/bin/bash
    2. # 定义变量方便修改
    3. APP=gmall
    4. hive=/opt/modules/hive/bin/hive
    5. # 如果是输入的日期按照取输入日期；如果没输入日期取当前时间的前一天
    6. if [ -n "$1" ] ;then
    7. do_date=$1
    8. else
    9. do_date=`date -d "-1 day" +%F`
    10. fi
    11. echo "===日志日期为 $do_date==="
    12. sql="
    13. load data inpath '/origin_data/gmall/log/topic_start/$do_date' overwrite
    14. into table ${APP}.ods_start_log partition(dt='$do_date');
    15. load data inpath '/origin_data/gmall/log/topic_event/$do_date' overwrite
    16. into table ${APP}.ods_event_log partition(dt='$do_date');
    17. "
    18. $hive -e "$sql"
    19. hadoop jar /opt/modules/hadoop/share/hadoop/common/hadoop-lzo-0.4.20.jar
    20. com.hadoop.compression.lzo.DistributedLzoIndexer
    21. /warehouse/gmall/ods/ods_start_log/dt=$do_date
    22. hadoop jar /opt/modules/hadoop/share/hadoop/common/hadoop-lzo-0.4.20.jar
    23. com.hadoop.compression.lzo.DistributedLzoIndexer
    24. /warehouse/gmall/ods/ods_event_log/dt=$do_date

    2）增加脚本执行权限

    chmod 777 hdfs_to_ods_log.sh

    3）脚本使用

    hdfs_to_ods_log.sh 2020-03-11

    4）查看导入数据

    1. select * from ods_start_log where dt='2020-03-11' limit 2;
    2. select * from ods_event_log where dt='2020-03-11' limit 2;

    5）脚本执行时间
    企业开发中一般在每日凌晨 30 分~1 点

    3.3 ODS 层（业务数据）

    在这里插入图片描述

    3.3.1 订单表（增量及更新）

    1. drop table if exists ods_order_info;
    2. create external table ods_order_info (
    3. `id` string COMMENT '订单号',
    4. `final_total_amount` decimal(10,2) COMMENT '订单金额',
    5. `order_status` string COMMENT '订单状态',
    6. `user_id` string COMMENT '用户 id',
    7. `out_trade_no` string COMMENT '支付流水号',
    8. `create_time` string COMMENT '创建时间',
    9. `operate_time` string COMMENT '操作时间',
    10. `province_id` string COMMENT '省份 ID',
    11. `benefit_reduce_amount` decimal(10,2) COMMENT '优惠金额',
    12. `original_total_amount` decimal(10,2) COMMENT '原价金额',
    13. `feight_fee` decimal(10,2) COMMENT '运费'
    14. ) COMMENT '订单表'
    15. PARTITIONED BY (`dt` string)
    16. row format delimited fields terminated by '	'
    17. STORED AS
    18. INPUTFORMAT 'com.hadoop.mapred.DeprecatedLzoTextInputFormat'
    19. OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
    20. location '/warehouse/gmall/ods/ods_order_info/';

    3.3.2 订单详情表（增量）

    1. drop table if exists ods_order_detail;
    2. create external table ods_order_detail(
    3. `id` string COMMENT '订单编号',
    4. `order_id` string COMMENT '订单号',
    5. `user_id` string COMMENT '用户 id',
    6. `sku_id` string COMMENT '商品 id',
    7. `sku_name` string COMMENT '商品名称',
    8. `order_price` decimal(10,2) COMMENT '商品价格',
    9. `sku_num` bigint COMMENT '商品数量',
    10. `create_time` string COMMENT '创建时间'
    11. ) COMMENT '订单详情表'
    12. PARTITIONED BY (`dt` string)
    13. row format delimited fields terminated by '	'
    14. STORED AS
    15. INPUTFORMAT 'com.hadoop.mapred.DeprecatedLzoTextInputFormat'
    16. OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
    17. location '/warehouse/gmall/ods/ods_order_detail/';

    3.3.3 SKU 商品表（全量）

    1. drop table if exists ods_sku_info;
    2. create external table ods_sku_info(
    3. `id` string COMMENT 'skuId',
    4. `spu_id` string COMMENT 'spuid',
    5. `price` decimal(10,2) COMMENT '价格',
    6. `sku_name` string COMMENT '商品名称',
    7. `sku_desc` string COMMENT '商品描述',
    8. `weight` string COMMENT '重量',
    9. `tm_id` string COMMENT '品牌 id',
    10. `category3_id` string COMMENT '品类 id',
    11. `create_time` string COMMENT '创建时间'
    12. ) COMMENT 'SKU 商品表'
    13. PARTITIONED BY (`dt` string)
    14. row format delimited fields terminated by '	'
    15. STORED AS
    16. INPUTFORMAT 'com.hadoop.mapred.DeprecatedLzoTextInputFormat'
    17. OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'

    3.3.4 用户表（增量及更新）

    1. drop table if exists ods_user_info;
    2. create external table ods_user_info(
    3. `id` string COMMENT '用户 id',
    4. `name` string COMMENT '姓名',
    5. `birthday` string COMMENT '生日',
    6. `gender` string COMMENT '性别',
    7. `email` string COMMENT '邮箱',
    8. `user_level` string COMMENT '用户等级',
    9. `create_time` string COMMENT '创建时间',
    10. `operate_time` string COMMENT '操作时间'
    11. ) COMMENT '用户表'
    12. PARTITIONED BY (`dt` string)
    13. row format delimited fields terminated by '	'
    14. STORED AS
    15. INPUTFORMAT 'com.hadoop.mapred.DeprecatedLzoTextInputFormat'
    16. OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
    17. location '/warehouse/gmall/ods/ods_user_info/';

    3.3.5 商品一级分类表（全量）

    1. drop table if exists ods_base_category1;
    2. create external table ods_base_category1(
    3. `id` string COMMENT 'id',
    4. `name` string COMMENT '名称'
    5. ) COMMENT '商品一级分类表'
    6. PARTITIONED BY (`dt` string)
    7. row format delimited fields terminated by '	'
    8. STORED AS
    9. INPUTFORMAT 'com.hadoop.mapred.DeprecatedLzoTextInputFormat'
    10. OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
    11. location '/warehouse/gmall/ods/ods_base_category1/';

    3.3.6 商品二级分类表（全量）

    1. drop table if exists ods_base_category2;
    2. create external table ods_base_category2(
    3. `id` string COMMENT ' id',
    4. `name` string COMMENT '名称',
    5.
    阅读：182 次
    日期：2021-01-24
    【 打印 】 【 关闭 】  【 字体：大 中 小 】 
    上一篇： 如何快速构建内部钓鱼平台
    下一篇： python程序下载腾讯企业邮箱附件_python 发送邮件
      >> 相关文章  

    联系我们 | 邮箱收费 | 独立控制面板 | 站点地图 | 浙ICP备20002735号-1

    上海 北京 广州 深圳 南京 苏州 无锡 南通 徐州 杭州 温州 宁波 台州 嘉兴 湖州 绍兴 金华 舟山 成都 重庆 青岛 大连 武汉 郑州 长沙 合肥 南昌 佛山 东莞 惠州 中山

    Copyright © 2016 宁波奇悦网络科技有限公司 All Rights Reserved  销售热线：0574-55011290

    咨询在线客服

    服务热线

    0574-55011290

    微信二维码