incubator-cassandra-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Jon Haddad <...@jonhaddad.com>
Subject Re: Efficient IP address location lookup
Date Sat, 16 Nov 2013 02:47:16 GMT
Instead of determining your table first, you should figure out what you want to ask Cassandra.

What do you want to look up your data by?  For each query you may need to store the data multiple
times, which is perfectly reasonable and is recommended.

On Nov 15, 2013, at 4:36 PM, Jacob Rhoden <jacob.rhoden@me.com> wrote:

> Hi Guys,
> 
> It occurs to me that someone may have done this before and be willing to share, or may
just be interested in helping work out it.
> 
> Assuming a database table where the partition key is the first component of a users IPv4
address, i.e. (ip=100.0.0.1, part=100) and the remaining three parts of the IP address become
a 24bit integer.
> 
> create table location(
>     part int,
>     start bigint,
>     end bigint,
>     country text,
>     city text,
>     primary key (part, start, end));
> 
> // range 100.0.0.0 - 100.0.0.10
> insert into location (part, start, end, country, city) values(100,0,10,'AU','Melbourne’);
> 
> // range 100.0.0.11 - 100.0.0.200
> insert into location (part, start, end, country, city) values(100,11,200,'US','New York’);
> 
> // range 100.0.0.201-100.0.0.255
> insert into location (part, start, end, country, city) values(100,201,255,'UK','London');
> 
> What is the appropriate way to then query this? While the following is possible:
> 
> select * from location where part=100 and start<=30
> 
> What I need to do, is this, which seems not allowed. What is the correct way to query
this?
> 
> select * from location where part=100 and start<=30 and end>=30
> 
> Or perhaps I’m going about this all wrong? Thanks!


Mime
View raw message