incubator-cassandra-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Jacob Rhoden <jacob.rho...@me.com>
Subject Re: Efficient IP address location lookup
Date Sat, 16 Nov 2013 02:51:48 GMT

> On 16 Nov 2013, at 1:47 pm, Jon Haddad <jon@jonhaddad.com> wrote:
> Instead of determining your table first, you should figure out what you want to ask Cassandra.

Thanks Jon, Perhaps I should have been more clear. I need to efficiently look up the location
of an IP address.

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