How to do a join queries with 2 or more tables in cassandra cql

Question

I am new to cassandra. Here I have two tables EVENTS and TOWER. I need to join those for some queries. But I'm not enable to do it.

Structure of EVENTS table:

eid int PRIMARY KEY,
a_end_tow_id text,
a_home_circle text,
a_home_operator text,
a_imei text,
a_imsi text,

Structure of TOWER table:

 tid int PRIMARY KEY,
 tower_address_1 text,
 tower_address_2 text,
 tower_azimuth text,
 tower_cgi text,
 tower_circle text,
 tower_id_no text,
 tower_lat_d text,
 tower_long_d text,
 tower_name text,

Now, I want to join these table with respect to EID and TID so that I can fetch the data of both tables.

Answer 1

Cassandra = No Joins. Your model is 100% relational. You need to rethink it for Cassandra. I would advice you take a look at these slides. They dig deep into how to model data for cassandra. Also here is a webinar covering the topic. But stop thinking foreign keys and joining tables, because if you need relations cassandra isn't the tool for the job.

But Why?
Because then you need to check consistency and do many other things that relational databases do and so you loose the performance and scalability that cassandra offers.

What can I do?
DENORMALIZE! Lots of data in one table? But the table will have too many columns!
So? Cassandra can handle a very large number of columns in a table.

The other thing you can do is to simulate the join in your client application. Match the two datasets in your code, but this will be very slow because you'll have to iterate over all your information.

Another way is to carry out multiple queries. Select the event you want, then the matching tower.

Answer 2

There are a couple of ways that you can join tables together in Cassandra and query them. But of course you have to rethink the data model part.

1. Use Apache Spark’s SparkSQL™ with Cassandra (either open source or in DataStax Enterprise – DSE).
2. Use DataStax provided ODBC connectors with Cassandra and DSE.