Bringing together the Apache Cassandra experts from the community and DataStax.
Want to learn? Have a question? Want to share your expertise? You are in the right place!
Not sure where to begin? Getting Started
I have a scenario where I need to get an attribute from a table and query the second table.
Table 1 will query with a partition key and clustering key to get the attributes and query table 2 passing one of the attributes from table 1 as the key and getting all attributes and returning the (both table 1 and table 2 ) in one transaction.
Is it possible to do it in one transaction?
No, these are two separate tables so no matter what it would take two separate transactions. The only thing that I can think of to speed this up would be to use a prepared statement at least, so you can minimize the round trips.
It sounds like you want to do the equivalent of a SQL JOIN but it isn't supported in Cassandra because they don't scale or perform well particularly as the dataset gets large.
This is a very common question from those used to relational databases where tables are normalised. You do the opposite in Cassandra data modelling -- denormalise -- because the tables are then optimised for very fast reads.
My recommendation is to design a single table that provides the results required by your application query. Let me illustrate with an example using these 2 tables of videos and users:
id | title | channel | url ---+-----------------------------------------+--------------+--------------------- v1 | World's Most Dangerous Escape Room | MrBeast | youtu.be/3jS_yEK8qVI v2 | I can't show this... | JJ Olatunji | youtu.be/YT3TVIU_v5I v3 | I Built MrBeast a Giant Chocolate Bar! | Matthew Beem | youtu.be/_xRpMaB6BcE v4 | When you bring McDonald's to school | Jimi Jackson | youtu.be/b3iAKya6a5g v5 | More christmas movies to complain about | Drew Gooden | youtu.be/3HFi09o_iLU
id | username | name | email ---+----------+---------------+---------------- u1 | alice | Alice Bridges | alice@mail.com u2 | bob | Bob Quintana | bob@ping.me u3 | charli | Charli Yang | cyang@rocket.co
Let's say your app wants to get a list of videos watched by users. In a relational database, this would probably be stored as:
id | user_id | video_id ---+---------+--------- 01 | u1 | v1,v3 02 | u2 | v2,v3,v4 03 | u3 | v4,v5
In order to get a list of a user's watched videos and their details, you would need to do a JOIN on the videos table using the video ID as the foreign key.
In Cassandra, this would be very slow as you need to issue multiple requests to the cluster to get the data. Remember that tech giants choose Cassandra because (1) they have a scale problem, and (2) they need the results very fast. Instead of JOINs, we would design a table with all the data in it without having to use foreign keys or IDs. It would look something like:
CREATE TABLE watched_videos_by_user (
username text,
url text,
title text,
channel text,
PRIMARY KEY (username, url)
)
In this example, I've used url as the clustering key because it is universally unique. To get the titles of the videos Alice watched:
cqlsh> SELECT title FROM watched_videos_by_user WHERE username = 'alice'; title ---------------------------------------- World's Most Dangerous Escape Room I Built MrBeast a Giant Chocolate Bar! (2 rows)
Similarly to get details of the videos watched by Bob:
cqlsh> SELECT title, channel, url FROM watched_videos_by_user WHERE username = 'bob'; title | channel | url ----------------------------------------+--------------+---------------------- I can't show this... | JJ Olatunji | youtu.be/YT3TVIU_v5I I Built MrBeast a Giant Chocolate Bar! | Matthew Beem | youtu.be/_xRpMaB6BcE When you bring McDonald's to school | Jimi Jackson | youtu.be/b3iAKya6a5g (3 rows)
If you're interested, we've got lots of free hands-on tutorials on datastax.com/dev. I highly recommend the Data Modeling course. Cheers!
7 People are following this question.
