在CQL Cassandra中找到非主键列的不同值 [英] Finding distinct values of non Primary Key column in CQL Cassandra

问题描述

我使用以下代码创建表:

I use the following code for creating table:

CREATE KEYSPACE mykeyspace
WITH REPLICATION = { 'class' : 'SimpleStrategy', 'replication_factor' : 1 };
USE mykeyspace;
CREATE TABLE users (
  user_id int PRIMARY KEY,
  fname text,
  lname text
);
INSERT INTO users (user_id,  fname, lname)
  VALUES (1745, 'john', 'smith');
INSERT INTO users (user_id,  fname, lname)
  VALUES (1744, 'john', 'doe');
INSERT INTO users (user_id,  fname, lname)
  VALUES (1746, 'john', 'smith');

我想找到lname列的唯一值(不是PRIMARY KEY).我想得到以下结果:

I would like to find the distinct value of lname column (that is not a PRIMARY KEY). I would like to get the following result:

 lname
-------
 smith

通过使用SELECT DISTINCT lname FROM users; 但是，由于lname不是PRIMARY KEY，所以出现以下错误:

By using SELECT DISTINCT lname FROM users; However since lname is not a PRIMARY KEY I get the following error:

InvalidRequest: code=2200 [Invalid query] message="SELECT DISTINCT queries must
only request partition key columns and/or static columns (not lname)"
cqlsh:mykeyspace> SELECT DISTINCT lname FROM users;

如何从lname获取不同的值?

推荐答案

用户- Undefined_variable -有两个好处:

• 在Cassandra中，您需要构建数据模型以匹配您的查询模式.有时这意味着将您的数据复制到其他表中，以达到所需的查询灵活性.
• DISTINCT仅适用于分区键.

• In Cassandra, you need to build your data model to match your query patterns. This sometimes means duplicating your data into additional tables, to attain the desired level of query flexibility.
• DISTINCT only works on partition keys.

因此，使它起作用的一种方法是建立一个支持该查询的特定表:

So, one way to get this to work, would be to build a specific table to support that query:

CREATE TABLE users_by_lname (
    lname text,
    fname text,
    user_id int,
    PRIMARY KEY (lname, fname, user_id)
);

现在，在将您的INSERT运行到此新查询表后，此方法有效:

Now after I run your INSERTs to this new query table, this works:

aploetz@cqlsh:stackoverflow> SELECT DISTINCT lname FROm users_by_lname ;

 lname
-------
 smith
   doe

(2 rows)

注意:在此表中，具有相同分区键(lname)的所有行将按fname排序，因为fname是聚类键.我添加了user_id作为附加的聚类键，只是为了确保唯一性.

Notes: In this table, all rows with the same partition key (lname) will be sorted by fname, as fname is a clustering key. I added user_id as an additional clustering key, just to ensure uniqueness.

这篇关于在CQL Cassandra中找到非主键列的不同值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！