taozhiyi13 commented on issue #12832:
URL: https://github.com/apache/seatunnel/issues/12832#issuecomment-6266254913
Thanks for the feedback. I agree that this is a cross-connector data
contract rather than only an OpenSearch field mapping.
I have prepared a local implementation to validate the design, but I will
not open the PR until the contract and scope are discussed here.
## Goal
The goal is to support end-to-end synchronization of MySQL spatial columns
to Elasticsearch and OpenSearch through these two paths:
```text
MySQL JDBC Source
→ SeaTunnel
→ Elasticsearch Sink
→ Elasticsearch / OpenSearch
```
```text
MySQL-CDC / Debezium
→ SeaTunnel
→ Elasticsearch Sink
→ Elasticsearch / OpenSearch
```
The first implementation focuses only on:
- MySQL JDBC Source;
- MySQL-CDC snapshot and binlog;
- SeaTunnel Zeta;
- the existing Elasticsearch Sink writing to Elasticsearch/OpenSearch.
It does not attempt to introduce a general-purpose spatial framework.
## Current problem
SeaTunnel currently has no end-to-end representation for MySQL spatial
values.
- Generic MySQL `GEOMETRY` is treated as `BYTES`.
- Concrete types such as `POINT`, `LINESTRING`, and `POLYGON` are not
handled consistently during schema conversion.
- Debezium emits geometry values as `Struct` objects containing WKB and
SRID, which the current `BYTES` converter cannot deserialize.
- The Elasticsearch Sink cannot determine whether a field should be
`geo_point` or `geo_shape`.
- OpenSearch dynamic mapping interprets geographic JSON as an ordinary
object rather than a geo field.
## Minimal SeaTunnel geometry contract
I propose one minimal carrier type:
```text
SqlType.GEOMETRY
GeometryType
kind: GEOMETRY / POINT / LINESTRING / POLYGON /
MULTIPOINT / MULTILINESTRING / MULTIPOLYGON /
GEOMETRYCOLLECTION
Geometry
srid: unsigned 32-bit SRID represented as long
wkb: standard 2D WKB
```
The column kind is stored in `GeometryType`. The SRID is stored in each row
value because a MySQL column without a declared SRID may contain values with
different SRIDs.
When MySQL reports a column-level `SRS_ID`, it is also retained as column
metadata for early sink validation.
This is only a data carrier between source and sink. It does not add spatial
functions, predicates, coordinate conversion, or a generic GIS API.
Using `BYTES` is insufficient because it does not preserve the column kind
or provide an explicit SRID contract, and Debezium does not emit MySQL geometry
as an ordinary byte array.
Please confirm whether this minimal carrier in `seatunnel-api` is acceptable.
## Source behavior
Both MySQL source paths should preserve the original geometry without
target-specific restrictions.
### MySQL-CDC
Debezium emits:
- MySQL `POINT` as Debezium `Point`;
- other MySQL spatial types as Debezium `Geometry`.
Both contain standard WKB and SRID.
SeaTunnel converts them to the proposed `Geometry` value:
- SQL `NULL` remains `null`;
- Debezium `srid = null` is restored as MySQL SRID `0`;
- other SRIDs are restored as unsigned 32-bit values;
- WKB is preserved without coordinate modification.
The same conversion is used by snapshot and binlog records.
### MySQL JDBC Source
MySQL JDBC returns its internal geometry format:
```text
4-byte little-endian SRID + standard WKB
```
The JDBC Source removes the four-byte prefix, restores the unsigned SRID,
and produces the same `Geometry` value as MySQL-CDC.
The JDBC adapter is included because MySQL-CDC and MySQL JDBC Source share
`MySqlCatalog` and `MySqlTypeConverter`. Changing the MySQL spatial type
mapping without adapting JDBC Source would break JDBC reads of the same columns.
This does not add JDBC Sink support or modify other JDBC dialects.
### Source responsibility
MySQL-CDC and MySQL JDBC Source:
- preserve the original geometry kind, SRID, and WKB;
- accept every MySQL-valid unsigned 32-bit SRID;
- do not apply target-specific SRID restrictions;
- do not relabel or transform coordinates.
## Coordinate order
No axis swapping is performed.
MySQL's internal geographic representation stores longitude in X and
latitude in Y. Debezium WKB and JDBC geometry bytes preserve this
representation.
The sink therefore uses:
```text
X → longitude
Y → latitude
```
For example:
```text
X = 121.5
Y = 31.2
```
is written as:
```json
{
"lon": 121.5,
"lat": 31.2
}
```
## Elasticsearch/OpenSearch Sink behavior
The target mapping is:
| MySQL geometry kind | Elasticsearch/OpenSearch field |
|---|---|
| `POINT` | `geo_point` |
| All other geometry kinds | `geo_shape` |
A generic `GEOMETRY` column uses `geo_shape` because different rows may
contain different geometry kinds.
`geo_point` values are written as longitude/latitude objects. `geo_shape`
values are converted from WKB to GeoJSON.
When SeaTunnel creates a new index, it explicitly creates the geo mappings
before writing the first document. Dynamic mapping cannot infer `geo_point` or
`geo_shape`.
If the target index already exists, it must already contain compatible
mappings.
## SRID responsibility
MySQL sources preserve all valid SRIDs. Target compatibility is decided by
the sink.
Elasticsearch/OpenSearch geo fields do not store an SRID and interpret
geographic coordinates as WGS84 longitude and latitude.
Therefore, the Elasticsearch Sink:
- accepts only SRID `4326`;
- does not transform coordinates;
- does not silently change an SRID;
- rejects incompatible values with a clear error.
The validation behavior is:
- a column declares SRID `4326` → sink creation succeeds;
- a column declares another SRID → sink creation fails before reading source
data;
- a column does not declare an SRID → sink creation succeeds and each row is
checked when written;
- a row contains a non-4326 SRID → writing fails with the column name and
actual SRID.
Coordinate transformation must be completed before data reaches this sink
and is outside this proposal.
## Malformed geometry handling
The sink accepts standard 2D WKB types 1 through 7 in either byte order.
It rejects malformed data before sending the document to
Elasticsearch/OpenSearch, including:
- unsupported WKB type codes;
- truncated WKB;
- trailing bytes;
- invalid byte-order markers;
- invalid nested geometry types;
- a non-Point value in a `POINT` column.
Errors contain the source column name.
MySQL permits an empty geometry collection, while Elasticsearch/OpenSearch
rejects an empty `geo_shape`. The current proposal writes an empty geometry
collection as `null` and documents this limitation.
## Out of scope
The first implementation does not include:
- coordinate transformation or reprojection;
- spatial transforms or functions;
- MySQL JDBC Sink;
- Elasticsearch/OpenSearch Source;
- PostgreSQL/PostGIS or other spatial sources;
- Flink/Spark spatial translation;
- manually declared `GEOMETRY(...)` schemas;
- runtime spatial schema evolution.
## Validation plan
The PR will include deterministic tests for:
- all eight MySQL spatial types;
- MySQL-CDC snapshot;
- binlog `INSERT`, `UPDATE`, and `DELETE`;
- MySQL JDBC Source;
- SQL `NULL`;
- unsigned 32-bit SRIDs;
- `POINT → geo_point`;
- other kinds → `geo_shape`;
- explicit index mappings;
- representative geo queries;
- declared non-4326 SRID rejection during sink creation;
- undeclared columns with non-4326 row values failing during writing;
- malformed WKB handling.
Before opening the PR, I will extend the real MySQL-CDC →
Elasticsearch/OpenSearch fixture so all eight spatial types are covered by the
snapshot/binlog path, not only by unit tests.
The current OpenSearch E2E is verified with OpenSearch 2.19.1. Tested
versions will be distinguished from expected protocol-compatible versions.
## Decisions requested
Please confirm whether the following scope is acceptable:
1. Add the minimal `GEOMETRY` carrier in `seatunnel-api`.
2. Support both MySQL-CDC and MySQL JDBC Source because they share the MySQL
catalog and type converter.
3. Keep coordinate transformation, JDBC Sink, other databases, and other
execution engines outside the first implementation.
I will adjust the implementation based on this discussion before opening the
PR.
~~~
--
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]
