Query your data¶

The records are plain itemstore parts, Parquet by default, so any tool that reads Parquet reads them. These examples use DuckDB on a storage path data/books; replace it with yours.

Every row has the same columns: key, item_key, url, fetched_at, and data, the extracted fields as JSON. data->>'$.price' reads one field as text; cast it for arithmetic.

The current state of every record¶

The latest version of each record is its current state:

CREATE VIEW books AS
SELECT item_key, url, fetched_at, data
FROM read_parquet('data/books/items/part_*.parquet', union_by_name = true)
QUALIFY row_number() OVER (PARTITION BY item_key ORDER BY fetched_at DESC) = 1;

SELECT data->>'$.title' AS title, CAST(data->>'$.price' AS DOUBLE) AS price
FROM books
ORDER BY price DESC;

A record's history¶

Each version is the record from its fetched_at until the next version's. To see how prices moved:

SELECT item_key, fetched_at, CAST(data->>'$.price' AS DOUBLE) AS price
FROM read_parquet('data/books/items/part_*.parquet', union_by_name = true)
ORDER BY item_key, fetched_at;

To find records that changed at all:

SELECT item_key, count(*) AS versions
FROM read_parquet('data/books/items/part_*.parquet', union_by_name = true)
GROUP BY item_key
HAVING count(*) > 1;

When records were listed¶

Observations record every sighting on a listing page, which answers questions about the listing itself: when a record first appeared, when it was last seen, and how its card changed.

SELECT item_key,
       min(fetched_at) AS first_seen,
       max(fetched_at) AS last_seen,
       count(*) AS sightings
FROM read_parquet('data/books/observations/items/part_*.parquet', union_by_name = true)
GROUP BY item_key
ORDER BY first_seen DESC;

A record that a traversal did not see is not marked as removed. An incremental traversal stops at caught-up pages and skips most cards, so only an exhaustive one sees every listed record. To find records that left the listing, run databrew run --rescan and keep running until the summary no longer says traversal paused, with no coverage gaps; the records whose last_seen is earlier than the start of that rescan were not listed.

JSONL stores¶

With [storage].format = "jsonl", read the parts with read_json instead, as itemstore's format describes.

Reading during a crawl¶

The parts can be read while a crawl runs, though the newest rows may not have reached them yet; reading while a crawl runs has the details.