Find rows from Cassandra table that contains IP address within a specific IP range

user_11855988 0
Viewed 138

I have a table in Cassandra which stores IP in text format. I want to delete rows from that Cassandra table whose IP value is a private IP.

i.e. IPs which lie within the below ranges:

 172.16.0.0 - 172.31.255.255
192.168.0.0 - 192.168.255.255
  240.0.0.0 - 255.255.255.255

I was looking through querying using regex, but couldn't find a suitable answer. How do I go about it?

cql cassandra
2 Answers

For starters, Cassandra does not allow filtering by regular expressions.

As Cassandra only stores and retrieves data by PRIMARY KEY components, this is going to be difficult to answer without knowing what the PRIMARY KEY structure is. But, I can give you an example which may help.

Let's say I have a simple table for storing IP addresses, with a partition key on the IP's prefix (first two octets, in this case) and clustered on the IP address. This results in a PK structure like:

PRIMARY KEY (prefix, ip)

And that allows for a range query like this to work:

SELECT ip FROM ips WHERE prefix='172.16'
  AND ip >= '172.16.0.0'
  AND ip <  '172.16.0.4';

 ip
------------
 172.16.0.0
 172.16.0.1
 172.16.0.2
 172.16.0.3

(4 rows)

Likewise, you can also DELETE with a range filter:

DELETE FROM ips WHERE prefix='172.16'
  AND ip >= '172.16.0.0'
  AND ip <  '172.16.0.4';

Of course, this only works because I am filtering on the components of my PRIMARY KEY definition.

But the point of this example is to show that you can indeed run a range query on a string/TEXT type. Cassandra also provides the INET data type for the specific purpose of storing IP addresses.

user_10545580

You are better off writing a client code to remove such data. Write the logic to identify the keys to delete at client end and send delete queries to Cassandra one by one.

user_29308120
Related