使用cql在Cassandra中选择特定记录

问题描述

这是我使用的模式：

CREATE TABLE playerInfo (
key text,
column1 bigint,
column2 bigint,
column3 bigint,
column4 bigint,
column5 text,
value bigint,
PRIMARY KEY (key, column1, column2, column3, column4, column5)
)
WITH COMPACT STORAGE AND
bloom_filter_fp_chance=0.010000 AND
caching='KEYS_ONLY' AND
comment='' AND
dclocal_read_repair_chance=0.000000 AND
gc_grace_seconds=864000 AND
read_repair_chance=0.100000 AND
replicate_on_write='true' AND
populate_io_cache_on_flush='false' AND
compaction={'class': 'SizeTieredCompactionStrategy'} AND
compression={'sstable_compression': 'SnappyCompressor'};

注意我使用复合键。有这样的记录：

Note I use a composite key. And there is a record like this:

 key      | column1    | column2 | column3  | column4 | column5           | value
----------+------------+---------+----------+---------+--------------------------------------------------+-------
 Kitty    | 1411       |       3 | 713      |       4 |   American        |     1

在 cqlsh ？我尝试使用：

cqlsh:game> SELECT * FROM playerInfo WHERE KEY = 'Kitty' AND column5 = 'American';

但输出是：

Bad Request: PRIMARY KEY part column5 cannot be restricted (preceding part column4 is either not restricted or by a non-EQ relation)

那么我该如何选择这样的单元格呢？

Then how could I select such cell?

推荐答案

主键为 PRIMARY KEY（key，column1，column2，column3，column4，column5）所以如果你打算给where子句在column5然后你应该还需要指定键的where子句，column1，column2，column3，column4 。例如

You have choosen the primary key as PRIMARY KEY (key, column1, column2, column3, column4, column5) so if you are going to give where clause on column5 then you should also need to specify the where clause of key, column1, column2, column3, column4. for eg,

SELECT * FROM playerInfo WHERE KEY = 'Kitty' AND column1 = 1411 AND column2 = 3 AND column3 = 713 AND column4 = 4 AND column5 = 'American';

如果你打算在column2上给where子句，那么你以指定键的where子句，column1 。例如

If you are going to give where clause on column2 then you should also need to specify the where clause of key, column1. for eg,

SELECT * FROM playerInfo WHERE KEY = 'Kitty' AND column1 = 1411 AND column2 = 3;

如果要在主键的特定列上指定where子句，也需要给予。因此，您需要以棘手的方式选择cassandra数据建模，以获得良好的读写性能，并满足您的业务需求。但是，如果商业逻辑满足你，那么cassandra的表现不会满足你。如果cassandra性能满足你，那么你的业务逻辑不会满足你。这就是cassandra的美。当然cassandra需要更多的改善。

If you want to specify where clause on a particular column of primary key, then where clause of previous column also need to be given. So you need to choose the cassandra data modelling in a tricky way to have a good read and write performance and to satisfy your business needs too. But however if business logic satisfies you, then cassandra performance will not satisfies you. If cassandra performance satisfies you, then your business logic will not satisfies you. That is the beauty of cassandra. Sure cassandra needs more to improve.

这篇关于使用cql在Cassandra中选择特定记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！