In our Project we are using Quarkus Version 2.8.0.FINAL and Datastax Version 1.1.1 accessing cassandra database Version 3.11
Wa are getting the following Exception when we perform an insert via Java.
com.datastax.oss.driver.api.core.servererrors.InvalidQueryException: PRIMARY KEY column "col_4" cannot be restricted as preceding column "col_3" is not restricted
We do not understand why that happens since the following statement executed via SQLTools from VSCode succeeds without any problem
INSERT INTO my_table (
col_1,
col_2,
col_3,
col_4,
col_5,
col_6,
col_7
)
VALUES (
'Some Value',
'5ea20642-e9e7-44bd-b4f0-97b84ae97f2c',
'1ed91e32-a7ba-4b5f-b4dd-9cfe3e522914',
'Another Value',
'2010-01-01',
'24afc036-21e8-41c6-9f7a-1994fba71223',
'Just one mor value'
);
Our Table is as follows
CREATE TABLE IF NOT EXISTS my_table (
col_1 text,
col_2 text,
col_3 text,
col_4 text,
col_5 text,
col_6 text,
col_7 date,
PRIMARY KEY(col_1, col_2, col_3, col_4)
);
The Entity
@Entity
@CqlName("my_entity")
public class MyEntity {
@PartitionKey
private String col_1;
@ClusterinColumn
private String col_2;
@ClusterinColumn(1)
private String col_3;
@ClusterinColumn(2)
private String col_4;
private String col_5;
private String col_6;
private LocalDate col_1;
}
The Dao
@Dao
public interface MyEntityDao {
@Insert
void insert (MyEntity myEntity)
}
CodePudding user response:
The error you got is related to the Cassandra Driver object mapping, creating under the hood a CQL Statement that violates some constraints of your table. A mandatory attribute was probably not provided.
It happens because of a weak design and probably some misunderstanding of the Cassandra data modeling methodology. Let's improve this ! (working project on github at the end).
Comments about the table:
The creation table statement your provided is equivalent to
CREATE TABLE IF NOT EXISTS my_table (
col_1 text,
col_2 text,
col_3 text,
col_4 text,
col_5 text,
col_6 text,
col_7 date,
PRIMARY KEY((col_1), col_2, col_3, col_4)
WITH CLUSTERING ORDER BY (col_2 DESC, col_3 DESC, col_4 DESC);
col_1
is called your partition key. It does not ensure the unicity of the record and is absolutely mandatory in every request WHERE CLAUSE against the table. When double parenthesis are omited the first column is considered the partition key.Columns from
col_2
tocol_4
are called clustering columns and are used to complete the primary key to ensuring unicity and mark columns allowed in the clause WHERE. The order is important in the declaration. eg. If you want to filter on col_4 you need to providecol_1
(the partition key) but alsocol_2
etcol_3
To know more about the methodology take the free course DS220 on https://academy.datastax.com
Comments about the entity:
Here is an updated version you got a few mistakes:
The table name is
my_table
You provided the
col_1
twiceYou forgot an offset in the first
ClusteringColumn(0)
@Entity @CqlName("my_table") public class MyEntity { @PartitionKey @CqlName("col_1") private String col1; @ClusteringColumn(0) @CqlName("col_2") private String col2; @ClusteringColumn(1) @CqlName("col_3") private String col3; @ClusteringColumn(2) @CqlName("col_4") private String col4; @CqlName("col_5") private String col5; @CqlName("col_6") private String col6; @CqlName("col_7") private LocalDate col7; // Constructors // Getters and Setters }
Comments about the Dao:
@Dao
@DefaultNullSavingStrategy(NullSavingStrategy.SET_TO_NULL)
public interface MyEntityDao {
@Delete(entityClass = MyEntity.class)
void deleteByCol1(String col1);
@Select
PagingIterable<MyEntity> findByPkCol1(String col1);
@Select
Optional<MyEntity> findById(String col1, String col2, String col3, String col4);
@Insert
void save(MyEntity product);
}
I noticed you are using a fairly new Quarkus version but an old version of Cassandra. If you are starting a project jump to the 4.0 you will have. free perfromance boost.
Here I provide you the working Quarkus Application with your database schema and your entity => https://github.com/clun/quarkus_cassandra
Now, please NEVER, EVER use ALLOW FILTERING. It will do a full scan of your cluster and still be slow and then will return timeout (while working in dev). If you need multiple queries on the same data...duplicate the data in multiple tables, please.
CodePudding user response:
So, I figured it out. Quarkus or DataStax in this case is really ugly
I had a second Method in my Dao
@Select (custimWhereClause = "col2 = :col_2_value and col4 = :col_4_value")
PagingIterable<MyEntity> findByCriteria(String col_2_value, String col_4_value)
I temporarily added allowFiltering=true to the Annotation and now it worked