question

Erick Ramirez avatar image
Erick Ramirez asked Erick Ramirez answered

How do I insert data into a table that contains a frozen UDT set collection?

I was asked this question today in ASF Slack:

I need to insert data in set<CustomType> composed of 2 fields, but I can´t see what I am doing wrong
I tried different alternatives and I can´t see how to do it on internet 
CREATE TYPE agency.milestone ( 
    name text, 
    value text 
)
CREATE TABLE agency.travel (    
    travel_id int,
    client_id text,
    milestones set<frozen<milestone>>,
    PRIMARY KEY (travel_id, client_id)
) WITH CLUSTERING ORDER BY (client_id DESC)
How I do the INSERT? 
cqlsh:agency> INSERT INTO travel (travel_id, client_id, milestones)
                 ... VALUES ( '3', 'Bertoldo', {milestone: ['Versailles', '3-05 contacta agencia']} );
InvalidRequest: Error FROM server: code=2200 [Invalid query] message="Invalid user type literal for milestones of type set<frozen<milestone>>"
user-defined type
10 |1000
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 8 attachments (including images) can be used with a maximum of 1.0 MiB each and 10.0 MiB total.

1 Answer

Erick Ramirez avatar image
Erick Ramirez answered

Knowing the format for inserting data into tables with advanced types such as CQL collections and user-defined types (UDTs) isn't always readily obvious. There's an opportunity here to improve the official Apache Cassandra Doc site.

In the meantime, here is the valid INSERT statement for the table:

INSERT INTO travel (id, client, milestones) \
    VALUES ( 1, 'oscar', { {name:'melbourne', value:'3000'} } );

and here's the result:

cqlsh:community> SELECT * FROM travel ;
 id | client | milestones
----+--------+--------------------------------------
  1 |  oscar | {
                 {name: 'melbourne', value: '3000'}}
Share
10 |1000
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 8 attachments (including images) can be used with a maximum of 1.0 MiB each and 10.0 MiB total.