회사에서 데이터 추출에 쿼리를 짜야하는 일이 있었는데..

제가 짠 쿼리와 상사분께서 짠 쿼리의 성능차이가 엄청 난 것을 확인할 수 있었습니다. 

(추출되는 데이터도 100G 정도 차이나고, 속도 또한 훨씬 향상된 쿼리였습니다. ㅜ.ㅜ) 

제가 생각했을 때 근본적인 원인은

아직도 저는 빅쿼리를 사용할 때 RDB에서의 쿼리처럼 짜고 있는 것이라고 생각했습니다.  

RDB에서는 OLTP, OLAP 페이지에서 확인할 수 있듯이,

하나의 레코드 안에 파일로서 저장하기 때문에 검색 시에 모든 데이터를 풀 스캔 하여야 합니다. 

그러나, 빅쿼리는 컬럼 기반 저장소이기 떄문에, 각 칼럼 별로 다른 파일에 나누어 저장합니다. 

즉, 제가  추출하고 싶은 각 컬럼을 행으로 집합 처리 한 후  join 조건의 쿼리를 날렸어야 하는 것인거죠.. 

빅쿼리의 집합처리 방법에 대해 알아보는 페이지가 될 것 같습니다.

참고로, 빅쿼리는 비정규화 되었을 때 성능이 가장 뛰어납니다.

- 비정규화를 위해 중첩 및 반복 열을 사용하며

- 중첩된 레코드를 사용할 때 장점은 아래와 같습니다. 

• 빅쿼리 퍼포먼스 개선
• 데이터 저장 용량 효율
• 스키마가 바뀌어도 유연하게 대응 가능 

참고 :  https://zzsza.github.io/gcp/2020/04/12/bigquery-unnest-array-struct/

BigQuery ARRAY

1.  문서 : cloud.google.com/bigquery/docs/reference/standard-sql/array_functions?hl=ko#generate_date_array
   
   
2. 빅쿼리는 데이터 유형이 동일한 값으로 구성된 목록을 ARRAY(배열) 라 부릅니다.
   1. 파이선 LIST와 유사합니다.
   2. 하나의 행에 데이터 타입이 동일한 여러 값을 저장합니다.
   3. 빅쿼리 UI에서 배열로 보여줄 때 세로로 나열됩니다. 
      
      

3. 예시

   SELECT       [1,2,3] AS array_sample, 1 AS int_value   UNION ALL   SELECT       [3,5,7] AS array_sample, 2 AS int_value

   
   
   결과 : 행 1 안에 (1,2,3) 이 배열로서 출력되는  반면, 숫자값만 나열한 int_value는 1개만 볼 수 있습니다. 

   

   
   
   
   
4. ARRAY 생성 방법

   1. 대괄호 사용

      SELECT [1, 2, 3] AS array_sample

   2. ARRAY<타입> 사용

      SELECT ARRAY<INT64>[1,2,3] AS int_array

   3. GENERATE 함수 사용 

      -- GENERATE_ARRAY(시작, 종료, 간격) : python에서 range(start, end, step)과 동일하게 생성할 수 있습니다. SELECT GENERATE_ARRAY(1, 10, 2) AS generate_array_data

   4. ARRAY_AGG 사용 
      

      • 테이블에 저장된 데이터를 SELECT 하고 ARRAY 로 묶고 싶은 경우 사용합니다. 

      • 옵션
        1. DISTINCT : 각각의 고유한 expression  값이 한 번만 결과에 집계됩니다. 
        2. IGNORE NULLS : NULL 값이 결과에서 제외 / RESPECT NULLS : 지정되거나, 아무것도 지정되지 않으면 결과에 NULL 포함
        3. ORDER BY : 값의 순서 지정 (기본 정렬 방향은 ASC 입니다) 
        4. LIMIT : 결과에서 expression 입력의 최대 개수를 지정합니다. 한도 n은 상수 INT64입니다. 

      • 예시

        WITH programming_languages AS       (SELECT "python" AS programming_language        UNION ALL SELECT "go" AS programming_language        UNION ALL SELECT "scala" AS programming_language)                  SELECT ARRAY_AGG(programming_language) AS programming_languages_array   FROM programming_languages

        
        

        

        
        
        
        
        

5. ARRAY 내 접근
   1. 배열 N 번째 값을 가져오려면, OFFSET, ORDINARY  을 사용할 수 있습니다. 
      1. OFFSET : 0 부터 시작
      2. ORDINARY : 1부터 시작
         
         

   2. 존재하지 않은 N을 지정하면 에러가 발생하는데, SAFE_를 앞에 붙여주면 NULL이 return 됩니다. 

      WITH programming_languages AS     (SELECT "python" AS programming_language      UNION ALL SELECT "go" AS programming_language      UNION ALL SELECT "scala" AS programming_language)          SELECT   ARRAY_AGG(programming_language)[OFFSET(0)] AS programming_languages_array,   ARRAY_AGG(programming_language)[ORDINAL(1)] AS programming_languages_array2 FROM programming_languages

      
      

      

      
      
      
      
      
      

BigQuery STRUCT

1. 문서 : https://cloud.google.com/bigquery/docs/reference/standard-sql/data-types?hl=ko#struct-type
   
   
   
2. 구조체, BigQuery UI에서 RECORD로 표현됩니다. 
   
   1. 각각의 유형(필수) , 필드 이름(선택사항) 이 있는 정렬된 필드의 컨테이너로 
   2. Python 의 Dict 와 유사한 느낌입니다. 
   3. Firebase에서 저장된 데이터를 볼 때 많이 사용합니다. 
      
      
3. STRUCT 생성 방법

   1. 소괄호 사용 

      SELECT (1, 2, 3) AS struct_test

   2. STURCT<타입> 사용 

      SELECT STRUCT<INT64, INT64, STRING>(1, 2, 'HI') AS struct_test

      

      
      
      

   3. ARRAY 안에 여러 STRUCT 사용하고 싶은 경우 

      SELECT   ARRAY(     SELECT AS STRUCT 1 as hi, 2, 3     UNION ALL     SELECT AS STRUCT 4 as hi, 5, 6   ) AS new_array

      
      

      

BigQuery UNNSET

1. 문서 : https://cloud.google.com/bigquery/docs/reference/standard-sql/query-syntax?hl=ko#unnest
   
   
2. 배열을 평면화해서 배열에 있는 값을 펴줄 때 사용합니다. 
   1. NEST한 데이터를 UNNSET 하게 만드는 것과 같습니다. 
   2. UNNSET 연산자는 ARRAY 를 입력으로 받고 ARRAY 의 각 요소에 대한 행이 한 개씩 포함된 테이블을 return 합니다. 
      
      
3. 각 행에 배열의 값을 뿌려주는 직관적인 예시가 있어 첨부하겠습니다 : https://medium.com/firebase-developers/using-the-unnest-function-in-bigquery-to-analyze-event-parameters-in-analytics-fb828f890b42
   1. 해당 데이터가 있습니다. 
      

      

      
      
      
      
   2. crew = 'Zoe'를 추출하고 싶어 UNNSET을 사용해 데이터를 펴주는 과정을 시각화 하면 아래와 같습니다. 
      

      

      
      
      
      
   3. WHERE 절로 Zoe 를 찾습니다. 
      

      

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

빅쿼리란?(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을 사용하는것이 필수는 아니지만 추천 됨