Search code examples
cassandranosqlcql

Hard time understanding Cassandra query

In Cassandra, I understand that tables are supposed to be created according to what needs to be queried. For example, I currently have a Users and Users_By_Status table.

##Users##
CREATE TABLE Users (
    user_id uuid,
    name text,
    password text,
    status int,
    username text,
    PRIMARY KEY (user_id) 
);
CREATE INDEX user_username_idx ON Users (username);

##Users_By_Status##
CREATE TABLE Users_By_Status (
    username text,
    status int,
    user_id uuid,
    PRIMARY KEY (username, status, user_id) 
);

In this case, if a user leaves, their record won't be deleted. Instead, status will be changed from 1 to 0.

If I insert data into the Users table, do I need to manually insert the data into Users_By_Status table too? What happens if I update the status in Users? Do I need to manually update the record in Users_By_Status table too?

I have a feeling I'm understanding Cassandra wrongly. Appreciate all the help I can get.

Solution

  • Shortly answer: yes, in your case you need to delete manually. In cassandra db you need to write more code in your app layer to handle cenarios like that.

    But we have other options like materialized view or BATCH Statements.

    For your solution, i think that materialized view is the best option. You can create a Materialized view from your table Users. Like this:

    CREATE MATERIALIZED VIEW Users_By_Status
AS SELECT username, status, userid 
FROM Users
PRIMARY KEY(username, status, userid);

    And yes, when you update table users, the update will happen in the Materialized View Users_By_Status too.

    Reference: https://docs.datastax.com/en/cql-oss/3.3/cql/cql_using/useCreateMV.html