ONIX for Books is EDItEUR's XML standard for book metadata: release 3.1.2 (October 2024) is current, and 2.1 was formally deprecated in 2023. Publishers send it to retailers, as BookNest's catalog imitates; loading it means flattening it into a typed table and joining it to sales:
import decimal, duckdb, pyarrow as pa, pyarrow.parquet as pq
from lxml import etree
rows = [{"book_id": int(b.findtext("identifier")[3:]), # BN-0003 -> 3
"sku": b.findtext("identifier"), "title": b.findtext("title"),
"subject": b.findtext("subject"), "published": int(b.findtext("published")),
"price": decimal.Decimal(b.findtext("supply/price"))}
for b in etree.parse("booknest-catalog.xml").iter("book")]
schema = pa.schema([("book_id", pa.int32()), ("sku", pa.string()), ("title", pa.string()),
("subject", pa.string()), ("published", pa.int16()),
("price", pa.decimal128(9, 2))])
pq.write_table(pa.Table.from_pylist(rows, schema), "catalog.parquet")
ch3 = "/mnt/d/Books/Data Engineering/demos/ch03/out/orders.parquet" # Section 3.13
for row in duckdb.sql(f"""
SELECT c.subject, sum(i.qty) AS copies, sum(i.qty * i.unit_price) AS gross
FROM (SELECT status, unnest(items) AS i FROM '{ch3}') o
JOIN 'catalog.parquet' c ON c.book_id = o.i.book_id
WHERE o.status <> 'cancelled' GROUP BY ALL ORDER BY gross DESC""").fetchall():
print(*row)Output
Technology 23659 934530.50 Cooking 29873 716952.00 Fiction 46255 693362.45 Science Fiction 32930 533466.00 Home and Garden 15727 334985.10 Travel 4807 90131.25
The subjects add up to 3,303,427.30, the non-cancelled gross Analytical SQL and Data Warehouses computes from the same orders in PostgreSQL 1,289 (PostgreSQL 18 for Analytics). Real ONIX adds one Product per format, prices per territory and a notification type (new, update, delete), so loads are upserts (A Day in the Life) keyed on the ISBN.