Data Migration from Mysql to Cassandra

Question

I need help into below situation.

I have a MySQL table :-

CREATE TABLE employees (
    emp_no      INT             NOT NULL,  -- AUTO_INCREMENT??
    birth_date  DATE            NOT NULL,
    first_name  VARCHAR(14)     NOT NULL,
    user_id     INT             NOT NULL,   -- This is primary key of another table.   
    PRIMARY KEY (emp_no));

This table have approx 20000 records.

I am transferring this MySQL table to Cassandra table which is as below.

CREATE TABLE employees (
    emp_no      uuid,
    birth_date  timestamp,
    first_name  text,
    user_id uuid,
    PRIMARY KEY (emp_no));

Now i want to export all 20000 records from MySQL table to Cassandra table.

user_id column is primary key in another table and first_name column is mapped with user_id column also.

Please let me know how can i achieve this .

Below are the details for versions of datastax which I am using. [cqlsh 5.0.1 | Cassandra 2.1.15.1403 | DSE 4.8.9 | CQL spec 3.2.1 | Native protocol v3]

Please suggest me best possible way to data migration from MySQL to Cassandra.

Reveka Reveka · Accepted Answer · 2016-08-31T07:08:02

The datastax scenario above is great in your case. An alternative though would be to create a small java project that does that for you. In your case your model from the mysql table to the cassandra table is identical. So you could do the following:

Create a java project with hibernate and datastax driver
Create a java class for employees
Map employees to mysql through hibernate xml mapping (I prefer xml because then I can use the same java model for cassandra)
Map employees class to the cassandra table through datastax driver annotations
Use hibernate session to bring your employees in batches
Use datastax driver to save all the employees in the batch into cassandra.

Your employees class should look like this

@Table(keyspace = "your_keyspace", name ="employees")
public class Employee
{
    @PartitionKey(0)
    @Column(name = "emp_no")
    protected UUID employeeNo;

    @Column(name = "birth_date")
    protected Timestamp birthDate;

    @Column(name = "first_name")
    protected String firstName;

    @Column(name = "user_id")
    protected UUID userId;
}

Of course Java is not your only option. Datastax driver is available for many languages like C# or python.

Data Migration from Mysql to Cassandra

2 Answers