Skip to content

HathiTrust Database Build Process

Jump to bottom
Matt Miller edited this page Dec 17, 2025 · 1 revision

This document explains how the local HathiTrust database is built and used for offline book reconciliation.

Overview

The HathiTrust strategy uses a local SQLite database instead of making live API calls. This provides:

  • Fast searching - Full-text search via SQLite FTS5 is much faster than remote API calls
  • Offline capability - Works without internet once the database is built
  • No rate limits - Search as much as you need without API restrictions

The database must be built before using the HathiTrust reconciliation strategy. This is a one-time process that downloads and processes the full HathiTrust metadata dump.

Build Process Steps

The database build process (lib/strategies_hathitrust_build_db.py) runs in three main stages:

1. Find Latest Dump File

  • Fetches the HathiTrust file list from https://www.hathitrust.org/files/hathifiles/hathi_file_list.json
  • Scans for the newest "full" dump file (contains complete metadata, not incremental updates)
  • Returns the download URL for the latest dump

2. Download the Dump File

  • Downloads the gzipped TSV file (typically 1-2 GB compressed)
  • Saves to the HathiTrust data directory
  • Progress is tracked and reported via status updates

3. Build the SQLite Database

This is the most complex stage:

a. Initialize Database

Creates two tables in hathitrust.db:

records table - Stores the consolidated book metadata:

Column Description
ht_bib_key Primary key (HathiTrust bibliographic key)
htid HathiTrust item IDs (pipe-delimited if multiple)
access Access level (allow/deny)
description Volume/edition description
oclc_num OCLC numbers (pipe-delimited)
isbn ISBNs (pipe-delimited)
issn ISSNs (pipe-delimited)
lccn Library of Congress Control Numbers (pipe-delimited)
title Book title
rights_date_used Publication/rights date
lang Language codes (pipe-delimited)
author Author name

author_title table - FTS5 full-text search index:

Column Description
rowid Links to ht_bib_key in records table
author Author name (searchable)
title Title (searchable)

b. Process the Dump File

The dump file contains one line per item (physical/digital copy), but multiple items can belong to the same work (bibliographic record). The build process:

  1. Filters records - Only keeps:

    • Books (bib_fmt = 'BK')
    • Records with an author (skips anonymous works)

  2. Consolidates items - Groups items by ht_bib_key:

    • Collects all htid values into a list
    • Aggregates OCLC numbers, ISBNs, ISSNs, LCCNs, languages
    • Uses the best access level (if any item allows access, the record shows "allow")

  3. Batches to disk - Writes records in batches of 50,000 to temporary pickle files to manage memory usage

c. Insert into Database

  • Reads each batch file and inserts records into SQLite
  • Populates both the records table and the FTS5 author_title index
  • Commits every 10,000 records for durability
  • Cleans up batch files and the downloaded gzip after completion

Database Location

The database is stored in the application's data directory:

Platform Location
macOS ~/Library/Application Support/BookReconciler/data/hathi/hathitrust.db
Windows %APPDATA%\BookReconciler\data\hathi\hathitrust.db
Linux ~/.bookreconciler/data/hathi/hathitrust.db
Development PROJECT_ROOT/data/hathi/hathitrust.db

Build Statistics

Typical build results (as of the code comments):

Metric Count
Total lines in dump ~18.8 million
Lines after filtering (books with authors) ~10 million
Final consolidated records ~7.3 million

The build process takes approximately 30-60 minutes depending on download speed and system performance.

Status Tracking

The build process writes status updates to build_status.json in the HathiTrust data directory. Status includes:

{
  "status": "processing",
  "message": "Processed 500000 records from 750000 lines",
  "timestamp": "2024-01-15T10:30:00.000000",
  "progress": 500000,
  "total": 7300000
}

Status values:

  • starting - Initializing
  • finding_dump - Searching for latest dump URL
  • found_dump - Found dump file
  • downloading - Downloading dump file
  • download_complete - Download finished
  • preparing - Creating database tables
  • processing - Reading and inserting records
  • finalizing - Final database commit
  • complete - Build successful
  • error - Build failed (check message for details)

How Searching Works

When you use the HathiTrust reconciliation strategy:

  1. FTS5 Search - The author_title virtual table is searched using SQLite's full-text search:

    SELECT rowid FROM author_title
WHERE author MATCH ? AND title MATCH ?
ORDER BY rank;

  2. Retrieve Records - Matching rowid values are used to fetch full records from the records table

  3. Fuzzy Scoring - Results are scored using token sort ratio (same as other strategies) to rank matches

Rebuilding the Database

To rebuild the database (e.g., to get updated HathiTrust data):

  1. The build process automatically removes any existing database before starting
  2. Trigger a new build from the web interface or API
  3. The old database is replaced with the new one upon successful completion

Troubleshooting

Build fails with "File not found"

The download may have failed. Check your internet connection and try again.

Build fails with "Invalid gzip file"

The download was corrupted. Delete any partial downloads in the HathiTrust data directory and try again.

Search returns no results

  • Verify the database exists and is not empty
  • Check that SQLite FTS5 is available (the code verifies this at startup)
  • Try broader search terms

Database is very large

The database is typically 2-4 GB. This is normal given the ~7 million records with full-text search indexes.

Clone this wiki locally