はじめに

ユーザーのログに記録されているIPアドレスを用いて、国や地域情報が補完できれば、地域別の分析が可能となる。無料でダウンロードできるMaxMind社のGeoLite2を利用して、hiveにてIPアドレスに国や地域情報を補完するやり方を紹介する。

GeoLite2ダウンロード

MaxMind社 (https://www.maxmind.com) にて、GeoLite2のバイナリファイルとcsvファイルが用意されている。今回の手順ではcsvファイルをダウンロードし、hiveのテーブルにデータをロードして利用する。

今回はこれをダウンロード
- GeoLite2-City-CSV_20200211.zip
利用ファイル
- GeoLite2-City-Blocks-IPv4.csv ・・・IP - 地域のマッピングデータ
- GeoLite2-City-Locations-ja.csv ・・・地域コードのマスタデータ

データ加工

GeoLite2-City-Blocks-IPv4.csv は 1.0.64.0/23 のようにCIDR表記となっており、hiveで利用しやすいように from_ip, to_ip にデータ加工する。また、from_ip, to_ipのレンジでselectした場合に少ないデータ量だったとしても処理が重くて動かない。そこで google で紹介されているやり方を参考に ClassBカラムを追加し、論理パーティションのイメージで、アクセスするデータ量を減らすことで対応する。

■例1
1.0.64.0/23
↓
class_b 1.0.0.0, from 1.0.64.0, to 1.0.65.255

■例2
1.48.0.0/15
↓
class_b 1.48.0.0, from 1.48.0.0, to 1.48.255.255
class_b 1.49.0.0, from 1.49.0.0, to 1.49.255.255

※geoip2-csv-converter ・・・オフィシャルで用意されているscriptはclass_bカラムが対応されていないので、自前で加工scriptを用意する。

★自前のデータ加工script★ (geolite2_city_ipv4_mask_blocks.pl)

#!/usr/local/bin/perl

use strict;
use warnings;

my $class_b_mask = 16;
my $class_b_num = 65536;
my $class_b_maskbit = &bits2netmask_int($class_b_mask);

while(<STDIN>) {
  chomp();

  # network,geoname_id,registered_country_geoname_id,represented_country_geoname_id,is_anonymous_proxy,is_satellite_provider,postal_code,latitude,longitude,accuracy_radius
  # ex: 1.0.64.0/23,1862415,1861060,,0,0,730-0000,34.4000,132.4500,1
  # ex: 1.48.0.0/15,1809445,1814991,,0,0,,26.5833,106.7167,50
  my @rec  = split(/,/);
  if ($rec[0] eq "network"){
    next; # skip header
  }

  my @network = split(/\//, $rec[0]); # network
  my $ip      = &str2num($network[0]);
  my $mask    = $network[1];
  my $maskbit = &bits2netmask_int($mask);

  my $from_ip = &get_network($ip, $maskbit);
  my $to_ip   = &get_broadcast($ip, $maskbit);
  my $diff = $to_ip - $from_ip + 1;

  if ($diff > $class_b_num) {
    # 1.48.0.0/15
    # -->
    # class_b 1.48.0.0, from 1.48.0.0, to 1.48.255.255
    # class_b 1.49.0.0, from 1.49.0.0, to 1.49.255.255
    #
    # ex:
    # in : 1.48.0.0/15,1809445,1814991,,0,0,,26.5833,106.7167,50
    # out: 19922944,19922944,19988479,1.48.0.0/15,1809445,1814991,,0,0,,26.5833,106.7167,50
    # out: 19988480,19988480,20054015,1.48.0.0/15,1809445,1814991,,0,0,,26.5833,106.7167,50

    my $tmp_from_ip = $from_ip;
    my $tmp_to_ip = $to_ip;
    my $tmp_diff = $diff;
    my $tmp_class_b;
    while ($tmp_diff > 0) {
      $tmp_from_ip = &get_network($tmp_from_ip, $class_b_maskbit);
      $tmp_to_ip = &get_broadcast($tmp_from_ip, $class_b_maskbit);
      $tmp_diff = $tmp_diff - 65536;
      $tmp_class_b = &get_network($tmp_from_ip, $class_b_maskbit);
      print "$tmp_class_b,$tmp_from_ip,$tmp_to_ip," . join(",", @rec) ."\n";
      $tmp_from_ip = $tmp_to_ip + 1;
    }
  } else {
    # 1.0.64.0/23
    # -->
    # class_b 1.0.0.0, from 1.0.64.0, to 1.0.65.255
    #
    # ex:
    # in : 1.0.64.0/23,1862415,1861060,,0,0,730-0000,34.4000,132.4500,1
    # out: 16777216,16793600,16794111,1.0.64.0/23,1862415,1861060,,0,0,730-0000,34.4000,132.4500,1

    my $class_b = &get_network($from_ip, $class_b_maskbit);
    print "$class_b,$from_ip,$to_ip," . join(",", @rec) ."\n";
  }
}

sub str2num($) {
    my ($str) = @_ ;
    my $num = 0;

    if ($str !~ /([0-9]+)\.([0-9]+)\.([0-9]+)\.([0-9]+)/) {
      # die "Cannot parse IP addr: $str ";
    }
    $num = $1;
    $num *= 0x0100;
    $num += $2;
    $num *= 0x0100;
    $num += $3;
    $num *= 0x0100;
    $num += $4;
    return $num;
}

sub num2str($) {
    my ($num) = @_ ;
    return sprintf("%d.%d.%d.%d",
                 ($num & 0xff000000) >> 24,
                 ($num & 0x00ff0000) >> 16,
                 ($num & 0x0000ff00) >> 8,
                 ($num & 0x000000ff));
}

sub bits2netmask_int($) {
    my ($bit) = @_ ;
    my $nm;
    $nm = ~((1 << 32 - $bit) - 1);
    return $nm;
}

sub get_network($$) {
    my ($ipa, $netmask) = @_;
    return ($ipa & $netmask);
}

sub get_broadcast($$) {
    my ($network, $netmask) = @_;
    return ($network | ~$netmask);
}

exit;

■加工実施コマンド
$ cat GeoLite2-City-CSV_20200211/GeoLite2-City-Blocks-IPv4.csv | perl geolite2_city_ipv4_mask_blocks.pl > GeoLite2-City-Blocks-IPv4-with-ranges.csv
$ gzip GeoLite2-City-Blocks-IPv4-with-ranges.csv

hiveテーブルへのロード

以下のファイルをhadoopのGWサーバへアップする。

■IP - 地域のマッピングデータ
GeoLite2-City-Blocks-IPv4-with-ranges.csv.gz
※加工したclass_bカラム付きのcsvファイル

■地域コードのマスタデータ
$ gzip GeoLite2-City-Locations-ja.csv
GeoLite2-City-Locations-ja.csv.gz

テーブル作成＋データロード

■IP - 地域のマッピングデータ
create table if not exists XXXXX.geolite2_city_blocks_ipv4(
 class_b                    bigint
 , from_ip                  bigint
 , to_ip                    bigint
 , network                  string
 , geoname_id               string
 , registered_country_geoname_id    string
 , represented_country_geoname_id   string
 , is_anonymous_proxy       string
 , is_satellite_provider    string
 , postal_code              string
 , latitude                 string
 , longitude                string
 , accuracy_radius          string
)
row format delimited fields terminated by ',' lines terminated by '\n'
stored as textfile
location '/user/XXXXX/geolite2_city_blocks_ipv4'
;
load data local inpath 'GeoLite2-City-Blocks-IPv4-with-ranges.csv.gz' overwrite into table XXXXX.geolite2_city_blocks_ipv4;

■地域コードのマスタデータ
create table if not exists XXXXX.geolite2_city_locations_ja(
 geoname_id                 string
 , locale_code              string
 , continent_code           string
 , continent_name           string
 , country_iso_code         string
 , country_name             string
 , subdivision_1_iso_code   string
 , subdivision_1_name       string
 , subdivision_2_iso_code   string
 , subdivision_2_name       string
 , city_name                string
 , metro_code               string
 , time_zone                string
 , is_in_european_union     string
)
row format delimited fields terminated by ',' lines terminated by '\n'
stored as textfile
location '/user/XXXXX/geolite2_city_locations_ja'
;
load data local inpath 'GeoLite2-City-Locations-ja.csv.gz' overwrite into table XXXXX.geolite2_city_locations_ja;

hiveでIPアドレスに国や地域情報を補完する

with raw as (
 select '1.33.38.96' as ip_str
 union all
 select '27.94.192.173' as ip_str
)
, x1 as (
 select
  ip_str
  ,cast(
   cast(split(ip_str, '\\.')[0] as int) * pow(2,24)
   + cast(split(ip_str, '\\.')[1] as int) * pow(2,16)
   + cast(split(ip_str, '\\.')[2] as int) * pow(2,8)
   + cast(split(ip_str, '\\.')[3] as int) * pow(2,0) as int) as ip_integer
 from raw
)
, x2 as (
 select
  ip_str
  ,ip_integer
  ,(ip_integer & ~(cast(((1 * power(2,32 - 16)) - 1)as int))) as class_b
 from x1
)
, x3 as (
 select
  r.ip_str
  ,g.geoname_id
 from x2 r, XXXXX.geolite2_city_blocks_ipv4 g
 where r.class_b = g.class_b
 and from_ip <= ip_integer and to_ip >= ip_integer
)
 select
  r.ip_str
  ,m.continent_name
  ,m.country_name
  ,m.city_name
  ,m.time_zone
 from x3 r, XXXXX.geolite2_city_locations_ja m
 where r.geoname_id = m.geoname_id
;

OK
r.ip_str        m.continent_name        m.country_name  m.city_name     m.time_zone
1.33.38.96      "アジア"        "日本"  "横浜市"        Asia/Tokyo
27.94.192.173   "アジア"        "日本"  "墨田区"        Asia/Tokyo
Time taken: 47.897 seconds, Fetched: 2 row(s)
hive>

★補足
該当のIPアドレスから /16のClassBのネットワークアドレスを計算して利用している。

★問題点
範囲を条件にした外部結合がhiveではサポートされていないので、geolite2_city_blocks_ipv4に存在しているIPアドレスのみしかselectできない。この問題の対応版を後述する。

hiveでIPアドレスに国や地域情報を補完する（外部結合対応版）

with raw as (
 select '1.33.38.96' as ip_str
 union all
 select '27.94.192.173' as ip_str
 union all
 select '224.1.255.1' as ip_str --->データ無しのIP
)
, x1 as (
 select
  ip_str
  ,cast(
   cast(split(ip_str, '\\.')[0] as int) * pow(2,24)
   + cast(split(ip_str, '\\.')[1] as int) * pow(2,16)
   + cast(split(ip_str, '\\.')[2] as int) * pow(2,8)
   + cast(split(ip_str, '\\.')[3] as int) * pow(2,0) as int) as ip_integer
 from raw
)
, x2 as (
 select
  ip_str
  ,ip_integer
  ,(ip_integer & ~(cast(((1 * power(2,32 - 16)) - 1)as int))) as class_b
 from x1
)
, geo as (
 select
  distinct
  r.ip_str
  ,g.geoname_id
 from x2 r, XXXXX.geolite2_city_blocks_ipv4 g
 where r.class_b = g.class_b
 and from_ip <= ip_integer and to_ip >= ip_integer
)
, x3 as (
 select
  r.ip_str
  ,g.geoname_id
 from x2 r left outer join geo g
  on r.ip_str = g.ip_str
)
 select
  r.ip_str
  ,m.continent_name
  ,m.country_name
  ,m.city_name
  ,m.time_zone
 from x3 r left outer join XXXXX.geolite2_city_locations_ja m
  on r.geoname_id = m.geoname_id
;

OK
r.ip_str        m.continent_name        m.country_name  m.city_name     m.time_zone
224.1.255.1     NULL    NULL    NULL    NULL
1.33.38.96      "アジア"        "日本"  "横浜市"        Asia/Tokyo
27.94.192.173   "アジア"        "日本"  "墨田区"        Asia/Tokyo
Time taken: 33.036 seconds, Fetched: 3 row(s)
hive>

★補足
1IPずつの、IPアドレス＋地域補完した、geoの中間クエリを作成し、それと外部結合することで対応している。