cassandra-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "DOAN DuyHai (JIRA)" <j...@apache.org>
Subject [jira] [Commented] (CASSANDRA-11183) Enable SASI index for static columns
Date Sat, 02 Apr 2016 11:25:25 GMT

    [ 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)

Mime
View raw message