Cassandra greater than '>' issue

Question

in Cassandra i am trying to retrieve text data from table using >= operation but , nothing retrieved although trying to use = it returns successfully

this is sample of query

select * from s.vechile_information  where datetimelong >= '1493215758000' and vechile_customerid = '123' and vechileId = '32' allow filetring;

but when remove > it works fine

   select * from s.vechile_information  where datetimelong = '1493215758000' and vechile_customerid = '123' and vechileId = '32' allow filetring;

this is the table structure

CREATE TABLE fcs.vehicle_information (
    vehicle_customerId text,
    vehicleid text,
    cityid text,
    cityname text,
    citynamear text,
    createdby text,
    dateTimeLong text,
    description text,
    driverid text,
    drivername text,
    drivernamear text,
    groupofvehicles text,
    groupofvehiclesystemid text,
    insexpirygregoriandate bigint,
    name text,
    namear text,
    platenumber text,

   vehiclestatus text,
    PRIMARY KEY (vehicle_customerId, vehicleid)
) ;
CREATE CUSTOM INDEX dateTimeLongvehicle_information ON fcs.vehicle_information (dateTimeLong) USING 'org.apache.cassandra.index.sasi.SASIIndex' WITH OPTIONS = {'analyzer_class' : 'org.apache.cassandra.index.sasi.analyzer.NonTokenizingAnalyzer', 'case_sensitive' : 'false'};

after trail one more scenario the issue still exist , i would like to know why this behavior .

this the new table structure

CREATE TABLE fcs.devicetracking_log (
    customerid text,
    vehiclesystemid text,
    datetime text,
    uniqueid text,
    logaction int,
    logid uuid,
    cid int,
    altitude double,
    angle double,
    assignmentname text,
    assignmentsystemid text,
    cityid text,
    cityname text,
    citynamear text,
    coloronmap text,
    departmentid text,
    departmentname text,
    departmentnamear text,
    departmentsystemid text,
    device text,
    direction double,
    drivername text,
    drivernamear text,
    driversystemid text,
    groupofvehicles text,
    groupofvehiclesystemid text,
    gsm_signal bigint,
    id text,
    lastcid int,
    lastidledate bigint,
    lastoverspeednotificationtime bigint,
    laststoppeddate bigint,
    latitude double,
    longitude double,
    message_id bigint,
    mileage double,
    overspeedallowedperiod int,
    overspeedmaximumspeed int,
    receivingdate bigint,
    regionid text,
    regionname text,
    regionnamear text,
    report text,
    rtc_datetime bigint,
    rtctime bigint,
    satellites int,
    speed double,
    uid text,
    valid text,
    vehiclename text,
    vehiclenamear text,
    vehicleplatenumber text,
    PRIMARY KEY (customerid, vehiclesystemid, datetime, uniqueid, logaction, logid, cid)
) ;
CREATE CUSTOM INDEX ciddevicetrackinglog ON fcs.devicetracking_log (cid) USING 'org.apache.cassandra.index.sasi.SASIIndex' WITH OPTIONS = {'analyzer_class' : 'org.apache.cassandra.index.sasi.analyzer.NonTokenizingAnalyzer', 'case_sensitive' : 'false'};
CREATE CUSTOM INDEX citydevicetracking_log ON fcs.devicetracking_log (cityid) USING 'org.apache.cassandra.index.sasi.SASIIndex' WITH OPTIONS = {'analyzer_class' : 'org.apache.cassandra.index.sasi.analyzer.NonTokenizingAnalyzer', 'case_sensitive' : 'false'};
CREATE CUSTOM INDEX datetimedevicetrackinglog ON fcs.devicetracking_log (datetime) USING 'org.apache.cassandra.index.sasi.SASIIndex' WITH OPTIONS = {'analyzer_class' : 'org.apache.cassandra.index.sasi.analyzer.NonTokenizingAnalyzer', 'case_sensitive' : 'false'};
CREATE CUSTOM INDEX departmentdevicetracking_log ON fcs.devicetracking_log (departmentid) USING 'org.apache.cassandra.index.sasi.SASIIndex' WITH OPTIONS = {'analyzer_class' : 'org.apache.cassandra.index.sasi.analyzer.NonTokenizingAnalyzer', 'case_sensitive' : 'false'};
CREATE CUSTOM INDEX regiondevicetracking_log ON fcs.devicetracking_log (regionid) USING 'org.apache.cassandra.index.sasi.SASIIndex' WITH OPTIONS = {'analyzer_class' : 'org.apache.cassandra.index.sasi.analyzer.NonTokenizingAnalyzer', 'case_sensitive' : 'false'};
CREATE CUSTOM INDEX speeddevicetracking_log ON fcs.devicetracking_log (speed) USING 'org.apache.cassandra.index.sasi.SASIIndex' WITH OPTIONS = {'analyzer_class' : 'org.apache.cassandra.index.sasi.analyzer.NonTokenizingAnalyzer', 'case_sensitive' : 'false'};
CREATE CUSTOM INDEX vehiclenameardevicetracking_log ON fcs.devicetracking_log (vehiclenamear) USING 'org.apache.cassandra.index.sasi.SASIIndex' WITH OPTIONS = {'analyzer_class' : 'org.apache.cassandra.index.sasi.analyzer.NonTokenizingAnalyzer', 'case_sensitive' : 'false'};
CREATE CUSTOM INDEX vehiclenamedevicetrackinglog ON fcs.devicetracking_log (vehiclename) USING 'org.apache.cassandra.index.sasi.SASIIndex' WITH OPTIONS = {'analyzer_class' : 'org.apache.cassandra.index.sasi.analyzer.NonTokenizingAnalyzer', 'case_sensitive' : 'false'};
CREATE CUSTOM INDEX vehiclesystemiddevicetrackinglog ON fcs.devicetracking_log (vehiclesystemid) USING 'org.apache.cassandra.index.sasi.SASIIndex' WITH OPTIONS = {'analyzer_class' : 'org.apache.cassandra.index.sasi.analyzer.NonTokenizingAnalyzer', 'case_sensitive' : 'false'};

the select statement as following .

select * from fcs.devicetracking_log where customerId='179_gov'    and regionid='0000015b648d225c-0242ac11000e0001' and dateTime>='1493208398000' allow filtering;

but in case of using = it works

and also when adding one more condition or removing regionid it will work fine

and vehiclesystemid='0000015b64937c79-0242ac1100090001'

can any one help me ? it's a production issue

@AshrafulIslam i edited the question with the table structure because it too long to write it on a comment . — Yousef Al Kahky, Apr 26 '17 at 15:45
Which cassandra version are you using ?? I have inserted this data `INSERT INTO vehicle_information (vehicle_customerid , vehicleid , datetimelong) VALUES ( '123', '32', '1493215758000');` and queried with `select * from vehicle_information where datetimelong >= '1493215758000' and vehicle_customerid = '123' and vehicleid = '32' ALLOW FILTERING ;` It return the result — Ashraful Islam, Apr 26 '17 at 16:14
Did you try my query ? Your query have some misspelled field name — Ashraful Islam, Apr 26 '17 at 16:34
FYI- It's not a good idea to be using `ALLOW FILTERING` in production. — Aaron, Apr 26 '17 at 16:46
@Aaron how can i avoid or replace this , the query can't run without it ? — Yousef Al Kahky, Apr 26 '17 at 17:20
Usage of the `ALLOW FILTERING` directive is a sign that your table was not built to match your query pattern(s). In this case, you could make `datetimelong` your last clustering key, and you would not need the index or `ALLOW FILTERING`. In fact, all the index does is allow you to SELECT *only* by datetimelong, but that will not perform well at all...even with the SASI implementation. — Aaron, Apr 26 '17 at 18:15

questionaire · Accepted Answer · 2017-04-27T09:10:42.070

5

In general you shouldn't use ALLOW FILTERING in production. See as explanation.

In order to be able to do range queries on dateTimeLong it needs to be part of your key. In cassandra you normally tempt to create tables by your queries. This would mean in your scenario that you could create another table where dateTimeLong would be part of your key.

CREATE TABLE fcs.vehicle_information_byDateTime (
    vehicle_customerId text,
    vehicleid text,
    cityid text,
    cityname text,
    citynamear text,
    createdby text,
    dateTimeLong text,
    description text,
    driverid text,
    drivername text,
    drivernamear text,
    groupofvehicles text,
    groupofvehiclesystemid text,
    insexpirygregoriandate bigint,
    name text,
    namear text,
    platenumber text,

   vehiclestatus text,
    PRIMARY KEY (vehicle_customerId, vehicleid, dateTime)
) ;

Have in mind that if you want to be able to do range queries on dateTime you need to specify vehicleid.

Let's assume (customerid, (vehiclesystemid, datetime)) is your compound key. customerid is your primary key which you have to specify anyway.

If you know do:

select * from s.vechile_information where vechile_customerid = '123' AND vehiclesystemid >= '32';

This will work perfectly fine. This is introduced by the nature how cassandra stores data on disk. Your primary key specifies the location of the data in your cluster. The clustering columns vehiclesystemid, datetime specify how the data is stored on disk.

If you now want to do a range query for datetime you can do it by specifying the vehiclesystemid first:

select * from s.vechile_information where vechile_customerid = '123' AND vehiclesystemid = '32' AND datetime >= '1493215758000';

When inserting new data you have to insert then into both tables.

With Cassandra 3 there have been introduced materialized views which might be a fit for your use case. By this you would avoid the multiple inserts.

edited Apr 27 '17 at 09:10

answered Apr 26 '17 at 18:14

questionaire

2,475
2
14
28

can you see me last edit and last table structure and answer me ? i tried to make the design to can run this query . – Yousef Al Kahky Apr 27 '17 at 08:10
Why do you again create Secondary index? You should also avoid secondary indexes in production.... What is the result of the query? – questionaire Apr 27 '17 at 08:30
it works when i removed vehiclesystemid from the primary keys , and i removed the secondary indexes , thank you – Yousef Al Kahky Apr 27 '17 at 08:42
1

You should read about composite keys http://stackoverflow.com/questions/24949676/difference-between-partition-key-composite-key-and-clustering-key-in-cassandra. – questionaire Apr 27 '17 at 08:44
done :) , and also i need to know the behavior in old structure why greater than doesn't work while = is working correctly !! – Yousef Al Kahky Apr 27 '17 at 08:53
Again, read about composite keys. You cannot do a range query on a composite key where you didn't specify the first part of your composite key. – questionaire Apr 27 '17 at 08:54
and for SASI index i used the due to this article https://docs.datastax.com/en/cql/3.3/cql/cql_using/useSASIIndexConcept.html – Yousef Al Kahky Apr 27 '17 at 09:06
I extended the answer once more. Please read about compound keys! – questionaire Apr 27 '17 at 09:11

Cassandra greater than '>' issue

1 Answers1