SELECT in cassandra where id！= null [英] SELECT in cassandra where id != null

问题描述

如何在cassandra中查询！= null coulumns。

How do i query in cassandra for != null coulumns.

Select * from tableA where id != null;
Select * from tableA where name != null;

然后我想存储这些值并将它们插入到不同的表中。

Then i wanted to store these values and insert these into different table.

推荐答案

我认为这不可能与Cassandra。首先，Cassandra CQL不支持在WHERE子句中使用NOT或不等于运算符。其次，您的WHERE子句只能包含主键列，而主键列将不允许插入空值。我不知道第二个索引虽然，所以我运行这个快速测试：

I don't think this is possible with Cassandra. First of all, Cassandra CQL doesn't support the use of NOT or not equal to operators in the WHERE clause. Secondly, your WHERE clause can only contain primary key columns, and primary key columns will not allow null values to be inserted. I wasn't sure about secondary indexes though, so I ran this quick test:

create table nullTest (id text PRIMARY KEY, name text);
INSERT INTO nullTest (id,name) VALUES ('1','bob');
INSERT INTO nullTest (id,name) VALUES ('2',null);

我现在有一个表和两行（一个有空数据）：

I now have a table and two rows (one with null data):

SELECT * FROM nullTest;

 id | name
----+------
  2 | null
  1 |  bob

(2 rows)

然后尝试创建辅助索引（名称），我知道它包含空值。

I then try to create a secondary index on name, which I know contains null values.

CREATE INDEX nullTestIdx ON nullTest(name);

它让我做。现在，我将对该索引运行查询。

It lets me do it. Now, I'll run a query on that index.

SELECT * FROM nullTest WHERE name=null;
Bad Request: Unsupported null value for indexed column name

前提是你不能查询not null，如果你甚至不能查询可能实际为null的列值。

And again, this is done under the premise that you can't query for not null, if you can't even query for column values that may actually be null.

所以，我想这个不能做。此外，如果空值在您的主键中是可能的，那么您可能需要重新评估数据模型。再次，我知道OP的问题是关于查询数据不是 null。但是正如我之前提到的，Cassandra CQL没有NOT或！=操作符，所以这将是一个问题。

So, I'm thinking this can't be done. Also, if null values are a possibility in your primary key, then you may want to re-evaluate your data model. Again, I know the OP's question is about querying where data is not null. But as I mentioned before, Cassandra CQL doesn't have a NOT or != operator, so that's going to be a problem right there.

另一种选择是插入一个空字符串而不是一个null。然后，您将能够查询一个空字符串。但是，仍然不能让你超过在主键字段中有一个null的基本设计缺陷。也许如果你有一个复合主键，只有它的一部分（聚类列）有空的可能性（当然不是分区键的一部分）。但你仍然会遇到无法查询非空（而不是空值）的行的问题。

Another option, is to insert an empty string instead of a null. You would then be able to query on an empty string. But that still doesn't get you past the fundamental design flaw of having a null in a primary key field. Perhaps if you had a composite primary key, and only part of it (the clustering columns) had the possibility of being empty (certainly not part of the partitioning key). But you'd still be stuck with the problem of not being able to query for rows that are "not empty" (instead of not null).

注意：插入null这里的值仅用于演示目的。这是你应该尽最大努力避免，因为插入一个空列值将创建一个墓碑。同样，插入大量的空值将创建大量的墓碑。

NOTE: Inserting null values was done here for demonstration purposes only. It is something you should do your best to avoid, as inserting a null column value WILL create a tombstone. Likewise, inserting lots of null values will create lots of tombstones.

这篇关于SELECT in cassandra where id！= null的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！