Data, What?/Database

[GCP] IP 주소로 Timezone 분류하기 1(데이터 준비)

GrilleDream 2022. 5. 31. 20:30

IP? Internet Protocol? IP/TCP?

 

2006년의 이 어린이는 얼마나 두려웠을까

 

우리가 매우 흔하게 접하고, 알고 있는 IP 주소

개념은 우리 위키!를 참고하면 되겠고, 

내가 이번에 해보고자 하는 건 IP 주소로 Country 정보를 넘어 Timezone까지 분리하는 작업이다.

 

가장 먼저 드는 의문, 왜? 왜 이 짓을 해야 하느냐?

사실상 미국이 메인이다
알록~ 달록~, 더럽게 화려하다

보시다시피 같은 국가라도 땅덩어리가 넓은 나라는 여러 개의 시간대를 갖는다.(참고로 우리나라는 UTC+09:00 하나다)

 

내가 현재 하고 있는 일이 사용자가 특정 앱을 사용할 때 그에 적절한 광고를 전달하는 것인데, 이 모든 것의 원천은 사람의 라이프-사이클과 면밀하게 연관되어 있다.

 

실제로 트래픽이 가장 활발한 시간대는 당연히 사람들이 스마트폰을 많이 사용하는 저녁이나 이른 새벽 시간대이고, 늦은 새벽에는 트래픽이 급감했다 아침에 다시 돌아오는 주기적인 트렌드를 보인다.

 

그 외에도 앱을 보유한 Publisher는 LTV 등을 측정하여 더 Healthy하고 가치 있는 고객을 유지하고자 노력한다.

 

그러니, 가장 큰 광고 시장을 보유한 미국을 Timezone 별로 분리하여 더 디테일하게 살펴보고자 하는 욕구는 당연하지 않을까?

 

가장 기본적인 IP주소로 국가를 나누는 방법은 여러 소스가 있는데, 우리 회사는 Maxmind에서 제공하는 geolocation 데이터를 활용했다.

 

여기서 뭔가 추가정보를 얻고 싶었는데, 알아보니 프로세싱 과정에서 이미 맵핑하여 기록되기 때문에 추가 개발 요청을 올리기는 어려웠다.

그래서 이를 대체하기 위해 BigQuery 내에서 데이터를 가공하는 방법을 찾아냈다.

 

References

시나리오는 간단하다.

 

IP 주소 값을 Latitude, Longitude 값으로 변환, Timezone 데이터와 맵핑해주면 끝이다.

 

쿼리문

SELECT 
A.*, city, latitude, longitude
FROM (
SELECT
connectiontype, ip, country,
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,
SUM(CASE WHEN x_ymdh IS NOT NULL THEN 1 ELSE 0 END) req
FROM `IP가 저장되어 있는 테이블`
WHERE ymdh = "2022-03-30 00:00:00"
AND ip NOT LIKE "%:%" AND ip LIKE "%.%"
GROUP BY connectiontype, ip, country, clientipnum, classb
) A
LEFT OUTER JOIN
  `lat, long 정보가 포함된 테이블` AS B
ON
  A.classB = B.classB AND A.clientIpNum BETWEEN B.startIpNum AND B.endIpNum
)

1. IP 변환

Bigquery 내에 NET.IPV4_TO_INT64() 이라는 IP 변환 함수가 존재한다.

해당 함수는 IPv4 형태만 변환 가능하므로 IPv6는 제외하였는데, 전체 트래픽의 5% 이하라 큰 임팩트는 없을 것으로 예상했다.

 

2. 좌표값 업로드

위 레퍼런스 있듯이 Polygon 형식의 데이터를 다운 받아 BigQuery에 업로드 해준다.

이 때, UI를 활용한 업로드 용량은 100MB로 제한돼 있으므로 GCS 권한을 받아 넣어주는 걸 추천한다.

(없으면? 나처럼 csv를 쪼개서 올려야지 방법이 없다...)

 

3. latitude, longitude 맵핑

IP 변환값과 좌표를 연결하여 트래픽 별로 위,경도를 구해준다

 

4. Timezone 데이터 업로드

Polygon, Multipolygon 형태로 되어있는 Timezone 데이터를 BigQuery에 업로드 해준다. GCP에서 geography 데이터를 지원하여 사용이 가능하다는 걸 미리 조사해두었다.

데이터 용량이 100MB를 넘어 직접 업로드하지는 못 했고 엔지니어에게 GCS로 업로드를 요청하였다.

 

그런데, 여기서 오류가 뜨며 업로드에 실패하고 말았다.

 

5. 다른 방법 탐색(Dataframe)

오류 메시지를 보면, Timezone 데이터가 제대로 정리되지 않아 발생한 것으로 추정된다고 들었고, JupyterLab에서 Dataframe화 하여 업로드하는 방식을 시험해보기로 했다.

아래의 레퍼런스를 참고하여 GeoPandas 패키지를 설치하였고 데이터 프레임까지 정상적으로 이뤄졌다.

(Tip : 패키지 설치 순서 꼭 지켜야 한다!)

import os
from google.cloud import bigquery
import pandas as pd
import numpy as np
import geopandas as gpd
import shapely.geometry
import shapely.wkt
from datetime import datetime
from pytz import timezone
os.environ['GOOGLE_APPLICATION_CREDENTIALS'] = 'auth키 위치'

bigquery_client = bigquery.Client(project='프로젝트명')

study_area = pd.read_json("Timezone 파일 위치")
gdf = gpd.GeoDataFrame.from_features(study_area["features"])
print(gdf)

 

의지의 한국인! 이쯤 되면 성공하겠지?

제발! 안돼!!

그러나, 여기서 또다시 오류가 발생하고 만다.

 

이후 Local 환경을 공용 환경으로 바꾼다거가, Data type을 변경하는 등의 다른 시도들이 남아있었는데, 다른 프로젝트에 밀려 한동안 서랍 속에 쳐박혀 있었다.

 

그렇게 몇 주 후,

구글에서 제공하는 웨비나 교육을 들으며 문득 Q&A에 위 질문을 올리게 됐는데, 얼마 지나지 않아 아래 링크를 전달 받았다.

 

https://stackoverflow.com/questions/69679608/how-to-get-the-time-zone-from-latitude-and-longitude-coordinates-with-bigquery

https://stackoverflow.com/questions/71480839/how-to-get-time-zone-boundaries-in-bigquery

 

외쳐! 갓구글!!

 

위 방법대로 진행하니 데이터가 깔끔하게 업로드 되었고, 내게 남은 건 쿼리 작성 뿐이었다.

WITH points AS (
  SELECT 
  parsedJson.reqBidinfo.device.ip,
  parsedJson.reqBidinfo.device.geo.country,
  parsedJson.reqBidinfo.device.geo.lat latitude,
  parsedJson.reqBidinfo.device.geo.lon longitude,
  count(*) req
  FROM `IP, 위도, 경도 데이터가 저장된 테이블`
  GROUP BY 1,2,3,4
)

SELECT p.*, tzid FROM points p CROSS JOIN `timezone 저장된 테이블` tz
WHERE ST_Intersects(ST_GeogPoint(p.longitude, p.latitude), tz.geometry) IS TRUE

감사하게도 엔지니어 분이 lat, long 데이터가 추가로 저장되도록 만들어두셨고

(사실 country 변환 과정에서 이슈가 발견돼 확인 작업을 위해 넣은 거지만, 기분이 중요한 거 아니겠는가)

 

이를 활용해 별도의 변환 과정 없이 데이터를 불러왔다.

 

데이터가 준비되었으니 다음은 본격적인 분석 시간이다!