데이터 모델링?

주어진 개념으로 부터 논리적인 데이터 모델을 구성하는 작업. 

설계 순서 

1. 요구사항 파악 -> 2. 개념적 데이터 모델 설계 (ERD 작성) -> 3. 논리 모델링 -> 물리 모델링

모델링 3가지 요소

1. 업무가 관여하는 어떤 것(Things)

2. 어떤 것이 가지는 성격(Attributes)

3. 업무가 관여하는 어떤 것 간의 관계(Relationship)

좋은 데이터 모델의 요소 

1. 완전성

2. 중복배제 

3. 업무규칙

4. 데이터 재사용

5. 의사소통

6. 통합성

데이터 모델링 용어

논리 모델링

• 어떤 정보를 객체화 할 것인가에 대한 규정(엔티티, 엔티티타입, 관계 정의)
  • (EX) 업무를 분석하여 그에 대한 데이터 집합/ 관계를 중점적으로 표현하는 것

물리 모델링 

• 실제 DBMS에서 생성될 테이블을 설계.
• 논리 모델링에서 도출된 각 엔티티 관계에 의해 나올 수 있는 테이블을 설계하거나
  • (EX) many to many 관계에서 도출되는 table, super-sub 관계에서 도출되는 테이블 등)
• 관계에 대한 정의 (cascade 등등), index, 컬럼별 데이터 타입 및 제약 조건 등의 속성 정의 하여 정규화 실행

결과적으로는 모델링의 각종 이슈를 반영한 ERD를 포워딩 했을때 정확하게 데이터베이스가 생성되는 것을 보통 설계가 끝났다고 표현합니다.

ERD

:Entity Relationship Diagram / 데이터들의 관계 도표

ERD 규칙

- 엔티티(Entity) : 정보가 저장될 수 있는 사람, 장소, 사물, 사건 등 독립적인 존재. 즉, 테이블(학생, 과목, 수강, 사원, 부서)

- 두 개체의 관계 _선

- A 테이블의 PK 를 B테이블이 소유하면 -> A : 부모 / B : 자식

- 실선 : 부모 테이블의 PK를 자식 테이블이 가지고 있으며, 자식 테이블의 PK로 사용 시 

- 점선 : 부모 테이블의 PK를 자식 테이블이 가지고 있으나, 자식 테이블의 PK로 미사용 시

Address 개체에서 address_id가 PK로 설정이 되어있는데 Store 개체가 address_id를 가지려 한다면,

이 때 식별자 관계에서는 FK를 PK로 설정(🔑)을 했고,

비식별자 관계에서는 일반 속성(🔹)으로 가지고 온 것을 확인 가능

참고로, FK를 PK로 지정할 때의 

하나의 예시로 자식 테이블에서 할아버지/할머니 테이블을 참조할 때가 있을 수 있음.

상황에 따라 필요할 수도 있고 필요 없을수도?

- 속성(Attribute) : 엔터티의 성질, 분류, 수량, 상태 특성을 구체적으로 나타내는 세부 항목. 즉, 물리적 모델의 컬럼(열)을 말함.

※관계스키마 : 과목(과목코드, 과목내용, 과목명)

학생(이름, 학번, 주소, 전공, 취미)

     (1) 속성유형

     - 단순 속성(Simple Attribute)  : 더 이상 작은 구성원소로 분해 할 수 없는 속성

     - 복합 속성(Composite Attribute) : 몇 개의 기본적인 단순 속성으로 분해 할 수 있는 속성

     - 다중 값 속성(Multivate Attribute) : 다중 값 속성은 한 엔터티에 대해서 여러 개의 값을 갖는 것으로써, 취미 속성

     - 유도된 애트리뷰트(Derived Attribute) : 실제 값이 저장되어 있는 것이 아니라 저장된 값으로부터 계산해서                                                                    얻은 결과 값을 사용하는 애트리뷰트를 말한다.

     (2) 주 식별자 / 비 식별자

     - 주 식별자 : 식별 할 수 있는 유일한(Primary Key) 제약 조건을 갖는속성

       ERD에서 실별자는 속성에 밑줄을 그어서 표현

     예제) ERD

※ 관계스키마 : 사원(사원번호(PK), 이름, 주소, 주민번호)

취미

부서(부서코드(PK), 부서명)

     (3) 관계 (Relation)

     - 엔터티 사이의 연관성을 표현하는 개념

     - 두 개의 엔터티 타입 사이의 업무적인 연관성을 논리적으로 표현

     - ERD에서 엔터티들 사이에 관계

     - 타입은 마름모를 사용하여 표현한 후 그 관계에 연관된 엔터티에 선으로 연결하여 표시

     예제) ERD

      - 소속 관계

    - 수강 관계

     (4) 유형

     1. 카디날리티(Cardinality) : 관계의 대응 엔터티 수라고도 함

     2. 카디날리티 표현방법 : 일대일(1:1), 일대다(1:N), 다대다(N:M)

- 두 개체의 관계 - 선의 끝Cardinality

Cardinality(차수)는 한 개체에서 발생할 수 있는 발생 횟수를 정의하며,

다른 개체에서 발생할 수 있는 발생 횟수와 연관.

1대1관계, 1대N관계, N대N 관계가 있음.

✔️  One-to-One Cardinality

1:1 관계에서는 아래와 같이 표기합니다.

One-to-One Cardinality

✔️  One-to-Many Cardinality

1:N 관계에서는 아래와 같이 표기합니다.

✔️  Many-to-Many Cardinality

N:M 관계에서는 아래와 같이 표기합니다.

Many-to-Many Cardinality

필수참여 조건

'|' 표시가 있는 곳은 반드시 있어야 하는 개체, 'O' 표시가 있다면 없어도 되는 개체

inventory 개체가 없어도 store 개체는 있을 수 있고, store 개체가 없다면 inventory 개체도 있을 수 없음.

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

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

(추출되는 데이터도 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 를 찾습니다. 
      

      

개요

빅쿼리는 파이썬 기반의 CLI(Command Line Interface)도구인 bq 를 이용해서 명령을 실행할 수 있습니다. 

해당 페이지에는 기본적인 명령어와 사용법을 정리해보았습니다. 

참고 사이트 : https://cloud.google.com/bigquery/docs/reference/bq-cli-reference?hl=ko

설치

• gcloud CLI 는 구글 클라우드 SDK의 일부입니다. 
• 시스템에서 SDK를 다운받아 설치하고 초기화 한 후 gcloud 명령줄 도구를 사용할 수 있습니다. 
•  PowerShell 명령어를 통해 SDK를 설치할 수 있습니다.  powershell 로 진행해보겠습니다. 

1. Prerequire
   Google Cloud SDK 는 Python 으로 프로그래밍 되어있기 때문에, python 3.5 ~ 3.7 버전까지 지원합니다. 저는 3.8 이였기 때문에 다운그레이드 하여 설치하였습니다. 

2. Installation
   Powershell 에서 해당 명령어를 실행합니다. 

3. 설치 완료 후 다음 옵션을 허용 후 기본값으로 설정해주면 Cloud SDK Shell 설치 완료됩니다. 
   
   1. Cloud SDK 쉘 시작
   2. gcloud init 실행 
      
      

명령어

(New-Object Net.WebClient).DownloadFile("https://dl.google.com/dl/cloudsdk/channels/rapid/GoogleCloudSDKInstaller.exe", "$env:Temp\GoogleCloudSDKInstaller.exe")
& $env:Temp\GoogleCloudSDKInstaller.exe

bq 명령어 

명령설명사용법

로컬 데이터 소스에서 데이터 로드

아래의 방법 중 하나를 사용하여 읽을 수 있는 데이터 소스를 로드할 수 있습니다.  

• Cloud Console 또는 기본 BigQuery 웹 UI
• bq 명령줄 도구의 bq load 명령어
• API
• 클라이언트 라이브러리

이 중, bq 명령줄 도구의 bq load 명령어를 사용하면 아래와 같이 진행됩니다. 

조건은

• 구글 쉘 SDK를 실행되는 리눅스에 데이터소스에 대한 파일이 있어야 하며, 윈도우 PATH나 파일은 인식하지 않습니다. 

1. 해당 데이터를 로드할 테이블을 생성 합니다.  (참고 : https://cloud.google.com/bigquery/docs/tables?hl=ko) 

2. bq 명령어를 사용해 테이블에 데이터를 로드합니다. 

bq load \
프로젝트명:데이터셋명.테이블명\
데이터 path (절대 주소) / 데이터.csv

Cloud Storage 에서 데이터 로드 

리눅스에 파일 업로드나 , 용량이 커서 Cloud Console을 통해 테이블에 업로드하지 못했다면 Cloud Storage를 통해 로드할 수 있습니다. 

조건은

• 테이블을 포함한 데이터세트는 스토리지 버킷과 같은 리전이나 멀티 리전에 위치에 있어야합니다. 
• 빅쿼리 로드에 대한 모든 권한이 있어야 합니다. 
  
  

1.  스토리지 버킷 생성 → 데이터 업로드 가 선행되어야 합니다. (참고 : https://cloud.google.com/storage/docs/creating-buckets?hl=ko ) 
   
   
2. 해당 데이터를 로드할 테이블을 생성합니다. (참고 : https://cloud.google.com/bigquery/docs/tables?hl=ko) 

3. bq 명령어를 사용해 테이블에 데이터를 로드합니다. 

bq load \
프로젝트명:데이터셋명.테이블명\
gs://버킷명/데이터.csv

위의 두 가지 방법을 사용하면 아래와 같이 데이터가 해당 테이블에 로드된 것을 확인할 수 있습니다. 

추가적인 옵션(--autodetct) 등등은 구글 문서에서 확인하실 수 있습니다. 

OLTP (On-Line Transaction Processing)

실시간으로 서버가 자료를 처리하는 과정을 의미하며, 여러 과정의 연산이 하나의 단위 프로세스(= 트랜잭션) 으로 실행하는 프로세스입니다.

예를 들면

A가 B에게 10,000원을 이체한다면 아래의 단계들이 하나의 프로세스로 이루어져야 하며, 중간에 오류가 발생하는 경우 모든 단계를 되돌려야합니다.

1. A 의 계좌에서 10,000원이 인출되어
2. B 의 계좌에 10,000원이 입급됩니다. 

ROW단으로 저장하는 MySQL, MSSQL 등 RDBMS에서 사용하는 프로세스입니다.

해당 DB들은 물리적으로 데이터 저장 시 아래와 같이 하나의 레코드안에 파일로서 저장을 하기 때문에 검색시 모든 데이터를 풀스캔 하여야 합니다. 

FILE 1 : “001;Cho;Terry;Seoul;30,002;Lee;Simon;Suwon;40,003;Kim;Carl;Busan;22”

이러한 I/O 효율성을 높이기 위해  Index를 사용하는 것이 해당 프로세스의 특징입니다. 

Table 구조 

DB 설계 시 OLTP 처리 프로세스의 영향이 있습니다.

모든 웹 사이트 서비스를 이용 시 회원가입을 진행합니다. 가입자 수가 많아지면 가입된 회원 ID를 가져오는 것이 느려지는데, 

이 문제를 해결하는 방법은 Index(=Primary Key)를 생성해 해결할 수 있습니다. 

Index 는 보통 테이블의 자료를 빠르게 검색하기 위해 생성되며, 1개의 테이블에 N개의 Index를 생성할 수 있습니다.

Index 생성 시 속성(UNIQUE / NON UNIQUE)를 설정해 중복 허용 여부를 지정할 수 있습니다. 

가장 많이 쓰이는  BTree Index 는 이진검색에 기반을 둔 것으로

자료가 정렬되어있는 상태에서 절반 위치를 찾아가는 구조로 전체를 읽을 때보다 빠르게 원하는 값을 찾을 수 있고 이 값이 위치한 테이블의 레코드를 바로 접근하여 원하는 값을 가져올 수 있습니다. 

인덱스와 테이블 관계를 표시한 예시는 다음과 같습니다. 

회원_ID_IndexID(KEY)Table 위치 값

→ 

회원입력순서ID암호

클러스터 

디스크로부터 데이터를 읽어오는 시간을 줄이기 위해서 조인이나 자주 사용되는 테이블의 데이터를 디스크의 같은 위치에 저장하는 것으로

OLTP 특성을 가진 DB의 테이블에 인덱스로 많이 사용되는 형식입니다. 

클러스터 하기 좋은 테이블은 

• 주로 조회가 발생하고 수정이 거의 발생하지 않는 테이블
• 컬럼안의 많은 중복 데이터 가지고 있는 테이블
• 자주 JOIN 되는 테이블 

이고 ,  클러스터 KEY로는 

• 데이터 값의 범위가 큰 컬럼
• 테이블 간의 조인에 사용되는 칼럼

을 많이 사용하고 있습니다. 

클러스터형 인덱스는 두 가지 종류로 나눠질 수 있는데 특징은 다음과 같습니다. 

• 클러스터 인덱스 
  

  - 인덱스를 생성할 때는 데이터 페이지 전체를 다시 정렬한다.
  - 대용량의 데이터를 강제로 다시 클러스터 인덱스를 생성하는 건 조심
  - 인덱스 자체가 데이터 페이지이다. 인덱스 자체에 데이터가 포함
  - 비클러스형 인덱스 보다 검색 속도는 더 빠르다. 하지만 데이터의 입력/수정/삭제는 느리다.
  - 테이블에 한 개만 생성할 수 있다.

  
  
  
  
• 넌클러스터 인덱스
  

  - 별도의 페이지에 인덱스를 구성한다.
  - 검색 속도는 느리지만, 데이터의 입력,수정,삭제가 더 빠르다.
  - 남용할 경우에는 시스템 성능을 떨어뜨리는 결과를 가져온다.

OLAP ( On-Line Analytical Processing )

대용량 데이터를 고속으로 처리하며 다양한 관점에서 추출, 분석할 수 있도록 지원하는 데이터 분석 기술입니다. 

최종 사용자가 대규모 데이터(DW) 에 직접 접근하여 대화식으로 정보를 분석하고 의사결정에 활용하는 과정을 의미하며 DW에 저장된 데이터를 쉽게 보고서로 출력할 수있도록 도와줍니다. 

데이터베이스 내용을 다차원적으로 분석하고자 스냅샷형식으로 데이터를 가지고와서 정제와 가공 과정을 거쳐 Data Mart에 담아놓고 분석 View를 제공하는 프로세스라고 할 수 있습니다. 

BigQuery 

빅쿼리는 OLAP 기반으로 컬럼 단위로 저장하는 데이터베이스입니다. 

컬럼 기반 저장소의 경우 각 컬럼을 아래와 같이 다른 파일에 나눠서 저장합니다. 

FILE 1: 001:Cho,002:Lee,003:Kim

FILE 2: 001:Terry,002:Simon,003:Carl

FILE 3: 001:Seoul;002:Suwon:003:Busan

컬럼 단위로 저장 시 해당 컬럼 기준으로 검색하면 다른 컬럼 데이터를 스캔하지 않아도 되기 때문에 데이타 효율성이 높습니다. 

SQL 연산 수행 순서를 보면 해당 프로세스를 이해하기 쉽습니다. 

natality 테이블에서 1980 ~ 1990 년 대 태어난 아이들의 수를 state 별로 그룹핑하여 내림차순 정렬 후 상위 10개 데이터만 출력하는 쿼리 예시입니다. 

SELECT state, COUNT(*) count_babies
FROM [publicdata:samples.natality]
WHERE year >= 1980 AND year < 1990
GROUP BY state
ORDER BY count_babies DESC
LIMIT 10

디스크에서 state, year 컬럼 읽어와서 → 1980 ~ 1990 년 데이터를 주 단위로 태어난 아이들 수 카운트

 → mixer 에서 state 별 아이들 수 합치고 → mixer0 에서 mixer1의 모든 값을 합쳐 sorting → sorting 끝나고 맨 위 10개만 리턴 

RDBMS와 다르게 BigQuery 가 가지고 있는 특성은 다음과 같습니다.

• 키나 인덱스의 개념이 없어서 무조건 풀 스캔한다. 
• 성능 위해서 update 대신 append만 지원한다. 한번 입력되면 수정되기 어렵기 때문에 테이블을 지우고 다시 생성해야한다 
• 3개의 데이터센터에 걸쳐 복제하기 때문에 데이터 입력 후 데이터가 바로 조회되지 않을 수 있다. 

이러한 특성을 잘 알고 있어야 빅쿼리 조회 시 비용이 어마어마하게 청구되는 일을 막을 수 있을 것 같습니다 ㅎㅎ 

1. 파티션 기준으로 조회 
2. ( * ) 는 절대 금지 

기능적 비교 

데이터 측면 비교 

참고 : https://bcho.tistory.com/1117

쿼리문 : 테이블 / 표현식을 한 개 이상 검색하고 계산된 결과를 행으로 반환

BigQuery SQL 구문

query_statement:
    query_expr
    
    
query_expr:
    [ WITH with_query_name AS ( query_expr ) [, ...] ]
    { select | ( query_expr ) | query_expr set_op query_expr }
    [ ORDER BY expression [{ ASC | DESC }] [, ...] ]
    [ LIMIT count [ OFFSET skip_rows ] ]

select:
    SELECT [ AS { STRUCT | VALUE } ] [{ ALL | DISTINCT }]
        { [ expression. ]* [ EXCEPT ( column_name [, ...] ) ]
            [ REPLACE ( expression [ AS ] column_name [, ...] ) ]
        | expression [ [ AS ] alias ] } [, ...]
    [ FROM from_item  [, ...] ]
    [ WHERE bool_expression ]
    [ GROUP BY { expression [, ...] | ROLLUP ( expression [, ...] ) } ]
    [ HAVING bool_expression ]
    [ WINDOW window_name AS ( window_definition ) [, ...] ]

set_op:
    UNION { ALL | DISTINCT } | INTERSECT DISTINCT | EXCEPT DISTINCT

from_item: {
    table_name [ [ AS ] alias ] [ FOR SYSTEM_TIME AS OF timestamp_expression ]  |
    join |
    ( query_expr ) [ [ AS ] alias ] |
    field_path |
    { UNNEST( array_expression ) | UNNEST( array_path ) | array_path }
        [ [ AS ] alias ] [ WITH OFFSET [ [ AS ] alias ] ] |
    with_query_name [ [ AS ] alias ]
}

join:
    from_item [ join_type ] JOIN from_item
    [ { ON bool_expression | USING ( join_column [, ...] ) } ]

join_type:
    { INNER | CROSS | FULL [OUTER] | LEFT [OUTER] | RIGHT [OUTER] }

표기법:

• 대괄호 '[ ]'는 절(선택사항)
• 괄호 '( )'는 리터럴 괄호
• 세로 막대 '|'는 논리 OR
• 중괄호 '{ }'는 옵션 조합을 묶는 데 사용
• 꺾쇠 괄호 '[, ...]' 안에 있는 쉼표 다음에 오는 줄임표는 앞의 항목이 쉼표로 구분된 목록으로 반복될 수 있음

SELECT

- SELECT expression

- SELECT expression.* : 열마다 출력 열을 한 개씩 생성

WITH groceries AS
  (SELECT "milk" AS dairy,
   "eggs" AS protein,
   "bread" AS grain)
SELECT g.*
FROM groceries AS g;

+-------+---------+-------+
| dairy | protein | grain |
+-------+---------+-------+
| milk  | eggs    | bread |
+-------+---------+-------+

- SELECT DISTINCT : 중복 행 삭제 / 나머지 행만 반환 / STRUCT , ARRAY 유형의 열은 반환할 수 없음

- SELECT * EXCEPT : 결과에서 제외할 열의 이름을 한 개 이상 지정 / 일치하는 모든 열 이름은 출력에서 생략

WITH orders AS
  (SELECT 5 as order_id,
  "sprocket" as item_name,
  200 as quantity)
SELECT * EXCEPT (order_id)
FROM orders;

+-----------+----------+
| item_name | quantity |
+-----------+----------+
| sprocket  | 200      |
+-----------+----------+

- SELECT * REPLACE : expression AS identifier 절 한 개 이상 지정 / 각 식별자는 SELECT * 문의 열 이름 한개와 일치 
                             출력 열 목록에서 REPLACE 절의 식별자와 일치하면, 표현식으로 바뀜

WITH orders AS
  (SELECT 5 as order_id,
  "sprocket" as item_name,
  200 as quantity)
SELECT * REPLACE ("widget" AS item_name)
FROM orders;

+----------+-----------+----------+
| order_id | item_name | quantity |
+----------+-----------+----------+
| 5        | widget    | 200      |
+----------+-----------+----------+

WITH orders AS
  (SELECT 5 as order_id,
  "sprocket" as item_name,
  200 as quantity)
SELECT * REPLACE (quantity/2 AS quantity)
FROM orders;

+----------+-----------+----------+
| order_id | item_name | quantity |
+----------+-----------+----------+
| 5        | sprocket  | 100      |
+----------+-----------+----------+

- 정의 : 데이터의 일관성을 보장하기 위한 방법 (= 잠금 / 데이터를 동시에 사용하게 해주는 기능)
- 유형

• Shared Lock(= 공유 Lock / Read Lock) : 다른 사용자가 같이 읽는 것을 허용하지만 변경하는 것은 허용하지 않음

  • Select

• Exclusive Lock(= 배타적 Lock / Write Lock) : 다른 사용자가 읽는 것, 변경하는 것 모두를 허용하지 않음

  • Insert / Update / Delete

- Lock 단위

• RID : 하나의 행

• KEY : 인덱스가 있을 때 행

• PAGE : 8KB 데이터 페이지 또는 인덱스 페이지

• EXTENT : 인접한 8개의 데이터 페이지 또는 인덱스

• TABLE : 데이터와 인덱스가 포함된 전체 테이블

• DB : 데이터베이스

- Lock 종류

• SHARED(S)

  • 트랜잭션이 데이터를 읽기 동안에만 잠금

  • 공유잠금(S) 를 사용시, 여러 트랜잭션이 동시에 하나의 개체를 읽을 수 있다. 즉, 공유 잠금끼리는 서로 충돌되지 않는다

  • 공유잠금(S) 를 사용시, 다른 트랜잭션이 데이터를 변경할 수 없다.

  • 다음 행을 읽을 때, 현재 행의 공유 잠금을 해제한다.

    • 예외 사항

      • 트랜잭션 고립수준 : REPEATABLE 레벨 이상 설정 시

      • LOCK HINT 써서, 해당 공유잠금을 트랜잭션 끝까지 유지

      • SELECT * FROM EMP(HOLDLOCK)

• EXCLUSIVE(X)

  • 트랜잭션이 데이터를 변경할 때 단독 잠금

  • 단독잠금이 걸린 데이터는 다른 트랜잭션이 공유 잠금, 단독 잠금 또한 걸 수 없다.

  • 오직 하나의 트랜잭션만이 데이터에 대한 단독 잠금을 걸 수 있다.

    • 예외 사항

      • 트랜잭션 격리 수준사용해서 단독 잠금 걸린 데이터를 다른 트랜잭션이 읽게 할 수 있다.

• UPDATE(U)

  • 트랜잭션이 변경할 데이터를 찾으면 거는 락

  • 나중에 데이터를 변경할 때 까지 데이터가 변경되지 않았음을 확신하기 위해 건다.

  • 한 번에 한 트랜잭션에만 업데이트 잠금을 얻을 수 있기 때문에 교착상태(deadLock)이 방지된다.

  • U 잠금이 걸린 데이터에는 다른 트랜잭션이 U잠금이나 X잠금을 걸 수 없지만, S잠금은 걸 수는 있다.

• INTENT(I)

  • 트랜잭션이 대상 데이터에 잠금을 걸 수 있을지 없을지를 신속히 판단할 수 있게 도와주는 잠금

  • ex ) 의도적 공유 잠금 (IS) 를 테이블 수준에서 설정 = 해당 트랜잭션이 해당 테이블의 페이지 또는 행에 대해 공유 잠금을 설정

  • 직접 테이블의 모든 행, 페이지 잠금을 확인할 필요가 없기 때문에 성능이 향상된다.

  • 종류

    • IX (= 의도적 배타저 잠금) : 잠금을 걸려는 트랜잭션이 각 리소스 계층 (테이블, 페이지, 행) 에 대해 X 잠금 설정하여 계층의 아래쪽 일부 리소스 수정

    • SIX (= 공유 및 의도적 배타적 잠금) : 잠금을 걸려는 트랜잭션이 각 리소스 계층에 대해 IX 잠금을 설정하여 계층의 아래쪽에 있는 모든 리소스에 대해서는 읽기 작업 / 일부 리소스는 수정 작업 / 최상위 수준 리소스에서는 동시 IS 잠금 허용

• SCHEMA (Sch)

  • Sch-M (Schema Manipulation)

    • DDL(스키마 변경) 문 실행 시, Schema 자체에 대해서 건다.

    • 모든 잠금에 대해 배타적, 어떤 작업도 허용하지 않음.

  • Sch-S (Schema Stability)

    • 쿼리문 컴파일 시 발생한다

    • S or X와 호환된다

    • 다른 트랜잭션 잠금을 차단하지 않 cl는다.

    • DDL 작업은 수행할 수 없다.

-  Lock 옵션 (WITH ~)

• 테이블 힌트

  • 쿼리문 작성 시 FROM 절에 오는 테이블 이름 다음에 WITH 구문으로 옵션 작성

  • 사용시 사용자가 원하는 형태로 강제 판단할 수 있음.

• 테이블 힌트 종류
  • NOLOCK ( = READUNCOMMITED) 
    • 배타적잠금 걸린 대상에 대해 공유잠금(S)를 설정할 수 없어 차단 발생 시,
    • 공유 잠금이 걸린 대상에 배타적잠금을 설정하는 경우 
      => 차단 현상이 발생하는 문제 발생 
    • 해결책 : SELECT 쿼리를 할 때 공유잠금을 설정하지 않고 데이터를 읽음 / 커밋되지 않은 결과를 미리 읽어오는 것
    • 예제 

• • ROWLOCK
    • 기본적인 행 단위 잠금을 사용하지만, 내부적 기준 따라 더 상위 단위(페이지, 테이블) 대상에 대해서 잠금 설정하려면 페이지 , 테이블 잠금을 자동적으로 승격하여 사용 
    • 해결책 : 사용자가 이를 통제하여 행단위잠금을 강제하기 위해서 사용하는 옵션
  • READPAST
    • 다른 트랜잭션이 잠근 행을 읽지 않음
  • XLOCK
    • 단독 잠금을 걸고 트랜잭션이 끝날 때 까지 유지
  • UPLOCK
    • SELECT 하는 대상에 대해 곧바로 배타적 잠금 설정 예정일 경우사용
    • SELECT 하는 대상에 다른 세션에서 배타적 잠금을 설정하지 못하도록 통제하기 위해서 사용 
    • 예시

1) SPID 51 : SELECT * FROM 테이블 WITH(UPDLOCK)
2) SPID 52 : UPDATE 테이블 SET...
3) SPID 51 : UPDATE 테이블 SET...


->  52번 업데이트 작업이 51번에서 설정된 UPLOCK 으로 인해 차단, 3번 UPDATE 작업은 계속하여 진행

- Lock 레벨

• 아래 레벨 외에 인덱스(key)에 로우 레벨 lock 거는 경우 있음

• locking level이 낮을 수록 동시성은 좋지만, 관리해야할 Lock 개수가 증가하기 때문에 더 많은 리소스 소비
• locking level이 높을 수록 적은 양의 lock 리소스를 사용하지만 하나의 lock 으로 수많은 레코드를 한꺼번에 locking 하기 때문에 동시성은 나빠짐
• Lock Level

- Blocking 

• Lock들의 경합(Race condition) 이 발생하여 특정 세션이 작업을 진행하지 못하고 멈춰선 상태
  ->  먼저 Lock 을 설정한 트랜잭션을 기다려야하기 때문에, 이런 현상 반복시 빠른 서비스를 제공할 수 없다.
• 공유 Lock - 배타적 Lock / 배타적 Lock - 배타적 Lock 끼리 블로킹 발생할 수 있음
• 해결 방법 : Transaction commit 또는 Rollback
  1. SQL 문장에 가장 빠르게 실행되도록 리펙토링 하는 것
  2. 트랜잭션을 가능한 짧게 정의하면 경합 줄일 수 있음
  3. 동일한 데이터를 동시에 변경하는 작업을 하지 않도록 설계 
     / 트랜잭션이 활발한 주간에는 대용량 갱신 작업 수행하면 안됨
  4. 대용량 작업이 불가피할 경우
     작업 단위를 쪼개거나  / lock_timeout 설정하여 해당 lock 최대 시간 설정
     

set lock_timeout 3000

- DeadLock

• 트랜잭션간의 교착상태 
• 두 개의 트랜잭션 간에 각각의 트랜잭션이 가지고 있는 리소스 Lock 획득시 , 발생
• 해결책 : 한쪽 트랜잭션 처리를 강제 종료 (비용이 적은 트랜잭션의 처리 강제 종료)

1. 첫번째 deadlock 예시

• 1번 트랜잭션에서 2번 리소스의 잠금 획득 
• 2번 트랜잭션에서 1번 리소스 잠금 획득
  
  -> 이 때, 동시에 상대방 데이터를 액세스하려고 할 때, 기존의 Lock 이 해제될 때 까지 기다리는 상황

2. 두번째 deadlock 예시

• 1번 트랜잭션이 공유 Lock 설정하고 -> Sleep 
• 2번 트랜잭션이 배타적 Lock 설정하려고 할 때, 무기한 기다리게 되는 교착상태에 빠짐

SELECT  *,  DATETIME(datetime, "Asia/Seoul") as KST FROM `bigquery-ex-278307.dummy_test.dummy_ex3` ORDER BY id ASC

Join 과 Union 공통점 : 하나 이상의 테이블의 데이터 결합

Join 과 Union 차이점 : 데이터가 결합되는 방식

ex)

A테이블

col1  |  col2

-----------

A      |  1

B      |  1

C      |  2

C      |  2

D      |  3

B테이블

col1 |   col2

-----------

A     |  가

A     |  나

B     |  나

C     |  나

D     |  다

- Join : 새로운 열로 결합 (수평결합)

   -> 두 개의 테이블 결합 시, 첫 번째 테이블의 데이터는 동일한 행의 두 번째 테이블 열과 함께 한 열 세트에 표시

SELECT A.col1, A.col2, B.col2
FROM A
INNER JOIN B
ON A.col1 = B.col1;

결과)

A.col1    A.col2    B.col2

-----------------------

A          1            가

A          1            나

B          1            나

C          2            나

C          2            나

D          3            다

- Union : 새로운 행으로 결합(수직결합)

  -> 두 개의 테이블 결합 된 경우, 첫번째 테이블의 데이터는 한 행 세트에 있고 / 두 번째 테이블 데이터는 다른 세트에

  -> select 에 컬럼 수는 맞춰서 진행해야 한다.

SELECT col1, col2
FROM A
UNION
SELECT col1, col2
FROM B;

결과)

col1     col2

-----------

A        1

B        1

C        2

D        3 

A       가

A       나

B       나

C       나

D       다