BigQuery를 이용한 데이터 처리 프로세스–ETL
지난 포스팅에서는 Bizspring Growth Platform(이하 ‘그로쓰플랫폼’) 데이터 수집부터 정제 데이터 리포트 출력까지의 과정을 살펴보았는데요. 이번 포스팅에서는 해당 내용 중, BigQuery (이하 ‘빅쿼리’)를 이용한 데이터 처리 프로세스에 대해 살펴보겠습니다.
ETL과 ELT
빅쿼리 데이터 처리 프로세스를 설명하기전에, 데이터 엔지니어링 용어인 ‘ETL’과 ‘ELT’에 대해 설명 및 장•단점에 대해서 알아보겠습니다.
1.ETL (Extract, Transform, Load)
ETL은 조직이 다양한 소스에서 데이터를 추출하여 단일 데이터베이스로 가져오는 데 도움이 되는 데이터 통합 프로세스로 데이터를 추출(E), 변환(T), 적재(L) 순으로 데이터를 처리하는 프로세스이며, 관계형 SQL 기반 데이터 구조만 허용합니다.
각 단계별 개념은 아래 설명과 같습니다.
Extract(추출) : 원본 데이터베이스 또는 데이터 소스에서 데이터를 가져오는 것을 말한다.
Transform(변환): 데이터의 구조를 변경하는 프로세스를 의미한다. 용도에 맞는 필터링, Reshaping, 정제 등의 단계를 통해 필요한 형태로 변환한다.
Load (적재): 데이터를 스토리지에 저장하는 프로세스를 의미한다 .
▶︎ 장점
- 데이터를 구조화/변환한 후 ETL을 사용하면 보다 빠르고 효율적이며 안정적인 데이터 분석이 가능합니다.
- 관계형 SQL 기반 데이터로 변환하여 GDPR, HIPAA 및 CCPA와 같은 데이터 개인 정보 보호 및 보호 규정을 준수할 수 있습니다.
- 정교한 데이터 변환을 수행할 수 있습니다.
- ETL은 20년 이상 사용된 프로세스 입니다.
▶︎ 단점
- 데이터를 변환하여 과정의 시간이 다소 소요되어 데이터 수집 프로세스를 더 느리게 만듭니다.
- ETL 프로세스를 설정하는 초기 비용은 프로젝트에 필요한 프로세스와 변환을 정의해야 할 수 있으므로 높을 수 있습니다.
- ETL 프로세스를 지속적으로 유지 관리하고, 다른 데이터 유형을 요구할 경우 변경하는 입력 소스를 최신 상태로 유지해야하기 때문에 유지보수가 필요합니다.
2. ELT (Extract, Load, Transform)
ELT는 데이터를 추출(E), 로드(L) 및 변환(T)을 하는 프로세스를 의미하며, 위에 언급한 ETL의 T와 L의 위치가 바뀐 개념으로 생각하시면 됩니다. 데이터 로드 후 변환 하기 때문에 별도의 스테이징 서버가 필요하지 않습니다.
▶︎ 장점
- 클라우드 기반으로 스키마 변경과 같은 작업을 자동화하므로 유지 관리가 최소화됩니다.
- 정형, 비정형, 반정형(semi-structured) 데이터 유형의 모든 데이터 타입을 활용할 수 있습니다.
- 클라우드에서 스토리지를 빠르게 확장할 수 있어 대규모의 데이터를 수집 관리할 수 있습니다.
- 데이터를 적재 후 변환하기 때문에 데이터 로드 시간이 짧습니다.
▶︎ 단점
- 데이터 보안은 스토리지에 대량의 원시 데이터를 로드하기 때문에 개인정보보호규정 및 규정준수 규칙에 문제가 될 수 있습니다.
- 모든 데이터를 저장하고 다양한 사용자와 애플리케이션이 액세스할 수 있도록 하면 보안 위험이 따릅니다.
- 기업은 데이터를 마스킹하고, 암호화 하여 대상 시스템의 보안 조치를 취해야 합니다.
ETL과 ELT의 프로세스 요약 (출처: striim.com 블로그 - https://www.striim.com/blog/etl-vs-elt-differences/)현재 그로쓰플랫폼 내에 있는 ‘AIR (통합매체리포트)’에 사용되는 데이터는 안정적인 데이터 분석을 위해 ‘ETL’ 과정으로 데이터를 만들어 사용하고 있습니다.
ETL 방식의 단점인 데이터 로드시간을 단축하기 위해 Elasticsearch(이하 엘라스틱서치)를 함께 활용하여 리포트를 제공하고 있습니다. 이에 대한 내용은 지난 포스팅에서도 확인하실 수 있습니다.
ETL의 가장 큰 특징은 Transform, 즉 변환 단계에 있습니다. 특정 쿼리를 이용하여 Raw Data(이하 로우 데이터, 가공되지 않은 원본 데이터)를 용도에 맞게 변환하여 적재하는 단계라고 볼 수 있습니다. 이를 빠르고 간편하게 하기 위해 빅쿼리에서는 ‘쿼리 예약’ 이라는 시스템을 지원하고 있습니다.
쿼리 예약
빅쿼리에는 이런 ETL 프로세스를 효율적으로 사용하기 위해 ‘쿼리 예약’ 시스템을 제공하고 있습니다. 해당 시스템을 사용하면 쿼리가 반복적으로 실행되도록 일정을 예약할 수 있습니다.
SQL 편집기에서 반복적으로 실행할 쿼리를 작성하고, 상위 메뉴 중 ‘일정’ > ‘새로 예약된 쿼리 만들기’ 버튼을 선택 시 설정을 할 수 있습니다.
예약 쿼리 메뉴 (출처: BizSpring)예약 쿼리에는 다음과 같은 특징을 가지고 있습니다.
Google 표준 SQL로 작성되어야 하며 데이터 정의 언어(DDL) 및 데이터 조작 언어(DML) 문을 포함할 수 있습니다.
쿼리 문자열과 대상 테이블을 매개변수화하여 사용할 수 있습니다.
사용 가능한 매개변수는 다음과 같습니다.
다음은 ‘@run_time’ 매개변수를 이용하여 처리 시간을 취득하는 예시입니다.
SELECT @run_time AS time,
title,
author,
text
FROM `bigquery-public-data.hacker_news.stories`
LIMIT
1000
템플릿 구문을 사용하여 대상 테이블 이름에 런타임 매개변수인 ‘매개변수 템플릿’을 지원합니다.
다음은 매개변수 템플릿의 예시입니다.
쿼리 예약 설정으로 진입하게 된다면 반복 빈도, 시간대, 종료 시간, 해당 쿼리 실행 결과 테이블 생성 혹은 덮어쓰기 관련 내용 등의 항목들을 설정할 수 있습니다.
모든 항목을 구성한 후 저장 시, ‘예약된 쿼리’ 탭에서 생성한 예약 리스트의 조회 및 작업 내역 조회, 수정, 삭제 등이 가능합니다.
예약 쿼리 설정 (출처: BizSpring)이외에 쿼리 예약 시스템에 대한 자세한 내용은 해당 도움말 페이지에서 확인 가능합니다.
그로쓰플랫폼 콘솔
위와 같이 빅쿼리 내에 ‘쿼리 예약’ 시스템을 제공하고 있지만 사용가능한 쿼리 문자열의 제한이 있는 단점이 있습니다. 또, 해당 쿼리를 실행하기 위한 조건 설정을 상세하게 설정할 수 없는 점도 있습니다.
이를 보완하기 위해 저희 그로쓰플랫폼에서는 해당 기능에 여러 부가기능을 추가한(특정한 상황에서만 해당 쿼리가 실행, 커스텀 쿼리 문자열 사용 등) ‘그로쓰플랫폼 콘솔’을 사용하여 데이터 적재, 집계 프로세스 등을 처리하고 있습니다.
그로쓰플랫폼 콘솔 (출처: BizSpring)이상으로, BigQuery를 이용한 데이터 처리 프로세스를 정리해보았습니다.
빅쿼리 예약 시스템이나 그로쓰플랫폼 콘솔에 대해 궁금한 점이 있다면 언제든지 문의 해주시길 바랍니다.
ad@bizspring.co.kr / 02-6919-5516
마케팅에서의 데이터 활용 기술과 인사이트
No.1 Data Partner for Data-Driven Growth
비즈스프링
공식 블로그 | 페이스북 | 네이버 블로그 | 유튜브 | 트위터 | 슬라이드쉐어
