非辅助索引查询尚不支持非主键列(事件类型)上的Cassandra谓词 [英] Cassandra Predicates on non-primary-key columns (eventtype) are not yet supported for non secondary index queries

问题描述

我开发了如下表，主键为id，这是一种uuid类型

i developed a table as shown as below with primary key as id which is a uuid type

 id                                   | date                     | eventtype    | log      | password | priority | sessionid | sourceip     | user       | useragent
--------------------------------------+--------------------------+--------------+----------+----------+----------+-----------+--------------+------------+------------
 6b47e9b0-d11a-11e8-883c-5153f134200b |                     null | LoginSuccess |  demolog |     1234 |       10 |    Demo_1 | 123.12.11.11 |       Aqib |  demoagent
 819a58d0-cd3f-11e8-883c-5153f134200b |                     null | LoginSuccess |  demolog |     1234 |       10 |    Demo_1 | 123.12.11.11 |       Aqib |  demoagent
 f4fae220-d133-11e8-883c-5153f134200b | 2018-10-01 04:01:00+0000 | LoginSuccess |  demolog |     1234 |       10 |    Demo_1 | 123.12.11.11 |       Aqib |  demoagent

但是当我尝试查询以下内容时

But when i try to query some thing like below

select * from loginevents where eventtype='LoginSuccess';

我收到如下错误

InvalidRequest: Error from server: code=2200 [Invalid query] message="Predicates on non-primary-key columns (eventtype) are not yet supported for non secondary index queries"

这是我的桌子

cqlsh:events> describe loginevents;

CREATE TABLE events.loginevents (
    id uuid PRIMARY KEY,
    date timestamp,
    eventtype text,
    log text,
    password text,
    priority int,
    sessionid text,
    sourceip text,
    user text,
    useragent text
) WITH bloom_filter_fp_chance = 0.01
    AND caching = '{"keys":"ALL", "rows_per_partition":"NONE"}'
    AND comment = ''
    AND compaction = {'class': 'org.apache.cassandra.db.compaction.SizeTieredCompactionStrategy'}
    AND compression = {'sstable_compression': 'org.apache.cassandra.io.compress.LZ4Compressor'}
    AND dclocal_read_repair_chance = 0.1
    AND default_time_to_live = 0
    AND gc_grace_seconds = 864000
    AND max_index_interval = 2048
    AND memtable_flush_period_in_ms = 0
    AND min_index_interval = 128
    AND read_repair_chance = 0.0
    AND speculative_retry = '99.0PERCENTILE';

我该如何解决

推荐答案

您的问题的直接答案是在 eventtype 列上创建二级索引，如下所示:

An immediate answer to your question would be to create a secondary index on the column eventtype like this:

CREATE INDEX my_index ON events.loginevents (eventtype);

然后您可以在此特定列上进行过滤:

Then you can filter on this particular column :

SELECT * FROM loginevents WHERE eventtype='LoginSuccess';

但是，此解决方案可能严重影响群集的性能.

However this solution can badly impact the performances of your cluster.

如果您来自SQL世界并且是Cassandra的新手，请阅读有关Cassandra建模的介绍，例如

If you come from the SQL world and are new to Cassandra, go read an introduction on cassandra modeling, like this one.

首先要确定查询，然后根据创建表.

The first thing is to identify the query, then create the table according to.

在Cassandra中，数据是根据分区键在群集中分布的，因此读取属于同一分区的记录非常快.

In Cassandra, data are distributed in the cluster according to the partition key, so reading records that belong to the same partition is very fast.

对于您来说，一个好的开始可能是根据 eventtype 来对记录进行分组:

In your case, maybe a good start would be to group your records based on the eventtype :

CREATE TABLE events.loginevents (
  id uuid,
  date timestamp,
  eventtype text,
  log text,
  password text,
  priority int,
  sessionid text,
  sourceip text,
  user text,
  useragent text,
  PRIMARY KEY (eventtype, id)

)

然后您可以像这样进行选择:

Then you can do select like this :

SELECT * FROM loginevents WHERE eventtype='LoginSuccess';

甚至:

SELECT * FROM loginevents WHERE eventtype in ('LoginSuccess', 'LoginFailure');

(这不是一个理想的模型，肯定需要在生产之前进行改进.)

(It's not a perfect model, it definitely needs to be improved before production.)

这篇关于非辅助索引查询尚不支持非主键列(事件类型)上的Cassandra谓词的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！