7.数据仓库出ETL操作指导

一 数仓架构介绍

数仓架构

CDP负责数据源之间异构和同构数据的交换，可以将外部源系统中的数据导入ODPS，也可以将ODPS中的数据导入外部数据库。

ODPS作为数仓平台被划分为三大块：

汇聚层：外部源系统中的数据进入数仓首先被暂存在这一区域，表结构与源系统完全一致，使用时间作为分区字段，记录每天抽取上来的增量或全量数据，作为映像层和主题层的数据来源，这里只保留最近7-30天的数据。

映像层：负责备份原始数据的全量镜像，表结构与源系统保持一致，使用时间最为分区字段，每天一个分区，记录当天的全量数据，历史分区根据业务需要保留一定时长，通常3-5年。

主题层：主题层按照【主题-实体】来组织数据，汇集层的数据经过清洗、转换和整合等处理后，以拉链的方式保存主题层，数据质量很高，这里的数据根据业务需求保留一定时长，通常3-5年。

专题层：专题层指基于映像层和主题层的数据做面向应用或部门的数据加工与统计，机器学习与算法处理，按照依赖的数据区域可分为依赖映像层的独立数据集市和依赖主题层整合后数据的依赖性集市。

数仓中的工作流

以一个表为例，从源系统进入数仓，到被集市层的应用所使用，经历的过程如下：

1. CDP抽取，数据被保存在汇聚层的一个分区中。
2. 汇聚层保存的数据被存入镜像层。
3. 汇聚层保存的数据经过清洗、转换、整合等操作被存储在主题层。
4. 集市层读取主题层的数据参与应用计算，产出具有业务价值的数据。

上述四个过程构成了一条工作流，工作流上的每个节点都有一定的顺序依赖，前一个节点做完了，才能开始执行下一个节点（2和3可以并行，他们共同的上游节点是1），由于每个表的情况都不同，所以进入数仓的每个表都需一条适合自己的工作流，由于源系统中的表会被更新，所以工作需要被定期调度，使数仓中的数据可以得到及时的更新，在真实生产环境的数仓中，会几十甚至几百条这样的工作流在不断的更新数仓中的数据。

数仓的开发与运维

ODPS作为数仓的实现实体，它的开发和运维使用BASE来完成，数仓中的各层对于BASE上的不同的Project，计算资源和存储资源是隔离的，层与层之间的数据访问依赖BASE各Project的授权机制，BASE上可以创建CDP任务、创建数据加工处理任务，并将各个任务串成工作流进行调度，详细的BASE介绍请参考BASE说明文档。

二 ETL准备工作

ETL，是英文 Extract-Transform-Load 的缩写，用来描述将数据从来源端经过抽取（extract）、转换（transform）、加载（load）至目的端的过程。

在上一章描述的数仓系统中，ETL的目的是为了把数据从源系统中送到数仓的主题层和镜像层，中间经过的数据抽取、加工处理都属于ETL的过程。

在开发ETL之前，我们需要做充分的准备工作，包括如下内容：

1. 明确数据从哪些业务系统过来，了解各个业务系统的核心业务，明确各个业务系统中表和字段的意义。
2. 了解表中中各个字段的类型、取值范围、是否可以为空、格式，编码规范等规则，并以此检查数据的存在哪些质量问题。
3. 明确源系统的数据提供形式，数据库、文件、数据服务接口等，数量有多大，是否存在非结构化数据。
4. 业务系统的数据是如何更新的，以及更新周期，更新记录时是否会带上时间戳。

三 数据抽取

数据抽取分类

1. 全量抽取，将源系统中的数据全部抽取过来，这种操作数据量会很大，占用大量的带宽，通常在数仓初始化数据时，一次性载入全量数据，后续使用增量的方式同步源系统的数据更新。
2. 增量抽取，将源系统中一段时间内变化的数据抽取过来，适合周期性执行，同步源系统的数据变化到数仓中。

数据抽取

1. 在汇聚层创建与源系统中表结构完全一个的数据表，并指定分区字段，字段名通常命名为‘dt’，datetime的缩写，由于创建表是一次性操作，所以使用BASE中手动任务完成，为每个表创建一个手动任务，注意组织好任务的目录结构，以方便日后管理维护，建议按类似如下结构的目录来组织任务

将手动任务发布到生产环境，并手动触发执行，完成数据表的创建。

1. 在汇聚层配置数据源，创建CDP任务，配置CDP的任务（源表、目的表、分区字段、where条件、调度周期等），将CDP任务发布到生产环境，参与调度。CDP任务的组织形式参考上面的目录结构。CDP的配置细节请参考BASE手册。

四 数据清洗和转换

数据进入汇聚层后，会被分别整合到镜像层和主题层，数据进入镜像层相当于直接拷贝，不需要做中间的加工处理，只需根据镜像层的数据备份粒度（历史全量切片、增量切片或者是拉链表）将数据导入。而数据进入主题层是需要进行清洗和转换的，因为主题层对数据的质量和一致性要求很高。按照这种情况，汇聚层作为后续流程的数据提供者，不对数据做任何的加工处理，而镜像层和主题层作为两个数据的需求者，需要根据各自对数据的使用目的进行相应的数据处理，然后存储在各自的表中，所以数据加工处理工作是在数据的需求方哪里完成的，即数据的清洗、转换和载入的任务是在主题层创建的，镜像层的数据载入任务是在镜像层创建的。

数据清洗

数据清洗的主要任务是过滤掉不合格的数据，被过滤掉的数据应由业务部门确认，是否丢弃或修正后重新抽取。不符合要求的数据主要包括不完整的数据、错误的数据和重复的数据。

1. 不完整的数据：主要是信息缺失，不该为空的字段为空了，例如用户信息表中用户名不应该为空，如果为空属信息缺失的情况，但有些字段是允许为空的，例如别名，不是每个人都有的，出现空值时不属信息缺失。
2. 错误数据：这一类错误产生的原因可能是由于业务系统不健全，接收输入时没有进行完整的合法性检查，导致在数据库中写入错误数据，比如错误的日期格式、非法字符、超出合法范围的值等。
3. 重复数据：完全相同的记录出现多次，我们只需保留一条记录。

数据清洗的数据源来自于汇聚层的表，每个表的数据质量情况可能都不相同，需要为每个表建立一个清洗任务，即ODPS SQL任务，同时为每张表建立两个临时表，一张用于存储经过清洗符合要求的数据，另外一张表存储被清洗掉的不符合要求的数据，符合要求的数据继续参与后面的数据处理，不符合要求的数据返回给业务部门进行确认。

在主题层创建数据清洗的SQL任务，编写数据清洗SQL脚本，测试脚本效果，配置任务的上游节点为汇聚层对应同一张表的CDP任务，然后发布到生产环境参与调度。清洗任务的目录组织形式参考下图：

按照主题组织一级目录，二级目录为主题下的表名，二级目录下放置SQL脚本，一个脚本可以同时完成数据的清洗、转换和装载，参考下图：

数据清洗SQL示例：

-- 创建临时表 存储符合规则的数据

drop table if exists table_ok;  

create table table_ok like project_huijuceng.table_src;

alter table table_ok set lifecycle 1;

-- 创建临时表 存储不符合规则的数据

drop table if exists table_failed;

create table table_failed like project_huijuceng.table_src;

alter table table_failed set lifecycle 1;

-- 将符合规则的数据插入临时表

insert overwrite table table_ok

    select * from project_huijuceng.table_src

    where dt='${bizdate}'                    -- 指定分区参数

    and field1 is not null and field2<>''    --  指定清洗规则   

-- 将不符合规则的数据插入临时表

insert overwrite table table_filed

    select * from project_huijuceng.table_src

    where  dt='${bizdate}'

and !(field1 is not null and field2<>'')

数据转换

数据转换是一个数据标准化的过程，包括：统一字段命名、统一数据类型、统一编码规范、统一字段度量单位、统一时间格式等。

数据转换SQL示例：

drop table if exists table_convert;

create table table_convert like table_ok;

alter table table_convert set lifecycle 1;

insert overwrite table table_convert

select field1, field2,

--将汉字的性别信息转化为标准的性别编码

case when fied3='男'  then 1

        when field3='女' then 0

        when field3='未知' then 2

end as field3

from table_ok;

一些通用的数据转换需求或者SQL很难实现的数据转换，例如字符串的全角转半角，可以通过UDF函数来完成。

数据的清洗和转换也可以在一个SQL语句中完成，使用子查询的方式，内层查询完成数据清洗，外层查询完成数据转换，这样可以少创建一次临时表，参考如下代码：

insert overwrite table table_convert

select field1, field2,

--将汉字的性别信息转化为标准的性别编码

case when fied3='男'  then 1

        when field3='女' then 0

        when field3='未知' then 2

end as field3

from

(

select * from project_huijuceng.table_src

where dt='${bizdate}'                      -- 指定分区参数

and field1 is not null and field2<>''    --  指定清洗规则   

）t;

装换后的数据被存储在临时表中，数据装载过程会将转换后的数据装载到主题层中的正式表里。

数据装换时涉及到数据的统一编码，例如‘男’使用数字1编码，在数仓系统中会涉及到很多这样的编码，例如职业编码、地区编码、学历编码等，有标准规范的建议使用规范中的编码，如果没有标准规范，只能自定义编码。所以这些编码需要统一管理，在主题层创建一个‘数据字典’主题，用户存储所有的编码信息，目录组织形式参考下图：

编码的数据可能来自外部的某个数据库，也需要通过工作流定期同步变更的信息，所以上图中的SQL脚本同样是完成数据清洗、转换和装载的任务，同样也需要一个手动任务来创建对应的表，数据字典和其他主题在处理上是一样的。

六 数据装载

数据装载分为主题层的数据装载和镜像层的数据装载，通常情况下主题层使用拉链表的形式存储数据，镜像层使用分区保存原始表的每日全量数据，所以两个区域的装载逻辑是不同的，下面分别介绍。

镜像区的数据装载

首先在镜像层对应的Base Project中创建手动任务节点，用户创建目标表，每个表一个任务节点，任务目录组织方式参考汇聚层手动任务的目录组织方式，测试后，发布到生产环境，手动触发执行，完成表结构的创建。

接下来创建ODPS SQL任务，来实现镜像层数据的装载，配置任务的上游节点为汇聚层对应于同一张表的CDP任务，镜像层任务的目录组织形式请参考汇聚层的。

镜像层数据装载逻辑依赖于汇聚层的数据抽取方式，具体如下：

1. 如果汇聚层每天抽取的是全量数据，只需要把汇聚层当天的抽取的数据直接insert overwrite到镜像层对应表的对应分区中即可，SQL示例代码如下：

insert overwrite table AAA partition(dt='${bizdate}')

select * from huijuceng.AAA where dt='${bizdate}'

1. 如果汇聚层每天抽取的是增量数据，需要使用汇聚层的增量数据与镜像层前一天的全量数据拼出一个当天的全量数据保，然后保存到当天的分区中，SQL示例代码如下：

insert overwrite table AAA partition(dt='${bizdate}')

select id, value from

(

    -- 取前一天的全量中未被更新和未被删除的记录

    select t1.id, t1.value from

        AAA t1 left outer join huijuceng.AAA t2

        on t1.id = t2.id -- 主键

        and t1.dt = to_char(dateadd(to_date('${bizdate}', 'yyyymmdd'), -1, 'dd'), 'yyyymmdd')

        and t2.dt = '${bizdate}'

        where t2.id is null or t2.flag = 'D' -- 'D'删除标志

    

    union all

    

    -- 当天的增量

    select id, value from  huijuceng.AAA

        where dt = '${bizdate}' and t2.flag <> 'D'

) t

主题层数据装载

首先在主题层对应的Base Project中创建手动任务，用于创建目标表，每个表一个任务，任务目录组织方式参考数据清洗任务的目录组织方式，测试后，发布到生产环境，手动触发执行，完成表结构的创建。

主题层的数据装载任务与数据清洗和转换时一个任务，SQL脚本卸载统一文件中，因为主题层使用拉链表保存数据，所以数据装载的过程稍复杂一些，SQL示例代码如下：

insert overwrite table ll_all

select id, val1, s_date, e_date from

(

-- 原有ID 已关链

select t2.id, t2.val1, t2.s_date, t2.e_date

    from ll_all t2

    where t2.e_date < '30001231'

    

union all

-- 原有ID 无更新

select t2.id, t2.val1, t2.s_date, t2.e_date

    from ll_all t2

    left outer join

        ll_add t1

    on t1.id = t2.id

    and t1.dt='${bizdate}'

    where t2.e_date = '30001231'

    and t1.id is null

union all

-- 原有ID 无值更新

select t2.id, t2.val1, t2.s_date, t2.e_date

    from ll_all t2

    inner join

        ll_add t1

    on t1.id = t2.id

    and t1.dt='${bizdate}'

    where t2.e_date = '30001231'

    and coalesce(t1.val1, '') = coalesce(t2.val1, '')

    

union all

-- 原有ID 关旧链

select t2.id, t2.val1, t2.s_date, '${bizdate}' as e_date

    from ll_add t1

    inner join

        ll_all t2

    on t1.id = t2.id

    and t2.e_date = '30001231'

    where t1.dt='${bizdate}'

    and coalesce(t1.val1, '') <> coalesce(t2.val1, '')

union all

    

-- 原有ID 开新链

select t1.id, t1.val1, t1.s_date, '30001231' as e_date

    from ll_add t1

    inner join

        ll_all t2

    on t1.id = t2.id

    and t2.e_date = '30001231'

    where t1.dt='${bizdate}'

    and coalesce(t1.val1, '') <> coalesce(t2.val1, '')

    

union all

    

-- 新增ID 开新链

select t1.id, t1.val1, t1.s_date, '30001231' as e_date

    from ll_add t1

    left outer join

        ll_all t2

    on t1.id = t2.id

    and t2.e_date = '30001231'

    where t1.dt='${bizdate}'

        and t2.id is null

) t

;

七 工作流监控

在BASE的“运维中心”中，可以看到每个任务的调度执行情况，并且可以看到整条工作流的执行情况，如果执行失败，可以通过日志可以查看失败原因。“运维中心”详细的功能描述与操作请参考BASE手册。