ProPublica Illinois 如何使用 GNU Make 每天加载 1.4GB 数据

Person standing in front of a giant computer screen with numbers, data

图片来源

Opensource.com

我长期以来避免在我的数据新闻工作中使用 GNU Make，部分原因是文档晦涩难懂，我看不出 Make 这个众多提取-转换-加载 (ETL) 流程之一的工具，如何能帮助我的日常数据报告工作。但是今年，为了构建 The Money Game，我需要每天加载 1.4GB 的伊利诺伊州政治捐款和支出数据，而 ETL 过程耗时数小时，所以我再次尝试了 Make。

现在，相同的过程只需不到 30 分钟。

以下是它的工作原理，但如果您想直接跳到代码，我们已在此处开源。

从根本上说，Make 让你可以声明

文件 X 依赖于应用于文件 Y 的转换
如果文件 X 不存在，则将该转换应用于文件 Y 并创建文件 X

“从文件 Y 开始获取文件 X”模式是数据新闻的日常现实，使用 Make 加载政治捐款和支出数据是一个很好的用例。数据量相当大，通过缓慢的 FTP 服务器访问，格式怪异，完整性问题也恰到好处，足以保持趣味性，并且需要与遗留代码库兼容。为了解决这个问题，我需要从头开始。

概述

我们使用的财务披露数据来自伊利诺伊州选举委员会，但 Illinois Sunshine 项目发布了开源代码（不再可用）来处理 ETL 过程和筹款计算。使用他们的代码，ETL 过程在强大的硬件上大约需要两个小时才能运行，在我们的服务器上则超过五个小时，有时会因我从未完全理解的原因而失败。我需要它工作得更好、更快。

该过程如下所示

下载通过 FTP 从伊利诺伊州选举委员会下载数据文件。
清理使用 Python 清理数据，解决完整性问题，并创建数据文件的干净版本。
加载使用 PostgreSQL 高效但挑剔的“\copy”命令将清理后的数据加载到 PostgreSQL 中。
转换在数据库中转换数据，清理列名，并使用“raw”和“public”PostgreSQL 模式和物化视图（本质上是标准 SQL 视图的持久缓存版本）提供更直接有用的数据形式。

清理步骤必须在任何数据加载到数据库之前进行，这样我们才能利用 PostgreSQL 高效的导入工具。如果单行数据在期望整数的列中包含字符串，则整个操作都会失败。

GNU Make 非常适合这项任务。Make 的模型围绕描述您的 ETL 过程应生成的输出文件以及从一组原始源文件到一组输出文件所需的操作而构建。

与任何 ETL 过程一样，目标是保留您的原始数据，保持操作的原子性，并提供一个简单且可重复的过程，可以一遍又一遍地运行。

让我们检查一下其中的几个步骤

下载和预导入清理

看一下这个代码片段，它可以是一个独立的 Makefile

data/download/%.txt :
aria2c -x5 -q -d data/download --ftp-user="$(ILCAMPAIGNCASH_FTP_USER)" --ftp-passwd="$(ILCAMPAIGNCASH_FTP_PASSWD)" 
ftp://ftp.elections.il.gov/CampDisclDataFiles/$*.txt

data/processed/%.csv : data/download/%.txt
python processors/clean_isboe_tsv.py $< $* > $@

此代码片段首先通过 FTP 下载文件，然后使用 Python 处理它。例如，如果“Expenditures.txt”是我的源数据文件之一，我可以运行 make data/processed/Expenditures.csv 来下载和处理支出数据。

这里有两点需要注意。

首先，我们使用 Aria2 来处理 FTP 任务。早期版本的脚本使用了其他 FTP 客户端，它们要么像糖浆一样慢，要么难以使用。经过一些试验和错误，我发现 Aria2 比 lftp（速度快但挑剔）或古老的 ftp（速度慢且挑剔）做得更好。我还发现了一些咒语，将下载时间从大约一小时缩短到不到 20 分钟。

其次，清理步骤对于此数据集至关重要。它使用了一个简单的基于类的 Python 验证方案，您可以在此处查看。需要注意的重要一点是，虽然 Python 通常很慢，但 Python 3 对于此任务来说足够快。并且只要您仅逐行处理，而不在内存中累积任何对象或执行任何额外的磁盘写入，即使在 ProPublica 集群中的低资源机器上，性能也很好，并且没有任何意外的怪癖。

加载

Make 是围绕文件输入和输出构建的。但是，如果我们的数据既在文件中又在数据库表中，会发生什么情况？以下是我学到的将数据库表集成到 Makefile 中的一些有价值的技巧

每个表/转换一个 SQL 文件：Make 喜欢文件和简单的映射，因此我为每个表的模式定义或任何其他原子表级操作创建了单独的文件。表名与 SQL 文件名匹配，SQL 文件名与源数据文件名匹配。您可以在此处查看它们。

使用退出代码魔法使表看起来像 Make 的文件：来自 DataMade 的 Hannah Cushman 和 Forrest Gregg 在 Twitter 上向我介绍了这个技巧。如果您在表级命令前加上发出适当退出代码的命令，则可以欺骗 Make 将表视为文件。如果表存在，则发出成功代码。如果不存在，则发出错误。

除此之外，加载仅由高效的 PostgreSQL \copy 命令组成。虽然 COPY 命令效率更高，但它与 Amazon RDS 配合不佳。即使 ProPublica 迁移到不同的数据库提供商，我也会继续使用 \copy 以获得可移植性，除非榨取更多性能至关重要。

还有一个最后的难题：加载步骤将数据导入名为 raw 的 PostgreSQL 模式，以便我们可以进一步干净地转换数据。Postgres 模式提供了一种在单个数据库中分割数据的有用方法——无需使用像 raw_contributions 和 clean_contributions 这样的单个命名空间中的表，您可以使用几乎像文件夹一样的结构 raw.contributions 和 public.contributions 来保持事物的简单和清晰。

导入后转换

Illinois Sunshine 代码还重命名列并稍微重塑数据，以提高可用性和性能。列别名对于最终用户很有用，中间表是与遗留代码兼容所必需的。

在这种情况下，加载器将数据导入到名为 raw 的模式中，该模式尽可能接近源数据。

然后通过创建原始表的物化视图来转换数据，这些视图重命名列并处理一些轻微的后处理。这对于我们的目的来说已经足够了，但可以应用更精细的转换，而不会牺牲清晰度或模糊源数据。以下是其中一个视图定义的代码片段

CREATE MATERIALIZED VIEW d2_reports AS
    SELECT
        id as id,
        committeeid as committee_id,
        fileddocid as filed_doc_id,
        begfundsavail as beginning_funds_avail,
        indivcontribi as individual_itemized_contrib,
        indivcontribni as individual_non_itemized_contrib,
        xferini as transfer_in_itemized,
        xferinni as transfer_in_non_itemized,
        # ….
    FROM raw.d2totals
WITH DATA;

这些转换非常简单，但仅仅使用更具可读性的列名就对最终用户来说是一个很大的改进。

与表模式定义一样，每个表都有一个文件，用于描述转换后的视图。我们使用物化视图，再次强调，它本质上是标准 SQL 视图的持久缓存版本，因为存储很便宜，并且它们比传统的 SQL 视图更快。

关于安全性的一点说明

您会注意到我们使用了环境变量，这些变量在命令运行时以内联方式展开。这对于调试很有用，并且有助于提高可移植性。但如果您认为日志文件或终端输出可能被泄露，或者不应该知道这些秘密的人可以访问日志或共享系统，那么这不是一个好主意。为了提高安全性，您可以使用像 PostgreSQL pgconf 文件这样的系统，并删除环境变量引用。

Makefile 的胜利

我之前唯一一次使用 Make 的经历是在 15 年前的一门计算数学课程中，当时它是一个令人沮丧且解释不清的脚注。晦涩难懂的文档、我在学校的糟糕经历以及一个已经可靠的框架让我敬而远之。此外，我的 shell 脚本和 Python Fabric/Invoke 代码在为我正在进行的较小、快速周转的项目构建基于相同原则的可靠数据处理管道方面做得很好。

但是在为这个项目尝试了 Make 之后，我对结果印象深刻。它简洁而富有表现力。它强制执行原子操作，但以非常简单的方式处理部分构建来奖励它们，这在开发期间非常重要，因为您真的不想重复昂贵的操作来测试单个组件。结合 PostgreSQL 快速的导入工具、模式和物化视图，我能够在更短的时间内加载数据。同样重要的是，新流程的性能对系统资源变化不太敏感。

如果您渴望开始使用 Make，这里有一些额外的资源