how to allow filter in cassandra with java datastax

Question

I created a table and two indexes on it i am not able to do filter query from my spring application 

first i created table

CREATE TABLE keyspace.favourite_setting (
id uuid,
user_name text,
filter_name text,
columns LIST<FROZEN<columns_def>>,
filters text,
PRIMARY KEY (id)

);

second i added two indexes

CREATE INDEX IF NOT EXISTS user_name_index ON keyspace.favourite_setting (user_name)
CREATE INDEX IF NOT EXISTS filter_name_index ON keyspace.favourite_setting (filter_name);

then i am trying to filter shows me error you must allow filter although i am doing it already...

My Accessory

@Accessor
 public interface FavouriteSettingAccessor {


@Query("select * from favourite_setting where user_name =:userName allow filtering  " )
Result<FavouriteSetting> getAllByUserName(@Param("userName") String userName );

      }

my Service Impl

@Override
public List<FavouriteSetting> getAllFilterNames(String userName) throws  Exception {

    session = cassandraFactory.getDataSource();


    favouriteSettingAccessor =
            new MappingManager(session).createAccessor(FavouriteSettingAccessor.class);


    return favouriteSettingAccessor.getAllByUserName(userName).all();
}

My Favourite Setting Model

@Table(keyspace = "keyspace", name = "favourite_setting")
public class FavouriteSetting {

@PartitionKey
@Column(name = "id")
private UUID id;

@Column(name = "user_name")
private String userName;

@Column(name = "filter_name")
private String filterName;

@Column(name = "columns")
private List<ColumnsDef> columns;

@Column(name = "filters")
private String filters;

 public FavouriteSetting(UUID id, String userName, String filterName, 
 List<ColumnsDef> columns, String filters) {
    this.id = id;
    this.userName = userName;
    this.filterName = filterName;
    this.columns = columns;
    this.filters = filters;
}

public FavouriteSetting() {
}

public UUID getId() {
    return id;
}

public void setId(UUID id) {
    this.id = id;
}

public String getFilterName() {
    return filterName;
}

public void setFilterName(String filterName) {
    this.filterName = filterName;
}

public String getUserName() {
    return userName;
}

public void setUserName(String userName) {
    this.userName = userName;
}

public List<ColumnsDef> getColumns() {
    return columns;
}

public void setColumns(List<ColumnsDef> columns) {
    this.columns = columns;
}

public String getFilters() {
    return filters;
}

public void setFilters(String filters) {
    this.filters = filters;
}

}

Cannot execute this query as it might involve data filtering and thus may have unpredictable performance. If you want to execute this query despite the performance unpredictability, use ALLOW FILTERING

Answer 1

For the query

@Query("select * from favourite_setting where user_name =:userName ... " )

userName is not part of the primary key, you can set it as a clustering key. For more information you can look here or here.

Please note that there is no alter statement to add a clustering key, you will need to recreate the table with:

CREATE TABLE keyspace.favourite_setting (
id uuid,
user_name text,
filter_name text,
columns LIST<FROZEN<columns_def>>,
filters text,
PRIMARY KEY (id, username)

The index created for the username column is considered one of the antipatterns for indexes, as it will be expected to be unique (and therefore, it will have high cardinality); you can find more information of why this is a problem here

allow filtering should never be used, as explained here you should resist the urge to just add ALLOW FILTERING to it. You should think about your data, your model and what you are trying to do.

Answer 2

It works Now By adding user_name As Cluster Key and ADD To The End of Query ALLOW FILTERING .. Thanks :)