在相同Cassandra查询中按AND LIKE排序 [英] Order by AND LIKE in Same Cassandra Query

问题描述

我想在cassandra表上执行此查询：

I want to execute this query on a cassandra table:

       select query,score,term  from mytable where term 
       like '%bottle%'  order by score desc limit 10;

表中的唯一列是查询，得分，术语。

The only columns of the table are query, score, term.

我按如下方式创建表：

CREATE TABLE mytable( "term" TEXT, "query" TEXT, "score" double,
PRIMARY KEY ("term","score")) WITH CLUSTERING ORDER BY (score desc);

我也有一个索引：

这给出了各种疯狂的错误。第一个是：

This gives all sort of crazy errors. The first is:

 InvalidRequest: code=2200 [Invalid query] message="LIKE restriction is
 only supported on properly indexed columns. term LIKE 'bottle' is not valid."

如果我将LIKE替换为equals，则查询有效。但是我需要LIKE和命令。因此，经过一番谷歌搜索后，我认为我会使用SASI索引。

If I replace the LIKE with equals, the query works. But I need the LIKE and the order by. So, after some googling, I thought I would use a SASI index.

CREATE CUSTOM INDEX term_idx2 ON mytable (term) USING
'org.apache.cassandra.index.sasi.SASIIndex' WITH OPTIONS = {   
'analyzer_class':
'org.apache.cassandra.index.sasi.analyzer.StandardAnalyzer',
'case_sensitive': 'false'};

这会产生另一条错误消息：

This produces another error message:

InvalidRequest: code=2200 [Invalid query] message=
"Cannot create secondary index on partition key column term"

确定。这也许意味着如果我想使用LIKE，则不应在 term上创建二级索引。然后，我们尝试通过查询进行查询。查询变为：

OK. This maybe means that if I want to use LIKE, I should not create a secondary index on "term". Then let's try to query by "query" instead. The query becomes:

       select query,score,term  from mytable where query 
       like '%bottle%'  order by score desc limit 10;

现在，这会产生使用允许过滤错误。从本质上讲，我在查询上创建索引。即使在查询中创建SASI类型索引也无济于事。

Now, this produces the "use ALLOW FILTERING" error. Essentially leading me to create index on query. Even creating the SASI type index on query does not not help.

这是否可能？

推荐答案

否！ ，您不能使用所有 like 和

No! you can't use all like and order by in same query.

您已经知道无法在主键和聚簇键上创建索引。但是，您可以使用以下命令在其他列上创建索引，例如： query ：

You already know you can't create indexes on primary and clustering keys. However, you can create indexes on other columns e.g: query, by using following command:

CREATE CUSTOM INDEX mytable_query_idx  ON mytable ("query") 
USING 'org.apache.cassandra.index.sasi.SASIIndex'     
WITH OPTIONS = {'mode': 'CONTAINS', 
'analyzer_class': 'org.apache.cassandra.index.sasi.analyzer.StandardAnalyzer',
'case_sensitive': 'false'};

然后，您进行类似

SELECT * FROM mytable WHERE query LIKE '%abc%';

但是您不能使用 order by 和 like ，因为

But you can't use order by and like in same query because

不支持带有第二索引的ORDER BY。

"ORDER BY with 2ndary indexes is not supported."

但是，如果您真的需要这些功能，Cassandra不是一个好选择。

Howerver, If you really need these functionality Cassandra is not a good choice.

这篇关于在相同Cassandra查询中按AND LIKE排序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！