cql-import dynamic map Entries in cassandra

Question

I have 2 mysql tables as given below

Table Employee:

id int, name varchar

Table Emails

emp_id int, email_add varchar

Table Emails & Employee are connected by employee.id = emails.emp_id

I have entries like:

mysql> select * from employee;

id       name
1         a    
2         b   
3         c   

mysql> select * from emails;

  empd_id  emails         

   1      [email protected]   
   1      [email protected]  
   1      [email protected] 
   2      [email protected]   
   2      [email protected]  
   3      [email protected]   

6 rows in set (0.02 sec)

Now i want to import data to cassandra in below 2 formats

---format 1---

table in cassandra : emp_details:

id , name , email map{text,text}

i.e. data should be like

1 , a, { 'email_1' : '[email protected]' , 'email_2 : '[email protected]' ,'email_3' :'[email protected]'}

2 , b , {'email_1' :'[email protected]' ,'email_2':'[email protected]'}

3, c, {'email_1' : '[email protected]'}

---- format 2 ----

i want to have the dynamic columns like

id , name, email_1 , email_2 , email_3 .... email_n

Please help me for the same. My main concern is to import data from mysql into above 2 formats.

Answer 1

Edit: change list to map

Logically, you don't expect an user to have >1000 emails, I would suggest to use Map<text, text> or even List<text>. It's a good fit for CQL collections.

CREATE TABLE users (
   id int,
   name text,
   emails map<text,text>,
   PRIMARY KEY(id)
);

INSERT INTO users(id,name,emails)
VALUES(1, 'a', {'email_1': '[email protected]', 'email_2': '[email protected]', 'email_3': '[email protected]'});