Better way to define UDT's in Cassandra database

Question

We are trying to remove 2 columns in a table with 3 types and make them as UDT instead of having those 2 as columns. So we came up with below two options. I just wanted to understand if there are any difference in these two UDT in Cassandra database?

First option is:

CREATE TYPE test_type (
    cid int,
    type text,
    hid int
);

and then using like this in a table definition

test_types set<frozen<test_type>>,

vs

Second option is:

CREATE TYPE test_type (
    type text,
    hid int
);

and then using like this in a table definition

test_types map<int, frozen<test_type>

So I am just curious which one is a preferred option here for performance related or they both are same in general?

Answer 1

It's really depends on how will you use it - in the first solution you won't able to select element by cid, because to access the set element you'll need to specify the full UDT value, with all fields.

The better solution would be following, assuming that you have only one collection column:

CREATE TYPE test_type (
    type text,
    hid int
);

create table test (
  pk int, 
  cid int
  udt frozen<test_type>,
  primary key(pk, cid)
);

In this case:

• you can easily select individual element by specifying the full primary key. The ability to select individual elements from map is coming only in Cassandra 4.0. See the CASSANDRA-7396. Until that you'll need to get full map back, even if you need one element, and this will limit you on the size of the map
• you can even select the range of the values, using the range query
• you can get all values by specifying only partition key (pk in this example)
• you can select multiple non-consecutive values by doing select * from test where pk = ... and cid in (..., ..., ...);

See the "Check use of collection types" section in the data model checks best practices doc.