【数据仓库】— 5分钟浅谈 ETL（适合新手小白）

大家好，我是摇光~

在现在很多数据仓库和数据中台的系统中，都会涉及到 ETL，那么ETL到底是什么呢，我们来看看吧~

本文前面讲的是 ETL 的概念，后面会用一个实际案例来讲解 ETL。

一、ETL的基本概念

ETL是数据抽取（Extract）、转换（Transform）和加载（Load）的简称。

从字面意思来理解，其实ETL就是将业务系统的数据经过抽取、清洗转换之后加载到数据仓库或数据中台中，目的是将企业中的分散、零乱、标准不统一的数据整合到一起，为企业的决策提供分析依据。

抽取、转换、加载说来简单，但是实际涉及到的事情很多，我们接下来看看 ETL 的整个过程。

二、ETL的过程

虽然 ETL 是抽取、转换、加载 三个步骤，但是由于涉及东西比较多，所以我们可以将步骤细分：

以下是ETL步骤：

• 1、数据抽取：确定数据源，定义数据接口，选择数据抽取方法（主动抽取或由源系统推送）。
• 2、数据清洗：处理不完整数据、错误数据、重复数据等，确保数据的准确性和一致性。
• 3、数据转换：进行空值处理、数据标准统一、数据拆分、数据验证、数据替换和数据关联等操作。
• 4、规则检查：根据业务需求进行数据质量和业务规则的校验。
• 5、数据加载：将数据缓冲区的数据加载到目标数据库或数据仓库中，可能是全量加载或增量加载。

上面的步骤，很多都是抽象的，接下来用一个实际案例来理解。

三、ETL的实际案例

案例背景：

某公司使用多个不同的系统来存储不同部门的数据：

• 如销售数据存储在CRM系统中，财务数据存储在ERP系统中，人力资源数据存储在HR系统中。
• 为了更好地进行报告和分析，该公司决定将这些数据集成到一个统一的数据仓库中。

ETL过程：

1、数据抽取：（这里可以看是自己主动抽取数据，还是对方系统推送数据给你）

• 从CRM系统中抽取销售数据。
• 从ERP系统中抽取财务数据。
• 从HR系统中抽取人力资源数据。

以下是一个示例Shell脚本，它使用sqlplus工具从源Oracle数据库（HR系统）中抽取数据，并将数据加载到目标Oracle数据仓库系统中。

请注意，这个脚本是一个基本示例，你可能需要根据实际情况进行调整。

#!/bin/bash

# 源数据库连接信息
SOURCE_DB_HOST="hr_system_host"
SOURCE_DB_PORT="1521"
SOURCE_DB_SID="hr_system_sid"
SOURCE_DB_USER="source_db_user"
SOURCE_DB_PASS="source_db_password"

# 目标数据库连接信息
TARGET_DB_HOST="data_warehouse_host"
TARGET_DB_PORT="1521"
TARGET_DB_SID="data_warehouse_sid"
TARGET_DB_USER="target_db_user"
TARGET_DB_PASS="target_db_password"

# 抽取数据的SQL查询
EXTRACT_QUERY="SELECT id, first_name, last_name, email, department_id, job_title FROM employees;"

# 临时文件用于存储从源数据库抽取的数据
TEMP_FILE="/tmp/employees_data.csv"

# 创建CSV文件的头部（可选，取决于目标表的结构）
echo "id,first_name,last_name,email,department_id,job_title" > "$TEMP_FILE"

# 从源数据库抽取数据并追加到CSV文件中（使用sqlplus的spool功能）
sqlplus -s "${SOURCE_DB_USER}/${SOURCE_DB_PASS}@${SOURCE_DB_HOST}:${SOURCE_DB_PORT}/${SOURCE_DB_SID}" <<EOF
SET PAGESIZE 0 FEEDBACK OFF VERIFY OFF HEADING OFF ECHO OFF
SPOOL $TEMP_FILE APPEND
$EXTRACT_QUERY
SPOOL OFF
EXIT;
EOF

# 加载数据到目标数据库（使用SQL*Loader或INSERT语句，这里使用INSERT语句作为示例）
# 注意：在实际生产环境中，使用SQL*Loader通常更高效且更安全

# 为了使用INSERT语句，我们需要确保目标表结构与源查询结果相匹配
# 并且可能需要处理数据中的特殊字符（如单引号）以避免SQL注入风险
# 下面的示例脚本直接通过sqlplus执行INSERT语句，但这种方法在处理大量数据时可能效率不高
# 且存在SQL注入风险，因此仅作为示例提供

# 加载数据（使用INSERT语句的示例，不推荐用于生产环境）
while IFS=, read -r id first_name last_name email department_id job_title; do
    # 对特殊字符进行转义处理（这里仅对单引号进行简单处理，实际情况可能需要更复杂的逻辑）
    first_name=$(echo "$first_name" | sed "s/'/''/g")
    last_name=$(echo "$last_name" | sed "s/'/''/g")
    email=$(echo "$email" | sed "s/'/''/g")
    job_title=$(echo "$job_title" | sed "s/'/''/g")

    # 执行INSERT语句
    sqlplus -s "${TARGET_DB_USER}/${TARGET_DB_PASS}@${TARGET_DB_HOST}:${TARGET_DB_PORT}/${TARGET_DB_SID}" <<EOF
INSERT INTO employees (id, first_name, last_name, email, department_id, job_title)
VALUES ('$id', '$first_name', '$last_name', '$email', $department_id, '$job_title');
COMMIT;
EXIT;
EOF
done < "$TEMP_FILE"

# 清理临时文件
rm -f "$TEMP_FILE"

# 提示用户操作完成
echo "人员信息已成功从HR系统抽取并加载到数据仓库系统。"

注意：这个步骤的难点就是，每个系统可能有不同的数据格式和结构，因此需要编写不同的提取程序或查询来提取数据。

2、数据清洗

数据清理这个步骤的难点在于：需要对数据进行观察，处理不完整数据、错误数据、重复数据等问题。

我列举几个常见的例子：

• 信息补全：比如人员信息表的地址不完全，可能需要人员证件表的地址信息进行数据填充
• 格式错误：比如日期格式应该为：YYYYMMDD，结果真实数据是：YYYY/MM/DD，这种就需要统一格式
• 重复数据：比如一张表中你只需要10个字段，但是源表有50个字段，你取的10个字段中，就有重复数据，这种就需要去重

3、数据转换：

数据转换和数据清洗差不多，不过也有区分，数据转换主要做空值处理、数据标准统一、数据拆分、数据验证、数据替换和数据关联等操作。

我列举几个常见的例子：

• 空值处理：比如个人信息表中，行业这个选项，很多人不选，但是系统可能为了保持数据完整性，就用个“其他”来表示。
• 数据替换：比如性别，在系统中就不会存男女，而是会存 F/M 来表示

转换过程可以通过使用ETL工具（如Kettle）或编写自定义脚本来完成。

以下是一个非常基础的示例，实际的ETL过程可能涉及更复杂的转换逻辑和错误处理。

#!/bin/bash

# 输入和输出文件
INPUT_FILE="input_data.csv"
OUTPUT_FILE="transformed_data.csv"

# 创建输出文件，并写入头部（如果有）
echo "transformed_id,first_name,last_name,email,salary_usd" > "$OUTPUT_FILE"

# 处理每一行数据
while IFS=, read -r id raw_first_name raw_last_name raw_email salary_cents; do
    # 字段格式化：将salary_cents转换为美元（假设100 cents = 1 USD）
    salary_usd=$(echo "$salary_cents / 100" | bc)
    
    # 数据清洗：将姓名中的空格替换为下划线（仅为示例）
    first_name=$(echo "$raw_first_name" | tr ' ' '_')
    last_name=$(echo "$raw_last_name" | tr ' ' '_')
    
    # 验证email格式（这里使用简单的正则表达式，实际应用中可能需要更复杂的验证）
    if [[ "$raw_email" =~ ^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$ ]]; then
        email="$raw_email"
    else
        # 如果email格式不正确，可以设置为默认值或进行其他处理
        echo "Invalid email format for ID $id: $raw_email" >&2
        email="unknown@$domain.com" # 假设有一个默认的域名
    fi
    
    # 将转换后的数据写入输出文件
    echo "$id,$first_name,$last_name,$email,$salary_usd" >> "$OUTPUT_FILE"
done < "$INPUT_FILE"

# 提示用户操作完成
echo "Data transformation completed. Output written to $OUTPUT_FILE."

4、数据检查

这一步其实就是对数据清洗和转换后的数据进行进一步的检查

可以进行省略，根据业务所需，如果业务要求数据表的完整性必须高达 80%，就需要计算数据的完整性。

5、数据加载：

数据加载主要就是将处理后的数据加载到目标数据库

其第一步已经包含了数据加载的代码，可以返回看看。

四、ETL的挑战与解决方案

尽管ETL技术在数据整合中发挥着重要作用，但在实施过程中也可能会面临一些挑战：

数据源的多样性和复杂性：

• 不同的业务系统可能使用不同的数据库和数据格式，这增加了数据抽取和转换的难度。
• 解决方案是建立统一的数据源管理系统，并对数据源进行标准化处理。

数据转换规则的复杂性：

• 不同的业务系统可能使用不同的业务规则和数据模型，这需要在ETL过程中进行复杂的数据转换。
• 解决方案是制定清晰的数据转换规则，并使用ETL工具或自定义脚本来实现这些规则。

数据加载的效率：

• 当数据量非常大时，数据加载可能会成为瓶颈。
• 解决方案是采用增量加载和并行处理等技术手段来提高数据加载的效率。