[
https://issues.apache.org/jira/browse/CASSANDRA-11183?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15222859#comment-15222859
]
DOAN DuyHai commented on CASSANDRA-11183:
-----------------------------------------
*Test scenario*
*Schema* & *data*:
{noformat}
CREATE TABLE sensors(
sensor_id int,
sensor_type text static,
date bigint,
value double,
variance int,
PRIMARY KEY(sensor_id, date)
);
INSERT INTO sensors(sensor_id,sensor_type) VALUES(1, 'TEMPERATURE');
INSERT INTO sensors(sensor_id,date,value,variance) VALUES(1, 20160401, 24.46,
2);
INSERT INTO sensors(sensor_id,date,value,variance) VALUES(1, 20160402, 25.62,
5);
INSERT INTO sensors(sensor_id,date,value,variance) VALUES(1, 20160403, 24.96,
4);
INSERT INTO sensors(sensor_id,sensor_type) VALUES(2, 'PRESSURE');
INSERT INTO sensors(sensor_id,date,value,variance) VALUES(2, 20160401, 1.03, 9);
INSERT INTO sensors(sensor_id,date,value,variance) VALUES(2, 20160402, 1.04, 7);
INSERT INTO sensors(sensor_id,date,value,variance) VALUES(2, 20160403, 1.01, 4);
CREATE CUSTOM INDEX ON test.sensors (sensor_type) USING
'org.apache.cassandra.index.sasi.SASIIndex' WITH OPTIONS = {'mode': 'PREFIX',
'analyzer_class':
'org.apache.cassandra.index.sasi.analyzer.NonTokenizingAnalyzer',
'case_sensitive': 'false'};
CREATE CUSTOM INDEX ON test.sensors (variance) USING
'org.apache.cassandra.index.sasi.SASIIndex';
CREATE CUSTOM INDEX ON test.sensors (value) USING
'org.apache.cassandra.index.sasi.SASIIndex';
{noformat}
*Tests*:
{noformat}
//All data
SELECT * FROM sensors ;
sensor_id | date | sensor_type | value | variance
-----------+----------+-------------+-------+----------
1 | 20160401 | TEMPERATURE | 24.46 | 2
1 | 20160402 | TEMPERATURE | 25.62 | 5
1 | 20160403 | TEMPERATURE | 24.96 | 4
2 | 20160401 | PRESSURE | 1.03 | 9
2 | 20160402 | PRESSURE | 1.04 | 7
2 | 20160403 | PRESSURE | 1.01 | 4
// Static column only with prefix
SELECT * FROM sensors WHERE sensor_type LIKE 'temp%';
sensor_id | date | sensor_type | value | variance
-----------+----------+-------------+-------+----------
1 | 20160401 | TEMPERATURE | 24.46 | 2
1 | 20160402 | TEMPERATURE | 25.62 | 5
1 | 20160403 | TEMPERATURE | 24.96 | 4
// EQ on static column combined with non static columns
SELECT * FROM sensors WHERE sensor_type='pressure' AND value >= 1.02 AND value
<= 1.05 AND variance = 7 ALLOW FILTERING;
sensor_id | date | sensor_type | value | variance
-----------+----------+-------------+-------+----------
2 | 20160402 | PRESSURE | 1.04 | 7
// Non static columns only
SELECT * FROM sensors WHERE value >= 1.02 AND variance <= 7 ALLOW FILTERING;
sensor_id | date | sensor_type | value | variance
-----------+----------+-------------+-------+----------
1 | 20160401 | TEMPERATURE | 24.46 | 2
1 | 20160402 | TEMPERATURE | 25.62 | 5
1 | 20160403 | TEMPERATURE | 24.96 | 4
2 | 20160402 | PRESSURE | 1.04 | 7
{noformat}
> Enable SASI index for static columns
> ------------------------------------
>
> Key: CASSANDRA-11183
> URL: https://issues.apache.org/jira/browse/CASSANDRA-11183
> Project: Cassandra
> Issue Type: Improvement
> Components: CQL
> Reporter: DOAN DuyHai
> Assignee: DOAN DuyHai
> Priority: Minor
> Attachments: patch_SASI_for_Static.txt
>
>
> This is a follow up ticket for post Cassandra 3.4 SASI integration.
> Since [CASSANDRA-8103] it is possible to index static columns, which is
> *extremely useful* for some scenarios (find all sensors whose characteristics
> are saved in static columns)
> /cc [~xedin] [~rustyrazorblade] [~jkrupan]
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
