数据仓库——上篇

最新推荐文章于 2024-05-15 19:28:32 发布

平平无奇小码农

最新推荐文章于 2024-05-15 19:28:32 发布

阅读量3.1k

点赞数 9

分类专栏：笔记文章标签：大数据

本文链接：https://blog.csdn.net/weixin_46669856/article/details/109522988

版权

笔记专栏收录该内容

31 篇文章 5 订阅

订阅专栏

文章目录

一、数仓分层
- 1.1 数仓的分层
二、数仓理论
三、数仓环境搭建
四、ODS层
五、DWD层

一、数仓分层

1.1 数仓的分层

1.1.1 ODS(原始数据层)层

将导入数仓的数据，保持原貌，不做任何处理！

举例：

日志： xxxx.lzo , 一条数据是一行！

ODS： xxxx.lzo, 1列

也是分区表！以日期作为分区字段！

1.1.2 DWD(数据明细层)层

将ODS层的数据，进行展开，将每个数据的明细抽取！

在抽取数据明细时，会根据要求，对数据进行ETL！

维度退化（降维）！

脱敏操作！

也是分区表！以日期作为分区字段！

1.1.3 DWS(数据服务层)

为数仓最终产生的数据，提供服务！

通过查询数据服务层提供的数据，或做一些计算，就可以想要的结果！

通常是按天，对所需的数据，进行聚合！

是分区表！以日期作为分区字段！

1.1.4 DWT(数据主题层)

也是数据服务层一种特殊实现！将数据按照主题，进行累积聚合！

DWT层是一张全量表！不是分区表！

一般情况不是分区表

1.1.5 ADS（数据应用层）

存放的是统计出的结果！可以直接提供给应用程序或其他的需求方！

一般情况不是分区表

什么时候建分区标，什么时候不建？
	分区表是为了在数据量大，分散数据！将数据分散到多个分区目录中，在过滤时，可以根据分区的字段只选择其中的某些目录进行查询

1.1.6 总结

ODS： O:原始数据

DWD： D：明细数据

DWS ： S：数据服务

DWT： T ：主题！累积的数据服务层！

ADS： A ：应用数据

注意：层与层之间，要严格按照顺序进行数据的摄取和导入！

ODS ----->DWD----->DWS----->DWT

DWS/DWT ---->ADS

二、数仓理论

2.1 范式、

范式在设计一张表时需要遵守的规范和模式！

没有范式，可能会造成

①数据存储的冗余

②更新数据时，数据的不一致性！

遵守范式，可以解决上述问题！

2.2 函数依赖

完全函数依赖：如果 Y 完全函数依赖于 X，代表由X的全部，才能推出Y，由X的一部分，不能推出Y!

部分函数依赖：如果Y部分函数依赖于X，此时代表，X的一部分就可以推出Y！

传递函数依赖：如果X可以推出 Y，Y可以推出Z，Y不能推出X，称 Z传递函数依赖于X！

X和Z本身无关系！借助Y，进行传递，才有关系！

特征：不能反推！

我今天很高兴

我今天买了一双鞋，花了500块

2.3 常见的三范式

第一范式：属性不可切割！

第二范式：表中除了主键列，其他的非主键列，都必须完全函数依赖于主键列！

不能出现部分函数依赖！

第三范式：不能有传递函数依赖！

设计表时，至少要遵守三范式！

2.4 关系建模和维度建模

关系建模(E-R建模)：用于关系型数据的表模型设计！

特征： ①强调数据的治理(精细)

②强掉数据的整合

③保证消除数据的冗余和保证数据强一致性

看起来比较复杂！

弊端：如果要求业务的全部信息，需要进行多次的关联！

维度建模：主要面向业务！不在乎冗余和强一致性！业务的实现怎么方便怎么来！

事实表 + 若干维度表

不太遵循范式！将多个维度，降维为一个维度！方便和事实表进行关联！减少关联次数！

主要用在大数据的应用场景上！

2.5 维度表和事实表

事实表：在表中记录一个事实（已经发生的事件，动作，度量）的信息！

我今天中午去津味源吃了一份20元的套餐，3个菜

我和同桌今天中午去津味源吃了一份20元的套餐，3个菜

事实表基本都有以下元素：

人物时间地点度量

who when where

维度表：用来描述事实中的部分属性，一般都是一些名词

2.6 维度建模的模型

星型模型：维度表直接关联在事实表上！当查询某个维度时，最多只需要关联一次！

维度建模中使用的最多的！

雪花模型：类似关系建模！存在维度表间接关联在事实表上！当查询某个维度时，可能需要关联多次！

星座模型：本质上也是星型模型！是星型模型的一个变种！

可能有多个事实表！维度表还是直接关联在事实表上！存在多个事实表共有一个维度表！

2.7 事实表的分类

事务型事实表：如果一类事实，一旦发生，就不会变化。记录这类事实的表，称为事务型事实表！

这个表的特点是表中的数据只会不断新增，不会发生修改！

举例：支付信息详情表！

事务型事实表在同步数据时，只同步当天新增的数据！

周期型快照事实表：如果某个事实，在一个周期内会不断发生变化，只需要记录在周期结束时，事实的状态，此时这类表称为周期型快照事实表！

举例：记录一个人身高生长的事实

人	时间	身高
jack	2020-1-1	60
jack	2021-1-1	60
jack	2022-1-1	80

周期型快照事实表: 事实的记录会有周期，重视周期结束时，事实的状态（结果）！

累积型快照事实表：如果一个事实，在其生命周期内，不断变化！只记录在某些时间点的状态变化，且可以查看一个状态的累积变化趋势，称这类表为累积型快照事实表！

举例：记录一个人身高生长的事实

人	出生	3岁时	12岁（青春期）	18（成年时）	22（成人时）
jack	40	70	120	180	183

分类是为了，总结同一类型表导入数据的方式！方便向表中导入数据！

2.8 数据仓库建模

2.8.1 ODS层

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

2.8.2 DWD层

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

维度建模一般按照一下四个步骤：

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

（1）选择业务过程

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

（2）声明粒度

数据粒度数据仓库的数据中保存数据的细化程度或者总和程度的级别。
声明粒度意味着精确定义事实表中的一行数据表示什么，应尽可能选择最小粒度，以此来应各种各样的需求。

典型的粒度声明如下：
	订单详情表中，每行数据对应一个订单中的一个商品项，
	订单表中，每行数据对应一个订单

（3）确定维度

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

（4）确定事实

此处的“事实”一词，指定是业务中的度量值，例如订单金额、下单次数等。

在DWD层以业务过程为建模驱动，基于每个具体业务过程的特点，构建最细粒度的明细层事实表，事实表可做适当的宽表化处理

至此，数仓的维度建模已经完毕，DWS、DWT和ADS和维度建模已经没有关系了

DWS和·DWT都是建宽表，宽表都是按照主题去建。主题相当于观察问题的角度，对应着维度表

2.8.3 DWS层

统计各个主题对象的当天行为，服务于DWT层的主题宽表。

如下图所示，DWS层的宽表字段，是站在不同维度的视角去看事实表，重点关注事实表的度量值，通过与之关联的事实表，获得不同的事实表的度量值。

2.8.4 DWT层

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

DWT层主题宽表都记录什么字段？

如下图所示，每个维度关联的不同事实表度量值以及首次、末次时间、累积至今的度量值、累积某个时间段的度量值。

2.8.5 ADS层

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

2.9 每层分别做了哪些事？

2.9.1 ODS层

1)保持数据原貌，不做任何修改
2压缩采用LZO，压缩比是100g数据压缩完10g左右
3）创建分区表

2.9.2 DWD层

1.数据清洗
	a.空值去除
	b.过滤核心字段无意义的数据，比如订单表id为null，支付表中支付id为空
	c.将用户行为宽表和业务表进行数据一致性处理
2.清洗手段
	sql、mr、rdd、keetle、python(项目中采用sql进行清除)
3.清洗掉多少数据算合理
	1万条数据清洗掉1条
4.脱敏
	对手机号，身份证号等敏感数据脱敏
5.维度退化
	对业务数据传过来的表进行维度退化和降维。（商品一级，二级，三级）7
6.压缩LZO
7.列式存储parqued

2.9.3 DWS层

1.dws层有3-5张宽表（处理100-200个指标  70%以上的需求）
	设备宽表，注册用户宽表，商品宽表，活动宽表，地区宽表，优惠券宽表
	具体宽表名称：
		用户行为宽表，用户购买商品明细行为宽表，商品宽表，购物车宽表，物流宽表，登录注册，售后等
2.评论、打赏、收藏、关注-商品、关注-人、点赞、分享、好价爆料、文章发布、活跃、签到、补签卡、幸运星、幸运屋、礼品、金币、电商点击、gmv、

2.9.4 ADS层

--分析过的指标：
	日活、月活、周活、留存、留存率、新增（日，周，年）、转化率、流失、回流、七天内连续三天登录（点赞、收藏、评价、够买、加够、下单、活动）、连续3周（月）登录、GMV、复购率、复购率排行、点赞、评论、收藏、领优惠券人数、使用优惠券、沉默、值不值得买、退款人数、退款率、topn、热门商品

--留转G复活指标
	活跃：
		日活：100万
		月活：是日活的2-3倍   300万
		总注册的用户是多少？	1000万-3000万之间
	GMV:
		GMV：每天10万订单（50-100元）  500万-1000万
		10% - 20%  100万 - 200万（人员：程序员）
	复购率：
		某日常商品复购：（手纸、面膜、牙膏）10% - 20%
		电脑、显示器、手表  1%
	转化率：
		商品详情    ->     加购物车   ->     下单    ->    支付
			    5% - 10%          60 -70%      90 - 95%  
	留存率：
		1/2/3、周留存、月留存
		搞活动：10-20%

ADS层手写指标：
	如何分析用户活跃？ 		--在启动日志中统计不同设备id出现次数、去重
	如何分析用户新增？ 		--用活跃用户表 left  join 用户新增表，用户新增表中mid为空的即为新增。
	如何分析用户1天了留存？   --留存用户 = 前一天新增join  今天活跃    用户留存率 = 留存用户/前一天新增用户
    如何分析沉默用户？		--登录时间为七天前，且只出现过一次  按照设备id对日活表分组，登录次数为1，且是在一周前登录
    如何分析本周回流用户？    --本周活跃left join 本周新增 left join 上周活跃 ， 且本周新增id和上周活跃id都为null
    如何分析流失用户？       --（登录时间为7天前）  按照设备id对周活进行分组，统计次数大于3次
    如何分析最近七天内连续三天活跃用户数？   
    					 --查询出最近七天的活跃用户，并对用户活跃日期进行排名
    					 --计算用户活跃日期及排名之间的差值
    					 --对同用户及差值分组，统计差个数
    					 --将差值相同个数大于等于3的数据取出，然后去重（去的是什么重？），即为连续三天及以上活跃的用户
    7天连续收藏、点赞、够买、加购、付款、浏览、商品点击、退货
    1个月连续7天
    连续两周

分析过最难的指标：
	最近连续3周活跃用户：
		
	最近7天连续3天活跃用户数：

三、数仓环境搭建

3.1 Hive环境配置

3.1.1 hive on spark \ spark on hive \ spark s q l 三者的区别

Spark SQ L :代表用sparkSql的数据分析模快分析数据，他的数据可以来自于各种数据源，也可以不来源于hive，可以在hdfs上。

用sparksql 来分析hive上的数据，叫做spark on hive ，是spark s q l的一种使用场景，本质还是用的spark，内置了hive，去读hive管理起来的元数据，hive进行一系列建表，建库操作，就是往mysql里面写入了一系列元数据，spark on hive 内置了hive的jar包，只要配一个hive的配置文件。我就可以读mysql的数据，就是hive写他自己的，我spark on hive 可以通过你的元数据服务直接读数据库的东西，你不配配置文件，我就可以借助hive的元数据服务

hive on spark 本质就是hive ，引擎要换成spark用的是hive的cli，写到就是hql语句，符合hive的语法规范，hql语句会翻译成指定引擎支持的job类型，比如你把引擎配置成spark，此时就要翻译成一个spark的job，此时要有客户端和集群的概念

【写sparkcontest创建是要指定一个master，又是三种模式，（local模式，standalone模式，yarn模式）当hql语句生成一个job时，就好比拿了一个idea写成另一个程序，打成了jar包，把jar包要提交到spark的集群，用suparksubmit的东西，后面指定了一堆参数，比如master参数指定为yarn】

此时hive就想相当于客户端

sparn-on-yarn就相当于集群

--配置时候：

第一步：

			需要装hive客户端

			告诉hive要把hql语句翻译成sparkjob

第二步：

			客户端向集群提交job包，所以要配置cluster，保证spark -on-yarn 正常工作，保证hql语句能够提交

3.1.2 具体步骤

3.1.2.1 检查spark-yarn是否正常

①启动hadoop，开启yarn界面监控。Hadoop103:8088

②到spark-yarn查看/conf/spark-default文件参数

--【配置每个job提怎么把日志写到目录里面】
spark.eventLog.enabled true
#需要手动创建
spark.eventLog.dir hdfs://hadoop102:8020/sparklogs


--【和yarn的历史服务做继承】
##spark历史服务的地址和端口 
spark.yarn.historyServer.address=hadoop103:18080
spark.history.ui.port=18080

spark.master yarn

--sparksql默认建库
spark.sql.warehouse.dir  hdfs://hadoop102:8020/user/hive/warehouse

③测试bin/spark-shell

[atguigu@hadoop102 spark-yarn]$ bin/spark-shell

输入测试语句

val result = sc.textFile("hdfs://hadoop102:8020/input")
      .flatMap(_.split(" "))
      .map((_, 1))
      .reduceByKey(_ + _,3)
          .reduceByKey(_ + _,2)
      .collect().mkString(",")

执行是否ok

3.1.2.2 安装hive

[1] 查看hive/conf目录下的hive-site.xml文件

	#以下四个是配数据库的
	<property>
        <name>javax.jdo.option.ConnectionURL</name>
        <value>jdbc:mysql://hadoop102:3306/metastore?useSSL=false</value>
        #--【要想让mysql能够读取中文，这里需要修改】
    </property>

    <property>
        <name>javax.jdo.option.ConnectionDriverName</name>
        <value>com.mysql.jdbc.Driver</value>
    </property>

    <property>
        <name>javax.jdo.option.ConnectionUserName</name>
        <value>root</value>
    </property>

    <property>
        <name>javax.jdo.option.ConnectionPassword</name>
        <value>123321</value>
    </property>
#以下是配元数据版本校验
 	<property>
        <name>hive.metastore.schema.verification</name>
        <value>false</value>
    </property>

[2] 复制mysql驱动

cp /opt/module/spark-yarn/jars/mysql-connector-java-5.1.27-bin.jar ./lib

[3] 修改hive配置文件，让它能够读取中文

<property>
        <name>javax.jdo.option.ConnectionURL</name>
        <value>
        jdbc:mysql://hadoop102:3306
        /metastore?createDatabaseIfNotExist=true
        &amp;
        useUnicode=true
        &amp;
        characterEncoding=UTF-8</value>
    </property>

[4] 初始化数据库

schematool -dbType mysql -initSchema

[5] 接着在sqlyog软件执行

#修改字段注释字符集
alter table COLUMNS_V2 modify column COMMENT varchar(256) character set utf8;
#修改表注释字符集
alter table TABLE_PARAMS modify column PARAM_VALUE varchar(20000) character set utf8;
#修改分区参数，支持分区建用中文表示
alter table PARTITION_PARAMS modify column PARAM_VALUE varchar(20000) character set utf8;
alter table PARTITION_KEYS modify column PKEY_COMMENT varchar(20000) character set utf8;
#修改索引名注释，支持中文表示
alter table INDEX_PARAMS modify column PARAM_VALUE varchar(4000) character set utf8;
#修改视图，支持视图中文
ALTER TABLE TBLS modify COLUMN VIEW_EXPANDED_TEXT mediumtext CHARACTER SET utf8;
ALTER TABLE TBLS modify COLUMN VIEW_ORIGINAL_TEXT mediumtext CHARACTER SET utf8;

这时候创建一张表有中文注释就不会乱码了

查看表的具体信息

desc formatted 表名;

3.2 hive on spark 配置

问：hive怎么知道当前机器安装了spark？

答：需要配置一个环境变量

①配置spark环境变量

sudo vim /etc/profile.d/my_env.sh

#spark_yarn
export SPARK_HOME = /opt/module/spark-yarn

然后source一下
source /etc/profile.d/my_env.sh

然后来到cd /opt/module/hive/conf/的目录下

修改配置文件hive-site.xml,加入以下配置文件

②修改hive-site.xml配置文件

--【注意下面这个参数：spark的jars目录下有很多jar包，提交job时，是提交到yarn上，yarn是分布式运算，有可能在集群的任何位置，101,102,103等任一台机器上，101,102启动executor来运行提交的jar包（我们使用spark-submit命令提交我们写的wc.jar包）,这个jar包只有我们写的drive类，没有spark的依赖（依赖没有在打包的时候打进来），那么在executor端要运行，executor需要加载spark的jar包，有可能当前机器没有安装spark，即是没有spark的jar包，此时建议制定一个参数（spark.yarn.jars 或者 spark.yarn.archive[archive是归导的意思，能够将一对jar包打包成tar.gz包]），参数指定的路径是hdfs的路径，这时候执行时就通过这个路径来读取jar包，如果没有设置程序就自己回头把jar上传上去】

<!--Spark依赖位置-->
<property>
    <name>spark.yarn.jars</name> 
    【为了让分布式运算的每个executor都能读到提交的jar包】
    【hive是提交sparkjob的客户端，此时这个hive和spark-shell是一样的，都是一个应用程序】
    <value>hdfs://hadoop102:8020/spark-jars/*</value>【告诉客户端spark的程序在哪个路径，让程序能够根据这个程序来读取jar包】
</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>

③在hdfs上创建spark-jars目录

在hdfs上创建目录/spark-jars上传jar包

上传spark的jars的纯净的jar包

先传到linux上
$ hadoop fs -put jars/* /spark-jars

hadoop fs -put /home/atguigu/origin_data /

验证一下是否成功;

启动hive
hive
创建一个表
create table student(id int, name string);
插入数据
insert into table student values(1,'abc');

④验证是否成功

执行完成界面显示

hive> insert into table student values(2,'def');
Query ID = atguigu_20201019130301_20a5827b-caf2-4b36-b8ce-ee8f7e577e46
Total jobs = 1
Launching Job 1 out of 1
In order to change the average load for a reducer (in bytes):
  set hive.exec.reducers.bytes.per.reducer=<number>
In order to limit the maximum number of reducers:
  set hive.exec.reducers.max=<number>
In order to set a constant number of reducers:
  set mapreduce.job.reduces=<number>
--------------------------------------------------------------------------------------
          STAGES   ATTEMPT        STATUS  TOTAL  COMPLETED  RUNNING  PENDING  FAILED  
--------------------------------------------------------------------------------------
Stage-2 ........         0      FINISHED      1          1        0        0       0  
Stage-3 ........         0      FINISHED      1          1        0        0       0  
--------------------------------------------------------------------------------------
STAGES: 02/02    [==========================>>] 100%  ELAPSED TIME: 1.01 s     
--------------------------------------------------------------------------------------
Spark job[1] finished successfully in 1.01 second(s)
Loading data to table default.student
OK

则成功

再看Hadoop03:8088

就是一个app类似于spark-shell,spark-shell是代码，hive提交的是hql语句

你会发现一直在运行，点击application会发现是spark监控界面

3.3 扩充hive队列【Yarn容量调度器单队列问题演示】

3.3.1 引言

有一个问题待解决

只有一个默认的调度器，yarn是给很多程序使用订单，我们希望hive使用自己的队列

Yarn容量调度器单队列问题演示

Yarn默认调度器为Capacity Scheduler（容量调度器），且默认只有一个队列——default。该调度器单个队列内的调度策略为FIFO，故单个队列的任务并行度为1，即一个队列，同一时刻，只能有一个任务运行。以下案例，可印证上述说法。

*（1）启动1个hive客户端，执行以下插入数据的sql语句。*

hive (default)> insert into table student values(1,'abc');

执行该语句，hive会初始化一个Spark Session，用以执行hive on spark任务。由于未指定队列，故该Spark Session默认占用使用的就是default队列，且会一直占用该队列，直到退出hive客户端。

可访问ResourceManager的web页面查看相关信息。

*（2）在hive客户端开启的状态下，提交一个MR。*

[atguigu@hadoop102 ~]$ hadoop jar /opt/module/hadoop-3.1.3/share/hadoop/mapreduce/hadoop-mapreduce-examples-3.1.3.jar pi 1 1

MR任务同样未指定队列，所以其默认也提交到了default队列，由于容量调度器单个队列的并行度为1。故后提交的MR任务会一直等待，不能开始执行。

任务提交界面如下：

ResourceManager的web页面如下：

*（3）总结*

通过上述案例，可看出，Yarn的容量调度器的单个队列的并行度为1，很显然，这不能满足生产环境的需要，故需给容量调度器配置多队列。

3.3.2 具体操作

①修改hadoop的capacity-scheduler.xml

cd /opt/module/hadoop/etc/hadoop/

#【修改capacity-scheduler.xml文件名字】
[atguigu@hadoop102 hadoop]$ mv capacity-scheduler.xml capacity-scheduler.xml.bak  

#创建一个新的capacity-scheduler.xml文件
[atguigu@hadoop102 hadoop]$ vim capacity-scheduler.xml

capacity-scheduler.xml文件如下

<!--
  Licensed under the Apache License, Version 2.0 (the "License");
  you may not use this file except in compliance with the License.
  You may obtain a copy of the License at

    http://www.apache.org/licenses/LICENSE-2.0

  Unless required by applicable law or agreed to in writing, software
  distributed under the License is distributed on an "AS IS" BASIS,
  WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
  See the License for the specific language governing permissions and
  limitations under the License. See accompanying LICENSE file.
-->
<configuration>

   <!-- 容量调度器最多可以容纳多少个job-->
  <property>
    <name>yarn.scheduler.capacity.maximum-applications</name>
    <value>10000</value>
    <description>
      Maximum number of applications that can be pending and running.
    </description>
  </property>

  <!-- 当前队列中启动的MRAppMaster进程，所占用的资源可以达到队列总资源的多少
		通过这个参数可以限制队列中提交的Job数量
  -->
  <property>
    <name>yarn.scheduler.capacity.maximum-am-resource-percent</name>
    <value>0.5</value>
    <description>
      Maximum percent of resources in the cluster which can be used to run 
      application masters i.e. controls number of concurrent running
      applications.
    </description>
  </property>

  <!-- 为Job分配资源时，使用什么策略进行计算
  -->
  <property>
    <name>yarn.scheduler.capacity.resource-calculator</name>
    <value>org.apache.hadoop.yarn.util.resource.DefaultResourceCalculator</value>
    <description>
      The ResourceCalculator implementation to be used to compare 
      Resources in the scheduler.
      The default i.e. DefaultResourceCalculator only uses Memory while
      DominantResourceCalculator uses dominant-resource to compare 
      multi-dimensional resources such as Memory, CPU etc.
    </description>
  </property>

   <!-- root队列中有哪些子队列-->
  <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>

  <!-- root队列中default队列占用的容量百分比
		所有子队列的容量相加必须等于100
  -->
  <property>
    <name>yarn.scheduler.capacity.root.default.capacity</name>
    <value>30</value>
    <description>Default queue target capacity.</description>
  </property>
  
  <property>
    <name>yarn.scheduler.capacity.root.hive.capacity</name>
    <value>70</value>
    <description>Default queue target capacity.</description>
  </property>
  
  

    <!-- 队列中用户能使用此队列资源的极限百分比
  -->
  <property>
    <name>yarn.scheduler.capacity.root.default.user-limit-factor</name>
    <value>1</value>
    <description>
      Default queue user limit a percentage from 0.0 to 1.0.
    </description>
  </property>
  
   <property>
    <name>yarn.scheduler.capacity.root.hive.user-limit-factor</name>
    <value>1</value>
    <description>
      Default queue user limit a percentage from 0.0 to 1.0.
    </description>
  </property>
 

  <!-- root队列中default队列占用的容量百分比的最大值
  -->
  <property>
    <name>yarn.scheduler.capacity.root.default.maximum-capacity</name>
    <value>100</value>
    <description>
      The maximum capacity of the default queue. 
    </description>
  </property>
  
   <property>
    <name>yarn.scheduler.capacity.root.hive.maximum-capacity</name>
    <value>100</value>
    <description>
      The maximum capacity of the default queue. 
    </description>
  </property>
  
  

    <!-- root队列中每个队列的状态
  -->
  <property>
    <name>yarn.scheduler.capacity.root.default.state</name>
    <value>RUNNING</value>
    <description>
      The state of the default queue. State can be one of RUNNING or STOPPED.
    </description>
  </property>
  
    <property>
    <name>yarn.scheduler.capacity.root.hive.state</name>
    <value>RUNNING</value>
    <description>
      The state of the default queue. State can be one of RUNNING or STOPPED.
    </description>
  </property>

  
  <!-- 限制向default队列提交的用户-->
  <property>
    <name>yarn.scheduler.capacity.root.default.acl_submit_applications</name>
    <value>*</value>
    <description>
      The ACL of who can submit jobs to the default queue.
    </description>
  </property>
  
  <property>
    <name>yarn.scheduler.capacity.root.hive.acl_submit_applications</name>
    <value>*</value>
    <description>
      The ACL of who can submit jobs to the default queue.
    </description>
  </property>
  

  <property>
    <name>yarn.scheduler.capacity.root.default.acl_administer_queue</name>
    <value>*</value>
    <description>
      The ACL of who can administer jobs on the default queue.
    </description>
  </property>
  
  <property>
    <name>yarn.scheduler.capacity.root.hive.acl_administer_queue</name>
    <value>*</value>
    <description>
      The ACL of who can administer jobs on the default queue.
    </description>
  </property>
  
 

  <property>
    <name>yarn.scheduler.capacity.node-locality-delay</name>
    <value>40</value>
    <description>
      Number of missed scheduling opportunities after which the CapacityScheduler 
      attempts to schedule rack-local containers. 
      Typically this should be set to number of nodes in the cluster, By default is setting 
      approximately number of nodes in one rack which is 40.
    </description>
  </property>

  <property>
    <name>yarn.scheduler.capacity.queue-mappings</name>
    <value></value>
    <description>
      A list of mappings that will be used to assign jobs to queues
      The syntax for this list is [u|g]:[name]:[queue_name][,next mapping]*
      Typically this list will be used to map users to queues,
      for example, u:%user:%user maps all users to queues with the same name
      as the user.
    </description>
  </property>

  <property>
    <name>yarn.scheduler.capacity.queue-mappings-override.enable</name>
    <value>false</value>
    <description>
      If a queue mapping is present, will it override the value specified
      by the user? This can be used by administrators to place jobs in queues
      that are different than the one specified by the user.
      The default is false.
    </description>
  </property>

</configuration>

② 分发

xsync capacity-scheduler.xml

③重启yarn

stop-yarn.sh
start-yarn.sh

注意：有时重启不行，需要刷新界面

yarn rmadmin -refreshQueues

④查看Hadoop03:8088界面

⑤修改hive-site.xml文件

添加如下参数

<!--Hive的任务提交到hive队列-->
<property>
    <name>mapreduce.job.queuename</name>
    <value>hive</value>
</property>

⑥ 重启hive

再次添加数据，可见hive任务在hive消息队列执行

3.4 总结

3.4.1 hive on spark 环境配置总结

要求：①安装Spark，配置Spark on YARN

在Hive所在的机器安装！

配置和导出SPARK_HOME到全局变量

②安装Hive

安装和Spark对应的版本一起编译的Hive

配置元数据到Mysql，修改对中文注释的支持

③配置Hive on spark

④上传纯净的spark的jars中的jar包导、spark-jars

3.4.2 配置容量调度器总结

①配置capacity-scheduler.xml文件

②分发该文件

四、ODS层

4.1 创建数据库

4.1.1 启动hive

[atguigu@hadoop102 hive]$ bin/hive

4.1.2 显示数据库

hive (default)> show databases;

4.1.3 创建数据库

hive (default)> create database gmall;

4.1.4 使用数据库

hive (default)> use gmall;

查看表的详细信息

desc formatted student;

	采集文件的格式是TextFile,用来lzo压缩了，lzo压缩的数据文件只能指定一个特定的输入格式来读com.hadoop.mapred.DeprecatedLzoTextInputFormat

4.2 日志数据

4.2.1 创建表

① 创建支持lzo压缩的分区表 ---- （日志表o d s_log ）

hive (gmall)> 
drop table if exists ods_log;
CREATE EXTERNAL TABLE ods_log (`line` string)
PARTITIONED BY (`dt` string) -- 按照时间创建分区
STORED AS -- 指定存储方式，读数据采用LzoTextInputFormat；
  INPUTFORMAT 'com.hadoop.mapred.DeprecatedLzoTextInputFormat'
  OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
LOCATION '/warehouse/gmall/ods/ods_log'  -- 指定数据在hdfs上的存储位置
;

4.2.2 导入数据

因为ods层是将数据保持原貌，不作任何改动，所以你只是将数据从 / origin_data / gmall / applog / topic_log移动到hive的路径下，用load就可以，数据在hdfs所以不用写local，load命令如果是hdfs上的命令，则是move（移动）操作

在hive下
hive> use gmall;
hive> load data inpath '/origin_data/gmall/applog/topic_log/2020-10-10' overwrite into table ods_log partition(dt='2020-10-10');

如果数据特别大，lzo压缩的文件，切片是通过建索引的方式，根据索引对文件进行切片，所以创建索引

4.2.3 创建索引

[atguigu@hadoop102 ~]$ 
hadoop jar /opt/module/hadoop/share/hadoop/common/hadoop-lzo-0.4.20.jar com.hadoop.compression.lzo.DistributedLzoIndexer /warehouse/gmall/ods/ods_log/dt=2020-10-10

这时就有一个index结尾的索引文件

4.2.4 hdfs_to_ods_log.sh脚本

【将采集到的hdfs上的数据倒入到hive上】

脚本模板

# !bin/base
#接受外部传入的日期
# -n 是一个逻辑运算符，用于判断后面的字符串长度是否为0，为0返回false，否则返回true
if [ -n "$1" ]
then
	do_date=$1
else
#默认使用当前日期的前一天
	do_date=$(date -d 'yesterday' '+%F')
fi

#echo $do_date

#声明hql
hql="
use gmall;

"
hive -e "$hql"

hdfs_to_ods_log.sh

#!/bin/bash
#接受外部传入的数据
#-n是一个逻辑运算符，用于判断后面的字符串长度是否为0，为0返回false，否则不为0返回true
if [ -n "$1" ]
then
        do_date=$1
else
        #默认使用当前日期的前一天
        do_date=$(date -d 'yesterday' '+%F')
fi

#echo $do_date

#声明hql
hql="
use gmall;
load data inpath '/origin_data/gmall/log/topic_log/$do_date' overwrite into table ods_log partition(dt='$do_date');
"
hive -e "$hql"

hadoop jar /opt/module/hadoop/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

执行脚本

hdfs_to_ods_log.sh 参数为你要导入的日期

4.3 业务数据

4.3.1 创建表

已经将mysql的表导入到hdfs上，现在要保持数据原貌不动导入到ods层

以订单表为例

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' -- 指定分割符为\t 
STORED AS -- 指定存储方式，读数据采用LzoTextInputFormat；输出数据采用TextOutputFormat
  INPUTFORMAT 'com.hadoop.mapred.DeprecatedLzoTextInputFormat'
  OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
location '/warehouse/gmall/ods/ods_order_info/' -- 指定数据在hdfs上的存储位置
;

已将建表语句直接写成.sql 文件

如果要在ods层建表执行一下语句
[atguigu@hadoop102 ~]$ hive -f ods_tables.sql

4.3.2 导入数据脚本

hdfs_to_ods_db.sh

# !bin/base
#接受外部传入的日期
# -n 是一个逻辑运算符，用于判断后面的字符串长度是否为0，为0返回false，否则返回true
if [ -n "$2" ]
then
	do_date=$2
else
#默认使用当前日期的前一天
	do_date=$(date -d 'yesterday' '+%F')
fi

#echo $do_date

#声明hql
sql1=" 
use gmall;

load data inpath '/origin_data/gmall/db/order_info/$do_date' OVERWRITE into table ods_order_info partition(dt='$do_date');

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

sql2=" 
use gmall;

load data inpath '/origin_data/gmall/db/base_province/$do_date' OVERWRITE into table ods_base_province;

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

注意这里有两个sql，第一次导的话，两个都要导，后面导的时候，sql2不用导

该脚本只能一次一天的导如数据

测试

hdfs_to_ods_db.sh first 参数为你要导入的日期

补充：

用mysql_to_hdfs.sh脚本生成base_province的数据

mysql_to_hdfs.sh base_province【表名】 2020-10-13【日期】

mysql_to_hdfs.sh base_region 2020-10-13
mysql_to_hdfs.sh base_region 2020-10-14

mysql_to_hdfs.sh base_province 2020-10-13
mysql_to_hdfs.sh base_province 2020-10-14

4.4 总结

1.导入数据到hdfs：hadoop -put /home/atguigu/origin_data /

2.创建ods层的所有表：hive -f ods_tables.sql

3.将hdfs的日志数据导入到ods层：执行脚本：hdfs_to_ods_log.sh 日期

4.将hdfs的业务数据导入到ods层：执行脚本:hdfs_to_ods_db.sh first/all/other 日期

五、DWD层

本层用于抽取明细信息

表的存储

InputFormat: 切片的结果需要看使用什么类型的InputFormat!

层	表的存储格式	是否压缩	是否可切片（只看存储格式）
ODS	TEXTFILE	lzo	lzo可切，可切
DWD	PARQUET	lzo	可切
	ORC		可切

2.1的spark不支持orc
只要是hadoop的框架都支持
可不可切只跟表中存储文件格式相关，只有是TEXTFILE时，才看压缩格式所有列式存储的格式都是

5.1 用户行为数据

日志数据实例

①启动日志：

｛ common：” xxx“

                       start：”xxxx“，
					err：“xxx”,
                      ts:"启动行为发生的时间戳"
｝

{
  "common": {
    "ar": "370000",
    "ba": "Honor",
    "ch": "wandoujia",
    "md": "Honor 20s",
    "mid": "eQF5boERMJFOujcp",
    "os": "Android 11.0",
    "uid": "76",
    "vc": "v2.1.134"
  },
  "start": {   
    "entry": "icon",         --icon手机图标  notice 通知   install 安装后启动
    "loading_time": 18803,  --启动加载时间
    "open_ad_id": 7,        --广告页ID
    "open_ad_ms": 3449,    -- 广告总共播放时间
    "open_ad_skip_ms": 1989   --  用户跳过广告时点
  },
"err":{                     --错误
"error_code": "1234",      --错误码
    "msg": "***********"       --错误信息
},
  "ts": 1585744304000
}

{"common":{"ar":"420000","ba":"iPhone","ch":"Appstore","md":"iPhone 8","mid":"mid_991","os":"iOS 13.3.1","uid":"418","vc":"v2.1.134"},
 "page":{"during_time":3336,"item":"3,7","item_type":"sku_ids","last_page_id":"trade","page_id":"payment"},"ts":1583769315209}

②事件日志：

{
"common":{}
"actions":[

]

page:{},
err:{},
ts:xxx
}

{
  "common": {                  -- 公共信息
    "ar": "230000",              -- 地区编码
    "ba": "iPhone",              -- 手机品牌
    "ch": "Appstore",            -- 渠道
    "md": "iPhone 8",            -- 手机型号
    "mid": "YXfhjAYH6As2z9Iq", -- 设备id
    "os": "iOS 13.2.9",          -- 操作系统
    "uid": "485",                 -- 会员id
    "vc": "v2.1.134"             -- app版本号
  },
"actions": [                     --动作(事件)  
    {
      "action_id": "favor_add",   --动作id
      "item": "3",                   --目标id
      "item_type": "sku_id",       --目标类型
      "ts": 1585744376605           --动作时间戳
    }
  ]，
  "displays": [
    {
      "displayType": "query",        -- 曝光类型
      "item": "3",                     -- 曝光对象id
      "item_type": "sku_id",         -- 曝光对象类型
      "order": 1                        --出现顺序
    },
    {
      "displayType": "promotion",
      "item": "6",
      "item_type": "sku_id",
      "order": 2
    },
    {
      "displayType": "promotion",
      "item": "9",
      "item_type": "sku_id",
      "order": 3
    },
    {
      "displayType": "recommend",
      "item": "6",
      "item_type": "sku_id",
      "order": 4
    },
    {
      "displayType": "query ",
      "item": "6",
      "item_type": "sku_id",
      "order": 5
    }
  ],
  "page": {                       --页面信息
    "during_time": 7648,        -- 持续时间毫秒
    "item": "3",                  -- 目标id
    "item_type": "sku_id",      -- 目标类型
    "last_page_id": "login",    -- 上页类型
    "page_id": "good_detail",   -- 页面ID
    "sourceType": "promotion"   -- 来源类型
  },
"err":{                     --错误
"error_code": "1234",      --错误码
    "msg": "***********"       --错误信息
},
  "ts": 1585744374423  --跳入时间戳
}

{"common":{"ar":"420000","ba":"iPhone","ch":"Appstore","md":"iPhone 8","mid":"mid_991","os":"iOS 13.3.1","uid":"418","vc":"v2.1.134"},"displays":[{"displayType":"promotion","item":"10","item_type":"sku_id","order":1},{"displayType":"query","item":"10","item_type":"sku_id","order":2},{"displayType":"query","item":"10","item_type":"sku_id","order":3},{"displayType":"promotion","item":"5","item_type":"sku_id","order":4},{"displayType":"query","item":"3","item_type":"sku_id","order":5},{"displayType":"query","item":"7","item_type":"sku_id","order":6},{"displayType":"query","item":"5","item_type":"sku_id","order":7},{"displayType":"recommend","item":"1","item_type":"sku_id","order":8},{"displayType":"query","item":"10","item_type":"sku_id","order":9},{"displayType":"query","item":"6","item_type":"sku_id","order":10}],"page":{"during_time":12161,"item":"2","item_type":"sku_id","last_page_id":"good_detail","page_id":"good_spec","sourceType":"query"},"ts":1583769287899}

③查看一条日志数据

{"common":{"ar":"230000","ba":"Xiaomi","ch":"web","md":"Xiaomi 10 Pro ","mid":"mid_630","os":"Android 11.0","uid":"211","vc":"v2.1.134"},"start":{"entry":"icon","loading_time":7620,"open_ad_id":17,"open_ad_ms":5716,"open_ad_skip_ms":1457},"ts":1602683291000}

5.1.1 get_json_object函数使用

① 作用：从一个JSON字符串中取出指定路径对应的数据！

执行 desc function extended get_json_object ; 语句

② 执行完日志：

get_json_object(json_txt, path) - Extract a json object from path 
Extract json object from a json string based on json path specified, and return json string of the extracted json object. It will return null if the input json string is invalid.
A limited version of JSONPath supported:
  $   : Root object
  .   : Child operator
  []  : Subscript operator for array
  *   : Wildcard for []
Syntax not supported that's worth noticing:
  ''  : Zero length string as key
  ..  : Recursive descent
  &amp;#064;   : Current object/element
  ()  : Script expression
  ?() : Filter (script) expression.
  [,] : Union operator
  [start:end:step] : array slice operator

Function class:org.apache.hadoop.hive.ql.udf.UDFJson
Function type:BUILTIN

③ 怎么用：

get_json_object(json_txt, path)： 从一个JSON字符串中取出指定路径对应的数据！
核心：path怎么写？

$:  代表根对象
. : 获取子元素的操作符
[] : 获取一个数组中子元素的操作符

④ 举例：

select get_json_object('[{"name":"大郎","sex":"男","age":"25"},{"name":"西门庆","sex":"男","age":"47"}]',
                       '$[0].name');

select get_json_object('{"name":"大郎","sex":"男","age":"25"}',
                       '$.age');

select get_json_object('{"name":"大郎","sex":"男","age":"25","wife":[{"name":"小潘"}]}',
                       '$.wife[0].name');

5.1.2 启动日志表 ——dwd_start_log

5.1.2.1 创建表

注意：启动日志有哪些，建表时就应该有哪些字段

执行建表语句文件，将dwd蹭到表全部建立出来

[atguigu@hadoop102 sqls]$ hive -f dwd_tables.sql

启动hive如果只是提交元数据的操作就不需要提交sparkjob，只有提交count，或者增删改查操作才要提交sparkjob

5.1.2.2 提交数据

① 先执行如下语句

改变输入格式，让他在压缩时不会将索引文件也压缩进去

SET hive.input.format=org.apache.hadoop.hive.ql.io.HiveInputFormat;

②将启动日志过滤出来

SELECT *
from ods_log 
where dt='2020-10-10'
and get_json_object(line,'$.start') is not null

③查看dwd_start_log启动日志需要什么字段

SELECT 
area_code, brand, channel, model, mid_id, os, user_id, version_code, entry, loading_time, open_ad_id, open_ad_ms, open_ad_skip_ms, ts, dt
FROM gmall.dwd_start_log;

④按字段顺序查询出来

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-10-10'
and get_json_object(line,'$.start') is not null

⑤将查询出的数据导入

insert overwrite table dwd_start_log partition(dt='2020-10-14')

⑥概括

SET hive.input.format=org.apache.hadoop.hive.ql.io.HiveInputFormat;

-- 只要是分区表，都是overwrite 
insert overwrite table dwd_start_log partition(dt='2020-10-10')
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-10-10'
and get_json_object(line,'$.start') is not null

5.1.2.3 错误解决

dbeave连接时无法插入数据，没写权限

解决一：

hadoopo fs -chmod -R 777 /

解决二：dbeave指定用户为atguigu

5.1.2.4 总结

1.创建dwd层的所有表：
	[atguigu@hadoop102 sqls]$ hive -f dwd_tables.sql 
2.将ods层的日志表中筛选出启动日志
	执行脚本：dwd_start_log.sh 日期

5.1.3 页面日志表——dwd_page_log

insert overwrite table dwd_page_log partition(dt='2020-10-10')
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-10-10'
and get_json_object(line,'$.page') is not null

5.1.4 动作日志表——dwd_action_log

5.1.4.1 建表

hive (gmall)> 
drop table if exists dwd_action_log;
CREATE EXTERNAL TABLE dwd_action_log(
    `area_code` string COMMENT '地区编码',
    `brand` string COMMENT '手机品牌', 
    `channel` string COMMENT '渠道', 
    `model` string COMMENT '手机型号', 
    `mid_id` string COMMENT '设备id', 
    `os` string COMMENT '操作系统', 
    `user_id` string COMMENT '会员id', 
    `version_code` string COMMENT 'app版本号', 
    `during_time` bigint COMMENT '持续时间毫秒', 
    `page_item` string COMMENT '目标id ', 
    `page_item_type` string COMMENT '目标类型', 
    `last_page_id` string COMMENT '上页类型', 
    `page_id` string COMMENT '页面ID ',
    `source_type` string COMMENT '来源类型', 
    `action_id` string COMMENT '动作id',
    `item` string COMMENT '目标id ',
    `item_type` string COMMENT '目标类型', 
    `ts` bigint COMMENT '时间'
) COMMENT '动作日志表'
PARTITIONED BY (dt string)
stored as parquet
LOCATION '/warehouse/gmall/dwd/dwd_action_log'
TBLPROPERTIES('parquet.compression'='lzo');

5.1.4.2 创建UDTF函数----设计思路

方式：

①继承GenericUDTF

②实现initaize，process，可选实现close（）

initaize:

有hive自己调用，告诉UDTF函数传入的参数类型和返回的结果！返回object inspector，函数生成一行数据的类型是什么。就返回这个类型对应的 object inspector! 进行参数类型检查！

process

Hive将输入的行传递给函数，由函数在process中进行计算，生成UDTF返回的每行结果！

forward

将结果传递给其它的运算符运算（输出）！

最后会调用close（）

5.1.4.3 创建UDTF函数----编写代码

①创建一个maven工程：hiveFunction

②创建包名：com.atguigu.hive.udtf

③引入如下依赖

<dependencies>
    <!--添加hive依赖-->
    <dependency>
        <groupId>org.apache.hive</groupId>
        <artifactId>hive-exec</artifactId>
        <version>3.1.2</version>
    </dependency>
</dependencies>

④编码

package com.atguigu.hivefunctions;

import org.apache.hadoop.hive.ql.exec.UDFArgumentException;
import org.apache.hadoop.hive.ql.metadata.HiveException;
import org.apache.hadoop.hive.ql.udf.generic.GenericUDTF;
import org.apache.hadoop.hive.serde2.objectinspector.ObjectInspector;
import org.apache.hadoop.hive.serde2.objectinspector.ObjectInspectorFactory;
import org.apache.hadoop.hive.serde2.objectinspector.StructField;
import org.apache.hadoop.hive.serde2.objectinspector.StructObjectInspector;
import org.apache.hadoop.hive.serde2.objectinspector.primitive.PrimitiveObjectInspectorFactory;
import org.json.JSONArray;

import java.util.ArrayList;
import java.util.List;

/**
 * Created by VULCAN on 2020/10/20
 */
public class MyUDTF extends GenericUDTF {

    private String[] result=new String[1];

    /*
            告诉Hive当前函数所需要的输入的参数类型和输出的参数类型

                函数 ： a

                使用：  select a(jsonArrayStr) from xxx

     */
    @Override
    public StructObjectInspector initialize(StructObjectInspector argOIs) throws UDFArgumentException {

        //先获取当前函数传入的参数的结构
        List<? extends StructField> inputFields = argOIs.getAllStructFieldRefs();

        // 判断当前函数传入的参数复合要求的前提是，当前只传入了一个字段，且是string类型
        if (inputFields.size() != 1){
            throw new UDFArgumentException("只允许传入一列String类型的参数！");
        }

        if (!"string".equals(inputFields.get(0).getFieldObjectInspector().getTypeName())){
            throw new UDFArgumentException("当前此列不是String类型的参数！");
        }

        //告诉Hive 函数返回的每一行的字段名
        List<String> fieldNames=new ArrayList<>();
        fieldNames.add("jsonstr");
        //告诉Hive 函数返回的每一行的字段的类型
        List<ObjectInspector> fieldOIs=new ArrayList<>();
        fieldOIs.add(PrimitiveObjectInspectorFactory.javaStringObjectInspector);

        return ObjectInspectorFactory.getStandardStructObjectInspector(fieldNames,
                fieldOIs);
    }

    /*
            将输入的数据，进行处理，生成要返回的数据

            Object[] args：  [{a},{b},{c}]
     */
    @Override
    public void process(Object[] args) throws HiveException {

        JSONArray jsonArray = new JSONArray(args[0].toString());

        for (int i=0; i < jsonArray.length(); i++){
            String jsonObjectStr = jsonArray.getString(i);
            result[0] = jsonObjectStr;
            //写出的是 1行1列
            forward(result);
        }

    }

    @Override
    public void close() throws HiveException {

    }
}

5.1.4.4 创建函数

①打包

②在/opt/module/hive/创建一个auxlib目录

mkdir auxlib

③用XFtp将jar包上传到/opt/module/hive/auxlib目下

④注意：如果修改了自定义函数重新生成jar包怎么处理？只需要替换HDFS路径上的旧jar包，然后重启Hive客户端即可。

⑤创建永久函数

注意：函数是有库的范围，自定义的函数，在哪个库定义，只能在哪个库用！

或使用库名.函数名

use gmall;

create function 函数名 as '函数全类名';

create function explode_array as 'com.atguigu.hivefunctions.MyUDTF';

[{"name":"大郎","sex":"男","age":"25"},{"name":"西门庆","sex":"男","age":"47"}]

5.1.4.5 数据导入

SET hive.input.format=org.apache.hadoop.hive.ql.io.HiveInputFormat;

insert overwrite table dwd_action_log partition(dt='2020-10-10')
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(actionStr,'$.action_id'),
    get_json_object(actionStr,'$.item'),
    get_json_object(actionStr,'$.item_type'),
    get_json_object(actionStr,'$.ts')
from ods_log 
lateral view explode_array(get_json_object(line,'$.actions')) tmp as actionStr
where dt='2020-10-10'
and get_json_object(line,'$.actions') is not null;

5.1.5 曝光日志表——dwd_display_log

5.1.5.1 创建表

CREATE EXTERNAL TABLE dwd_display_log(
    -- common
    `area_code` string COMMENT '地区编码',
    `brand` string COMMENT '手机品牌', 
    `channel` string COMMENT '渠道', 
    `model` string COMMENT '手机型号', 
    `mid_id` string COMMENT '设备id', 
    `os` string COMMENT '操作系统', 
    `user_id` string COMMENT '会员id', 
    `version_code` string COMMENT 'app版本号', 
    --page
    `during_time` bigint COMMENT 'app版本号',
    `page_item` string COMMENT '目标id ', 
    `page_item_type` string COMMENT '目标类型', 
    `last_page_id` string COMMENT '上页类型', 
    `page_id` string COMMENT '页面ID ',
    `source_type` string COMMENT '来源类型', 
    --ts
    `ts` bigint COMMENT 'app版本号',
    --dispalys
    `display_type` string COMMENT '曝光类型',
    `item` string COMMENT '曝光对象id ',
    `item_type` string COMMENT 'app版本号', 
    `order` bigint COMMENT '出现顺序'
) COMMENT '曝光日志表'
PARTITIONED BY (dt string)
stored as parquet
LOCATION '/warehouse/gmall/dwd/dwd_display_log'
TBLPROPERTIES('parquet.compression'='lzo');

5.1.5.2 导入数据

SET hive.input.format=org.apache.hadoop.hive.ql.io.HiveInputFormat;

insert overwrite table dwd_display_log partition(dt='2020-10-10')
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(displayStr,'$.displayType'),
    get_json_object(displayStr,'$.item'),
    get_json_object(displayStr,'$.item_type'),
    get_json_object(displayStr,'$.order')
from ods_log 
lateral view explode_array(get_json_object(line,'$.displays')) tmp as displayStr
where dt='2020-10-10'
and get_json_object(line,'$.displays') is not null;

5.1.6 错误日志表——dwd_error_log

5.1.6.1 创建表

CREATE EXTERNAL TABLE dwd_error_log(
    --common
    `area_code` string COMMENT '地区编码',
    `brand` string COMMENT '手机品牌', 
    `channel` string COMMENT '渠道', 
    `model` string COMMENT '手机型号', 
    `mid_id` string COMMENT '设备id', 
    `os` string COMMENT '操作系统', 
    `user_id` string COMMENT '会员id', 
    `version_code` string COMMENT 'app版本号', 
    
    --page取，少during_time
    `page_item` string COMMENT '目标id ', 
    `page_item_type` string COMMENT '目标类型', 
    `last_page_id` string COMMENT '上页类型', 
    `page_id` string COMMENT '页面ID ',
    `source_type` string COMMENT '来源类型', 
    --start取
    `entry` string COMMENT ' icon手机图标  notice 通知   install 安装后启动',
    `loading_time` string COMMENT '启动加载时间',
    `open_ad_id` string COMMENT '广告页ID ',
    `open_ad_ms` string COMMENT '广告总共播放时间', 
    `open_ad_skip_ms` string COMMENT '用户跳过广告时点',
    --actions
    `actions` string COMMENT '动作',
    --displays
    `displays` string COMMENT '曝光',
    --ts
    `ts` string COMMENT '时间',
    --err
    `error_code` string COMMENT '错误码',
    `msg` string COMMENT '错误信息'
) COMMENT '错误日志表'
PARTITIONED BY (dt string)
stored as parquet
LOCATION '/warehouse/gmall/dwd/dwd_error_log'
TBLPROPERTIES('parquet.compression'='lzo');

5.1.6.2 导入数据

insert overwrite table dwd_error_log partition(dt='2020-10-10')
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'),
    get_json_object(line,'$.actions'),
    get_json_object(line,'$.displays'),
    get_json_object(line,'$.ts'),
    get_json_object(line,'$.err.error_code'),
    get_json_object(line,'$.err.msg')
from ods_log 
where dt='2020-10-10'
and get_json_object(line,'$.err') is not null;

5.1.7 DWD层用户行为数据加载脚本 dwd_start_log.sh脚本

# !bin/base
#接受外部传入的日期
# -n 是一个逻辑运算符，用于判断后面的字符串长度是否为0，为0返回false，否则返回true
if [ -n "$1" ]
then
	do_date=$1
else
#默认使用当前日期的前一天
	do_date=$(date -d 'yesterday' '+%F')
fi

#echo $do_date

#声明hql
hql="
use gmall;
#启动日志
insert overwrite table 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 ods_log 
where dt='$do_date'
and get_json_object(line,'$.start') is not null;
#页面日志
insert overwrite table 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 ods_log 
where dt='$do_date'
and get_json_object(line,'$.page') is not null;
#动作日志
insert overwrite table 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(actionStr,'$.action_id'),
    get_json_object(actionStr,'$.item'),
    get_json_object(actionStr,'$.item_type'),
    get_json_object(actionStr,'$.ts')
from ods_log 
lateral view explode_array(get_json_object(line,'$.actions')) tmp as actionStr
where dt='$do_date'
and get_json_object(line,'$.actions') is not null;
#曝光日志
insert overwrite table 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(displayStr,'$.displayType'),
    get_json_object(displayStr,'$.item'),
    get_json_object(displayStr,'$.item_type'),
    get_json_object(displayStr,'$.order')
from ods_log 
lateral view explode_array(get_json_object(line,'$.displays')) tmp as displayStr
where dt='$do_date'
and get_json_object(line,'$.displays') is not null;
#错误日志
insert overwrite table 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'),
    get_json_object(line,'$.actions'),
    get_json_object(line,'$.displays'),
    get_json_object(line,'$.ts'),
    get_json_object(line,'$.err.error_code'),
    get_json_object(line,'$.err.msg')
from ods_log 
where dt='$do_date'
and get_json_object(line,'$.err') is not null;
$do_date
"
hive -e "$hql"

5.2 业务数据

【维度表】->全量导入

5.2.1 商品维度表（全量表）——dwd_dim_sku_info

5.2.1.1 创建表

DROP TABLE IF EXISTS `dwd_dim_sku_info`;
CREATE EXTERNAL TABLE `dwd_dim_sku_info` (
    --ods_sku_info   --SKU商品表
    `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',
    --ods_base_trademark   
    `tm_name` string COMMENT '品牌名称',
    
    -- ods_base_category1，ods_base_category2，ods_base_category3
    `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 '一级分类名称',
    -- ods_spu_info
    `spu_name` string COMMENT 'spu名称',
    -- --ods_sku_info
    `create_time` string COMMENT '创建时间'
) COMMENT '商品维度表'
PARTITIONED BY (`dt` string)
stored as parquet
location '/warehouse/gmall/dwd/dwd_dim_sku_info/'
tblproperties ("parquet.compression"="lzo");

如何Join?

取ods_sku_info的全部数据，因此将ods_sku_info作为左表，left join其他表！

ods_xxx 是每日全量同步！

导入数据时，应该获取昨天同步的最新的维度表的数据，以此数据进行Join，导入dwd层！

5.2.1.2 导入数据

insert overwrite table dwd_dim_sku_info  partition(dt='2020-10-10')
SELECT 
    t1.id, spu_id, price, sku_name, sku_desc, weight, t1.tm_id,
    tm_name, 
    category3_id, category2_id, category1_id, 
    category3_name, category2_name, category1_name,
    spu_name, 
    create_time
from 
(SELECT 
    id, spu_id, price, sku_name, sku_desc, weight, tm_id, 
    create_time ,category3_id
from ods_sku_info  			--sku商品表
where dt='2020-10-10') t1
left join 
(select 
    tm_id,tm_name
from ods_base_trademark  where dt='2020-10-10') t2       --品牌表
on t1.tm_id=t2.tm_id
left join
(select
    id,name category3_name,category2_id
from ods_base_category3 where dt='2020-10-10') t3
on t1.category3_id=t3.id
left join
(select
    id,name category2_name,category1_id
from ods_base_category2 where dt='2020-10-10') t4
on t3.category2_id = t4.id
left join
(select
    id,name category1_name
from ods_base_category1 where dt='2020-10-10') t5
on t4.category1_id = t5.id
left join
(select
    id,spu_name
from ods_spu_info where dt='2020-10-10') t6   --spu商品表
on t6.id = t1.spu_id

5.2.2 优惠券维度表（全量）——dwd_dim_coupon_info

5.2.2.1 创建表

drop table if exists dwd_dim_coupon_info;
create external table dwd_dim_coupon_info(
    --从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)
stored as parquet
location '/warehouse/gmall/dwd/dwd_dim_coupon_info/'
tblproperties ("parquet.compression"="lzo");

5.2.1.2 导入数据

INSERT overwrite table dwd_dim_coupon_info partition(dt='2020-10-10')
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-10-10'

5.2.3 活动维度表（全量）——dwd_dim_activity_info

5.2.3.1 创建表

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 '活动类型',
    `start_time` string  COMMENT '开始时间',
    `end_time` string  COMMENT '结束时间',
    `create_time` string  COMMENT '创建时间'
) COMMENT '活动信息表'
PARTITIONED BY (`dt` string)
stored as parquet
location '/warehouse/gmall/dwd/dwd_dim_activity_info/'
tblproperties ("parquet.compression"="lzo");

5.2.3.2 导入数据

INSERT overwrite table dwd_dim_activity_info partition(dt='2020-10-10')
SELECT 
    id, activity_name, activity_type, 
    start_time, end_time, create_time  
FROM ods_activity_info 
where dt='2020-10-10';

5.2.4 地区维度表（特殊）——dwd_dim_base_province

5.2.4.1 创建表

DROP TABLE IF EXISTS `dwd_dim_base_province`;
CREATE EXTERNAL TABLE `dwd_dim_base_province` (
    --ods_base_province
    `id` string COMMENT 'id',
    `province_name` string COMMENT '省市名称',
    `area_code` string COMMENT '地区编码',
    `iso_code` string COMMENT 'ISO编码',
    `region_id` string COMMENT '地区id',
    --ods_base_region
    `region_name` string COMMENT '地区名称'
) COMMENT '地区维度表'
stored as parquet
location '/warehouse/gmall/dwd/dwd_dim_base_province/'
tblproperties ("parquet.compression"="lzo");

5.2.4.2 导入数据

地区和省份join而来

注意：别名不能和hive和shell中的关键字冲突！冲突可以使用``

INSERT overwrite table  dwd_dim_base_province
SELECT 
    op.id, name province_name, 
    area_code, iso_code, region_id, region_name
from ods_base_province op left join ods_base_region `or` 
on op.region_id = `or`.id

5.2.5 时间维度表（特殊）——dwd_fact_order_detail

5.2.5.1 创建表

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 '是否是节假日'
) COMMENT '时间维度表'
row format delimited fields terminated by '\t'
location '/warehouse/gmall/dwd/dwd_dim_date_info/';

5.2.5.2 导入数据

先将dim_date_info.txt的数据导入家linux的目录，在hive客户端执行以下语句

load data local inpath '/home/atguigu/date_info.txt' overwrite into table dwd_dim_date_info;

【事实表】

【事务型事实表】-> 导入新增和变化

5.2.6 订单明细事实表 ——dwd_fact_order_detail

【事务型事实表只会新增，不会修改，这类表从mysql到hdfs再到ods层时是只导入每日新增的同步上去的】

① ods_order_info：订单表

	"没有这笔订单包含的商品信息
		粒度： 一笔订单是一条，一条数据记录的是一笔订单的信息"
	--------------------------------------------------------------------------------
	id(订单号)：      original_total_amount    benefit_reduce_amount   feight_fee  final_total_amount
	1001       xxxx	   1010						50						10		    970

	final_total_amount :  最终支付金额
	benefit_reduce_amount： 优惠金额
	original_total_amount：  优惠前原始金额
	feight_fee： 运费
	
	最终支付金额 = 优惠前原始金额 - 优惠金额 + 运费
	final_total_amount=original_total_amount - benefit_reduce_amount + feight_fee

② ods_order_detail : 订单详情表

	"侧重点，描述一笔订单的商品信息(单价，数量，描述)
		粒度：  一笔订单中的一个商品"
	--------------------------------------------------------------------------------
	order_id(订单号)		sku_id    sku_num  order_price    final_amount_d     sum_final_amount_d  final_total_amount  最终final_amount_d  rownumber_num
	1001               		1          10       100            960.40			970							970									1
	1001                    2          1        10			   9.60				970																2
	
	1002               		1          10       100            960.40			970.01						970			 960.39					1
	1002                    2          1        10			   9.61				970	.01						970			 9.61 					2

③ dwd_fact_order_detail: 订单详情事实表

根据排名，第一享受多退少补，不是第一还是原价。
------------------------------------------------------------------------------

dwd_fact_order_detail:  订单详情事实表    从ods_order_detail来
	
	--  从ods_order_info取数据计算：
		`original_amount_d` decimal(20,2) COMMENT '原始价格分摊',
			sku_num * order_price  
    	
    -- '比例'= 当前商品的总价 /  当前订单的优惠前的总价
    	
    	`final_amount_d` decimal(20,2) COMMENT '购买价格分摊',
			购买价格： 指的是 包含运费的，还是不包含运费的 这里我们算包含运费的	
		round(sku_num * order_price / original_total_amount	* (original_total_amount - benefit_reduce_amount + feight_fee),2) 
	
    	`feight_fee_d` decimal(20,2) COMMENT '分摊运费',
		round(sku_num * order_price / original_total_amount	* feight_fee,2)
			
    	`benefit_reduce_amount_d` decimal(20,2) COMMENT '分摊优惠'
		round(sku_num * order_price / original_total_amount * benefit_reduce_amount,2)
	
	
'运费怎么分摊？  价值比例分摊！'

------------------------------------------
9056	3872		7356.00	7364.00	8.00	0.00

5.2.6.1 创建表

create external table dwd_fact_order_detail (
    -- 从ods_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 '创建时间',
    -- ods_order_info取
    `province_id` string COMMENT '省份ID',
     -- 从ods_order_detail取
    `source_type` string COMMENT '来源类型',
    `source_id` string COMMENT '来源编号',
    
    ---- ods_order_info取
    --final_total_amount ,benefit_reduce_amount，original_total_amount，feight_fee
    `original_amount_d` decimal(20,2) COMMENT '原始价格分摊',   --商品的单价x数量
    --最终给的钱【包含运费还是不包含运费？我们这包含运费】按比例分摊 【比例：每件商品的价格/订单的总价格】
    `final_amount_d` decimal(20,2) COMMENT '购买价格分摊',	
    `feight_fee_d` decimal(20,2) COMMENT '分摊运费',
    `benefit_reduce_amount_d` decimal(20,2) COMMENT '分摊优惠'
) COMMENT '订单明细事实表表'
PARTITIONED BY (`dt` string)
stored as parquet
location '/warehouse/gmall/dwd/dwd_fact_order_detail/'
tblproperties ("parquet.compression"="lzo");

如果出现四舍五入后，所遇商品分摊的价格比总价多或少的情况，由当前订单总额最多的商品承担！

orderId	商品ID	购买价格分摊	总额
1	1	20.34	61
1	2	20.33
1	3	20.33
2	4

5.2.6.2 导入数据注意理解思想

insert overwrite table dwd_fact_order_detail partition(dt='2020-10-10')
SELECT
-- 从ods_order_detail取
    id, order_id, user_id, sku_id, sku_name, order_price, sku_num, create_time, 

-- ods_order_info取
    province_id, 
    
-- 从ods_order_detail取    
    source_type, source_id,
  
 -- ods_order_info取
    --原始价格分摊 original_amount_d
    original_amount_d, 
    
    --有if是因为有冤大头
    --购买价格分摊  final_amount_d
    if(rn=1,final_total_amount - sum_final_amount_d + final_amount_d ,final_amount_d ) final_amount_d, 
     --运费分摊  feight_fee_d
    if(rn=1,feight_fee - sum_feight_fee_d + feight_fee_d ,feight_fee_d ) feight_fee_d, 
    --分摊优惠   benefit_reduce_amount_d
    if(rn=1,benefit_reduce_amount - sum_benefit_reduce_amount_d + benefit_reduce_amount_d ,benefit_reduce_amount_d ) 		                 benefit_reduce_amount_d
from 
(SELECT
-- 从ods_order_detail left join ods_order_info取
 	--订单商品的所有信息，省份信息
    t1.*,
 	province_id,

    --原始价格分摊 original_amount_d  商品数量（数量）*商品价格（单价） 【订单中买的某一种商品（可以有很多件）优惠前总价】
 	sku_num * order_price original_amount_d,  
 	
--比例 = 原始价格（商品价格） / 原价金额（订单总价） 或者   
 '【订单中买的某一种商品（可以有很多件）优惠前总价】 / 该订单的总价格'
 
 	--购买价格分摊  final_amount_d   比例 *购买价格   【订单中买的某一种商品（可以有很多件）优惠后这一种商品的总价】
 --2是四舍五入取几位
    round(sku_num * order_price / original_total_amount * (original_total_amount - benefit_reduce_amount + feight_fee),2) final_amount_d,
 	--运费分摊  feight_fee_d   比例*运费  某种商品的运费分摊
 	round(sku_num * order_price / original_total_amount * feight_fee,2) feight_fee_d,
    --分摊优惠   benefit_reduce_amount_d  比例*优惠金额   某种商品的优惠分摊
 	round(sku_num * order_price / original_total_amount * benefit_reduce_amount,2) benefit_reduce_amount_d,
    --上面是订单中一类商品的每件商品
 	--下面三个sum就是订单中的一类商品，可见它是同一个order id开了一个窗
 	sum(round(sku_num * order_price / original_total_amount * (original_total_amount - benefit_reduce_amount + feight_fee),2)) 						over(PARTITION  by order_id ) sum_final_amount_d,
    sum(round(sku_num * order_price / original_total_amount * feight_fee,2))
 			over(PARTITION  by order_id ) sum_feight_fee_d,
 	sum(round(sku_num * order_price / original_total_amount * benefit_reduce_amount,2)) 
 			over(PARTITION  by order_id ) 
 	sum_benefit_reduce_amount_d,
 
    ROW_NUMBER() over(PARTITION  by order_id order by sku_num * order_price desc)  rn  ,
    
 --总价
 	final_total_amount ,benefit_reduce_amount,original_total_amount,feight_fee
from
(SELECT 
     id, order_id, user_id, sku_id, sku_name, order_price, sku_num, create_time,
     source_type, source_id
from ods_order_detail where dt='2020-10-10') t1   --从订单详情表找订单的商品相关信息
left join 
(select
    id,province_id,
    final_total_amount ,benefit_reduce_amount,original_total_amount,feight_fee   --从订单表找订单的最终金额，订单的优惠金额，运费信息
from ods_order_info where dt='2020-10-10') t2
on t1.order_id=t2.id) t3

订单详情表的order_id订单号就是订单表的id

问？

一个订单表可以对应多个商品

一个商品也可以对应多个订单

那么订单表跟商品表这样的多对多关系怎么连接呢？

解决：

我们引用一个中间表，订单详情表

订单详情表的id是唯一标识

order_id与订单表的id相同

订单表和订单详情是一对多的关系，一个订单可以有多个订单详情

订单详情表与商品表是多对一的关系，一个商品表可以对应多个订单详情表

5.2.7 支付事实表——dwd_fact_payment_info

5.2.7.1 创建表

drop table if exists dwd_fact_payment_info;
create external table dwd_fact_payment_info (
    `id` string COMMENT 'id',
    `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'
) COMMENT '支付事实表表'
PARTITIONED BY (`dt` string)
stored as parquet
location '/warehouse/gmall/dwd/dwd_fact_payment_info/'
tblproperties ("parquet.compression"="lzo");

粒度：一条订单的支付信息

举例：

2020-10-10 23:59:59 用户下单

mysql的order_info表中生成一条记录，creat_time = 2020-10-10 23:59:59

将10-10的数据同步到ods层之后，在ods_order_info/2020-10-10分区中！

如果此笔订单10-11日发货，状态会被修改，有可能

也出现在10-11日的分区中！

———————————————————————————————————

2020-10-11 00:10:00 用户付款

mysql的payment_info表中生成一条记录，creat-time = 2020-10-11 00:10:00

将2020-10-11的数据同步到ods层时，此时在ods_payment_info / 2020-11-11 分区中有此订单支付记录！

总结：遇到咵天支付，在求当前日期的支付订单详情信息时，如果需要关联订单表，需要关联当天及前一天的下单信息！

5.2.7.2 导入数据

insert overwrite table dwd_fact_payment_info partition(dt='2020-10-10')
SELECT
    t1.id, out_trade_no, order_id, user_id, alipay_trade_no, 
    total_amount payment_amount, 
subject, payment_type, payment_time,
province_id
from
(SELECT 
    *
from ods_payment_info where dt='2020-10-10') t1
left join 
(select
    DISTINCT id,province_id    --distinct去重
from ods_order_info  where dt='2020-10-10') t2  --date_sub(参数1，参数2) 【参数1为日期，参数2要减去的天数】
on t1.order_id = t2.id

5.2.8 退款事实表——dwd_fact_order_refund_info

5.2.8.1 创建表

drop table if exists dwd_fact_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)
stored as parquet
location '/warehouse/gmall/dwd/dwd_fact_order_refund_info/'
tblproperties ("parquet.compression"="lzo");

5.2.8.2 导入数据

insert overwrite table dwd_fact_order_refund_info partition(dt='2020-10-10')
select
    id,
    user_id,
    order_id,
    sku_id,
    refund_type,
    refund_num,
    refund_amount,
    refund_reason_type,
    create_time
from ods_order_refund_info
where dt='2020-10-10';

5.2.9 评价事实表——dwd_fact_comment_info

5.2.9.1 创建表

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)
stored as parquet
location '/warehouse/gmall/dwd/dwd_fact_comment_info/'
tblproperties ("parquet.compression"="lzo");

5.2.9.2 导入数据

insert overwrite table dwd_fact_comment_info partition(dt='2020-10-10')
select
    id,
    user_id,
    sku_id,
    spu_id,
    order_id,
    appraise,
    create_time
from ods_comment_info
where dt='2020-10-10;

—>事务型事实表总结

1.事务型事实表记录只会新增，不会修改！

2.从Mysql同步的数据，都是增量同步！导入到ODS层！

3.导入DWD层，只需要从ODS层，取出当天导入的新增的数据即可

【周期型快照事实表】->全量导入

注意事实表一般不全量导入，这里比较特殊

5.2.10 加购事实表——dwd_fact_cart_info

事实：统计用户将商品加入购物车的事实！

周期：每天

5.2.10.1 创建表

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)
stored as parquet
location '/warehouse/gmall/dwd/dwd_fact_cart_info/'
tblproperties ("parquet.compression"="lzo");

关心：截止到某个日期结束，例如截止2020-10-10，此时用户的购物车（还有什么）状态！

ODS层是全量同步：将购物车，从使用APP起，到同步日期那天，所有用户购物车的状态全部同步

如果数据量大： ODS应该同步新增和变化的数据！

2020-10-10同步，同步的是2020-10-10新加入购物车的记录和对购物车的数据进行修改的记录！

数仓一般数据保留半年，为了减少冗余，可以缩短保留周期，根本解决办法，可以不按数据的同步时间分区，而是按照加入购物车的create_time进行分区

DWD：截止到2020-10-10.所有用户购物车状态！

需要先取2020-10-09之前dwd所有购物车的状态

和

ODS层 2020-10-10新增和变化的数据，进行合并，取时间最新的！

5.2.10.2 导入数据

insert overwrite table dwd_fact_cart_info partition(dt='2020-10-10')
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-10-10';

5.2.11 收藏事实表——dwd_fact_favor_info

5.2.11.1 创建表

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)
stored as parquet
location '/warehouse/gmall/dwd/dwd_fact_favor_info/'
tblproperties ("parquet.compression"="lzo");

5.2.11.2 导入数据

SET hive.input.format=org.apache.hadoop.hive.ql.io.HiveInputFormat;
insert overwrite table dwd_fact_favor_info partition(dt='2020-06-14')
select
    id,
    user_id,
    sku_id,
    spu_id,
    is_cancel,
    create_time,
    cancel_time
from ods_favor_info
where dt='2020-10-10';

—>周期型快照事实表总结

数据流向  搞清楚 （善于画图）！

掌握场景的业务处理能力（函数，自定义函数）！

总结：   表的同步方式！
		建模时，如何建！
		如何基于建模优化同步方式！

【累积型快照事实表】

累积型快照事实表通常也会使用分区表，但是分区字段会使用事实生命周期中最早的时间！

事实发生的时间，作为分区字段！

5.2.12 优惠券领用事实表——dwd_fact_coupon_use

5.2.12.1 创建表

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)
stored as parquet
location '/warehouse/gmall/dwd/dwd_fact_coupon_use/'
tblproperties ("parquet.compression"="lzo");

需要更新数据

如何更新

先求出被修改的两个分区数据有哪些为old数据

修改后为new数据

这两个表做full join 则没修改的在new的时候为null

修改了的就是修改后的数据

再做一个判断 

if (new.id is null ,old.id,new.id)

合并后的表就是更新后的表

期望数据：

优惠券ID	用户ID	领取时间	下单时间	支付时间
1001	1	2020-10-10:08:08:08	2020-10-10:08:08:09	2020-10-10:08:08:10
1002	2	2020-10-11:08:08:08

需要更新数据！

Hive依赖于HDFS存储，HDFS不支持随机写，Hive不支持update语句！

如何间接实现update:

5.2.12.2 导入过程表格描述

第一次导入的数据：2020-08-08 ods_coupon_use / 2020-08-08

优惠券ID	用户ID	领取时间	下单使用时间	支付使用时间	dt
1	1	2020-08-08	null	null	2020-08-08
2	1	2020-08-08	null	null	2020-08-08

第二次导入数据：2020-08-09 ods_coupon_use / 2020-08-09

优惠券ID	用户ID	领取时间	下单使用时间	支付使用时间	dt
1	1	2020-08-08	2020-08-09	2020-08-09	2020-08-08
3	1	2020-08-09	null	null	2020-08-09

使用 insert overwrite table xxx partition(dt=’“2020-08-08”’),插入以下数据：

优惠券ID	用户ID	领取时间	下单使用时间	支付使用时间	dt
1	1	2020-08-08	2020-08-09	2020-08-09	2020-08-08
2	1	2020-08-08	null	null	2020-08-08

使用 insert overwrite table xxx partition(dt=’“2020-08-09”’),插入以下数据：

优惠券ID	用户ID	领取时间	下单使用时间	支付使用时间	dt
3	1	2020-08-09	null	null	2020-08-09

总结：累积的分区的表，操作步骤：

①查询出，2020-08-09导入的数据中，哪些老分区的数据，发生了变化(2020-08-08)

②将发生变化的分区中的老的数据查询出来

优惠券ID	用户ID	领取时间	下单使用时间	支付使用时间	dt
1	1	2020-08-08	null	null	2020-08-08
2	1	2020-08-08	null	null	2020-08-08

③和今天新导入的数据进行混合

优惠券ID	用户ID	领取时间	下单使用时间	支付使用时间	dt
1	1	2020-08-08	2020-08-09	2020-08-09	2020-08-08
3	1	2020-08-09	null	null	2020-08-09

混合后：

优惠券ID	用户ID	领取时间	下单使用时间	支付使用时间	dt
1	1	2020-08-08	2020-08-09	2020-08-09	2020-08-08	旧数据和新数据的交集
2	1	2020-08-08	null	null	2020-08-08	旧的，今天没有改变的数据
3	1	2020-08-09	null	null	2020-08-09	今天新导入的数据

④将混合的数据，插入到对应的分区

总结：
--查询老数据中那些分区的数据发生了变化
--根据变化的分区日期，找出老的分区的数据
--查询当天新的数据
--old full join new 
		--新老交替
--将合并后的结果写入分区

5.2…12.3 导入过程文字叙述理解解题思想

--求今天新导入的数据中，发生变化的数据的分区有哪些
select data_format(get_time,'yyy-MM-dd') from ods_coupon_use where dt = '2020-10-10' [t1]
--查询发生变化的老的分区的数据old
select * from dwd_format_use where dt in t1 [old]
--再求当天新增的数据
select * from ods_coupon_use where dt = '2020-10-10' new 
--满外连接后新老分区
select
	if(new.id is not null,new.id,old.id) 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
	old full outer join new	on new.id=old.id

问：什么时候用静态分区，什么时候用动态分区？

答：

5.2.12.4 导入数据

--什么时候静态（分区字段值是写死的）？什么时候动态(参考select最后的字段的值，根据值将数据插入到对应的分区)？  
set hive.exec.dynamic.partition.mode=nonstrict;
 
insert overwrite table dwd_fact_coupon_use  partition(dt)
SELECT
--将发现变化的老的数据的字段进行更新 新老交替
    if(new.id is not null,new.id,old.id) id, 
    if(new.id is not null,new.coupon_id,old.coupon_id) coupon_id, 
    if(new.id is not null,new.user_id,old.user_id) user_id, 
    if(new.id is not null,new.order_id,old.order_id) order_id, 
    if(new.id is not null,new.coupon_status,old.coupon_status) coupon_status, 
    if(new.id is not null,new.get_time,old.get_time) get_time, 
    if(new.id is not null,new.using_time,old.using_time) using_time, 
    if(new.id is not null,new.used_time,old.used_time) used_time, 
    date_format(if(new.id is not null,new.get_time,old.get_time),'yyyy-MM-dd') gouzi   --这个值作为分区的值
from
--② 查询发生变化的老的分区的数据  old
(
    SELECT 
    *
	from dwd_fact_coupon_use 
	where dt in(
			--① 求今天新导入的数据中，发生变化的老的分区有哪些
			SELECT 
  				 date_format(get_time,'yyyy-MM-dd') 
			from ods_coupon_use 
			where dt='2020-10-10' and date_format(get_time,'yyyy-MM-dd') < '2020-10-10' )
	 ) old
FULL  join 
--③  查询今天新增和变化的新数据  new
(
    SELECT 
    	*
	from ods_coupon_use 
	where dt='2020-10-10'
) new
on old.id = new.id ;

插入完数据，当你自己查询的时候

select * from dwd_fact_coupon_use where dt = '2020-10-10' and 
		(date_format(using_time,'yyyy-MM-dd')='2020-10-11')
		or
		(date_format(usd_time,'yyyy-MM-dd')='2020-10-11')

5.2.13 订单事实表——dwd_fact_order_info

5.2.13.1 创建表

drop table if exists dwd_fact_order_info;
create external table dwd_fact_order_info (
    --ods_order_info   【订单表】
    `id` string COMMENT '订单编号',
    `order_status` string COMMENT '订单状态',
    `user_id` string COMMENT '用户id',
    `out_trade_no` string COMMENT '支付流水号',
    
    -- 从ods_order_info 或 从ods_order_status_log取
    `create_time` string COMMENT '创建时间(未支付状态)',
    --从ods_order_status_log取   【订单状态表】
    `payment_time` string COMMENT '支付时间(已支付状态)',
    `cancel_time` string COMMENT '取消时间(已取消状态)',
    `finish_time` string COMMENT '完成时间(已完成状态)',
    `refund_time` string COMMENT '退款时间(退款中状态)',
    `refund_finish_time` string COMMENT '退款完成时间(退款完成状态)',
     --ods_order_info   【订单表】
    `province_id` string COMMENT '省份ID',
    --ods_activity_order   【活动表】
    `activity_id` string COMMENT '活动ID',
    --ods_order_info   【订单表】
    `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 '订单金额'
) COMMENT '订单事实表'
PARTITIONED BY (`dt` string) 
stored as parquet
location '/warehouse/gmall/dwd/dwd_fact_order_info/'
tblproperties ("parquet.compression"="lzo");

5.2.13.2 导入数据

SET hive.input.format=org.apache.hadoop.hive.ql.io.HiveInputFormat;
set hive.exec.dynamic.partition.mode=nonstrict;
insert overwrite table dwd_fact_order_info partition(dt)
SELECT
    if(new.id is not null,new.id,old.id) id,
    if(new.id is not null,new.order_status,old.order_status) order_status,
    if(new.id is not null,new.user_id,old.user_id) user_id,
    if(new.id is not null,new.out_trade_no,old.out_trade_no) out_trade_no,
    
    if(new.create_time is not null,new.create_time,old.create_time) create_time, 
    if(new.payment_time is not null,new.payment_time,old.payment_time) payment_time, 
    if(new.cancel_time is not null,new.cancel_time,old.cancel_time) cancel_time, 
    if(new.finish_time is not null,new.finish_time,old.finish_time) finish_time, 
    if(new.refund_time is not null,new.refund_time,old.refund_time) refund_time, 
    if(new.refund_finish_time is not null,new.refund_finish_time,old.refund_finish_time) refund_finish_time, 
    
    if(new.id is not null,new.province_id,old.province_id) province_id,
    if(new.id is not null,new.activity_id,old.activity_id) activity_id,
    if(new.id is not null,new.original_total_amount,old.original_total_amount) original_total_amount,
    if(new.id is not null,new.benefit_reduce_amount,old.benefit_reduce_amount) benefit_reduce_amount,
    if(new.id is not null,new.feight_fee,old.feight_fee) feight_fee,
    if(new.id is not null,new.final_total_amount,old.final_total_amount) final_total_amount,
    if(new.id is not null,date_format(new.ct,'yyyy-MM-dd'),old.dt)   
from 
---old表
(
    --【② 在dwd层找出该分区的所有数据】
   		SELECT 
   		*
		from dwd_fact_order_info 
		where dt in
	(
        --【① 找出ods层生成新数据的分区的日期】
    	SELECT 
    		date_format(create_time,'yyyy-MM-dd' )
		from ods_order_info 
		where dt='2020-10-10' and date_format(create_time,'yyyy-MM-dd' ) < '2020-10-10'
    )
) old

full join

---new表
(
       	SELECT
    	t3.*,
    	statusMap['1001'] create_time, 
    	statusMap['1002'] payment_time,
    	statusMap['1003'] cancel_time, 
    	statusMap['1004'] finish_time, 
    	statusMap['1005'] refund_time, 
    	statusMap['1006'] refund_finish_time, 
   		activity_id
		from
    (
        --【获取订单表的所有信息】
        SELECT 
    		id, order_status, user_id, out_trade_no, province_id,
			original_total_amount, benefit_reduce_amount, feight_fee, final_total_amount,create_time ct
		from ods_order_info 
		where dt='2020-10-10'
    ) t3
	left join
	(
        --【订单状态表的订单id和订单状态集合---此处为重点】
        SELECT 
   			order_id,str_to_map(concat_ws('#',collect_list(concat(order_status,':',operate_time))),'#',':') statusMap 
		from ods_order_status_log --粒度是 一笔订单的一个状态是一行  将粒度由细粒度 聚合 得到粗粒度(1笔订单的所有状态信息是一条)                   
		where dt='2020-10-10'
		GROUP  by order_id
    ) t2
	on t3.id=t2.order_id
	left join
	(
        --【获取活动表的活动id和优惠券id】
        SELECT 
    		activity_id,order_id 
		from ods_activity_order 
		where dt='2020-10-10'
    ) t1
	on t3.id=t1.order_id
) new
--新老交替条件
on old.id=new.id

订单状态表（order_status_log）

标签	含义
id	编号
order_id	订单编号
order_status	订单状态
operate_time	操作时间

扩展系统函数

concat函数

多个字符串进行拼接！注意事项：传入参数中不能有NULL，一旦有一个未NULL，返回NULL

concat (str1 , str2 , ... strN)

concat ws函数

concat_ws(separator , [string | array (string)]+)

是一个聚合函数

行转列： N列多行  转N行一列 ！ 聚合函数！

collect_list(列名)

将这列数据，全部加入到List中返回，允许重复！

collect_set(列名)

将这列数据，全部加入到Set中返回，不允许重复！

str_to_map 函数

text就是一个字符串，delimiter1代表分割entry的分隔符！delimiter2代表entry中分割k-v的分割符！ 如果有同名的k-v,后面会覆盖之前的！

str_to_map(text, delimiter1 ,delimiter2)

—>累积型快照事实表

--查询老数据中那些分区的数据发生了变化

--根据变化的分区日期，找出老的分区的数据

--查询当天新的数据

--old full join new         

--新老交替--将合并后的结果写入分区

【用户维度表】

维度表一般不经常变化，用户维度表有可能发生变化，所以设为拉链表

本质来分有事实表，维度表

5.2.14 拉链表

缓慢变化的维度表适合做拉链表

5.2.14.1 简介

累积型快照事实表和拉链表：

如果对于一张事实表，希望追踪这个事实在某些阶段的变化信息，可以使用累积型快照事实表！

如果是一张维度表，希望追踪维度表中每条记录变化的状态，可以使用拉链表！

事实表和维度表：

对于事实表来说，除了事务型事实表，表中的数据在生命周期没有结束时，有可能经常发生变化。

维度表用来描述事实，维度表的特点是数据可能会增加，但是已经增加的数据，不经常变化（修改）！

拉链表：

a) 作用：用来追踪维度表中记录的变化状态的！

b) 适用场景：缓慢变化维度表！

c) 拉链表：拉：拉取数据

链：一条数据，在表中类似一个链条状！

d) 形式：分区表 or 全量表？只取决于数据

分区表主要是为了分散数据！将一个表的数据，分散到多个分区目录中！在查询时，可以根据分区字段进行过滤，过滤后，直接从某个分区目录中取数据，而不是全表扫描！

如何选取分区字段？

a) 根据查询习惯，选其他的对查询的过滤帮助不大！

b) 根据数据特征，可以选取数据的生命周期起始时间等典型的字段

维度表数据不多，可以使用全量表！

使用：在查询时，使用记录的生效日期和结束日期作为条件进行过滤！

例如： select * from xxx where start_date <= 2020-08-16 and end_date >= 2020-08-16

取出 2020-8-18的数据全量切片：

查询2020-8-16 及之前生效的，在2020-8-16日及以后失效的数据！

导入：

1.当天导入的新数据， start_date=当天日期， end_date=9999-99-99

2.没有发生变化的老数据，原封不动

3.发生变化的老数据，将end_date=当天日期-1

有个问题：

这个表是个全量表，全量表覆盖时候是全覆盖，分区表覆盖时只会覆盖指定的分区，对别的分区没有影响，覆盖的过程，是先把原表删除，再把新表复制过去，但如果这个过程突然进程崩溃了，原表数据已经丢了，所以我们要先将新表复制成一份临时表

拉链表可以看是一张累积型的全量表！

累积型： old full join new

新老交替，覆盖原表

5.2.14.2 创建拉链表

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");

创建临时表

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");

5.2.14.1 导入数据

-- 先查老的，再查新的，新老交替
-- full join(5条) union all inner join(1条)
-- 或 left join(old 3条) union all new(3条)
SET hive.input.format=org.apache.hadoop.hive.ql.io.HiveInputFormat;
-- 先查老的及发现老的发生变化的将end_date修改
INSERT overwrite table dwd_dim_user_info_his_tmp
select 
    *
from
(SELECT 
    old.id,old.name, old.birthday, old.gender, old.email, 
    old.user_level, old.create_time,
    old.operate_time, old.start_date, 
    if(new.id is null,old.end_date ,date_sub(new.dt,1)) end_date  
 --left join传过来一个时间，我把老的时间改了，
 --那么怎样知道是李四这个表的时间改了，只要判断右表为不为空，不为空需要改，为空不需要改，new.dt 就是新表的creat_time，就是旧表的结束时间
from dwd_dim_user_info_his old
left join
(
select
    *,'9999-99-99' end_date 
from ods_user_info 
where dt='2020-10-10'
) new
on old.id = new.id
UNION  all
--新的
select
    *,'9999-99-99' end_date 
from ods_user_info 
where dt='2020-10-10') tmp
order by id;

insert overwrite table dwd_dim_user_info_his  
select * from dwd_dim_user_info_his_tmp;

||–> 如何谨慎的完成SQL语句

a) 熟悉数据来龙去脉
	来龙：当前这层数据，从哪一层取，源头层数据的特点是什么
		特点：①  源头层数据的存储格式是什么？
				ods：TextFile + lzo +index ,需要设置输入格式
			 ②	源头层数据是如何同步的？
			 	ods_order_status_log:每日新增同步
			 	ods_cart_info:每日全量同步
		熟悉业务流程：
			例如：电商 --> 用户登录 --> 用户下单 --> 用户支付 --> 完成订单
			
	去脉：目标层的表，数据的存储格式，有没有其他的注意事项！
		 目标层的表的分区字段是什么，特征是什么，需要哪些字段。

b) 逻辑清晰，什么时候用lefe_join,join
		先分组，再过滤

平平无奇小码农

关注

9
点赞
踩
33

收藏

觉得还不错? 一键收藏
0
评论
数据仓库——上篇

文章目录一、数仓分层1.1 数仓的分层1.1.1 ODS(原始数据层)层1.1.2 DWD(数据明细层)层1.1.3 DWS(数据服务层)1.1.4 DWT(数据主题层)1.1.5 ADS（数据应用层）1.1.6 总结二、数仓理论2.1 范式、2.2 函数依赖2.3 常见的三范式2.4 关系建模和维度建模2.5 维度表和事实表2.6 维度建模的模型2.7 事实表的分类2.8 数据仓库建模2.8.1 ODS层2.8.2 DWD层2.8.3
复制链接

扫一扫

专栏目录