标准化处理

标准化处理

目的:标准化源数据
两种方式,kettle组件,纯写sql

1. 字段标准化:数据类型标准化
2. 添加时间戳:etl采集时间,源数据日期
3. 删除字段:在数仓中不需要的字段
4. 命名规范:字段名,表名,索引名...
5. 数据清洗:过滤脏数据(where is not null,正则表达式)
6. 采集频率
   • T+1
   • M+1
   • 实时数仓
7. 采集方式
   • 增量
   • 全量

更改数据类型

类型	统一
整形	INTEGER(10)
金额	DECIMAL(20,2)
浮点	DECIMAL(20,x)
比率	DECIMAL(12,6)
净值	DECIMAL(16,3)
日期	VARCHAR(8),YYYYMMDD
时间日期	VARCHAR(14),YYYYMMDDHHmmSS

使用kettle的字段选择-->元数据中进行更改
也可以在表输出中直接写sql,使用同to_char来进行转换

添加时间戳

采集频率T+1
源数据日期:

• 代表系统数据产生的日期,类似食品生产日期
• 源系统数据下发时,肯定要带一个时间戳指明这批数据是哪天哪一批次的数据

ETL采集时间

• 代表数据采集的时间,方便开发人员

在ODS层存储数据库上每个表添加字段

ODS_SRC_SYS_NO CHAR(30) 原系统编号
ODS_SRCDATE CHAR(30) 数据日期
ODS_ETLDATE CHAR(30) 采集日期

在使用kettle获取源数据时添加:

, to_char(sysdate-1,'yyyy/mm/dd') as src_date
, to_char(sysdate,'yyyy/mm/dd Hh24:mi:ss') as etl_date
, '核心' as src_sys_no
FROM LOAN1.F_LN_LNP_CTRT_CBAPP

增量和全量

增量
采集数据截止到昨天新增有变化的数据(只采集昨天一天的数据)必须有个增量字段

, to_char(sysdate-1,'yyyy/mm/dd') as src_date
, to_char(sysdate,'yyyy/mm/dd Hh24:mi:ss') as etl_date
, '核心' as src_sys_no
FROM LOAN1.F_LN_LNP_CTRT_CBAPP
WHERE CONTDATE=trunc(sysdate-1,'dd')

全量

• 采集截止到今天0点之前的,所有数据

• 快照表

  • DW(数仓)不删除历史数据,加一个时间戳,全量存储每天的数据

, to_char(sysdate-1,'yyyy/mm/dd') as src_date
, to_char(sysdate,'yyyy/mm/dd Hh24:mi:ss') as etl_date
, '核心' as src_sys_no
FROM LOAN1.F_LN_LNP_ACCT_AMWKPL06

清空目标库

每次采集昨天的数据,避免单日多次运行,产生重复数据

kettle --> 脚本 --> 执行sql脚本

增量采集时,指定的增量字段便是CONTDATE
采集的数据是昨天产生的,所以这里指定删除条件为昨天产生的

delete from bkf_ln_lnp_ctrt_cbapp where CONTDATE=to_char(sysdate-1,'yyyy/mm/dd');

全量采集时,在目标库中删除今天要采集的数据

delete from bkf_ln_lnp_acct_amwkpl06 where trim(ODS_SRCDATE)=to_char(sysdate-1,'yyyy/mm/dd');