0

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      aa@gmail.com   
   1      aaa@gmail.com  
   1      aaaa@gmail.com 
   2      bb@gmail.com   
   2      bbb@gmail.com  
   3      cc@gmail.com

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' : 'aa@gmail.com' , 'email_2 : 'aaa@gmail.com' ,'email_3' :'aaaa@gmail.com'}

2 , b , {'email_1' :'bb@gmail.com' ,'email_2':'bbb@gmail.com'}

3, c, {'email_1' : 'cc@gmail.com'}

---- 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.

Abhishek aggarwal
  • 1

1 Answers1

0

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': 'aa@gmail.com', 'email_2': 'bb@gmail.com', 'email_3': 'cc@gmail.com'});
doanduyhai
  • 8,712
  • 27
  • 26
  • thanks doan, i want the map with key name same as the column value from mysql . i don't want to fix the key name inside the map . it should come from the mysql . Something like 2 , b , {'email_1' :'bb@gmail.com' ,'email_2':'bbb@gmail.com'} where email_1 is also coming from mysql db – Abhishek aggarwal Feb 05 '16 at 06:01