2、数据仓库电商项目（尚硅谷第二版）ODS层

最新推荐文章于 2024-04-28 14:19:02 发布

Jecky不姓码

最新推荐文章于 2024-04-28 14:19:02 发布

阅读量1.7k

点赞数 2

分类专栏：自己玩的大数据（练习）文章标签： hadoop

本文链接：https://blog.csdn.net/weixin_29057619/article/details/109843921

版权

自己玩的大数据（练习）专栏收录该内容

10 篇文章 11 订阅

订阅专栏

第二章建模！ODS层数据分析

上一章节：1、数据仓库电商项目（尚硅谷第二版）
下一章节：3、数据仓库电商项目（尚硅谷第二版）DWD上

本章需求：

1. 对用户行为数据，业务数据进行建模
2. 建立ODS层，将业务数据及行为数据按照表的形式存入HDFS中

注：按照需求自己要敲一遍，可以百度，但切记不要粘贴复制。

什么是建模？

目前数据处理可以分为两大类：联机事务处理OLTP(on-line transaction processing) 、联机分析处理OLAP（On-Line Analytical Processing）。OLTP是传统的关系型数据库的主要应用，主要是基本的、日常的事务处理，例如银行交易。OLAP是数据仓库系统的主要应用，支持复杂的分析操作，侧重决策支持，并且提供直观易懂的查询结果。二者的主要区别对比如下表所示。

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

-关系建模

在这里插入图片描述
关系模型如图所示，严格遵循第三范式（3NF），从图中可以看出，较为松散、零碎，物理表数量多，而数据冗余程度低。由于数据分布于众多的表中，这些数据可以更为灵活地被应用，功能性较强。关系模型主要应用与OLTP系统中，为了保证数据的一致性以及避免冗余，所以大部分业务系统的表都是遵循第三范式的。
图维度模型示意图
维度模型如图所示，主要应用于OLAP系统中，通常以某一个事实表为中心进行表的组织，主要面向业务，特征是可能存在数据的冗余，但是能方便的得到数据。

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

-维度建模

在维度建模的基础上又分为三种模型：星型模型、雪花模型、星座模型。这块建议参考以下博主资料：

1. 全方位解读星型模型,雪花模型及星座模型、

2. 数仓建模思想之星型模型、雪花模型、星座模型

在这里插入图片描述

维度表和事实表（必须搞懂的概念）

-维度表

-维度表：一般是对事实的描述信息。每一张维表对应现实世界中的一个对象或者概念，简单来说就是描述一件事务的下限，比如说：碳酸饮料，下面又百事，可乐，下面在分成价格和规格等这种情况。

-维度表：

1. 维表的范围很宽（具有多个属性、列比较多）

2. 跟事实表相比，行数相对较小：通常< 10万条

3. 内容相对固定：编码表

-时间维度表：

日期ID	day of week	day of year	季度	节假日
2020-01-01	2	1	1	元旦
2020-01-02	3	2	1	无
2020-01-03	4	3	1	无
2020-01-04	5	4	1	无
2020-01-05	6	5	1	无

-事实表

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

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

事实表特征：

1. 非常的大

2. 内容相对的窄：列数少

3. 经常发生变化，每天会新增加很多

1）事务型事实表

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

2）周期型快照事实表

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

3）累积型快照事实表

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

订单id	用户id	`下单时间`	`打包时间`	`发货时间`	`签收时间`	订单金额
		3-8	3-8	3-9	3-10

数仓分层（重点掌握）

-为什么要分层

在这里插入图片描述

-ODS层

写了半天终于到主角了，实在是累… …

ODS层是整个阶段最简单一层分层，主要职责是建立数据原始层，将数据都保存在HDFS上，经过步骤1我们建立出来了两个数据文件夹，一个是db，一个是log，分别代表业务数据、用户行为数据。

在这里插入图片描述
ODS层的特点：

（1）保持数据原貌不做任何修改，起到备份数据的作用。

（2）数据采用压缩，减少磁盘存储空间（例如：原始数据100G，可以压缩到10G左右）

（3）创建分区表，防止后续的全表扫描

实现需求

1、选择维度模型进行建模，选择星座模型进行整合。（完成）

2、将数据通过Hive保存到HDFS下。（进行ing）

步骤及思考：

我采用的环境是CDH，CDH下的LZO配置说过了，如果不使用CDH，使用Hive的话建议自己百度配置一下。这里不在强调赘述了。

ODS层用户行为数据

在用户行为数据中，需要创建两张表，一张是启动数据表，一张是事件行为表。在最后进行脚本的编写。

分解需求：当前需要建立两张表ods_start_log，ods_event_log，采用LZO压缩。落盘路径为：/warehouse/gamll/ods/ods_start_log

创建启动日志表ods_start_log

参考资料：

Hive_ Hive 建表语句详解

Hive下的LZO压缩

这里我选择了两天的进行测试~

CREATE DATABASE gmall;
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/gamll/ods/ods_start_log';

LOAD DATA INPATH '/origin_data_cdh/gmall/log/topic_start/2020-11-19' INTO TABLE ods_start_log PARTITION (dt='2020-11-19');
LOAD DATA INPATH '/origin_data_cdh/gmall/log/topic_start/2020-11-20' INTO TABLE ods_start_log PARTITION (dt='2020-11-20');

SELECT * FROM ods_start_log WHERE dt='2020-11-19' LIMIT 2;

SELECT * FROM ods_start_log WHERE dt='2020-11-20' LIMIT 2;

SELECT count(*) FROM ods_start_log WHERE dt='2020-11-20'

创建LZO索引：

sudo -u hdfs hadoop jar /opt/module/LZO/hadoop-lzo-0.4.15-cdh5.8.3.jar  com.hadoop.compression.lzo.LzoIndexer /warehouse/gamll/ods/ods_start_log/dt=2020-11-19

在这里插入图片描述

创建事件日志表ods_event_log

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/gamll/ods/ods_event_log';

LOAD DATA INPATH '/origin_data_cdh/gmall/log/topic_event/2020-11-19' INTO TABLE ods_event_log PARTITION (dt='2020-11-19');

SELECT * from ods_event_log WHERE dt='2020-11-19' LIMIT 2;


LOAD DATA INPATH '/origin_data_cdh/gmall/log/topic_event/2020-11-20' INTO TABLE ods_event_log PARTITION (dt='2020-11-20');

SELECT * from ods_event_log WHERE dt='2020-11-20' LIMIT 2;

SELECT count(*) FROM ods_event_log WHERE dt='2020-11-19';

创建LZO索引：

sudo -u hdfs hadoop jar /opt/module/LZO/hadoop-lzo-0.4.15-cdh5.8.3.jar  com.hadoop.compression.lzo.LzoIndexer /warehouse/gamll/ods/ods_event_log/dt=2020-11-19

第一部分出现了问题：

在这里插入图片描述

注（这块应该是sqoop提到的，刚才发现数据传输出现问题了~补上）：

创建LZO索引的方式参考：http://www.360doc.com/content/18/0411/15/52230779_744753094.shtml

下载链接：http://archive.cloudera.com/gplextras5/redhat/7/x86_64/gplextras/5.8.3/RPMS/x86_64/

安装完成后，LZO的jar包在：/usr/lib/hadoop/lib/hadoop-lzo-0.4.15-cdh5.8.3.jar

在这里插入图片描述
把jar包分发到各个节点上去：

创建脚本 hdfs_to_ods_log.sh

#!/bin/bash

# 定义变量方便修改
APP=gmall
# 如果是输入的日期按照取输入日期；如果没输入日期取当前时间的前一天
if [ -n "$1" ] ;then
   do_date=$1
else
   do_date=`date -d "-1 day" +%F`
fi
echo "===说明：ods层用户行为日志导入==="
echo "===日志日期为 $do_date==="
sql="
load data inpath '/origin_data_cdh/gmall/log/topic_start/$do_date' overwrite into table ${APP}.ods_start_log partition(dt='$do_date');

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

sudo -u hive hive -e "$sql"

#创建索引
echo "===说明：$do_date ods层  启动日志  创建LZO索引......==="
sudo -u hdfs hadoop jar /opt/module/LZO/hadoop-lzo-0.4.15-cdh5.8.3.jar  com.hadoop.compression.lzo.LzoIndexer /warehouse/gamll/ods/ods_start_log/dt=$do_date
echo "===说明：$do_date ods层  事件日志  创建LZO索引......==="
sudo -u hdfs hadoop jar /opt/module/LZO/hadoop-lzo-0.4.15-cdh5.8.3.jar  com.hadoop.compression.lzo.LzoIndexer /warehouse/gamll/ods/ods_event_log/dt=$do_date

再次创建日志，测试脚本是否可用：

在这里插入图片描述
启动成功！可以看到索引都出来了~

在这里插入图片描述

ODS层用户行为日志，日志文件—>HDFS—>ODS落盘成功！

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

ODS层业务数据

将数据库中的表数据通过HIve传输到ODS层中

电商表结构（24张表）：
在这里插入图片描述
这一块建议理解一下，建表语句最好自己写，虽然有些多，后面的可以复制。

思路如下：
我们将MySQL数据库中的业务数据通过sqoop全部罗盘到HDFS上，格式整齐，并且建立了LZO索引，现在我们想将这些数据通过Hive清洗成ODS层的数据，不严谨的说就是将MySQL上的表做成Hive上的表。

通过sqoop上传到HDFS的表
步骤很简单：Hive中建表—> load inpath 数据到此表中，再写一个脚本完工~ 当然写24张表。

建表

用到一些函数说明：

Hive中的DECIMAL类型：(Decimal)小数点

这里我们对DECIMAL类型做两点说明：

1）DECIMAL(9,8)代表最多9位数字，后8位是小数。此时也就是说，小数点前最多有1位数字，如果超过一位则会变成null。

2）如果不指定参数，那么默认是DECIMAL(10,0)，即没有小数位，此时0.82会变成1。

参考连接：数据分析EPHS(14)-Hive数据类型详解

这部分表并不是全部从MySQL中复制的，而是挑选符合的字段进行清洗使用，比如第一个表订单表。

订单表（增量及更新）

MySQL中的order_info

在这里插入图片描述

建表过程中筛选的字段，你会发现并不是所有的字段，数据清洗只挑选有用的字段。

drop TABLE IF EXISTS ods_order_info;
CREATE EXTERNAL TABLE ods_order_info(
id STRING COMMENT '订单号',
fina_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/gamll/ods/ods_oder_info';

表太多，站体复制吧~

订单详情表表（增量）

drop table if exists ods_order_detail;
create external table ods_order_detail( 
    `id` string COMMENT '订单编号',
    `order_id` string  COMMENT '订单号', 
    `user_id` string COMMENT '用户id',
    `sku_id` string COMMENT '商品id',
    `sku_name` string COMMENT '商品名称',
    `order_price` decimal(10,2) COMMENT '商品价格',
    `sku_num` bigint COMMENT '商品数量',
    `create_time` string 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_detail/';

SKU商品表（全量表）

drop table if exists ods_sku_info;
create external table ods_sku_info( 
    `id` string COMMENT 'skuId',
    `spu_id` string   COMMENT 'spuid', 
    `price` decimal(10,2) COMMENT '价格',
    `sku_name` string COMMENT '商品名称',
    `sku_desc` string COMMENT '商品描述',
    `weight` string COMMENT '重量',
    `tm_id` string COMMENT '品牌id',
    `category3_id` string COMMENT '品类id',
    `create_time` string COMMENT '创建时间'
) COMMENT 'SKU商品表'
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_sku_info/';

用户表（增量及更新）

drop table if exists ods_user_info;
create external table ods_user_info( 
    `id` string COMMENT '用户id',
    `name`  string COMMENT '姓名',
    `birthday` string COMMENT '生日',
    `gender` string COMMENT '性别',
    `email` string COMMENT '邮箱',
    `user_level` string COMMENT '用户等级',
    `create_time` string COMMENT '创建时间',
    `operate_time` string 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_user_info/';

商品一级分类表（全量）

drop table if exists ods_base_category1;
create external table ods_base_category1( 
    `id` string COMMENT 'id',
    `name`  string 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_base_category1/';

商品二级分类表（全量）

drop table if exists ods_base_category2;
create external table ods_base_category2( 
    `id` string COMMENT ' id',
    `name` string COMMENT '名称',
    category1_id string COMMENT '一级品类id'
) 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_base_category2/';

商品三级分类表（全量）

drop table if exists ods_base_category3;
create external table ods_base_category3(
    `id` string COMMENT ' id',
    `name`  string COMMENT '名称',
    category2_id string COMMENT '二级品类id'
) 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_base_category3/';

支付流水表（增量）

drop table if exists ods_payment_info;
create external table ods_payment_info(
    `id`   bigint COMMENT '编号',
    `out_trade_no`    string COMMENT '对外业务编号',
    `order_id`        string COMMENT '订单编号',
    `user_id`         string COMMENT '用户编号',
    `alipay_trade_no` string COMMENT '支付宝交易流水编号',
    `total_amount`    decimal(16,2) COMMENT '支付金额',
    `subject`         string COMMENT '交易内容',
    `payment_type`    string COMMENT '支付类型',
    `payment_time`    string 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_payment_info/';

省份表（特殊)

drop table if exists ods_base_province;
create external table ods_base_province (
    `id`   bigint COMMENT '编号',
    `name`        string COMMENT '省份名称',
    `region_id`    string COMMENT '地区ID',
    `area_code`    string COMMENT '地区编码',
    `iso_code` string COMMENT 'iso编码'
   )  COMMENT '省份表'
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_base_province/';

地区表（特殊）

drop table if exists ods_base_region;
create external table ods_base_region (
    `id`   bigint COMMENT '编号',
    `region_name`        string COMMENT '地区名称'
   )  COMMENT '地区表'
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_base_region/';

品牌表（全量）

drop table if exists ods_base_trademark;
create external table ods_base_trademark (
    `tm_id`   bigint COMMENT '编号',
    `tm_name` string 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_base_trademark/';

订单状态表（增量）

hive (gmall)>
drop table if exists ods_order_status_log;
create external table ods_order_status_log (
    `id`   bigint COMMENT '编号',
    `order_id` string COMMENT '订单ID',
    `order_status` string COMMENT '订单状态',
    `operate_time` string 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_status_log/';

SPU商品表（全量）

hive (gmall)>
drop table if exists ods_spu_info;
create external table ods_spu_info(
    `id` string COMMENT 'spuid',
    `spu_name` string COMMENT 'spu名称',
    `category3_id` string COMMENT '品类id',
    `tm_id` string COMMENT '品牌id'
) COMMENT 'SPU商品表'
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_spu_info/';

商品评论表（增量）

hive (gmall)>
drop table if exists ods_comment_info;
create external table ods_comment_info(
    `id` string COMMENT '编号',
    `user_id` string COMMENT '用户ID',
    `sku_id` string COMMENT '商品sku',
    `spu_id` string COMMENT '商品spu',
    `order_id` string COMMENT '订单ID',
    `appraise` string COMMENT '评价',
    `create_time` string 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_comment_info/';

退单表（增量）

hive (gmall)>
drop table if exists ods_order_refund_info;
create external table ods_order_refund_info(
    `id` string COMMENT '编号',
    `user_id` string COMMENT '用户ID',
    `order_id` string COMMENT '订单ID',
    `sku_id` string COMMENT '商品ID',
    `refund_type` string COMMENT '退款类型',
    `refund_num` bigint COMMENT '退款件数',
    `refund_amount` decimal(16,2) COMMENT '退款金额',
    `refund_reason_type` string COMMENT '退款原因类型',
    `create_time` string 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_refund_info/';

加购表（全量）

hive (gmall)>
drop table if exists ods_cart_info;
create external table ods_cart_info(
    `id` string COMMENT '编号',
    `user_id` string  COMMENT '用户id',
    `sku_id` string  COMMENT 'skuid',
    `cart_price` string  COMMENT '放入购物车时价格',
    `sku_num` string  COMMENT '数量',
    `sku_name` string  COMMENT 'sku名称 (冗余)',
    `create_time` string  COMMENT '创建时间',
    `operate_time` string COMMENT '修改时间',
    `is_ordered` string COMMENT '是否已经下单',
    `order_time` string  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_cart_info/';

商品收藏表（全量）

hive (gmall)>
drop table if exists ods_favor_info;
create external table ods_favor_info(
    `id` string COMMENT '编号',
    `user_id` string  COMMENT '用户id',
    `sku_id` string  COMMENT 'skuid',
    `spu_id` string  COMMENT 'spuid',
    `is_cancel` string  COMMENT '是否取消',
    `create_time` string  COMMENT '收藏时间',
    `cancel_time` string  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_favor_info/';

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

hive (gmall)>
drop table if exists ods_coupon_use;
create external table ods_coupon_use(
    `id` string COMMENT '编号',
    `coupon_id` string  COMMENT '优惠券ID',
    `user_id` string  COMMENT 'skuid',
    `order_id` string  COMMENT 'spuid',
    `coupon_status` string  COMMENT '优惠券状态',
    `get_time` string  COMMENT '领取时间',
    `using_time` string  COMMENT '使用时间(下单)',
    `used_time` string  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_coupon_use/';

优惠券表（全量）

hive (gmall)>
drop table if exists ods_coupon_info;
create external table ods_coupon_info(
  `id` string COMMENT '购物券编号',
  `coupon_name` string COMMENT '购物券名称',
  `coupon_type` string COMMENT '购物券类型 1 现金券 2 折扣券 3 满减券 4 满件打折券',
  `condition_amount` string COMMENT '满额数',
  `condition_num` string COMMENT '满件数',
  `activity_id` string COMMENT '活动编号',
  `benefit_amount` string COMMENT '减金额',
  `benefit_discount` string COMMENT '折扣',
  `create_time` string COMMENT '创建时间',
  `range_type` string COMMENT '范围类型 1、商品 2、品类 3、品牌',
  `spu_id` string COMMENT '商品id',
  `tm_id` string COMMENT '品牌id',
  `category3_id` string COMMENT '品类id',
  `limit_num` string COMMENT '最多领用次数',
  `operate_time`  string COMMENT '修改时间',
  `expire_time`  string 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_coupon_info/';

活动表（全量）

hive (gmall)>
drop table if exists ods_activity_info;
create external table ods_activity_info(
    `id` string COMMENT '编号',
    `activity_name` string  COMMENT '活动名称',
    `activity_type` string  COMMENT '活动类型',
    `start_time` string  COMMENT '开始时间',
    `end_time` string  COMMENT '结束时间',
    `create_time` string  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_activity_info/';

活动订单关联表（增量）

hive (gmall)>
drop table if exists ods_activity_order;
create external table ods_activity_order(
    `id` string COMMENT '编号',
    `activity_id` string  COMMENT '优惠券ID',
    `order_id` string  COMMENT 'skuid',
    `create_time` string  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_activity_order/';

优惠规则表（全量）

hive (gmall)>
drop table if exists ods_activity_rule;
create external table ods_activity_rule(
    `id` string COMMENT '编号',
    `activity_id` string  COMMENT '活动ID',
    `condition_amount` string  COMMENT '满减金额',
    `condition_num` string  COMMENT '满减件数',
    `benefit_amount` string  COMMENT '优惠金额',
    `benefit_discount` string  COMMENT '优惠折扣',
    `benefit_level` string  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_activity_rule/';

编码字典表（全量）

hive (gmall)>
drop table if exists ods_base_dic;
create external table ods_base_dic(
    `dic_code` string COMMENT '编号',
    `dic_name` string  COMMENT '编码名称',
    `parent_code` string  COMMENT '父编码',
    `create_time` string  COMMENT '创建日期',
    `operate_time` string  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_base_dic/';

ODS层加载数据脚本

1）创建脚本hdfs_to_ods_db.sh

vim hdfs_to_ods_db.sh

在脚本中填写如下内容

#!/bin/bash

APP=gmall
hive=/opt/module/hive/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"
   sudo -u hive  hive -e "$sql1"
   sudo -u hive  hive -e "$sql2"
};;
"all"){
#$hive -e "$sql1"
   sudo -u hive  hive -e "$sql1"
};;
esac

ODS层到此位置，让我歇歇~
因为业务数据都是规则的，字段都是做好的，都是MySQL中的，所以后面的内容都是对用户行为日志进行清洗，简单来说，就是把一串一串的数据，整齐划一的做成需要的表，然后从这些表中进行select。

补充一下啊，脚本执行：

sh hdfs_to_ods_db.sh first 2020-03-29   
#first 指的是第一次导入全部表，后面的话就直接用all
sh hdfs_to_ods_db.sh all 2020-03-29

补充：

拿order_info举例，因为Sqoop导入的数据：

在这里插入图片描述
所以我们导入数据的时候它会按照格式进行按坑就位~：

LOAD DATA INPATH '/origin_data_cdh/gmall/db/order_info/2020-11-19' overwrite into table test.ods_order_info_test PARTITION (dt='2020-11-19');

Jecky不姓码

关注

2
点赞
踩
26

收藏

觉得还不错? 一键收藏
4
评论
2、数据仓库电商项目（尚硅谷第二版）ODS层

第二章建模！ODS层数据分析本章需求：       1. 对用户行为数据，业务数据进行建模      2. 建立ODS层，将业务数据及行为数据按照表的形式存入HDFS中 注：按照需求自己要敲一遍，可以百度，但切记不要粘贴复制。什么是建模？      目前数据处理可以分为两大类：联机事务处理OLTP(
复制链接

扫一扫