練習題: 匯入IP國家資料庫至DynamoDB - Part 1 解析資料

IP與地理資訊對應資料庫,在很多場合都會用到, 例如網路廣告業、線上遊戲產業、各種影片、直播鎖區的需求等等. 以前知道有一些免費的這類資料可供下載, 我想試試看將資料灌到 NoSQL 也就是 AWS DynamoDB裡,並測試一下效能如何.

下載資料庫

根據 stackoverflow 鄉民的討論串: Best IP to Country Database , 免費資料庫有下列兩種, 大部分人都推薦前者Maxmind 的產品. 這類資料庫的付費模式大都相同, 免費的資料庫涵蓋了各個國家的ip對照表, 而更詳細的城市、經緯度對照則要付費(年費超過1,000美金).

Maxmind 公司的 GeoIP2 資料庫
ip2nation

下載

http://dev.maxmind.com/geoip/geoip2/geolite2/

解壓縮

裡面的資料有三類:

IPv4 資料庫
IPv6 資料庫
國家名稱與國家代碼的對應表

整理資料

目標

先說明一下此整理資料的目標是: 把上面的 1. ipv4 資料庫 2. 國家名稱代碼對照表 , 兩張表格合併成一張CSV(如下圖), 以利後面匯入到DynamoDB.

整合後的表格欄位如下:

任意相同的字串, 這會是DynamoDB Table的主鍵之一(Partition key)
ip 區段的第一個 ip 轉換成數字, 例如: 1.0.0.0 => 16777216 (另一個主鍵)
ip 區段的最後一個 ip 轉換成數字
國家全名
國家縮寫代碼
CIDR

處理過程後面會再詳細敘述.

1. ipv4 資料庫

GeoLite2-Country-Blocks-IPv4-csv 記載著 CIDR ,以及國家代碼數字。

提示:

其中第2,3,4個欄位都是國家代碼數字,合併處理一下
少數資料是完全沒有國家代碼的

2. 國家名稱代碼對照表

GeoLite2-Country-Locations-en.csv 會用到的欄位有國家代碼數字、國家代碼縮寫、國家名稱

提示:

請用UTF8 讀檔進來, 有些國家名稱是 UTF8字元.
有些國家名稱有逗號, 例如這個國家 "Bonaire, Sint Eustatius, and Saba", 處理字串時須注意這稍微難纏的狀況.

解析

IPv4資料庫是這樣子的:

1.0.0.0/24,2077456,2077456,,0,0
1.0.1.0/24,1814991,1814991,,0,0
1.0.2.0/23,1814991,1814991,,0,0

解析工作主要有二:

把 CIDR 轉換成IP區間, 例如: 1.0.0.0/24 -> 1.0.0.0 ~ 1.0.0.255
把 IP 轉換成數字, 例如: 1.0.0.0 -> 16777216

所有的IP都可以轉換數字, 這樣轉換是為了匯入到資料庫後, 我們可以用一些大於小於的條件來做查詢.

1. CIDR 轉換成 IP 區間

CIDR用很容易的方式, 在IP後面加個斜線數字, 就能輕鬆說明子網路遮罩.例如:

a.b.c.d/32 -> 子網路遮罩255.255.255.255 -> 1個 IP
a.b.c.d/24 -> 子網路遮罩255.255.255.000 -> 256個 IP
a.b.c.d/16 -> 子網路遮罩255.255.000.000 -> 65536個 IP

使用 Python的朋友,可以安裝 netaddr 這個 lib, 可以讓你的工作簡單一些. 一次完成兩樣工作. 使用其他程式語言的玩家, 可以用 " convert cidr to ip range stackoverflow " 當關鍵字搜尋.

from netaddr import IPNetwork

ip = IPNetwork('1.0.0.0/24')

print('ip.network   :%s' % ip.network)
print('ip.broadcast :%s' % ip.broadcast)
print('ip.size      :%s' % ip.size)
print('ip.first     :%s' % ip.first)
print('ip.last      :%s' % ip.last)

執行結果:
ip.network   :1.0.0.0
ip.broadcast :1.0.0.255
ip.size      :256
ip.first     :16777216
ip.last      :16777471

2. 把 IP 轉換成數字

假設IP是 w.x.y.z , 轉換成數字的公式是:

IP Number = 16777216*w + 65536*x + 256*y + z

假如你是Python玩家,只想用內建 lib的話,以下是範例:

import socket
import struct

def ip2int(addr):
    '''ip to int
    '''
    return struct.unpack("!I", socket.inet_aton(addr))[0]

print(ip2int('168.95.1.1'))

執行結果:
2824798465

使用 MySQL 也可以

對於 SQL 比較熟悉的朋友, 不一定要用本篇寫程式轉換的方式. 有另外的路,就是使用MySQL Import CSV ,然後再把兩張Table Join Select出來. 剛才查了一下 StackOverFlow ,看了其他人的方法,發覺自己好像繞了一條比較遠的路 XD

maxmind geoip country database installation instructions in mysql database for windows os

下一篇談談如何匯入 DynamoDB.