(GCP) BigQuery 에 대해서

 

BigQuery란?

BigQuery 는 확장성이 뛰어난 구글의 기업용 서버리스 기반의 데이터 웨어하우스 입니다. 관리할 인프라가 없기 때문에 데이터 분석에 집중할 수 있으며, 인프라 및 데이터를 관리할 관리자도 필요하지 않습니다.

 

BigQuery는 ANSI:2011을 준수하는 표준 SQL을 지원하기 때문에 기존에 SQL을 알고 있는 사용자도 손쉽게 이용할 수 있는 장점이 있으며, ODBC 및 JDBC 드라이버를 제공하여 데이터를 쉽고 빠르게 통합할 수 있습니다. BigQuery는 몇 초 만에 기가바이트급에서 페타바이트급에 이르는 데이터를 대상으로 초고속으로 SQL쿼리를 실행할 수 있습니다. 매월 무료로 최대 1TB 상당의 데이터를 분석하고 10GB의 데이터를 저장할 수 있습니다. 

 

BigQuery는 스토리지와 컴퓨팅이 분리되어 있기 때문에 데이터 웨어하우스의 용량을 원하는대로 계획할 수 있는 탄력적인 확장성을 가집니다. 자동 확장과 고성능 스트리밍 수집방식을 지원해서 실시간 분석의 어려움도 간편하게 해결할 수 있습니다. 또한 BigQuery는 내부적으로 관리형 열형식 스토리지, 대량 동시실행, 자동 성능 최적화 기능을 제공하고 있어서 데이터 크기에 관계없는 클라우드 데이터 레이크를 구축하고 동시에 빠르게 분석할 수 있습니다. 

 

또 Google Cloud Storage와 구글시트, 구글 드라이브 등으로 손쉽게 데이터를 읽을 수 있으며 인포매티카(Infomatica)와 탈랜드(Talend) 같은 기존 ETL 도구와의 연동도 지원합니다. 아울러 태블로(Tableau), 마이크로스트레티지(MicroStrategy), 루커(Looker), 데이터 스튜디오(Data Studio)와 같은 BI 도구와 자체적으로 BI Engine을 지원하여 누구나 손쉽게 보고서와 대시보드를 만들 수 있습니다. 

 

BigQuery는 모든 배치와 스트리밍 데이터를 분석할 수 있으며, 강력한 스트리밍 수집 기능은 실시간으로 데이터를 캡쳐하고 분석해, 통계를 항상 최신 상태로 유지합니다. 이제 데이터세트, 쿼리, 스트레드시트, 보고서로 조직 안팎에서 유용한 정보를 안전하게 공유하는 것이 가능합니다. 

 

또한 최근 릴리즈된 BigQuery ML을 이용하면 SQL 쿼리를 통해 ML 모델을 학습시키는 것이 가능하며, 클라우드 ML 엔진(Cloud ML Engine)및 텐서플로(Tensorflow) 와도 통합이 가능합니다. BigQuery GIS를 이용하면 일반적으로 GIS 함수에 대한 SQL 지원을 BigQuery내에서 이용할 수 있습니다. 

 

BigQuery 구조

• Project : 가장 큰 개념으로, 프로젝트에는 결제 및 승인된 사용자에 대한 정보가 저장되며 각 프로젝트에는 이름과 고유 ID가 있습니다. 하나의 프로젝트에는 여러개의 데이터세트(DataSet)가 들어갈 수 있습니다. 
• DataSet : RDB의 DataBase와 같은 개념으로 DataSet는 특정 프로젝트에 포함되며, 테이블과 뷰에 대한 액세스를 구성하고 제어하는 데 사용합니다. 하나의 DataSet에는 여러개의 Table을 가질 수 있습니다. 
• Table : RDB의 Table과 같은 개념으로 행으로 구성된 개별 레코드가 포함됩니다. 각 레코드는 컬럼으로 구성되며, 모든 테이블은 컬럼명, 데이터 유형, 기타 정보를 설명하는 스키마로 정의됩니다. BigQuery에서 지원되는 테이블 유형은 다음과 같습니다.
• 기본 테이블 : 기본 BigQuery Repository에서 지원되는 테이블 입니다.
• 외부 테이블 : BigQuery 외부 Repository에서 지원되는 테이블 입니다.
• 뷰 : SQL쿼리로 정의된 가상 테이블 입니다.
• JOB : 쿼리, 데이터 로딩, 생성, 삭제 등 작업에 대한 단위입니다. 

BigQuery SQL

BigQuery의 SQL은 크게 표준 SQL과 이전 SQL 2가지 종류로 나뉩니다. 표준 SQL은 BigQuery 2.0부터 지원하기 시작했습니다. 표준 SQL은 SQL 2011표준을 준수하며, 중첩 및 반복 데이터 쿼리를 지원합니다. 현재는 기본 SQL로 표준 SQL이 설정되어 있습니다. 

 

표준 SQL VS 이전 SQL

Standard SQL	Legacy SQL
'프로젝트명.데이터세트.테이블명' ex) select * from 'my-project.my-dataset.my-table'	[프로젝트명:데이터세트.테이블명] ex) select * from [my-project:my-dataset.my-table]
'with절' 사용 가능
DML (Insert, Update, Delete)
Array 및 Struct 데이터 타입 사용가능
더 엄격한 TIMESTMP 값의 범위
모든 위치에서 Sub Query 지원

 

관련링크 : https://cloud.google.com/bigquery/docs/reference/standard-sql/migrating-from-legacy-sql

Migrating to GoogleSQL  |  BigQuery  |  Google Cloud

 

표준 SQL 데이터 타입

타입	데이터 타입	설명
숫자	INT64	정수
	NUMERIC	좀더 정밀한 숫자
	FLOAT64	부동 소수점, 배정밀도 십진수 값
부울유형	BOOL	TURE/FALSE
	STRING	Unicode 데이터
	BYTES	가변 길이 바이너리 데이터
	DATE	날짜
	DATETIME	날짜 + 시간
	TIME	시간
	TIMESTAMP	ms 단위의 절대 시점 값
	ARRAY	동일한 자료형의 리스트
	STRUCT	서로 다른 자료형의 리스트

 

관련링크 : https://cloud.google.com/bigquery/docs/reference/standard-sql/data-types

Data types  |  BigQuery  |  Google Cloud

 

표준 SQL 쿼리

• SELECT * FROM 'my-project.my_dataset.product';
• 별칭을 주고 출력 : SELECT HELLO.* FROM 'my-project.my_dataset.product' AS HELLO;
• 특정 컬럼명을 제외하고 출력 : SELECT * EXCEPT (PRODUCT_ID) FROM 'my-project.my_dataset.product';
• 특정 컬럼의 데이터를 변경1 : SELECT * REPLACE (PRICE * 0.8 AS PRICE) FROM 'my-project.my_dataset.product';
• 특정 컬럼의 데이터를 변경2 : SELECT * REPLACE ('SOLD OUT' AS PRODUCT) FROM 'my-project.my_dataset.product';
• 조건문 : SELECT * FROM 'my-project.my_dataset.product' WHERE PRICE >= 5;
• GROUP BY : SELECT PRODUCT FROM 'my-project.my_dataset.product' GROUP BY PRODUCT;
• ORDER BY : SELECT * FROM 'my-project.my_dataset.product' ORDER BY PRICE;
• 서브쿼리 : SELECT TEMP.* FROM ( SELECT * FROM 'my-project.my_dataset.product' ) AS TEMP;
• CROSS JOIN : SELECT * FROM 'my-project.my_dataset.product' CROSS JOIN 'my-project.my_dataset.access_log';
• INNER JOIN : SELECT product.product_id, product, price, producer, unit_cost FROM 'my-project.my_dataset.product' AS product JOIN 'my-project.my_dataset.product' AS producer ON product.product_id = producer.product_id SELECT * FROM 'my-project.my_dataset.product' AS product;
• LEFT JOIN, RIGHT JOIN 도 기존 SQL 형태와 동일
• FULL JOIN(OUTER JOIN) : SELECT product.product_id, product, price, producer, unit_cost FROM 'my-project.my_dataset.product' AS product FULL JOIN 'my-project.my_dataset.producer' AS producer ON product.product_id = producer.product_id SELECT * FROM 'my-project.my_dataset.product' AS product FULL JOIN 'my-project.my_dataset.producer' AS producer USING (product_id);

관련링크 : https://cloud.google.com/bigquery/docs/reference/standard-sql/query-syntax

Query syntax  |  BigQuery  |  Google Cloud

 

표준 SQL 함수

관련링크 : https://cloud.google.com/bigquery/docs/reference/standard-sql/query-syntax