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，这里有一些额外的资源