Big Query_2

Big Query

1. Persistent Storage를 제공하는 Data Warehouse

2. CRUD 기능

   • CREATE
     : [SQL] INSERT & DDL(Data Definition Language)
     : [API] Streaming Insert API

   • READ
     : [SQL] SELECT
     : [API] Bulk Read API

   • UPDATE
     : [SQL] UPDATE / MERGE
     _ DML(Data Manipulation Language) 작업

   • DELTE
     : [SQL] DELETE
     _ DML 작업

[참고사항] #standardsql
Big Query는 과거 표준 SQL이 아닌 Legacy SQL문을 사용했기 때문에 일부 도구 및 사용자 인터페이스의 기본값은 Legacy SQL이다. 따라서 쿼리 첫 행에 #standardsql을 입력하면 수신한 쿼리를 표준 SQL로 처리함

IAM(Identity and Access Management)은 기업에서 오직 적합한 사람과 디바이스만 필요할 때 원하는 애플리케이션과 리소스 및 시스템에 액세스할 수 있도록 허용하는 프레임워크

3. Simple Query

   1) SELECT
   : 테이블에서 지정된 열의 값(행)을 검색할 수 있음
   : SELECT * REPLACE() 를 사용하여 한 컬럼의 값을 변환할 수 있음

   2) WHERE
   : SELECT가 반환한 결과를 필터링
   : WHERE절은 FROM절에 지정한 테이블의 컬럼을 참조하기 때문에 SELECT * AS로 지정한 별칭을 사용할 수 없음
   : NULL값 찾을 때는 IS NOT NULL, IS NULL을 사용. NULL 값에 대한 비교연산(=, !=, <, >)은 NULL값을 반환하기 때문에 사용 X

   3) WITH | SUBQUERY
   : SELECT를 이용하는 방법
   : WITH * AS() 를 이용하는 방법 > Big Query에서 WITH절은 이름이 있는 SUBQUERY처럼 작용하며 임시테이블을 생성하지 않음

FROM 절에서 사용할 수 있는 객체를 from_item이라 일컬음. from_item은 SELECT절에서도 사용 가능. from_item은 테이블은 아니지만테이블처럼 사용 가능

4) ORDER BY
: SELECT절 이후에 실행되기 때문에 별칭 사용 가능

5) GROUP BY

6) HAVING
: GROUP BY 이후에 Filtering 가능
: WHERE은 GROUP BY 결과값으로 Filtering 불가

7) DISTINCT
: SELECT DISTINCT 로 컬럼의 고유값 추출 가능
: DISTINCT는 SELECT에서 지정한 컬럼 전체에 적용됨 > 2 컬럼을 지정했을 시, 2개의 컬럼 DISTINCT 값의 조합이 추출됨

4. ARRAY & STRUCT

1) ARRAY 배열
: ARRAY와 UNNEST를 함께 사용하면 쿼리, 함수 및 데이터 타입을 빠르게 실험할 수 있음

SELECT
	city, SPLIT(city, ' ') AS parts
FROM (
	SELECT * FROM UNNEST([
    'Seattle WA', 'New York', 'Singapore'
    ]) AS city
    )

2) UNION ALL
: 중복값 포함 Table 합치기
: 하드 코딩하여 Table 생성하는 예시

WITH example AS (
	SELECT 'Sat' AS day, 1451 AS numrides, 1018 AS oneways
    UNION ALL SELECT 'Sun', 2376, 900
    UNION ALL SELECT 'Mon', 2001, 897
SELECT * FROM example
WHERE numrides < 2000

3) ARRAY_AGG
: 요소 세트를 배열로 집계 > Row를 Array로 변환
: GROUP BY와 함께 사용되며, GROUP BY된 값들을 ARRAY로 반환함

id	name	number
s_1	js_1	01
s_1	js_2	02

위와 같은 형식을 아래와 같은 형식으로 변환하고자 사용

id	name	number
s_1	js_1, hong_2	01, 02

: Array는 NULL값을 가질 수 있으나, 그 배열을 테이블에 저장할 수는 없음

4) STRUCT
: 순서를 갖는 필드의 그룹
: STRUCT 키워드와 AS * 를 생략하면 Anonymous struct가 생성됨 > 컬럼 및 Struct 필드에 임의의 이름이 할당됨

SELECT 
  [
    STRUCT('male' AS gender, [9306602, 3955871] AS numtrips)
    , STRUCT('female' AS gender, [3236735, 1260893] AS numtrips)
  ] AS bikerides

4) ARRAY 풀기
4-1) 행 1개만을 반환하는 SELECT

SELECT
	[
    STRUCT('male' AS gender, [1,2] AS num),
    STRUCT('female' AS gender, [3,4] AS num)
    ]

index	gender	num
1	male	1
		2
	female	3
		4

UNNEST 진행

SELECT * FROM UNNEST(
[
    STRUCT('male' AS gender, [1,2] AS num),
    STRUCT('female' AS gender, [3,4] AS num)
    ])

index	gender	num
1	male	1
		2
2	female	3
		4

UNNEST는 from_item이기에 SELECT에서 사용 가능

5. JOIN

   JOIN을 하려면 from_item을 작성할 때 사용하는 모든 데이터셋이 동일한 Region에 있어야 함

5-1) INNER JOIN

• 일반 JOIN이라 일컬으면 INNER JOIN을 의미함
• Join 조건을 만족하는 행만 유지됨
  5-2) CROSS JOIN
• 조인 조건 없음. 2개의 from_item의 모든 행이 결합됨
• CROSS JOIN b_ 로도 사용하지만 FROM a_ 와 ,로 묶어서 사용하기도 함
• basic table의 모든 값들이 CROSS JOIN되는 table 조건(?)을 반복하며 얻음

SELECT a_.*, b_.*
FROM a_
CROSS JOIN b_

는 아래와 동일

SELECT a_.*, b_.*
FROM a_, b_

5-3) OUTER JOIN

• FULL OUTER JOIN : Join 조건이 충족되지 않아도 모든 행 유지
• LEFT OUTER JOIN : Left는 모두 유지, 일부 Right Table은 사라짐
• RIGHT OUTER JOIN

• Big Query는 행을 읽는 작업을 여러 워커에 분배, 각 워커는 데이터셋의 서로 다른 샤드에서 데이터를 읽음으로 LIMIT 사용시 매 결과가 다를 수 있음

요금 청구 방법 : 쿼리에서 처리되는 데이터의 양에 따라 요금이 청구됨. 즉, 쿼리에서 읽는 컬럼이 많을 수록 청구되는 요금이 많아짐