大数据体系

平台组 数据组 运维组

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;