exp_blog

BigQuery는 구글 내부에서 방대한 양의 데이터를 SQL문을 통해서 분석하기 위해 개발한

Dremel이라는 프로젝트의 public implementation입니다.

Dremel 이전에는 하둡 기반의 클러스트를 구축하고, Map Reduce 방식을 통해 데이터 웨어하우스를 구축했다고 합니다

Dremel은 structured 데이터를 분산 저장하고 SQL문을 통해 빠르게 데이터를 분석하는데 특화되어 있습니다.

이를 통해, 데이터 분석가들이 다양한 쿼리문을 통해서 데이터에서 인사이트를 뽑아낼 수 있도록 도와줍니다.

한마디로 말하자면 빅쿼리란, 

Dremel 프로젝트를 기반으로 구글 외부의 사용자들도 쓸 수 있도록 서버리스 클라우드 서비스로 만들어 공개한 것

입니다.

빅쿼리가 빠른 성능을 보여주는 것은 2가지의 특징 때문입니다. 

1. Columnar Storage

RDBMS는 레코드 단위로 데이터를 저장.

빅쿼리는 컬럼 단위로 데이터를 저장

즉, 같은 데이터 타입의 데이터들이 몰려서 저장되는 것 의미합니니다.

컬럼 단위로 데이터를 저장하는 장점은

• 트래픽 최소화 : 쿼리 사용 시 해당하는 컬럼만 조회하면 되기 때문에, 데이터 양이 방대해질 수록 트래픽을 최소화 할 수 있다.
• Higher Compression Ratio :  같은 데이터가 몰려 저장되어있기 때문에, 그 결과 압축하기에 더 용이.

2.Tree Architecture Distribution

루트 서버 : 클라이언트 SQL 쿼리문 분석 -> 분산 머신에서 동작하는 수많은 작은 단위의 쿼리문 생성 -> Intermediate servers 전송

Intermediate server :  전달받은 쿼리를 더 작게 쪼개 -> leaf server로 전송

leaf servers :   실제 파일 시스템(빅 쿼리의 경우 Colossus)에 저장된 데이터를 읽어와서 쿼리 연산을 수행하고 결과를 부모 노드에게 전송

회사에서 데이터 파이프라인 고도화 작업에 사용한다고 하여 공부하면서 정리해 볼 예정입니다..

빅쿼리란?(BigQuery)

• 대용량 Dataset(최대 몇 십억 개의 행)를 대화식으로 분석할 수 있는 웹 서비스
• 대규모 데이터 저장 및 분석 플랫폼으로, 일종의 데이터 웨어하우스
• 데이터 웨어하우스 : 축적된 데이터를 모아 관리하는 곳
• 확장 가능하고 사용이 간편한 BigQuery를 통해 개발자와 기업은 필요할 때 강력한 데이터 분석을 수행 할 수 있음

특징

1. 클라우드 서비스로 설치/운영이 필요 없다.(NoOps)
   간단하게 클릭 몇 번으로 서비스 사용이 가능하고, 별도의 설정이나 운영이 필요 없다(PaaS 겸 IaaS)
2. SQL 언어 사용
   기존 RDBMS에서 사용되는 SQL언어를 그대로 사용한다. 로그인 후 SQL만 수행하면 되므로 상대적으로 쉬움
3. 클라우드 스케일의 인프라를 활용한 대용량 지원과 빠른 성능
4. 데이터 복제를 통한 안정성
   3개의 복제본이 서로 다른 데이터 센터에 분산되어 저장되기 때문에 데이터에 대한 유실 위험이 적다.
5. 배치와 스트리밍 모두 지원
   한꺼번에 데이터를 로딩하는 배치 외에, 실시간으로 데이터를 입력할 수 있는 스트리밍 기능을 제공
6. 비용이 저렴하다.

구성

• Project : 데이터를 담는 최상위 개념, 하나의 프로젝트에 여러개의 데이터셋이 들어갈 수 있음
  • 사용자와 데이터셋에 대한 권한 제어를 포함한 전반적인 리소스 관리 
• Dataset : RDB에서 Database의 개념이며, 하나의 Dataset에 여러개의 테이블이 들어갈 수 있음
  • 빅쿼리에서 권한 제어를 할 수 있는 최소 단위
  • 데이터셋에 대한 권한부여를 통해 다른 사용자와 데이터 공유 가능 
• Table : RDB에서 테이블과 동일한 개념
  • 스키마를 가지고 있음
  • 행(row) 단위 업데이트는 지원하지 않음
  • 테이블에 대한 권한제어는 따로 적용되지 않으며, 해당 테이블을 가지고 있는 부모 데이터셋으로부터 상속받음
• Job : BigQuery에서 내리는 모든 명령
  • 쿼리 / 데이터 로딩 / 삭제 / 복사 / 내보내기 등의 명령을 수행하는 비동기식 연산 
  • Job(잡)의 구성요소
    • Reference - job ID : 유니크한 이름
    • Configuration - job Task : 수행하는 작업 종류
    • Status - job state : 오류와 경고를 포함한 상태
    • Statistics - job statistics : 수행 내역을 제공하는 통계
  • 잡의 구성요소는 향후 감사(audit)등의 목적을 위해서 로그를 남김
  • 실행 도중 취소 가능 , 하지만 취소해도 해당 명령에 대한 프로세싱은 이루어지기 때문에 비용 발생

데이터 타입

• STRING : UTF-8인코딩. 최대 2MB
• BYTES : base64로 인코딩 된 이진 데이터
• INTEGER : 64 bit signed integer
• FLOAT : Double precision, floating-point format
• BOOLEAN : CSV format: true or false (case insensitive), or 1 or 0

                                 JSON format: true or false (case insensitive)

• RECORD : A Collection of one or more field
  • 중첩 및 반복을 허용하고  JSON 과 같이 여러 개의 데이터 값을 가질 수 있다.
  • 불필요한 반복 및 중첩이 많아지면 쿼리를 포함한 여러 가지 작업들이 어려워지니 가급적 피하기 
• TIMESTAMP

데이터 필드의 모드 

• REQUIRED : NULL값을 허용하지 않음
• NULLABLE : NULL값을 허용(기본값)
• REPEATED : NULL값을 허용하고 1개 또는 배열 형태로 여러 개의 값을 가질 수 있으며 JSON, Avro포맷에서만 사용 가능

데이터 로딩 포맷

• CSV
• JSON(newline-delimited)
• Avro
• Cloud Datastore backups
• Google sheets

## 스키마에 따라서 CSV, JSON , AVRO 모든  flat data 를 지원하지만,

      스키마에 중첩되거나 반복되는 필드를 가진 경우  JSON , Avro 만 지원

## 기본적으로 데이터에 줄바꿈(개행문자) 가 포함되어 있는 경우  JSON, Avro 가 빠름

데이터 로딩 지원도구(Tools)

• Web UI : 웹 브라우저 기반으로 제공하는 빅쿼리 전용 웹 콘솔로 비전문가도 사용하기 쉽다.
• Command-line interface (CLI) : 파이썬 기반의 명령어 도구가 제공된다.
• REST API (POST) : POST요청으로 데이터 로딩을 할 수 있는 REST형태의 API를 제공하며, 재개 가능(Resumable)업로드, 다중 부분(Multipart) 업로드의 두가지 옵션을 제공한다.
• Streaming API : 잡을 사용해서 대규모의 데이터를 로드하는 대신 한번에 하나의 레코드를 삽입할 수 있는 API를 제공한다.
• Third-party ETL tools and connectors : 각종 3party의 ETL(Extract, Transform, Load) 툴 및 시각화, 개발환경 등과의 연동할 수 있도록 커넥터를 제공한다.

SQL의 사용 

• BigQuery는 Standard SQL과 legacy SQL을 모두 제공
• Legacy SQL은 초기부터 사용 된 문법이나, 2.0이후에는 Standard SQL이 지원
• Standard SQL을 사용하는것이 필수는 아니지만 추천 됨

NULL 값은 더하거나 빼거나 곱하거나 나눌 수 없는 값

=> 필요에 따라 NULL 값을 '0' 또는 '원하는값'으로 변경할 수 있어야 한다. 

위의 테이블과 같이 컬럼이 비어있는 것을 "No Name"으로 채워서 처리하고 싶을 때  여러가지 방법이 있다.

1. CASE

: True / False를 판단하여 조건식에 맞게 값을 변환할 때 사용

사용 예시)
CASE WITH 조건식1 THEN 식1
            [WHEN 조건식2 THEN 식2 ...
              ELSE  식3]
END

//실제 사용예시
SELECT 
    ANIMAL_TYPE,
    CASE
        WHEN NAME IS NULL THEN "No name"
        else NAME

    END AS NAME,
    SEX_UPON_INTAKE
FROM ANIMAL_IN

2. COALESCE

: 입력받은 여러 개의 인자 중 NULL이 아닌 값을 반환

사용 예시)
SELECT COALESCE(ID, VALUE)
FROM 테이블

- 주의 사항 

   1. ELSE 문 꼭 사용 -> 기재하지 않으면 NULL 값

   2. NULL 값 체크 시 " = " 을 사용하지 않는다. 

SELECT
    ANIMAL_TYPE,
    COALESCE(NAME, "No NAME") AS NAME,
    SEX_UPON_INTAKE

FROM ANIMAL_INS

3. IFNULL()

: 해당 필드의 값이 NULL 반환할 때, 다른 값으로 출력할 수 있도록 하는 함수 

  하나의 필드 값 뿐 아니라, 연속적으로도 활용할 수 있다.

사용 예시)
SELECT IFNULL(필드명, "대체할 값") FROM 테이블명; 

SELECT
    ANIMAL_TYPE,
    IFNULL(NAME, "No name") as NAME,
    SEX_UPON_INTAKE
from ANIMAL_INS

문제 설명

ANIMAL_OUTS 테이블은 동물 보호소에서 입양 보낸 동물의 정보를 담은 테이블입니다. ANIMAL_OUTS 테이블 구조는 다음과 같으며, ANIMAL_ID, ANIMAL_TYPE, DATETIME, NAME, SEX_UPON_OUTCOME는 각각 동물의 아이디, 생물 종, 입양일, 이름, 성별 및 중성화 여부를 나타냅니다.

보호소에서는 몇 시에 입양이 가장 활발하게 일어나는지 알아보려 합니다. 0시부터 23시까지, 각 시간대별로 입양이 몇 건이나 발생했는지 조회하는 SQL문을 작성해주세요. 이때 결과는 시간대 순으로 정렬해야 합니다.

예시

SQL문을 실행하면 다음과 같이 나와야 합니다.

다른 문제들과 달리 레벨4 라 조금 어려웠고, 새로운 이론도 알 수 있었던 문제였다.

방법 1. UNION  함수 사용

- UNION 이란?

• 2개 이상의 쿼리를 결합할 때 사용
  • UNION : 결합 시 중복 제거 
  • UNION ALL : 결합 시 중복 제거 X

- 해결 방법

• 0~23시 모든 시간에서, 
  해당 시각에 입양된 기록이 없어도(NULL 이어도) COUNT 0으로 출력하는 것이 관건

• UNION으로 0~23 모든 시간을 가진 TABLE을 생성 후
  여기에 LEFT JOIN과 IFNULL을 사용해 문제 해결

SELECT H1.HOUR, IFNULL(OUTS.COUNT,0) AS COUNT 
FROM ( 
    SELECT 0 AS HOUR 
    UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 
    UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 
    UNION SELECT 9 UNION SELECT 10 UNION SELECT 11 UNION SELECT 12
     UNION SELECT 13 UNION SELECT 14 UNION SELECT 15 UNION SELECT 16 
     UNION SELECT 17 UNION SELECT 18 UNION SELECT 19 UNION SELECT 20 
     UNION SELECT 21 UNION SELECT 22 UNION SELECT 23 ) H1 
     LEFT JOIN ( 
         SELECT HOUR(DATETIME) AS 'HOUR', COUNT(*) AS 'COUNT' 
         FROM ANIMAL_OUTS 
         GROUP BY HOUR) AS OUTS ON H1.HOUR = OUTS.HOUR

방법 2. 변수 사용

- 0 ~ 23 시 모든 HOUR을 출력하기 위해 변수 사용

- MySQL 변수 선언 방법

• SET @변수명 = '값';   => " = " : MySQL에서 대입연산자, 비교연산자 로 사용됨(SET 명령어에서만 대입 연산자로 인식)

• SET @변수명 := '값';  => " := "

•  

• SET을 제외한 다른 쿼리문(SELECT 등)은 '=' 를 비교연산자(comparison operator)로 인식하기 때문에, SET이 아닌 쿼리문에서는 반드시 대입 연산자(assignment operator) ':='을 사용

- 해결 방법

• SET으로 HOUR_LIST라는 변수 선언하고 +1 씩 하며 0 ~ 23시 까지 모든 시간에 대한 COUNT 출력

SET @HOUR_LIST = -1; 
SELECT 
(@HOUR_LIST := @HOUR_LIST + 1) AS 'HOUR', 
(SELECT COUNT(*) FROM ANIMAL_OUTS WHERE HOUR(DATETIME) = @HOUR_LIST) AS 'COUNTS' 
FROM ANIMAL_OUTS 
WHERE @HOUR_LIST <23;

join을 통해 2개 이상의 테이블을 연결하여 원하는 데이터로 정제 

• EQUIJOIN : 두 개의 테이블 간에 칼럼값들이 정확하게 일치하는 경우 사용(PK = FK)
• NON - EQUIJOIN : 두 개의 테이블 간에 칼럼값들이 서로 정확하게 일치하지 않는 경우 사용
• OUTER JOIN : 두 개의 테이블 간의 JOIN 걸었을 때, JOIN 조건 만족하지 않는 경우에도 그 테이블을 보고자 하는 경우(+) 연산자 사용하는 조인
• SELF JOIN : 같은 테이블에 있는 행동을 JOIN 하는데 사용

EQUI JOIN  VS  OUTER JOIN

-- Equi Join 으로 부서 번호를 조회하는 예제
SELECT DISTINCT(e.deptno), d.deptno, d.dname
  FROM emp e, dept d
 WHERE e.deptno = d.deptno;
 
DEPTNO     DEPTNO
------ ----------
    10         10
    20         20
    30         30
 
-- Outer Join 으로 부서 번호를 조회하는 예제
SELECT DISTINCT(e.deptno), d.deptno
  FROM emp e, dept d
 WHERE e.deptno(+) = d.deptno;
 
DEPTNO  DEPTNO
 ------- --------
     10       10
     20       20
     30       30
              40
              
-- ename LIKE 조건절에 (+)연산자가 누락된 경우
SELECT DISTINCT(a.deptno), b.deptno
  FROM emp a, dept b
 WHERE a.deptno(+) = b.deptno
   AND a.ename LIKE '%';
 
DEPTNO     DEPTNO
---------- ----------
        10         10
        20         20
        30         30
 
-- ename LIKE 조건절에 (+)연산자를 추가해야 정상적으로 데이터가 조회 된다. 
SELECT DISTINCT(a.deptno), b.deptno
  FROM emp a, dept b
 WHERE a.deptno(+) = b.deptno
   AND a.ename(+) LIKE '%';
 
DEPTNO  DEPTNO
 ------- --------
     10       10
     20       20
     30       30
              40

LEFT OUTER JOIN : 오른쪽 테이블에 조인시킬 컬럼의 값이 없는 경우 사용

-- LEFT OUTER JOIN 조인 예제
SELECT DISTINCT(e.deptno), d.deptno
  FROM dept d 
  LEFT OUTER JOIN emp e
  ON d.deptno = e.deptno;

RIGHT OUTER JOIN : 왼쪽 테이블에 조인시킬 컬럼의 값이 없는 경우

-- RIGHT OUTER JOIN 조인 예제
SELECT DISTINCT(e.deptno), d.deptno
  FROM emp e 
 RIGHT OUTER JOIN dept d
    ON e.deptno = d.deptno;

FULL OUTER JOIN : 양쪽 테이블 모두 OUTER JOIN 걸어야 하는 경우 사용

-- FULL OUTER JOIN 조인 예제
SELECT DISTINCT(e.deptno), d.deptno
  FROM emp e 
  FULL OUTER JOIN dept d
    ON e.deptno = d.deptno;

정의 

SELECT 문을 통해 결과값들이 나올 때, 이 결과들을 메모리 공간(커서)에 저장

커서 => 쿼리문에 의해 반환되는 결과값들을 저장하는 메모리 공간

Fetch => 커서에서 원하는 결과값을 추출

사용하는 이유?

커서는 로우를 기반으로 작업된다 

PL/SQL 의 SQL 문처럼 하나의 결과를 리턴하면 암시적으로 커서에 저장되므로 암시적 커서라 하고

PL/SQL 레코드 와 PL/SQL 테이블을 결합한 것은 명시적 커서라 한다.

커서 종류

1. 묵시적 커서(Implicit Cursor)

        : 오라클에서 자동으로 선언해주는 SQL 커서.(사용자는 생성 유무를 알 수 없다.)

2. 명시적 커서(Explicit cursor)

        : 사용자가 선언해서 생성한 후에 사용하는 SQL 커서, 주로 여러개의 행을 처리하고자

          할 경우 사용한다.

커서 속성

1. %Found : 가져올 레코드가 있는 경우 true를 반환

2. %isOpen : 커서가 오픈 상태일 경우 true를 반환

3. %NotFound : 더이상 참조할 레코드가 없을 때 true를 반환

4. %RowCount : 카운터 역할을 한다. 처음 오픈시 0, 패치발생할 때마다 1씩 증가

커서 처리 단계(명시적)

1. 명시적 커서 선언(커서 생성)

        EX) Cursor 커서이름

2. 명시적 커서 오픈

        EX) Open커서이름

3. 커서에서 데이터 추출(데이터 행 가져오기)

        EX) Fetch 커서이름

4. 커서 종료

        EX) Close 커서이름

Procedural Language extension to SQL => 오라클에서 SQL 확장하여 사용하는 프로그램 언어 (절차적 언어)

사용하는 이유 ?

• 대용량 데이터를 연산해야 할 때, WAS 등의 서버로 전송하여 처리하면 네트워크에 부하가 많이 걸릴 수 있다.
  • => 웹 서버(WEB) : HTML 페이지 등을 네트워크 망에 종속되지 않고, 웹서비스를 할 수 있도록 하는 Application
  • => 웹 애플리케이션 서버(WAS) : 웹 서버 + 웹 컨테이너 => tomcat 
                                      / 인터넷 상에서 HTTP 통해 사용자 컴퓨터나 장치에 애플리케이션을 수행해주는 미들웨어 
                                      / 웹 컨테이너 : JSP + Servlet 실행시킬 수 있는 SW 
• 로직 수정하기 위해 서버를 셧다운 시키지 않고, 단순히 DB에 프로시저 호출하여 사용
• 블록 단위로 유연하게 사용할 수 있다.

WAS 기본 기능

• 프로그램 실행 환경과 DB 접속 기능 제공
• 여러 개의 트랜잭션 관리
• 업무 처리하는 BL 수행
• WEB Service 플랫폼 역할

기본 구조 

• 블록 : 기본 단위 , 선언부, 실행부 , 예외 처리부 / 종류 : 익명 블록, 함수, 프로시저 
  • 선언부 : DECLARE
  • 실행부 : 실제 로직 처리
  • 예외처리부 : 로직 처리하다가 오류가 발생하면 처리하는 내용 기술 하는 부분으로 생략

PL / SQL sub program : function / procedure

• 함수(function) : 매개변수 존재 / 반환값 존재
• 프로시져(procedure) : 매개변수 존재 / 반환값 없음(특정 로직만 처리 후 종료)

작성하는 순서 

: SELECT -> FROM -> WHERE -> GROUP BY -> HAVING -> ORDER BY

기본 SQL 문법 처리 및 실행 순서

: FROM -> WHERE -> GROUP BY -> HAVING -> SELECT -> ORDER BY