[Database] 이것만 알면 로그 데이터 완전 정복!

로그 데이터와 이를 효과적으로 집계하는 방법에 대해 알아보자

개요

데이터 엔지니어라면 당연지사 빅데이터를 다룰 것이다. 빅데이터엔 여러 종류의 데이터가 있지만, 보통 실무에선 로그 데이터가 대표적이다. 끊임없이 들어오는 로그 데이터를 잘 정리하고 집계하여 DM을 구축하거나, 로그 데이터에 쿼리를 날려 원하는 데이터로 가공하는 것은 데이터 엔지니어의 필수 업무가 아닐까 싶다. 학부생 시절엔 간단한 SQL 쿼리만 작성해봐서 이것들이 실무에 어떻게 적용되는지 알기가 어렵다 (저만 그런걸까요? 🤔). 때문에 이 글을 통해서 실무의 데이터 처리 방법을 간접적으로나마 알아갈 수 있었으면 좋겠다. 😄

로그 데이터

우선 로그 데이터란 무엇일까? 로그(log) 란 쉽게 말하면 기록이다. 이를 IT 업계에서는 인프라에서 발생된 모든 데이터라고 부르지만, 서비스 레벨에선 사용자의 발자취라고 이해하는게 편하다. 사용자가 어떤 서비스를 이용한다면, 그 서비스를 운영하는 회사에선 사용자의 로그가 남는다. 그리고 회사는 로그들을 통해 사용자의 경험을 분석하고 니즈를 파악해 서비스를 개선한다.

데이터 구조

어떤 서비스를 다루느냐에 따라 로그 데이터 구조는 천차만별이다. 회사의 서비스에 맞게, 그리고 여러 부서간의 협업으로 로그 데이터 구조는 결정된다. 단순히 웹사이트를 운영하는 회사라 할지라도, 사용자가 웹 서버에 접속한 로그 데이터엔 유저의 정보 및 IP 주소를 포함하여, 웹 브라우저 정보, 그리고 그 브라우저의 버전 등 엄청나게 다양한 정보가 남게된다 (여담이지만 "이런 정보까지 저장이돼..?" 하는 정보들도 저장이 돼요 🫢). 따라서 회사에서 필요한 정보라 판단이 된다면, 그 정보를 로그에 기록하게 된다.

이 글에선 로그 데이터를 집계하는 방법을 다뤄야 하므로, 결제 시스템의 로그로 예시를 들 것이다. 물론 예시이기 때문에 모든 결제 시스템이 이러한 방식으로 로그를 저장하는 것은 아니며, 아래 정보보다 수많은 정보가 저장된다는 점은 참고 바란다.

예를 들어 다음과 같이 payment 테이블이 있다고 가정하자. 이 테이블에는 from_user가 to_user에게 amount 만큼의 돈을 보냈다는 로그 정보가 기록되어 있다.

transaction_id	from_user	to_user	amount	device	timestamp
100	datast	lala	3000	mobile	2023-01-01 16:00:00
101	garten	datast	10000	moblie	2023-01-01 16:05:00
102	lala	lala_bot	5000	mobile	2023-01-01 17:00:00
102	lala_bot	yei	5000	mobile	2023-01-01 17:00:01
103	garten	garten_bot1	1000	web	2023-01-01 19:10:00
103	garten_bot1	garten_bot2	1000	web	2023-01-01 19:10:01
103	garten_bot2	yei	1000	web	2023-01-01 19:10:02
104	yei	datast	7000	mobile	2023-01-01 19:30:00
105	yei	lala	6000	web	2023-01-01 20:00:00

집계 방법 1: 집계 단위를 설정하자

집계 단위 설정은 데이터를 전처리하기 전 매우 중요한 단계이다. 위 예시는 아주 단순하게 표현한 것이라 집계 단위의 설정이 무의미 할 수 있지만, 실무에선 방대한 양의 컬럼과 데이터가 존재한다. 이 데이터를 그냥 DB에 저장하다간 후에 데이터를 조회하거나 분석하는 단계에서 어려움이 생길 수 있다 (로그 정보를 그대로 저장하는건 Database 라기 보다는 Data Lake라 볼 수 있다).

사전에 우리의 서비스가 어떤 지표를 원하는지, 어떤 요구사항이 있는지 등을 면밀히 파악한 뒤, 로그 데이터를 어떻게 집계할 지 설정하는 것이 집계 단위 설정이다. 여기서는 하나의 transaction_id가 하나의 거래를 뜻한다고 보기 때문에, 집계 단위를 하나의 transaction_id로 설정할 것이다.

그럼 transaction_id가 같은 로그들을 살펴보자. 여기서는 102와 103 트랜잭션이 중복되어 나타난다. 해당 데이터를 보면 _bot을 통해 거래가 되고 있고, amount와 device는 같은 값으로 확인된다. 이를 통해 우리는 _bot을 이용한 결제는 같은 트랜잭션으로 묶인다는 것을 알 수 있으며, 한 트랜잭션 내에서 amount와 device는 변하지 않는 값이라는 걸 알 수 있다.

위 집계 단위에 맞춰 쿼리를 작성하면, 다음과 같이 우리가 원하는 결과를 얻을 수 있다.

transaction_id 로 group by 하여 집계

select
	transaction_id,
    max(case
    	when from_user like "%_bot%" then NULL
        else from_user
    end) as from_user,
    max(case
    	when to_user like "%_bot%" then NULL
        else to_user
    end) as to_user,
    max(amount) as amount,
    max(device) as device,
    min(timestamp) as timestamp
from payment
group by transaction_id;

결과

+--------------+---------+-------+------+------+-------------------+
|transaction_id|from_user|to_user|amount|device|          timestamp|
+--------------+---------+-------+------+------+-------------------+
|           100|   datast|   lala|  3000|mobile|2023-01-01 16:00:00|
|           101|   garten| datast| 10000|moblie|2023-01-01 16:05:00|
|           102|     lala|    yei|  5000|mobile|2023-01-01 17:00:00|
|           103|   garten|    yei|  1000|   web|2023-01-01 19:10:00|
|           104|      yei| datast|  7000|mobile|2023-01-01 19:30:00|
|           105|      yei|   lala|  6000|   web|2023-01-01 20:00:00|
+--------------+---------+-------+------+------+-------------------+

쿼리를 잠깐 살펴보면 다음과 같다.

• 집계 단위가 transaction_id 이기 때문에 transaction_id로 group by 진행
• _bot이 들어간 이름은 NULL 값으로 바꿔 그룹핑 후보에서 제외
• amount와 device는 하나의 트랜잭션 내에서 변하지 않는 값이므로 max 함수를 이용해 group by
• timestamp는 최초 발생한 거래 시간으로 기록

집계 방법 2: 필요한 컬럼을 추가하자

사실 집계 단위만 잘 설정되어도 이미 반 이상은 한 것이다. 하지만 위 처럼 집계를 하면 봇에 의해 발생된 거래 사실은 알 수 없어진다. 또한 timestamp의 경우도 최초에 발생한 거래 시간만 남게되어, 추후 데이터 분석 및 해석 단계에서 어려움을 줄 수 있다. 그래서 필요한 것이 필요한 컬럼을 추가하는 단계이다.

위 테이블에서 우리는 추가적으로 봇에 의한 거래인지 확인하는 is_bot 컬럼과, 거래 시작 시간과 종료 시간을 함께 기록하기 위해 start_timestamp, end_timestamp 컬럼을 추가할 것이다.

위에서 사용한 쿼리에 필요한 컬럼을 추가하면, 다음과 같이 우리가 원하는 결과를 얻을 수 있다.

is_bot, start/end_timestamp 컬럼 추가하여 집계

select
	transaction_id,
    max(case
    	when from_user like "%_bot%" then NULL
        else from_user
    end) as from_user,
    max(case
    	when to_user like "%_bot%" then NULL
        else to_user
    end) as to_user,
    max(case
    	when from_user like "%_bot%" or to_user like "%_bot%" then true
        else false
    end) as is_bot,
    max(amount) as amount,
    max(device) as device,
    min(timestamp) as start_timestamp,
    max(timestamp) as end_timestamp
from payment
group by transaction_id;

결과

+--------------+---------+-------+------+------+------+-------------------+-------------------+
|transaction_id|from_user|to_user|is_bot|amount|device|    start_timestamp|      end_timestamp|
+--------------+---------+-------+------+------+------+-------------------+-------------------+
|           100|   datast|   lala| false|  3000|mobile|2023-01-01 16:00:00|2023-01-01 16:00:00|
|           101|   garten| datast| false| 10000|moblie|2023-01-01 16:05:00|2023-01-01 16:05:00|
|           102|     lala|    yei|  true|  5000|mobile|2023-01-01 17:00:00|2023-01-01 17:00:01|
|           103|   garten|    yei|  true|  1000|   web|2023-01-01 19:10:00|2023-01-01 19:10:02|
|           104|      yei| datast| false|  7000|mobile|2023-01-01 19:30:00|2023-01-01 19:30:00|
|           105|      yei|   lala| false|  6000|   web|2023-01-01 20:00:00|2023-01-01 20:00:00|
+--------------+---------+-------+------+------+------+-------------------+-------------------+

집계 방법 3: 불필요한 컬럼은 삭제하자

이 파트는 원래 "집계 방법 2" 와 맥락도 비슷하고, 보통은 집계할 때 같이 진행하기 때문에 따로 분리해서 진행할 필요는 없다. 하지만 필자는 이 부분 또한 다른 파트와 비교했을 때 충분히 숙지가 필요하다고 여겨 설명을 추가했다.

불필요한 컬럼을 삭제하는 것은 언뜻 당연하다 생각이 들 수 있다. 하지만 어떤 데이터가 불필요한 컬럼인지는 고민해봐야 할 숙제이다. 지금은 필요없을거라 생각해 삭제한 컬럼이 후에는 필요해지는 경우가 생길 수 있고, 또 필요할 것이라 생각했던 컬럼이 계속적으로 쓰이지 않아 저장공간만 차지하는 쓰레기 데이터가 될 수도 있다 (또한 데이터가 많으면 집계하는데 드는 오버헤드도 커지고 시간도 많이 소요된다). 물론 이걸 처음부터 완벽히 판단하여 포함 여부를 결정하는 것은 당연 쉽지 않으므로, 팀 내의 요구사항에 맞게 결정해야 한다. 그리고 이 과정 속에서 해당 파트를 한번 더 숙지하여 고민하는 시간을 가져보는 것도 추천한다.

우리는 예시 데이터를 보고 있기 때문에, 여기선 device 정보가 불필요하다고 가정해보자. 혹은 device 중 mobile에서 발생한 거래 내역만 우리 팀이 다루고 분석한다고 가정하면, web에서 발생한 거래 내역은 불필요해진다.

이는 쿼리에서 조건을 주면 간단히 집계할 수 있고, 다음과 같이 우리가 원하는 결과를 얻을 수 있다. (계속적으로 언급하지만 우리가 보고 있는건 단순한 예시 데이터이기 때문에 쉽게 조건을 줄 수 있는 것이지, 실제 로그 데이터에서는 조건을 주는 것이 굉장히 까다로울 때가 많다.)

mobile device 만 집계

select
	transaction_id,
    max(case
    	when from_user like "%_bot%" then NULL
        else from_user
    end) as from_user,
    max(case
    	when to_user like "%_bot%" then NULL
        else to_user
    end) as to_user,
    max(case
    	when from_user like "%_bot%" or to_user like "%_bot%" then true
        else false
    end) as is_bot,
    max(amount) as amount,
    min(timestamp) as start_timestamp,
    max(timestamp) as end_timestamp
from payment
where device = 'mobile'
group by transaction_id;

결과

+--------------+---------+-------+------+------+-------------------+-------------------+
|transaction_id|from_user|to_user|is_bot|amount|    start_timestamp|      end_timestamp|
+--------------+---------+-------+------+------+-------------------+-------------------+
|           100|   datast|   lala| false|  3000|2023-01-01 16:00:00|2023-01-01 16:00:00|
|           102|     lala|    yei|  true|  5000|2023-01-01 17:00:00|2023-01-01 17:00:01|
|           104|      yei| datast| false|  7000|2023-01-01 19:30:00|2023-01-01 19:30:00|
+--------------+---------+-------+------+------+-------------------+-------------------+

정리

집계 단위 설정, 필요한 컬럼 추가, 불필요한 컬럼 삭제..
언뜻보면 너무나도 당연한 얘기라 "이걸 굳이 글로 쓴다고?" 할 수 도 있다 (시니어 입장에선 말문이 막혀 웃길수도 있을거에요 😅). 하지만 처음 로그 데이터를 접하는 학부생 입장에선 해당 데이터를 쉽게 다뤄보지 못했을 뿐더러, 방대한 양의 데이터에 어떻게 접근해야할지 감이 안잡히는 경우가 허다하다. 이때 나름의 규칙(?)을 설정하여 데이터를 바라본다면 생각보다 단순하게 집계할 수 있으므로, 이를 글로 풀어 설명해 보았다.

위에서 본 예시 데이터는 로그 데이터라 하기 민망할 정도로 엄청 단순한 데이터이다. 때문에 쿼리 조건도 작성에 큰 어려움이 없어 "이걸 단계별로 나눠 생각할 필요가 있을까" 의문이 들지만, 실제 현업에서는 집계 기준이 매우 중요한 부분이다. 해당 집계 기준으로 이후 모든 데이터 처리 및 분석 업무가 이루어지는데 앞단에서 이 기준이 흔들려버리면 서비스 전체에 영향을 줄 수 있다. 그러므로 위와 같은 집계 방법들이 데이터를 처음 집계하는데 있어서, 혹은 팀에 합류하여 데이터를 이해하는데 있어서, 가장 중요한 기준점이 되길 바라는 마음으로 이 글을 마친다.

결론

1. 로그 데이터를 집계하기 전, 먼저 집계 단위를 설정하자
2. 집계하며 누락된 정보 중 원하는 컬럼 혹은 필요한 정보를 추가하자
3. 불필요한 컬럼과 데이터는 삭제하여 전체 데이터 크기를 줄이자