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

 * 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しようとするととても非効率なクエリにならざるを得ない。

試したわけではないがこういう感じのクエリが思いつく。

```sql
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でマスクした値を元に国名（国コード）への対応表にしてみる。

つまり、こういう風に使えるテーブルを作る。

```sql
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 に置いた。

このように使うと、

```bash
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コマンドが便利。

```bash
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://googlecloudplatform.blogspot.jp/2014/03/geoip-geolocation-with-google-bigquery.html

http://dev.maxmind.com/geoip/legacy/geolite/ ここから GeoLite Country を落として展開すると GeoIPCountryWhois.csv というファイルができるので、こんな感じでClassBを付加する。

```ruby
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クエリはこうなる。

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