matsukaz's blog

Agile, node.js, ruby, AWS, cocos2d-xなどなどいろいろやってます

BigQuery上でIPアドレスから位置情報を算出する方法

概要

クライアントのIPアドレスを含んだ行動ログをBigQueryに保存し、どの地域からアクセスされているのかを分析したいなと思ったので、やり方をまとめました。

3年以上前の情報ですが、以下の記事を参考にしています。

cloudplatform.googleblog.com

準備

以下のような元データをBigQuery上に用意します。

$ cat test_data.csv
1,192.188.171.9
2,202.41.146.198
3,103.83.231.23
4,202.64.101.91
5,43.240.52.112
6,103.12.247.112
7,217.228.79.15
8,35.10.42.69
9,86.40.207.143
10,66.71.60.27
11,72.136.125.125
12,68.144.88.194
13,194.197.79.18
14,69.119.90.108
15,83.235.250.67

$ bq mk <dataset>.log id:integer,ip:string

$ bq load --source_format=CSV <dataset>.log test_data.csv

IPアドレスと位置情報のマッピングについては、Googleの方がMaxMind社が提供している GeoLite Legacy Downloadable Databases « MaxMind Developer Site をBigQueryに取り込んだものをPublic公開してくれているので、そちらを利用します。ありがたい!

GeoLite2 Free Downloadable Databases « MaxMind Developer Site という新しいバージョンのDBもあるようなのですが、先ほど紹介した記事ではバイナリ形式で提供されていてBigQueryに取り込めなかったとのこと。 いま見たらCSVフォーマットも用意されていたので、より正確で最新のマッピング情報を利用したければ、GeoLite2を利用すると良いかもしれません(取り込むためにはそれなりに対応が必要そうですが)

クエリ

BigQueryはレガシーSQLと標準SQLの2種類があります。 それぞれ書き方が違うのと、一部レガシーSQLでは問題があったので両方の書き方を紹介します。

標準SQL

#standardSQL
SELECT
  id,
  IFNULL(city, 'Other') AS city,
  IFNULL(countryLabel, 'Other') AS countryLabel,
  latitude,
  longitude
FROM (
  SELECT
    id,
    NET.IPV4_TO_INT64(NET.IP_FROM_STRING(ip)) AS clientIpNum,
    TRUNC(NET.IPV4_TO_INT64(NET.IP_FROM_STRING(ip))/(256*256)) AS classB
  FROM
    `<project>.<dataset>.log` ) AS a
LEFT OUTER JOIN
  `fh-bigquery.geocode.geolite_city_bq_b2b` AS b
ON
  a.classB = b.classB
  AND a.clientIpNum BETWEEN b.startIpNum AND b.endIpNum
ORDER BY
  id ASC

実行結果は以下の通りです。

Row id city countryLabel latitude longitude
1 1 Other Other null null
2 2 Taiwan 23.5000 121.0000
3 3 Other Other null null
4 4 Hong Kong 22.2500 114.1667
5 5 Japan 35.6900 139.6900
6 6 Bangladesh 24.0000 90.0000
7 7 Heiden Germany 51.8333 6.9333
8 8 East Lansing United States 42.7283 -84.4882
9 9 Buncrana Ireland 55.1333 -7.4500
10 10 Collegeville United States 40.1879 -75.4254
11 11 Canada 60.0000 -95.0000
12 12 Calgary Canada 51.0833 -114.0833
13 13 Helsinki Finland 60.1756 24.9342
14 14 Bellmore United States 40.6726 -73.5364
15 15 Greece 39.0000 22.0000

Query complete (4.5s elapsed, 112 MB processed)

標準SQLならJOIN時に範囲条件を指定することができるので、IPアドレスにマッチングしない地域があったらOtherと表示する、といった外部結合が可能でした。

レガシーSQL

#legacySQL
SELECT
  id,
  city,
  countryLabel,
  latitude,
  longitude
FROM (
  SELECT
    id,
    INTEGER(PARSE_IP(ip)) AS clientIpNum,
    INTEGER(PARSE_IP(ip)/(256*256)) AS classB
  FROM
    [<project>:<dataset>.log] ) AS a
LEFT OUTER JOIN
  [fh-bigquery:geocode.geolite_city_bq_b2b] AS b
ON
  a.classB = b.classB
WHERE
  a.clientIpNum BETWEEN b.startIpNum AND b.endIpNum
ORDER BY
  id ASC

実行結果は以下の通りです。

Row id city countryLabel latitude longitude
1 2 Taiwan 23.5000 121.0000
2 4 Hong Kong 22.2500 114.1667
3 5 Japan 35.6900 139.6900
4 6 Bangladesh 24.0000 90.0000
5 7 Heiden Germany 51.8333 6.9333
6 8 East Lansing United States 42.7283 -84.4882
7 9 Buncrana Ireland 55.1333 -7.4500
8 10 Collegeville United States 40.1879 -75.4254
9 11 Canada 60.0000 -95.0000
10 12 Calgary Canada 51.0833 -114.0833
11 13 Helsinki Finland 60.1756 24.9342
12 14 Bellmore United States 40.6726 -73.5364
13 15 Greece 39.0000 22.0000

Query complete (2.5s elapsed, 112 MB processed)

標準SQLとは違い、レガシーSQLはJOIN時に範囲条件を指定できないため、外部結合ができずにレコードが2つ結果として取得できなくなっています。 用途によっては標準SQLじゃないと困る、といった場面がありそうですね。

まとめ

完璧な精度というわけではないですが、IPアドレスからそれなりに地域を特定することができました。 これで国別のアクセス数などの分析がはかどりますね!(・∀・)

また参考にした記事のコメントにて、「国レベルでよければもっと効率的な方法があるよ」と紹介してくださってる方がいました。以下、その内容のリンクです。

plus.google.com

後日試してみよっと。

Gistにもクエリなどを残しておきました。

Find a geolocation of an IP address in BigQuery · GitHub

2017/08/17 14:13 追記

上記のコメントの内容をサクッと試せたので、そちらを追記します。

標準クエリ

SELECT
  id,
  geoip.Country_Name AS country
FROM (
  SELECT
    id,
    NET.IPV4_TO_INT64(NET.IP_FROM_STRING(ip)) AS ip,
    TRUNC(NET.IPV4_TO_INT64(NET.IP_FROM_STRING(ip))/(256*256*256)) AS ip_class_a
  FROM
    `<project>.<dataset>.log` ) AS main
LEFT OUTER JOIN
  `fh-bigquery.geocode.geolite_country_bq` AS geoip
ON
  main.ip_class_a = geoip.classA
  AND main.ip BETWEEN geoip.From_IP_Code
  AND geoip.To_IP_Code
ORDER BY
  id

実行結果は以下の通りです。

Row id country
1 1 NA
2 2 Taiwan
3 3 NA
4 4 Hong Kong
5 5 Japan
6 6 Bangladesh
7 7 Germany
8 8 United States
9 9 Ireland
10 10 United States
11 11 Canada
12 12 Canada
13 13 Finland
14 14 United States
15 15 Greece

Query complete (1.8s elapsed, 2.81 MB processed)

おお!!こっちのクエリだと実行時間も早いし、アクセスするデータ量も1/37ぐらいに抑えられました!

レガシークエリ

SELECT
  id,
  geoip.Country_Name AS country
FROM (
  SELECT
    id,
    INTEGER(PARSE_IP(ip)) AS ip,
    INTEGER(PARSE_IP(ip)/(256*256*256)) AS ip_class_a
  FROM
    [<project>:<dataset>.log] ) AS main
JOIN EACH [fh-bigquery:geocode.geolite_country_bq] AS geoip
ON
  main.ip_class_a = geoip.classA
WHERE
  main.ip BETWEEN geoip.From_IP_Code
  AND geoip.To_IP_Code
ORDER BY
  id

実行結果は以下の通りです。

Row id country
1 1 NA
2 2 Taiwan
3 3 NA
4 4 Hong Kong
5 5 Japan
6 6 Bangladesh
7 7 Germany
8 8 United States
9 9 Ireland
10 10 United States
11 11 Canada
12 12 Canada
13 13 Finland
14 14 United States
15 15 Greece

Query complete (1.4s elapsed, 2.81 MB processed)

こちらもはるかに高速!

あと該当がないときは地域をNAとして表示できるので、レガシーSQLでも問題なく使えますね。素晴らしいヽ(=´▽`=)ノ

ビッグデータ分析・活用のためのSQLレシピ

ビッグデータ分析・活用のためのSQLレシピ

ビッグ (字幕版)

ビッグ (字幕版)