数仓ods层到ads层数据抽取常用sql_业务应该怎么读取数仓的ads层
赞
踩
一. 数仓分层
数据仓库在构建过程中通常都需要进行分层处理。
业务不同，分层的技术处理手段也不同，数仓分层原因：
1.把复杂问题简单化，每一层只处理简单的任务，方便定位问题；
2.减少重复开发，规范数据分层，通过中间层数据能够减少重复计算，且增加计算结果的复用性；
3.隔离原始数据，不论是数据的异常还是数据的敏感性，使真实数据与统计数据解耦开。
数仓一般分为ODS层，DW层，ADS层
二．ODS层
1.ods层概念
数据运营层：Operation Data Store 数据准备区，也称为贴源层。数据源中的数据，经过抽取、洗净、传输，也就是ETL过程之后进入本层。
该层的主要功能：
- ODS是后面数据仓库层的准备区
- 为DWD层提供原始数据
- 减少对业务系统的影响
为了考虑后续可能需要追溯数据问题，因此对于这一层就不建议做过多的数据清洗工作，原封不动地接入原始数据即可
2.数据抽取方式
添加数据源，通过同步任务在ods层建表，将业务系统的数据抽取到ods表中
同步任务分为整库同步和数据同步（单表同步）
三．DW层
1.dwd层概念
数据细节层：data warehouse details，DWD
该层是业务层和数据仓库的隔离层，保持和ODS层一样的数据颗粒度；主要是对ODS数据层做一些数据的清洗和规范化的操作，比如去除空数据、脏数据、离群值等。
为了提高数据明细层的易用性，该层通常会才采用一些维度退化方法，将维度退化至事实表中，减少事实表和维表的关联。
2.数据抽取方式
通过SQL语句将ods层表数据经过清洗后导入到dwd层
数据脱敏（姓名，手机号，邮箱，身份证号，个人地址等）
3.常用SQL
建表语句
数据装载
四．ADS层
1.ads层概念
数据应用层：Application Data Service，ADS；
该层主要是提供给数据产品和数据分析使用的数据，一般会存放在ES、Redis、PostgreSql等系统中供线上系统使用；也可能存放在hive或者Druid中，供数据分析和数据挖掘使用，比如常用的数据报表就是存在这里的。
2.数据抽取方式
按照业务需求从统一数仓层、标签数据层抽取数据，并面向业务的特殊需要加工业务特定数据，以满足业务及性能需求，向特定应用组装应用数据；
3.常用SQL
(1)
-- 输入表：ads_user_total
-- 输出表：ads_user_total_1
-- 功能说明：此任务用于用户统计
建表语句:
DROP TABLE IF EXISTS ads_user_total;
CREATE EXTERNAL TABLE `ads_user_total` (
`dt` STRING COMMENT '统计日期',
`recent_days` BIGINT COMMENT '最近天数,0:累积值,1:最近1天,7:最近7天,30:最近30天',
`new_user_count` BIGINT COMMENT '新注册用户数',
`new_order_user_count` BIGINT COMMENT '新增下单用户数',
`order_final_amount` DECIMAL(16,2) COMMENT '下单总金额',
`order_user_count` BIGINT COMMENT '下单用户数',
`no_order_user_count` BIGINT COMMENT '未下单用户数(具体指活跃用户中未下单用户)'
) COMMENT '用户统计'
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
LOCATION '/warehouse/gmall/ads/ads_user_total/';
数据装载:
insert overwrite table ads_user_total
select * from ads_user_total
union
select
'2020-06-14',
recent_days,
sum(if(login_date_first>=recent_days_ago,1,0)) new_user_count,
sum(if(order_date_first>=recent_days_ago,1,0)) new_order_user_count,
sum(order_final_amount) order_final_amount,
sum(if(order_final_amount>0,1,0)) order_user_count,
sum(if(login_date_last>=recent_days_ago and order_final_amount=0,1,0)) no_order_user_count
from
(
select
recent_days,
user_id,
login_date_first,
login_date_last,
order_date_first,
case when recent_days=0 then order_final_amount
when recent_days=1 then order_last_1d_final_amount
when recent_days=7 then order_last_7d_final_amount
when recent_days=30 then order_last_30d_final_amount
end order_final_amount,
if(recent_days=0,'1970-01-01',date_add('2020-06-14',-recent_days+1)) recent_days_ago
from dwt_user_topic lateral view explode(Array(0,1,7,30)) tmp as recent_days
where dt='2020-06-14'
)t1
group by recent_days;
(2)
-- 输入表：ads_user_change_1
-- 输出表：ads_user_change
-- 功能说明：此任务用于统计变动用户
流失用户：末次活跃时间为7日前的用户即为流失用户
回流用户：末次活跃时间为今日，上次活跃时间在8日前的用户即为回流用户
建表语句:
DROP TABLE IF EXISTS ads_user_change;
CREATE EXTERNAL TABLE `ads_user_change` (
`dt` STRING COMMENT '统计日期',
`user_churn_count` BIGINT COMMENT '流失用户数',
`user_back_count` BIGINT COMMENT '回流用户数'
) COMMENT '用户变动统计'
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
LOCATION '/warehouse/gmall/ads/ads_user_change/';
数据装载:
insert overwrite table ads_user_change
select * from ads_user_change
union
select
churn.dt,
user_churn_count,
user_back_count
from
(
select
'2020-06-14' dt,
count(*) user_churn_count
from dwt_user_topic
where dt='2020-06-14'
and login_date_last=date_add('2020-06-14',-7)
)churn
join
(
select
'2020-06-14' dt,
count(*) user_back_count
from
(
select
user_id,
login_date_last
from dwt_user_topic
where dt='2020-06-14'
and login_date_last='2020-06-14'
)t1
join
(
select
user_id,
login_date_last login_date_previous
from dwt_user_topic
where dt=date_add('2020-06-14',-1)
)t2
on t1.user_id=t2.user_id
where datediff(login_date_last,login_date_previous)>=8
)back
on churn.dt=back.dt;
