SAP HANA Cloud vector engine: embeddings next to business data, searched with SQL

SELECT TOP 3 ID, TEXT,
       COSINE_SIMILARITY(EMBEDDING, TO_REAL_VECTOR(?)) AS SCORE
  FROM COURSE_HELP_CHUNKS
 WHERE (COMPANY_CODE = ? OR COMPANY_CODE = '*')
   AND VALID_FROM <= ?
 ORDER BY SCORE DESC

CREATE HNSW VECTOR INDEX COURSE_HELP_CHUNKS_IDX
  ON COURSE_HELP_CHUNKS (EMBEDDING)
  SIMILARITY FUNCTION COSINE_SIMILARITY
  BUILD CONFIGURATION '{"M": 16, "efConstruction": 100}'
  SEARCH CONFIGURATION '{"efSearch": 100}'
  ONLINE

"""Unit 7: the SAP HANA Cloud vector engine, end to end.

Loads made-up SAP help notes (and unit07/chunks.jsonl if you made it) into a table with a
REAL_VECTOR column, searches them with COSINE_SIMILARITY plus business filters in SQL, builds
and drops an HNSW vector index, and measures index recall and speed against exact search.

How to run (from your course folder, with .venv turned on):
    python unit07/hana_vector_lab.py load                      # embed the notes and load them
    python unit07/hana_vector_lab.py search "why is this order blocked" --company-code 1000
    python unit07/hana_vector_lab.py index create --m 16 --ef-construction 100 --ef-search 100
    python unit07/hana_vector_lab.py index drop
    python unit07/hana_vector_lab.py bench --n 20000           # index vs exact search
    python unit07/hana_vector_lab.py info
Options for every command:
    --sample    no account: a local file stands in for SAP HANA Cloud (searches are always exact)
    --offline   toy embeddings, no model download (matches words, not meaning)
    --show-sql  print each SQL statement before it runs
"""
import argparse
import datetime as dt
import hashlib
import json
import math
import os
import re
import struct
import sys
import time
from pathlib import Path

HERE = Path(__file__).resolve().parent
CHUNKS = HERE / "chunks.jsonl"                 # from "Chunking and document preparation" (optional)
SAMPLE_DB = HERE / "hana_lab_sample.json"      # the --sample stand-in for the database
TABLE, INDEX = "COURSE_HELP_CHUNKS", "COURSE_HELP_CHUNKS_IDX"
BENCH_TABLE, BENCH_INDEX = "COURSE_BENCH_VECTORS", "COURSE_BENCH_VECTORS_IDX"
DIM = 384                                      # all-MiniLM-L6-v2 gives 384 numbers per text
MODEL = "sentence-transformers/all-MiniLM-L6-v2"

# Made-up help notes: (id, process, company code or * for all, valid from, text)
NOTES = [
    ("kb-001", "order-to-cash", "1000", "2026-01-01", "Company code 1000: a sales order is blocked for delivery when the customer's open items plus the order value exceed the credit limit. Credit management releases it."),
    ("kb-002", "order-to-cash", "2000", "2026-01-01", "Company code 2000: a sales order is blocked when it exceeds the credit limit, and also when the customer has items overdue by more than 30 days."),
    ("kb-003", "order-to-cash", "1000", "2026-11-01", "Company code 1000, from November 2026: credit blocks on orders below 500 EUR are released automatically each night."),
    ("kb-004", "order-to-cash", "*", "2025-07-01", "Orders for export customers stop at delivery when customs or export documents are missing."),
    ("kb-005", "order-to-cash", "*", "2025-07-01", "A delivery block set by the sales team holds an order until pricing or payment terms are confirmed with the customer."),
    ("kb-006", "procure-to-pay", "*", "2025-07-01", "An invoice is blocked for payment when the invoiced quantity is higher than the quantity received in goods receipt."),
    ("kb-007", "procure-to-pay", "1000", "2026-07-01", "Company code 1000: a price difference of more than 5 percent between purchase order and supplier invoice blocks the invoice."),
    ("kb-008", "procure-to-pay", "2000", "2026-07-01", "Company code 2000: a price difference of more than 2 percent between purchase order and supplier invoice blocks the invoice."),
    ("kb-009", "procure-to-pay", "*", "2025-07-01", "Three-way match compares purchase order, goods receipt and invoice before the invoice can be paid."),
    ("kb-010", "plan-to-produce", "*", "2025-07-01", "MRP raises an exception message when a planned receipt arrives after the date the material is needed."),
    ("kb-011", "plan-to-produce", "*", "2025-07-01", "A reschedule-in exception means MRP suggests moving a planned order earlier to cover demand."),
    ("kb-012", "plan-to-produce", "3000", "2026-01-01", "Company code 3000: planners review MRP exceptions every morning before 9:00, starting with materials on the critical parts list."),
]

SQL_CREATE = (f"CREATE COLUMN TABLE {TABLE} (ID NVARCHAR(100) PRIMARY KEY, TEXT NVARCHAR(5000), "
              "SOURCE NVARCHAR(200), PROCESS NVARCHAR(20), COMPANY_CODE NVARCHAR(4), VALID_FROM DATE, "
              f"EMBED_MODEL NVARCHAR(60), EMBEDDING REAL_VECTOR({DIM}))")
SQL_INSERT = f"INSERT INTO {TABLE} VALUES (?, ?, ?, ?, ?, ?, ?, ?)"


# ---------- embeddings ----------

def toy_embed(text: str) -> list:
    """Stand-in for a real model: hash each word into one of 384 slots. Matches words, not meaning."""
    vector = [0.0] * DIM
    for word in re.findall(r"[a-z0-9]+", text.lower()):
        vector[int(hashlib.md5(word.encode()).hexdigest(), 16) % DIM] += 1.0
    length = math.sqrt(sum(v * v for v in vector)) or 1.0
    return [v / length for v in vector]


def get_embedder(offline: bool):
    """Return (model name stored with each row, function from a list of texts to a list of vectors)."""
    if offline:
        return "toy-hash-384", lambda texts: [toy_embed(t) for t in texts]
    try:
        from sentence_transformers import SentenceTransformer
    except ImportError:
        sys.exit("sentence-transformers is not installed. See Set up for Unit 3, or add --offline.")
    try:
        model = SentenceTransformer(MODEL)
    except Exception as error:
        sys.exit(f"Could not load the model ({type(error).__name__}). Check your network, or add --offline.")
    return "minilm-384", lambda texts: model.encode(texts, normalize_embeddings=True).tolist()


def vector_text(vector) -> str:
    """'[0.1,0.2,...]': the text form that TO_REAL_VECTOR turns into a vector."""
    return "[" + ",".join(f"{v:.6f}" for v in vector) + "]"


def vector_bytes(vector) -> bytes:
    """Binary REAL_VECTOR: 4-byte little-endian length, then 4-byte floats. Faster than text for loading."""
    return struct.pack(f"<I{len(vector)}f", len(vector), *vector)


def cosine(a, b) -> float:
    dot = sum(x * y for x, y in zip(a, b))
    return dot / ((math.sqrt(sum(x * x for x in a)) * math.sqrt(sum(y * y for y in b))) or 1.0)


# ---------- the SQL for a filtered vector search ----------

def search_sql(top: int, company_code, process, valid_on, exact: bool, table: str = TABLE):
    """Build the SELECT and the list of filter values. The question vector is the first parameter."""
    where, params = [], []
    if company_code:
        where.append("(COMPANY_CODE = ? OR COMPANY_CODE = '*')")
        params.append(company_code)
    if process:
        where.append("PROCESS = ?")
        params.append(process)
    if valid_on:
        where.append("VALID_FROM <= ?")
        params.append(valid_on)
    sql = (f"SELECT TOP {int(top)} ID, PROCESS, COMPANY_CODE, TEXT, "
           f"COSINE_SIMILARITY(EMBEDDING, TO_REAL_VECTOR(?)) AS SCORE FROM {table}")
    if where:
        sql += " WHERE " + " AND ".join(where)
    sql += " ORDER BY SCORE DESC"
    if exact:
        sql += " WITH HINT (NO_VECTOR_INDEX)"   # compare every row, even if an index exists
    return sql, params


def index_sql(table: str, index: str, m, ef_construction, ef_search) -> str:
    build = {k: v for k, v in (("M", m), ("efConstruction", ef_construction)) if v}
    sql = f"CREATE HNSW VECTOR INDEX {index} ON {table} (EMBEDDING) SIMILARITY FUNCTION COSINE_SIMILARITY"
    if build:
        sql += f" BUILD CONFIGURATION '{json.dumps(build)}'"
    if ef_search:
        sql += f" SEARCH CONFIGURATION '{json.dumps({'efSearch': ef_search})}'"
    return sql + " ONLINE"


# ---------- two backends with the same methods ----------

class HanaBackend:
    """Talks to your SAP HANA Cloud instance with SAP's hdbcli driver."""

    def __init__(self, show_sql: bool):
        try:
            from dotenv import load_dotenv
            from hdbcli import dbapi
        except ImportError as error:
            sys.exit(f"{error.name} is not installed. Run: pip install -r requirements.txt")
        load_dotenv()
        names = ["HANA_DB_ADDRESS", "HANA_DB_PORT", "HANA_DB_USER", "HANA_DB_PASSWORD"]
        missing = [n for n in names if not os.getenv(n)]
        if missing:
            sys.exit(f"Missing in .env: {', '.join(missing)}. See Set up for Unit 7, Step 5, or add --sample.")
        self.dbapi, self.show_sql = dbapi, show_sql
        try:
            self.conn = dbapi.connect(address=os.getenv("HANA_DB_ADDRESS"), port=int(os.getenv("HANA_DB_PORT")),
                                      user=os.getenv("HANA_DB_USER"), password=os.getenv("HANA_DB_PASSWORD"))
        except dbapi.Error as error:
            sys.exit(f"Could not connect: {error}\nIs the instance running (it stops every night)? "
                     "Check the address, port, user, password and the allowed IP addresses.")

    def run(self, sql, params=None, many=False, quiet_errors=False):
        if self.show_sql:
            print(f"  SQL> {sql}")
        cursor = self.conn.cursor()
        try:
            if many:
                cursor.executemany(sql, params)
            else:
                cursor.execute(sql, params or [])
            return cursor.fetchall() if cursor.description else []
        except self.dbapi.Error as error:
            if quiet_errors:
                return None
            sys.exit(f"SAP HANA Cloud refused the statement: {error}")
        finally:
            cursor.close()

    def reset_table(self, create_sql, table):
        self.run(f"DROP TABLE {table}", quiet_errors=True)   # normal to fail the first time
        self.run(create_sql)

    def insert(self, sql, rows):
        for start in range(0, len(rows), 1000):            # send 1,000 rows per round trip
            batch = [r[:-1] + (vector_bytes(r[-1]),) for r in rows[start:start + 1000]]
            self.run(sql, batch, many=True)

    def search(self, vector, top, company_code=None, process=None, valid_on=None, exact=False, table=TABLE):
        sql, params = search_sql(top, company_code, process, valid_on, exact, table)
        return self.run(sql, [vector_text(vector)] + params)

    def models(self):
        rows = self.run(f"SELECT DISTINCT EMBED_MODEL FROM {TABLE}", quiet_errors=True)
        return None if rows is None else {r[0] for r in rows}

    def create_index(self, table, index, m, efc, efs):
        self.run(index_sql(table, index, m, efc, efs))

    def drop_index(self, index):
        return self.run(f"DROP INDEX {index} ONLINE", quiet_errors=True) is not None

    def info(self):
        version = self.run("SELECT CLOUD_VERSION FROM SYS.M_DATABASE", quiet_errors=True)
        count = self.run(f"SELECT COUNT(*) FROM {TABLE}", quiet_errors=True)
        return (version[0][0] if version else "unknown"), (count[0][0] if count else None)


class SampleBackend:
    """No account: keeps rows in unit07/hana_lab_sample.json and searches them exactly in Python.
    It prints the same SQL the real backend would send, so you can read along."""

    def __init__(self, show_sql: bool):
        self.show_sql = show_sql
        self.data = json.loads(SAMPLE_DB.read_text(encoding="utf-8")) if SAMPLE_DB.exists() else {}

    def _sql(self, sql):
        if self.show_sql:
            print(f"  SQL> {sql}")

    def _save(self):
        SAMPLE_DB.write_text(json.dumps(self.data), encoding="utf-8")

    def reset_table(self, create_sql, table):
        self._sql(f"DROP TABLE {table}")
        self._sql(create_sql)
        self.data = {"rows": [], "index": None}

    def insert(self, sql, rows):
        self._sql(sql)
        self.data["rows"] += [list(r[:5]) + [str(r[5]), r[6], r[7]] for r in rows]
        self._save()

    def search(self, vector, top, company_code=None, process=None, valid_on=None, exact=False, table=TABLE):
        self._sql(search_sql(top, company_code, process, valid_on, exact, table)[0])
        if not self.data.get("rows"):
            sys.exit("The sample store is empty. Run first: python unit07/hana_vector_lab.py load --sample")
        hits = []
        for rid, text, _src, proc, code, valid, _model, emb in self.data["rows"]:
            if company_code and code not in (company_code, "*"):
                continue
            if process and proc != process:
                continue
            if valid_on and valid > str(valid_on):
                continue
            hits.append((rid, proc, code, text, cosine(emb, vector)))
        return sorted(hits, key=lambda h: -h[4])[:top]

    def models(self):
        return {r[6] for r in self.data["rows"]} if self.data.get("rows") else None

    def create_index(self, table, index, m, efc, efs):
        self._sql(index_sql(table, index, m, efc, efs))
        self.data["index"] = {"M": m, "efConstruction": efc, "efSearch": efs}
        self._save()

    def drop_index(self, index):
        self._sql(f"DROP INDEX {index} ONLINE")
        had = bool(self.data.get("index"))
        self.data["index"] = None
        self._save()
        return had

    def info(self):
        return "sample (no database)", len(self.data.get("rows", [])) if self.data else None


# ---------- commands ----------

def read_rows(model_name, embed):
    """Built-in notes plus chunks.jsonl if it exists; returns rows ready to insert."""
    records = [(i, t, "course-help-notes", p, c, v) for i, p, c, v, t in NOTES]
    if CHUNKS.exists():
        for line in CHUNKS.read_text(encoding="utf-8").splitlines():
            if line.strip():
                rec = json.loads(line)
                meta = rec.get("metadata", {})
                records.append((rec["id"][:100], rec["text"][:5000], meta.get("source", "chunks.jsonl"),
                                meta.get("process", "procure-to-pay"), meta.get("company_code", "*"),
                                meta.get("valid_from") or "2025-01-01"))
    vectors = embed([r[1] for r in records])
    return [r[:5] + (dt.date.fromisoformat(r[5]), model_name, vec) for r, vec in zip(records, vectors)]


def cmd_load(db, args):
    model_name, embed = get_embedder(args.offline)
    rows = read_rows(model_name, embed)
    db.reset_table(SQL_CREATE, TABLE)
    db.insert(SQL_INSERT, rows)
    extra = len(rows) - len(NOTES)
    print(f"Loaded {len(rows)} rows into {TABLE} ({len(NOTES)} help notes"
          + (f" + {extra} chunks from chunks.jsonl" if extra else "") + f"), embedded with {model_name}.")


def cmd_search(db, args):
    model_name, embed = get_embedder(args.offline)
    stored = db.models()
    if stored is None:
        sys.exit(f"No table {TABLE} yet. Run first: python unit07/hana_vector_lab.py load"
                 + (" --sample" if args.sample else ""))
    if stored != {model_name}:
        sys.exit(f"The table was embedded with {', '.join(sorted(stored))}, but this run uses {model_name}. "
                 "Vectors from different models can't be compared: run load again with the same options.")
    start = time.perf_counter()
    hits = db.search(embed([args.question])[0], args.top, args.company_code, args.process,
                     args.valid_on, args.exact)
    ms = 1000 * (time.perf_counter() - start)
    filters = [f"{k} {v}" for k, v in (("company code", args.company_code), ("process", args.process),
                                         ("valid on", args.valid_on)) if v]
    print(f'Question: "{args.question}"' + (f"  [{', '.join(filters)}]" if filters else "")
          + ("  (exact search)" if args.exact else ""))
    if not hits:
        print("No rows matched the filters. That is a valid empty result, not an error.")
    for rank, (rid, proc, code, text, score) in enumerate(hits, 1):
        print(f"{rank}. {rid}  {proc}  company {code}  similarity {float(score):.3f}\n   {text[:150]}")
    print(f"({ms:.0f} ms)")


def cmd_index(db, args):
    if args.action == "drop":
        print(f"Dropped {INDEX}." if db.drop_index(INDEX) else f"No index {INDEX} to drop.")
        return
    db.drop_index(INDEX)                     # replace an older index with the new settings
    start = time.perf_counter()
    db.create_index(TABLE, INDEX, args.m, args.ef_construction, args.ef_search)
    print(f"Created HNSW index {INDEX} in {time.perf_counter() - start:.1f} s "
          f"(M {args.m or 'default'}, efConstruction {args.ef_construction or 'default'}, "
          f"efSearch {args.ef_search or 'default'}).")
    if args.sample:
        print("Sample mode only records the settings; its searches stay exact. Run without --sample to use a real index.")


def cmd_bench(db, args):
    try:
        import numpy as np
    except ImportError:
        sys.exit("numpy is not installed. Run: pip install -r requirements.txt")
    rng = np.random.default_rng(7)
    centres = rng.normal(size=(200, DIM)).astype(np.float32)
    centres /= np.linalg.norm(centres, axis=1, keepdims=True)

    def make(n):
        noise = rng.normal(size=(n, DIM)).astype(np.float32)
        noise /= np.linalg.norm(noise, axis=1, keepdims=True)
        v = centres[rng.integers(0, 200, size=n)] + 0.9 * noise
        return v / np.linalg.norm(v, axis=1, keepdims=True)

    vectors, queries = make(args.n), make(args.queries)
    codes = rng.choice(["1000", "2000", "3000"], size=args.n, p=[0.60, 0.395, 0.005])
    k = 10

    def truth(code=None):
        ids = np.arange(args.n) if code is None else np.where(codes == code)[0]
        scores = queries @ vectors[ids].T
        return [set(ids[np.argsort(-row)[:k]].tolist()) for row in scores]

    def measure(exact, code=None, expected=None):
        found, start = [], time.perf_counter()
        for q in queries:
            rows = db.search(q.tolist(), k, company_code=code, exact=exact, table=BENCH_TABLE)
            found.append({int(r[0]) for r in rows})
        ms = 1000 * (time.perf_counter() - start) / len(queries)
        return ms, sum(len(f & t) / k for f, t in zip(found, expected)) / len(expected)

    print(f"Made {args.n:,} synthetic vectors of {DIM} numbers; company code 3000 holds "
          f"{int((codes == '3000').sum())} of them (0.5%). {args.queries} test questions, top {k}.")
    if args.sample:
        start = time.perf_counter()
        truth()
        print(f"Sample mode: exact top {k} for all questions in Python took {time.perf_counter() - start:.2f} s.")
        print("The index comparison needs SAP HANA Cloud. Run without --sample. It would send:")
        print("  " + index_sql(BENCH_TABLE, BENCH_INDEX, args.m, args.ef_construction, args.ef_search))
        print("  " + search_sql(k, "3000", None, None, True, BENCH_TABLE)[0])
        return

    db.reset_table(f"CREATE COLUMN TABLE {BENCH_TABLE} (ID INTEGER PRIMARY KEY, PROCESS NVARCHAR(20), "
                   f"COMPANY_CODE NVARCHAR(4), TEXT NVARCHAR(10), EMBEDDING REAL_VECTOR({DIM}))", BENCH_TABLE)
    start = time.perf_counter()
    db.insert(f"INSERT INTO {BENCH_TABLE} VALUES (?, ?, ?, ?, ?)",
              [(i, "bench", str(codes[i]), "", vectors[i].tolist()) for i in range(args.n)])
    print(f"Loaded in {time.perf_counter() - start:.1f} s.")
    all_truth, rare_truth = truth(), truth("3000")
    results = [("exact (NO_VECTOR_INDEX)", "all") + measure(True, None, all_truth),
               ("exact (NO_VECTOR_INDEX)", "3000") + measure(True, "3000", rare_truth)]
    start = time.perf_counter()
    db.create_index(BENCH_TABLE, BENCH_INDEX, args.m, args.ef_construction, args.ef_search)
    print(f"Built the HNSW index in {time.perf_counter() - start:.1f} s.")
    results += [("HNSW index", "all") + measure(False, None, all_truth),
                ("HNSW index", "3000") + measure(False, "3000", rare_truth)]
    print(f"\n{'search':26}{'company code':>14}{'ms per query':>14}{'recall@10':>11}")
    for name, code, ms, rec in results:
        print(f"{name:26}{code:>14}{ms:>14.1f}{rec:>11.2f}")
    if not args.keep:
        db.run(f"DROP TABLE {BENCH_TABLE}")
        print(f"\nDropped {BENCH_TABLE} (its index goes with it). Use --keep to look at it first.")


def cmd_info(db, args):
    version, count = db.info()
    print(f"Database version: {version}")
    if count is None:
        print(f"No table {TABLE} yet. Run: python unit07/hana_vector_lab.py load")
        return
    models = db.models() or set()
    print(f"{TABLE}: {count} rows, embedding model(s): {', '.join(sorted(models)) or '-'}")
    print(f"Vector data: about {count * (DIM * 4 + 4) / 1024:.0f} KB ({count} rows x {DIM} numbers x 4 bytes, "
          "before index, text and other columns)")


def main():
    common = argparse.ArgumentParser(add_help=False)
    common.add_argument("--sample", action="store_true", help="no account: use a local file instead of HANA")
    common.add_argument("--offline", action="store_true", help="toy embeddings; no model download")
    common.add_argument("--show-sql", action="store_true", help="print each SQL statement")
    hnsw = argparse.ArgumentParser(add_help=False)
    hnsw.add_argument("--m", type=int, help="links per node (database default if left out)")
    hnsw.add_argument("--ef-construction", type=int, help="candidates while building")
    hnsw.add_argument("--ef-search", type=int, help="candidates while searching")

    parser = argparse.ArgumentParser(description="SAP HANA Cloud vector engine lab.")
    sub = parser.add_subparsers(dest="command", required=True)
    sub.add_parser("load", parents=[common], help="embed the notes and load the table")
    s = sub.add_parser("search", parents=[common], help="filtered vector search")
    s.add_argument("question")
    s.add_argument("--company-code", help="e.g. 1000; rows for * (all company codes) always match")
    s.add_argument("--process", choices=["order-to-cash", "procure-to-pay", "plan-to-produce"])
    s.add_argument("--valid-on", type=dt.date.fromisoformat, help="only rows valid on this date, YYYY-MM-DD")
    s.add_argument("--top", type=int, default=3)
    s.add_argument("--exact", action="store_true", help="add WITH HINT (NO_VECTOR_INDEX)")
    i = sub.add_parser("index", parents=[common, hnsw], help="create or drop the HNSW index")
    i.add_argument("action", choices=["create", "drop"])
    b = sub.add_parser("bench", parents=[common, hnsw], help="recall and speed: index vs exact")
    b.add_argument("--n", type=int, default=20000, help="how many synthetic vectors (default 20000)")
    b.add_argument("--queries", type=int, default=50, help="how many test questions (default 50)")
    b.add_argument("--keep", action="store_true", help="don't drop the bench table at the end")
    sub.add_parser("info", parents=[common], help="version, row count, models, size estimate")
    args = parser.parse_args()

    db = SampleBackend(args.show_sql) if args.sample else HanaBackend(args.show_sql)
    {"load": cmd_load, "search": cmd_search, "index": cmd_index, "bench": cmd_bench, "info": cmd_info}[args.command](db, args)


if __name__ == "__main__":
    main()

  SQL> DROP TABLE COURSE_HELP_CHUNKS
  SQL> CREATE COLUMN TABLE COURSE_HELP_CHUNKS (ID NVARCHAR(100) PRIMARY KEY, TEXT NVARCHAR(5000), SOURCE NVARCHAR(200), PROCESS NVARCHAR(20), COMPANY_CODE NVARCHAR(4), VALID_FROM DATE, EMBED_MODEL NVARCHAR(60), EMBEDDING REAL_VECTOR(384))
  SQL> INSERT INTO COURSE_HELP_CHUNKS VALUES (?, ?, ?, ?, ?, ?, ?, ?)
Loaded 12 rows into COURSE_HELP_CHUNKS (12 help notes), embedded with toy-hash-384.

Question: "why is this customer's order blocked for delivery"
1. kb-001  order-to-cash  company 1000  similarity 0.433
   Company code 1000: a sales order is blocked for delivery when the customer's open items plus the order value exceed the credit limit. Credit managemen
2. kb-006  procure-to-pay  company *  similarity 0.267
   An invoice is blocked for payment when the invoiced quantity is higher than the quantity received in goods receipt.
3. kb-002  order-to-cash  company 2000  similarity 0.239
   Company code 2000: a sales order is blocked when it exceeds the credit limit, and also when the customer has items overdue by more than 30 days.
(1 ms)

  SQL> SELECT TOP 3 ID, PROCESS, COMPANY_CODE, TEXT, COSINE_SIMILARITY(EMBEDDING, TO_REAL_VECTOR(?)) AS SCORE FROM COURSE_HELP_CHUNKS WHERE (COMPANY_CODE = ? OR COMPANY_CODE = '*') ORDER BY SCORE DESC
Question: "why is this customer's order blocked for delivery"  [company code 2000]
1. kb-006  procure-to-pay  company *  similarity 0.267
   An invoice is blocked for payment when the invoiced quantity is higher than the quantity received in goods receipt.
2. kb-002  order-to-cash  company 2000  similarity 0.239
   Company code 2000: a sales order is blocked when it exceeds the credit limit, and also when the customer has items overdue by more than 30 days.
3. kb-005  order-to-cash  company *  similarity 0.209
   A delivery block set by the sales team holds an order until pricing or payment terms are confirmed with the customer.
(1 ms)

Question: "orders below 500 EUR released automatically"  [company code 1000, valid on 2026-12-01]
1. kb-003  order-to-cash  company 1000  similarity 0.639
   Company code 1000, from November 2026: credit blocks on orders below 500 EUR are released automatically each night.
2. kb-004  order-to-cash  company *  similarity 0.192
   Orders for export customers stop at delivery when customs or export documents are missing.
(0 ms)

  SQL> DROP INDEX COURSE_HELP_CHUNKS_IDX ONLINE
  SQL> CREATE HNSW VECTOR INDEX COURSE_HELP_CHUNKS_IDX ON COURSE_HELP_CHUNKS (EMBEDDING) SIMILARITY FUNCTION COSINE_SIMILARITY BUILD CONFIGURATION '{"M": 16, "efConstruction": 100}' SEARCH CONFIGURATION '{"efSearch": 100}' ONLINE
Created HNSW index COURSE_HELP_CHUNKS_IDX in 0.0 s (M 16, efConstruction 100, efSearch 100).
Sample mode only records the settings; its searches stay exact. Run without --sample to use a real index.

Made 20,000 synthetic vectors of 384 numbers; company code 3000 holds 106 of them (0.5%). 50 test questions, top 10.
Loaded in xx.x s.
Built the HNSW index in x.x s.

search                      company code  ms per query  recall@10
exact (NO_VECTOR_INDEX)              all          xx.x       1.00
exact (NO_VECTOR_INDEX)             3000          xx.x       1.00
HNSW index                           all          xx.x       0.9x
HNSW index                          3000          xx.x       x.xx

Dropped COURSE_BENCH_VECTORS (its index goes with it). Use --keep to look at it first.

Made 20,000 synthetic vectors of 384 numbers; company code 3000 holds 106 of them (0.5%). 50 test questions, top 10.
Sample mode: exact top 10 for all questions in Python took 0.04 s.
The index comparison needs SAP HANA Cloud. Run without --sample. It would send:
  CREATE HNSW VECTOR INDEX COURSE_BENCH_VECTORS_IDX ON COURSE_BENCH_VECTORS (EMBEDDING) SIMILARITY FUNCTION COSINE_SIMILARITY BUILD CONFIGURATION '{"M": 16, "efConstruction": 100}' SEARCH CONFIGURATION '{"efSearch": 100}' ONLINE
  SELECT TOP 10 ID, PROCESS, COMPANY_CODE, TEXT, COSINE_SIMILARITY(EMBEDDING, TO_REAL_VECTOR(?)) AS SCORE FROM COURSE_BENCH_VECTORS WHERE (COMPANY_CODE = ? OR COMPANY_CODE = '*') ORDER BY SCORE DESC WITH HINT (NO_VECTOR_INDEX)

Database version: sample (no database)
COURSE_HELP_CHUNKS: 12 rows, embedding model(s): toy-hash-384
Vector data: about 18 KB (12 rows x 384 numbers x 4 bytes, before index, text and other columns)

# SKETCH: needs pip install langchain-hana, a running SAP HANA Cloud instance
# (Set up for Unit 7) and a LangChain embeddings object named embeddings.
import os
from dotenv import load_dotenv
from hdbcli import dbapi
from langchain_hana import HanaDB

load_dotenv()
connection = dbapi.connect(address=os.getenv("HANA_DB_ADDRESS"), port=int(os.getenv("HANA_DB_PORT")),
                           user=os.getenv("HANA_DB_USER"), password=os.getenv("HANA_DB_PASSWORD"))
store = HanaDB(connection=connection, embedding=embeddings, table_name="HELP_NOTES",
               specific_metadata_columns=["company_code"])     # a real column, faster to filter
store.add_texts(["Company code 1000: orders above the credit limit are blocked."],
                metadatas=[{"company_code": "1000", "process": "order-to-cash"}])
store.create_hnsw_index(m=16, ef_construction=100, ef_search=100)
hits = store.similarity_search("why is my order blocked", k=3,
                               filter={"company_code": {"$in": ["1000", "*"]}})

-- SKETCH: needs NLP enabled on the instance; model ID as listed in SAP's hana-ml documentation.
SELECT TOP 3 ID, TEXT,
       COSINE_SIMILARITY(EMBEDDING,
                         VECTOR_EMBEDDING('why is my order blocked', 'QUERY', 'SAP_NEB.20240715')) AS SCORE
  FROM HELP_NOTES_NEB
 ORDER BY SCORE DESC