Changin Composite Key in Cassandra

Question

I want to change my cassandra table. I know that if i need to add extra primary key, i have to create another table. For example, I want to turn this table

        create table sample1 (
              p1 int,
              p2 timestamp,
              c1 text,
              c2 text,
              c3 text,
              c4 text,
              x1 int,
              x2 int,
              x3 int
              PRIMARY KEY((p1,p2),c1,c2,c3,c4)      
        );

into this table

        create table sample1 (
              p1 int,
              p2 timestamp,
              c1 text,
              c2 text,
              c3 text,
              c4 text,
              c5 text,
              c6 text,
              x1 int,
              x2 int,
              x3 int
              PRIMARY KEY((p1,p2),c1,c2,c3,c4,c5,c6)      
        );

This situation will be happened again, i will be have to change this table again.

I am considering to create table like this. Do you think that is it best approach? Or is there another way?

create table sample1 (
          p1 int,
          p2 timestamp,
          combined_six_field text,
          c1 text,
          c2 text,
          c3 text,
          c4 text,
          c5 text,
          c6 text,
          x1 int,
          x2 int,
          x3 int
          PRIMARY KEY((p1,p2), combined_six_field)      
    );

What type of select query you want to use ? Show us, some sample of your select query. — Ashraful Islam
Select * from sample1 where p1=x and p2=y I just use other fields for unique information. — Yılmaz
Don't you need to query with clustering key ? like select * from sample1 where p1=x and p2=y and c1 ='z' — Ashraful Islam

Ashraful Islam Ashraful Islam · Accepted Answer · 2017-08-04T15:35:00

You can use frozen map as clustering column.

Example :

CREATE TABLE sample1 (
      p1 int,
      p2 timestamp,
      c frozen<map<text, text>>,
      x1 int,
      x2 int,
      x3 int
      PRIMARY KEY((p1,p2), c)      
);

Insert Example :

INSERT INTO sample1(pk1, pk2, c, x1, x2, x3) VALUES (
     1,
     toTimestamp(now()),
     {'c1' : 'v1', 'c2' : 'v2', 'c3' : 'v3'},
     10,
     100,
     1000
);

INSERT INTO sample1(pk1, pk2, c, x1, x2, x3) VALUES (
     2,
     toTimestamp(now()),
     {'c1' : 'v1', 'c2' : 'v2', 'c3' : 'v3', 'c4' : 'v4', 'c5' : 'v5', 'c6' : 'v6'},
     20,
     200,
     2000
);

Changin Composite Key in Cassandra

1 Answers