MaxMindという会社が提供しているGeoIP (GeoLite)というデータベースがあるが、おおまかに以下の様な形をしている。
ip_start,ip_end,country
1.0.0.0,1.0.0.255,Australia
1.0.1.0,1.0.3.255,China
最近はGeoLite2というのもあるが、情報としては基本的には同じと思っていい。
- http://dev.maxmind.com/geoip/legacy/geolite/
- http://dev.maxmind.com/geoip/geoip2/geolite2/
- http://dev.maxmind.com/geoip/geoip2/geoip2-csv-databases/
これをそのままMySQL等に入れてしまうとかなり扱いにくく、JOINしようとするととても非効率なクエリにならざるを得ない。
試したわけではないがこういう感じのクエリが思いつく。
SELECT
some_log.time, geoip.country
FROM
some_log
LEFT JOIN
geoip ON (true)
WHERE
geoip.start_ip <= some_log.ip AND some_log.ip <= geoip.end_ip
できればプライマリーキーで一発でJOINしたいので、多少の制度を切り捨てて、IPアドレスを/24でマスクした値を元に国名（国コード）への対応表にしてみる。
つまり、こういう風に使えるテーブルを作る。
SELECT
some_log2.time, geoip.country
FROM (
SELECT
time,
mask24(some_log.ip) AS ip24
FROM
some_log) AS some_log2
LEFT JOIN
geoip ON (geoip.start_ip == some_log2.ip24)
mask24は架空の関数で、実装は((ip >> 8) << 8)とかip & ~((1 << 8) - 1)とかになるはず。
GeoLite2のデータベースを使う
http://dev.maxmind.com/geoip/geoip2/geolite2/ からCSVをダウンロードして展開すると、GeoLite2-Country-Locations.csvとGeoLite2-Country-Blocks.csvというのが入っている。
geoname_id,continent_code,continent_name,country_iso_code,country_name,subdivision_iso_code,subdivision_name,city_name,metro_code,time_zone
1605651,AS,Asia,TH,Thailand,,,,,
1861060,AS,Asia,JP,Japan,,,,,
1814991,AS,Asia,CN,China,,,,,
2077456,OC,Oceania,AU,Australia,,,,,
1269750,AS,Asia,IN,India,,,,,
1733045,AS,Asia,MY,Malaysia,,,,,
1835841,AS,Asia,KR,"South Korea",,,,,
1668284,AS,Asia,TW,Taiwan,,,,,
1819730,AS,Asia,HK,"Hong Kong",,,,,
network_start_ip,network_mask_length,geoname_id,registered_country_geoname_id,represented_country_geoname_id,postal_code,latitude,longitude,is_anonymous_proxy,is_satellite_provider
::ffff:1.0.128.0,113,1605651,1605651,,,,,0,0
::ffff:1.0.64.0,114,1861060,1861060,,,,,0,0
::ffff:1.0.32.0,115,1814991,1814991,,,,,0,0
::ffff:1.0.16.0,116,1861060,1861060,,,,,0,0
::ffff:1.0.8.0,117,1814991,1814991,,,,,0,0
::ffff:1.0.4.0,118,2077456,2077456,,,,,0,0
::ffff:1.0.2.0,119,1814991,1814991,,,,,0,0
::ffff:1.0.0.0,120,2077456,2077456,,,,,0,0
::ffff:1.0.1.0,120,1814991,1814991,,,,,0,0
フォーマットの説明は http://dev.maxmind.com/geoip/geoip2/geoip2-csv-databases/ にある。
geoname_idという番号で両テーブルが紐付いている。geoname_id,registered_country_geoname_id,represented_country_geoname_idの違いは読んでもよくわからない。
network_start_ipが::ffff:で始まっているものはIPv4である。下の方にはIPv6のアドレスも出てくる。network_mask_lengthから96を引くとIPv4のマスク長になる。今回はIPv4だけを対象にする。
変換
変換するためのスクリプトを https://gist.github.com/edvakf/b64cf467c4733b7f7b73 に置いた。
このように使うと、
curl -O https://gist.githubusercontent.com/edvakf/b64cf467c4733b7f7b73/raw/geolite2_country_ipv4_mask_blocks.rb
cat GeoLite2-Country-Blocks.csv | ruby geolite2_country_ipv4_mask_blocks.rb > ipv4_mask24_blocks.tsv
このような一覧ができる。
16809984 1605651
16810240 1605651
16810496 1605651
16810752 1605651
16811008 1605651
16811264 1605651
16811520 1605651
1列目がIPアドレスの/24のブロックをuint32に変換したもので、2つ目がgeoname_idになる。
/24の中に複数の国があるものは便宜的に0と出力してある。そのような行は6657行しか無いので、全体の13910432行に比べれば誤差でしか無い。
geoname_idというのすら取ってしまって国コードを右カラムにしてしまうにはjoinコマンドが便利。
join -1 2 -2 1 <(sort -k 2,2 ipv4_mask24_blocks.tsv) <(cat GeoLite2-Country-Locations.csv | awk -F, '{print $1 "\t" $2}' | sort -k 1,1) | awk '{print $2 "\t" $3}' | sort -n -k 1,1 > result.tsv
geoname_idを0にした行はjoinする相手がいないので最後の結果には出力されない。
追記: Google BigQuery方式
上位16bitを格納したカラムを作ってインデックスを貼ればJOINのコストがだいぶ下がるらしい。
http://dev.maxmind.com/geoip/legacy/geolite/ ここから GeoLite Country を落として展開すると GeoIPCountryWhois.csv というファイルができるので、こんな感じでClassBを付加する。
require 'csv'
CSV.foreach("GeoIPCountryWhois.csv") do |row|
start_ip = row[2].to_i;
end_ip = row[3].to_i;
(start_ip>>16..end_ip>>16).each do |class_b|
puts [class_b, start_ip, end_ip, row[4], row[5]].join("\t")
end
end
SQLクエリはこうなる。
SELECT
some_log2.time, geoip.country
FROM (
SELECT
time,
ip,
ip >> 16 AS class_b
FROM
some_log) AS some_log2
LEFT JOIN
geoip ON (geoip.class_b == some_log2.class_b)
WHERE
geoip.ip_start <= some_log2.ip AND some_log2.ip <= geoip.ip_end
こちらのほうが空間効率がだいぶ良くて、さっきの方式だと1400万行近くだったのが、この方式だと13万行のテーブルを用意するだけで良くなる。