Skip to content

BiliTube(二):用 SQLite 断点续采,重跑不重复 ​

第一次跑全量采集,跑到第 87 页被风控掐断。第二次跑到补齐详情,模型服务欠费。第三次终于跑完,改了个打分权重想重算,结果发现弹幕要重新抓——因为中间表是内存里的。

这大概是所有离线流水线都会经历的三个阶段:先能跑,再能断,最后能重跑。前两个靠代码,第三个靠存储设计。

BiliTube 的选择是一个 SQLite 文件:data/bilitube.db。没有数据库服务,没有容器,备份就是复制文件。它同时承担四件事:采集落库、断点状态、评分与卡片的中间结果、导出的唯一数据源。

一、为什么不是 JSONL,也不是 Postgres ​

最早的版本用的是 JSONL:一页搜索结果写一个文件,简单、可读、追加方便。问题很快出现:

  • 同一个视频会出现在多个关键词、多个排序里,JSONL 里就是多行,去重得自己写;
  • 「哪些视频还没补详情」「哪些还没抓弹幕」需要扫全部文件;
  • 改一条记录的字段,要重写整个文件;
  • 中途写坏一行,整份文件解析失败。

Postgres 当然能解决这些问题,但它引入了一个需要安装、需要启动、需要备份、需要管连接的服务。这个项目是单机、单用户、每周跑一次,数据量在百万行以内。用 Postgres 是用运维成本换一个当前不需要的并发能力。

SQLite 正好卡在中间:单文件、事务、索引、SQL 查询齐全,Python 标准库直接支持。ADR-003 的结论是「需要写少量 SQL,换来断点、事务与可重建性」,这笔交易很划算。

二、十张表,一条主线 ​

表作用关键约束
raw_videos搜索与详情归一后的视频bvid 主键
raw_topics每个关键词 × 排序 × 页的采集记录三列联合主键
raw_comments热评(未登录只能拿到少量)(bvid, rpid)
raw_danmaku弹幕文本(bvid, content) 去重
raw_subtitles字幕(可选,需登录态)(bvid, lang)
raw_upsUP 主粉丝数缓存mid 主键
videos过滤结论:passed / review / rejectedbvid 主键
scores机器分、内容分、总分、标记bvid 主键
cards模型生成的卡片 JSONbvid 主键
crawl_state每个采集单元的断点状态key 主键

主线只有一条:raw_videos 是原料,videos 是过滤后的结论,scores 和 cards 是两阶段评估,导出时把这四张表 join 起来。任何派生产物都可以从这三步重建,不需要重新采集。

建表不是一次性写完的。db.py 里用 SCHEMA_VERSION = 4 和四个迁移脚本,按版本号顺序执行,执行过的版本记在 schema_version 表里:

python
def init_db(conn: sqlite3.Connection) -> None:
    """按版本顺序执行迁移;可重复调用。"""
    conn.execute(
        "CREATE TABLE IF NOT EXISTS schema_version ("
        "version INTEGER PRIMARY KEY, applied_at TEXT NOT NULL)"
    )
    row = conn.execute("SELECT COALESCE(MAX(version), 0) AS v FROM schema_version").fetchone()
    current = int(row["v"])
    for version, migration in enumerate(MIGRATIONS, start=1):
        if version <= current:
            continue
        conn.executescript(migration)
        conn.execute(
            "INSERT INTO schema_version (version, applied_at) VALUES (?, ?)",
            (version, _now()),
        )
        conn.commit()

bilitube/db.py#L160-L176

没有 Alembic,没有迁移服务器。四个版本都是 ALTER TABLE ADD COLUMN 和建索引,够用。关键是迁移本身可重复执行:每次启动都调 init_db,已经应用的版本直接跳过。

连接参数只有三行,但都很重要:

python
conn = sqlite3.connect(path, timeout=30)
conn.row_factory = sqlite3.Row
conn.execute("PRAGMA journal_mode = WAL")
conn.execute("PRAGMA foreign_keys = ON")

bilitube/db.py#L150-L158

WAL 让读写不互相阻塞,采集进程写库时还能用另一个连接做检查。timeout=30 是给偶发的写锁等待留余量。

三、幂等写入:重复采集不产生重复行 ​

采集一定会重复。同一个视频会在「大模型」和「AI 教程」两个关键词下出现,也会在「最多播放」和「最新发布」两个排序里出现。第一版用 INSERT,结果 raw_videos 里同一个 BV 号出现五六次,后面所有查询都要先 DISTINCT,慢且容易漏。

现在统一走 upsert:

python
_VIDEO_COLUMNS = ("bvid", "aid", "title", ..., "raw_json", "cover")

assignments = ", ".join(
    f"{col} = COALESCE(excluded.{col}, raw_videos.{col})"
    for col in _VIDEO_COLUMNS
    if col != "bvid"
)
sql = (
    f"INSERT INTO raw_videos ({', '.join(_VIDEO_COLUMNS)}, collected_at) "
    f"VALUES ({placeholders}, ?) "
    f"ON CONFLICT(bvid) DO UPDATE SET {assignments}, collected_at = excluded.collected_at"
)

bilitube/db.py#L198-L245

COALESCE(excluded.col, raw_videos.col) 是核心:新值非空才覆盖,空值保留旧值。

这条规则救过两次:

  1. 搜索接口不返回 coin(投币)字段,第一次采集时 coin 全是 NULL;后来详情补齐写入了真实的投币数。如果第二次搜索采集直接覆盖,投币数会被重新写成 NULL。
  2. cover 字段是后来迁移加的。老数据没有封面,新采集有;用 COALESCE 才能在补齐时只填不改。

ADR-012 记录了这个发现:首轮 3,652 条候选里投币全是空值,所以投币率在详情补齐前不参与任何打分。代码里的 COALESCE 是这条结论的技术保证。

四、断点:每个采集单元一行状态 ​

断点不是「记住跑到第几页」。同一个关键词有两个排序,每个排序有自己的页,详情补齐按视频逐条走,弹幕按 BV 号逐条走。所以状态键必须细到最小的可重试单元:

text
search:<keyword>:<order>:<page>
enrich:<bvid>
danmaku:<bvid>

crawl_state 只有五个字段:key、status(pending / done / failed)、attempts、error、updated_at。写入用 upsert,失败时记录错误信息,下次只看 status != done 的:

python
def set_crawl_state(conn, key, status, *, error=""):
    """记录采集状态;attempts 在已是 pending 时累加。"""
    conn.execute(
        "INSERT INTO crawl_state (key, status, attempts, error, updated_at) "
        "VALUES (?, ?, 1, ?, ?) "
        "ON CONFLICT(key) DO UPDATE SET "
        "status = excluded.status, "
        "attempts = crawl_state.attempts + 1, "
        "error = excluded.error, "
        "updated_at = excluded.updated_at",
        (key, status, error, _now()),
    )
    conn.commit()

bilitube/db.py#L253-L273

弹幕采集直接用这条状态做 NOT EXISTS 子查询,只抓没抓过的:

sql
SELECT r.bvid, r.cid FROM raw_videos r
JOIN videos v ON v.bvid = r.bvid
WHERE v.status = 'passed' AND r.cid IS NOT NULL
  AND NOT EXISTS (
    SELECT 1 FROM crawl_state c
    WHERE c.key = 'danmaku:' || r.bvid AND c.status = 'done'
  )
ORDER BY r.view DESC

bilitube/collector.py#L388-L400

这就是「中断后重跑只补没做的」的实现。不需要额外的任务队列,一张状态表加一条 NOT EXISTS 就够了。

五、周更时最坑的一件事 ​

断点状态有一个反面:它会让你跳过本该重抓的页。

首轮采集把 search:大模型:pubdate:1 标成了 done。一周后再跑,代码看到 done 就直接跳过,于是永远只采第一周的数据。

第一次周更发现了这个问题:日志里 完成 0 页,跳过 60 页,导出的数据一条没变。修复不是删状态表,而是给 collect_search 加了一个 fresh 参数:

python
if not fresh and get_crawl_state(conn, key) == STATE_DONE:
    report.pages_skipped += 1
    continue

bilitube/collector.py#L175-L182

update 命令默认 fresh=True,强制重抓增量窗口里的页。BV 号写入本身幂等,重抓不会产生重复,只是多花几次请求。手动 collect search 默认仍然是断点续采,适合首次全量或大规模补采。

还有一个相邻的小优化:如果上一轮已经确认某个关键词只有 3 页(第 3 页返回的条数小于 page_size),这轮就不必再打第 4 页空请求:

python
def _exhausted_page(conn, keyword, order, page_size):
    """返回该关键词+排序已确认"是最后一页"的页码。"""
    row = conn.execute(
        "SELECT page, total FROM raw_topics "
        "WHERE keyword = ? AND order_type = ? ORDER BY page DESC LIMIT 1",
        (keyword, order),
    ).fetchone()
    if row is not None and (row["total"] or 0) < page_size:
        return int(row["page"])
    return None

bilitube/collector.py#L138-L147

踩坑:断点状态和增量更新是两件事

断点解决的是「这次没跑完,下次接着跑」;增量解决的是「上一轮跑完了,这一轮要重新看」。两者共用一张 crawl_state 表,但语义相反:一个要跳过 done,一个要无视 done。

如果只做断点不做增量,周更会永远返回第一周的数据;如果只做增量不做断点,一次风控中断就得从头再来。参数化的 fresh 是这两件事的分界线。

六、这张表有多贵 ​

数据规模重建成本
raw_videos4,739 行重新采集 + 补齐,受风控影响,最慢
videos1,774 条通过规则重跑,秒级
raw_danmaku213,027 行重新抓取 1,774 个视频,约 1 小时
cards1,774 张重新调模型,按单价计费,约 1 小时

所以 README 里专门写了备份命令:

bash
python -c "import sqlite3;c=sqlite3.connect('data/bilitube.db');c.execute(\"VACUUM INTO 'data/backup-2026-11-05.db'\")"

VACUUM INTO 会生成一个整理过的副本,不用停采集,也不会复制 WAL 里的中间状态。

什么时候该换 Postgres

几个信号出现时再考虑:需要多个进程同时写;数据涨到千万行、索引维护开始明显;要把采集和评估拆到不同机器;需要行级权限或远程访问。现在一条都不满足。

真到那一天,迁移成本主要在 SQL 方言和 json_extract 这类函数上,表结构本身是标准的。db.py 把连接、迁移、写入都收在一个文件里,换实现的边界是清楚的。

七、小结 ​

这个 SQLite 文件不是「先用简单的,以后再换」。它承担的是明确的职责:

  • 用主键和 upsert 保证重复采集不产生重复数据;
  • 用 crawl_state 把断点细到最小可重试单元;
  • 用 fresh 参数区分断点与增量;
  • 用 COALESCE 保证后补的字段不会被空值覆盖;
  • 用迁移表保证表结构可以随版本演进。

数据能稳定落下来之后,才轮到「怎么判断一个视频值不值得看」。下一篇讲过滤和打分:为什么固定阈值会放过最该挡掉的内容。

本文阅读--次|全站访问--次|访客--人