电商数仓系统
一、数仓分层
1.1 数仓简介
--1. 数据仓库和数据库的区别
1. 数据仓库：
a、数据量特别的大，TB-PB级别
b、会保留历史数据
c、一般使用OLAP分析，(on-line analytical processing) 联机分析处理(聚合操作) ，多维分析
d、会有重复的数据
2. 数据库：
a、数据量：GB-TB
b、不保留历史数据
c、一般使用OLTP(on-line transaction processing) 联机事物处理(增删改查)
d、传统的数据
-- 2. 如何创建数据仓库？
a、如何创建表
b、如何对数据进行分层
-- 3. 数据仓库各层的介绍
数仓一共分成5层，每层的作用如下：
第一层：'ods，operation data store，存放原始数据'，将hdfs上的数据原封不动的存储到ods层。
第二层：'dwd，data warehouse detail ，明细数据层'，对ods层的数据进行清洗，如去除空值，脏数据、脱敏
（用于保护用户的隐私），维度退化。
第三层：'dws ： data warehouse service，数据服务层'，以dwd为基础，按天统计，进行轻度的汇总
第四层：'dwt：data warehouse topic，数据主题层'，以dws层为基础，按主题进行汇总。
第五层：'ads，application data service,数据应用层'，以dwt为基础，为各种报表提供数据。
各层具体的实现方式、作用在后续进行详细的介绍。
-- 4. 数仓为什么要进行分层：
1. 将复杂的问题简单化；
a、复杂任务分解成多层来实现；
b、每一层处理一些简单的任务；
c、方便定位问题。
2. 减少重复开发
a、中间层的数据，可以重复利用到不同的需求
b、如果计算出错，不需要重头开始计算
3. 隔离原始数据
1.2 数据集市
数仓是面对企业级别，而数据集市是面向企业中的某一个部门。
1.3 数仓命名规范
命名规范实现见名知意
1.3.1 表命名
-- 1. 原则：通过表名知道数据位于数仓哪一层。
-- 2. 规范:
1、ods层：'ods_表名'
2、dwd层：'dwd_fact/dim_表名' --fact：事实表，dim：维度表
3、dws层：'dws_表名'
4、dwt层: 'dwt_表名'
5、ads层：'ads_表名'
6、临时表：'xxx_tmp'
7、用户行为表，'以log为后缀'
1.3.2 脚本命名
-- 1. 规则：
数据源_to_目的地_log/db.sh
-- 2. 说明：
a、用户行为脚本使用'log'为后缀；
b、业务数据脚本使用'db'为后缀
1.3.3 表字段
1. 数量类型：bigint
2. 金额类型：decimal（16,2），'小数类型，保留两位小数'
3. 字符串(如名字，描述信息等)类型：string
4. 主键外键类型：string
5. 时间戳类型：bigint
二、数仓理论
2.1 关系建模
-- 1. 什么是关系型数据库
设计关系型数据库时，遵守一定的规范要求，目的在于降低数据的冗余。
-- 2. 问题：
问题1：为什么要降低数据冗余？
问题2：关系型数据库的缺点是什么？
问题3：关系型数据库的特点是什么？
问题4：什么规范要求？
- 问题解析
-- 问题1：为什么要降低数据冗余？
1. 在2000年开始的时候，磁盘价格很昂贵，为了减少磁盘的存储
2. 以前没有分布式系统，都是单机，只能增加磁盘的方式进行扩容
-- 问题2 ：关系型数据库的缺点是什么？
1. 修改一个数据，需要修改多个表，很难保证数据的一致性；
2. 由于数据解耦合性很高，所以获取数据时，都是通过join拼接得到最后的结果。
-- 问题3： 关系型数据库的特点，见下图
1. 数据较为松散、零碎，物理表数量多，而数据冗余程度低。
-- 问题4： 需要遵守的规范
范式理论，什么是范式理论呢？见下小节分析。
2.2 范式理论
在学习范式理论之前，先说说函数的依赖，因为范式理论和函数的依赖有关系。
2.2.1 函数的依赖
-- 1. 函数依赖的分类
1. 完全函数依赖
2. 部分函数依赖
3. 传递函数依赖
-- 2. 各种函数依赖是什么意思呢？使用高中的函数进行类比
1. 完全函数依赖
a、函数：z = f(x,y)
b、说明：z完全依赖于x和y，只有知道x和y的值，才能求出z
2. 部分函数依赖
a、函数：z = f(x,y) = x + 2
b、说明：只通过x就可以得到z的值，可以不需要y
3. 传递函数依赖
a、函数：y = f(x) , z = f(y)
b、说明：通过x得到y，通过y的值，得到z，但是通过z不能得到x
-- 3. 在关系型数据库中如上3个函数依赖关系体现如下：
1. 完全函数依赖：'学号和课名是主键'
通过{学号 、 课名} ，得到唯一的分数，则'分数完全依赖于学号和课名'
2. 部分函数依赖：'学号和课名是主键'
通过{学号，课名} 得到唯一的姓名，但是不需要课名也可以，则'姓名部分依赖于学号和课名'
3. 传递函数依赖：
通过学号，可以找到学号所在的系名。通过系名，找到系主任，则'系主任传递依赖于学号'
2.2.2 范式理论
-- 1. 范式分类：
目前业界范式有：第一范式(1NF)、第二范式(2NF)、第三范式(3NF)、巴斯-科德范式(BCNF)、第四范式(4NF)、第五范式(5NF)
-- 2. 在行业内，目前大都是遵守三范式
'第一范式'：属性不可切割
如果数据表的设计不满足这个基本要求，那么操作一定是不会成功的。
'第二范式'：不能出现部分函数依赖
通过分表的方式，去除部分依赖
'第三范式'：不能出现传递函数依赖
2.3 关系建模与维度建模
'建模就是创建表'
-- 1. 关系建模和维度建模的区别：
'关系建模'：
a、关系建模依赖于范式原理'主要是三范式'设计原则；
b、侧重数据的事务性；
c、一张表的数据达到了500G以上以后，效率比较低 --'行业的标准'
'维度建模'：
a、顾名思义，按照维度建模的思想进行设计表；
b、多角度进行分析；
c、表结构是：中间一个事实表，周围是维度表，如下图所示
2.4 维度建模
'维度建模是由kimball创建'。
-- 1. 维度建模的模型分类：
a、星型模型：
b、雪花模型：一张事实表, 多个多层维度表
c、星座模型
-- 2. 各种模型区别：
a、星型模型:一张事实表, 多个一层维度表
b、雪花模型:一张事实表, 多个多层维度表，比较靠近第三范式
c、星座模型:基于多个事实表，多个事实表之间有共同的维度表
-- 3. 维度建模模型的选择：
a、'原则'：取决于性能更优还是灵活更优。星型模型性能更优，雪花模型灵活更优。
b、'选择'：当前是更倾向于维度更少的模型，尤其是在hadoop的体系中，减少join就是减少shuffle
c、关系型数据库可以依靠强大的主键来优化效率。
-- 4. 维度建模的优势
a、表结构清晰，容易上手
b、存在数据的冗余，查询速度快
c、使用于多维分析
-- 5. 不同模型的图示
2.5 事实表和维度表（重点）
2.5.1 简介
-- 1. 如何理解维度表和事实表？
a、'事实表'：根据业务来的，一个业务建成事实表。
以电商为例：收藏、评论、加购物车、下单、支付、领用优惠券、退款等业务
b、'维度表'：对事实表的描述信息
以电商为例：时间、地点、用户、商品、活动、优惠券
-- 2. 举例：
事实表：支付业务
描述：2020年06月26号 张三 在北京 参加618活动 买了 1台苹果手机
'时间' '用户' '地点' '活动' '商品'
2.5.2 维度表
-- 1. 维度表是为事实表服务
-- 2. 特征：
a、维度表的范围很宽，具有多个属性，列比较宽
b、与事实表比，行数比较少，< 10万条
c、内容相对比较固定：如编码表，地区表
2.5.3 事实表
-- 1. 事实表中的每行数据代表一个业务事件，如下单操作、支付、退款、评价等
-- 2. 特征：
a、数据量大
b、内容相对窄，列数量较少,行数量很大
c、经常性变化，每天都会有新增很多数据
-- 3. 结构：
由'度量值字段'（1个及以上） + 与'维度表关联的外键字段'（2个及以上） + 冗余字段
-- 4. 度量值
分为三种类型：
a、'可加'：使用任意维度均可加
b、'半可加'：该度量值只能在某些维度可加
如: 地点维度可以加, 但是在时间维度没意义
c、'不可加'：在任何的维度都不可加
如: 比率 10% 20%
100 10 10%
90 10 1/9
这类度量值的处理方式：变成可加, 把度量拆分, 分子分母进行拆分变成可加
2.5.4 事实表的分类
-- 1. 事实表的分类：
a、事务型事实表
b、周期性快照事实表
c、累积性快照事实表
-- 2. 事务型事实表
a、'数据更新方式'：'增量更新'，只增加新的数据，不会对旧的数据类型进行更改
b、'应用场景'：只有新增的数据，旧数据不会变更的事实表采用这种类型
-- 3. 周期型快照事实表
a、'数据更新方式'：'全量'，每天一个快照
b、'应用场景'：只关心最终的一个结果，不关心中间的业务操作过程，如收藏表和加购表，只关心用户购物车中产品，
而不关心用户加入购物的过程，比如上午加了一个产品到购物车中，下午又将这个产品从购物车删除，我们不关心这个过程。
-- 4. 累积型快照事实表
a、'数据更新方式'：'新增及变化'
b、'应用场景'：一条数据一次写不完，需要多次才能写完，如订单事实表，订单的状态，有从下单到最后结束订单一共有6个状态。
2.6 数据仓库建模（绝对的重点）
数据仓库建模主要是指dwd层
2.6.1 ODS层
-- 1. 说明：
1. 数据和hdfs上数据保持一致，不做任何的修改，起到数据备份作用
2. 数据采用lzo压缩，需要索引文件，mysql导入hdfs时，自带索引文件，log文件不带索引文件，所以在ods层需要自己加索引。
3. 创建分区表
4. '同步策略与mysql和log数据存储到HDFS的同步策略保持一致'
-- 2. 原始数据采集到HDFS上的策略是什么？又是如何实现的？
1. 'log文件'：
a、每天一个文件夹，flume采集以后的数据就直接发送到HDFS上，
b、'实现每天一个文件夹的方法'：如下为最后一个flume的配置文件，每天的数据，
会被放进一个当天日期的文件夹中。
--a1.sinks.k1.hdfs.path = /origin_data/gmall/log/topic_log/%Y-%m-%d
c、'压缩'：文件夹中的数据采用lzo压缩，'同时没有索引文件'
d、'每个文件夹中的数据'：当天产生的日志数据
2. 'db业务数据'
a、对于mysql中的每一张表，每天导一次，数据就会创建一个lzo压缩 + lzo索引的文件夹,'除特殊表，省份表、地区表'
b、同一张表，每天导的数据会进入当天的文件夹的方法：在sqoop导数据的脚本中：
--target-dir /origin_data/gmall/db/$1/$do_date
c、'当天的文件中存储的数据是什么'？
通过sqoop将mysql导入到hdfs中，根据同步策略的不同，当天文件夹中存储的数据不同，具体如下：
以2020-06-25导入为例：
a、'全量同步策略'：2020-06-25，mysql中所有的数据
b、'新增及变化策略'：今天mysql中新增和变化了的全部数据
c、'新增同步策略'：今天mysql新增的全部数据
d、'每张表的同步策略'及'同步策略选择原则'见下图所示。
- msql数据的同步策略
-- 3. ods层数据和HDFS的数据保持一致，那么具体的数据内容是怎么做的呢？
'ods层的表属于分区表'。
-- 创建表
1. log数据：创建一个字段为line，分区字段为dt
2. db数据：字段和mysql的字段保持一致,分区字段为dt
-- ods_log加载数据：
load data inpath '/origin_data/gmall/log/topic_log/$do_date' into table "$APP".ods_log partition(dt='$do_date')
-- ods_db加载数据：
load data inpath '/origin_data/$APP/db/order_detail/$do_date' OVERWRITE into table ${APP}.ods_order_detail partition(dt='$do_date')
2.6.2 DWD 层 (重点)
-- 1. 说明：DWD层是数仓维度建模最核心的部分，我们一般是采用星型模型，由于存在多个事实表，其实也就形成了星座模型
-- 2. 建模的4个步骤：
选择业务过程 -> 声明粒度 -> 确定维度 -> 确认事实
-- 3. 选择业务过程： --确定事实表
1. 选择业务过程：确定事实表，每一个业务创建一个事实表。
-- 4. 声明粒度： -- 声明事实表的粒度
1. 声明粒度，其实是声明事实表的粒度
2. 什么是粒度？
a、是一个形容词，表示表中的一行数据保存数据细化程度或综合程度
3. 声明粒度的过程：
第一步：找到和业务相关的所有表
第二步：确定这些相关表每一行的粒度
第三步：'选择粒度最细'的表作为事实表。
4. 怎么算是最细的呢？
外键越多，粒度越细；外键越少，粒度越细。--事实表的维度越多，外键越多。
'典型的粒度声明如下'：
订单详情表中，每行数据对应一个订单中的一个商品项，粒度为商品
订单表中，每行数据对应一个订单。粒度为订单
此时下单业务中，商品为最细粒度。
5. 为什么要选择粒度最细的表作为事实表呢？
事实表存储明细数据，不存储聚合数据，而需求一般都需要对数据进行聚合的运算，这样就可以应对各种需求。
-- 5. 确定维度：
确定每一张事实表的维度，外键。
-- 6. 确定事实
指确定事实表的度量值。
'看完上面的步骤，是不是还是有些模糊呢？没关系，后面咱们建DWD表的时候，详细讲述我们的表是怎么建的'
-- 7. 关于事实表几个原则：
1. 每一个业务都需要建一个事实表
2. 一个事实表中的粒度要一致
3. 度量值的单位要一致
4. 使用维度冗余，来减少join的次数
5. null值，如果是数值类型的数据，那么使用0来代替
2.6.3 DWS&DWT
-- 1. dws层和dwt层的表，都是宽表
-- 2. dws层宽表如何建？站在维度的角度看待事实表的度量值。
a、每一个维度建一个宽表
b、宽表的字段：维度字段 + 找到和这个维度表相关的所有事实表，事实表的所有度量值作为宽表的字段，当然也可以根据需求选择
部分的字段，由于我们最终是为客户的需求服务，能写上就尽量写上。
-- 3. dws：统计当天的行为数据，分区表，按天进行分区
-- 4. dwt：统计累计行为，站在维度的角度，来看事实表，看事实表的开始时间，结束时间，总累积的度量值，一段时间内的度量值。
如统计累计7天的下单量，不是分区表。属于全量表。
2.6.4 ADS
-- ADS层
对电商系统各大主题指标进行分析
三、数仓搭建 —ODS层
3.1 Hive环境准备
3.1.1 hive的介绍
-- 1. Hive的引擎
a、 mr：计算周、月、季度、年的指标时使用mr计算引擎
b、 tez：用于测试使用，完全基于内存
c、 spark：基于内存和磁盘，用于计算日指标
-- 2. hive on spark 及 spark on hive 的区别
a、hive on spark：表示hive使用spark的引擎
b、spark on hive：可以理解为sparkSql
3.1.2 Hive on Spark 的配置
- 解压spark-2.4.5-bin-without-hive.tgz，并改名
[atguigu@hadoop102 software]$ tar -zxf /opt/software/spark-2.4.5-bin-without-hive.tgz -C /opt/module
[atguigu@hadoop102 software]$ mv /opt/module/spark-2.4.5-bin-without-hive /opt/module/spark
- 配置SPARK_HOME环境变量
[atguigu@hadoop102 software]$ sudo vim /etc/profile.d/my_env.sh
- 增加如下内容
export SPARK_HOME=/opt/module/spark
export PATH=$PATH:$SPARK_HOME/bin
- 使其生效
[atguigu@hadoop102 software]$ source /etc/profile.d/my_env.sh
- 配置spark运行环境
[atguigu@hadoop102 software]$ mv /opt/module/spark/conf/spark-env.sh.template /opt/module/spark/conf/spark-env.sh
[atguigu@hadoop102 software]$ vim /opt/module/spark/conf/spark-env.sh
- 添加如下内容
export SPARK_DIST_CLASSPATH=$(hadoop classpath)
- 新建spark配置文件
[atguigu@hadoop102 software]$ vim /opt/module/hive/conf/spark-defaults.conf
- 添加如下内容
spark.master yarn
spark.eventLog.enabled true
spark.eventLog.dir hdfs://hadoop102:8020/spark-history
spark.executor.memory 1g
spark.driver.memory 1g
- 在HDFS创建如下路径
[atguigu@hadoop102 software]$ hadoop fs -mkdir /spark-history
- 上传Spark依赖到HDFS
[atguigu@hadoop102 software]$ hadoop fs -mkdir /spark-jars
[atguigu@hadoop102 software]$ hadoop fs -put /opt/module/spark/jars/* /spark-jars
- 修改hive-site.xml
<!--Spark依赖位置-->
<property>
<name>spark.yarn.jars</name>
<value>hdfs://hadoop102:8020/spark-jars/*</value>
</property>
<!--Hive执行引擎-->
<property>
<name>hive.execution.engine</name>
<value>spark</value>
</property>
<!--Hive和spark连接超时时间-->
<property>
<name>hive.spark.client.connect.timeout</name>
<value>10000ms</value>
</property>
注意：hive.spark.client.connect.timeout的默认值是1000ms，如果执行hive的insert语句时，抛如下异常，可以调大该参数到10000ms
FAILED: SemanticException Failed to get a spark session: org.apache.hadoop.hive.ql.metadata.HiveException: Failed to create Spark client for Spark session d9e0224c-3d14-4bf4-95bc-ee3ec56df48e
3.1.3 Hive on spark测试
#1）启动hive客户端
[atguigu@hadoop102 hive]$ bin/hive
#2）创建一张测试表
hive (default)> create external table student(id int, name string) location '/student';
#3）通过insert测试效果
hive (default)> insert into table student values(1,'abc');
#4）如果插入数据过程/tmp/atguigu/hive.log文件中抛如下异常
Caused by: javax.security.sasl.SaslException: Server closed before SASL negotiation finished.
#5）修改/opt/module/hadoop-3.1.3/etc/hadoop/capacity-scheduler.xml中am启动的最大资源配置。分发、并重新启动resourcemanager
[atguigu@hadoop102 hadoop]$ vim capacity-scheduler.xml
<property>
<name>yarn.scheduler.capacity.maximum-am-resource-percent</name>
<value>1</value>
</property>
3.1.4 增加Hive队列
- 修改容量调度器配置文件
默认Yarn的配置下，容量调度器只有一条Default队列。在capacity-scheduler.xml中可以配置多条队列。
- 修改以下属性，增加hive队列。
<property>
<name>yarn.scheduler.capacity.root.queues</name>
<value>default,hive</value>
<description>
The queues at the this level (root is the root queue).
</description>
</property>
<property>
<name>yarn.scheduler.capacity.root.default.capacity</name>
<value>50</value>
<description>
default队列的容量为50%
</description>
</property>
- 同时为新加队列添加必要属性：
<property>
<name>yarn.scheduler.capacity.root.hive.capacity</name>
<value>50</value>
<description>
hive队列的容量为50%
</description>
</property>
<property>
<name>yarn.scheduler.capacity.root.hive.user-limit-factor</name>
<value>1</value>
<description>
一个用户最多能够获取该队列资源容量的比例
</description>
</property>
<property>
<name>yarn.scheduler.capacity.root.hive.maximum-capacity</name>
<value>80</value>
<description>
hive队列的最大容量
</description>
</property>
<property>
<name>yarn.scheduler.capacity.root.hive.state</name>
<value>RUNNING</value>
</property>
<property>
<name>yarn.scheduler.capacity.root.hive.acl_submit_applications</name>
<value>*</value>
<description>
访问控制，控制谁可以将任务提交到该队列
</description>
</property>
<property>
<name>yarn.scheduler.capacity.root.hive.acl_administer_queue</name>
<value>*</value>
<description>
访问控制，控制谁可以管理(包括提交和取消)该队列的任务
</description>
</property>
<property>
<name>yarn.scheduler.capacity.root.hive.acl_application_max_priority</name>
<value>*</value>
<description>
访问控制，控制用户可以提交到该队列的任务的最大优先级
</description>
</property>
<property>
<name>yarn.scheduler.capacity.root.hive.maximum-application-lifetime</name>
<value>-1</value>
<description>
hive队列中任务的最大生命时长
</description>
</property>
<property>
<name>yarn.scheduler.capacity.root.hive.default-application-lifetime</name>
<value>-1</value>
<description>
hive队列中任务的默认生命时长
</description>
</property>
- 分发配置文件
[atguigu@hadoop102 ~]$ xsync /opt/module/hadoop-3.1.3/etc/hadoop/capacity-scheduler.xml
- 重启Hadoop集群
3.1.5 创建数据库
1）启动hive
[atguigu@hadoop102 hive]$ bin/hive
2）显示数据库
hive (default)> show databases;
3）创建数据库
hive (default)> create database gmall;
4）使用数据库
hive (default)> use gmall;
3.2 ODS层(用户行为数据)
3.2.1 行为数据说明
-- 1. 用户行为数据在ods创建表的说明：
1. 维度建模不涉及到用户行为数据
2. 一行用户行为数据代表：用户在该页面的所有操作，以页面为单位
3. 在hdfs上的用户行为数据采用了lzo压缩，所以hive读取数据时，需要采用指定文件的输入格式：
INPUTFORMAT 'com.hadoop.mapred.DeprecatedLzoTextInputFormat'
4. 由于hive的数据最终都是存储在HDFS，所以也指定了输出文件的格式，来降低数据的存储
OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
5. ods层的数据，采用分区表，以日期为分区字段，一天一个分区，每个分区存储当天最新数据
6. 建表时，将一行数据作为一列存储到ods层的表中；
7. 加载数据，如果是加载hdfs上的数据到hive表，其实是实现数据的剪切。
8. 队列的切换，特定的业务使用特定的队列
9. hive使用spark引擎，资源调度还是使用yarn，默认的调度器是容量调度器，capacity
10. 脚本执行时间,企业开发中一般在每日凌晨30分~1点
-- 2. 日志数据时间说明：
'问题'：在实际生产过程中，前一天的数据有可能进入到第二天中，
'原因'：数据采集过程有关，日志数据采集到HDFS上的过程：
日志数据（时间戳） -> 日志服务器 -> flume1 -> kafka -> flume2 -> HDFS
flume2采集数据时，使用kafka source，这个source会给event加上时间戳，这个时间默认是系统时间，kafka会给event的header
加上时间戳,由于数据传递到第二个flume需要时间，如果在传递的过程中出现了故障，那么在flume2中，event的header的时间可能
就是第二天的。
'处理措施'：自定义拦截器，通过获取日志数据的时间戳作为event的header，就可以保证数据的一致性。
-- 3. 关于索引文件的说明：
hive中的数据是存储在HDFS上，如果采用了列式存储+ lzo，不需要创建索引文件，如果没有采用列式存储，
那么需要给它lzo文件建索引。由于ods层的数据没有采用列式存储，所以需要建索引文件。
1. 'hdfs上的log数据'：因为hdfs上的log数据没有索引，所以ods文件读取log日志数据时，也没有索引文件，所以即使lzop支持切
片,没有索引文件也不能实现切片，因此需要单独给ods的ods_log数据建索引， 由于我们通过flume采集到HDFS上的数据，
一个文件的大小不会超，但是还是加上。
2. 'hdfs上的mysql数据'：mysql的数据通过sqoop导入到hdfs时，自带索引文件，所以ods从hdfs上读取mysql文件时，
则不需要再单独建索引文件。
3.2.2 创建并加载日志表：ods_log
- 创建支持lzo压缩的分区表
hive (gmall)>
drop table if exists ods_log;
CREATE EXTERNAL TABLE ods_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_log';
- 加载数据
hive (gmall)>
load data inpath '/origin_data/gmall/log/topic_log/2020-06-25' into table ods_log partition(dt='2020-06-25');
- 查看是否加载成功
hive (gmall)> select * from ods_log limit 2;
- 为lzo压缩文件创建索引
[atguigu@hadoop102 bin]$ hadoop jar /opt/module/hadoop-3.1.3/share/hadoop/common/hadoop-lzo-0.4.20.jar com.hadoop.compression.lzo.DistributedLzoIndexer -Dmapreduce.job.queuename=hive /warehouse/gmall/ods/ods_log/dt=2020-06-25
3.2.3 单引号和双引号
-- 1. 在shell中， 单引号和双引号的区别：
a、共同点：表示字符串
b、不同点：
1. 如果最外层是'双引号'，则内部的变量值或运算'会执行'
2. 如果最外层是'单引号'，则内部的变量值或运算'不会执行'
3. 反引号：表示执行命令
-- 2. 例子：
abc=10
--双引号的说明：
b="a$abc" =>b=a10
b="a'$abc'" =>b=a'10'
--单引号的说明
b='a$abc' =>b=a$abc
b='a"$abc"' =>b=a"$abc"
3.2.4 ODS层加载log数据脚本
-- 1. 说明：
1. 脚步需要考虑导入哪一天的数据，在实际开发中一共有两种情况：
'情况1'：导入前一天的数据 --默认情况是导入前一天的数据
'情况2'：导入指定日期的数据 --当前几天的数据导入异常时，需要重新导指定某一天的数据
2. hive 命令用法：如下两种方式都不用进入hive的客户端就可以执行
a. hive -e + "sql" ：表示执行sql语句，'这个双引号不能省略'
b. hive -f + 指定文件中的sql语句
3. 关于date说明
date + %F =>获取date的年月日，格式为：2020-06-27
date -d "-1 day" +%F =>返回date日期的前一天，2020-06-26
- 日期的使用案例
[atguigu@hadoop102 module]$ date
Sat Jun 27 16:36:29 CST 2020
[atguigu@hadoop102 module]$ date +%F
2020-06-27
[atguigu@hadoop102 module]$ date -d "-1 day" +%F
2020-06-26
[atguigu@hadoop102 module]$ date -d "yesterday" +%F
2020-06-26
- 在hadoop102的/home/atguigu/bin目录下创建脚本，
注意脚本的名字,需要符合命名规范
[atguigu@hadoop102 bin]$ vim hdfs_to_ods_log.sh
- 编辑脚本内容
#!/bin/bash
# 定义变量方便修改
APP=gmall
hive=/opt/module/hive/bin/hive
hadoop=/opt/module/hadoop-3.1.3/bin/hadoop
# 如果是输入的日期按照取输入日期；如果没输入日期取当前时间的前一天
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_log/$do_date' into table "$APP".ods_log partition(dt='$do_date');
"
$hive -e "$sql"
$hadoop jar /opt/module/hadoop-3.1.3/share/hadoop/common/hadoop-lzo-0.4.20.jar com.hadoop.compression.lzo.DistributedLzoIndexer -Dmapreduce.job.queuename=hive /warehouse/gmall/ods/ods_log/dt=$do_date
- 脚本说明
[ -n 变量值 ] 判断变量的值，是否为空
-- 变量的值，非空，返回true
-- 变量的值，为空，返回false
[ -z 变量值 ] 判断变量的值，是否为空
-- 变量的值，非空，返回false
-- 变量的值，为空，返回true
注意：[ -n 变量值 ]不会解析数据,使用[ -n 变量值 ]时，需要对变量加上双引号(" ")
- 增加脚本执行权限
[atguigu@hadoop102 bin]$ chmod 777 ods_log.sh
- 查看导入数据
hive (gmall)>
select * from ods_log where dt='2020-06-15' limit 2;
3.3 ODS层业务数据导入
3.3.1 业务数据说明
-- 1. 说明
1. ods层的业务数据，建表字段和mysql字段完全一致，只需要加一个dt的分区字段，按照天进行分区。
2. 分隔符需要和sqoop导入时的分割符一致，‘\t’
3. 由于数据是每天都需要导一次，但是有两张表：地区表和省份表只要导一次就可以
4. 需要深刻了解每张表中，每个分区是什么数据、每行数据代表什么意思
-- 2. 脚本说明
1. 由于地区表和省份表只要第一次导入就可以，后续就不要再导入了。在脚本中，使用first来表示第一次导入，all表示其他导入
2. 同样指定数据导入的日期，如果传日期了，则表示导入指定日期的数据，否则导入前一天的数据。
3.3.2 建表语句
-- 3.3.1 订单表（增量及更新）
-- hive (gmall)>
drop table if exists ods_order_info;
create external table ods_order_info (
`id` string COMMENT '订单号',
`final_total_amount` decimal(16,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(16,2) COMMENT '优惠金额',
`original_total_amount` decimal(16,2) COMMENT '原价金额',
`feight_fee` decimal(16,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/';
-- '\t'与生成数据脚本的一致都是'\t'
-- 压缩格式是lzop
-- 3.3.2 订单详情表（增量）
-- hive (gmall)>
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(16,2) COMMENT '商品价格',
`sku_num` bigint COMMENT '商品数量',
`create_time` string COMMENT '创建时间',
`source_type` string COMMENT '来源类型',
`source_id` 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/';
-- 3.3.3 SKU商品表（全量）
-- hive (gmall)>
drop table if exists ods_sku_info;
create external table ods_sku_info(
`id` string COMMENT 'skuId',
`spu_id` string COMMENT 'spuid',
`price` decimal(16,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/';
-- 3.3.4 用户表（增量及更新）
-- hive (gmall)>
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/';
-- 3.3.5 商品一级分类表（全量）
-- hive (gmall)>
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/';
-- 3.3.6 商品二级分类表（全量）
-- hive (gmall)>
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/';
-- 3.3.7 商品三级分类表（全量）
-- hive (gmall)>
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/';
-- 3.3.8 支付流水表（增量）
-- hive (gmall)>
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/';
-- 3.3.9 省份表（特殊）
-- hive (gmall)>
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编码,superset可视化使用'
) 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/';
-- 3.3.10 地区表（特殊）
-- hive (gmall)>
drop table if exists ods_base_region;
create external table ods_base_region (
`id` string 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/';
-- 3.3.11 品牌表（全量）
-- hive (gmall)>
drop table if exists ods_base_trademark;
create external table ods_base_trademark (
`tm_id` string 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/';
-- 3.3.12 订单状态表（增量）
-- hive (gmall)>
drop table if exists ods_order_status_log;
create external table ods_order_status_log (
`id` string 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/';
-- 3.3.13 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/';
-- 3.3.14 商品评论表（增量）
-- 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/';
-- 3.3.15 退单表（增量）
-- 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/';
-- 3.3.16 加购表（全量）
-- 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` decimal(16,2) COMMENT '放入购物车时价格',
`sku_num` bigint COMMENT '数量',
`sku_name` string COMMENT 'sku名称 (冗余)',
`create_time` string COMMENT '创建时间',
`operate_time` string COMMENT '修改时间',
`is_ordered` string COMMENT '是否已经下单',
`order_time` string COMMENT '下单时间',
`source_type` string COMMENT '来源类型',
`source_id` 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/';
-- 3.3.17 商品收藏表（全量）
-- 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/';
-- 3.3.18 优惠券领用表（新增及变化）
-- 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/';
-- 3.3.19 优惠券表（全量）
-- 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` decimal(16,2) COMMENT '满额数',
`condition_num` bigint COMMENT '满件数',
`activity_id` string COMMENT '活动编号',
`benefit_amount` decimal(16,2) COMMENT '减金额',
`benefit_discount` decimal(16,2) 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` bigint 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/';
-- 3.3.20 活动表（全量）
-- 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/';
-- 3.3.21 活动订单关联表（增量）
-- 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/';
-- 3.3.22 优惠规则表（全量）
-- 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` decimal(16,2) COMMENT '满减金额',
`condition_num` bigint COMMENT '满减件数',
`benefit_amount` decimal(16,2) COMMENT '优惠金额',
`benefit_discount` decimal(16,2) 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/';
-- 3.3.23 编码字典表（全量）
-- 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/';
3.3.3 加载数业务据脚本
- 在/home/atguigu/bin目录下创建脚本hdfs_to_ods_db.sh
[atguigu@hadoop102 bin]$ 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$sql2"
};;
"all"){
$hive -e "$sql1"
};;
esac
- 修改权限
[atguigu@hadoop102 bin]$ chmod 777 hdfs_to_ods_db.sh
- 初次导入
[atguigu@hadoop102 bin]$ hdfs_to_ods_db.sh first 2020-06-22
- 每日导入
[atguigu@hadoop102 bin]$ hdfs_to_ods_db.sh all 2020-06-15
- 测试数据是否导入成功
hive (gmall)> select * from ods_order_detail where dt='2020-06-15';
四、DWD层(重点)
4.1 DWD层数据说明
-- 1. 说明
1. DWD层是数仓建模的核心，建模涉及到维度建模；
2. 用户行为数据不涉及维度建模。业务数据才涉及到维度建模理论
3. DWD层的表都是分区表，除两张特殊表和用户拉链表以外
4. 在创建DWD层的表时，由于前期可能是不知道最后的用户需求的，需要计算什么样的指标，所以把能放进去的字段尽量都放进去。
5. 均是创建外部表
-- 2. 需要掌握：
1. 如何创建表，即表的字段是什么？
2. 数据来源？
3. 数据的内容是什么？
-- 3. 数据存储格式：
parquet（列式存储） + lzo
a、由于在实际开发中，我们经常是需要某列的所有数据，才有列式存储的方式，同一列的数据紧密排列在一起，这样提高了查询的效率
b、列式存储的文件，不需要创建索引文件，parquet存储方式支持切片。
4.2 DWD用户行为日志解析
4.2.1 行为数据说明
-- 1. 说明
1. ods层的日志数据，存储在一列中，数据格式为：json格式
2. 日志数据分类：
a、'启动日志'：包含公共字段、启动信息、错误信息、启动时间
b、'页面埋点日志'：包含公共字段、动作数据、曝光数据、页面数据、错误信息、当前页面跳入时间
4.2.2 行为数据解析思路
-- 1. 思路：为方便后续的数据分析，我们需要对日志解析到启动、页面、动作、曝光、错误五张表中。
-- 2. 每张表处理的逻辑
a、表的字段怎么创建？
b、表的数据来源哪里？
c、表中一行数据代表什么意思？
-- 3. 各张表的设计如下：
1. '启动日志解析'
a、'表的字段怎么创建？'
启动数据包含：公共字段、启动信息、启动时间
b、'表的数据来源哪里？'
通过启动日志中获取，并通过过滤包含'start'的字样来获取
c、'表中一行数据代表什么意思？'
一行数据代表一个启动记录
**************************************************************
2. 页面日志解析
a、'表的字段怎么创建？'
页面日志数据包含：公共字段、页面数据、跳入当前页面时间
b、'表的数据来源哪里？'
通过页面埋点数据中获取，并通过过滤包含'page'的字样来获取
c、'表中一行数据代表什么意思？'
一行数据代表一个页面访问记录
*****************************************************************
3. 动作日志解析
a、'表的字段怎么创建？'
动作日志数据包含：公共字段、页面数据、动作数据、跳入当前页面时间
b、'表的数据来源哪里？'
通过页面埋点数据中获取，并通过过滤包含'action'的字样来获取，由于在日志数据中，一行数据代表着这个页面的所有操作
所以，一行数据可能会有多个动作，需要进行分解成多个动作
c、'表中一行数据代表什么意思？'
一行数据代表在一个页面中的一个操作
*****************************************************************
4. 曝光日志解析
a、'表的字段怎么创建？'
动作日志数据包含：公共字段、页面数据、曝光数据、跳入当前页面时间
b、'表的数据来源哪里？'
通过页面埋点数据中获取，并通过过滤包含'displays'的字样来获取，由于在日志数据中，一行数据代表着这个页面的所有
曝光数据，所以，一行数据可能会有多个曝光，需要进行分解成多个动作
c、'表中一行数据代表什么意思？'
一行数据代表在一个页面中的一个曝光数据
*****************************************************************
5. 错误日志解析
a、'表的字段怎么创建？'
动作日志数据包含：公共字段、曝光数据、动作数据，页面数据、时间
b、'表的数据来源哪里？'
通过页面埋点数据和启动数据中获取，并通过过滤包含'err'的字样来获取，我们会将这个页面的曝光数据封装成一列，页面
数据、动作数据都包装成一列数据
c、'表中一行数据代表什么意思？'
一行数据对应一个错误记录
-- 5. 从如上的设计思路，那么有如下三个问题需要解决：
1. json数据如何进行解析
2. 一行多个action动作事件如何拆分成多行数据
3. 一行多个action动作事件如何包装成一列数据。
4.2.3 json数据解析
-- 1. 函数：get_json_object(json对象，需要获取的字段)
-- 2. 参数1：json对象,字符串类型
'可以是一个json，也可以是json数组'
例：
-- a、一个json
{"name":"lzp","age":10}
-- b、json数组
[{"name":"lzp","age":10},{"name":"ryx","age":5}]
-- 3. 参数2：需要获取的字段，字符串类型，如上案例，需要获取第一个json对象的name的value值，此时参数2的值为：
-- a、一个json
"$.name"
-- b、json数组
"$[0].name"
-- 4. 如上案例的使用方式
get_json_object('{"name":"lzp","age":10}',"$.name") =>lzp
get_json_object('[{"name":"lzp","age":10},{"name":"ryx","age":5}]',"$[1].name") =>lzp
-- 5. 注意事项：
a、两个参数都是需要传递字符串，则需要使用引号括起来，单引号和双引号都可以，不过为避免嵌套的问题，建议json数据使用
单引号，后面的取值使用双引号
b、json数组的角标从'0'开始计算。
-- 6. 实际数据
json={"common":{"ar":"370000","ba":"Huawei","ch":"oppo","md":"Huawei Mate 30","mid":"mid_633","os":"Android 9.0","uid":"156","vc":"v2.1.134"},"start":
{"entry":"icon","loading_time":11342,"open_ad_id":18,"open_ad_ms":6391,"open_ad_skip_ms":3578},"ts":1591113606000}
get_json_object('json',"$.common.ar") => 370000
4.2.4 自定义函数
-- 1. 一行变多行：UDTF
'动作数据表和曝光数据表'
在ods层，一行数据中，action和displays数据是一个json数组，一个json对象包含一个事件对象，所以我们需要将一行数据转换成为
多行数据，转换后的一行数据表示一个动作事件或者是曝光事件。
-- 2. 一行变一行：UDF
'错误数据表'
在ods层，action和displays数据是一个json数组，我们需要将这个json数据转换成真正的数组，数组中为struct类型，一个struct
代表一个动作事件或者曝光事件，这样一来，便于取数据
-- 3. 自定义函数的详细步骤见文档：hive自定义函数
-- 4. 完成自定义函数以后需要对函数进行注册
4.2.5 注册自定义函数
-- 1. 说明：
a、如果创建的自定义函数是临时函数，那么这个函数不属于某一个数据库，在任何数据库中均可以使用
b、如果创建的自定义函数是永久函数，那么这个函数属于当前数据库，如果需要在其他数据库中使用，需要带上数据库的名字。
-- 2. 如果修改了自定义函数重新生成jar包怎么处理？
只需要替换HDFS路径上的旧jar包，然后重启Hive客户端即可。
- 打包
- 将hivefunction-1.0-SNAPSHOT.jar上传到hadoop102的/opt/module，然后再将该jar包上传到HDFS的/user/hive/jars路径下
[atguigu@hadoop102 module]$ hadoop fs -mkdir -p /user/hive/jars
[atguigu@hadoop102 module]$ hadoop fs -put hivefunction-1.0-SNAPSHOT.jar /user/hive/jars
- 创建永久函数与开发好的java class关联
hive (gmall)>
create function explode_json_array as 'com.atguigu.hive.udtf.ExplodeJSONArray' using jar 'hdfs://hadoop102:8020/user/hive/jars/hivefunction-1.0-SNAPSHOT.jar';
-- 语法
create function 函数名 as '自定义函数全类名' using jar 'hdfs的路径'
4.2.6 启动日志表
--1. '启动日志解析'
a、'表的字段怎么创建？'
启动数据包含：公共字段、启动信息、启动时间
b、'表的数据来源哪里？'
通过启动日志中获取，并通过过滤包含'start'的字样来获取
c、'表中一行数据代表什么意思？'
一行数据代表一个启动记录
-- 2. 分区表：每个分区为当天的启动日志数据
-- 3. 解决索引文件被当成数据的方式：
SET hive.input.format=org.apache.hadoop.hive.ql.io.HiveInputFormat;
- 建表语句
drop table if exists dwd_start_log;
CREATE EXTERNAL TABLE dwd_start_log(
`area_code` string,
`brand` string,
`channel` string,
`model` string,
`mid_id` string,
`os` string,
`user_id` string,
`version_code` string,
`entry` string,
`loading_time` bigint,
`open_ad_id` string,
`open_ad_ms` bigint,
`open_ad_skip_ms` bigint,
`ts` bigint
)
PARTITIONED BY (dt string)
stored as parquet
LOCATION '/warehouse/gmall/dwd/dwd_start_log'
TBLPROPERTIES('parquet.compression'='lzo');
- 数据插入
-- ods层有lzo和index文件
-- 默认HiveInputFormat
-- 会把索引文件当作输入文件
-- 一般情况下, 会把索引文件转换成null的文件, 但是由于包的原因, 会识别成索引文件
hive.input.format=org.apache.hadoop.hive.ql.io.CombineHiveInputFormat
SET hive.input.format=org.apache.hadoop.hive.ql.io.HiveInputFormat;
insert overwrite table dwd_start_log partition(dt='2020-06-25')
select
get_json_object(line,'$.common.ar'),
get_json_object(line,'$.common.ba'),
get_json_object(line,'$.common.ch'),
get_json_object(line,'$.common.md'),
get_json_object(line,'$.common.mid'),
get_json_object(line,'$.common.os'),
get_json_object(line,'$.common.uid'),
get_json_object(line,'$.common.vc'),
get_json_object(line,'$.start.entry'),
get_json_object(line,'$.start.loading_time'),
get_json_object(line,'$.start.open_ad_id'),
get_json_object(line,'$.start.open_ad_ms'),
get_json_object(line,'$.start.open_ad_skip_ms'),
get_json_object(line,'$.ts')
from ods_log
where dt='2020-06-25'
and get_json_object(line,'$.start') is not null;
4.2.7 页面日志表
-- 1. 页面日志解析
a、'表的字段怎么创建？'
页面日志数据包含：公共字段、页面数据、跳入当前页面时间
b、'表的数据来源哪里？'
通过页面埋点数据中获取，并通过过滤包含'page'的字样来获取
c、'表中一行数据代表什么意思？'
一行数据代表一个页面访问记录
- 建表语句
drop table if exists dwd_page_log;
CREATE EXTERNAL TABLE dwd_page_log(
`area_code` string,
`brand` string,
`channel` string,
`model` string,
`mid_id` string,
`os` string,
`user_id` string,
`version_code` string,
`during_time` bigint,
`page_item` string,
`page_item_type` string,
`last_page_id` string,
`page_id` string,
`source_type` string,
`ts` bigint
)
PARTITIONED BY (dt string)
stored as parquet
LOCATION '/warehouse/gmall/dwd/dwd_page_log'
TBLPROPERTIES('parquet.compression'='lzo');
- 数据导入
SET hive.input.format=org.apache.hadoop.hive.ql.io.HiveInputFormat;
insert overwrite table dwd_page_log partition(dt='2020-06-25')
select
get_json_object(line,'$.common.ar'),
get_json_object(line,'$.common.ba'),
get_json_object(line,'$.common.ch'),
get_json_object(line,'$.common.md'),
get_json_object(line,'$.common.mid'),
get_json_object(line,'$.common.os'),
get_json_object(line,'$.common.uid'),
get_json_object(line,'$.common.vc'),
get_json_object(line,'$.page.during_time'),
get_json_object(line,'$.page.item'),
get_json_object(line,'$.page.item_type'),
get_json_object(line,'$.page.last_page_id'),
get_json_object(line,'$.page.page_id'),
get_json_object(line,'$.page.sourceType'),
get_json_object(line,'$.ts')
from ods_log
where dt='2020-06-125'
and get_json_object(line,'$.page') is not null;
4.2.8 动作日志表
--1. 动作日志解析
a、'表的字段怎么创建？'
动作日志数据包含：公共字段、页面数据、动作数据、跳入当前页面时间
b、'表的数据来源哪里？'
通过页面埋点数据中获取，并通过过滤包含'action'的字样来获取，由于在日志数据中，一行数据代表着这个页面的所有操作
所以，一行数据可能会有多个动作，需要进行分解成多个动作
c、'表中一行数据代表什么意思？'
一行数据代表在一个页面中的一个操作
--2. 一行变多行，自定义UDTF函数explode_json_array，使用
lateral view explode_json_array(get_json_object(line,'$.actions')) tmp as action
- 建表语句
drop table if exists dwd_action_log;
CREATE EXTERNAL TABLE dwd_action_log(
`area_code` string,
`brand` string,
`channel` string,
`model` string,
`mid_id` string,
`os` string,
`user_id` string,
`version_code` string,
`during_time` bigint,
`page_item` string,
`page_item_type` string,
`last_page_id` string,
`page_id` string,
`source_type` string,
`action_id` string,
`item` string,
`item_type` string,
`ts` bigint
)
PARTITIONED BY (dt string)
stored as parquet
LOCATION '/warehouse/gmall/dwd/dwd_action_log'
TBLPROPERTIES('parquet.compression'='lzo');
- 插入数据
SET hive.input.format=org.apache.hadoop.hive.ql.io.HiveInputFormat;
insert overwrite table dwd_action_log partition(dt='2020-06-25')
select
get_json_object(line,'$.common.ar'),
get_json_object(line,'$.common.ba'),
get_json_object(line,'$.common.ch'),
get_json_object(line,'$.common.md'),
get_json_object(line,'$.common.mid'),
get_json_object(line,'$.common.os'),
get_json_object(line,'$.common.uid'),
get_json_object(line,'$.common.vc'),
get_json_object(line,'$.page.during_time'),
get_json_object(line,'$.page.item'),
get_json_object(line,'$.page.item_type'),
get_json_object(line,'$.page.last_page_id'),
get_json_object(line,'$.page.page_id'),
get_json_object(line,'$.page.sourceType'),
get_json_object(action,'$.action_id'),
get_json_object(action,'$.item'),
get_json_object(action,'$.item_type'),
get_json_object(action,'$.ts')
from ods_log lateral view explode_json_array(get_json_object(line,'$.actions')) tmp as action
where dt='2020-06-25'
and get_json_object(line,'$.actions') is not null;
4.2.9 曝光日志表
--1. 曝光日志解析
a、'表的字段怎么创建？'
动作日志数据包含：公共字段、页面数据、曝光数据、跳入当前页面时间
b、'表的数据来源哪里？'
通过页面埋点数据中获取，并通过过滤包含'displays'的字样来获取，由于在日志数据中，一行数据代表着这个页面的所有
曝光数据，所以，一行数据可能会有多个曝光，需要进行分解成多个动作
c、'表中一行数据代表什么意思？'
一行数据代表在一个页面中的一个曝光数据
- 建表语句
drop table if exists dwd_display_log;
CREATE EXTERNAL TABLE dwd_display_log(
`area_code` string,
`brand` string,
`channel` string,
`model` string,
`mid_id` string,
`os` string,
`user_id` string,
`version_code` string,
`during_time` bigint,
`page_item` string,
`page_item_type` string,
`last_page_id` string,
`page_id` string,
`source_type` string,
`ts` bigint,
`display_type` string,
`item` string,
`item_type` string,
`order` bigint
)
PARTITIONED BY (dt string)
stored as parquet
LOCATION '/warehouse/gmall/dwd/dwd_display_log'
TBLPROPERTIES('parquet.compression'='lzo');
- 插入数据
SET hive.input.format=org.apache.hadoop.hive.ql.io.HiveInputFormat;
insert overwrite table dwd_display_log partition(dt='2020-06-25')
select
get_json_object(line,'$.common.ar'),
get_json_object(line,'$.common.ba'),
get_json_object(line,'$.common.ch'),
get_json_object(line,'$.common.md'),
get_json_object(line,'$.common.mid'),
get_json_object(line,'$.common.os'),
get_json_object(line,'$.common.uid'),
get_json_object(line,'$.common.vc'),
get_json_object(line,'$.page.during_time'),
get_json_object(line,'$.page.item'),
get_json_object(line,'$.page.item_type'),
get_json_object(line,'$.page.last_page_id'),
get_json_object(line,'$.page.page_id'),
get_json_object(line,'$.page.sourceType'),
get_json_object(line,'$.ts'),
get_json_object(displays,'$.displayType'),
get_json_object(displays,'$.item'),
get_json_object(displays,'$.item_type'),
get_json_object(displays,'$.order')
from ods_log lateral view explode_json_array(get_json_object(line,'$.displays')) tmp as displays
where dt='2020-06-25'
and get_json_object(line,'$.displays') is not null;
4.2.10 错误日志表
--1. 错误日志解析
a、'表的字段怎么创建？'
动作日志数据包含：公共字段、曝光数据、动作数据，页面数据、时间
b、'表的数据来源哪里？'
通过页面埋点数据和启动数据中获取，并通过过滤包含'err'的字样来获取，我们会将这个页面的曝光数据封装成一列，页面
数据、动作数据都包装成一列数据
c、'表中一行数据代表什么意思？'
一行数据对应一个错误记录
-- 2.使用UDF函数:json_array_to_struct_array
- 建表:
drop table if exists dwd_error_log;
CREATE EXTERNAL TABLE dwd_error_log(
`area_code` string,
`brand` string,
`channel` string,
`model` string,
`mid_id` string,
`os` string,
`user_id` string,
`version_code` string,
`page_item` string,
`page_item_type` string,
`last_page_id` string,
`page_id` string,
`source_type` string,
`entry` string,
`loading_time` string,
`open_ad_id` string,
`open_ad_ms` string,
`open_ad_skip_ms` string,
`actions` array<struct<action_id:string,item:string,item_type:string,ts:bigint>>,
`displays` array<struct<display_type:string,item:string,item_type:string,`order`:int>>,
`ts` string,
`error_code` string,
`msg` string
)
PARTITIONED BY (dt string)
stored as parquet
LOCATION '/warehouse/gmall/dwd/dwd_error_log'
TBLPROPERTIES('parquet.compression'='lzo');
-- 解释
hive的结构体数组声明
`actions` array<struct<action_id:string,item:string,item_type:string,ts:bigint>>,
`displays` array<struct<display_type:string,item:string,item_type:string,`order`:int>>,
array<bigint>
map<string,bigint>
struct<id:id, name:string, age:int>
- 插入数据
SET hive.input.format=org.apache.hadoop.hive.ql.io.HiveInputFormat;
insert overwrite table dwd_error_log partition(dt='2020-06-25')
select
get_json_object(line,'$.common.ar'),
get_json_object(line,'$.common.ba'),
get_json_object(line,'$.common.ch'),
get_json_object(line,'$.common.md'),
get_json_object(line,'$.common.mid'),
get_json_object(line,'$.common.os'),
get_json_object(line,'$.common.uid'),
get_json_object(line,'$.common.vc'),
get_json_object(line,'$.page.item'),
get_json_object(line,'$.page.item_type'),
get_json_object(line,'$.page.last_page_id'),
get_json_object(line,'$.page.page_id'),
get_json_object(line,'$.page.sourceType'),
get_json_object(line,'$.start.entry'),
get_json_object(line,'$.start.loading_time'),
get_json_object(line,'$.start.open_ad_id'),
get_json_object(line,'$.start.open_ad_ms'),
get_json_object(line,'$.start.open_ad_skip_ms'),
json_array_to_struct_array(get_json_object(line,'$.actions'),'action_id','item','item_type','ts','action_id:string','item:string','item_type:string','ts:bigint'),
json_array_to_struct_array(get_json_object(line,'$.displays'),'displayType','item','item_type','order','display_type:string','item:string','item_type:string','order:int'),
get_json_object(line,'$.ts'),
get_json_object(line,'$.err.error_code'),
get_json_object(line,'$.err.msg')
from ods_log
where dt='2020-06-25'
and get_json_object(line,'$.err') is not null;
-- 解释
json_array_to_struct_array(get_json_object(line,'$.actions'),'action_id','item','item_type','ts','action_id:string','item:string','item_type:string','ts:bigint')
在总共的字段中选择需要的字段, 此时前面的字段(参数)用来指定, 后面对应的字段(参数)用来数据处理
4.2.11 DWD层用户行为数据加载脚本
- 在hadoop102的/home/atguigu/bin目录下创建脚本
[atguigu@hadoop102 bin]$ vim ods_to_dwd_log.sh
- 在脚本中编写如下内容
#!/bin/bash
hive=/opt/module/hive/bin/hive
APP=gmall
# 如果是输入的日期按照取输入日期；如果没输入日期取当前时间的前一天
if [ -n "$1" ] ;then
do_date=$1
else
do_date=`date -d "-1 day" +%F`
fi
sql="
SET mapreduce.job.queuename=hive;
SET hive.input.format=org.apache.hadoop.hive.ql.io.HiveInputFormat;
insert overwrite table ${APP}.dwd_start_log partition(dt='$do_date')
select
get_json_object(line,'$.common.ar'),
get_json_object(line,'$.common.ba'),
get_json_object(line,'$.common.ch'),
get_json_object(line,'$.common.md'),
get_json_object(line,'$.common.mid'),
get_json_object(line,'$.common.os'),
get_json_object(line,'$.common.uid'),
get_json_object(line,'$.common.vc'),
get_json_object(line,'$.start.entry'),
get_json_object(line,'$.start.loading_time'),
get_json_object(line,'$.start.open_ad_id'),
get_json_object(line,'$.start.open_ad_ms'),
get_json_object(line,'$.start.open_ad_skip_ms'),
get_json_object(line,'$.ts')
from ${APP}.ods_log
where dt='$do_date'
and get_json_object(line,'$.start') is not null;
insert overwrite table ${APP}.dwd_action_log partition(dt='$do_date')
select
get_json_object(line,'$.common.ar'),
get_json_object(line,'$.common.ba'),
get_json_object(line,'$.common.ch'),
get_json_object(line,'$.common.md'),
get_json_object(line,'$.common.mid'),
get_json_object(line,'$.common.os'),
get_json_object(line,'$.common.uid'),
get_json_object(line,'$.common.vc'),
get_json_object(line,'$.page.during_time'),
get_json_object(line,'$.page.item'),
get_json_object(line,'$.page.item_type'),
get_json_object(line,'$.page.last_page_id'),
get_json_object(line,'$.page.page_id'),
get_json_object(line,'$.page.sourceType'),
get_json_object(action,'$.action_id'),
get_json_object(action,'$.item'),
get_json_object(action,'$.item_type'),
get_json_object(action,'$.ts')
from ${APP}.ods_log lateral view ${APP}.explode_json_array(get_json_object(line,'$.actions')) tmp as action
where dt='$do_date'
and get_json_object(line,'$.actions') is not null;
insert overwrite table ${APP}.dwd_display_log partition(dt='$do_date')
select
get_json_object(line,'$.common.ar'),
get_json_object(line,'$.common.ba'),
get_json_object(line,'$.common.ch'),
get_json_object(line,'$.common.md'),
get_json_object(line,'$.common.mid'),
get_json_object(line,'$.common.os'),
get_json_object(line,'$.common.uid'),
get_json_object(line,'$.common.vc'),
get_json_object(line,'$.page.during_time'),
get_json_object(line,'$.page.item'),
get_json_object(line,'$.page.item_type'),
get_json_object(line,'$.page.last_page_id'),
get_json_object(line,'$.page.page_id'),
get_json_object(line,'$.page.sourceType'),
get_json_object(line,'$.ts'),
get_json_object(displays,'$.displayType'),
get_json_object(displays,'$.item'),
get_json_object(displays,'$.item_type'),
get_json_object(displays,'$.order')
from ${APP}.ods_log lateral view ${APP}.explode_json_array(get_json_object(line,'$.displays')) tmp as displays
where dt='$do_date'
and get_json_object(line,'$.displays') is not null;
insert overwrite table ${APP}.dwd_page_log partition(dt='$do_date')
select
get_json_object(line,'$.common.ar'),
get_json_object(line,'$.common.ba'),
get_json_object(line,'$.common.ch'),
get_json_object(line,'$.common.md'),
get_json_object(line,'$.common.mid'),
get_json_object(line,'$.common.os'),
get_json_object(line,'$.common.uid'),
get_json_object(line,'$.common.vc'),
get_json_object(line,'$.page.during_time'),
get_json_object(line,'$.page.item'),
get_json_object(line,'$.page.item_type'),
get_json_object(line,'$.page.last_page_id'),
get_json_object(line,'$.page.page_id'),
get_json_object(line,'$.page.sourceType'),
get_json_object(line,'$.ts')
from ${APP}.ods_log
where dt='$do_date'
and get_json_object(line,'$.page') is not null;
insert overwrite table ${APP}.dwd_error_log partition(dt='$do_date')
select
get_json_object(line,'$.common.ar'),
get_json_object(line,'$.common.ba'),
get_json_object(line,'$.common.ch'),
get_json_object(line,'$.common.md'),
get_json_object(line,'$.common.mid'),
get_json_object(line,'$.common.os'),
get_json_object(line,'$.common.uid'),
get_json_object(line,'$.common.vc'),
get_json_object(line,'$.page.item'),
get_json_object(line,'$.page.item_type'),
get_json_object(line,'$.page.last_page_id'),
get_json_object(line,'$.page.page_id'),
get_json_object(line,'$.page.sourceType'),
get_json_object(line,'$.start.entry'),
get_json_object(line,'$.start.loading_time'),
get_json_object(line,'$.start.open_ad_id'),
get_json_object(line,'$.start.open_ad_ms'),
get_json_object(line,'$.start.open_ad_skip_ms'),
${APP}.json_array_to_struct_array(get_json_object(line,'$.actions'), 'action_id','item','item_type','ts','action_id:string','item:string','item_type:string','ts:bigint'),
${APP}.json_array_to_struct_array(get_json_object(line,'$.displays'), 'displayType','item','item_type','order','display_type:string','item:string','item_type:string','order:int'),
get_json_object(line,'$.ts'),
get_json_object(line,'$.err.error_code'),
get_json_object(line,'$.err.msg')
from ${APP}.ods_log
where dt='$do_date'
and get_json_object(line,'$.err') is not null;
"
$hive -e "$sql"
4.3 DWD业务数据
4.3.1 业务建模说明
-- DWD层的数据都是分区表,地区表、时间表、用户表，这三个维度表不是分区表
-- 1. DWD维度建模的4个步骤
1. '选择业务过程'：确定事实表
2. '声明粒度'：根据最小粒度选择具体的事实表
3. '确定维度'：确定每个事实表相关的维度
4. '确定事实'：确定每个事实表的度量值
'维度建模理论只适用于事实表'
-- 2. 本项目中有哪些维度表，有哪些事实表
1. 维度表：6张：用户、时间、地区、商品、活动、优惠券
事实表：8张：加购、收藏、订单明细、退单、评价、支付、订单、优惠券领用
-- 3. DWD层的维度表如何确定？
步骤1：'确定表的字段'：从ODS层找到和这个维度相关所有相关的表，然后获取这些表的所有字段作为维度表的字段
步骤2：'确定每个分区中存储的数据'：也就是同步策略：一般情况下，所有的维度表都是全量表:，因为是对该维度的详情描述，数据
量不会太大，同时新增及变更的频率也比较少。
-- 4. DWD层的事实表如何创建？
步骤1：确定维度：根据实际需求，确定和该事实表相关的所有维度；
步骤2：确定相关表：从ods层确定和事实表相关的表
步骤3：确定度量值：将步骤2中所有相关表的度量值作为事实表度量值
步骤4：确定字段：度量值 + 维度表的关联外键 + 相关维度表的其他字段（保留相关步骤2中的字段，以便后面计算的使用）
-- 5. DWD层事实表同步策略
1. 事务型事实表：订单明细、退单、支付、评价
'同步策略'：新增表
'分区数据'：当天新增的数据
'适用范围'：数据只有新增，旧数据不会出现变更的数据
2. 周期型快照事实表：加购、收藏
'同步策略'：全量表
'分区数据'：每天一个快照，数据为所有数据
'适用范围'：只关心当天数据的最终结果，不关注中间过程
'说明'：在开发中，会根据业务的需求，定期清理部分分区的数据
3. 累计型快照事实表：优惠券领用、订单事实表
'同步策略'：新增及变化表
'分区数据'：当天新增的数据，同时如果有变化的数据，会将旧数据进行更新
'适用范围'：适合一次性写不完的事实表
-- 6. DWD层表的数据来源：
来源于ODS层，所以DWD层事实表与ODS层数据同步策略一致，而ODS层的数据和导入到HDFS上的策略保持一致。
不同的同步策略数据导入的策略：
1. 全量策略：where 1 = 1 ，表示mysql数据库中的数据全部要
2. 新增及变化策略：where create_time=$do_date or operate_time=$do_date，表示创建时间或最新的更新时间等于指定日期
3. 新增策略：where create_time=$do_date，表示只要今天新增的数据
-- 7. 维度表和事实表数据的维护
1. 维度表：一般是全量表，所以在ods层和维度相关的表一般也都是全量表，所以直接获取ods层当天的分区数据作为维度表分区的数据
'特例'：
a、地区表、时间表只需要导入一次，所以这两个维度表不是分区表
b、用户维度表采用拉链表，原因、数据维护方式后面详细说。
2. 事实表：不同事实表维护的策略不同
'事务型事实表':获取ods层相关表的最新分区的数据，直接加到dwd层最新分区中
'周期型快照事实表'：获取ods层相关表的最新分区的数据，直接加到dwd层最新分区中
'累计型快照事实表'：获取ods层相关表的最新分区的数据，修改变化数据所在分区的数据，新增的数据直接添加到新分区中
-- 8. 数据存储格式：
parquet + lzo
特殊表：地区和时间表，不使用分区表，也不使用lzo压缩，因为使用压缩时，在读取数据时，还需要消耗内存进行数据的解压。
-- 9. 数据导入说明：
1. 由于DWD层的数据来源于ODS层，导入数据的时候，是通过insert的方式，所以可以不指定列的分割符
2. 插入数据时，需要使用insert overwrite的方式，说明如下：
insert overwrite table 和 insert into 的区别：
a、 insert into ,表示向原表中插入数据
b、 insert overwrite table：首先将数据写到一个临时路径下，当所有的数据写完以后，删除原表中的数据'如果插入数据时指定
了分区，那么删除原表中指定分区，如果没有指定分区，那么删除全表数据'，然后再将临时路径下的数据插入到目标路径下。
3. 由于dwd层表都是建成列式存储 + lzo方式，那么不能直接通过load的方式加载数据，只能使用insert + select 的方式
4.3.2 维度表
4.3.2.1 商品维度表(全量)
-- 1. 表构建过程：
1. 从ods层找到和商品维度所有相关的表，并判断这些表在ods层同步策略：
1.商品三级分类表 --全量
2.商品二级分类表 --全量
3.商品一级分类表 --全量
4.SPU商品表 --全量
5.sku商品表 --全量
6.品牌表 --全量
2. 取上述相关表的所有字段作为商品维度表的字段
3. 表命名：dwd_dim_sku_info
-- 2. 数据维护
取上面6张表中当天分区内的所有数据放到商品维度表的当天分区内
-- 3. 插入数据时，注意数据连接的顺序，尤其的一二三级商品分类表。
- 建表
DROP TABLE IF EXISTS `dwd_dim_sku_info`;
CREATE EXTERNAL TABLE `dwd_dim_sku_info` (
`id` string COMMENT '商品id',
`spu_id` string COMMENT 'spuid',
`price` decimal(16,2) COMMENT '商品价格',
`sku_name` string COMMENT '商品名称',
`sku_desc` string COMMENT '商品描述',
`weight` decimal(16,2) COMMENT '重量',
`tm_id` string COMMENT '品牌id',
`tm_name` string COMMENT '品牌名称',
`category3_id` string COMMENT '三级分类id',
`category2_id` string COMMENT '二级分类id',
`category1_id` string COMMENT '一级分类id',
`category3_name` string COMMENT '三级分类名称',
`category2_name` string COMMENT '二级分类名称',
`category1_name` string COMMENT '一级分类名称',
`spu_name` string COMMENT 'spu名称',
`create_time` string COMMENT '创建时间'
)
COMMENT '商品维度表'
PARTITIONED BY (`dt` string)
stored as parquet
location '/warehouse/gmall/dwd/dwd_dim_sku_info/'
tblproperties ("parquet.compression"="lzo");
- 插入数据
SET hive.input.format=org.apache.hadoop.hive.ql.io.HiveInputFormat;
insert overwrite table dwd_dim_sku_info partition(dt='2020-06-25')
select
sku.id,
sku.spu_id,
sku.price,
sku.sku_name,
sku.sku_desc,
sku.weight,
sku.tm_id,
ob.tm_name,
sku.category3_id,
c2.id category2_id,
c1.id category1_id,
c3.name category3_name,
c2.name category2_name,
c1.name category1_name,
spu.spu_name,
sku.create_time
from
(
select * from ods_sku_info where dt='2020-06-25'
)sku
join
(
select * from ods_base_trademark where dt='2020-06-25'
)ob on sku.tm_id=ob.tm_id
join
(
select * from ods_spu_info where dt='2020-06-25'
)spu on spu.id = sku.spu_id
join
(
select * from ods_base_category3 where dt='2020-06-25'
)c3 on sku.category3_id=c3.id
join
(
select * from ods_base_category2 where dt='2020-06-25'
)c2 on c3.category2_id=c2.id
join
(
select * from ods_base_category1 where dt='2020-06-25'
)c1 on c2.category1_id=c1.id;
- 查询
select * from dwd_dim_sku_info where dt='2020-06-25' limit 2;
4.3.2.2 优惠券维度表(全量)
-- 1. 表构建过程：
1. 从ods层找到和优惠券维度所有相关的表，并判断这些表在ods层同步策略：
1. 优惠券表：ods_coupon_info --全量
2. 取上述相关表的所有字段作为商品维度表的字段
3. 表命名：dwd_dim_coupon_info
-- 2. 数据维护
取ods_coupon_info表中当天分区内的所有数据放到优惠券维度表的当天分区内
- 建表
drop table if exists dwd_dim_coupon_info;
create external table dwd_dim_coupon_info(
`id` string COMMENT '购物券编号',
`coupon_name` string COMMENT '购物券名称',
`coupon_type` string COMMENT '购物券类型 1 现金券 2 折扣券 3 满减券 4 满件打折券',
`condition_amount` decimal(16,2) COMMENT '满额数',
`condition_num` bigint COMMENT '满件数',
`activity_id` string COMMENT '活动编号',
`benefit_amount` decimal(16,2) COMMENT '减金额',
`benefit_discount` decimal(16,2) 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` bigint COMMENT '最多领用次数',
`operate_time` string COMMENT '修改时间',
`expire_time` string COMMENT '过期时间'
) COMMENT '优惠券信息表'
PARTITIONED BY (`dt` string)
row format delimited fields terminated by '\t'
stored as parquet
location '/warehouse/gmall/dwd/dwd_dim_coupon_info/'
tblproperties ("parquet.compression"="lzo");
- 插入数据
SET hive.input.format=org.apache.hadoop.hive.ql.io.HiveInputFormat;
insert overwrite table dwd_dim_coupon_info partition(dt='2020-06-25')
select
id,
coupon_name,
coupon_type,
condition_amount,
condition_num,
activity_id,
benefit_amount,
benefit_discount,
create_time,
range_type,
spu_id,
tm_id,
category3_id,
limit_num,
operate_time,
expire_time
from ods_coupon_info
where dt='2020-06-25';
- 查询
select * from dwd_dim_coupon_info where dt='2020-06-25' limit 2;
4.3.2.3 活动维度表(全量)
-- 1. 表构建过程：
1. 从ods层找到和活动维度所有相关的表，并判断这些表在ods层同步策略：
1. 优惠规则表：ods_activity_rule --全量
2. 活动表: ods_activity_info --全量
2. 取上述相关表的所有字段作为活动维度表的字段
3. 表命名：dwd_dim_activity_info
-- 2. 数据维护
取上述相关表中当天分区内的所有数据放到活动维度表的当天分区内
-- 3. 加载数据
使用ods_activity_info left join ods_activity_rule，因为有些活动可能没有活动规则
关联条件：活动id
- 建表
drop table if exists dwd_dim_activity_info;
create external table dwd_dim_activity_info(
`id` string COMMENT '编号',
`activity_name` string COMMENT '活动名称',
`activity_type` string COMMENT '活动类型',
`condition_amount` decimal(16,2) COMMENT '满减金额',
`condition_num` bigint COMMENT '满减件数',
`benefit_amount` decimal(16,2) COMMENT '优惠金额',
`benefit_discount` decimal(16,2) COMMENT '优惠折扣',
`benefit_level` 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 parquet
location '/warehouse/gmall/dwd/dwd_dim_activity_info/'
tblproperties ("parquet.compression"="lzo");
- 插入数据
SET hive.input.format=org.apache.hadoop.hive.ql.io.HiveInputFormat;
insert overwrite table dwd_dim_activity_info partition(dt='2020-06-25')
select
info.id,
info.activity_name,
info.activity_type,
rule.condition_amount,
rule.condition_num,
rule.benefit_amount,
rule.benefit_discount,
rule.benefit_level,
info.start_time,
info.end_time,
info.create_time
from
(
select * from ods_activity_info where dt='2020-06-25'
)info
left join
(
select * from ods_activity_rule where dt='2020-06-25'
)rule on info.id = rule.activity_id;
- 查看
select * from dwd_dim_activity_info where dt='2020-06-25' limit 2;
4.3.2.4 地区维度表(特殊)
-- 1. 表构建过程：
1. 从ods层找到和地区维度所有相关的表，并判断这些表在ods层同步策略：
1. 地区表：ods_base_region --特殊
2. 省份表: ods_base_province --特殊
2. 取上述相关表的所有字段作为活动维度表的字段
3. 表命名：dwd_dim_base_province
-- 2. 数据维护
由于地区表只导一次，所以不是分区表，属于全量表，其实可以不用列式存储和lzo压缩
- 建表
DROP TABLE IF EXISTS `dwd_dim_base_province`;
CREATE EXTERNAL TABLE `dwd_dim_base_province` (
`id` string COMMENT 'id',
`province_name` string COMMENT '省市名称',
`area_code` string COMMENT '地区编码',
`iso_code` string COMMENT 'ISO编码',
`region_id` string COMMENT '地区id',
`region_name` string COMMENT '地区名称'
)
COMMENT '地区省市表'
stored as parquet
location '/warehouse/gmall/dwd/dwd_dim_base_province/'
tblproperties ("parquet.compression"="lzo");
- 插入数据
SET hive.input.format=org.apache.hadoop.hive.ql.io.HiveInputFormat;
insert overwrite table dwd_dim_base_province
select
bp.id,
bp.name,
bp.area_code,
bp.iso_code,
bp.region_id,
br.region_name
from ods_base_province bp
join ods_base_region br
on bp.region_id=br.id;
4.3.2.5 时间维度表(特殊)
-- 1. 说明：在业务系统中，是没有时间维度表，但是在数仓中，必须有时间维度，那么我们可以通过自定代码的方式生成时间数据。
-- 2. 通过时间维度，我们可以对数据进行分析。
-- 3. 数据导入说明：
1. 把date_info.txt文件上传到hadoop102的/opt/module/db_log/路径
2. 创建的表用列式存储和建立压缩.不能使用load的方式加载数据，要建一张临时表进行'非列式存储和建立压缩的存储'，
将数据load到创建的临时表中，最后再通过insert overwrite + select 的方式导入数据。
- 建表
DROP TABLE IF EXISTS `dwd_dim_date_info`;
CREATE EXTERNAL TABLE `dwd_dim_date_info`(
`date_id` string COMMENT '日',
`week_id` string COMMENT '周',
`week_day` string COMMENT '周的第几天',
`day` string COMMENT '每月的第几天',
`month` string COMMENT '第几月',
`quarter` string COMMENT '第几季度',
`year` string COMMENT '年',
`is_workday` string COMMENT '是否是周末',
`holiday_id` string COMMENT '是否是节假日'
)
row format delimited fields terminated by '\t'
stored as parquet
location '/warehouse/gmall/dwd/dwd_dim_date_info/'
tblproperties ("parquet.compression"="lzo");
- 插入数据
--（1）创建临时表，非列式存储
DROP TABLE IF EXISTS `dwd_dim_date_info_tmp`;
CREATE EXTERNAL TABLE `dwd_dim_date_info_tmp`(
`date_id` string COMMENT '日',
`week_id` string COMMENT '周',
`week_day` string COMMENT '周的第几天',
`day` string COMMENT '每月的第几天',
`month` string COMMENT '第几月',
`quarter` string COMMENT '第几季度',
`year` string COMMENT '年',
`is_workday` string COMMENT '是否是周末',
`holiday_id` string COMMENT '是否是节假日'
)
row format delimited fields terminated by '\t'
location '/warehouse/gmall/dwd/dwd_dim_date_info_tmp/';
-- （2）将数据导入临时表
load data local inpath '/opt/module/db_log/date_info.txt' into table dwd_dim_date_info_tmp;
-- （3）将数据导入正式表
insert overwrite table dwd_dim_date_info select * from dwd_dim_date_info_tmp;
-- 4）查询加载结果
select * from dwd_dim_date_info;
4.3.2.6 用户维度表(拉链表)
- 拉链表说明
-- 1. 拉链表应用于维度表
-- 2. 什么样的维度表使用拉链表？
数据会发生变化，但是大部分是不变的表。
'比如'：用户信息会发生变化，但是每天变化的比例不高。如果数据量有一定规模，按照每日全量的方式保存效率很低。
'比如'：1亿用户*365天，每天一份用户信息。(做每日全量效率低)
-- 3. 拉链表的定义
记录每条信息的生命周期，一旦一条记录的生命周期结束，就重新开始一条新的记录，并把当前日期放入生效开始日期。
如果当前信息至今有效，在生效结束日期中填入一个极大值（如9999-99-99 ）。
-- 4. 使用拉链表的效果：
a、能保留历史数据的状态
b、数据不会出现冗余
c、一条数据会有多行，在这里，一个用户会有多行数据
-- 5. 拉链表一般怎么使用？
a、获取每条数据的最新数据
b、分析历史数据，如指定某个时间点的数据
-- 6. 拉链表的形成过程：
1. 初始化拉链表，就是添加start-time 和 end_time,首次独立运行
2. 先合并变动数据，再追加新增数据，插入到临时表中
3. 把临时表汇总的数据覆盖原来的旧表
- 用户维度表
-- 1. 说明：
'在实际生产环境下'：
单独使用sqoop将业务系统数据中的用户表全部导入到HDFS上，由于拉链表的数据需要增加start_time 和end_time两列数据，所以可
以先通过创建一个临时表，将数据导入到临时表中，并添加start_time 和end_time，然后再insert overwrite 到拉链表中。
'在实验阶段':
数据直接从ods层获取。
-- 2. 建表过程：
1. 从ods层找到和用户维度所有相关的表，并判断这些表在ods层同步策略：
1. 用户表：ods_user_info --新增及变化
2. 取上述相关表的所有字段作为用户维度表的字段，并加上start_time 和 end_time字段
3. 表命名：dwd_dim_user_info
-- 3. 数据维护
1. 初始化dwd_dim_user_info_his，将第一天用户数据导入并将start_date设置为今天，end_date设置为9999-99-99
2. 处理新数据：
取出ods_user_info表第二天的数据
1. 首先将ods_user_info数据，增加start_date 和 end_date字段
start_date：当天时间
end_date：设置为9999-99-99
3. 处理旧数据
1. 初始表 left join ods_user_info表第二天的数据，当ods_user_info的id不为null且初始表的end_date为9999-99-99
的数据，将end_date改为昨天，否则使用初始化数据
4. 创建临时表dwd_dim_user_info_his_tmp
5. 合并新旧数据
步骤3数据 union all 步骤2的表，将结果 insert overwrite 到临时表中'两张表的字段名称要一一对应'
6. 将临时表的数据插入到用户维护表中。
-- 4. 用户维度表的数据内容：
1. 不是分区表，保存所有的数据
2. 一个用户会有多行数据
3. 导入数据时，这行代码一定要加
SET hive.input.format=org.apache.hadoop.hive.ql.io.HiveInputFormat;
- 创建表
drop table if exists dwd_dim_user_info_his;
create external table dwd_dim_user_info_his(
`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 '操作时间',
`start_date` string COMMENT '有效开始日期',
`end_date` string COMMENT '有效结束日期'
) COMMENT '用户拉链表'
stored as parquet
location '/warehouse/gmall/dwd/dwd_dim_user_info_his/'
tblproperties ("parquet.compression"="lzo");
- 初始化 dwd_dim_user_info_his
SET hive.input.format=org.apache.hadoop.hive.ql.io.HiveInputFormat;
INSERT overwrite table dwd_dim_user_info_his
SELECT
id,
name,
birthday,
gender,
email,
user_level,
create_time,
operate_time,
'2020-06-25',
'9999-99-99'
FROM ods_user_info
WHERE dt='2020-06-25'
- 处理新数据
(SELECT
id,
name,
birthday,
gender,
email,
user_level,
create_time,
operate_time,
'2020-06-26',
'9999-99-99'
FROM ods_user_info
WHERE dt='2020-06-26') tmp
- 处理旧数据
SELECT
his.id,
his.name,
his.birthday,
his.gender,
his.email,
his.user_level,
his.create_time,
his.operate_time,
his.start_date,
if(tmp.id is not null and his.end_date is '9999-99-99',date_add(tmp.dt,-1),his.end_date) end_date
FROM dwd_dim_user_info_his his
left join
(select *
from ods_user_info
where dt = '2020-06-26'
)tmp
on tmp.id = his.id
- 将处理后的新数据和处理后的旧数据 union all 插入到临时表中
创建临时表dwd_dim_user_info_his_tmp
drop table if exists dwd_dim_user_info_his_tmp;
create external table dwd_dim_user_info_his_tmp(
`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 '操作时间',
`start_date` string COMMENT '有效开始日期',
`end_date` string COMMENT '有效结束日期'
) COMMENT '用户拉链表'
stored as parquet
location '/warehouse/gmall/dwd/dwd_dim_user_info_his_tmp/'
tblproperties ("parquet.compression"="lzo");
SET hive.input.format=org.apache.hadoop.hive.ql.io.HiveInputFormat;
insert overwrite table dwd_dim_user_info_his_tmp
select * from (
SELECT
his.id,
his.name,
his.birthday,
his.gender,
his.email,
his.user_level,
his.create_time,
his.operate_time,
his.start_date,
if(tmp.id is not null and his.end_date ='9999-99-99',date_add(tmp.dt,-1),his.end_date) end_date
FROM dwd_dim_user_info_his his
left join
(select *
from ods_user_info
where dt = '2020-06-26'
)tmp
on tmp.id = his.id
union all
SELECT
id,
name,
birthday,
gender,
email,
user_level,
create_time,
operate_time,
'2020-06-26' start_date,
'9999-99-99' end_date
FROM ods_user_info
WHERE dt='2020-06-26'
) tmp1
order by tmp1.id ,tmp1.start_date
- 将临时表的数据插入到用户维度表中
insert overwrite table dwd_dim_user_info_his select * from dwd_dim_user_info_his_tmp;
4.3.3 事实表
4.3.3.1 订单明细事实表(事务型)
-- 1. 建表过程
'准备'： 维度有：用户 、 地区 、 时间 、 商品 、 活动 、 优惠券
'步骤1'：确定订单明细事实表的维度：用户 、 时间 、 商品 、地区
'步骤2'：找到ods层的相关表：ods_order_detail，订单明细表，增量表
ods_order_info ，订单表，新增及变化表
'步骤3'：确定度量值：金额/个数
-- 2. 建表的字段：
1. '外键'：所有的维度作为事实表的外键；
2. '度量值'：相关表的所有数值类型的字段作为事实表的度量值，如果这个度量值不能满足要求，考虑从其他表中组合获取；
3. '冗余字段'：相关表的其他字段也直接放置在事实表中，便于后续计算的需求。
'在实际创建表时，取到相关表的字段，看是否满足如上3个方面字段的需求，如果不满足，想办法从其他地方获取'
-- 3. 数据说明：
1. '事实表类型'：事务型事实表，新增策略
2. '每个分区数据'：是分区表，每个分区存储当天新增订单明细
3. '一行数据'：一个订单会有多个产品，这里的一行数据就是一个订单中一个商品的详情
-- 4. 数据的维护
从ods层获取当天新增的数据，保存到当天的分区中。
- 建表语句
drop table if exists dwd_fact_order_detail;
create external table dwd_fact_order_detail (
`id` string COMMENT '订单编号',
`order_id` string COMMENT '订单号',
`user_id` string COMMENT '用户id', --用户维度
`sku_id` string COMMENT 'sku商品id',--商品维度
`sku_name` string COMMENT '商品名称',
`order_price` decimal(16,2) COMMENT '商品价格',
`sku_num` bigint COMMENT '商品数量',
`create_time` string COMMENT '创建时间', -- 时间维度
`province_id` string COMMENT '省份ID', -- 地区维度
`source_type` string COMMENT '来源类型',
`source_id` string COMMENT '来源编号',
`original_amount_d` decimal(20,2) COMMENT '原始价格分摊',
`final_amount_d` decimal(20,2) COMMENT '购买价格分摊',
`feight_fee_d` decimal(20,2) COMMENT '分摊运费',
`benefit_reduce_amount_d` decimal(20,2) COMMENT '分摊优惠'
)
PARTITIONED BY (`dt` string)
stored as parquet
location '/warehouse/gmall/dwd/dwd_fact_order_detail/'
tblproperties ("parquet.compression"="lzo");
-- 1. 建表语句说明：
从如上的建表语句可知，咱们的度量值无法从ods层的订单详情表中直接获取，那么咱们需要想办法从其他地方来获取。
-- 2. 几个度量值的介绍
`original_amount_d` decimal(20,2) COMMENT '原始价格分摊',
-- 指这个产品的原价 = 商品价格 * 商品数量
`final_amount_d` decimal(20,2) COMMENT '购买价格分摊',
-- 指在一个订单中，买了这个产品的分摊价格 = 商品价格 * 商品数量 / 订单原价 * 订单最终价格
`feight_fee_d` decimal(20,2) COMMENT '分摊运费',
-- 由于一个订单中，会有很多产品，那么需对运费进行分摊，分摊价格 = 商品价格 * 商品数量 / 订单原价 * 订单运费
`benefit_reduce_amount_d` decimal(20,2) COMMENT '分摊优惠'
-- 在一个订单中，当前产品分摊的优惠金额 = 商品价格 * 商品数量 / 订单原价 * 订单优惠金额
-- 3. 如上的几个数据：订单价格、订单运费、订单的优惠金额均在ods层的订单表中，那么需要想办法从其中获取
-- 4. 一般情况下，分摊价格可能会有很多小数，咱们就会使用四舍五入的方式，这样一来，最后的价格可能和订单支付的价格不符，所以
差额的哪一部分，就分摊到一个订单中购买价格分摊最多的产品中。
- 插入数据
SET hive.input.format=org.apache.hadoop.hive.ql.io.HiveInputFormat;
insert overwrite table dwd_fact_order_detail partition(dt='2020-06-25')
select
`id`, --订单编号
`order_id` ,-- 订单号
`user_id` ,--用户id
`sku_id` ,--商品id
`sku_name` ,--商品名称
`order_price` ,--商品价格
`sku_num` ,--商品数量
`create_time`,--创建时间
`province_id` , --省份ID
`source_type`,--来源类型
`source_id` ,--来源编号
original_amount_d,--原始价格分摊
if(rk=1,final_amount_d + final_total_amount-sum_final_amount_d,final_amount_d),--购买价格分摊
if(rk=1,feight_fee_d + feight_fee-sum_feight_fee_d,feight_fee_d),--分摊运费
if(rk=1,benefit_reduce_amount_d + benefit_reduce_amount-sum_benefit_reduce_amount_d,benefit_reduce_amount_d) --分摊优惠
from(
select
detail.`id`, --订单编号
detail.`order_id` ,-- 订单号
detail.`user_id` ,--用户id
detail.`sku_id` ,--商品id
detail.`sku_name` ,--商品名称
detail.`order_price` ,--商品价格
detail.`sku_num` ,--商品数量
detail.`create_time`,--创建时间
info.`province_id` , --省份ID
detail.`source_type`,--来源类型
detail.`source_id` ,--来源编号
order_price * sku_num original_amount_d , --原始价格分摊
info.final_total_amount,--订单金额
info.feight_fee,--订单运费
info.benefit_reduce_amount,--订单优惠金额
round(order_price * sku_num / original_total_amount * final_total_amount , 2) final_amount_d,--购买价格分摊
round(order_price * sku_num / original_total_amount * feight_fee ,2) feight_fee_d, --运费分摊运费
round(order_price * sku_num / original_total_amount * benefit_reduce_amount ,2) benefit_reduce_amount_d, --优惠分摊优惠
rank() over(partition by detail.`order_id` order by order_price * sku_num desc) rk , -- 按照购买价格分摊降序排序
sum(round(order_price * sku_num / original_total_amount * final_total_amount ,2)) over (partition by detail.`order_id` ) sum_final_amount_d,-- 购买价格分摊总额
sum(round(order_price * sku_num / original_total_amount * feight_fee ,2)) over (partition by detail.`order_id` ) sum_feight_fee_d, -- 分摊优惠总额
sum(round(order_price * sku_num / original_total_amount * benefit_reduce_amount ,2)) over (partition by detail.`order_id` ) sum_benefit_reduce_amount_d --分摊优惠总额
from ods_order_detail detail
join ods_order_info info
on detail.order_id = info.id
where detail.dt = '2020-06-25'
)oi;
4.3.3.2 支付事实表(事务型)
-- 1. 建表过程
'准备'： 维度有：用户 、 地区 、 时间 、 商品 、 活动 、 优惠券
'步骤1'：确定订单明细事实表的维度：用户 、 时间 、 地区
'步骤2'：找到ods层的相关表：ods_payment_info ，支付表，增量表
'步骤3'：确定度量值：金额/个数
-- 2. 建表的字段：
1. '外键'：所有的维度作为事实表的外键；
2. '度量值'：相关表的所有数值类型的字段作为事实表的度量值，如果这个度量值不能满足要求，考虑从其他表中组合获取；
3. '冗余字段'：相关表的其他字段也直接放置在事实表中，便于后续计算的需求。
'在实际创建表时，取到相关表的字段，看是否满足如上3个方面字段的需求，如果不满足，想办法从其他地方获取'
在此事实表中，地区字段就不能通过相关表直接获取，那找到订单表中地区。
-- 3. 数据说明：
1. '事实表类型'：事务型事实表，新增策略
2. '每个分区数据'：是分区表，每个分区存储当天支付的订单信息
3. '一行数据'：一行数据代表一个支付订单
-- 4. 数据的维护
从ods层获取当天数据，保存到当天的分区中。
分区字段：订单支付的时间
- 建表语句
drop table if exists dwd_fact_payment_info;
create external table dwd_fact_payment_info (
`id` string COMMENT '',
`out_trade_no` string COMMENT '对外业务编号',
`order_id` string COMMENT '订单编号',
`user_id` string COMMENT '用户编号',
`alipay_trade_no` string COMMENT '支付宝交易流水编号',
`payment_amount` decimal(16,2) COMMENT '支付金额',
`subject` string COMMENT '交易内容',
`payment_type` string COMMENT '支付类型',
`payment_time` string COMMENT '支付时间',
`province_id` string COMMENT '省份ID'
)
PARTITIONED BY (`dt` string)
stored as parquet
location '/warehouse/gmall/dwd/dwd_fact_payment_info/'
tblproperties ("parquet.compression"="lzo");
- 插入数据
SET hive.input.format=org.apache.hadoop.hive.ql.io.HiveInputFormat;
insert overwrite table dwd_fact_payment_info partition(dt='2020-06-25')
select
ods_payment_info.`id`,--编号
ods_payment_info.`out_trade_no` ,-- 对外业务编号
ods_payment_info.`order_id` , --订单编号
ods_payment_info.`user_id` , --用户编号
ods_payment_info.`alipay_trade_no` , --支付宝交易流水编号
ods_payment_info.`total_amount` ,--支付金额
ods_payment_info.`subject` ,--交易内容
ods_payment_info.`payment_type` ,--支付类型
ods_payment_info.`payment_time` ,--支付时间
ods_order_info.province_id --省份ID
from ods_payment_info
join ods_order_info
on ods_order_info.id = ods_payment_info.order_id
where ods_payment_info.dt='2020-06-25'
4.3.3.3 退款事实表(事务型)
-- 1. 建表过程
'准备'： 维度有：用户 、 地区 、 时间 、 商品 、 活动 、 优惠券
'步骤1'：确定订单明细事实表的维度：用户 、 时间 、 商品
'步骤2'：找到ods层的相关表：ods_order_refund_info ，退款表，增量表
'步骤3'：确定度量值：金额/个数
-- 2. 建表的字段：
1. '外键'：所有的维度作为事实表的外键；
2. '度量值'：相关表的所有数值类型的字段作为事实表的度量值，如果这个度量值不能满足要求，考虑从其他表中组合获取；
3. '冗余字段'：相关表的其他字段也直接放置在事实表中，便于后续计算的需求。
'在实际创建表时，取到相关表的字段，看是否满足如上3个方面字段的需求，如果不满足，想办法从其他地方获取'
-- 3. 数据说明：
1. '事实表类型'：事务型事实表，新增策略
2. '每个分区数据'：是分区表，每个分区存储当天退款的信息
3. '一行数据'：一行数据代表一个退款信息
-- 4. 数据的维护
从ods层ods_order_refund_info表中获取当天数据，保存到当天的分区中。
分区字段：退单创建的时间
- 建表
create external table dwd_fact_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'
location '/warehouse/gmall/dwd/dwd_fact_order_refund_info/';
- 插入数据
SET hive.input.format=org.apache.hadoop.hive.ql.io.HiveInputFormat;
insert overwrite table dwd_fact_order_refund_info partition(dt='2020-06-25')
select
`id` ,--编号
`user_id`,--用户ID
`order_id` ,--订单ID
`sku_id` ,--商品ID
`refund_type`, --退款类型
`refund_num`,--退款件数
`refund_amount`,-- 退款金额
`refund_reason_type`,--退款原因类型
`create_time` --退款时间
from ods_order_refund_info
where dt='2020-06-25'
4.3.3.4 评价事实表(事务型)
-- 1. 建表过程
'准备'： 维度有：用户 、 地区 、 时间 、 商品 、 活动 、 优惠券
'步骤1'：确定订单明细事实表的维度：用户 、 时间 、 商品
'步骤2'：找到ods层的相关表：ods_comment_info，评论表，增量表
'步骤3'：确定度量值：评论的次数
-- 2. 建表的字段：
1. '外键'：所有的维度作为事实表的外键；
2. '度量值'：相关表的所有数值类型的字段作为事实表的度量值，如果这个度量值不能满足要求，考虑从其他表中组合获取；
3. '冗余字段'：相关表的其他字段也直接放置在事实表中，便于后续计算的需求。
'在实际创建表时，取到相关表的字段，看是否满足如上3个方面字段的需求，如果不满足，想办法从其他地方获取'
-- 3. 数据说明：
1. '事实表类型'：事务型事实表，新增策略
2. '每个分区数据'：是分区表，每个分区存储当天评论数据
3. '一行数据'：一行数据代表一个评论信息
-- 4. 数据的维护
从ods层ods_comment_info表中获取当天数据，保存到当天的分区中。
分区字段：评论时间
- 建表
drop table if exists dwd_fact_comment_info;
create external table dwd_fact_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'
location '/warehouse/gmall/dwd/dwd_fact_comment_info/';
- 插入数据
SET hive.input.format=org.apache.hadoop.hive.ql.io.HiveInputFormat;
insert overwrite table dwd_fact_comment_info partition(dt='2020-06-25')
select
id,
user_id,
sku_id,
spu_id,
order_id,
appraise,
create_time
from ods_comment_info
where dt='2020-06-25';
4.3.3.5 加购事实表(周期性快照)
-- 1. 建表过程
'准备'： 维度有：用户 、 地区 、 时间 、 商品 、 活动 、 优惠券
'步骤1'：确定订单明细事实表的维度：用户 、 时间 、 商品
'步骤2'：找到ods层的相关表：ods_cart_info，加购表，全量表
'步骤3'：确定度量值：加购商品的金额和数量
-- 2. 建表的字段：
1. '外键'：所有的维度作为事实表的外键；
2. '度量值'：相关表的所有数值类型的字段作为事实表的度量值，如果这个度量值不能满足要求，考虑从其他表中组合获取；
3. '冗余字段'：相关表的其他字段也直接放置在事实表中，便于后续计算的需求。
'在实际创建表时，取到相关表的字段，看是否满足如上3个方面字段的需求，如果不满足，想办法从其他地方获取'
-- 3. 数据说明：
1. '事实表类型'：周期性快照事实表，每天一个全量，全量策略
2. '每个分区数据'：是分区表，每个分区存储一个当天的一个全量数据
3. '一行数据'：一行数据代表一个加购车信息
-- 4. 数据的维护
从ods层ods_cart_info表中获取当天数据即为全量数据，保存到当天的分区中。
分区字段：加购车时间
- 建表
drop table if exists dwd_fact_cart_info;
create external table dwd_fact_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 '是否已经下单。1为已下单;0为未下单',
`order_time` string COMMENT '下单时间',
`source_type` string COMMENT '来源类型',
`srouce_id` string COMMENT '来源编号'
) COMMENT '加购事实表'
PARTITIONED BY (`dt` string)
row format delimited fields terminated by '\t'
location '/warehouse/gmall/dwd/dwd_fact_cart_info/';
- 插入数据
SET hive.input.format=org.apache.hadoop.hive.ql.io.HiveInputFormat;
insert overwrite table dwd_fact_cart_info partition(dt='2020-06-25')
select
id,
user_id,
sku_id,
cart_price,
sku_num,
sku_name,
create_time,
operate_time,
is_ordered,
order_time,
source_type,
source_id
from ods_cart_info
where dt='2020-06-25';
4.3.3.6 收藏事实表(周期型快照)
-- 1. 建表过程
'准备'： 维度有：用户 、 地区 、 时间 、 商品 、 活动 、 优惠券
'步骤1'：确定订单明细事实表的维度：用户 、 时间 、 商品
'步骤2'：找到ods层的相关表：ods_favor_info，商品收藏表，全量表
'步骤3'：确定度量值：收藏商品的金额和数量
-- 2. 建表的字段：
1. '外键'：所有的维度作为事实表的外键；
2. '度量值'：相关表的所有数值类型的字段作为事实表的度量值，如果这个度量值不能满足要求，考虑从其他表中组合获取；
3. '冗余字段'：相关表的其他字段也直接放置在事实表中，便于后续计算的需求。
'在实际创建表时，取到相关表的字段，看是否满足如上3个方面字段的需求，如果不满足，想办法从其他地方获取'
-- 3. 数据说明：
1. '事实表类型'：周期性快照事实表，每天一个全量，全量策略
2. '每个分区数据'：是分区表，每个分区存储一个当天的一个全量数据
3. '一行数据'：一行数据代表一个收藏信息
-- 4. 数据的维护
从ods层ods_favor_info表中获取当天数据即为全量数据，保存到当天的分区中。
分区字段：加入收藏的时间
- 建表
drop table if exists dwd_fact_favor_info;
create external table dwd_fact_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'
location '/warehouse/gmall/dwd/dwd_fact_favor_info/';
- 插入数据
SET hive.input.format=org.apache.hadoop.hive.ql.io.HiveInputFormat;
insert overwrite table dwd_fact_favor_info partition(dt='2020-06-25')
select
id,
user_id,
sku_id,
spu_id,
is_cancel,
create_time,
cancel_time
from ods_favor_info
where dt='2020-06-25';
4.3.3.7优惠券事实表(累计型快照)
-- 1. 建表过程
'准备'： 维度有：用户 、 地区 、 时间 、 商品 、 活动 、 优惠券
'步骤1'：确定订单明细事实表的维度：用户 、 时间 、优惠券
'步骤2'：找到ods层的相关表： ods_coupon_use，商品收藏表，新增及变化
'步骤3'：确定度量值：优惠券的领用
-- 2. 建表的字段：
1. '外键'：所有的维度作为事实表的外键；
2. '度量值'：相关表的所有数值类型的字段作为事实表的度量值，如果这个度量值不能满足要求，考虑从其他表中组合获取；
3. '冗余字段'：相关表的其他字段也直接放置在事实表中，便于后续计算的需求。
'在实际创建表时，取到相关表的字段，看是否满足如上3个方面字段的需求，如果不满足，想办法从其他地方获取'
-- 3. 数据说明：
1. '事实表类型'：累计型快照事实表
2. '每个分区数据'：是分区表，当天添加数据时，当天的分区中只有新增数据，而变化的数据，会将旧分区中的数据进行修改
3. '一行数据'：一行数据代表一个优惠券的使用情况
-- 4. 数据的维护
-- 思路1：
1. 从ods层ods_favor_info 表'称作new表'中获取当天数据，该数据中有新增及变化数据
2. 从new表中取出今天新增的数据 --查询1
3. 从new表中取出今天变化的数据，然后找到这个数据所在的分区
4. 从old表中，取出上述分区所有的数据
5. 对步骤4变化的数据进行修改，没有变化的数据不做修改，然后将计算结果放回原来的分区中 --查询2
6. 将查询1的数据和查询2的数据进行合并：union all ，以后动态插入到旧表中，则完成了old表的更新
-- 思路2：
1. 从new表中取出今天变化的数据，然后找到这个数据所在的分区
2. 从old表中，取出上述分区所有的数据
3. 步骤2的结果数据和新表数据进行full join ，然后取字段：
新表有数据，则取新表数据，新表如果为null，则取旧表数据。
4. 最后使用动态分区的方式将数据插入到原表中
-- 5. hive中不支持insert 、 upsert 操作，虽然现在也支持了。但是效率是非常低，所以我们更新数据时，采用的方式是：
1. 通过select将数据查询出来
2. 根据需求对数据进行修改
3. 修改完成以后再insert overwrite table 到旧表中
-- 6. 累积性快照事实表必须是分区表，否则每次修改数据都需要全表扫描，那么数据的非常低。
-- 7. 数据的生命周期：领用->正在使用->已使用
- 创建表
drop table if exists dwd_fact_coupon_use;
create external table dwd_fact_coupon_use(
`id` string COMMENT '编号',
`coupon_id` string COMMENT '优惠券ID',
`user_id` string COMMENT 'userid',
`order_id` string COMMENT '订单id',
`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'
location '/warehouse/gmall/dwd/dwd_fact_coupon_use/';
- 插入数据实现步骤
- 获取新增数据
select
id,--编号
coupon_id ,--优惠券ID
user_id , --skuid
order_id ,-- spuid
coupon_status ,--优惠券状态
get_time, --领取时间
using_time ,--使用时间(下单)
used_time -- 使用时间(支付)
from ods_coupon_use
where dt='2020-06-25'
and date_format(get_time,'yyyy-MM-dd') !='2020-06-25'
- 获取旧表中变化数据所在分区的所有数据
select *
from dwd_fact_coupon_use
where dt in (
select
date_format(get_time,'yyyy-MM-dd')
from ods_coupon_use
where dt='2020-06-25'
and date_format(get_time,'yyyy-MM-dd') !='2020-06-25'
)--t1
- 修改旧表中的新增数据
select
t1.id,--编号
t1.coupon_id ,--优惠券ID
t1.user_id , --skuid
t1.order_id ,-- spuid
nvl(new.coupon_status,t1.coupon_status) coupon_status,--优惠券状态
nvl(new.get_time,t1.get_time) get_time ,-- 领取时间
nvl(new.using_time,t1.using_time) using_time,--使用时间(下单)
nvl(new.used_time,t1.used_time) used_time-- 使用时间(支付)
from (
select *
from dwd_fact_coupon_use
where dt in (
select
date_format(get_time,'yyyy-MM-dd')
from ods_coupon_use
where dt='2020-06-25'
and date_format(get_time,'yyyy-MM-dd') !='2020-06-25'
)
)t1
left join (
select *
from ods_coupon_use
where dt='2020-06-25'
)new
on new.id = t1.id
- 合并新旧数据并动态插入数据
set hive.exec.dynamic.partition.mode=nonstrict;
SET hive.input.format=org.apache.hadoop.hive.ql.io.HiveInputFormat;
insert overwrite table dwd_fact_coupon_use partition(dt)
select
id,--编号
coupon_id ,--优惠券ID
user_id, --skuid
order_id ,-- spuid
coupon_status ,--优惠券状态
get_time ,-- 领取时间
using_time ,--使用时间(下单)
used_time, -- 使用时间(支付)
date_format(get_time,'yyyy-MM-dd') dt
from (
select
id,--编号
coupon_id ,--优惠券ID
user_id, --skuid
order_id ,-- spuid
coupon_status ,--优惠券状态
get_time ,-- 领取时间
using_time ,--使用时间(下单)
used_time -- 使用时间(支付)
from ods_coupon_use
where dt='2020-06-25'
and date_format(get_time,'yyyy-MM-dd') ='2020-06-25'
union all
select
t1.id,--编号
t1.coupon_id ,--优惠券ID
t1.user_id , --skuid
t1.order_id ,-- spuid
nvl(new.coupon_status,t1.coupon_status) coupon_status,--优惠券状态
nvl(new.get_time,t1.get_time) get_time ,-- 领取时间
nvl(new.using_time,t1.using_time) using_time,--使用时间(下单)
nvl(new.used_time,t1.used_time) used_time-- 使用时间(支付)
from (
select *
from dwd_fact_coupon_use
where dt in (
select
date_format(get_time,'yyyy-MM-dd')
from ods_coupon_use
where dt='2020-06-25'
and date_format(get_time,'yyyy-MM-dd') !='2020-06-25'
)
)t1
left join (
select *
from ods_coupon_use
where dt='2020-06-25'
)new
on new.id = t1.id
)tmp
4.3.3.8 系统函数
1）concat函数
concat函数在连接字符串的时候，只要其中一个是NULL，那么将返回NULL
hive> select concat('a','b');
ab
hive> select concat('a','b',null);
NULL
2）concat_ws函数
concat_ws函数在连接字符串的时候，只要有一个字符串不是NULL，就不会返回NULL。concat_ws函数需要指定分隔符。
hive> select concat_ws('-','a','b');
a-b
hive> select concat_ws('-','a','b',null);
a-b
hive> select concat_ws('','a','b',null);
ab
3）STR_TO_MAP函数
（1）语法描述
STR_TO_MAP(VARCHAR text, VARCHAR listDelimiter, VARCHAR keyValueDelimiter)
（2）功能描述
使用listDelimiter将text分隔成K-V对，然后使用keyValueDelimiter分隔每个K-V对，组装成MAP返回。默认listDelimiter为（ ，），keyValueDelimiter为（=）。
（3）案例
str_to_map('1001=2020-06-14,1002=2020-06-14', ',' , '=')
输出
{"1001":"2020-06-14","1002":"2020-06-14"}
4.3.3.9 订单事实表(累积型快照)
-- 1. 建表过程
'准备'： 维度有：用户 、 地区 、 时间 、 商品 、 活动 、 优惠券
'步骤1'：确定订单明细事实表的维度：用户 、 时间 、优惠券
'步骤2'：找到ods层的相关表： ods_coupon_use，商品收藏表，新增及变化
'步骤3'：确定度量值：优惠券的领用
-- 2. 建表的字段：
1. '外键'：所有的维度作为事实表的外键；
2. '度量值'：相关表的所有数值类型的字段作为事实表的度量值，如果这个度量值不能满足要求，考虑从其他表中组合获取；
3. '冗余字段'：相关表的其他字段也直接放置在事实表中，便于后续计算的需求。
'在实际创建表时，取到相关表的字段，看是否满足如上3个方面字段的需求，如果不满足，想办法从其他地方获取'
-- 3. 数据说明：
1. '事实表类型'：累计型快照事实表
2. '每个分区数据'：是分区表，当天添加数据时，当天的分区中只有新增数据，而变化的数据，会将旧分区中的数据进行修改
3. '一行数据'：一行数据代表一个优惠券的使用情况
-- 4. 数据的维护
和优惠券事实表的差异在与：
a、订单表的生命周期需要和其他表join
b、同时需要对数据进行转换。
由于时间原因，这个表就不再一一分析。
-- 6. 累积性快照事实表必须是分区表，否则每次修改数据都需要全表扫描，那么数据的非常低。
-- 7. 数据的生命周期：创建 -> 支付 -> 取消 -> 完成 -> 退款 -> 退款完成
- 建表
drop table if exists dwd_fact_order_info;
create external table dwd_fact_order_info (
`id` string COMMENT '订单编号',
`order_status` string COMMENT '订单状态',
`user_id` string COMMENT '用户id',
`out_trade_no` string COMMENT '支付流水号',
`create_time` string COMMENT '创建时间(未支付状态)',
`payment_time` string COMMENT '支付时间(已支付状态)',
`cancel_time` string COMMENT '取消时间(已取消状态)',
`finish_time` string COMMENT '完成时间(已完成状态)',
`refund_time` string COMMENT '退款时间(退款中状态)',
`refund_finish_time` string COMMENT '退款完成时间(退款完成状态)',
`province_id` string COMMENT '省份ID',
`activity_id` string COMMENT '活动ID',
`original_total_amount` decimal(16,2) COMMENT '原价金额',
`benefit_reduce_amount` decimal(16,2) COMMENT '优惠金额',
`feight_fee` decimal(16,2) COMMENT '运费',
`final_total_amount` decimal(16,2) COMMENT '订单金额'
)
PARTITIONED BY (`dt` string)
stored as parquet
location '/warehouse/gmall/dwd/dwd_fact_order_info/'
tblproperties ("parquet.compression"="lzo");
- 使用用函数
-- 传多个字符, 多字符拼接在一起, 如果里面有null, 则最终结果为null
select order_id, concat(order_status,'=', operate_time) from ods_order_status_log where dt='2020-06-25';
-- 数组
select order_id, collect_set(concat(order_status,'=',operate_time)) from ods_order_status_log where dt='2020-06-25' group by order_id;
-- 传一个分隔符, 和一个字符. 则会使用拼接起来
select order_id, concat_ws(',', collect_set(concat(order_status,'=',operate_time))) from ods_order_status_log where dt='2020-06-25' group by order_id;
-- map
select order_id, str_to_map(concat_ws(',',collect_set(concat(order_status,'=',operate_time))), ',' , '=') from ods_order_status_log where dt='2020-06-25' group by order_id;
- 插入数据
set hive.exec.dynamic.partition.mode=nonstrict;
SET hive.input.format=org.apache.hadoop.hive.ql.io.HiveInputFormat;
insert overwrite table dwd_fact_order_info partition(dt)
select
if(new.id is null,old.id,new.id),
if(new.order_status is null,old.order_status,new.order_status),
if(new.user_id is null,old.user_id,new.user_id),
if(new.out_trade_no is null,old.out_trade_no,new.out_trade_no),
if(new.tms['1001'] is null,old.create_time,new.tms['1001']),--1001对应未支付状态
if(new.tms['1002'] is null,old.payment_time,new.tms['1002']),
if(new.tms['1003'] is null,old.cancel_time,new.tms['1003']),
if(new.tms['1004'] is null,old.finish_time,new.tms['1004']),
if(new.tms['1005'] is null,old.refund_time,new.tms['1005']),
if(new.tms['1006'] is null,old.refund_finish_time,new.tms['1006']),
if(new.province_id is null,old.province_id,new.province_id),
if(new.activity_id is null,old.activity_id,new.activity_id),
if(new.original_total_amount is null,old.original_total_amount,new.original_total_amount),
if(new.benefit_reduce_amount is null,old.benefit_reduce_amount,new.benefit_reduce_amount),
if(new.feight_fee is null,old.feight_fee,new.feight_fee),
if(new.final_total_amount is null,old.final_total_amount,new.final_total_amount),
date_format(if(new.tms['1001'] is null,old.create_time,new.tms['1001']),'yyyy-MM-dd')
from
(
select
id,
order_status,
user_id,
out_trade_no,
create_time,
payment_time,
cancel_time,
finish_time,
refund_time,
refund_finish_time,
province_id,
activity_id,
original_total_amount,
benefit_reduce_amount,
feight_fee,
final_total_amount
from dwd_fact_order_info
where dt
in
(
select
date_format(create_time,'yyyy-MM-dd')
from ods_order_info
where dt='2020-06-25'
)
)old
full outer join
(
select
info.id,
info.order_status,
info.user_id,
info.out_trade_no,
info.province_id,
act.activity_id,
log.tms,
info.original_total_amount,
info.benefit_reduce_amount,
info.feight_fee,
info.final_total_amount
from
(
select
order_id,
str_to_map(concat_ws(',',collect_set(concat(order_status,'=',operate_time))),',','=') tms
from ods_order_status_log
where dt='2020-06-25'
group by order_id
)log
join
(
select * from ods_order_info where dt='2020-06-25'
)info
on log.order_id=info.id
left join
(
select * from ods_activity_order where dt='2020-06-25'
)act
on log.order_id=act.order_id
)new
on old.id=new.id;
4.3.4 DWD层业务数据导入
- 在/home/atguigu/bin目录下创建脚本ods_to_dwd_db.sh
[atguigu@hadoop102 bin]$ vim ods_to_dwd_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="
set mapreduce.job.queuename=hive;
set hive.exec.dynamic.partition.mode=nonstrict;
SET hive.input.format=org.apache.hadoop.hive.ql.io.HiveInputFormat;
insert overwrite table ${APP}.dwd_dim_sku_info partition(dt='$do_date')
select
sku.id,
sku.spu_id,
sku.price,
sku.sku_name,
sku.sku_desc,
sku.weight,
sku.tm_id,
ob.tm_name,
sku.category3_id,
c2.id category2_id,
c1.id category1_id,
c3.name category3_name,
c2.name category2_name,
c1.name category1_name,
spu.spu_name,
sku.create_time
from
(
select * from ${APP}.ods_sku_info where dt='$do_date'
)sku
join
(
select * from ${APP}.ods_base_trademark where dt='$do_date'
)ob on sku.tm_id=ob.tm_id
join
(
select * from ${APP}.ods_spu_info where dt='$do_date'
)spu on spu.id = sku.spu_id
join
(
select * from ${APP}.ods_base_category3 where dt='$do_date'
)c3 on sku.category3_id=c3.id
join
(
select * from ${APP}.ods_base_category2 where dt='$do_date'
)c2 on c3.category2_id=c2.id
join
(
select * from ${APP}.ods_base_category1 where dt='$do_date'
)c1 on c2.category1_id=c1.id;
insert overwrite table ${APP}.dwd_dim_coupon_info partition(dt='$do_date')
select
id,
coupon_name,
coupon_type,
condition_amount,
condition_num,
activity_id,
benefit_amount,
benefit_discount,
create_time,
range_type,
spu_id,
tm_id,
category3_id,
limit_num,
operate_time,
expire_time
from ${APP}.ods_coupon_info
where dt='$do_date';
insert overwrite table ${APP}.dwd_dim_activity_info partition(dt='$do_date')
select
info.id,
info.activity_name,
info.activity_type,
rule.condition_amount,
rule.condition_num,
rule.benefit_amount,
rule.benefit_discount,
rule.benefit_level,
info.start_time,
info.end_time,
info.create_time
from
(
select * from ${APP}.ods_activity_info where dt='$do_date'
)info
left join
(
select * from ${APP}.ods_activity_rule where dt='$do_date'
)rule on info.id = rule.activity_id;
insert overwrite table ${APP}.dwd_fact_order_detail partition(dt='$do_date')
select
id,
order_id,
user_id,
sku_id,
sku_num,
order_price,
sku_num,
create_time,
province_id,
source_type,
source_id,
original_amount_d,
if(rn=1,final_total_amount-(sum_div_final_amount-final_amount_d),final_amount_d),
if(rn=1,feight_fee-(sum_div_feight_fee-feight_fee_d),feight_fee_d),
if(rn=1,benefit_reduce_amount-(sum_div_benefit_reduce_amount-benefit_reduce_amount_d),benefit_reduce_amount_d)
from
(
select
od.id,
od.order_id,
od.user_id,
od.sku_id,
od.sku_name,
od.order_price,
od.sku_num,
od.create_time,
oi.province_id,
od.source_type,
od.source_id,
round(od.order_price*od.sku_num,2) original_amount_d,
round(od.order_price*od.sku_num/oi.original_total_amount*oi.final_total_amount,2) final_amount_d,
round(od.order_price*od.sku_num/oi.original_total_amount*oi.feight_fee,2) feight_fee_d,
round(od.order_price*od.sku_num/oi.original_total_amount*oi.benefit_reduce_amount,2) benefit_reduce_amount_d,
row_number() over(partition by od.order_id order by od.id desc) rn,
oi.final_total_amount,
oi.feight_fee,
oi.benefit_reduce_amount,
sum(round(od.order_price*od.sku_num/oi.original_total_amount*oi.final_total_amount,2)) over(partition by od.order_id) sum_div_final_amount,
sum(round(od.order_price*od.sku_num/oi.original_total_amount*oi.feight_fee,2)) over(partition by od.order_id) sum_div_feight_fee,
sum(round(od.order_price*od.sku_num/oi.original_total_amount*oi.benefit_reduce_amount,2)) over(partition by od.order_id) sum_div_benefit_reduce_amount
from
(
select * from ${APP}.ods_order_detail where dt='$do_date'
) od
join
(
select * from ${APP}.ods_order_info where dt='$do_date'
) oi
on od.order_id=oi.id
)t1;
insert overwrite table ${APP}.dwd_fact_payment_info partition(dt='$do_date')
select
pi.id,
pi.out_trade_no,
pi.order_id,
pi.user_id,
pi.alipay_trade_no,
pi.total_amount,
pi.subject,
pi.payment_type,
pi.payment_time,
oi.province_id
from
(
select * from ${APP}.ods_payment_info where dt='$do_date'
)pi
join
(
select id, province_id from ${APP}.ods_order_info where dt='$do_date'
)oi
on pi.order_id = oi.id;
insert overwrite table ${APP}.dwd_fact_order_refund_info partition(dt='$do_date')
select
id,
user_id,
order_id,
sku_id,
refund_type,
refund_num,
refund_amount,
refund_reason_type,
create_time
from ${APP}.ods_order_refund_info
where dt='$do_date';
insert overwrite table ${APP}.dwd_fact_comment_info partition(dt='$do_date')
select
id,
user_id,
sku_id,
spu_id,
order_id,
appraise,
create_time
from ${APP}.ods_comment_info
where dt='$do_date';
insert overwrite table ${APP}.dwd_fact_cart_info partition(dt='$do_date')
select
id,
user_id,
sku_id,
cart_price,
sku_num,
sku_name,
create_time,
operate_time,
is_ordered,
order_time,
source_type,
source_id
from ${APP}.ods_cart_info
where dt='$do_date';
insert overwrite table ${APP}.dwd_fact_favor_info partition(dt='$do_date')
select
id,
user_id,
sku_id,
spu_id,
is_cancel,
create_time,
cancel_time
from ${APP}.ods_favor_info
where dt='$do_date';
insert overwrite table ${APP}.dwd_fact_coupon_use partition(dt)
select
if(new.id is null,old.id,new.id),
if(new.coupon_id is null,old.coupon_id,new.coupon_id),
if(new.user_id is null,old.user_id,new.user_id),
if(new.order_id is null,old.order_id,new.order_id),
if(new.coupon_status is null,old.coupon_status,new.coupon_status),
if(new.get_time is null,old.get_time,new.get_time),
if(new.using_time is null,old.using_time,new.using_time),
if(new.used_time is null,old.used_time,new.used_time),
date_format(if(new.get_time is null,old.get_time,new.get_time),'yyyy-MM-dd')
from
(
select
id,
coupon_id,
user_id,
order_id,
coupon_status,
get_time,
using_time,
used_time
from ${APP}.dwd_fact_coupon_use
where dt in
(
select
date_format(get_time,'yyyy-MM-dd')
from ${APP}.ods_coupon_use
where dt='$do_date'
)
)old
full outer join
(
select
id,
coupon_id,
user_id,
order_id,
coupon_status,
get_time,
using_time,
used_time
from ${APP}.ods_coupon_use
where dt='$do_date'
)new
on old.id=new.id;
insert overwrite table ${APP}.dwd_fact_order_info partition(dt)
select
if(new.id is null,old.id,new.id),
if(new.order_status is null,old.order_status,new.order_status),
if(new.user_id is null,old.user_id,new.user_id),
if(new.out_trade_no is null,old.out_trade_no,new.out_trade_no),
if(new.tms['1001'] is null,old.create_time,new.tms['1001']),--1001对应未支付状态
if(new.tms['1002'] is null,old.payment_time,new.tms['1002']),
if(new.tms['1003'] is null,old.cancel_time,new.tms['1003']),
if(new.tms['1004'] is null,old.finish_time,new.tms['1004']),
if(new.tms['1005'] is null,old.refund_time,new.tms['1005']),
if(new.tms['1006'] is null,old.refund_finish_time,new.tms['1006']),
if(new.province_id is null,old.province_id,new.province_id),
if(new.activity_id is null,old.activity_id,new.activity_id),
if(new.original_total_amount is null,old.original_total_amount,new.original_total_amount),
if(new.benefit_reduce_amount is null,old.benefit_reduce_amount,new.benefit_reduce_amount),
if(new.feight_fee is null,old.feight_fee,new.feight_fee),
if(new.final_total_amount is null,old.final_total_amount,new.final_total_amount),
date_format(if(new.tms['1001'] is null,old.create_time,new.tms['1001']),'yyyy-MM-dd')
from
(
select
id,
order_status,
user_id,
out_trade_no,
create_time,
payment_time,
cancel_time,
finish_time,
refund_time,
refund_finish_time,
province_id,
activity_id,
original_total_amount,
benefit_reduce_amount,
feight_fee,
final_total_amount
from ${APP}.dwd_fact_order_info
where dt
in
(
select
date_format(create_time,'yyyy-MM-dd')
from ${APP}.ods_order_info
where dt='$do_date'
)
)old
full outer join
(
select
info.id,
info.order_status,
info.user_id,
info.out_trade_no,
info.province_id,
act.activity_id,
log.tms,
info.original_total_amount,
info.benefit_reduce_amount,
info.feight_fee,
info.final_total_amount
from
(
select
order_id,
str_to_map(concat_ws(',',collect_set(concat(order_status,'=',operate_time))),',','=') tms
from ${APP}.ods_order_status_log
where dt='$do_date'
group by order_id
)log
join
(
select * from ${APP}.ods_order_info where dt='$do_date'
)info
on log.order_id=info.id
left join
(
select * from ${APP}.ods_activity_order where dt='$do_date'
)act
on log.order_id=act.order_id
)new
on old.id=new.id;
"
sql2="
insert overwrite table ${APP}.dwd_dim_base_province
select
bp.id,
bp.name,
bp.area_code,
bp.iso_code,
bp.region_id,
br.region_name
from ${APP}.ods_base_province bp
join ${APP}.ods_base_region br
on bp.region_id=br.id;
"
sql3="
insert overwrite table ${APP}.dwd_dim_user_info_his_tmp
select * from
(
select
id,
name,
birthday,
gender,
email,
user_level,
create_time,
operate_time,
'$do_date' start_date,
'9999-99-99' end_date
from ${APP}.ods_user_info where dt='$do_date'
union all
select
uh.id,
uh.name,
uh.birthday,
uh.gender,
uh.email,
uh.user_level,
uh.create_time,
uh.operate_time,
uh.start_date,
if(ui.id is not null and uh.end_date='9999-99-99', date_add(ui.dt,-1), uh.end_date) end_date
from ${APP}.dwd_dim_user_info_his uh left join
(
select
*
from ${APP}.ods_user_info
where dt='$do_date'
) ui on uh.id=ui.id
)his
order by his.id, start_date;
insert overwrite table ${APP}.dwd_dim_user_info_his
select * from ${APP}.dwd_dim_user_info_his_tmp;
"
case $1 in
"first"){
$hive -e "$sql1$sql2"
};;
"all"){
$hive -e "$sql1$sql3"
};;
esac
- 脚本的使用
2）增加脚本执行权限
[atguigu@hadoop102 bin]$ chmod 777 ods_to_dwd_db.sh
3）初次导入
（1）时间维度表
参照4.4.5节数据装载
（2）用户维度表
参照4.4.14节拉链表初始化
（3）其余表
[atguigu@hadoop102 bin]$ ods_to_dwd_db.sh first 2020-06-25
4）每日定时导入
[atguigu@hadoop102 bin]$ ods_to_dwd_db.sh all 2020-06-26
4.3.5 总结
-- 1. 不同类型的事实表，一个分区存储什么数据？
a、事务型事实表，新增策略，每个分区存储当天新增的数据
b、周期型快照事实表：全量策略，每个分区存储当天的mysql中所有的数据
c、累积型快照事实表：新增变化策略，当天新分区存储新增的数据，同时会修改旧分区中改变的数据
-- 2. 拉链表与累积型事实表之间的差别
'相同点'：
a、同步策略相同
b、维护数据时，都涉及到修改原表中的旧数据
c、功能相似：都可以追溯业务的生命周期，保存了数据的历史状态
'不同点'：
a、'用途不同'：
累积型快照事实表：是事实表
拉链表：是维度表，而且是缓慢变化维
b、'保留历史数据的方式不同'：
累积型快照事实表：历史状态的数据保留在一行中
拉链表：一个状态一行数据
-- 3. 动态分区表必须设置非严格模式。
set hive.exec.dynamic.partition.mode=nonstrict;
-- 4. 关于存储格式问题：
1. ods层的数据：采用lzo压缩，因为dwd层从ods取数据时，基本上是全部要，并不会查某一列的数据。
2. 其他层的数据：采用列式存储
3. 列式存储：自带切片功能，在实际开发中，查询某一列的效率是非常高
4. lzo存储：只有lzop的压缩格式支持切片，而且需要创建索引文件才能实现切片。
5. dwd层读取ods的数据时，为了避免索引文件被读取到数据中，需要设置：这样索引文件会被过滤
SET hive.input.format=org.apache.hadoop.hive.ql.io.HiveInputFormat;
-- 5. 分区表：
ods、dwd、dws层数据为分区表
dwt、ads不是分区表。
扫一扫
2226
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
