arsham/product_profile_ingestion
1
0
Fork 0
Code Issues Pull Requests Actions Packages Projects Releases Wiki Activity
main
product_profile_ingestion/README.md
Arsham Mirehvandi ba447e8796 Initial commit
2026-07-30 09:29:10 +02:00

5.9 KiB
Raw Permalink Blame History

Product Profile Ingestion

Data ingestion pipeline for an agricultural treatment assistant. It reads local product profile JSON files and label-chunk JSONL files, then loads them into the configured backends:

  • Weaviate — stores the semantic retrieval_summary from product profiles in the ProductProfile collection for vector search.
  • Microsoft SQL Server — stores structured product metadata in products, per-crop use rules in product_uses, and label text chunks in label_chunks.

The pipeline is idempotent: a hash manifest (.processed_manifest.json) tracks which files have already been ingested, so re-running skips unchanged files unless --force is used.

Prerequisites

  • Python 3.10+
  • Docker (for local Weaviate)
  • Microsoft SQL Server with the products, product_uses, and label_chunks tables already created
  • ODBC Driver for SQL Server (e.g. ODBC Driver 17 or 18)

Setup

  1. Clone the repository and enter the project directory.

  2. Create a virtual environment and install dependencies:

    python -m venv venv
# Windows
venv\Scripts\activate
# macOS / Linux
source venv/bin/activate

pip install -r requirements.txt

  3. Configure environment variables:

    cp .env.example .env

    Edit .env with your Gemini API key, Weaviate connection settings, and SQL Server credentials.

  4. Start Weaviate (uses the Gemini text2vec-google vectorizer; requires v1.32+ for taskType support):

    docker compose up -d

  5. Create the Weaviate collection (or let ingest.py create it on first run):

    python setup_weaviate.py

    The ProductProfile collection is configured to embed retrieval_summary with gemini-embedding-001 and the RETRIEVAL_DOCUMENT task type. If you change embedding settings on an existing database, recreate the collection:

    python setup_weaviate.py --recreate
python ingest.py --force

Usage

Place product profile JSON files under data/productProfiles/ and label-chunk JSONL files under data/chunks/, then run:

# Process new or changed profile and chunk files
python ingest.py

# Scan different directories
python ingest.py --profiles-dir path/to/profiles
python ingest.py --chunks-dir path/to/chunks

# Process a single file
python ingest.py --file data/productProfiles/ramin_sc_0916.json
python ingest.py --chunk-file data/chunks/delan_pro_16562.jsonl

# Reprocess everything, ignoring the manifest
python ingest.py --force

Profiles are processed before chunks in each run. Chunk rows reference product_id as a foreign key to products, so the matching product profile should be ingested first (the default run order handles this when both files are present).

Exit code 0 means all targeted files succeeded; 1 means at least one file failed (partial SQL transactions are rolled back per file).

Configuration

Variable Required Default Description
GEMINI_API_KEY Yes — API key for Weaviate's Gemini vectorizer
WEAVIATE_HOST No localhost Weaviate HTTP host
WEAVIATE_HTTP_PORT No 8080 Weaviate HTTP port
WEAVIATE_GRPC_PORT No 50051 Weaviate gRPC port
SQL_DRIVER Yes — ODBC driver name (e.g. ODBC Driver 17 for SQL Server)
SQL_SERVER Yes — SQL Server hostname
SQL_DATABASE Yes — Database name
SQL_USERNAME Yes — SQL login
SQL_PASSWORD Yes — SQL password

Product profile JSON format

Each file must include at least product_id, product_name, and retrieval_summary. See data/productProfiles/ramin_sc_0916.json for a full example with metadata arrays and uses rules.

Weaviate receives:

  • product_id, product_name, retrieval_summary

SQL products receives global metadata (registration, manufacturer, action flags, spray volumes, JSON-encoded arrays for ingredients, crops, diseases, etc.).

SQL product_uses receives one row per entry in the uses array (crop, disease, dose ranges, treatment intervals, growth stages, and related fields).

Label chunk JSONL format

Each .jsonl file contains one JSON object per line. See data/chunks/delan_pro_16562.jsonl for an example.

SQL label_chunks receives one row per line with:

JSON field SQL column Notes
chunk_id chunk_id Primary key
product_id product_id Foreign key to products
product_name product_name
target_crops target_crops Stored as JSON string via json.dumps()
target_diseases target_diseases Stored as JSON string via json.dumps()
chunk_type chunk_type
chunk_text chunk_text

On re-ingestion, all existing label_chunks rows for the file's product_id are deleted and replaced with the fresh set from the file.

Project layout

product_profile_ingestion/
├── data/
│   ├── productProfiles/    # Product profile JSON files (*.json)
│   └── chunks/            # Label-chunk JSONL files (*.jsonl)
├── ingest.py              # Main ingestion script
├── setup_weaviate.py      # ProductProfile collection schema (Gemini embeddings)
├── requirements.txt       # Python dependencies
├── docker-compose.yml     # Local Weaviate instance
├── .env.example           # Environment variable template
└── .processed_manifest.json  # Generated at runtime (gitignored)

Dependencies