平台组 数据组 运维组
ETL工程师:对数据进行挖掘、收集、处理
BI工程师:对业务需求进行计算
报表开发工程师:对数据库的数据,进行可视化界面的开发
数据库工程师:对数据库本身进行配置和操作
大数据平台的架构/数仓架构:
1.数据源系统:
核心系统、客户系统、财务系统、人力资源系统、结算系统、销售系统、第三方系统数据、人工的补录数据、埋点数据、日志信息等等。
结构化数据:数据库中的数据、csv表格、excel表格
半结构数据:json xml
{'username':'lilei','age':18,'sex':'男'}
lilei
18
男
非结构数据:图片 视频 音乐
2.ODS临时存储层:将数据源的数据,原封不动的抽取到ODS临时存储层的数据库里面,在原有是数据基础上,一般只添加数据的来源和数据的抽取时间:所有的数据都只保存3-6个月的时间
--ODM:数据贴源层:直接抽取数据源的数据,不做任何处理,只是添加时间和来源字段
--SDM:标准数据层:抽取ODM上面的数据,统一字段的数据类型、长度、精度;统一计算的单位、金额的币种;统一客户的代码;统一状态;统一默认值;统一时间的格式...
3.HDS历史数据存储层:超过了3-6月之后的ODS数据保存的位置
4.DW数据仓库:抽取SDM数据层的数据,进一步的进行筛选和清洗。数据在这里,一般是保存3年的时间
怎么进行筛选和清洗?
排序 去重 字段的合并 字段的拆分 空值的默认值处理 新旧代码的处理 数据的筛选
---FDM:基础数据层,为不同的数据做分类,分成不同的主题库进行保存
主题库有哪些?
客户主题:保存的就是所有的客户的信息
参与者主题:商家和客户的所有的信息
协议主题:保存的所有的协议和合同的内容
事件主题:任何的资金的流动,都是一次事件,保存所有的资金的流动
绩效主题:保存所有销售人员和销售团队的信息
财务主题:保存的是公司里面每一个部门的财务状况和信息
公共主题:汇率、利息比率、币种转换率、日期等...
产品主题:理财产品、借贷产品的所有信息
...
5.DM数据集市:将DW数据仓库的数据,根据业务部门的需求和指标强详情,对数据库的数据进行计算,保存结果到数据集市里面,不同的数据会保存到不同的对应的主题库的域中
--ADM:聚合计算层:分不同的域来保存计算的结果,进行数据的聚合计算和业务计算的
集市层的所有的表,都要符合第三范式(3NF):
第一范式:每一个列都是不可再拆分的列
第二范式:每个表都是有主键的
第三范式:每一个表的其他的列,都是和主键有直接相关的关系
如何计算和保存数据?
有两种常见的数据保存的模型:
星型模型:将一个大的事实表,直接拆分成多个维度表,来保存和计算数据,在工作中星型模型比较常见
优点:结构整体比较简单,表的数量比较少
缺点:每个表格都可能会存在一些冗余数据
雪花模型:将一张拆分后的维度表,再当成一个小的事实表,继续进行维度的拆分
优点:每个表格都没有冗余的数据,表格的内容比较干净
缺点:整个结构就会比较复杂,表的数量也会很多
DA数据报表:也叫做APP层或者MDM层...将DM的数据进一步进行计算和汇总,最终用于页面的可视化展示
一个大数据的组:数据开发的人数 5-10
产品经理 业务人员 数据开发 软件测试
工作的流程:
业务人员:收集客户需求,确定客户需要什么内容(指标详情文档)
产品人员:对接业务和技术,会将业务收集的需求,转换成技术可以看得懂、能够实际操作的文档(需求规格说明书)
召开需求评审会议:产品人员主持、业务、开发、测试参与,讨论每个需求的可实现性、以及每个需求的实现时间
编写开发设计文档
编写sql语句(代码走读),编写调度设计的文档
进行开发的自测(验证是否能够跑通、数据是否正确、数据是否一致)
上传写好的代码(SVN git)
软件测试进行sql脚本的测试:写bug单
进行准生产环境的最后一次验证
版本更新上线(每个公司都有确定的时间点)(版本的敏捷迭代更新)
最后进行一次线上的数据验证
整理数据字典的excel文档
在工作中有几套环境:
本地环境
开发环境
测试环境
预发布环境(准生产环境,和线上的环境一模一样的环境)
生产环境(正式环境)
ETL的数据处理:
E:extract
T:transform
L:load:删除所有的索引;加载数据到数据库表格;恢复索引以及清空临时表
完全刷新:全量更新,先使用truncate清空数据表,然后重新导入所有的数据,一般全量更新都是在DM和DA里面。
事件增量:增量更新,通过时间字段将新增数据抽取出来加载到数据库中,一般是ODS和DW的表格比较多。
镜像增量和镜像对比:镜像更新,是使用两个表的一个列或者是多个列进行数据的对比,如果这些列的数据一致,对其他的字段进行数据的更新,如果这些列的数据不一致,那么就进行数据的新增。一般用来处理历史数据。
--先准备两个基本的数据源的表格
--长沙的销售数据
create table changsha_sale(
saleid varchar2(50) primary key,
saleman varchar2(50),
goods varchar2(50),
price number(8,2),
saletime date
);
insert into changsha_sale values('cs2020113001','tom','华为运动手表',200,sysdate-2);
insert into changsha_sale values('cs2020113002','jack','水杯',168,sysdate-2);
--武汉的销售数据
create table wuhan_sale(
saleid varchar2(50) primary key,
saleman varchar2(50),
goods varchar2(50),
price number(8,2),
saletime date
);
insert into wuhan_sale values('wh2020113001','lily','锁',14,sysdate-2);
insert into wuhan_sale values('wh2020113002','lucy','钥匙',3,sysdate-2);
--定义一个ods系统的表格
create table ods_sales(
saleid varchar2(50) primary key,
saleman varchar2(50),
goods varchar2(50),
price number(8,2),
saletime date,
sys_source varchar2(50),
update_time date
);
select * from ods_sales;
--定义ods表格全量抽取的存储过程
create or replace procedure refresh_sales
as
begin
--清空数据表
execute immediate 'truncate table ods_sales';
--重新添加表格的数据
insert into ods_sales select changsha_sale.*,'长沙',sysdate from changsha_sale;
commit;
insert into ods_sales select wuhan_sale.*,'武汉',sysdate from wuhan_sale;
commit;
end;
--调用存储过程
call refresh_sales();
--编写一个增量抽取的过程
create or replace procedure add_sales
as
begin
--为了避免重复增量抽取产生重复的数据,要先删除今天抽取的之前的数据
delete from ods_sales where to_char(update_time,'yyyymmdd')=to_char(sysdate,'yyyymmdd');
commit;
--重新抽取今天的数据
insert into ods_sales
select changsha_sale.*,'长沙',sysdate
from changsha_sale
where to_char(saletime,'yyyymmdd')=to_char(sysdate,'yyyymmdd');
commit;
insert into ods_sales
select wuhan_sale.*,'武汉',sysdate
from wuhan_sale
where to_char(saletime,'yyyymmdd')=to_char(sysdate,'yyyymmdd');
commit;
end;
call add_sales();
--镜像更新的存储过程 merge into
merge into的基本语法
merge into 要更新的表名字 a表
using (数据来源的sql语句) b表
on (a.主键=b.主键)
when matched then
update set
a.列=b.列
when not mathced then
insert (a.列)
values (b.列);
create or replace procedure mirror_sales
as
begin
merge into ods_sales a
using (select changsha_sale.*,'长沙' sys_source,sysdate update_time from changsha_sale
union all
select wuhan_sale.*,'武汉' sys_source,sysdate update_time from wuhan_sale) b
on(a.saleid=b.saleid)
when matched then
update set --更新除了主键之外的其他列
a.saleman=b.saleman,
a.goods=b.goods,
a.price=b.price,
a.saletime=b.saletime,
a.update_time=b.update_time
when not matched then --插入包括了主键之内的所有列
insert (a.saleid,a.saleman,a.goods,a.price,a.saletime,a.sys_source,a.update_time)
values (b.saleid,b.saleman,b.goods,b.price,b.saletime,b.sys_source,b.update_time);
commit;
end;
call mirror_sales();
2020-11-30练习:
--银行分行信息表
create table bank_info(
bank_id number primary key, --银行id
bank_addr varchar2(100) --银行所在城市
);
--客户信息表
create table customer_info(
cus_id number primary key, --客户id
cus_name varchar2(20), --客户名字
cus_type varchar2(50), --证件类型
cus_number varchar2(50) --证件号码
);
--客户余额表
create table balance_info(
bal_id varchar2(50) primary key, --借据号
contract_id varchar2(50), --合同编号
petition_id varchar2(50), --申请编号
loan_amount number(11,2), --贷款金额
loan_balance number(11,2), --贷款余额
overdue number --逾期天数
);
--客户贷款合同表
create table contract_info(
contract_id varchar2(50) primary key, --合同编号
petition_id varchar2(50), --申请编号
cus_id number, --客户id
amount number, --授信金额
amount_date date --授信日期
);
--客户贷款申请表
create table petition_info(
petition_id varchar2(50) primary key, --申请编号
petition_date date, --申请日期
product varchar2(50), --产品名称
status char(1), --申请状态
stat_mod_date date, --状态最新变更日期
cus_id number, --客户编号
bank_id number --银行编号
);
select * from tc_balance_info@teacher_db;
select * from tc_bank_info@teacher_db;
select * from tc_contract_info@teacher_db;
select * from tc_customer_info@teacher_db;
select * from tc_petition_info@teacher_db;
统计每个地区,贷款的审核通过率和平均的审核通过时间,和未还金额剩余多少?
地区 贷款的审核通过率 平均的审核通过时间 未还金额
北京 76% 54 6544311
select a.bank_addr,tongguolv,tongguoshijian,zonghe from
(select bank_addr,round(sum(status)/count(1)*100,2)||'%' tongguolv from tc_petition_info a join tc_bank_info b
on a.bank_id=b.bank_id
group by bank_addr) a
join
(select bank_addr,ceil(avg(stat_mod_date-petition_date)) tongguoshijian from tc_petition_info a join tc_bank_info b
on a.bank_id=b.bank_id where status=1
group by bank_addr) b on a.bank_addr=b.bank_addr
join
(select bank_addr,sum(loan_balance) zonghe from tc_balance_info a join tc_petition_info b on a.petition_id=b.petition_id
join tc_bank_info c on c.bank_id=b.bank_id
group by bank_addr) c on c.bank_addr=a.bank_addr;