incubator-cassandra-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Janne Jalkanen <Janne.Jalka...@ecyrd.com>
Subject Re: Efficient IP address location lookup
Date Sat, 16 Nov 2013 08:48:57 GMT
Idea:

Put only range end points in the table with primary key (part, remainder)

insert into location (part, remainder, city) values (100,10,Sydney) // 100.0.0.1-100.0.0.10
is Sydney
insert into location (part, remainder, city) values (100,50,Melbourne) // 100.0.0.11-100.0.0.5
is Melb

then look up (100.0.0.30) as

select * from location where part=100 and remainder >= 30 limit 1

For nonused ranges just put in an empty city or some other known value :)

/Janne

On Nov 16, 2013, at 04:51 , Jacob Rhoden <jacob.rhoden@me.com> wrote:

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