Cassandra DB Inserting data into multiple tables

Question

I have been reading the documentation of Cassandra Db on datastax as well as Apache docs. So far I have learned that we cannot create more than one index (one primary, one secondary index) on a table. And there should be an individual table for each query. Comparing this to an SQL table for example one on which we want to query 4 fields, for this table in case of Cassandra we should split this table into 4 tables right? ( please correct me if I am wrong ). on these 4 tables I can have the indexes and make the queries, My question is How can we insert data into these 4 tables, should I make 4 consecutive insert requests?

my priority is to avoid secondary index

user_4269535 · Answer

To keep data in sync across denormalised tables, you need to use CQL BATCH statements.

For example, if you had these tables to maintain:

movies
movies_by_actor
movies_by_genre

then you would group the updates in a CQL BATCH like this:

BEGIN BATCH
  INSERT INTO movies (...) VALUES (...);
  INSERT INTO movies_by_actor (...) VALUES (...);
  INSERT INTO movies_by_genre (...) VALUES (...);
APPLY BATCH;

Note that it is also possible to do UPDATE and DELETE statements as well as conditional writes in a batch.

The above example is just to illustrate it in cqlsh and is not used in reality. Here is an example BatchStatement using the Java driver:

SimpleStatement insertMovies =
  SimpleStatement.newInstance(
    "INSERT INTO movies (...) VALUES (?, ...)", );

SimpleStatement insertMoviesByActor =
  SimpleStatement.newInstance(
    "INSERT INTO movies_by_actor (...) VALUES (?, ...)", );

SimpleStatement insertMoviesByGenre =
  SimpleStatement.newInstance(
    "INSERT INTO movies_by_genre (...) VALUES (?, ...)", );

BatchStatement batch =
  BatchStatement.builder(DefaultBatchType.LOGGED)
    .addStatement(insertMovies)
    .addStatement(insertMoviesByActor)
    .addStatement(insertMoviesByGenre)
    .build();

For details, see Java driver Batch statements. Cheers!

user_2930812 · Answer

Cassandra supports secondary Index, SSTable Attached Secondary Index(SASI). Storage Attached Indexes (SAI) has been donated to the project but not yet accepted.

You need to create your tables such that you can get all your required data from table using a single query which looks something like this

SELECT * from keyspace.table_name where key = 'ABC';

So what it means to a as a designer. You need to get all queries identified and on the basis of those queries you define your data model (tables). So if you think you will need 4 tables to satisfy your queries then you are right.

Since all the 4 tables defined by you will have to be in sync if they represent same data, best way is to use batch

BEGIN BATCH 
  DML_statement1 ;
  DML_statement2 ;
  DML_statement3 ;
  DML_statement4 ;
APPLY BATCH ;

Batch does not guarantee that all statements will be successful are rolled back. It informs the client that group of statements has failed. So client should retry to apply them.

Better to avoid secondary indexes if you can because of performance issues with them. A general rule of thumb is to index a column with low cardinality of few values.

Cassandra DB Inserting data into multiple tables

2 Answers