Migrating SQL applications to Cassandra – Pattern #3

Pattern #3: Get rid of all NOT operators from sql where clauses. For example, consider a typical SQL query to find all employees who have NOT completed a mandatory information security training:

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

Cassandra does not support NOT operator. So the way to make this work is to have NOT-condition data and have Cassandra search for it as below:

findNotTrainedEmployee
select * from employee_training 
where courseName = ‘No Information Security’

and

findByEmployeeID
select * from employee
where employeeID = ?

and let the middle-tier object graph builder (I am using Ohioedge Jeebuilder) compose it for you as below:

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

The split two queries are Cassandra compliant and NOT operator is replaced by additional NOT-condition data in the database.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s