//www.modb.pro/404?redirect=%2Fdb%2F241289

一、数仓分层

1、ODS层：原始数据层

ODS(O=original D=data S=store)

1)设计要点

文件存储源自多条销售业务平台、自动化测试埋点、网页爬虫调用的一系类动态动态数据库连接的动态动态数据。 我们公司要做三件事： 【1】要保持统计资料库文件原貌不做什么降重，补齐过往统计资料库文件，发挥统计资料库文件备份文件的帮助。 【2】便用lzo降低。100G的的数据降低以来应该为20G。 【3】撰写区别表，以防止未果的全表扫苗，常见按天贮存。

2）ODS层数据组成

【1】前面埋点系统日志：由kafka还有flume数据采集到HDFS上 flume采集程序的语句[flume]性能参数放置_胖胖学编写程序的搜狐博客网-CSDN搜狐博客网 【2】由网页前端业务部数据分析库用sqoop采集程序到HDFS上

3）前端埋点日志的处理

前面埋点笔记以JSON文件类型风格有着 建表语句

createexternaltable ods_flow_ph
(
    line string
)
partitioned by (dt string)
Stored as  
inputformat 'com.hadoop.mapred.DeprecatedLzoTextInputFormat'
outputformat 'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
;

将flume落盘的资料构建lzo目录，否则的话無法分块

hadoop jar /opt/module/hadoop-3.1.4/share/hadoop/common/hadoop-lzo-0.4.20.jar \
com.hadoop.compression.lzo.DistributedLzoIndexer \
-Dmapreduce.job.queuename=hive \
/user/hive/warehouse/ods.db/ods_flow_ph/dt=2022-11-11

数据库加载创建好目录的数据库

load data inpath '/user/hive/warehouse/ods.db/ods_flow_ph/dt=2022-11-11'intotable ods_flow_ph partition (dt='2022-11-11')；

4）MySQL数据库的处理

mysql统计sql语句的表经由sqoop收集到HDFS，运行\t作竖向符，那ods层的表也需运行\t作切割成符；

5）同步策略

（1）每日增量同步：流量表(ods_flow_ph)

也是埋点表，天级增测量表，一切的埋点数据表格都一览将在一張表上。 具有前后的端埋点和后台开发埋点 【1】前段埋点与后台埋点： 前边埋点：不要有网咯的，在本身手机手机app/网页ie浏览器/小软件程序里就能制造的大家方式资料报告信息报告，他们资料报告信息报告会期限申报到产品器。 前后端分离的资料报告信息报告：要有和MySQL交互设计的资料报告信息报告，譬如大家注冊的那时候，要把大家名登录密码同类的存道MySQL。 大数据报告信息的时间漂移：移动普通用户苹果六手机中存的前端部位的大数据报告信息会计算时间到必然条数再上传到服务性器，举例子80条，例如移动普通用户1-5号存了40条就关掉app，1-2号浏览器打开了app，这40条的大数据报告信息才会转为1-2的了。 举例说明：玩家青睐了别的个玩家是后面开发埋点玩家青睐文件列表+1，玩家发布下载一堆个培训课程视频播放播放是后面开发埋点,可能要把视频播放播放发布下载到服务管理器。 即使后面的统计数据都注入到安全阿里云服务器了，那为些什么还要有后面埋点呢？ 假如一这两个顾客观注了其他这两个顾客，又关闭观注了，也许下后端开发的相互就沒有这句话备案了，不过应该从埋点中间如何查看到：观注了，如果关闭观注了这类犯罪动作。这类犯罪动作在埋点表开发的情况，还得带顾客id,观注了谁的id,这这两个数据类型。 【2】带到策略 但如果是用的apache hadoop。用Nginx短路电流均衡性，把数剧均的推带到系统日志的高防服务器中。就可以直接用flume拉取数剧,taildir source memory channel hdfs sink。 要用蚂蚁爸爸的dataworks：涉及运维+资料集成式(从MySQL啥的导资料)+MaxCompute(相近hive，是可以写sql) 注:似的用蚂蚁爸爸的提供服务话语还用quickBI做会计报表展现出。都是后面和前边将资料载入到kafka中，然后呢从kafka中每小时候做资料一起。

（2）每日全量同步：用户表、订单表、看课表、课程表、章节表、教师表、互动表、卡片表

普通访客角度表：角度表。产自于前后端分离，面包涵了普通访客的基本的相关信息，比如说普通访客的两性，借款人年纪，生日宴，12星座，職業等情况。 拓展课角度表：记录时间拓展课屬性信息内容的角度表。这类拓展课id 拓展课种类 拓展课url 老师id等等等等。 货单表：由考生选购内容，购买行成的记录。 看课表：由学生们看课出现的记载，细致划分为几张表：值播看课记载表、录像看课记载表。 章数表：拓展课的每一项章数图片信息，举例拓展课id 拓展课命名 章数id 章数命名 等。 讲师表：存贮讲师的信息 活动表：用户名的评论区、瞩目、微信分享犯罪行为 卡片盒表：培训课页瀑光与弹框表，由组内另一队员用到spark开发技术

（3）特殊：一次性拉取，不建分区表（DIM层的父数据）

举例子东南部表，单位名称表

2、DIM

dim=dimension。数据存储为Parquet后缀名。

1）同步策略

全量同部：学科要素表。第一天和本周全都全量先添加到ODS再添加到DIM层。

拉链表：用户维度表

特种：政府部门表、国家（他清理）

2）拉链表

（1）什么是拉链表

采用文件贮存变迁，但变迁的规律过慢的数据分析库。若用全量文件贮存，会文件贮存巨大重覆的数据分析库，故此用拉链头表。

（2）每条数据的意义

该条数据文件的有效果日期。

（3）制造拉链表

【1】建表语句

create table dim_user_info(
id string,
user_name string --用户名称,
name string --真实姓名,
phone_num string,
gerder string --性别,
email string,
create_time string --创建时间,
operate_time string --操作时间,
start_date string --开始日期(拉链表特有),
end_date string --结束日期(拉链表特有)
)
partitioned by(dt string)
stored as parquet
table properties("parquet.compression"="lzo")

【2】区分计划 【3】第一日配载 要展开初始状态化，ods层该表首先天从MySQL拉取的每个数据文件置于9999-99-99区分

insert overwrite table dim.dim_user_info partition(dt='9999-99-99')
select
id,
user_name,
name,
phone_num,
gerder,
email,
create_time,
operate_time,
'2022-19-01' start_date,
'9999-99-99' end_date
from 
ods.ods_user_info
where dt='2022-10-01'

【4】每日装载

a)将近期的动态数据垃圾装载到9999-99-99系统分割 要是new为null（没能发展），则取old 假若new不為null（今年发生的了新增加及的变化），则取new

select
if(new.id is not null,new.id,old.id) id,
if(new.user_name is not null,new.user_name,old.user_name) user_name,
if(new.name is not null,new.name,old.name) name,
if(new.phone_num is not null,new.phone_num,old.phone_num) num,
if(new.gerder is not null,new.gerder,old.gerder) gerder,
if(new.email is not null,new.email,old.email) nemail,
if(new.create_time is not null,new.create_time,old.create_time) create_time ,
if(new.operate_time is not null,new.operate_time,old.operate_time) operate_time,
if(new.start_date is not null,new.start_date,old.start_date) start_date,
if(new.end_date is not null,new.end_date,old.end_date) end_date
(
    select
    id,
    user_name,
    name,
    phone_num,
    gerder,
    email,
    create_time,
    operate_time,
    '2022-19-01' start_date,
    '9999-99-99' end_date
    from 
    dim.dim_user_info
    where dt='9999-99-99'
)ods
full join
(
    select
    id,
    user_name,
    name,
    phone_num,
    gerder,
    email,
    create_time,
    operate_time,
    '2022-10-01' start_date,
    '9999-99-99' end_date --新增及变化的数据都是最新数据
    from 
    ods.ods_user_info --ods_user_info表是每日增量导入的
    where dt='2022-10-01' --新增及变化的数据
    )new
on ods.id=new.id

b)将到期数据文件安装到前有一天的分离（提前准备准确时间范围内如果没有相同） new和old都会有的资料取old

select
old.id id,
old.user_name user_name,
old.name name,
old.phone_num num,
old.gerder gerder,
old.email nemail,
old.create_time create_time ,
old.operate_time operate_time,
old.start_date start_date,
old.end_date end_date
(
    select
    id,
    user_name,
    name,
    phone_num,
    gerder,
    email,
    create_time,
    operate_time,
    '2022-19-01' start_date,
    '9999-99-99' end_date
    from 
    dim.dim_user_info
    where dt='9999-99-99'
)ods
full join
(
    select
    id,
    user_name,
    name,
    phone_num,
    gerder,
    email,
    create_time,
    operate_time,
    '2022-10-01' start_date,
    '9999-99-99' end_date --新增及变化的数据都是最新数据
    from 
    ods.ods_user_info --ods_user_info表是每日增量导入的
    where dt='2022-10-01' --新增及变化的数据
    )new
on ods.id=new.id
where new.id is not null and old.id is not null
;

（4）对拉链表进行查询

【1】更改在某一时间有效性的所有的用户名的大数据

--获取2019-01-01有效的所有历史数据
select * from user_info where start_date<='2019-01-01' and end_date>='2019-01-01';

【2】获得到目前为止全部的手机用户的近期数值

select * from user_info where end_date>='9999-99-99';

3、DWD层

dwd=data warehouse detail

1）说明

dwd层是对真相表的净化处理，表达的是保险业务的最短颗粒层，一切的数据的记录卡也能够 以从这上获得，为之后的的dws和dwt层做准备工作。 dwd层是站到选定 光彩的事实表的的基础上，对特点模型制作的感觉。

2）对埋点数据进行处理

将2g流量表(ods_flow_ph)表独一无二的字符串line这一json串采取分析,分析连成一片个字符串有一个字符串的。

3）对业务数据进行处理

【1】时间是浏览其实表 不光水流量表意外的表都都在MySQL导出闯进来的。

103-尚硅谷-数仓搭建-DWD层优惠券领用事实表_哔哩哔哩_bilibili

一个知识点:累计快照事实表(没看完)
如：优惠券领用，这种发生周期变化的场景
createtable dwd_coupun_use(
coupun_id string, --优惠券id
user_id string, --用户id
coupun_status string, --优惠券状态
using_time string, --使用时间(下单)
used_time string, --使用时间(支付)
expire_time  string --过期时间
)
partitioned by(dt string)
;

4、dim和dwd

DIM和DWD用于特点模型场景方法，一样 用于星型实体模型场景方法，显示工作状态一样 为白羊座实体模型场景方法。信息见特点模型场景方法的过程。

5、dws、dwt、ads

dws= data warehouse service dwt=data warehosue topic ads=application data store dws、dwt、ads都是以要为驱动下载的，和维度模型制作就已经 找不到的关系了。 dws、dwt：又称为宽表面层，这一二层的结构设计政治思想主要相当，根据以上案例库展开情况说明：

1）问题引出

的两个各种需求，调查总计所有地区下单的总数、调查总计内个地区下单的总标准

2）处理办法

还是将区域和签单表参与join,group by区域，以后来估算出来，亦是的数剧被来估算出来了四次，真正上像的游戏环境有非常多，那应该怎么设计方案构思是这样才能解决多次反复重复来估算出来呢？应对上面游戏环境能能设计方案构思每张区域表，其主键为区域ID，字符为：购买多次，购买费用，消费多次，消费费用等，上面所有指标英文統一参与来估算出来，并将可是维持在该宽腕表，是这样就能有效率解决数剧的多次反复重复来估算出来。 3）要求哪些宽表（活动形式表）：以空间维度为基准线 4）DWS和DWT层的区分： DWS层储存所有的当晚的分类汇总活动，诸如每一省份当晚订单危害，订单大额等，dwt层储存的是所有的主题内容男朋友的总共使用活动，诸如每一省份最进7天（十天、30天、60天）的订单危害，订单大额等。 5）ads层：文件存储每个统计报表要有的结局。