标准化处理
标准化处理
目的:标准化源数据
两种方式,kettle组件,纯写sql
- 字段标准化:数据类型标准化
- 添加时间戳:etl采集时间,源数据日期
- 删除字段:在数仓中不需要的字段
- 命名规范:字段名,表名,索引名...
- 数据清洗:过滤脏数据(where is not null,正则表达式)
- 采集频率
- T+1
- M+1
- 实时数仓
- 采集方式
- 增量
- 全量
更改数据类型
类型 | 统一 |
---|---|
整形 | 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');