WXPNG opened a new issue, #12704:
URL: https://github.com/apache/seatunnel/issues/12704
When using the Elasticsearch sink (also used to write to OpenSearch) with a
composite primary key (primary_keys containing more than one column), SeaTunnel
generates the document _id by simply joining the key column values with the
key_delimiter (default '_').
If a key column value itself contains the delimiter character ('_'), two
distinct rows can be mapped to the same _id. Since OpenSearch/Elasticsearch
treats _id as the document identity, the second document silently overwrites
the first one, resulting in data loss.
1. Reproduce steps
Create a MySQL source table with a composite primary key on two string
columns:
```
create table tb_test_pk (
c1 varchar(50),
c2 varchar(50),
c3 int,
c4 varchar(50),
primary key (c1, c2)
);
insert into tb_test_pk values
('1_1', '2', 1, '1'),
('1', '1_2', 2, '2'),
('3_3', '4', 3, '3'),
('3', '3_4', 4, '4');
```
2. Configure the sink with the composite primary key:
```
sink {
Elasticsearch {
hosts = ["http://127.0.0.1:9200";]
index = "tb_test_pk"
primary_keys = ["c1", "c2"]
}
}
```
3. Expected behavior
All 4 rows should be written to the target index, each with a unique _id,
i.e. 4 documents in total.
4. Actual behavior
The generated _ids collide:
c1 c2 generated _id (key_delimiter = _)
1_1 2 1_1_2
1 1_2 1_1_2 <- collision
3_3 4 3_3_4
3 3_4 3_3_4 <- collision
The colliding rows overwrite each other, so only 2 documents remain in
OpenSearch instead of 4.
5. Root cause
In `KeyExtractor.apply` (Elasticsearch connector):
```
for (int i = 0; i < fieldFormatters.length; i++) {
if (i > 0) {
builder.append(keyDelimiter);
}
String value = fieldFormatters[i].format(row);
builder.append(value); // raw value, no escaping / length prefix
}
```
The raw values are concatenated with a delimiter that may also appear inside
the values, so the mapping (c1, c2) -> _id is not injective.
6. Suggested fix
When there is more than one primary key column, make the concatenation
injective, e.g. prefix each value with its length and the delimiter:
```
if (fieldFormatters.length > 1) {
builder.append(value.length()).append(keyDelimiter).append(value);
} else {
builder.append(value);
}
```
This keeps _id unique even when values contain _, for example:
("1_1", "2") -> 3_1_1_1_2
("1", "1_2") -> 1_1_3_1_2
Is the suggested fix (length-prefix encoding) a reasonable approach? Is it
acceptable to submit a PR to fix this issue? If so, I would be happy to
contribute the PR together with unit tests.
--
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]
