Edited at

GeoLite2をBigQueryに格納してGeoLocationを取得(IPアドレス→国情報変換)


やりたいこと

IPアドレスに対してGeoLocation情報を付与したい。

BigQueryに格納しているIPアドレスにCountry,Cityの情報を付与したい。


必要なもの

GeoLite2データ

フリー版はこちらからダウンロード

https://dev.maxmind.com/geoip/geoip2/geolite2/

GeoLite2 City CSVを使う


手順

1

GeoLite2-City-Locations-en.csv (city_locaion)

GeoLite2-City-Blocks-IPv4.csv (city_blocks_ipv4)

をBigQueryにインポート

2.

突き合せのためのテーブルを作成

create table yumano.geolite2_city as 

select * from
yumano.city_location as a
inner join
(SELECT
network,
NET.IPV4_TO_INT64(NET.IP_FROM_STRING(REGEXP_EXTRACT( network, '(.*)/' ) )) as startIpNum,
CAST(NET.IPV4_TO_INT64(NET.IP_FROM_STRING(REGEXP_EXTRACT( network, '(.*)/' ))) + POW(2, 32- CAST(REGEXP_EXTRACT(network, '/(.*)') as INT64 )) -1 as Int64) as endIpNum ,
CAST(TRUNC(NET.IPV4_TO_INT64(NET.IP_FROM_STRING(REGEXP_EXTRACT( network, '(.*)/' )))/(256*256)) as Int64) as ClassB,
ifnull(geoname_id, registered_country_geoname_id )
as uniq_geoname_id, is_anonymous_proxy,is_satellite_provider,postal_code
FROM city_blocks_ipv4 ) as b
on uniq_geoname_id = a.geoname_id



  1. IPアドレスが入っているテーブルとJOIN

select 

ACCESS_IP_ADDRESS,
IFNULL(country_name, 'Other') AS country_name,
IFNULL(city_name, 'Other') AS city_name
FROM (
SELECT
ACCESS_IP_ADDRESS,
NET.IPV4_TO_INT64(NET.IP_FROM_STRING(access_ip_address)) AS clientIpNum,
TRUNC(NET.IPV4_TO_INT64(NET.IP_FROM_STRING(access_ip_address))/(256*256)) AS classB
FROM
yumano.IP_LIST ) AS a
LEFT OUTER JOIN
yumano.geolite2_city AS b
ON
a.classB = b.classB
AND a.clientIpNum BETWEEN b.startIpNum AND b.endIpNum


参考

http://matsukaz.hatenablog.com/entry/2017/08/17/133923