Is there a way we can insert a Map onto the Cassandra Table using QueryBuilder for a dynamic table(Table with no model class)

Question

public int save(String tableName, Map<String, Object> dataMap) throws IllegalAccessException {
    SimpleStatement saveStatement = QueryBuilder.insertInto(tableName).values()
            



    return 1;

}

I have tried other inbuilt methods of QueryBuilder to save map values as a whole but only a map of type map<String,Term> can be used to save data if "values()" method is used and I already have a map<String,Object>.This needs to be done for a dynamic table p.s. I am not that much familiar with Dynamic Tables. The other method I tried using with a return type integer was

public int save(String tableName, Map<String, Object> dataMap) throws IllegalAccessException {



    SimpleStatement updateStatement = QueryBuilder.update(tableName)
            .set(appendSet(dataMap))
            .where(appendWhere(domainId))
            .build();
    log.info(updateStatement.getQuery());

    return 1;
}
private Iterable<Assignment> appendSet(Map<String, Object> dataMap) throws IllegalAccessException {
    List<Assignment> assignments = new ArrayList<>();
    for (Field field : dataMap.getClass().getDeclaredFields()) {
        if (!field.getName().equals("key")) {
            try {
                field.setAccessible(true);
                if (field.get(dataMap) != null) {
                    if (field.getType().equals(Long.class)) {
                        assignments.add(Assignment.setColumn(field.getName(), literal(Long.valueOf(field.get(dataMap).toString()))));
                    } else {
                        assignments.add(Assignment.setColumn(field.getName(), literal(field.get(dataMap))));
                    }
                }
            } catch (IllegalAccessException e) {
                log.catching(e);
            }
        }
    }
    return assignments;
}

private Iterable<Relation> appendWhere(Object key) {
    List<Relation> relations = new ArrayList<>();
    for (Field field : key.getClass().getDeclaredFields()) {
        try {
            field.setAccessible(true);
            if (field.get(key) != null) {
                if (field.getType().equals(Long.class)) {
                    relations.add(Relation.column(field.getName()).isEqualTo(literal(Long.valueOf(field.get(key).toString()))));
                } else {
                    relations.add(Relation.column(field.getName()).isEqualTo(literal(field.get(key))));
                }
            }
        } catch (IllegalAccessException e) {
            log.catching(e);
        }
    }
    return relations;
}

This probably didn't work out too. I need to return an integer with the save method but I just cannot figure out how to insert map values into the cassandra table itself using QueryBuilders or CassandraTemplate. I am working with Cassandra Database and my table is dynamic. Can anyone suggest me a good article or anything? I found articles but those did not help me insert map key value pairs into the table so I am quite struggling. Any help would be appreciated.

score 2 · Accepted Answer · answered Oct 19 '21 at 07:51

Cassandra Data Modelling reminders

With Cassandra there is no such things as dynamic tables, it works with a strict schema. I would argue that if you did not find any samples it is because it is an anti-pattern.

With a Cassandra databases you start by designing your queries and ONLY THEN define the tables because **you can only filter on fields (part of where clause) within the primary key. I mentionned this as Iterable<Relation> appendWhere(Object key) is suspicious, there is no joins nor relations in Cassandra.

If a new query arise on the same data, you duplicate the data in another table (yes, it is different for relational or document oriented)

At initialization of your application you prepare your (static) statements to validate syntax and share a PrepareStatementID with the server.

@PostConstruct
public void prepareStatements() {
   PreparedStatement  stmtCreateUser = 
   session.prepare(QueryBuilder.insertInto(USER_TABLENAME)
                .value(USER_EMAIL, QueryBuilder.bindMarker())
                .value(USER_FIRSTNAME, QueryBuilder.bindMarker())
                .value(USER_LASTNAME, QueryBuilder.bindMarker())
                .ifNotExists().build());
}

^ Noticed the constants used in the definition. Later when you reuse the column names for extra tables or rename the columns it is way easier to debug.

Given a table :

CREATE TABLE IF NOT EXISTS users_by_city (
    city name,
    firstname text,
    lastname text,
    email text,
    PRIMARY KEY ((city), lastname, firstname, email)
);

Legal but not recommended:

// This query is legal but never forget the where clause or you do full scan cluster
SELECT [fields] FROM users_by_city;

// This query is legal as you provide the partition in the where clause but having * in the select is hazardous if columns are added later.
SELECT * FROM users_by_city WHERE city=?

Legal Queries

SELECT firstname,lastname,email FROM users_by_city WHERE city=?

SELECT firstname,email FROM users_by_city WHERE city=? and lastname=?

// order is important
SELECT firstname,email FROM users_by_city WHERE city=? and lastname=? and firstname=?

Illegal Queries

// city is required in PK
SELECT firstname,email FROM users_by_city WHERE lastname=? and firstname=?

// order of cluster columns are important
SELECT firstname,email FROM users_by_city WHERE a city=?  and firstname=?

Implementation Details

With all those reminders (sorry if you knew already all this thinking of those who might come to the question later...) here is some ideas.

QueryBuilder is already a builder to build your queries dynamically but looking at your code you can think of

INSERT

public SimpleStatement insertInto(String keyspace, 
  String tableName, Map<String, Object> fields) {
  return QueryBuilder.insertInto(keyspace, tableName)
                     .values(fields.entrySet()
                         .stream().collect(Collectors.toMap(
                             entry -> entry.getKey(), 
                             entry -> QueryBuilder.literal(entry.getValue()))))
                     .build();
}

SELECT

public SimpleStatement selectFrom(String keyspace, String tableName, Map<String, Relation> fields) {
        return QueryBuilder.selectFrom(keyspace, tableName)
                           .columns(fields.keySet())
                           .where(fields.values())
                           .build();
        
    }

Extra resources

Much appreciated. Although i found a little different approach.. this was what i was looking for. — Narupo, Oct 19 '21 at 09:13

Is there a way we can insert a Map onto the Cassandra Table using QueryBuilder for a dynamic table(Table with no model class)

1 Answers1