BigQuery A-Z: 1. 대시보드에서 사용하기.

18 min readMay 19, 2023

BigQuery는 Google Cloud의 완전 관리형 데이터 웨어하우스 서비스며, 각종 기업들의 데이터 파이프라인 스택에 사용되고 있습니다. BigQuery를 처음 접하시는 분들을 위해 BigQuery가 다른 DBMS와 비교되는 특징에 대해 알아보고, Google Cloud 콘솔 대시보드에서 데이터셋과 테이블을 생성하고 쿼리를 작성하는 방법에 대해 포스트해보았습니다.

BigQuery 특징

빠른 SELECT

MySQL, PostgreSQL과 같은 기존의 RDBMS는 데이터를 행 기반 형식으로 저장합니다. 레코드와 함께 행이 저장되며, 각 행의 필드가 순차적으로 저장됩니다. 이 경우 auto increment된 primary key와 함께 개별 행의 레코드를 효율적으로 조회할 수 있습니다. 하지만 조회시 SELECT문에서 어떤 필드를 지정하더라도 상관없이 행의 모든 필드를 읽어야 하므로, 필드가 많은 (열 개수가 많은) 테이블에는 비효율적일 수 있습니다.

반면에 BigQuery는 각 열을 개별 스토리지에 저장하는 열 기반 형식을 사용합니다. 필드 수가 많은 빅데이터를 다룰 때, 우리는 전체 테이블에서 일부 필드만을 골라 조회하곤 합니다. 이때 각 행의 모든 필드를 전부 읽어내지 않고 쿼리문에서 지정한 필드만을 읽어내기 때문에 더 적은 리소스를 사용합니다.

OLAP

기존 RDBMS의 방식인 OLTP(Online Transaction Processing)는 INSERT, UPDATE, DELETE시 데이터의 무결성을 보장하고 처리하는데에 중점을 두고 있습니다.

반면 BigQuery는 OLAP(Online Analytical Processing) 방식으로 이미 저장된, 무결성이 보장된 데이터를 이용해 빠르고 효율적인 조회와 분석(주로 SELECT문)을 수행하는데 중점을 두고 있습니다. 이는 오랜 기간동안 적재된 데이터를 한 데 모아놓은 통합 데이터베이스인 데이터 웨어하우스에 적합합니다.

따라서 데이터 파이프라인 구성시 BigQuery(OLAP) 활용 이전에 OLTP가 선행되어야 함에 주의해야 합니다. BigQuery는 auto increment를 지원하지 않고, primary key와 foreign key는 TABLE_CONSTRAINTS view를 통해 제한적으로 지원하므로 데이터 처리(INSERT, UPDATE, DELETE)시 더욱 철저한 검증이 필요합니다.

OLTP와 OLAP에 대한 비교는 다음 링크에서 더 자세히 알 수 있습니다.

OLAP vs. OLTP - 데이터 처리 시스템 비교 - AWS

온라인 분석 처리(OLAP) 및 온라인 거래 처리(OLTP)는 비즈니스 데이터를 저장하고 분석하는 데 도움이 되는 데이터 처리 시스템입니다. 웹 사이트, 애플리케이션, 스마트 미터 및 내부 시스템과 같은 여러…

aws.amazon.com

표준 SQL(ANSI SQL) 문법 지원

BigQuery는 DBMS의 종류에 제약을 받지 않는 ANSI(American National Standard Institute) SQL 문법을 채택하고 있습니다. 따라서 MySQL에서는 지원하지 않는 FULL OUTER JOIN이나, MySQL 8.0 이상 버전에서만 사용 가능한 CTE(Common Table Expression) 같은 문법을 모두 지원합니다.

Array, JSON 형식 지원

다른 최신 DBMS와 같이 Array 자료형과 JSON 자료형을 지원합니다.

-- ARRAY 자료형 예제
WITH cruise AS (
  SELECT
    ARRAY<STRING>['wy', 'bh', 'hj'] AS crews,
    'maeji' AS ship_name,
    50 AS length
)

SELECT *
FROM cruise
CROSS JOIN UNNEST(crews) AS crew_member
WHERE crew_member = 'bh';

강력한 연동 기능

BigQuery 스토리지에 저장된 데이터를 Google spreadsheet로 내보내거나, XLSX 파일로 저장할 수 있습니다. 레코드가 5000개가 넘어가는 경우 상위 5000개 레코드에서 데이터가 절삭되므로 Cloud Storage에 저장 후 내보내는 방식으로 우회할 수 있습니다. 다만, 이런 대용량 데이터의 경우 스프레드시트로 조회시 성능 저하를 유발할 수 있으므로 권장하지 않습니다.

Tableau나 Google Looker Studio와 같은 BI(Business Intelligence) 도구에 Google 계정 또는 서비스 계정 인증을 통해 자동으로 데이터베이스를 연동할 수 있습니다. BI 툴을 이용하는 경우 Google Cloud의 BI Engine을 이용해 BI 툴 내부에서의 쿼리 속도를 가속할 수 있습니다.

BigQuery 시작하기

Google Cloud Console 에 접속 후 오른쪽 상단 메뉴를 통해 BigQuery에 접속합니다.

제품 세부정보 페이지에서 BigQuery API에 대해 사용을 클릭합니다.

BigQuery 초기 화면에서 보이는 ‘Google 트렌드 데모 쿼리 사용해 보기’ 항목의 이 쿼리 열기를 클릭합니다.

Google Cloud에서 기본으로 제공하는 bigquery-public-data프로젝트의 데이터셋들을 모두 불러오고, 그 중 google_trends 데이터셋의 top_terms 테이블을 다루는 쿼리문 데모를 보여줍니다.

bigquery-public-data.google_trends.top_terms 테이블을 다루는 쿼리문 데모.

상단의 탭에서 + 버튼을 클릭해 새 퀴리 작성 페이지를 추가할 수 있습니다. 쿼리문이 작성되었다면 오른쪽 상단에 BigQuery가 쿼리 실행 시 처리량을 예측하여 보여줍니다. BigQuery의 요금은 이 쿼리 처리량의 한 달 누적량으로 책정됩니다. SELECT문의 경우 처리량은 열 개수, 연산량에 영향을 받기 때문에, 열 개수가 많은 테이블의 경우 SELECT *로 열 전체를 조회하기 보다 조회할 열을 지정하는 등의 방법으로 처리량을 절약할 수 있습니다.

LIMIT 100 과 같이 조회하는 행 개수를 제한하는 방법의 경우, 쿼리문이 LIMIT문 이전까지 모두 실행되고 LIMIT문으로 출력하는 행 개수를 절삭하는 로직으로 진행되기 때문에 처리량에 영향을 주지 않습니다. 이는 앞서 언급했듯이 BigQuery가 열 기반 형식으로 데이터를 저장하고 조회하기 때문입니다.

쿼리문의 문법이 잘못되었을 경우, 오른쪽 상단 예상 처리량이 출력되는 자리에 예상 처리량 대신 오류 메세지를 출력합니다.

-- 데모 쿼리문이 보이지 않은 독자를 위한 쿼리문
-- 2주 전~현재 동안 일별 미국 구글 검색어 순위 1위 검색어를 조회하는 쿼리문
SELECT
   refresh_date AS Day,
   term AS Top_Term,
   rank
FROM `bigquery-public-data.google_trends.top_terms`
WHERE
   rank = 1
       -- 각 날짜의 1위 검색어
   AND refresh_date >= DATE_SUB(CURRENT_DATE(), INTERVAL 2 WEEK)
       -- 현재로 부터 2주 전까지 데이터를 필터링
GROUP BY Day, Top_Term, rank
ORDER BY Day DESC
   -- 날짜별 내림차순

탐색기에서 테이블을 클릭하면 원본 테이블의 정보를 확인할 수 있습니다. 스키마 항목에서 각 필드의 자료형을 확인할 수 있고, 미리보기 항목에서 원본 테이블을 볼 수 있습니다.

데이터셋 생성

BigQuery에 기본 제공 데이터셋이 아닌 나만의 데이터셋과 테이블을 업로드 하려고 합니다. BigQuery 초기 페이지에서 탐색기의 내 프로젝트 오른쪽 점 세 개를 클릭하고 데이터 세트 만들기를 클릭합니다.

데이터세트 만들기 페이지에서 데이터셋의 ID를 지정하고 데이터세트 만들기 버튼을 클릭합니다.

내 프로젝트 아래에 데이터셋이 생성된 모습을 확인할 수 있습니다.

테이블 생성하기

위에서 생성된 test_dataset 데이터셋 아래에 테이블을 생성하려고 합니다. 테이블을 생성하는 여러가지 방법 중에 로컬 파일을 직접 업로드하는 방식으로 테이블을 생성할 수 있습니다. 업로드할 수 있는 파일 형식은 CSV, JSONL, Avro, Parquet, ORC 이 있으며, 이 중 가장 친숙한 CSV 파일을 업로드 해보려고 합니다.

실습에서 사용할 데이터는 Kaggle open dataset 출처의 ‘World Population Insights: 1970–2022’ 데이터입니다. 전 세계 국가들의 연도별 인구, 인구 밀도, 인구 비율 등을 담고 있습니다. 아래의 링크에서 CSV파일을 다운로드 받을 수 있습니다.

World Population Insights: 1970-2022

Global Population Trends: Exploring the Changing Dynamics of People Worldwide

www.kaggle.com

BigQuery 탐색기에서 내 프로젝트 아래의 test_dataset 오른쪽의 점 세 개를 클릭하고 테이블 만들기를 클릭합니다.

테이블 만들기 페이지에서 테이블을 만들 소스 항목을 업로드로 변경하고, 파일 선택 항목에 다운로드 받은 CSV파일을 지정합니다. 파일 형식 항목이 CSV로 되어있는지 확인합니다. 아래의 테이블 항목에서 테이블의 이름을 지정합니다.

계속해서 테이블 만들기 페이지의 스키마 항목에서 자동 감지를 체크합니다. 테이블 만들기 버튼을 클릭해 테이블 생성과정을 마무리합니다.

내 프로젝트-test_dataset 데이터셋 아래에 population 테이블이 성공적으로 생성되었습니다. 기존 CSV 파일에서 열 이름이 지정되어있지 않았던 인덱스 열은 int64_field_0 라는 필드 이름이 붙여졌고 기존 파일에서 열 이름의 공백은 모두 밑줄(_)로 메워진 것을 확인할 수 있습니다. 이 외에도 열 이름에 등장하는 km²와 같은 위첨자 특수기호도 밑줄(_)로 대체되어 km_로 필드 이름이 변경된 것을 볼 수 있습니다.

열 이름이 한글로 되어있는 CSV 파일의 경우 업로드 시 오류를 출력하므로 업로드 이전에 열 이름을 영어로 번역하는 작업이 필요합니다. 데이터는 한글로 작성되어있어도 무방합니다.

열 이름 규칙 문서에 따르면 공백이나 위첨자 기호가 포함된 열 이름엔 모두 오류를 출력해야 하지만 오류 없이 테이블 생성이 되었습니다(이게 왜 되지…?). 유연한 열 이름 확장을 제한적으로 지원하지만, 스키마 자동 감지를 사용한 CSV 데이터 로드시, JSON으로 데이터 로드시 유연한 열 이름을 지원하지 않는다고 공식 문서에 언급되어 있습니다. 다음 문단에서 JSONL 파일을 이용해 테이블을 생성하기 위해서 CSV 데이터를 JSONL로 변환하고 업로드 과정을 거쳤으나, 열 이름 규칙을 엄격하게 따르며 오류를 출력했습니다.

테이블 생성 과정에서 스키마 자동 감지를 설정한 덕분에 각 필드의 자료형이 자동으로 지정되었습니다. 이번 실습에서는 각 필드의 스키마가 제대로 지정되었지만 BigQuery의 스키마 자동 감지의 성능이 그리 좋은 편은 아니라 프로덕션 레벨에서는 별도의 문법으로 필드 별로 스키마를 수동으로 지정해 주는 것을 권장합니다. 스키마를 수동으로 지정하는 방법은 추후 포스트를 통해 공유할 예정입니다.

생성한 population 테이블을 이용한 쿼리문을 작성하려고 합니다. 상단 탭에서 + 버튼을 눌러 새 쿼리 작성 페이지를 열고 다음 쿼리문을 작성합니다. 국가별로 구성되어있는 기존 통계를 대륙별로 합쳐서 볼 수 있도록 구성했습니다.

-- 국가별 통계를 대륙별 통계로 합쳐주는 쿼리문
SELECT 
  Continent,
  SUM(_2022_Population) AS ppl,
    -- 인구
  SUM(Area__km___) AS area_km_square,
    -- 면적(km²)
  ROUND(
    SUM(_2022_Population) / SUM(Area__km___), 2
  ) AS density_per_km_square,
    -- km²당 인구 밀도, 소수점 둘째 자리부터 반올림
  ROUND(
    SUM(_2022_Population) * 100.0 / SUM(SUM(_2022_Population)) OVER(), 2
  ) AS ppl_percentage
    -- 인구 비율, 소수점 둘째 자리부터 반올림
FROM `test_dataset.population`
GROUP BY Continent
ORDER BY ppl DESC;
  -- 인구수로 내림차순

JSONL 파일 업로드하기

앞서 언급했듯이 테이블 생성을 위해 업로드할 수 있는 파일 형식은 CSV, JSONL, Avro, Parquet, ORC 이 있습니다. 이번에는 JSONL 형식 데이터를 업로드해 테이블을 생성하려고 합니다.

JSONL(JSON Lines) 파일은 줄 바꿈(\n)으로 구분된 JSON 파일 형식으로 JSON 형식과 달리 .jsonl확장자로 저장됩니다. 실습을 진행하기 위해 기존 CSV 파일 데이터를 JSONL 파일로 변환합니다.

포스트 작성 이전에 미리 실습을 진행해보면서, 열 이름을 그대로 둔 채 업로드 과정을 거치면 CSV 데이터 업로드 과정과 다르게 열 이름 규칙에 맞지 않는다며 오류를 출력하고 테이블이 생성되지 않는 점을 알 수 있었습니다. 원활한 실습 진행을 위해 데이터 변환 과정에서 열 이름 규칙에 맞게 열 이름도 변환하는 과정을 추가하였습니다.

## CSV 파일을 JSONL 파일로 변환하기 ##
import csv
import json

# 열 이름 변환 함수
def replace_field(f: str) -> str:
    return f.lower()\
            .replace(' ', '_')\
            .replace('²', '_square')\
            .replace('(', '')\
            .replace(')', '')

# CSV 파일 불러오기
data = list()
with open('population.csv', 'r', encoding='utf-8') as csv_file:
    reader = csv.DictReader(csv_file)
    for line in reader:
        line_fix: dict = {replace_field(k): v for k, v in line.items()}
        del line_fix[""]   # 열 이름 없는 첫번째 열 제거
        data.append(line_fix)

# JSONL 파일 저장하기
with open('population.jsonl', 'w', encoding='utf-8') as jsonl_file:
    for i in data:
        jsonl_file.write(json.dumps(i) + '\n')

열 이름 변환 함수에서 볼 수 있듯이, 열 이름을 모두 소문자로 변환 후에 공백으로 밑줄(_)로 변환하고, km²에 사용되었던 위첨자 ²를 _square로 변환했습니다. 열 이름의 괄호 또한 삭제했습니다. 마지막으로 기존 CSV 데이터에서 인덱스를 나타내던 이름 없는 열은 열 전체를 삭제했습니다.

변환된 JSONL 데이터를 이용해 새 테이블을 생성하기 위해 BigQuery 탐색기에서 내 프로젝트 아래의 test_dataset 오른쪽의 점 세 개를 클릭하고 테이블 만들기를 클릭합니다.

테이블 만들기 페이지에서 테이블을 만들 소스 항목을 업로드로 변경하고, 파일 선택 항목에 다운로드 받은 JSONL파일을 지정합니다. 파일 형식 항목이 JSONL(줄바꿈으로 구분된 JSON)으로 되어있는지 확인합니다. 아래의 테이블 항목에서 테이블의 이름을 지정합니다. 마지막으로 스키마 항목에서 자동 감지를 체크합니다. 테이블 만들기 버튼을 클릭해 테이블 생성과정을 마무리합니다.

test_dataset 데이터셋 아래에 성공적으로 population2 테이블이 생성된 것을 확인할 수 있습니다. 가장 먼저 눈에 띄는 점은 필드 순서가 업로드 전 원본 데이터와 다르게 뒤죽박죽 되었다는 것입니다. 저는 처음 이 화면을 보고 “BigQuery는 열 기반 형식으로 데이터를 저장한다고 했으니, 필드 별로 스토리지에 저장되는 과정이 비동기로 진행되는구나” 하고 추측했었습니다.

BigQuery를 업무에 사용해보면서 느낀점은 BigQuery는 비동기를 적극적으로 활용한다는 점이였습니다. 대표적으로 SELECT문 사용시 ORDER BY문을 사용하지 않으면 결과창의 레코드의 순서가 뒤죽박죽되어 보입니다. 레코드의 순서와 상관없이 동시에 연산을 시작해 먼저 연산이 끝난 레코드부터 바로바로 출력에 올려 빠른 연산 속도를 보여줍니다. Auto increment된 primary key를 지원하지 않는 이유도 이와 연관된다고 생각합니다. 테이블 자체를 조회하기 보다 대용량 데이터의 요약, 분석 결과를 빠르게 얻어내기 위해 최적화 되어있다는 느낌이 듭니다.

이외에도 BigQuery의 영 좋지 않은 스키마 자동 감지도 (의외로) 잘 작동한 점을 확인할 수 있습니다.

마무리

지난 인턴 온보딩 과정에서 제가 BigQuery와 친해지던 과정을 되돌아보며 포스트를 작성해 보았습니다. 이 글을 읽는 BigQuery를 처음 접하시는 분들이 BigQuery와 한 뼘 더 가까워지는 시간이 되었으면 좋겠습니다.

물론 프로덕션 과정에서 Google Cloud 콘솔에 직접 들어가 대시보드에서 로컬 파일을 업로드하는 일은 없을 것입니다. 보통은 CLI 환경에서 BigQuery API를 이용해 각종 프로그래밍 언어로 데이터를 다루는데, 이번 포스트에서 GUI로 환경으로 실습한 부분을 주로 코드화 한다고 보면 되겠습니다. 다른 부분은 스키마를 수동으로 지정하고, (100만 × 1만의 대용량이 될지도 모르는)데이터를 로컬로 저장하는 과정이 생략된다는 점일 것 같습니다.

다음 포스트에서는 Python에서 BigQuery API를 이용해 데이터를 다루는 방법에 대해 알아보려고 합니다.

참고자료

“BigQuery란 무엇인가요?”. Google Cloud Docs. https://cloud.google.com/bigquery/docs/introduction?hl=ko.
“BigQuery 스토리지 개요”. Google Cloud Docs. https://cloud.google.com/bigquery/docs/storage_overview?hl=ko.
“OLAP란?”. IBM. https://www.ibm.com/kr-ko/topics/olap.
꿍스뿡이. “[DATABASE] OLTP와 OLAP의 차이점 & 예시”. 꿍스뿡이의 드림빌더. 2020년 7월 23일. https://too612.tistory.com/511.
“OLAP과 OLTP의 차이점은 무엇인가요?”. AWS Docs. https://aws.amazon.com/ko/compare/the-difference-between-olap-and-oltp/.
변성윤. “ARRAY”. BigQuery Guide Book. https://zzsza.github.io/bigquery/advanced/array.html.
“csv — CSV 파일 읽기와 쓰기”. Python 3.11.3 Documentation. https://docs.python.org/ko/3/library/csv.html.
“Documentation for the JSON Lines text file format”. JSON Lines. https://jsonlines.org/.
“스키마 지정”. Google Cloud Docs. https://cloud.google.com/bigquery/docs/schemas?hl=ko.