openlibrary-search

Open Library database

Open Library is an large online library of bibliographic data. They publish full data dumps of data on authors, works, and editions.

This project provides instructions and scripts for downloading and importing this data into a PostgreSQL database, and some sample queries to test the database.

The database is primarily aimed at querying using ISBN and includes tables specifically for these identifiers. It could be extended to change this to other identifiers, such as Open Library ID, or text searching by title or author.

Getting started

The following steps should get you up and running with a working database.

1. Install the required prerequisites so that you have a local database server.
2. Download the data from Open Library.
3. Run the processing the data scripts to prepare for import.
4. Import the data into the database.

Prerequisites

• Python 3 - Tested with 3.10
• PostgreSQL - Version 15 is tested but all recent versions should work. The scripts should be run on a local server - if you need to transfer this to the cloud, it would be better done once the database has been created.
• Disk space - The data files are large, and the uncompressed editions file is 45GB. You will need at least 250GB of free space to import all the data.

Downloading the data

Open Library offer bulk downloads on their website, available from the data dumps page.

These are updated every month. The downloads include (compressed sizes shown):

• Editions (~10.5GB)
• Works (~3.5GB)
• Authors (~0.5GB)
• All types (~10GB)

Download the Editions, Works, and Authors data dumps.

wget https://openlibrary.org/data/ol_dump_editions_latest.txt.gz -P ~/downloads
wget https://openlibrary.org/data/ol_dump_works_latest.txt.gz -P ~/downloads
wget https://openlibrary.org/data/ol_dump_authors_latest.txt.gz -P ~/downloads

Move the data from your downloads folder.

mv ~/downloads/ol_dump_authors_*txt.gz ./data/unprocessed/ol_dump_authors.txt.gz
mv ~/downloads/ol_dump_works_*txt.gz ./data/unprocessed/ol_dump_works.txt.gz
mv ~/downloads/ol_dump_editions_*txt.gz ./data/unprocessed/ol_dump_editions.txt.gz

Then uncompress the data files.

gzip -d -c data/unprocessed/ol_dump_editions.txt.gz > data/unprocessed/ol_dump_editions.txt
gzip -d -c data/unprocessed/ol_dump_works.txt.gz > data/unprocessed/ol_dump_works.txt
gzip -d -c data/unprocessed/ol_dump_authors.txt.gz > data/unprocessed/ol_dump_authors.txt

This should leave you with 3 unprocessed files in the data/unprocessed directory.

Processing the data

Unfortunately the downloads provided don’t play nicely for direct importing into PostgreSQL. They error on import as the number of columns provided varies. Cleaning it up can be difficult as the text file for editions is 25GB.

To do note: Check if this is still the case and if so there could be some Linux tools to fix this - maybe try sed and awk

Instead, the file openlibrary_data_process.py reads in the text file and writes it out again for each row, but only where there are 5 columns.

Because the files are huge and are only going to grow (editions is now 45gb+) the openlibrary_data_process.py file also splits the data into smaller files to load sequentially. You can change the number of lines in each chunk by using the LINES_PER_FILE setting in the script. The default is 2 million.

Once the files are split you can delete the 3 .txt files in the uncompressed folder because you will need around 250 Gb of freespace to load all 3 files into the database without encountering lack of space errors. If you have plenty of space you can keep the files!

python openlibrary_data_process.py

This generates multiple files into the data/processed directory. One of those files (filenames.txt) is used as a checklist when loading the data.

On a Macbook M3 this process took 15 minutes.

Import into database

We import the data directly into PostgreSQL tables and can then do complex searches with SQL.

There are a series of database scripts which will create the database and tables, and then import the data. These are in the database folder. The data files (created in the previous process) need to be within the data/processed folder for this to work.

The PostgreSQL database command line tool psql is used to run the scripts. The following command will create the database and tables and load the data. Replace username with your database server username.

psql --set=sslmode=require -f openlibrary-db.sql -h localhost -p 5432 -U username postgres

This process is intensive and you should expect it to take a long time. On a Macbook M3 this took about 5 hours.

Database details

The database is split into 5 main tables

Query the data

That’s the database set up - it can now be queried using SQL.

Get details for a single item using the ISBN13 9781551922461 (Harry Potter and the Prisoner of Azkaban):

select
    e.data->>'title' "EditionTitle",
    w.data->>'title' "WorkTitle",
	a.data->>'name' "Name",
    e.data->>'subtitle' "EditionSubtitle",
    w.data->>'subtitle' "WorkSubtitle",
    e.data->>'subjects' "Subjects",
    e.data->'description'->>'value' "EditionDescription",
    w.data->'description'->>'value' "WorkDescription",
    e.data->'notes'->>'value' "EditionNotes",
    w.data->'notes'->>'value' "WorkNotes"
from editions e
join edition_isbns ei
    on ei.edition_key = e.key
join works w
    on w.key = e.work_key
join author_works a_w
	on a_w.work_key = w.key
join authors a
	on a_w.author_key = a.key
where ei.isbn = '9781551922461'