Spring Data Cassandra 在次要索引的集群列上执行 LIKE 查询

huangapple go评论77阅读模式
英文:

spring data Cassandra LIKE query on secondary indexed cluster column

问题

Java版本:

openjdk版本 "20" 2023-03-21
OpenJDK运行环境(构建20+37)
OpenJDK 64位服务器虚拟机(构建20+37,混合模式,共享)

Spring Boot版本:3.0.1

Cassandra版本:

cqlsh:hipi> 显示版本;
[cqlsh 6.0.0 | Cassandra 4.0.7 | CQL规范3.4.5 | 本机协议v5]

表描述:

cqlsh:abc> DESCRIBE TABLE books;

CREATE TABLE abc.books (
    author_id text,
    name text,
    created_at timestamp,
    status boolean,
    PRIMARY KEY (author_id, name)
) WITH CLUSTERING ORDER BY (name ASC)
    AND additional_write_policy = '99p'
    AND bloom_filter_fp_chance = 0.01
    AND caching = {'keys': 'ALL', 'rows_per_partition': 'NONE'}
    AND cdc = false
    AND comment = ''
    AND compaction = {'class': 'org.apache.cassandra.db.compaction.SizeTieredCompactionStrategy', 'max_threshold': '32', 'min_threshold': '4'}
    AND compression = {'chunk_length_in_kb': '16', 'class': 'org.apache.cassandra.io.compress.LZ4Compressor'}
    AND crc_check_chance = 1.0
    AND default_time_to_live = 0
    AND extensions = {}
    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 = 'BLOCKING'
    AND speculative_retry = '99p'

CREATE INDEX books_name_idx ON abc.books (name);

在使用"WHEN"查询时:

SELECT * FROM books WHERE name='8b481c6e-48a0-4552-a624-9e8fbad4f930'

结果:

cqlsh:hipi> SELECT * FROM books WHERE name ='8b481c6e-48a0-4552-a624-9e8fbad4f930'
 author_id                            | name                                 | created_at                      | status
--------------------------------------+--------------------------------------+---------------------------------+--------
 a1c08312-d8fe-46f8-b625-bf43da2a2920 | 8b481c6e-48a0-4552-a624-9e8fbad4f930 | 2023-06-13 09:36:07.311000+0000 |   True

但在使用"LIKE"时:

SELECT * FROM books WHERE name LIKE '%8b4%'

结果:

*InvalidRequest: 服务器错误:代码=2200 [无效查询] 信息="LIKE限制仅受支持的适当索引列上。name LIKE '%8b4%' 不是有效的。"*
英文:

Java Version:

openjdk version "20" 2023-03-21
OpenJDK Runtime Environment (build 20+37)
OpenJDK 64-Bit Server VM (build 20+37, mixed mode, sharing)

Springboot Version: 3.0.1
Cassandra Version:

cqlsh:hipi> SHOW version;
[cqlsh 6.0.0 | Cassandra 4.0.7 | CQL spec 3.4.5 | Native protocol v5]

Table description

cqlsh:abc> DESCRIBE TABLE books;

CREATE TABLE abc.books (
    author_id text,
    name text,
    created_at timestamp,
    status boolean,
    PRIMARY KEY (author_id, name)
) WITH CLUSTERING ORDER BY (name ASC)
    AND additional_write_policy = '99p'
    AND bloom_filter_fp_chance = 0.01
    AND caching = {'keys': 'ALL', 'rows_per_partition': 'NONE'}
    AND cdc = false
    AND comment = ''
    AND compaction = {'class': 'org.apache.cassandra.db.compaction.SizeTieredCompactionStrategy', 'max_threshold': '32', 'min_threshold': '4'}
    AND compression = {'chunk_length_in_kb': '16', 'class': 'org.apache.cassandra.io.compress.LZ4Compressor'}
    AND crc_check_chance = 1.0
    AND default_time_to_live = 0
    AND extensions = {}
    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 = 'BLOCKING'
    AND speculative_retry = '99p';

CREATE INDEX books_name_idx ON abc.books (name);

While querying with WHEN =

SELECT * FROM books WHERE name='8b481c6e-48a0-4552-a624-9e8fbad4f930';

Result

cqlsh:hipi> SELECT * FROM books WHERE name ='8b481c6e-48a0-4552-a624-9e8fbad4f930';
 author_id                            | name                                 | created_at                      | status
--------------------------------------+--------------------------------------+---------------------------------+--------
 a1c08312-d8fe-46f8-b625-bf43da2a2920 | 8b481c6e-48a0-4552-a624-9e8fbad4f930 | 2023-06-13 09:36:07.311000+0000 |   True

But while using with LIKE

SELECT * FROM books WHERE name LIKE '%8b4%';

Result

InvalidRequest: Error from server: code=2200 [Invalid query] message="LIKE restriction is only supported on properly indexed columns. name LIKE '%8b4%' is not valid."

答案1

得分: 1

要在CQL中使用LIKE过滤,您需要将索引构建为SSTable附加二级索引(SASI)。 SASI索引被视为“实验性”,因此默认情况下处于禁用状态。 要启用它们,请在所有节点的cassandra.yaml中更改此设置,然后重新启动集群:

sasi_indexes_enabled: true

接下来,像这样创建您的索引:

CREATE CUSTOM INDEX books_name_idx ON abc.books (name)
    USING 'org.apache.cassandra.index.sasi.SASIIndex';

然后,这应该可以工作:

> SELECT * FROM books WHERE name LIKE 'Mastering%';

 author_id | name                           | created_at                      | status
-----------+--------------------------------+---------------------------------+--------
     6866 | Mastering Apache Cassandra 3.x | 2023-06-13 12:51:12.802000+0000 |   True

(1 rows)
英文:

To use LIKE filtering in CQL, you need to build your index as a SSTable Attached Secondary Index (SASI). SASI indexes are considered to be "experimental," so they are disabled by default. To enable them, flip this setting in the cassandra.yaml for all nodes, and restart the cluster:

sasi_indexes_enabled: true

Next, create your index like this:

CREATE CUSTOM INDEX books_name_idx ON abc.books (name)
    USING 'org.apache.cassandra.index.sasi.SASIIndex';

Then, this should work:

> SELECT * FROM books WHERE name LIKE 'Mastering%';

 author_id | name                           | created_at                      | status
-----------+--------------------------------+---------------------------------+--------
      6866 | Mastering Apache Cassandra 3.x | 2023-06-13 12:51:12.802000+0000 |   True

(1 rows)

huangapple
  • 本文由 发表于 2023年6月13日 17:46:46
  • 转载请务必保留本文链接:https://go.coder-hub.com/76463629.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定