In redshift, group by IP network

Question

I would like to find the most commonly banned networks in a redshift table. I have tried this:

select network(set_masklen(ip::inet,8)), count(1)
from banlist where status='BLOCKED' 
group by 1 order by 2 desc limit 10;

And got the following error:

INFO:  Function ""network"(inet)" not supported.
INFO:  Function "set_masklen(inet,integer)" not supported.
INFO:  Function "inet(text)" not supported.
INFO:  Function ""network"(inet)" not supported.
INFO:  Function "set_masklen(inet,integer)" not supported.
INFO:  Function "inet(text)" not supported.
ERROR:  Specified types or functions (one per INFO message) not supported on Redshift tables.

OTOH, this works:

# select network(set_masklen('10.0.0.1'::inet, 24));                                                          network
-------------
 10.0.0.0/24
(1 row)

You may want to use this answer: http://stackoverflow.com/questions/819355/how-can-i-check-if-an-ip-is-in-a-network-in-python to create a UDF in your Redshift cluster (http://docs.aws.amazon.com/redshift/latest/dg/user-defined-functions.html) — Guy, Oct 20 '15 at 01:10

score 1 · Accepted Answer · answered Oct 20 '15 at 02:28

While everyone else's responses regarding creating a UDF are probably a fantastic option, if you're willing to give up some of the flexibility and just get either class A, class B, or class C subnets, you can use SPLIT_PART and the concatenation operator to get a (not super) quick and dirty solution.

select SPLIT_PART(ip_address, '.', 1) || '.' || SPLIT_PART(ip_address, '.', 2) || '.' || SPLIT_PART(ip_address,'.', 3) as network, count(1) as mc from banlist group by network order by mc desc limit 10;

score 0 · Answer 2 · answered Oct 19 '15 at 18:29

0

As per the Redshift documentation, Network address functions and operators from PostgreSQL are not supported.

References -

PostgreSQL functions not supported in Redshift

PostgreSQL documentation - Network Address Functions and Operators

answered Oct 19 '15 at 18:29

DotThoughts

759
5
8

Is there a workaround or a regex that would solve my problem? – Neal Fultz Oct 19 '15 at 18:31
You can write your own UDFs in Python. See http://docs.aws.amazon.com/redshift/latest/dg/r_CREATE_FUNCTION.html – DotThoughts Oct 19 '15 at 22:41

score 0 · Answer 3 · answered Dec 09 '16 at 18:13

It's an old question now but I thought I'd add what I've just figured out.

The method by @earino works but I also want to add two other methods that work since I also need to solve this problem for aggregation.

select SPLIT_PART(ip, '.', 1) || '.' || SPLIT_PART(ip, '.', 2) || '.' || SPLIT_PART(ip,'.', 3) || '.0/24' as cidr from banlist;
select REGEXP_REPLACE(ip, '\.[0-9]{1,3}$', '.0/24') as cidr from banlist;
select REGEXP_SUBSTR(ip, '^[0-9]{1,3}\.[0-9]{1,3}\.[0-9]{1,3}\.') || '0/24' as cidr from banlist;

I'm not sure which is better performing but I think the regexp ones are easier to read.

In redshift, group by IP network

3 Answers3