CODEWITHSUNDEEP
mysqldatabasedatastaxdatastax-enterprisedatabase-administration
Big Data Guy
Big Data Guy

Reputation: 248

Data Migration from Mysql to Cassandra

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.

Upvotes: 1

Views: 4210

Answers (2)

Ish
Ish

Reputation: 2103

Refer : Migrate RDBMS to Cassandra

More info: https://docs.datastax.com/en/cql/3.1/cql/cql_reference/copy_r.html

Please convert your mysql data into CSV and import into cassandra table. This works I have checked this.

Upvotes: 0

Reveka
Reveka

Reputation: 101

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:

  1. Create a java project with hibernate and datastax driver
  2. Create a java class for employees
  3. Map employees to mysql through hibernate xml mapping (I prefer xml because then I can use the same java model for cassandra)
  4. Map employees class to the cassandra table through datastax driver annotations
  5. Use hibernate session to bring your employees in batches
  6. 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.

Upvotes: 1

Related Questions