LanceDB ｘ DuckDB Demo

A hands-on example showing how LanceDB and DuckDB work together on multimodal data. The repo ingests the Amazon Berkeley Objects (ABO) dataset into a LanceDB catalog with CLIP image embeddings and text embeddings, then uses DuckDB with the Lance extension to query, join, and materialize results via SQL.

The LanceDB products table stores product metadata, image paths, raw image bytes (image_bytes), a CLIP multimodal vector (multimodal_vec), and a text-semantic vector (text_vec). DuckDB attaches the Lance directory as a namespace and runs SQL directly on top of it -- vector search, joins to local DuckDB tables, aggregations, and even returning image BLOBs in the result set.

Setup

Requires Python 3.12+.

# Sync dependencies from pyproject.toml
uv sync
# Add dependencies as needed
uv add ...

Download the ABO dataset

Download the listings metadata and small images from the ABO dataset page and extract them into data/:

After extracting, the layout should look like:

data/
  abo-listings/listings/metadata/   # listings_*.json.gz files
  abo-images-small/images/
    metadata/images.csv.gz          # image metadata
    small/                          # downscaled image files

Usage

Ingest products into LanceDB

Embeddings are computed and written to LanceDB in batches, so progress is saved incrementally.

# Full ingestion (~145K products)
uv run python ingest.py

# Smoke test with a subset
uv run python ingest.py --limit 200

# Larger batches (default is 32)
uv run python ingest.py --batch-size 128

The output LanceDB directory is ./abo-products-lance by default (override with --output-root).

Text-semantic search

Search the catalog by text using the text_vec embedding column:

uv run python text_search.py

This encodes the text query with the intfloat/multilingual-e5-base multilingual embedding model and runs a top-k vector search over the Lance table via DuckDB.

Create a DuckDB sales table

Before running the image search, generate a local sales.duckdb file with synthetic sales rows linked to shoe products in the Lance catalog:

uv run python create_duckdb.py

This reads shoe item_ids from the Lance table and creates a sales table with 100 randomly assigned purchases. The image search script joins against this table.

Image-semantic search with sales join

Search by visual concept using the CLIP multimodal_vec column and join results to the sales table:

uv run python image_search.py

This encodes "beige shoes" and "colorful floral slip-on shoes" with CLIP, retrieves the nearest products, joins them to sales.duckdb, and writes the top floral match to disk by pulling the raw image_bytes straight from SQL.

Migrating an existing Lance table

If you ingested before image_bytes existed in the schema, run:

uv run python add_image_bytes.py

This reads each row's image_path and adds an image_bytes column via LanceDataset.merge, without re-running CLIP/text embeddings.

Query with the DuckDB CLI

You can also query the Lance table directly from the DuckDB CLI:

duckdb

INSTALL lance;
LOAD lance;
ATTACH './abo-products-lance' AS abo (TYPE LANCE);

SELECT item_id, title, brand, product_type
FROM abo.main.products
LIMIT 10;