Migrating SQL applications to Cassandra – Pattern #2

Pattern #2: Get rid of all EXISTS and IN from sql where clauses.

For example, consider a typical SQL query to find all employees who have completed a mandatory information security training:

select ee1.* from employee ee1
where exists (
    select ‘true’ from employee_training et1
    where et1.employeeID = ee1.employeeID
    and et1.courseName = ‘Information Security’
)

split this into two queries:

findTrainedEmployee
select * from employee_training 
where courseName = ‘Information Security’

and

findByEmployeeID
select * from employee
where employeeID = ?

and let the middle-tier object graph builder (I am using Object Persistence API for C*) compose it for you as below:

Collection<Employee> colOfTrainedEmployees = EmployeeTraining.findTrainedEmployees()
->  return Employee.findByEmployeeID(e.getEmployeeID())

The split two queries are Cassandra compliant and EXISTS/IN-logic is now in the middle-tier.

Next > Pattern #3