IPアドレスに都市名やドメイン名を付加する方法

皆さんこんにちは、h_matsumotoです。
最近レンタルが解禁された『君の名は。』を見て感動し、その後まだ上映している映画館に見に行きました（まさかの二度見）。

本日ご紹介する内容はIPアドレスの活用方法についてです。

Treasure Dataに新しく追加されたIPアドレスの関数

Treasure Dataで8月にリリースされたPrestoの新しい関数

• TD_IP_TO_CITY_NAME　　 IPアドレスから都市名を取得
• TD_IP_TO_POSTAL_CODE　IPアドレスから郵便番号を取得
• TD_IP_TO_DOMAIN　　　　IPアドレスからドメイン名を取得

以上の関数についてご紹介いたします。元々IPアドレスから国を取得する関数は存在していたのですが、都市や郵便番号が分かることで日本国内のどこからアクセスが多いのか把握する事が出来るようになります。

※8月からリリースノートが日本語版PDFでも閲覧出来るようになりました。

使い方

「.」（ドット）で区切られているIPアドレスを引数に取り変換します。

SELECT
  TD_IP_TO_CITY_NAME('1.66.96.0') "都市名",
  TD_IP_TO_POSTAL_CODE('1.66.96.0') "郵便番号",
  TD_IP_TO_DOMAIN('1.66.96.0') "二次レベルのドメイン"

実行結果

もしIPアドレスを10進数の数値としてDBに保存している場合は以下のコードで「.」（ドット）に戻してから関数に渡します。

SELECT
  CONCAT(CAST(CAST(FLOOR(remote / POWER(256, 3)) as bigint) AS VARCHAR),'.',
        CAST(CAST(FLOOR(remote / POWER(256, 2)) - FLOOR(remote / POWER(256, 3)) * 256 as bigint) AS VARCHAR),'.',
        CAST(CAST(FLOOR(remote / POWER(256, 1)) - FLOOR(remote / POWER(256, 2)) * 256 as bigint) AS VARCHAR),'.',
        CAST(CAST(FLOOR(remote / POWER(256, 0)) - FLOOR(remote / POWER(256, 1)) * 256 as bigint) AS VARCHAR)) as ip
FROM
  click
--remoteは10進数のIPアドレスが保存されたフィールド名

実行結果

Prestoだと以上の関数によって変換可能ですが、Hiveではまだ未実装のため別の手段を取る必要があります。

関数が追加される前に当社が変換していた方法

関数が追加されて大変便利なのですが、実は当社も以前からIPアドレスに位置情報やホスト名を付加して利用しておりました。今回はTreasure Dataを利用しなくてもIPアドレスにそれらの情報を付加する方法もご紹介いたします。

使用言語

Python3.5

IPアドレスに位置情報を付加

・データの入手元
当社もMaxMind社のオープンソースデータを利用させて頂いておりました。こちらは無料版ですが、より精度が高い有料サービスもあります。

・変換方法
上記から入手したCSVデータの中身は以下のようになっています。
GeoLiteCity-Blocks

GeoLiteCity-Location

実際の処理内容を簡単に説明しますと...
GeoLiteCity-Locationのデータを日本だけに絞ります。
海外の位置情報データを使う場合は削除不要です。

locIdをキーとしてlocationとblocksデータをくっつけます。

最後に位置情報を付与したいipアドレスがどこのlocIdの範囲に該当するか見つけてipアドレスにlocIdを付与します。

サンプルコード

#ライブラリー
import numpy as np
import pandas as pd
from pandas import Series,DataFrame
import requests
import zipfile
import os
import codecs

MAXMIND_FILE_URL = 'http://geolite.maxmind.com/download/geoip/database/GeoLiteCity_CSV/GeoLiteCity-latest.zip'

#関数の定義

#ホームページからファイルをダウンロードする
def download_file(url):
    filename = url.split('/')[-1]
    r = requests.get(url, stream=True)
    with open(filename, 'wb') as f:
        for chunk in r.iter_content(chunk_size=1024):
            if chunk:
                f.write(chunk)
                f.flush()
        return filename
    
    return False

#Zipファイルを展開
def zip_extract(filename):
    zfile = zipfile.ZipFile(filename)
    zfile.extractall('.')
    return zfile

#GeoLiteCity-Location.csvのデータを開いてデータフレームにする
def get_location_data(zfile):
    with codecs.open(os.getcwd() +'/' + zfile.namelist()[1], "r", "Shift-JIS", "ignore") as file:
        return pd.read_table(file, delimiter=",",skiprows=1,na_filter=False) 

#GeoLiteCity-Blocks.csvのデータを開いてデータフレームにする
def get_block_data(zfile):
    return pd.read_csv(os.getcwd() +'/' + zfile.namelist()[0],encoding='utf-8',skiprows=1)


#日本以外のデータを削除する
def delete_countries_other_than_japan(data_frame,ip_data_frame):
    return data_frame[(data_frame['remote'] >= ip_data_frame['startIpNum'].min()) & (ip_data_frame['endIpNum'].max() >= data_frame['remote'])]

#IPアドレスにlocidを付与する
def add_location_to_ip(ip_address,ip_array,ip_start,ip_end):
    min_ = len(ip_start[ip_start <= ip_address]) - 1
    max_ = len(ip_end) - len(ip_end[ip_end >= ip_address])
    if min_ == max_ :
        return {'remote':ip_address,
                   'locid':ip_array[min_][0],
                  'country':ip_array[min_][1],
                  'city':ip_array[min_][3],
                  'latitude':ip_array[min_][5],
                  'longitude':ip_array[min_][6],
                  'postalCode':ip_array[min_][4]}

#ここからは実際の処理です
filename = download_file(MAXMIND_FILE_URL)
zfile = zip_extract(filename)
location_df = get_location_data(zfile)
location_df = location_df.query("country == 'JP' and region != ''")
blocks_df = get_block_data(zfile)
ip_df = pd.merge(location_df,blocks_df,how='left',on='locId').dropna().sort('startIpNum',ascending=True).reset_index(drop=True)

#計算を早くするためにarrayにする
ip_array = np.array(ip_df)
ip_start = np.array(ip_df['startIpNum'])
ip_end = np.array(ip_df['endIpNum'])

add_location_to_ip(ip_address,ip_array,ip_start,ip_end)

実行結果

IPアドレスにホスト名を付加

socketライブラリーを使用します。

import socket

def add_hostname_to_ip(ip_address):
    jSLD = ['ac', 'ad', 'co', 'ed', 'go', 'gr', 'or', 'lg', 'ne']
    try:
        tmp = (socket.gethostbyaddr(ip_address)[0])
        h = ""
        w = tmp.split('.')
        if w[-1] == 'jp' and w[-2] in jSLD:
            h = w[-3] + "." + w[-2] + "." + w[-1]
        else:
            h = w[-2] + "." + w[-1]
        return h
    except:
        return None

実行結果

IPアドレスを活用する上での注意事項

IPアドレスの位置情報や契約者は不変ではありません。MaxMind社のデータも月1回更新されています。よって1年前のIPアドレスに現在の位置情報データを結び付けてもそれは現在の位置情報としては正しいですが、1年前の時点では異なる位置を示していたかもしれません。
IPアドレスの位置情報データを活用するには常に新しい位置情報データとIPアドレスを結び付けてDB等に保存しておく必要があります。

如何だったでしょうか？既にあるデータを掘り起こし価値を見出すのはデータマイニングの醍醐味ですね。

最後に

ファンコミュニケーションズでは機械学習エンジニアを募集しています。

主な開発言語はScala, PHPですが、分析作業においてはPythonやRも多用しています。
また分析環境としてTreasure Dataを導入しているため、SQLによって学習データの作成・機械学習をシームレスに行うことができ、データクレンジングなどの雑務に追われることなく非常に大きなデータを扱える環境が整っています。

興味がある方は以下のページをご覧ください。（Webアプリケーションエンジニアと書いてありますが、機械学習エンジニアも含んでいますので安心してください。）

www.fancs.com