bamaer opened a new pull request, #8428: URL: https://github.com/apache/hop/pull/8428
Fixes #8422 Adds two transforms for PostgreSQL with the [pgvector](https://github.com/pgvector/pgvector) extension: **pgvector upsert** (write chunks and their embeddings) and **pgvector search** (top-k similarity lookup). Registered as an optional Technology plugin. ### Why two transforms rather than the existing rdbms transforms The write half is arguably close to what `Table Output` / `Insert / Update` can do. The read half is not: similarity search needs pgvector's own operators, and each metric maps to one plus a score expression. ```java COSINE("<=>", "1 - (embedding <=> ?::vector)") L2("<->", "1 / (1 + (embedding <-> ?::vector))") INNER_PRODUCT("<#>", "(-1 * (embedding <#> ?::vector))") ``` No generic transform emits `ORDER BY embedding <=> ?::vector LIMIT k`. Doing it by hand in Database Join or Dynamic SQL row means writing it once per metric, with no `topK`, no `minScore` and no structured match fields. Index creation is likewise outside any generic transform — the upsert generates `CREATE INDEX ... USING hnsw (embedding <ops>)`, where the operator class follows the distance metric. ### Relationship to the Vector value type (#8409) These transforms have **no dependency** on the Vector value type: no pom entry, no imports. They compose through a shared data shape instead. `EmbeddingJsonParser.toPgVectorLiteral` dispatches on the runtime value, and `ValueMetaVector`'s native storage is `float[]`: ```java if (value instanceof float[] vector) { return toPgVectorLiteral(vector); } // typed field ... return toPgVectorLiteral(String.valueOf(value)); // String/JSON: parsed and re-rendered ``` So a Vector-typed field takes the fast path with no parsing, while a String or JSON field still works. The plugin is useful with or without #8409, and neither PR blocks the other. ### Testing - 29 unit tests - New integration test project `integration-tests/pgvector`, with `docker/integration-tests/integration-tests-pgvector.yaml` on `pgvector/pgvector:pg16`. A stock postgres image cannot create a `vector` column, which is why these tests do not run in the shared `database` project. An init script enables the extension on first start. - `main-0001-pgvector.hwf` upserts three chunks with 3-dimensional embeddings, letting the transform create its table, then runs a cosine search and compares against a golden set. The match score is deliberately not compared: it is a floating point distance. ### Notes - Registered in the `full` profile, alongside `hop-tech-redis`, `neo4j`, `mongodb` and `elastic`. Those four also have enabled IT projects, and I could not find anything in `Jenkinsfile.daily` or `run-tests-docker.sh` that activates `-Pfull`. Flagging in case `full`-profile ITs do not currently get their plugin in CI — this PR follows the same pattern either way. - No sample pipeline: a meaningful one needs a live database. `hop-tech-elastic` ships none either. Happy to add one if reviewers prefer. - Unrelated, found while building the IT project: `cratedb` and `azure` both have the description text in `executionInfoLocationName` in their `local.json` run configurations, which makes Hop log `Execution information location ... could not be found`. Not fixed here; worth a separate issue. ------------------------ Thank you for your contribution! Follow this checklist to help us incorporate your contribution quickly and easily: - [x] Run `mvn clean install apache-rat:check` to make sure basic checks pass. A more thorough check will be performed on your pull request automatically. - [x] If you have a group of commits related to the same change, please squash your commits into one and force push your branch using `git rebase -i`. - [x] Mention the appropriate issue in your description (for example: `addresses #123`), if applicable. To make clear that you license your contribution under the [Apache License Version 2.0, January 2004](http://www.apache.org/licenses/LICENSE-2.0) you have to acknowledge this by using the following check-box. - [x] I hereby declare this contribution to be licensed under the [Apache License Version 2.0, January 2004](http://www.apache.org/licenses/LICENSE-2.0) - [ ] In any other case, please file an [Apache Individual Contributor License Agreement](https://www.apache.org/licenses/icla.pdf). -- This is an automated message from the Apache Git Service. To respond to the message, please log on to GitHub and use the URL above to go to the specific comment. To unsubscribe, e-mail: [email protected] For queries about this service, please contact Infrastructure at: [email protected]
