[빅쿼리 UI 기초 사용법] 빅쿼리(BigQuery)에서 퍼블릭 데이터셋(public dataset)으로 쿼리하기

구글 클라우드 콘솔(GCP)에 있는 빅쿼리 UI의 기초적인 사용법을 익혀보겠습니다.  실습에 사용하기 위해 빅쿼리 퍼블릭 데이터셋(public dataset)을 사용할 것입니다.  

• 구글 클라우드 콘솔에서 빅쿼리 페이지로 이동합니다. 

• 연습에 사용할 퍼블릭 데이터셋을 추가합니다.  + ADD DATA 부분을 클릭하고 Explore public datasets를  클릭합니다.

• google analytics sample 이라고 검색하여 선택한 후 VIEW DATASET 을 누릅니다.

• Resources 아래에서 google_analytics_sampe 데이터셋의 ga_sessions_(366)이란 테이블을 선택합니다. Google Analytics(구글 애널리틱스, GA)데이터는 일자별로 테이블을 파티션(partition)해서 저장됩니다.

• 테이블의 스키마(schema)와 상세정보, 그리고 데이터를 살펴봅니다.

• QUERY TABLE을 클릭합니다.

• SELECT FROM 구문이 자동으로 생성됩니다. FROM 뒤에는 `프로젝트id.데이터셋id.테이블명` 형태로 구성되고 ` 은 ' 랑 다릅니다. ( ' 싱글 " 더블 ` 백틱)   `(백틱)은 숫자 1과 !(느낌표)가 있는 키보드 키 바로 앞에 있습니다. ~ 모양과 ` 모양 키입니다.  

• SELECT 뒤에 와일드카드(*) 를 넣어 모든 컬럼(필드)를 다 조회하겠습니다. 빅쿼리 validator가 이 쿼리를 실행하면 15.9MB 만큼의 데이터를 처리한다고 알려줍니다. 실행해봅니다.

• 결과를 보면 한 행의 크기가 무척 큰 것을 알 수 있습니다. GA의 한 세션에 발생한 데이터는 하나의 행(row)에 저장됩니다. 그리고 nested되고 반복되는 복잡한 구조를 가집니다. 그래서 일반 RDBMS와는 많이 다르고, 복잡한 구조를 쿼리하기 위해서 unnest() 함수를 사용할 때가 있습니다.  1. UNNEST() 함수 사용법  //  2. 빅쿼리 array와 struct 타입 이해하기

• 이번에는 *(와일드카드) 대신에 하나의 필드명만 조회하겠습니다. SELECT 뒤에 visitorid를 칩니다. 그리고 validator가 0 B 를 처리한다는 메세지를 확인합니다. 실제로는 0보다는 큽니다.  

** 빅쿼리는 일반 RDBMS와 다른 구조로 데이터를 저장을 합니다. 빅쿼리는 인덱스나 키가 없습니다. 빠른 처리 속도를 위해 정규화를 하지 않고 denormalize(비정규화, 역정규화, 반정규화? )를 합니다. 그래서 빅쿼리로 쿼리할 땐 *(와일드카드)를 쓰면 모든 컬럼을 쿼리할 것이고 이는 많은 요금을 발생시킵니다. 그래서 *(와일드카드) 사용을 권장하지 않습니다. 

• 혹시 대소문자를 비롯해 인덴테이션(indentation)을 예쁘게 하시고 싶으시면 More 버튼을 클릭하고 Format을 선택합니다. 그럼 자동으로 보기 좋게 만들어 줍니다. 

• EXPLORE DATA 를 누르면 데이터 스튜디오(Data Studio)로 쉽게 시각화 해줍니다. (저희가 쿼리한 것은 시각화할만한 데이터가 없습니다. 사용 방법만 기억하세요!) 

• SAVE RESULTS 를 클릭해서 쿼리결과를 저장할 수도 있습니다. csv 형식으로 저장하여 로컬에 다운로드 받습니다. 

• csv 파일을 아래와 같이 볼 수 있습니다. (실습에 사용한 쿼리는 null 값이 많아서 다른 쿼리를 실행하였습니다.) 

• Resources에서 본인의 프로젝트를 클릭하고 Dataset을 하나 만듭니다. 

• 데이터셋에서 CREATE TABLE 을 클릭합니다. 

• 아래와 같이 테이블을 생성합니다. 조금 전에 로컬에 저장했던 CSV 파일을 다시 업로드해서 테이블로 만들고 있습니다. 로컬에 있는 데이터를 빅쿼리에 load(적재)하는 한가지 방법입니다. 

• 아래와 같이 빅쿼리에 잘 적재(load) 되었는지 확인합니다. 

• Save query 버튼을 이용하면 본인의 쿼리를 저장할 수 있습니다. 

• 저장된 쿼리는 아래에서 확인할 수 있습니다. 

• 쿼리 내역은 Query history에서 볼 수 있고 클릭을 하면 자세히 볼 수 있습니다.

• Job history에서 작업한 내용을 볼 수 있고 내역을 클릭하면 자세히 살펴볼 수 있습니다. 

• 새로운 쿼리를 작성하시고 싶으시면 COMPOSE NEW QUERY를 누릅니다. 

• 마지막으로 실습했던 테이블과 데이터셋을 삭제합니다.