다시 한 번 정리해보자면, 빅쿼리는 페타바이트급의 데이터도 빠르게 분석할 수있는 데이터 분석 플랫폼이며
수고수러운 인프라 운영에는 신경을 쓰지 않고 비용효율적으로 데이터 분석에만 집중할 수 있어 많은 기업에서 도입하고 있는 데이터웨어하우스라는 내용이었다.
이어서 이번에는 기본적인 빅쿼리 사용법을 정리해보려고 한다.
빅쿼리 콘솔을 이용해서 아주 기본적인 SQL 쿼리들로 빅쿼리에서 데이터를 추출하고 정제하는 방법을 알아보자.
목차
1. 빅쿼리 개요
1.1 빅쿼리 콘솔
우선 빅쿼리 콘솔에 접속해보자.
구글 클라우드에 접속해서 좌측 메뉴에서 BigQuery > SQL 작업공간을 선택한다.
GCP 화면
1.2 빅쿼리 데이터 구조
빅쿼리 콘솔에 접속하면, 빅쿼리의 데이터 구조를 확인할 수 있다.
빅쿼리 데이터는 프로젝트 하에 데이터셋이 있고, 데이터셋 안에 테이블이 들어있는 계층 구조로 되어 있다.
(탐색기 패널의 데이터 추가 버튼을 통해 공개 데이터셋인 bigquery-public-data 프로젝트를 활용할 수 있다.)
빅쿼리의 데이터 구조: project > dataset > table
•
프로젝트: GCP 자원을 조직하는 단위. 여러 개의 데이터셋을 가지고 있다.
Project is an organizing entity for your Google Cloud resources. It often contains resources and services; for example, it may hold a pool of virtual machines, a set of databases, and a network that connects them together. Projects also contain settings and permissions, which specify security rules and who has access to what resources.
•
데이터셋: 여러 개의 테이블을 가지고 있는 테이블의 집합
•
테이블: 데이터가 저장되는 테이블
2. 빅쿼리 SQL 작성하기
이제 빅쿼리 데이터의 구조를 이해했으니,
위 그림의 빅쿼리 콘솔 화면 상의 우측 편집기에서 쿼리 작업을 할 수 있다.
빅쿼리의 표준 SQL
•
데이터 분석가에게 익숙한 SQL을 사용하기에 빠르게 익힐 수 있다
•
빅쿼리는 ANSI 기준을 따르는 표준SQL(Standard SQL)을 사용함
◦
standard와 legacySQL은 세부적인 문법에서 약간의 차이가 있음
쿼리 기본 구조와 작성 팁
#standardSQL
SELECT col1, col2, col3 AS col3_name
FROM `project.dataset.table`
ORDER BY col3_name DESC
LIMIT 100
SQL
복사
•
#standardSQL
◦
빅쿼리의 기본 설정은 표준SQL. comment로 #standardSQL나 #legacySQL 를 표시해주어 SQL언어를 설정할 수 있음
•
SELECT + 조회를 원하는 컬럼명
◦
주의사항: * 을 쓰면 테이블의 모든 컬럼을 쿼리할 수 있지만, 필요한 컬럼만 골라서 조회하도록 하자.
•
FROM + 컬럼을 가져올 테이블명
◦
`<프로젝트>.<데이터셋>.<테이블명>` 형태로, 백틱(backtick: `)으로 묶어줌
•
AS(Aliases): 사용하기 편한 이름을 AS 뒤에 붙여줄 수 있음
◦
주의사항
▪
WHERE 절 filtering에는 SELECT에 쓴 AS 컬럼명을 사용할 수 없음
(doesn’t exist yet when filtering in WHERE)
▪
ORDER BY, GROUP BY, HAVING 에는 AS 컬럼명을 사용할 수 있음
•
ORDER BY: 원하는 컬럼을 기준으로 쿼리 결과를 정렬할 수 있음
◦
ASC / DESC 로 오름차순, 내림차순 설정
•
LIMIT 절을 사용해 결과를 제한하기
3. 기본적인 표준SQL 함수
기본적인 함수의 종류에 관해 알아보자. 여기서는 대략적인 내용만 파악해보자.
자세한 내용은 그 때 그 때 문서를 찾아보고 써보면서 익혀나가는 것이 좋다.
•
집계 함수: SUM(), AVG(), COUNT(), COUNT(DISTINCT ), MAX()
◦
집계 함수는 데이터 분석에 있어 가장 핵심적인 SQL 함수로,
GROUP BY 와 함께 사용해서 특정 기준으로 집계하고자 하는 측정값을 계산하는 데 쓰인다.
▪
예: 쇼핑몰 고객별(GROUP BY) 총구매액(SUM), 학생들의 학년별(GROUP BY) 평균 키(AVG) 등
◦
함수는 중첩하여 사용 가능: ROUND(AVG(), 2)
◦
중복 레코드를 확인하는 방법: COUNT + GROUP BY
▪
filter aggregations with HAVING clause (count columns alias > 1)
# 쿼리 예시: customer_id가 중복된 row 확인하기
SELECT customer_id, COUNT(customer_id) AS id_counts
FROM `project.dataset.table`
GROUP BY customer_id
HAVING id_counts > 1
SQL
복사
•
문자열 관련 함수: FORMAT(), ...
◦
문자열 함수로 문자열 처리하기
CONCAT(”AB”, “C”)
>>> “ABC”
END_WITH("Apple", "e")
>>> true
LOWER("Apple")
>>> "apple"
REGEXP_CONTAINS("Lunchbox", r"^*box$")
>>> true
SQL
복사
◦
Wildcard filters with LIKE
•
데이터 타입 전환 함수: CAST(), ...
◦
빅쿼리 데이터 타입
◦
CAST 함수로 데이터 타입 전환하기
SELECT CAST("12345" AS INT64)
>>> 12345
SELECT CAST("2017-08-01" AS DATE)
>>> 2017-08-01
SELECT CAST(1112223333 AS STRING)
>>> "1112223333"
SELECT SAFE_CAST("apple" AS INT64)
>>> NULL
SQL
복사
•
날짜 관련 함수: PARSE_DATETIME(), ...
◦
YYYY-MM-DD 가 기본적인 날짜 데이터의 포맷
◦
날짜 함수
4. 데이터 정제와 변환(cleaning & transforming)
4.1 다섯가지 데이터 무결성(integrity) 원칙
1) Validity 타당성
•
Meaning
◦
Valid data follows constraints on uniqueness (needs unique identifiers)
◦
Valid data corresponds to range constraints
•
Challenges: Out of range / Empty fields / Data mismatch
2) Accuracy 정확성
•
Meaning: Accurate data matches to a known source of truth
•
Challenges: Lookup datasets / Do not exist
3) Completeness 완전성
•
Meaning: Complete data, not subset of the data
•
Challenges: Missing data
4) Consistency 일관성
•
Meaning: Consistent data ensures harmony across systems
•
Challenges: Duplicate records / Concurrency issues
5) Uniformity 균일성
•
Meaning: Uniformity in data means measuring the same way
•
Challenges: Same units of measurement
4.2 SQL을 사용한 데이터 정제 및 변형
위에서 살펴본 다섯가지 데이터 무결성 원칙을 지키기 위해, 빅쿼리 SQL로 아래와 같은 작업들을 할 수 있다.
1) Validity, 타당성 준수를 위해
•
필드의 데이터타입과 제약조건 설정하기
•
필드의 NULLABLE or REQUIRED 여부를 지정하기
•
NULL 값을 체크하기
•
가능 데이터 범위를 체크하고 필터링하기 - 조건문: CASE WHEN, IF()
•
업스트림 데이터 소스 시스템에는 primary key와 relational constraints가 필요
(빅쿼리는 primary 운영DB가 아닌 분석용 데이터웨어하우스임을 명심하자)
2) Accuracy, 정확성 준수를 위해
•
테스트 케이스나 계산된 필드를 통해 값을 확인
◦
예: (quantity_ordered * item_price) AS sub_total
•
룩업 테이블, 참조 데이터셋을 통해 확인 (IN()이나 JOIN 활용)
3) Completeness, 완전성 준수를 위해
•
데이터셋을 면밀하게 살펴서 데이터셋의 shape과 skewness, missing value를 확인하기
◦
NULLIF(), IFNULL(), COALESCE() 등 활용
•
UNION과 JOIN을 이용해 데이터를 풍부하게 하기
4) Consistency, 일관성 준수를 위해
•
“one fact in one place”, ID를 사용해 lookup하기
•
문자열 함수로 데이터 정제: PARTSE_DATE(), SUBSTR(), REPLACE()
5) Uniformity, 균일성 준수를 위해
•
데이터에 대한 문서와 주석을 잘 남기기(units, etc.)
•
FORMAT() 을 사용해 단위를 분명하게 표시
•
CAST()로 데이터 타입과 자릿수를 통일
•
모든 시각화 요소에 적절한 라벨링하기
참고자료
•