bgpdump to postges

bgpdump_to_asn.py

# echo 'DROP TABLE IF EXISTS asn_lookup; CREATE TABLE asn_lookup (prefix INET, asn BIGINT, name VARCHAR(500))' | psql
# bgpdump -m latest-bview.gz | python bgpdump_to_asn.py data-used-autnums | psql -c 'COPY asn_lookup FROM STDIN'
# echo 'CREATE INDEX asn_prefix ON asn_lookup (prefix);' | psql
# echo "SELECT * FROM asn_lookup WHERE prefix >> '`curl -s http://wtfismyip.com/text`';" | psql

import fileinput
import sys
import re

reload(sys)
sys.setdefaultencoding('utf-8')

namemap = {}

# read company names
sys.stderr.write("loading company list\n")
for line in fileinput.input(sys.argv[1], openhook = fileinput.hook_encoded("iso-8859-1")):
	m = re.match(' *([^ ]+) (.+)', line)
	if m:
		namemap[m.group(1)] = m.group(2)

sys.stderr.write("loaded %u names\n" % len(namemap))

def escape(s):
	return s.replace("\\", "\\\\").replace("\t", "\\T").replace("\n", "\\N").replace("\r", "\\R")

dedup = set()
n = 0

sys.stderr.write("reading bgpdump from stdin\n")
for line in fileinput.input('-'):
	parts = line.split('|')
	asn = parts[6].strip('{}').replace(',', ' ').split(' ')[-1].strip('{}')

	if '' == asn:
		continue

	if parts[5] in dedup:
		continue

	dedup.add(parts[5])
	sys.stdout.write("\t".join((
		escape(parts[5]), # prefix
		escape(asn), # asn
		escape(namemap.get(asn, '')) # name
	)) + "\n")

	n = n + 1
	if n % 1000 == 0:
		sys.stderr.write(".")
		sys.stderr.flush()

sys.stderr.write("\nimported %u nets\n" % n)