Go to Qiita Advent Calendar 2024 Top
LoginSignup
1
1

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 3 years have passed since last update.

@kouimamu

GeoLite2を利用して、hiveでIPアドレスに国や地域情報を補完する

Last updated at Posted at 2020-02-18

はじめに

ユーザーのログに記録されている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の中間クエリを作成し、それと外部結合することで対応している。

1
1
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
1
1

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?