이번 주 역시 여러 과제들을 풀며 서버에 적재되어 있는 많은 DB에 익숙해지는 시간을 가졌고, Hive 쿼리를 직접 작성하며 필요에 맞게 데이터를 추출하는 연습을 했습니다. 쿼리문을 이론으로 접할 때와 달리 실제로 쓰이는 문제들을 가지고 쿼리를 짜는 연습을 하다 보니, 생각지 않은 장애물들도 많았습니다. 이번 포스팅에서는 Hive 쿼리를 작성하며, 실제로 겪었던 장애물들에 대해 구체적인 사례를 소개하고, 어떤 쿼리를 작성하였는지, 무엇이 어려웠는지, 어떻게 해결해 나갔는지 조금 자세하게 작성해보려 합니다.

문제 예시

다음과 같은 문제가 주어졌다고 생각해봅시다.

“블레이드&소울 게임에서 6월 첫째 주에 접속한 유저들이 퀘스트를 완료한 횟수와 완료율을 에픽/어트랙션/일일/주간 카테고리로 나누어 각각 Top 10을 구하고, 그 퀘스트에서 얻은 금 수량을 추출하세요.”

블레이드&소울 퀘스트 완료 화면

문제와 완전히 맞아 떨어지도록 한 주마다 유저들이 퀘스트를 완료한 횟수와 그를 통해 얻은 금을 카테고리별로 상위 10개씩 기록하는 로그가 남으면 가장 좋겠지만, 이는 조금만 생각해보아도 굉장히 비효율적이라는 것을 깨달을 수 있겠죠? 모든 문제 상황에 맞는 DB를 새롭게 적재하여 일회성으로 사용하기 보다는 분석이나 모니터링에 필요한 최소한의 로그를 남기고, 이러한 테이블들을 병합하여 필요에 맞게 만들어내야 할 것입니다!

풀이 과정

그렇다면 다시 문제로 돌아와서, 어떤 테이블을 찾고 어떻게 병합하면 될지 문제를 조각 내어 좀 더 자세하게 이해해볼 필요가 있을 것입니다.

  • 블레이드&소울 DB

저희 회사에서 서비스하고 있는 여러 게임들 중 당연히 블레이드&소울과 관련하여 로그를 적재하고 있는 DB를 봐야 할 것입니다.

  • 6월 첫째 주, 즉, NC Time 기준으로 2018-06-06 06:00:00에서 2018-06-13 06:00:00 직전

실제 시간과 달리 게임 세계에서는 수요일 06시를 기준으로 서버 점검이 있고, 일일 아이템이나 퀘스트가 초기화됩니다. 따라서, 그에 맞추어 6월 첫째 주를 재정의합니다.

  • 완료 횟수와 완료율을 구하기 위해 정해진 기간 내 접속한 모든 유저들의 퀘스트 시작/완료 로그가 남는 테이블을 참고

앞서 작성했던, 분석이나 모니터링에 필요한 최소한의 로그 중 하나인 퀘스트 시작/완료 테이블에는 각 유저마다 행동이 발생하는 시간, 계정 코드, 퀘스트 코드 등이 적재됩니다. 예를 들어, ‘A가 2018-06-06 21:52:50에 퀘스트 B를 획득’과 ‘A가 2018-06-06 22:30:29에 퀘스트 B를 완료함’ 이라는 정보를 알 수 있습니다. 완료 횟수는 후자의 테이블에서 개수를 세어주면 되고, 완료율은 전자의 테이블에서 유저들이 받은 퀘스트 개수를 세고 완료 횟수와 결합하여 계산할 수 있을 것입니다.

  • 퀘스트 코드와 매칭하여 퀘스트의 실제 이름을 보여주는 테이블

퀘스트의 실제 한글 이름은 또 다른 테이블에서 적재되어 있고 필요에 따라 코드 번호로 매칭할 수 있습니다. 예를 들어 B라는 퀘스트가 ‘최강의 창, 최강의 방패’라는 이름이라는 것을 이 테이블과 병합하여 알 수 있습니다.

  • 퀘스트별로 에픽, 어트랙션, 일일, 주간을 구분할 수 있는 테이블

위와 비슷하게 퀘스트 코드별로 이 퀘스트가 어떤 카테고리인지를 나타내 주는 테이블이 따로 있습니다. 에픽, 일일, 주간, 에픽, 어트랙션, 직업 등 세분화하여 기록됩니다.

  • 카테고리 별로 10위까지 순위를 매기는 함수

회사에서 제공하는 사용자 정의 함수를 이용하면, 어떤 것을 기준으로 무엇을 카운트하여 순위를 매길지 직접 정하여 사용할 수 있습니다.

  • 퀘스트에서 얻은 금 수량 (->없음)

퀘스트에서 얻은 아이템은 기록되지만 게임 화폐는 기록되지 않았습니다. 이를 몰랐기에 처음 시도로 아이템 카테고리에 있는 ‘금’의 코드번호를 찾아 개수를 세어 문제를 풀었습니다. 멘토님께 결과를 보고하니 아이템 ‘금’과 게임 내 화폐인 ‘금’이 이름은 같지만 서로 다른 것이기에 다른 테이블을 살펴보아야 한다고 하셨습니다. 마찬가지로, 아이템획득 로그 기록도 적재되고 있었지만, 이곳에서 역시 화폐인 ‘금’은 기록되고 있지 않았습니다.

  • 게임 머니 증가관련 테이블

퀘스트에서 얻은 화폐 ‘금’ 수량은 직접적으로 찾을 수 없었지만, 사유별로 게임 머니 증가를 기록하는 테이블은 찾을 수 있었습니다. 그렇지만, 이 테이블에는 ‘어떤’ 퀘스트에서 얻은 ‘금’인지는 적재되어 있지 않았습니다. 그렇기 때문에 같은 시간, 같은 계정에서 퀘스트 완료라는 사유로 얻은 ‘금’을 위에서 작성한 퀘스트 완료 로그와 병합하였고, 간접적으로 B라는 퀘스트에서 C만큼의 화폐 ‘금’을 얻었다고 추정할 수 있었습니다.

주의할 점

실제로 위의 내용을 쿼리로 작성한다면 위에서 하나하나 작성한 조건들을 모두다 잘 고려하여 작성해야 합니다. 더욱이 방대한 양의 데이터로 진행되고 여러 사람들이 함께 시스템을 사용하여 데이터 추출을 진행하는 만큼 시스템에 과도한 부하를 주지 않도록 작성해야 합니다. 또한, 한 번 잘 못 작성된 쿼리를 디버깅 하는 것은 쿼리 실행 시간이 짧게는 1분 길게는 10분정도 소요되기 때문에 신중하게 작성해야 할 것입니다.

간단해 보이는 한 문제를 푸는 것에도 여러 디테일에 익숙해지지 못한다면 굉장히 많은 시간이 소요될 수 있습니다. 이처럼 많은 연습이 필요한 쿼리 작성, 몸소 느끼며 열심히 배우고 익숙해지는 시간을 가지고 있습니다!