Related to the post 8412, the secondary index is clearly not working for our case, as the external_id field, its value may be the same from a company to another company. Then it takes to have the company_id as discriminant. Unfortunately, the secondary approach doesn't work for us. It allows only to have 1 field and we need two fields company_id + external_id.
1) As other approach we can concatenate the two fields (@hash( company_id + external_id) ) and store the value to a single column. This will make good the secondary index.
2) Under JUNIT test we add the statement to initialize the keyspace, then after creating the table, we add the statement below. It does not work and CQL is not available to understand the syntax.
So, how to make DSE Search working under JUNIT ?
-> CREATE SEARCH INDEX IF NOT EXISTS ON test;
-> error is: no viable alternative at input 'SEARCH' ([CREATE] SEARCH...)
This above statement works under DSE Studio but when it is time to make a select, it does not. We see under the tab Schema, the table and the Search Indexes. An index is created on each column of the table. Despite of that, we are not able to perform that query:
-> select * myspace.test where external_id = 'The-External-Id';
-> error: 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
I read with page on DSE Search, seems very simple to use it.
And finally, I want to create a CUSTOM index, but SASI is not supported in PROD, so do you have other suggestion ?
Thank you for your assistance. Regards,
[UPDATE] for the point 1), I'm asking to my self if the problem is related the librairies version or if it needs to add a missing library. I tried to upgrade to version 4.9 but we cannot upgrade because the library 4.9 do not have the same Session object. Not backward compatible. It takes a CQLSession and we use the Session from com.datastax.driver.core.Session and I cannot upgrade all dependencies, then on this aspect I'm locked for this moment. But not sure if an upgrade will solve the problem. The project is using the following versions:
- com.datastax.cassandra:cassandra-driver-core:3.10.2
- com.datastax.cassandra:cassandra-driver-extras:3.10.2
- org.springframework.boot:spring-boot-starter-data-cassandra:1.5.12
- org.cassandraunit:cassandra-unit-shaded:3.11.2.0
- org.cassandraunit:cassandra-unit-spring:3.11.2.0
To initialize the JUNIT, it is as following
@Slf4j
@RunWith(SpringRunner.class)
@SpringBootTest(classes = TestConfigurationPersistence.class)
public class Test {
private final static String KEYSPACE_NAME = "my_test";
// list of statements to execute for initializing the JUnit tests
private final static List<String> CQL_INIT_TEST= Stream.concat(Stream.of("USE " + KEYSPACE_NAME + ";" ), CreateTestTable.getCQLStatements().stream()).collect(Collectors.toList());
@Autowired
private CassandraOperations cassandraTemplate;
@Rule
public CassandraCQLUnit cassandraCQLUnitTest = new CassandraCQLUnit(new MigrationTestCQLDataSet(CQL_INIT_TEST, false, false, KEYSPACE_NAME));
And the rule execute the same script put in post 8412, with now, an index creation like this
"CREATE SEARCH INDEX IF NOT EXISTS ON test";
So, when the @Rule is going to execute, the syntax is not recognized. If I remove it, JUnit test are working for those where the index is not necessary.
For the point 2),. It works now with the good syntax. Very good and thank you. Then, it will be nice to understand why under JUnit the syntax is not recognized. But going to try again in case of... and does solr_quey allows to make a search on 2 columns ?
===> YA : SELECT * FROM test WHERE solr_query='external_id: 1-external_id && company_id: 1-company_id';
So, the question is then, now, how do we implements the query with standard API of Cassandra ? to produce a such query ?
and finally for the point 3) the question is more we can use custom SAI index for Production as we read that SASI index is not supported. We are using 6.8.3 but my tests are on local docker version dse-server:5.1.17. For JUnit, this is the in-memory version of Cassandra. So what I m understanding is that SAI index are not SASI index. SAI is a new way to create custom Index. Please confirm.
