如何用Python和SQL搭建ETL数据管道

初学者的数据处理入门指南

这张照片来自 Unsplash

最近，我开始了一段使用Python和SQL学习数据工程的旅程。作为一名来自不同行业的学习者，我通过上课程、看教程和读书来自学。但是，和大多数技术技能一样，真正的学习是当你自己动手做一些事情的时候。于是，我决定用Python和SQL创建我的第一个ETL数据处理流水线。

由于我有运动生理学的背景，我自然而然地倾向于为我的第一个数据管道选择一个与健身相关的项目。我希望能够处理那些我理解并关心的数据。尽管像Kaggle这样的平台上提供了大量开源数据集，但我认为使用自己的健身数据更有价值。因此，我决定使用我的Strava数据来创建一个健身数据库和ETL流程。

在这篇文章中，我将一步步介绍我是如何构建这个项目的，使其对初学者和更有经验的开发者都是易于上手的。我还会分享我在过程中遇到的挑战及解决办法。

对于此项目，获取我的 Strava 数据最简单的方式是将我的账户与 Intervals.icu 关联。如果你想跟着操作，以下是一个逐步指南来下载你的数据：

1. 在intervals.icu上创建或者注册一个账户。
2. 进入设置页面，然后将您的账户与Strava或其他您常用的健身软件同步。
3. 转到活动页面，把视图从“日历”改成“列表”。
4. 点击列标签页，选择您感兴趣的数据列。
5. 转到编辑页面，然后下载CSV文件。

从Intervals.icu下载数据。

在这个数据工程项目中，我下载了从2024年1月1日到2024年10月8日期间的所有记录。我选择了以下列：

• “类别”，“日期”，“距离 (公里)”，“活动时间”，“名称”
• “平均心率 (bpm)”，“强度”，“平均海拔 (米)”，“平均心率占比”
• “总时间”，“HRRc”，“卡路里”
• “最高海拔 (米)”，“最高心率 (bpm)”，“最大心率占比”，“配速 (分钟/公里)”

下载的文件名将会类似于 i123456_activities ，其中“i123456”代表您的唯一运动者ID。我们将它作为数据库的主键。CSV文件是我们构建ETL（提取、转换、加载）管道的起点。

下载CSV文件之后，下一步是设置工作环境并创建数据库。我使用VS Code来进行项目设置，并通过以下命令来创建Python虚拟环境：

python -m venv .venv # 创建一个虚拟环境名为.venv

然后，我安装了必要的库，包括用于数据处理的 Pandas 和 SQLAlchemy，后者是一个允许我们用 Python 与 SQL 数据库进行交互的对象关系映射（ORM）工具。

    pip install pandas sqlalchemy

我使用 SQL Server Management Studio (SSMS) 设置了一个本地的 SQL Server 数据库，并创建了一个表结构。你可以随便给数据库和表命名，但要确保在 SQLAlchemy 代码中正确配置了这些名字。

在我的Fitness表里，我将AthleteId和ActivityId用作复合主键，因为每个运动员和活动都有唯一的标识符。另外，由于并非每位运动员都会跟踪心率，我将心率相关的变量（如平均心率、最大心率等）设置为NULL，而其他列则设置为NOT NULL。

要复制我的数据库结构的方式，你可以用下面的SQL代码：

    创建数据库 [myFitnessApp]  

    使用数据库 [myFitnessApp]  
    创建表 FitnessData (  
        AthleteId VARCHAR(50) NOT NULL,  
        ActivityId BIGINT NOT NULL,  
        Type VARCHAR(50) NOT NULL,  
        Date DATETIME NOT NULL,  
        Distance FLOAT NOT NULL,  
        MovingTime FLOAT NOT NOT NULL,  
        Name VARCHAR(255) NOT NULL,  
        AvgHR INT NULL,  
        IntensityPercent INT NULL,  
        AvgAltitude FLOAT NOT NULL,  
        AvgHRPercent INT NULL,  
        ElapsedTime FLOAT NOT NULL,  
        HRRc INT NULL,  
        kcal INT NOT NULL,  
        MaxAltitude FLOAT NOT NULL,  
        MaxHR INT NULL,  
        MaxHRPercent INT NULL,  
        Pace VARCHAR(10) NOT NULL,  
        主键约束 (AthleteId, ActivityId)  -- 复合主键  
    );

简单讲，ETL 代表 提取、转换、加载。这个项目的主要任务就是：提取、转换、加载。

• 提取：读取包含健身活动的CSV数据的文件。
• 转换：清理、重新格式化，并调整列以匹配数据库结构。
• 加载：将转换后的数据插入相应的SQL Server数据库中。

为了保持代码模块化和便于调试，我为每个步骤创建了单独的函数——提取、转换 以及 加载，然后将它们组合成一个总的 ETL 函数。

摘取

在提取步骤中，我创建了一个将CSV文件读取为pandas的DataFrame的函数，这个函数用于提取步骤。

    # 读取CSV文件  
    def extract_data(file_path):  
        df = pd.read_csv(file_path)  # pd: pandas库  
        print(f"成功从 {file_path} 提取数据。")  
        return df

转换

在转换阶段，我们处理了三个关键任务。

1. 配速格式化: convert_pace() 函数将配速转换为 min/km 格式。
2. 单位转换: 时间单位由秒转换成分钟，距离单位由米转换成千米。
3. 表结构对齐: 调整列名以匹配 SQL Server 表，确保数据顺利整合。

转换函数是这样的：

    # 将速度转换为配速
    def convert_pace(speed_m_per_s):  
        """  
        将米/秒的速度转换为每公里的配速（分钟:秒）。  

        参数说明:  
        speed_m_per_s (float): 米/秒的速度。  

        返回:  
        str: '分钟:秒/km' 格式的配速。  
        """  
        # 将速度从米/秒转换为每分钟千米
        speed_km_per_min = speed_m_per_s * 60 / 1000  

        # 将速度（千米/分钟）转换为每千米所需分钟数
        minutes_per_km = 1 / speed_km_per_min  

        # 获取整分钟数
        minutes = int(minutes_per_km)  

        # 获取剩余的秒数（小数部分转换为秒）
        seconds = (minutes_per_km - minutes) * 60  
        seconds = round(seconds)  

        # 处理秒数达到60的情况
        if seconds == 60:  
            minutes += 1  
            seconds = 0  

        # 格式化输出为 '分钟:秒/km'
        return f"{minutes}:{seconds:02d}/km"  

    # 数据转换步骤
    def transform_data(df, athlete_id):  
        # 将时间从秒转换为分钟
        df['Moving Time'] = df['Moving Time'] / 60  
        df['Elapsed time'] = df['Elapsed time'] / 60  

        # 将距离从米转换为千米
        df['Distance'] = df['Distance'] / 1000  

        # 使用新函数将配速格式化
        df['Pace'] = df['Pace'].apply(lambda x: convert_pace(x))  

        # 四舍五入部分列的值
        df['Intensity'] = df['Intensity'].round(1)  
        df['Avg Altitude'] = df['Avg Altitude'].round(1)  
        df['Avg HR%'] = df['Avg HR%'].round(1)  
        df['Max HR%'] = df['Max HR%'].round(1)  
        df['Max Altitude'] = df['Max Altitude'].round(1)  
        df['Pace'] = df['Pace'].round(2)  
        df['Distance'] = df['Distance'].round(2)  
        df['Moving Time'] = df['Moving Time'].round(2)  
        df['Elapsed time'] = df['Elapsed time'].round(2)  

        # 添加athlete_id列
        df['athlete_id'] = athlete_id  
        print(f"已转换数据用于运动员 {athlete_id}。")  

        # 重命名列以匹配 SQL Server 表的格式
        df.rename(columns={  
            'athlete_id': 'AthleteId',  
            'id': 'ActivityId',  
            'Intensity': 'IntensityPercent',  
            'Moving Time': 'MovingTime',  
            'Elapsed time': 'ElapsedTime',  
            'Avg Altitude': 'AvgAltitude',  
            'Avg HR%': 'AvgHRPercent',  
            'Max HR%': 'MaxHRPercent',  
            'Max Altitude': 'MaxAltitude',  
            'Max HR': '最高心率',  
            'HRRc': 'HRRc',  
            'Avg HR': '平均心率'  
        }, inplace=True)  

        return df

加载

我使用了 SQLAlchemy 的 to_sql() 函数，将转换后的数据行从 DataFrame 插入到了 SQL Server 数据表中。

    # 加载数据到SQL  
    def load_data_to_sql(df, engine):  
        try:  
            df.to_sql('FitnessData', con=engine, if_exists='append', index=False)  
            # 打印已添加活动的ID  
            added_ids = df['ActivityId'].tolist()  
            print(f"以下ID的活动已添加: {added_ids}")  
        except SQLAlchemyError as e:  
            print(f"插入数据时出错: {e}")

此功能尝试来添加数据到FitnessData表中。它还会输出新活动的ID，这样有助于追踪成功的插入。如果插入错误发生时，该错误将被捕获并记录下来。

建立SQL数据库连接

在加载数据之前，首先需要与SQL Server建立连接。配置和连接代码确保使用正确的服务器、数据库和驱动程序名称。

在 config.py 文件里，请将 YOUR_SERVER_NAME、YOUR_DATABASE_NAME 和 YOUR_DRIVER_NAME 替换成您设置中的实际名称。驱动程序名称一般会像 "ODBC Driver 17 for SQL Server" 这样格式。

    # config.py  
    SERVER_NAME = "YOUR_SERVER_NAME"  
    DATABASE_NAME = "YOUR_DATABASE_NAME"  # 正确的数据库名  
    DATA_FOLDER = "data/"  # 数据文件夹

在 db_connection.py 文件中，通过 create_engine() 函数创建连接字符串并初始化一个 SQLAlchemy 引擎。

    # db_connection.py

    from sqlalchemy import create_engine
    from config import SERVER_NAME, DATABASE_NAME
    # 连接到数据库的函数
    def connect_to_db():
        conn_string = f'mssql+pyodbc://{SERVER_NAME}/{DATABASE_NAME}?driver=[YOUR_DRIVER_NAME]'  # 连接字符串
        engine = create_engine(conn_string)  # 数据库引擎
        return engine

ETL 数据管道功能

在定义了提取、转换和加载步骤之后，我将它们整合成一个完整的ETL管道功能。此管道从data/文件夹中提取CSV文件，应用ETL过程，并将结果加载到SQL Server数据库中。此外，它从CSV文件名中提取运动员ID并将其分配给athlete_id列。

    # 主ETL函数  
    def etl_pipeline():  
        engine = connect_to_db()  

        for file_name in os.listdir(DATA_FOLDER):  
            if file_name.endswith("_activities.csv") and len(file_name) == 22:  # 检查是否为6位数字的格式  
                # 从文件名中提取出athlete_id  
                athlete_id = file_name.split("_activities")[0]  # 这将提取出'i123456'部分  

                file_path = os.path.join(DATA_FOLDER, file_name)  

                # 提取数据  
                df = extract_data(file_path)  

                # 转换数据  
                df_transformed = transform_data(df, athlete_id)  

                # 加载数据  
                load_data_to_sql(df_transformed, engine)  

    # 运行ETL流程  
    if __name__ == "__main__":  
        etl_pipeline()

我的GitHub上的仓库里有这个项目的完整代码和资料。要运行流程，请按照以下步骤操作：

1. 克隆代码库。
2. 将您的CSV文件放到data/文件夹里。
3. 运行etl_pipeline()脚本并通过SQL Server Management Studio (SSMS)来更新数据库。

Github上的项目: https://github.com/PanosChatzi/FitnessDatabase

在运行完ETL管道后，你可以通过查询SQL Server里的_FitnessData_表来检查结果。

    使用 [myFitnessApp]  
    SELECT DB_NAME() AS 当前数据库名称  

    SELECT * FROM [dbo].[FitnessData]  
    ORDER BY 日期 DESC

Voila! 一切顺利的话，我们会看到数据已经按日期排序了。如果在控制台上有错误出现，这通常是因为数据类型的问题，你可能需要调整约束条件或者用 Python 重新整理数据。

在 SQL 服务器中查看数据

回首我旅程的起点，每当我听到有关自动化管道和使用如SQLAlchemy这样的库时，我都会想象一个数据大师在使用高级工具和复杂的代码。结果表明，通过打好坚实的基础并练习写代码而不是念咒语，任何人都可以开始构建数据工程任务。

这个项目只是一个简单的例子，但我希望它清楚地展示了如何使用Python构建ETL数据管道并将其连接到本地SQL服务器。这个项目有很多可能的发展方向——我已经可以想象一些，还有很多将在我的学习过程中逐渐浮现。我非常期待看到这段旅程会如何发展，以及几年后当我回顾这一切开始的起点时，我的视角会有怎样的变化。

与此同时，我鼓励你去看看我在GitHub上的代码。无论你是热爱健身并想简化数据分析的人，还是想为自己的第一个项目找灵感的数据工程师，我希望我的工作能激励你勇敢地迈出第一步，开启自己的旅程。