BigQuery上でIPアドレスから位置情報を算出する方法
概要
クライアントのIPアドレスを含んだ行動ログをBigQueryに保存し、どの地域からアクセスされているのかを分析したいなと思ったので、やり方をまとめました。
3年以上前の情報ですが、以下の記事を参考にしています。
準備
以下のような元データを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アドレスからそれなりに地域を特定することができました。 これで国別のアクセス数などの分析がはかどりますね!(・∀・)
また参考にした記事のコメントにて、「国レベルでよければもっと効率的な方法があるよ」と紹介してくださってる方がいました。以下、その内容のリンクです。
後日試してみよっと。
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でも問題なく使えますね。素晴らしいヽ(=´▽`=)ノ
- 作者: 加嵜長門,田宮直人,丸山弘詩
- 出版社/メーカー: マイナビ出版
- 発売日: 2017/03/27
- メディア: 単行本(ソフトカバー)
- この商品を含むブログを見る
- 発売日: 2015/10/08
- メディア: Amazonビデオ
- この商品を含むブログを見る