Hotdry.
归档
systems

WARN法案通知聚合引擎：数据采集、清洗与PostgreSQL全文检索的工程化实践

解析如何构建覆盖全美50州的WARN法案通知聚合搜索引擎，从爬虫设计、ETL清洗到PostgreSQL全文检索的完整工程化pipeline。

在美国《工人调整和再培训通知法案》（WARN Act）的框架下，雇主在大规模裁员时必须提前 60 天向州政府提交通知。这些通知原本散布于各州劳动部门的不同平台，格式涵盖 PDF 文档、Excel 表格、HTML 页面乃至定制化搜索界面，数据异构性极高。WARN Firehose 作为新兴的垂直数据聚合项目，通过自动化爬虫每日抓取全美 50 个州的 WARN 通知，经清洗后存入 PostgreSQL 并构建全文检索引擎，目前，已收录超过 10.9 万条通知，影响员工逾 1290 万人，数据可追溯至 1998 年。本文将从工程视角解析这一数据聚合搜索系统的核心设计与实现路径。

数据采集：50 州异构源的统一抓取

WARN 法案通知的采集面临的首要挑战是数据源的高度异构性。美国各州负责发布 WARN 通知的机构各不相同，例如加州的 Employment Development Department、纽约州的 Department of Labor、德州的 Workforce Commission 等，均采用独立的数据发布机制。部分州提供结构化的数据下载（如 CSV 或 Excel 格式），而另一些州仅在网页上以表格形式展示，甚至仅提供 PDF 扫描件。更复杂的情况在于，同一州的不同年份可能采用不同的发布格式，这要求爬虫层具备极强的适应性。

工程实践中通常采用 “一种爬虫对应一个州” 的模块化设计。每州爬虫独立运行，通过统一的输出 Schema 保证下游处理的一致性。标准化的 JSON 结构应包含以下核心字段：state（州代码）、notice_id（州级通知编号）、employer（雇主名称）、city（城市）、county（县）、address（地址）、naics_code（北美行业分类系统代码）、notice_date（通知日期）、layoff_start_date（预计裁员起始日期）、employees_affected（受影响员工数）、raw_text（原始通知文本内容）、source_url（数据源链接）、created_at 与 updated_at（时间戳）。这种 Schema 设计既保留了原始数据的完整性，又为后续的结构化处理奠定了基础。

爬虫的调度策略通常采用每日增量模式，利用各州通知编号或内容哈希实现幂等性 —— 即同一通知不会被重复抓取。对于提供批量下载的州，可直接获取完整数据集后进行增量比对；对于仅支持网页展示的州，则需解析 HTML 表格或使用 OCR 处理 PDF 文档。调度层可借助 Cron、Airflow 或 Temporal 等任务编排工具实现，WARN Firehose 本身即采用每日自动化的采集管道确保数据实时性。

ETL 清洗：数据标准化与去重

采集到的原始数据进入 ETL 管道后，首先进行 “原始着陆”—— 将未经加工的数据以 JSON 或 CSV 格式存入对象存储（如 S3）或数据库的 raw_notices 表，以便后续追溯与调试。这一层的核心价值在于保留数据的原始面貌，当下游 Schema 需要调整或历史数据需要重新处理时，可直接从原始层拉取数据进行二次清洗。

清洗阶段的核心任务包括三项：第一，日期字段的解析与标准化，各州日期格式可能为 "MM/DD/YYYY"、"DD-Mon-YYYY" 或 "YYYY-MM-DD"，需统一转换为 ISO 8601 格式并存储为 date 类型；第二，雇主名称的规范化，例如 "Google LLC"、"Google Inc."、"Alphabet Inc. - Google" 等变体应统一归一，这通常需要结合正则匹配与人工维护的别名表；第三，地址组件的拆解，将完整地址字符串解析为 street、city、county、state、zipcode 等独立字段，为后续的地理维度分析提供支撑。

去重是清洗管道的关键环节。由于各州数据可能存在交叉引用（如某条通知同时出现在州劳动部门和地方 Workforce Board 的页面），需基于 (state, state_notice_id) 组合或关键字段哈希进行去重。对于同一雇主在相邻时间段内提交的多条通知，应根据 notice_date 和 layoff_start_date 进行关联分析，识别是否为同一裁员事件的分批通知。经过清洗后的数据最终以 Upsert 方式写入主表 warn_notice，利用数据库的唯一约束确保不会产生重复记录。

PostgreSQL 全文检索：搜索层的工程实现

完成数据入库后，搜索能力的构建是整个系统的核心价值所在。PostgreSQL 自 9.6 版本起提供的内置全文检索（Full-Text Search）功能足以支撑中等规模的垂直搜索场景，无需引入 Elasticsearch 等外部搜索引擎即可实现高效的文本匹配与相关性排序。

Tsvector 与 GIN 索引

全文检索的核心数据类型是 tsvector（词向量）与 tsquery（查询向量）。对于 warn_notice 表，可将 employer、city、county、naics_code、raw_text 等文本字段合并为一个 tsvector 列，并通过 GIN 索引加速查询。考虑到不同字段的搜索权重差异，工程实践中通常采用 setweight 函数为各字段分配优先级 ——employer（雇主名称）赋予最高权重 A，city 与 county 赋予权重 B，naics_code 赋予权重 C，raw_text（原始通知正文）赋予权重 D。这种权重设计确保当用户搜索 "Google" 时，包含 "Google" 雇主名称的记录会优先于仅在正文中提及 "Google" 的记录。

PostgreSQL 12 及以上版本支持 generated column（生成列）特性，可将 tsvector 列定义为存储式生成列，由数据库自动维护更新。示例 Schema 如下：

ALTER TABLE warn_notice
ADD COLUMN search_vector tsvector GENERATED ALWAYS AS (
  setweight(to_tsvector('english', coalesce(employer, '')), 'A') ||
  setweight(to_tsvector('english', coalesce(city, '')), 'B') ||
  setweight(to_tsvector('english', coalesce(county, '')), 'B') ||
  setweight(to_tsvector('english', coalesce(naics_code, '')), 'C') ||
  setweight(to_tsvector('english', coalesce(raw_text, '')), 'D')
) STORED;

CREATE INDEX idx_warn_notice_fts ON warn_notice USING gin (search_vector);

generated column 的优势在于写入数据时自动更新索引，无需额外的触发器或应用层逻辑，大幅降低了维护成本。

查询模式与相关性排序

完成索引构建后，典型的查询场景可分为三类。第一类是自由文本搜索，用户输入任意关键词（如公司名称、行业关键词），系统返回匹配结果并按相关性降序排列：

SELECT id, employer, city, state, notice_date,
       ts_rank(search_vector, plainto_tsquery('english', :q)) AS rank
FROM warn_notice
WHERE search_vector @@ plainto_tsquery('english', :q)
ORDER BY rank DESC
LIMIT 50;

plainto_tsquery 函数会将用户输入的自然语言转换为查询向量，自动处理分词与停用词过滤。第二类是布尔查询，支持 AND、OR、NOT 组合，例如搜索 "Tesla AND (Nevada OR Texas)" 表示查找在 Nevada 或 Texas 提交的、涉及 Tesla 的裁员通知。第三类是高亮摘要，调用 ts_headline 函数在返回结果中生成匹配片段，便于前端直接展示：

SELECT id, employer,
       ts_headline('english', raw_text, plainto_tsquery('english', :q)) AS snippet
FROM warn_notice
WHERE search_vector @@ plainto_tsquery('english', :q);

结构化过滤与全文检索的组合

实际业务中，纯全文检索往往无法满足需求，需结合结构化过滤条件。典型的组合查询包括：按州过滤、按通知日期或裁员起始日期区间过滤、按受影响员工数阈值过滤等。PostgreSQL 的查询优化器能够有效处理这种组合 ——GIN 索引负责全文匹配部分，B-tree 索引负责结构化字段的过滤：

SELECT *
FROM warn_notice
WHERE state = :state
  AND notice_date BETWEEN :start_date AND :end_date
  AND employees_affected >= :min_employees
  AND search_vector @@ plainto_tsquery('english', :q)
ORDER BY notice_date DESC;

这种组合查询模式是 WARN Firehose 等商业化聚合 API 的底层实现基础 —— 用户可通过 RESTful 端点传入 q（关键词）、state（州代码）、from 与 to（日期区间）、min_employees（最小裁员人数）等参数，后端将其转换为上述 SQL 执行。

运维参数与监控要点

构建生产级数据管道需要关注若干运维参数。首先是爬虫的容错与重试机制，建议对每个州配置独立的重试策略（指数退避）和超时阈值（建议单次请求不超过 30 秒），避免某个州的数据源故障影响整体采集进度。其次是 ETL 任务的调度频率，WARN 通知的发布通常以日为周期，建议将调度窗口设置在各地州政府工作时间之后（如每日 UTC 15:00），确保当日发布的数据能够被完整采集。

数据库层面的监控应关注以下指标：GIN 索引的膨胀率（可通过 pg_stat_monitor 插件观测）、查询延迟的 P95/P99 分位数、写入吞吐与主键冲突次数。对于数据量超过百万级的表，建议定期执行 VACUUM ANALYZE 维护索引健康，同时可根据查询热点考虑对 state、notice_date 等高频过滤字段构建复合索引。

整体而言，WARN 法案通知的聚合搜索是一个典型的 “异构源采集 + 结构化清洗 + 全文检索” 工程问题，PostgreSQL 的内置全文检索能力在数据量尚未达到 Elasticsearch 阈值前具有部署简单、维护成本低的显著优势。对于计划构建类似垂直数据聚合系统的团队，建议从单一州的数据采集与清洗起步，验证 Schema 设计后再扩展至多州，避免早期过度工程化。

参考资料

查看归档