ETL简介及开发流程概述

发表于 更新于 分类于

l57o5cb4lzuzn7e1.jpg

[TOC]

前言

ETL是BI项目最重要的一个环节

ETL将企业中的分散、零乱、标准不统一的数据整合到一起，为企业的决策提供分析依据。

ETL通常情况下会花掉整个项目的1/3的时间，ETL设计的好坏直接关接到BI项目的成败。

1566750986512

名词简介

数据仓库

英文全称为Data Warehouse，简称为DW。

数据仓库之父比尔·恩门（Bill Inmon）在1991年出版的《Building the Data Warehouse》（《建立数据仓库》）一书中所提出的定义被广泛接受——数据仓库（Data Warehouse）是一个面向主题的（Subject Oriented）、集成的（Integrated）、相对稳定的（Non-Volatile）、反映历史变化（Time Variant）的数据集合，用于支持管理决策(Decision Making Support)。

换句话说，数据仓库是一个以实现特定分析决策为目的，能够随时间的变化，稳定地、持续地为这个需求提供所需要的数据的集合。

业务库与数据仓库的不同：

  • 业务库通常是面向事务来设计的，而数据仓库则是面向主题来设计的。

  • 业务库尽量避免冗余，而数据仓库则会有意引入冗余，以便于快速分析。

  • 业务库为捕获数据而设计，数据仓库是为分析数据而设计。

操作型数据存储

英文全称为Operational Data Store，简称ODS。

是数据仓库体系结构中的一个可选部分，也被称为贴源层。

ODS具备数据仓库的部分特征和OLTP( On-Line Transaction Processing 联机事务处理过程， 前台接收的用户数据可以立即传送到计算中心进行处理，并在很短的时间内给出处理结果) 系统的部分特征。

它是“面向主题的、集成的、当前或接近当前的、不断变化的”数据。

ODS的特征如下：

  • 支持大量并发用户定期添加和修改数据。
  • 反映随时变化的单位状态，但不保存其历史记录。
  • 包含大量数据。
  • 可以进行优化以对事务活动做出响应。
  • ……

ODS的作用如下：

  1. 在业务系统和数据仓库之间形成一个隔离层

    降低数据来源复杂性

  2. 转移一部分业务系统细节查询的功能

    降低业务系统查询压力

  3. 完成数据仓库中不能完成的一些功能

    数据仓库中存储的数据粒度是根据需要而确定的，有时DW并不存储过于细节的数据，但ODS可以。

img

数据集市

数据集市（Data Mart），也叫数据市场，为满足特定的部门或者用户需求，按照多维的方式进行存储，包括定义维度、需要计算的指标、维度的层次等，生成面向决策分析需求的数据立方体。

数据集市，是企业级数据仓库的一个子集，主要面向部门级业务，只面向某个特定的主题。

数据集市数据来源于企业范围的数据库、专业的数据仓库。

1567419696532

img

ETL简介

抽取(Extract)

一般抽取过程需要连接到不同的数据源，在业务库与ODS之间建立同步机制，将不同数据源的数据统一放至ODS中，共清洗转换使用。

转换(Transform)

任何对数据的处理过程都是转换。通常包括且不限于以下操作：

  • 移动数据
  • 根据规则验证数据
  • 数据内容和数据结构的修改
  • 将多个数据源的数据集成
  • 根据处理后的数据计算派生值和聚合值

此部分最为繁琐，通常会占用ETL开发时间的2/3左右

加载(Load)

将清洗、转换完的数据加载到数据仓库中。

ETL开发流程

graph LR;
    业务库-- "抽取[E]、清洗" -->ODS;
    ODS-- "转换[T]、加载[L]" -->DW;
    DW-- "转换[T]、加载[L]" -->DW;
    DW-- "转换[T]、加载[L]" -->DM;
    DM-- "转换[T]、加载[L]" -->DM;
    DM-->BI前端工具;

抽取数据源中的数据

对于不同数据源

通常我们需要将多个不同的数据源中的数据整合到统一的BI数据仓库中。

  1. ODBC

    可以通过ODBC的方式创建DBLink——如SQL Server和Oracle之间。如果不能建立数据库链接，可以有两种方式完成，一种是通过工具将源数据导出成.txt或者是.xls文件，然后再将这些源系统文件导入到ODS中。

  2. 使用程序接口

    常用的ETL工具为**Spoon(Kettle)**。使用Spoon定义输入输出，两端可为文件、数据库、流等。文件包括规范的日志文件以及Excel等。

    以及一些其他带有类似功能的数据库工具，也可以完成此类操作。

对于相同数据源

对于相同的数据源，如同在Oracle中，可在DBMS自带的dblink基础上访问自身数据，使用Oracle procedure（存储过程）通过进行抽取，存放至DW中。亦或者是类似于SQL Server间可使用SSIS服务等等。

选择数据更新方式

  • 全量加载：全表删除后再进行数据加载的方式。

    如果每次更新的数据量占总量1/4以上，则可以考虑全量更新。

  • 增量加载：目标表仅更新源表变化的数据。

    • 数据量巨大的目标表。
    • 源表变化数据比较规律，例如按时间序列增长或减少。
    • 源表变化数据相对数据总量较小。
    • 目标表需要记录过期信息或者冗余信息
    • 业务系统能直接提供增量（delta）数据

一般情况下，业务系统会记录业务发生的时间。

我们可以用来做增量的标志,每次抽取之前首先判断ODS中记录最大的时间，然后根据这个时间去业务系统取大于这个时间所有的记录。利用业务系统的时间戳，一般情况下，业务系统没有或者部分有时间戳。

处理不合格数据

首先加载来自ODS中的数据，在加载的过程中，通常有三类不合格数据需要我们去处理：

在加载的过程中，通常有三类不合格数据需要我们去处理：

  1. 不完整数据

    包括客户名称缺失、联系方式确实、地址缺失等。通常需要将这类数据过滤出来，写入Excel向数据产生者确认补全。

  2. 错误数据

    包括字符集错误、日期格式错误、长度过长等等。这类问题会导致ETL流程执行失败，需要在ETL相关操作中添加操作日志，记录这些错误。事先也需要通过写SQL的方式去排查问题数据，交给业务部门处理，保证数据可靠。

  3. 重复数据

    如相同维度下出现多条数据，如果重复量过多则需要制定过滤规则，并需要向业务部门确认。

对数据进行整合计算

这块需要更具具体的BI分析规则来定。通常包括以下部分：

  1. 数据整理

    将不同数据规则的数据统一成相同格式，便于聚合操作。

  2. 数据聚合

    ODS中的数据粒度过细，需要将数据以数据仓库的规则进行聚合，以便于分析使用。

    以Oracle为例，通常转换步骤使用存储过程(procedure)来完成。

    首先使用存储过程将ODS中的数据加载到DW中，此时的数据字段较为冗余，相对的DW表数据符合某种业务主题，比如“用户的录单信息”，需要整合多张ODS中的表，存放到同一张DW表中，便于计算。此处也会过滤一些不必要的字段，以及添加一些冗余字段等。

  3. 数据计算

    整合完成后可以按照BI需求，对数据进行分组计算，加载到DM层中，以供BI工具直接调用展示。如根据部门BI业务规则，计算KPI数据，通常会将事实表与维度表相关联，形成符合业务模型的多维数据集。

一个简单的流程示例

使用Kettle将MySQL的数据加载入Oracle

1567443246869

对数据进行整合计算

示例：

ETL_DM_SHOPPING_INFO.prc

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
CREATE OR REPLACE PROCEDURE ETL_DM_SHOPPING_INFO(START_TIME   DATE,
                                                 END_TIME     DATE,
                                                 V_ETL_NUMBER INT,
                                                 V_FORCE_FLAG INT) IS
  V_SQL        VARCHAR2(100);
  V_INSERT     INT := 0;
  V_UPDATE     INT := 0;
  V_DELETE     INT := 0;
  V_DATE       DATE := START_TIME;
  V_TABLE_NAME VARCHAR2(100) := 'dm_shopping_info';
BEGIN

  FOR REC IN (SELECT CAL_NUMBER, CAL_DATE
                FROM DIM_CALENDAR
               WHERE CAL_DATE >= START_TIME - 1
                 AND CAL_DATE < END_TIME
               ORDER BY CAL_NUMBER) LOOP
    DELETE FROM DM_SHOPPING_INFO WHERE CAL_NUMBER = REC.CAL_NUMBER;
    COMMIT;
    -- 16-20
    INSERT INTO DM_SHOPPING_INFO
      (CAL_NUMBER, AGE_RANGE, QUANTITY)
      SELECT REC.CAL_NUMBER, '16-20岁', nvl(SUM(T.QUANTITY),0)
        FROM DW_SHOPPING_INFO T
       WHERE TO_CHAR(T.ADD_TIME, 'yyyymmdd') =
             TO_CHAR(REC.CAL_DATE, 'yyyymmdd')
         AND T.AGE >= 16
         AND T.AGE <= 20;
    -- 21-25
    INSERT INTO DM_SHOPPING_INFO
      (CAL_NUMBER, AGE_RANGE, QUANTITY)
      SELECT REC.CAL_NUMBER, '21-25岁', nvl(SUM(T.QUANTITY),0)
        FROM DW_SHOPPING_INFO T
       WHERE TO_CHAR(T.ADD_TIME, 'yyyymmdd') =
             TO_CHAR(REC.CAL_DATE, 'yyyymmdd')
         AND T.AGE >= 21
         AND T.AGE <= 25;
    -- 26-30
    INSERT INTO DM_SHOPPING_INFO
      (CAL_NUMBER, AGE_RANGE, QUANTITY)
      SELECT REC.CAL_NUMBER, '26-30岁', nvl(SUM(T.QUANTITY),0)
        FROM DW_SHOPPING_INFO T
       WHERE TO_CHAR(T.ADD_TIME, 'yyyymmdd') =
             TO_CHAR(REC.CAL_DATE, 'yyyymmdd')
         AND T.AGE >= 26
         AND T.AGE <= 30;
    COMMIT;
  END LOOP;

END ETL_DM_SHOPPING_INFO;

加载展示

加载的展现形式有很多种，比如使用QlikView作为前端报表工具。

1567580138912