and clause in cql cassandra

Question

i have created a table with this schema

CREATE TABLE iplocation (
    "idIPLocation" uuid,
    "fromIP" bigint,
    "toIP" bigint,
    "idCity" uuid,
    "idCountry" uuid,
    "idProvince" uuid,
    "isActive" boolean,
    PRIMARY KEY ("idIPLocation", "fromIP", "toIP")
) 

and inserted some records in it! now i want to fetch a record like this

select * from iplocation where "toIP" <= 3065377522 and "fromIP" >= 3065377522 ALLOW FILTERING;

but its giving me an error of

A column of a clustering key can be restricted only if the preceding one is restricted by an Equal relation.
You need to restrict fromIP before restrict toIP.

but if i want to do just

select * from iplocation where "toIP" <= 3065377522 ALLOW FILTERING;

It still says

 column of a clustering key can be restricted only if the preceding
   one is restricted by an Equal relation.
You need to restrict
   fromIP before restrict toIP.

i cant figureout whats the problem?

Answer 1

First of all, use of the ALLOW FILTERING directive is horribly inefficient, and its use is considered to be an anti-pattern. If you find yourself having to use it to satisfy a query requirement, you should be building a new table that better-suits your query, instead. Perhaps, one that makes better use of your partition keys for data retrieval.

select * from implication
where "toIP" <= 3065377522 and "fromIP" >= 3065377522 ALLOW FILTERING;

This fails because Cassandra only use non-equals conditions (>,=>,<,<=) on a single column, and it has to be the last one.

select * from implication
where "toIP" <= 3065377522 ALLOW FILTERING;

This fails with the same error message, because it senses that you are actively trying to prevent Cassandra from doing what it does best. And that is read a single row or a contiguous range of ordered rows off of the disk. Essentially, you are asking it to perform random reads, because it will have to check every node in your cluster to satisfy this query. As Cassandra is designed to support large-scale, that could introduce lots of network time into your query equation...something it is trying to save you from.

To solve this issue, I would rework the table with an appropriate partition key (as mentioned above) a single IP address column, and a from/to column...all a part of the key. It would look something like this:

CREATE TABLE iplocation (
    idIPLocation uuid,
    IP bigint,
    fromTo text,
    idCity uuid,
    idCountry uuid,
    idProvince uuid,
    isActive boolean,
    PRIMARY KEY (idIPLocation, IP, fromTo)
);

Now you essentially store your data twice, giving you a starting and ending IP range. The rows are differentiated by a F or T as a clustering key to tell you which is the "From IP" and which is the "To IP."

aploetz@cqlsh:stackoverflow> SELECT * FROm implication
   WHERE idiplocation=76080f76-92f7-4d25-a531-a44c38ff38a7 
   AND IP>=10000 AND IP<=3065377522;

 idiplocation                         | ip       | fromto | idcity                               | idcountry                            | idprovince                           | isactive
--------------------------------------+----------+--------+--------------------------------------+--------------------------------------+--------------------------------------+----------
 76080f76-92f7-4d25-a531-a44c38ff38a7 |    10001 |      F | 6921a08b-c156-428e-8d4f-b371ff13f073 | f33bd5ed-b9b3-419b-99ab-ac2a7c87ba55 | 5a13cfcc-382e-418a-aeae-309f43671336 |     True
 76080f76-92f7-4d25-a531-a44c38ff38a7 | 10480101 |      T | 6921a08b-c156-428e-8d4f-b371ff13f073 | f33bd5ed-b9b3-419b-99ab-ac2a7c87ba55 | 5a13cfcc-382e-418a-aeae-309f43671336 |     True

(2 rows)

This is similar to how I model problems where data points have a range of both a starting and ending time. While your end solution will probably be different, the modeling mechanism here is something that may work for you.

Answer 2

Your are misusing partition key concept. In your case the partition key is idIPLocation Cassandra use this key to know in which partition data will be write or read. So in your select statement you have to provide the partition key. Then you can filter data within the specified partition by provide fromIP, toIP.

You have four solutions : 1) Chose a better partition key : you can for example use followinf partition key clause : PRIMARY KEY ("toIP"). But in your case I guess this solution won't work because you want to query data by idIPLocation too.

2) Denormalize : add a new table with the same data structure but a différent partition key like so : CREATE TABLE backup_advertyze.iplocation ( "idIPLocation" uuid, "fromIP" bigint, "toIP" bigint, "idCity" uuid, "idCountry" uuid, "idProvince" uuid, "isActive" boolean, PRIMARY KEY ("idIPLocation", "fromIP", "toIP") ); CREATE TABLE backup_advertyze.iplocationbytoip ( "idIPLocation" uuid, "fromIP" bigint, "toIP" bigint, "idCity" uuid, "idCountry" uuid, "idProvince" uuid, "isActive" boolean, PRIMARY KEY ("toIP", "fromIP") );

with this structure you can run this query select * from iplocationbytoip where "toIP" <= 3065377522 and "fromIP" >= 3065377522. But with this solution you have to maintain doubles in two tables

3) Use materialized view : This is the same concept as 2) but you have to maintain data in one table instead of two : `CREATE TABLE backup_advertyze.iplocation ( "idIPLocation" uuid, "fromIP" bigint, "toIP" bigint, "idCity" uuid, "idCountry" uuid, "idProvince" uuid, "isActive" boolean, PRIMARY KEY ("idIPLocation", "fromIP", "toIP") );

CREATE MATERIALIZED VIEW backup_advertyze.iplocationbytoip AS SELECT * FROM backup_advertyze.iplocation WHERE idIPLocation IS NOT NULL AND fromIP IS NOT NULL AND toIP IS NOT NULL PRIMARY KEY (toip, fromip, idiplocation);`

4) The most simple solution but i don't recommend due to query performences issues is to use secondary indexes :

CREATE INDEX iplocationfromindex ON backup_advertyze.iplocation(fromip);

you can run your query select * from iplocation where "toIP" <= 3065377522 and "fromIP" >= 3065377522 ALLOW FILTERING;.

Hope this can help you.