Migration of MySql Tables to Cassandra table

In this section, we will let you know how to migrate MySql table to Cassandra table.
1. Create database in MySql.

create database mysql_to_cassandra ;

2. Create table in MySql.

CREATE TABLE `history` (  `id` varchar(30) DEFAULT NULL,  `city` varchar(30) DEFAULT NULL,  `province` varchar(30) DEFAULT NULL,  `country` varchar(30) DEFAULT NULL,  `zipCode` varchar(30) DEFAULT NULL,  `type` varchar(30) DEFAULT NULL,  `hasAirCondition` varchar(10) DEFAULT NULL,  `hasGarden` varchar(10) DEFAULT NULL,  `hasPool` varchar(10) DEFAULT NULL,  `isCloseToBeach` varchar(10) DEFAULT NULL,  `dailyPrice` float DEFAULT NULL,  `currency` varchar(10) DEFAULT NULL,  `roomsNumber` int(10) DEFAULT NULL,  `created_at` datetime DEFAULT NULL ) ; 

3. Insert one record in the table history;

insert into history( id ,  city ,  province ,  country ,  zipCode ,  type ,  hasAirCondition ,  hasGarden ,  hasPool ,  isCloseToBeach ,  dailyPrice  ,  currency ,  roomsNumber ,  created_at) values ("zZC8U",  "Mumbai", "province2", "india", "122001M", "studio", true, false, true, true, 14.92, "INR", 231, "2016-06-08 00:23:54" ); 

4. Take backup in csv in MySql console as given below.

select id, city, country, created_at, currency, dailyPrice, hasaircondition, hasgarden, isclosetobeach, province, type, zipcode from history INTO OUTFILE '/tmp/cql_cass.csv' FIELDS ENCLOSED BY '"' TERMINATED BY ',' ESCAPED BY '"' LINES TERMINATED BY '\r\n' ; 

5. Now you have backup of mysql table, and then we will create KEYSPACE(acronym of database) & table in Cassandra and import to this table.

6. Login to cqlsh console and create KeySpace as given below.

CREATE KEYSPACE from_mysql WITH replication = {'class': 'SimpleStrategy', 'replication_factor': '1'}  AND durable_writes = true; 

7. Create table in this keyspace/

use from_mysql ; 

;

CREATE TABLE mysql_table_data (id text PRIMARY KEY, city_name text, country text, created_at timestamp, currency text, dailyprice float, hasaircondition boolean, hasgarden boolean, haspool boolean, isclosetobeach boolean, province text, roomsnumber int, type text, zipcode text) ; 

8. Inserting data from the backup, we have created from MySql.

copy mysql_table_data (id, city, country, created_at, currency, dailyPrice, hasaircondition, hasgarden, isclosetobeach, province, type, zipcode ) from '/tmp/cql_cass.csv' ; 

9, You can verify the data using the command given below.

select * from mysql_table_data ;