问题导读：
1、为什么要进行数据仓库分层？
2、需要掌握哪些数仓理论知识？
3、如何进行关系建模与维度建模？
4、如何进行数据仓库搭建？
**

一、数仓分层

1.1 为什么要分层

企业级数据仓库构建（六）：数仓理论及数仓搭建 - 图1

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

企业级数据仓库构建（六）：数仓理论及数仓搭建 - 图2

1.3 数仓命名规范1.3.1 表命名

  • ODS层命名为ods_表名
  • DWD层命名为dwddim/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.   - （1）十几年前，磁盘很贵，为了减少磁盘存储。
  2.   - （2）以前没有分布式系统，都是单机，只能增加磁盘，磁盘个数也是有限的
  3.   - （3）一次修改，需要修改多个表，很难保证数据一致性

3）缺点
范式的缺点是获取数据时，需要通过 Join 拼接出最后的数据
4）分类
目前业界范式有：第一范式(1NF)、第二范式(2NF)、第三范式(3NF)、巴斯-科德范式(BCNF)、第四范式(4NF)、第五范式(5NF)
2.1.2 函数依赖
企业级数据仓库构建（六）：数仓理论及数仓搭建 - 图3

2.1.3 三范式区分

企业级数据仓库构建（六）：数仓理论及数仓搭建 - 图4
企业级数据仓库构建（六）：数仓理论及数仓搭建 - 图5

企业级数据仓库构建（六）：数仓理论及数仓搭建 - 图6

2.2 关系建模与维度建模

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

  1.   - OLTP 是传统的关系型数据库的主要应用，主要是基本的、日常的事务处理，例如银行交易
  2.   - OLAP 是数据仓库系统的主要应用，支持复杂的分析操作，侧重决策支持，并且提供直观易懂的查询结果

二者的主要区别对比如下表所示

企业级数据仓库构建（六）：数仓理论及数仓搭建 - 图7

2.2.1 关系建模

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

企业级数据仓库构建（六）：数仓理论及数仓搭建 - 图8

维度模型如下图所示，主要应用于 OLAP 系统中，通常以某一个事实表为中心进行表的组织，主要面向业务，特征是可能存在数据的冗余，但是能方便的得到数据关系模型虽然冗余少，但是在大规模数据，跨表分析统计查询过程中，会造成多表关联，这会大大降低执行效率。所以通常我们采用维度模型建模，把相关各种表整理成两种：

事实表和维度表两种

企业级数据仓库构建（六）：数仓理论及数仓搭建 - 图9

2.2.2 维度建模

在维度建模的基础上又分为三种模型：星型模型、雪花模型、星座模型

企业级数据仓库构建（六）：数仓理论及数仓搭建 - 图10

企业级数据仓库构建（六）：数仓理论及数仓搭建 - 图11

2.3 维度表和事实表

2.3.1 维度表

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

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

时间维度表：

企业级数据仓库构建（六）：数仓理论及数仓搭建 - 图12

2.3.2 事实表

事实表中的每行数据代表一个业务事件（下单、支付、退款、评价等）。
“事实”这个术语表示的是业务事件的度量值（可统计次数、个数、件数、金额等）
例如，订单事件中的下单金额
每一个事实表的行包括：具有可加性的数值型的度量值、与维表相连接的外键、通常具
有两个和两个以上的外键、外键之间表示维表之间多对多的关系
事实表的特征：

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

1）事务型事实表
以每个事务或事件为单位，例如一个销售订单记录，一笔支付记录等，作为事实表里的一行数据。一旦事务被提交，事实表数据被插入，数据就不再进行更改，其更新方式为增量更新
2）周期型快照事实表
周期型快照事实表中不会保留所有数据，只保留固定时间间隔的数据，例如每天或者每月的销售额，或每月的账户余额等
3）累积型快照事实表
累计快照事实表用于跟踪业务事实的变化。例如，数据仓库中可能需要累积或者存储订单从下订单开始，到订单商品被打包、运输、和签收的各个业务阶段的时间点数据来跟踪订单声明周期的进展情况。当这个业务过程进行时，事实表的记录也要不断更新

企业级数据仓库构建（六）：数仓理论及数仓搭建 - 图13

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

2.4.1 ODS 层

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

    2.4.2 DWD 层

DWD 层需构建维度模型，一般采用星型模型，呈现的状态一般为星座模型
维度建模一般按照以下四个步骤：
选择业务过程 → 声明粒度 → 确认维度 → 确认事实

  1.   - （1）选择业务过程<br />在业务系统中，挑选我们感兴趣的业务线，比如下单业务，支付业务，退款业务，物流业务，一条业务线对应一张事实表
  2.   - （2）声明粒度<br />数据粒度指数据仓库的数据中保存数据的细化程度或综合程度的级别。<br />声明粒度意味着精确定义事实表中的一行数据表示什么，应该尽可能选择最小粒度，以此来应各种各样的需求<br />典型的粒度声明如下：<br />订单中，每个商品项作为下单事实表中的一行，粒度为每次下单<br />每周的订单次数作为一行，粒度就是每周下单<br />每月的订单次数作为一行，粒度就是每月下单
  3.   - （3）确定维度<br />维度的主要作用是描述业务是事实，主要表示的是“谁，何处，何时”等信息
  4.   - （4）确定事实<br />此处的“事实”一词，指的是业务中的度量值，例如订单金额、下单次数等<br />在 DWD 层，以业务过程为建模驱动，基于每个具体业务过程的特点，构建最细粒度的明细层事实表。事实表可做适当的宽表化处理

企业级数据仓库构建（六）：数仓理论及数仓搭建 - 图14

企业级数据仓库构建（六）：数仓理论及数仓搭建 - 图15

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

2.4.3 DWS 层

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

企业级数据仓库构建（六）：数仓理论及数仓搭建 - 图16

2.4.4 DWT 层

以分析的主题对象为建模驱动，基于上层的应用和产品的指标需求，构建主题对象的全量宽表

企业级数据仓库构建（六）：数仓理论及数仓搭建 - 图17
**

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 层（用户行为数据）
企业级数据仓库构建（六）：数仓理论及数仓搭建 - 图18

3.2.1 创建启动日志表 ods_start_log

企业级数据仓库构建（六）：数仓理论及数仓搭建 - 图19
**

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';
  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');
  3. 注意：时间格式都配置成 YYYY-MM-DD 格式，这是 Hive 默认支持的时间格式

3）查看是否加载成功

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

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

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

3.2.2 创建事件日志表 ods_event_log

企业级数据仓库构建（六）：数仓理论及数仓搭建 - 图20
1）创建输入数据是 lzo ，输出是 text，支持 json 解析的分区表

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

2）加载数据

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

3）查看是否加载成功

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

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

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

2）增加脚本执行权限

  1. 1. chmod 777 hdfs_to_ods_log.sh

3）脚本使用

  1. 1. hdfs_to_ods_log.sh 2020-03-11

4）查看导入数据

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

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

3.3 ODS 层（业务数据）

企业级数据仓库构建（六）：数仓理论及数仓搭建 - 图21

3.3.1 订单表（增量及更新）

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

3.3.2 订单详情表（增量）

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

3.3.3 SKU 商品表（全量）

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

3.3.4 用户表（增量及更新）

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

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

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

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

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

3.3.7 商品三级分类表（全量）

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

3.3.8 支付流水表（增量）

  1. 1. drop table if exists ods_payment_info;
  2. 2. create external table ods_payment_info(
  3. 3. `id` bigint COMMENT '编号',
  4. 4. `out_trade_no` string COMMENT '对外业务编号',
  5. 5. `order_id` string COMMENT '订单编号',
  6. 6. `user_id` string COMMENT '用户编号',
  7. 7. `alipay_trade_no` string COMMENT '支付宝交易流水编号',
  8. 8. `total_amount` decimal(16,2) COMMENT '支付金额',
  9. 9. `subject` string COMMENT '交易内容',
  10. 10. `payment_type` string COMMENT '支付类型',
  11. 11. `payment_time` string COMMENT '支付时间'
  12. 12. ) COMMENT '支付流水表'
  13. 13. PARTITIONED BY (`dt` string)
  14. 14. row format delimited fields terminated by '\t'
  15. 15. STORED AS
  16. 16. INPUTFORMAT 'com.hadoop.mapred.DeprecatedLzoTextInputFormat'
  17. 17. OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
  18. 18. location '/warehouse/gmall/ods/ods_payment_info/';
  19. 19. 
  20. 复制代码

3.3.9 省份表（特殊）

  1. 1. drop table if exists ods_base_province;
  2. 2. create external table ods_base_province (
  3. 3. `id` bigint COMMENT '编号',
  4. 4. `name` string COMMENT '省份名称',
  5. 5. `region_id` string COMMENT '地区 ID',
  6. 6. `area_code` string COMMENT '地区编码',
  7. 7. `iso_code` string COMMENT 'iso 编码'
  8. 8. ) COMMENT '省份表'
  9. 9. row format delimited fields terminated by '\t'
  10. 10. STORED AS
  11. 11. INPUTFORMAT 'com.hadoop.mapred.DeprecatedLzoTextInputFormat'
  12. 12. OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
  13. 13. location '/warehouse/gmall/ods/ods_base_province/';

3.3.10 地区表（特殊）

  1. 1. drop table if exists ods_base_region;
  2. 2. create external table ods_base_region (
  3. 3. `id` bigint COMMENT '编号',
  4. 4. `region_name` string COMMENT '地区名称'
  5. 5. ) COMMENT '地区表'
  6. 6. row format delimited fields terminated by '\t'
  7. 7. STORED AS
  8. 8. INPUTFORMAT 'com.hadoop.mapred.DeprecatedLzoTextInputFormat'
  9. 9. OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
  10. 10. location '/warehouse/gmall/ods/ods_base_region/';

3.3.11 品牌表（全量）

  1. 1. drop table if exists ods_base_trademark;
  2. 2. create external table ods_base_trademark (
  3. 3. `tm_id` bigint COMMENT '编号',
  4. 4. `tm_name` string COMMENT '品牌名称'
  5. 5. ) COMMENT '品牌表'
  6. 6. PARTITIONED BY (`dt` string)
  7. 7. row format delimited fields terminated by '\t'
  8. 8. STORED AS
  9. 9. INPUTFORMAT 'com.hadoop.mapred.DeprecatedLzoTextInputFormat'
  10. 10. OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
  11. 11. location '/warehouse/gmall/ods/ods_base_trademark/';

3.3.12 订单状态表（增量）

  1. 1. drop table if exists ods_order_status_log;
  2. 2. create external table ods_order_status_log (
  3. 3. `id` bigint COMMENT '编号',
  4. 4. `order_id` string COMMENT '订单 ID',
  5. 5. `order_status` string COMMENT '订单状态',
  6. 6. `operate_time` string COMMENT '修改时间'
  7. 7. ) COMMENT '订单状态表'
  8. 8. PARTITIONED BY (`dt` string)
  9. 9. row format delimited fields terminated by '\t'
  10. 10. STORED AS
  11. 11. INPUTFORMAT 'com.hadoop.mapred.DeprecatedLzoTextInputFormat'
  12. 12. OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
  13. 13. location '/warehouse/gmall/ods/ods_order_status_log/';

3.3.13 SPU 商品表（全量）

  1. 1. drop table if exists ods_spu_info;
  2. 2. create external table ods_spu_info(
  3. 3. `id` string COMMENT 'spuid',
  4. 4. `spu_name` string COMMENT 'spu 名称',
  5. 5. `category3_id` string COMMENT '品类 id',
  6. 6. `tm_id` string COMMENT '品牌 id'
  7. 7. ) COMMENT 'SPU 商品表'
  8. 8. PARTITIONED BY (`dt` string)
  9. 9. row format delimited fields terminated by '\t'
  10. 10. STORED AS
  11. 11. INPUTFORMAT 'com.hadoop.mapred.DeprecatedLzoTextInputFormat'
  12. 12. OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
  13. 13. location '/warehouse/gmall/ods/ods_spu_info/';

3.3.14 商品评论表（增量）

  1. 1. create external table ods_comment_info(
  2. 2. `id` string COMMENT '编号',
  3. 3. `user_id` string COMMENT '用户 ID',
  4. 4. `sku_id` string COMMENT '商品 sku',
  5. 5. `spu_id` string COMMENT '商品 spu',
  6. 6. `order_id` string COMMENT '订单 ID',
  7. 7. `appraise` string COMMENT '评价',
  8. 8. `create_time` string COMMENT '评价时间'
  9. 9. ) COMMENT '商品评论表'
  10. 10. PARTITIONED BY (`dt` string)
  11. 11. row format delimited fields terminated by '\t'
  12. 12. STORED AS
  13. 13. INPUTFORMAT 'com.hadoop.mapred.DeprecatedLzoTextInputFormat'
  14. 14. OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
  15. 15. location '/warehouse/gmall/ods/ods_comment_info/';

3.3.15 退单表（增量）

  1. 1. drop table if exists ods_order_refund_info;
  2. 2. create external table ods_order_refund_info(
  3. 3. `id` string COMMENT '编号',
  4. 4. `user_id` string COMMENT '用户 ID',
  5. 5. `order_id` string COMMENT '订单 ID',
  6. 6. `sku_id` string COMMENT '商品 ID',
  7. 7. `refund_type` string COMMENT '退款类型',
  8. 8. `refund_num` bigint COMMENT '退款件数',
  9. 9. `refund_amount` decimal(16,2) COMMENT '退款金额',
  10. 10. `refund_reason_type` string COMMENT '退款原因类型',
  11. 11. `create_time` string COMMENT '退款时间'
  12. 12. ) COMMENT '退单表'
  13. 13. PARTITIONED BY (`dt` string)
  14. 14. row format delimited fields terminated by '\t'
  15. 15. STORED AS
  16. 16. INPUTFORMAT 'com.hadoop.mapred.DeprecatedLzoTextInputFormat'
  17. 17. OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
  18. 18. location '/warehouse/gmall/ods/ods_order_refund_info/';

3.3.16 加购表（全量）

  1. 1. drop table if exists ods_cart_info;
  2. 2. create external table ods_cart_info(
  3. 3. `id` string COMMENT '编号',
  4. 4. `user_id` string COMMENT '用户 id',
  5. 5. `sku_id` string COMMENT 'skuid',
  6. 6. `cart_price` string COMMENT '放入购物车时价格',
  7. 7. `sku_num` string COMMENT '数量',
  8. 8. `sku_name` string COMMENT 'sku 名称 (冗余)',
  9. 9. `create_time` string COMMENT '创建时间',
  10. 10. `operate_time` string COMMENT '修改时间',
  11. 11. `is_ordered` string COMMENT '是否已经下单',
  12. 12. `order_time` string COMMENT '下单时间'
  13. 13. ) COMMENT '加购表'
  14. 14. PARTITIONED BY (`dt` string)
  15. 15. row format delimited fields terminated by '\t'
  16. 16. STORED AS
  17. 17. INPUTFORMAT 'com.hadoop.mapred.DeprecatedLzoTextInputFormat'
  18. 18. OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
  19. 19. location '/warehouse/gmall/ods/ods_cart_info/';

3.3.17 商品收藏表（全量）

  1. 1. drop table if exists ods_favor_info;
  2. 2. create external table ods_favor_info(
  3. 3. `id` string COMMENT '编号',
  4. 4. `user_id` string COMMENT '用户 id',
  5. 5. `sku_id` string COMMENT 'skuid',
  6. 6. `spu_id` string COMMENT 'spuid',
  7. 7. `is_cancel` string COMMENT '是否取消',
  8. 8. `create_time` string COMMENT '收藏时间',
  9. 9. `cancel_time` string COMMENT '取消时间'
  10. 10. ) COMMENT '商品收藏表'
  11. 11. PARTITIONED BY (`dt` string)
  12. 12. row format delimited fields terminated by '\t'
  13. 13. STORED AS
  14. 14. INPUTFORMAT 'com.hadoop.mapred.DeprecatedLzoTextInputFormat'
  15. 15. OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
  16. 16. location '/warehouse/gmall/ods/ods_favor_info/';

3.3.18 优惠券领用表（新增及变化）

  1. 1. drop table if exists ods_coupon_use;
  2. 2. create external table ods_coupon_use(
  3. 3. `id` string COMMENT '编号',
  4. 4. `coupon_id` string COMMENT '优惠券 ID',
  5. 5. `user_id` string COMMENT 'skuid',
  6. 6. `order_id` string COMMENT 'spuid',
  7. 7. `coupon_status` string COMMENT '优惠券状态',
  8. 8. `get_time` string COMMENT '领取时间',
  9. 9. `using_time` string COMMENT '使用时间(下单)',
  10. 10. `used_time` string COMMENT '使用时间(支付)'
  11. 11. ) COMMENT '优惠券领用表'
  12. 12. PARTITIONED BY (`dt` string)
  13. 13. row format delimited fields terminated by '\t'
  14. 14. STORED AS
  15. 15. INPUTFORMAT 'com.hadoop.mapred.DeprecatedLzoTextInputFormat'
  16. 16. OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
  17. 17. location '/warehouse/gmall/ods/ods_coupon_use/';

3.3.19 优惠券表（全量）

  1. 1. drop table if exists ods_coupon_info;
  2. 2. create external table ods_coupon_info(
  3. 3. `id` string COMMENT '购物券编号',
  4. 4. `coupon_name` string COMMENT '购物券名称',
  5. 5. `coupon_type` string COMMENT '购物券类型 1 现金券 2 折扣券 3 满减券 4 满件打折券',
  6. 6. `condition_amount` string COMMENT '满额数',
  7. 7. `condition_num` string COMMENT '满件数',
  8. 8. `activity_id` string COMMENT '活动编号',
  9. 9. `benefit_amount` string COMMENT '减金额',
  10. 10. `benefit_discount` string COMMENT '折扣',
  11. 11. `create_time` string COMMENT '创建时间',
  12. 12. `range_type` string COMMENT '范围类型 1、商品 2、品类 3、品牌',
  13. 13. `spu_id` string COMMENT '商品 id',
  14. 14. `tm_id` string COMMENT '品牌 id',
  15. 15. `category3_id` string COMMENT '品类 id',
  16. 16. `limit_num` string COMMENT '最多领用次数',
  17. 17. `operate_time` string COMMENT '修改时间',
  18. 18. `expire_time` string COMMENT '过期时间'
  19. 19. ) COMMENT '优惠券表'
  20. 20. PARTITIONED BY (`dt` string)
  21. 21. row format delimited fields terminated by '\t'
  22. 22. STORED AS
  23. 23. INPUTFORMAT 'com.hadoop.mapred.DeprecatedLzoTextInputFormat'
  24. 24. OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
  25. 25. location '/warehouse/gmall/ods/ods_coupon_info/';
  26. 26. 
  27. 复制代码

3.3.20 活动表（全量）

  1. 1. drop table if exists ods_activity_info;
  2. 2. create external table ods_activity_info(
  3. 3. `id` string COMMENT '编号',
  4. 4. `activity_name` string COMMENT '活动名称',
  5. 5. `activity_type` string COMMENT '活动类型',
  6. 6. `start_time` string COMMENT '开始时间',
  7. 7. `end_time` string COMMENT '结束时间',
  8. 8. `create_time` string COMMENT '创建时间'
  9. 9. ) COMMENT '活动表'
  10. 10. PARTITIONED BY (`dt` string)
  11. 11. row format delimited fields terminated by '\t'
  12. 12. STORED AS
  13. 13. INPUTFORMAT 'com.hadoop.mapred.DeprecatedLzoTextInputFormat'
  14. 14. OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
  15. 15. location '/warehouse/gmall/ods/ods_activity_info/';

3.3.21 活动订单关联表（增量）

  1. 1. drop table if exists ods_activity_order;
  2. 2. create external table ods_activity_order(
  3. 3. `id` string COMMENT '编号',
  4. 4. `activity_id` string COMMENT '优惠券 ID',
  5. 5. `order_id` string COMMENT 'skuid',
  6. 6. `create_time` string COMMENT '领取时间'
  7. 7. ) COMMENT '活动订单关联表'
  8. 8. PARTITIONED BY (`dt` string)
  9. 9. row format delimited fields terminated by '\t'
  10. 10. STORED AS
  11. 11. INPUTFORMAT 'com.hadoop.mapred.DeprecatedLzoTextInputFormat'
  12. 12. OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
  13. 13. location '/warehouse/gmall/ods/ods_activity_order/';

3.3.22 优惠规则表（全量）

  1. 1. drop table if exists ods_activity_rule;
  2. 2. create external table ods_activity_rule(
  3. 3. `id` string COMMENT '编号',
  4. 4. `activity_id` string COMMENT '活动 ID',
  5. 5. `condition_amount` string COMMENT '满减金额',
  6. 6. `condition_num` string COMMENT '满减件数',
  7. 7. `benefit_amount` string COMMENT '优惠金额',
  8. 8. `benefit_discount` string COMMENT '优惠折扣',
  9. 9. `benefit_level` string COMMENT '优惠级别'
  10. 10. ) COMMENT '优惠规则表'
  11. 11. PARTITIONED BY (`dt` string)
  12. 12. row format delimited fields terminated by '\t'
  13. 13. STORED AS
  14. 14. INPUTFORMAT 'com.hadoop.mapred.DeprecatedLzoTextInputFormat'
  15. 15. OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
  16. 16. location '/warehouse/gmall/ods/ods_activity_rule/';
  17. 17. 
  18. 复制代码

3.3.23 编码字典表（全量）

  1. 1. drop table if exists ods_base_dic;
  2. 2. create external table ods_base_dic(
  3. 3. `dic_code` string COMMENT '编号',
  4. 4. `dic_name` string COMMENT '编码名称',
  5. 5. `parent_code` string COMMENT '父编码',
  6. 6. `create_time` string COMMENT '创建日期',
  7. 7. `operate_time` string COMMENT '操作日期'
  8. 8. ) COMMENT '编码字典表'
  9. 9. PARTITIONED BY (`dt` string)
  10. 10. row format delimited fields terminated by '\t'
  11. 11. STORED AS
  12. 12. INPUTFORMAT 'com.hadoop.mapred.DeprecatedLzoTextInputFormat'
  13. 13. OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
  14. 14. location '/warehouse/gmall/ods/ods_base_dic/';

3.3.24 ODS 层加载数据脚本

1）vim hdfs_to_ods_db.sh
在脚本中填写如下内容

  1. 1. #!/bin/bash
  2. 2. APP=gmall
  3. 3. hive=/opt/modules/hive/bin/hive
  4. 4. # 如果是输入的日期按照取输入日期；如果没输入日期取当前时间的前一天
  5. 5. if [ -n "$2" ] ;then
  6. 6. do_date=$2
  7. 7. else
  8. 8. do_date=`date -d "-1 day" +%F`
  9. 9. fi
  10. 10. sql1="
  11. 11. load data inpath '/origin_data/$APP/db/order_info/$do_date' OVERWRITE into table
  12. 12. ${APP}.ods_order_info partition(dt='$do_date');
  13. 13. load data inpath '/origin_data/$APP/db/order_detail/$do_date' OVERWRITE into table
  14. 14. ${APP}.ods_order_detail partition(dt='$do_date');
  15. 15. load data inpath '/origin_data/$APP/db/sku_info/$do_date' OVERWRITE into table
  16. 16. ${APP}.ods_sku_info partition(dt='$do_date');
  17. 17. load data inpath '/origin_data/$APP/db/user_info/$do_date' OVERWRITE into table
  18. 18. ${APP}.ods_user_info partition(dt='$do_date');
  19. 19. load data inpath '/origin_data/$APP/db/payment_info/$do_date' OVERWRITE into table
  20. 20. ${APP}.ods_payment_info partition(dt='$do_date');
  21. 21. load data inpath '/origin_data/$APP/db/base_category1/$do_date' OVERWRITE into table
  22. 22. ${APP}.ods_base_category1 partition(dt='$do_date');
  23. 23. load data inpath '/origin_data/$APP/db/base_category2/$do_date' OVERWRITE into table
  24. 24. ${APP}.ods_base_category2 partition(dt='$do_date');
  25. 25. load data inpath '/origin_data/$APP/db/base_category3/$do_date' OVERWRITE into table
  26. 26. ${APP}.ods_base_category3 partition(dt='$do_date');
  27. 27. load data inpath '/origin_data/$APP/db/base_trademark/$do_date' OVERWRITE into table
  28. 28. ${APP}.ods_base_trademark partition(dt='$do_date');
  29. 29. load data inpath '/origin_data/$APP/db/activity_info/$do_date' OVERWRITE into table
  30. 30. ${APP}.ods_activity_info partition(dt='$do_date');
  31. 31. load data inpath '/origin_data/$APP/db/activity_order/$do_date' OVERWRITE into table
  32. 32. ${APP}.ods_activity_order partition(dt='$do_date');
  33. 33. load data inpath '/origin_data/$APP/db/cart_info/$do_date' OVERWRITE into table
  34. 34. ${APP}.ods_cart_info partition(dt='$do_date');
  35. 35. load data inpath '/origin_data/$APP/db/comment_info/$do_date' OVERWRITE into table
  36. 36. ${APP}.ods_comment_info partition(dt='$do_date');
  37. 37. load data inpath '/origin_data/$APP/db/coupon_info/$do_date' OVERWRITE into table
  38. 38. ${APP}.ods_coupon_info partition(dt='$do_date');
  39. 39. load data inpath '/origin_data/$APP/db/coupon_use/$do_date' OVERWRITE into table
  40. 40. ${APP}.ods_coupon_use partition(dt='$do_date');
  41. 41. load data inpath '/origin_data/$APP/db/favor_info/$do_date' OVERWRITE into table
  42. 42. ${APP}.ods_favor_info partition(dt='$do_date');
  43. 43. load data inpath '/origin_data/$APP/db/order_refund_info/$do_date' OVERWRITE into table
  44. 44. ${APP}.ods_order_refund_info partition(dt='$do_date');
  45. 45. load data inpath '/origin_data/$APP/db/order_status_log/$do_date' OVERWRITE into table
  46. 46. ${APP}.ods_order_status_log partition(dt='$do_date');
  47. 47. load data inpath '/origin_data/$APP/db/spu_info/$do_date' OVERWRITE into table
  48. 48. ${APP}.ods_spu_info partition(dt='$do_date');
  49. 49. load data inpath '/origin_data/$APP/db/activity_rule/$do_date' OVERWRITE into table
  50. 50. ${APP}.ods_activity_rule partition(dt='$do_date');
  51. 51. load data inpath '/origin_data/$APP/db/base_dic/$do_date' OVERWRITE into table
  52. 52. ${APP}.ods_base_dic partition(dt='$do_date');
  53. 53. "
  54. 54. sql2="
  55. 55. load data inpath '/origin_data/$APP/db/base_province/$do_date' OVERWRITE into table
  56. 56. ${APP}.ods_base_province;
  57. 57. load data inpath '/origin_data/$APP/db/base_region/$do_date' OVERWRITE into table
  58. 58. ${APP}.ods_base_region;
  59. 59. "
  60. 60. case $1 in
  61. 61. "first"){
  62. 62. $hive -e "$sql1"
  63. 63. $hive -e "$sql2"
  64. 64. };;
  65. 65. "all"){
  66. 66. $hive -e "$sql1"
  67. 67. };;
  68. 68. esac
  69. 69. 
  70. 复制代码

2）修改权限

  1. 1. chmod 770 hdfs_to_ods_db.sh
  2. 复制代码

3）初次导入

  1. 1. hdfs_to_ods_db.sh first 2020-03-10

4）每日导入

  1. 1. hdfs_to_ods_db.sh all 2020-03-11

5）测试数据是否导入成功

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

结束语
至此，数仓概论以及ODS、DWD层已经描述和搭建已经完成！博主将会在下一章开启DWD、DWS、DWT的数据清洗以及搭建的过程！