CODEWITHSUNDEEP
cassandracql
localdata01
localdata01

Reputation: 657

Cassandra data modeling understanding

I came from SQL and switched now to CQL. I don't quite understand something yet.

In SQL this works:

Table product
id,
 name, 
 desc,
 price

Table cart
  id 
  item_id

SELECT
  p.name,
  p.desc,
  p.price

FROM product

INNER JOIN cart
ON c.item_id = p.id

But in Cassandra is it different or I am wrong? Can I do his in Cassandra too, or is that bad? How you would query multiple tables in Cassandra?

Upvotes: 1

Views: 145

Answers (2)

Aaron
Aaron

Reputation: 57808

With Cassandra, you'll want to start with building a table to support your query:

SELECT * FROM product
INNER JOIN cart
ON c.item_id = p.id

Of course there aren't any joins, so we'll have to build a table to store that cart-product data:

CREATE TABLE cart_product (
    product_name TEXT,
    product_desc TEXT,
    product_price DECIMAL,
    product_id uuid,
    cart_id uuid,
    qty int,
    PRIMARY KEY (cart_id,product_id)
);

By defining my primary key like this, I'm ensuring that all of my rows will be stored together by cart_id. As it's the first primary key, it becomes the partitioning key. As I intend to have more than one product per cart, product_id needs to be a part of the key as a clustering column to ensure uniqueness.

After inserting some data, this works:

> SELECT product_name,product_price,product_desc 
  FROM cart_product
  WHERE cart_id=93aefdf3-acbf-4d3c-849c-4db9b2ef9e19;

 product_name         | product_price | product_desc
----------------------+---------------+---------------------------------------------------
            Minecraft |         29.99 | Build the future with blocks, and try not to die!
 Kerbal Space Program |         29.99 |         Blast into space.  Now with moar rockets!
       Cyberpunk 2077 |         59.99 |         Wake up Samurai.  We have a city to burn!

(3 rows)

Upvotes: 1

Alex Ott
Alex Ott

Reputation: 87279

Cassandra doesn't have joins, so you can't do it - if you need to perform the join, then it should be done in your application.

Cassandra encourages the data denormalization, when you can copy necessary data into another table to avoid joining when reading data - like, for cart table, you can copy price/description from the product table, and then when displaying the cart you won't need to query the product table.

I recommend to read first chapters of "Casasndra: The Definitive Guide, 3rd edition" book - it's freely available. This will help you with understanding how to model data for Cassandra

Upvotes: 1

Related Questions