bamaer opened a new pull request, #8463:
URL: https://github.com/apache/hop/pull/8463
…that support them
Fixes #8411
`IDatabase.getFieldDefinition()` had no case for the Vector value type added
in #8409, so a vector
field became a text column and the user had to hand-edit the generated DDL
to `vector(1536)`.
Six dialects now emit their own vector column, and three of them needed a
value binding as well:
the DDL alone would have produced a table Hop could not then insert into.
| Dialect | Dimension known | Dimension unknown | Binding |
|---|---|---|---|
| PostgreSQL | `VECTOR(n)` | `VECTOR` | yes, unspecified type |
| Oracle 23ai | `VECTOR(n, FLOAT32)` | `VECTOR(*, *)` | no |
| SQL Server 2025 | `VECTOR(n)` | text | no |
| MySQL 9 | `VECTOR(n)` | text | yes, little endian float32 |
| CrateDB | `FLOAT_VECTOR(n)` | text | yes, float array |
| DuckDB | `FLOAT[n]` | `FLOAT[]` | no |
The dimension comes from the field's length, which is what `ValueMetaVector`
already documents it as
carrying. Where a database has no unsized form of its vector type, a field
without a dimension keeps
today's behaviour and goes to text.
Availability is decided per server, not per dialect. Oracle and SQL Server
compare the version the
driver reports; PostgreSQL cannot, because pgvector is an extension and the
same server has the type
in one database and not the next, so the dialect asks the driver's type list
instead. With no
connection the declared type stands, so DDL generated offline is still
native.
MariaDB, Doris and CockroachDB take their parent dialect's rules **without**
the vector ones, each
with a comment saying why, and CrateDB claims its own `FLOAT_VECTOR` ahead
of the inherited pgvector
type. None of them inherits a column type its server would reject.
### Also fixed
A vector's length is its number of dimensions, not a number of characters.
The fallback for a type a
dialect cannot spell took it for a column width, so a four dimension vector
became `VARCHAR(4)` —
a column no vector of that size fits in. It now falls back to the widest
text the database has, on
every dialect.
### Known limitation
The MySQL and CrateDB bindings turn on the same condition as the column, a
known dimension, because
that is all a binding can see: it is chosen from the dialect and the value,
never from the column it
is about to write into. Pointing Table Output at a pre-existing table whose
column is text, with a
dimension set on the field, therefore writes the binary form into a text
column. PostgreSQL is not
affected, because the unspecified type it sends works for both. The Vector
type has not been in a
release yet, so no existing pipeline can be in that position.
### Not done, deliberately
**Four candidate dialects are not implemented**: ClickHouse
(`Array(Float32)`), Snowflake
(`VECTOR(FLOAT, n)`), SingleStore (`VECTOR(n)`) and BigQuery
(`ARRAY<FLOAT64>`). Every dialect here
was verified against a live server, and three of the six turned out to
reject the value Hop sends
even though they accept the DDL. Shipping the other four on documentation
alone would risk exactly
that: a column that is created and then cannot be written to. Each is a
small, self-contained change
for someone who has that database — the write rule is a few lines, and the
thing to check is not the
CREATE TABLE but the INSERT, through a JDBC bind rather than a SQL literal.
**Reading a vector column back is only wired up for PostgreSQL.** Elsewhere
a vector column still
comes into Hop as text or binary, so on those dialects a vector can be
written natively and not read
back as one. The rule is three lines per dialect
(`ColumnTypeRules.vectorColumn`), but it needs the
same per-server verification.
**The dimension is not recovered on the read path.** JDBC does not carry it:
PostgreSQL reports a
precision of 2147483647 for both a `vector(1536)` and an unsized one,
because the dimension lives in
the catalog. So a table-to-table copy of a `vector(1536)` column produces an
unsized `vector` on the
target. Closing that needs a dialect-specific catalog query at the point row
metadata is built,
which is outside the type-rule API as it stands.
**Nothing populates a dimension automatically.** Hop has no embedding
transform, and the pgvector
Upsert keeps its own `embeddingDimensions` option and its own DDL rather
than reading the dimension
off the field, so unless a user sets the length by hand the unsized path is
what they get. Those two
paths are worth reconciling separately.
### Testing
- `*VectorTypeRulesTest` in each of the six dialects: column type, version
or extension gate, binding.
- A `VECTOR` row added to `BaseFieldDefinitionGoldenTest`, so all 47
dialects record what they emit.
The golden files gained 705 lines and deleted none: no existing column
definition changed. The row
also makes an accidental inheritance visible as a diff, which is how the
CrateDB case was caught.
- Integration tests, one per dialect, each asserting the generated column
type *and* that rows
actually land in it:
- `pgvector/main-0007-vector-ddl` — sized, unsized, round trip
- `database/main-0044-vector-ddl` — MySQL, which is what guards the binding
- `database/main-0045-vector-ddl-no-extension` — a PostgreSQL without
pgvector gets text, and
still writes
- `mssql/main-0009-vector-ddl` — the version gate against two real
servers: 2025 gets VECTOR,
2022 gets varchar
- `cratedb/main-0005-vector-ddl` — FLOAT_VECTOR through the CrateDB driver
- `duckdb/main-0007-vector-ddl` — FLOAT[3]
- `oracle/main-0009-vector-ddl` — sized and unsized
Two of these paid for themselves immediately. The PostgreSQL binding was
missing and the pgvector
test found it: the table was created and then every insert was refused
with "column is of type
vector but expression is of type character varying". The CrateDB binding
was sending a boxed
`Float[]`, which pgjdbc accepts and the CrateDB driver does not; only
running against the real
driver showed it.
Docs updated in `data-types.adoc`, which previously stated that no dialect
emits a native vector
column type.
**Please** add a meaningful description for your change here
------------------------
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]
