英文:
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)
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论