I'm trying to create an Elasticsearch join between two tables using a UUID field.
My main table looks something like this:
CREATE TABLE main_table (
id UUID PRIMARY KEY,
...other fields...
);
The child table includes a reference back to the main table:
CREATE TABLE child_table (
child_id UUID PRIMARY KEY,
main_id UUID,
...other fields...
);
I use Elassandra to get the tables and indexes built, although here I show the curl -XPUT ... commands as I can reproduce the problem that way too (so the problem is not in Elassandra or express-cassandra):
curl -XPUT \
-H 'Content-Type: application/json' \
"http://127.0.0.1:9200/my_workspace_main_table/?pretty" \
-d '{
"settings": {
"keyspace": "'my_workspace'",
"index": {
"number_of_shards": 1
}
},
"index": {
"number_of_shards": 1
}
}'
# NOTE: In my script I sleep for a little while here to make sure
# the index is built or I get a 404 Index Not Found error
Now that we have an index, we can build the mapping with another PUT like so:
curl -XPUT \
--max-time 3600 \
-H 'Content-Type: application/json' \
"http://127.0.0.1:9200/my_workspace_main_table/_mapping/main_table/?pretty" \
-d '{
"properties": {
"id": {
"type": "join",
"relations": {
"question": "parent_id"
},
"eager_global_ordinals": true
}
}
}'
Here I set the type to "join" which means I can't specify "cql_collection": "singleton" for my column. Because of that, I get an error:
{ "error" : { "root_cause" : [ { "type" : "i_o_exception", "reason" : "Existing column [id] type [uuid] mismatch with inferred type [list<text>]" } ], "type" : "i_o_exception", "reason" : "Existing column [id] type [uuid] mismatch with inferred type [list<text>]", "caused_by" : { "type" : "i_o_exception", "reason" : "Existing column [id] type [uuid] mismatch with inferred type [list<text>]" } }, "status" : 500 }
Is there a way to bypass this error? I don't want to have to use a list<text> for my UUIDs.
Note that I tested and changing my column type to list<text> does the job and when I do that I get the following mapping:
{
"my_workspace_t16" : {
"mappings" : {
"my_table" : {
"properties" : {
"id" : {
"type" : "join",
"eager_global_ordinals" : true,
"relations" : {
"question" : "parent_id"
}
}
}
}
}
}
}
If you know the reason behind this weird inferred type (if you ask me,) I would be delighted to hear about. A link to a page that explains that choice would be wonderful.
I also tested with the keyword type and the following works as expected, but then I don't get a join, do I?
curl -XPUT \
--max-time 3600 \
-H 'Content-Type: application/json' \
"http://127.0.0.1:9200/my_workspace_main_table/_mapping/main_table/?pretty" \
-d '{
"properties": {
"id": {
"type": "keyword",
"cql_collection": "singleton"
}
}
}'