[BigQuery] 빅쿼리 데이터 구조 및 기본적인 사용방법과 문법

 

스스로 빅쿼리에 대해서 이해를 하는 것의 필요성을 느끼고, 데이터 구조와 기본적인 사용방법 및 문법에 대해서 간단하게 작성해보고자 한다.

 

빅쿼리(BigQuery)

: 페타바이트급의 데이터도 빠르게 분석할 수있는 데이터 분석 플랫폼이며 인프라 운영에는 신경을 쓰지 않고 비용효율적으로 데이터 분석에만 집중할 수 있어 많은 기업에서 도입하고 있는 데이터웨어하우스이다.

 

기본적인 빅쿼리 사용법을 정리겸 작성해보려고 한다.

 

빅쿼리 데이터 구조

빅쿼리 데이터는 프로젝트 하에 데이터셋이 있고, 데이터셋 안에 테이블이 들어있는 계층 구조로 되어 있다.

(탐색기 패널의 데이터 추가 버튼을 통해 공개 데이터셋인 bigquery-public-data 프로젝트를 활용할 수 있다.)

 

빅쿼리의 데이터 구조: project > dataset > table

- 프로젝트: GCP 자원을 조직하는 단위. 여러 개의 데이터셋을 가지고 있다.

- 데이터셋: 여러 개의 테이블을 가지고 있는 테이블의 집합

- 테이블: 데이터가 저장되는 테이블

 

 

빅쿼리 SQL 작성

빅쿼리의 표준 SQL

- 빅쿼리는 ANSI 기준을 따르는 표준SQL(Standard SQL)을 사용

 

쿼리 기본 구조와 작성 팁

#standardSQL
SELECT col1, col2, col3 AS col3_name
FROM `project.dataset.table`   
ORDER BY col3_name DESC
LIMIT 100

 

SELECT + 조회를 원하는 컬럼명

- 주의사항: * 을 쓰면 테이블의 모든 컬럼을 쿼리할 수 있지만, 필요한 컬럼만 골라서 조회하도록 하자.

 

FROM + 컬럼을 가져올 테이블명

- `<프로젝트>.<데이터셋>.<테이블명>` 형태로, 백틱(backtick: `)으로 묶어줌

 

AS(Aliases): 사용하기 편한 이름을 AS 뒤에 붙여줄 수 있음

주의사항

    - WHERE 절 filtering에는 SELECT에 쓴 AS 컬럼명을 사용할 수 없음

    - ORDER BY, GROUP BY, HAVING 에는 AS 컬럼명을 사용할 수 있음

 

ORDER BY: 원하는 컬럼을 기준으로 쿼리 결과를 정렬할 수 있음

- ASC / DESC 로 오름차순, 내림차순 설정

 

LIMIT 절을 사용해 결과를 제한하기

- 쓴 만큼 요금이 부과되기 때문에 단순 확인 용도라면 LIMIT 꼭 붙여주기

 

 

기본적인 표준SQL 함수

 

기본적인 함수의 종류에 관해 알아보자. 여기서는 대략적인 내용만 파악해보자. 자세한 내용은 그 때 그 때 문서를 찾아보고 써보면서 익혀나가는 것이 좋다.

 

집계 함수: SUM(), AVG(), COUNT(), COUNT(DISTINCT ), MAX()

- 집계 함수는 데이터 분석에 있어 가장 핵심적인 SQL 함수로, GROUP BY 와 함께 사용해서 특정 기준으로 집계하고자 하는 측정값을 계산하는 데 쓰인다.

- 예) 쇼핑몰 고객별(GROUP BY) 총구매액(SUM), 학생들의 학년별(GROUP BY) 평균 키(AVG) 등

 

함수는 중첩하여 사용 가능: ROUND(AVG(), 2)  : 평균의 값을 소수점 2째자리에서 반올림한다는 의미

 

중복 레코드를 확인하는 방법: COUNT + GROUP BY

# 쿼리 예시: customer_id가 중복된 row 확인하기
SELECT customer_id, COUNT(customer_id) AS id_counts
FROM `project.dataset.table`
GROUP BY customer_id
HAVING id_counts > 1

 

문자열 관련 함수: FORMAT(), ...

- 문자열 함수로 문자열 처리하기

CONCAT(”AB”, “C”)
>>> “ABC”

END_WITH("Apple", "e")
>>> true

LOWER("Apple")
>>> "apple"

REGEXP_CONTAINS("Lunchbox", r"^*box$")
>>> true

 

- 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

 

날짜 관련 함수: PARSE_DATETIME(), ...

- YYYY-MM-DD 가 기본적인 날짜 데이터의 포맷

- 날짜 함수

 

 

데이터 정제와 변환

 

다섯가지 데이터 무결성(integrity) 원칙

 

1. 타당성( Validity )

- 필드의 데이터타입과 제약조건 설정

- 필드의 NULLABLE/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()로 데이터 타입 및 자릿수 통일

- 모든 시각화 요소에 적절한 라벨링

 

 

 

출처 

- https://hyeshin.oopy.io/ds/gcp/20220505_bigquery_basic

- https://www.cloudskillsboost.google/course_templates/14