英文:
Secondary indexes on composite keys in cassandra
问题
我有一个在Cassandra中的表：
CREATE TABLE global_product_highlights (
deal_id text,
product_id text,
highlight_strength double,
category_id text,
creation_date timestamp,
rank int,
PRIMARY KEY (deal_id, product_id, highlight_strength)
)
当我在Golang中执行以下查询时：
err = session.Query("select product_id from global_product_highlights where category_id=? order by highlight_strength DESC",default_category).Scan(&prodId_array)
我得到错误：不支持在二级索引上使用ORDER BY。
我在category_id上有一个索引。
我不完全理解在Cassandra中如何应用二级索引到复合键。
如果有人能解释并纠正这个问题，我将不胜感激。
英文:
I have this table in cassandra
CREATE TABLE global_product_highlights (
deal_id text,
product_id text,
highlight_strength double,
category_id text,
creation_date timestamp,
rank int,
PRIMARY KEY (deal_id, product_id, highlight_strength)
)
When i fire below query in Golang
err = session.Query("select product_id from global_product_highlights where category_id=? order by highlight_strength DESC",default_category).Scan(&prodId_array)
I get ERROR : ORDER BY with 2ndary indexes is not supported.
I have an index on category_id.
I don't completely understand how is secondary index applied on composite keys in cassandra.
Appreciate if anyone would explain and rectify this one.
答案1
得分: 1
在Cassandra中，ORDER BY子句只能在第一个聚簇列（主键中的第二列）上起作用，而在这种情况下，你的product_id是第一个聚簇列。这篇DataStax文档指出：
> 查询复合主键和排序结果的ORDER BY子句只能选择单个列。该列必须是复合主键中的第二列。
因此，如果你想按highlight_strength对表进行排序，那么你需要将该字段作为第一个聚簇列。
英文:
The ORDER BY clause in Cassandra only works on your first clustering column (2nd column in the primary key), which in this case is your product_id. This DataStax doc states that:
> Querying compound primary keys and sorting results ORDER BY clauses
> can select a single column only. That column has to be the second
> column in a compound PRIMARY KEY.
So, if you want to have your table sorted by highlight_strength, then you'll need to make that field the first clustering column.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论