9
Help us understand the problem. What are the problem?

More than 5 years have passed since last update.

posted at

updated at

Organization

GeoIPのデータベースを変換して、マスクしたIPアドレスから国名への対応表を作る

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というのもあるが、情報としては基本的には同じと思っていい。

これをそのまま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というのが入っている。

GeoLite2-Country-Locations.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",,,,,
GeoLite2-Country-Blocks.csv
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

このような一覧ができる。

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万行のテーブルを用意するだけで良くなる。

Register as a new user and use Qiita more conveniently

  1. You can follow users and tags
  2. you can stock useful information
  3. You can make editorial suggestions for articles
What you can do with signing up
9
Help us understand the problem. What are the problem?