빅쿼리(BigQuery)에서 대용량 쿼리 결과 CSV로 다운받는 방법 & 빅쿼리 테이블을 CSV로 로컬에 저장하는 방법

빅쿼리에서 쿼리를 해서 csv 같은 포맷으로 다운로드를 받으려고 하는데 용량이나 행갯수 제약으로 안되는 경우가 있습니다. 혹은 빅쿼리 테이블을 내려받고 싶은데 이런 저런 이유로 안되는 경우가 있습니다. 여러가지 제약사항에 대해 알아보고 해결해보겠습니다.

• 빅쿼리에서 쿼리 결과를 저장하려고 할 때 16,000row(행)을 넘거나 용량이 10MB 이상이면 결과 저장이 안됩니다.  Google Drive에는 1GB까지 저장이 됩니다. 

• 10MB 이상의 쿼리 결과를 다운받아 보면, 10MB 이하로 저장되는 것을 확인할 수 있습니다. 

10메가로 보이지만 아니란다

9메가임!

• 구글 공식 문서에서도 확인 가능합니다. 

** 중요한 점은, 보통 빅쿼리를 사용하는 분들 중에 GA(구글 애널리틱스)와 연동해서 많이 쓰시는데, GA 데이터는 중첩 및 반복, 즉 스트럭트(struct)와 배열(array) 구조로 되어 있기 때문에 csv 형식으로 다운로드 받을 수 없습니다. 이건 여기다 보여 드리려다 다음 포스트에서 다루도록 하겠습니다. 드라마틱한 효과 굿?  

• 해결책은 쿼리 결과를 테이블로 저장하고 그 테이블을 Cloud Storage bucket으로 보낸 후 다운로드 받아야합니다. 근데 막상 빅쿼리 테이블에서 데이터를 내보내기(export) 할때도 또 막히게 됩니다. 단일 파일로 최대 1GB만 보낼 수 있다고 합니다. 이때는 당황하지마시고, 와일드카드를 사용하면 되고 사용법은 아주 쉽습니다. 뒤에서 실제로 보여드리겠습니다.

*** 참고로 많이들 헷갈려하시는 부분인데 테이블 데이터를 로컬 파일등으로 내보낼 수 없고 Cloud Storage로만 보낸다는 부분입니다. 빅쿼리 UI에서 CSV나 구글 드라이브로 내보낸 것은 빅쿼리 테이블 데이터가 아니라 그 테이블 데이터를 쿼리한 결과입니다. '테이블 데이터'와 '쿼리 결과'는 다릅니다. 

*** 빅쿼리 콘솔 UI에서는 쿼리 결과를 로컬 파일로 다운로드가 되지만 bq 커맨드라인이나 빅쿼리 API에서는 지원하지 않습니다. 하지만 UI에서 쿼리한 결과의 용량이 큰 경우 테이블로 저장하고 그 테이블을 Cloud Storage로 보낸 뒤 다운로드해야 하는 것입니다. 헷갈리셔도 괜찮습니다. 실제로 작업을 하면 에러메세지가 친절히 뭐를 잘못하고 있다고 말해주고 그렇게 하나 하나 해결해 나가면 됩니다. 외우실 필요가 전혀 없습니다. 그럼 실제 실습 ㄱㄱ 하겠습니다. 

• 7.95GB의 크기를 가진 flights_copy란 테이블이 있습니다.

• 이 테이블을 쿼리하고 SAVE RESULTS 버튼을 눌러서 로컬에 CSV로 다운로드 받으려고 하면 용량 때문에 안됩니다. "7천만 row 중에서 16000만 저장했다. 결과가 CSV로 저장하기 너무 크다. 테이블로 저장하고 export해라!" 라고 메세지가 뜹니다. (실습이라 * 로 쿼리했습니다. 실무에는 의미 있는 데이터를 도출하는 쿼리를 하십쇼!) 

8기가 데이터 쿼리 후 다운로드 시도

다운로드는 SAVE RESULTS 버튼을 누르고 종류 선택

친절한 메세지찡

• 위의 에러메세지는 뜨지만 다운로드된 파일이 하나 있는데 열어보면 16000행까지만 저장한 CSV 파일이 있습니다. (첫번째 행은 header 니깐 16000개 맞음 따지지 마 ㅇㅋ?) 

• 그럼 에러메세지님 말씀대로 친절하게 쿼리한 내용을 테이블로 저장하면 됩니다. SAVE RESULTS 버튼을 누르면 빅쿼리 테이블로 저장하는 항목이 있습니다.

SAVE RESULTS 버튼 누르고 BigQuery table 선택

쿼리 결과를 저장할 테이블 설정 

• 쿼리한 결과가 내가 만든 테이블에 들어갔는지 확인합니다.

• 더 간단한 방법은 처음에 쿼리하기 전에 결과를 테이블로 저장하게 설정 할 수 있습니다. 

** 이 외에도 bq 커맨드나 API를 사용해서 쿼리 결과를 테이블에 저장할 수 있습니다. 보여드리고 싶지만 자제 중.

• 그럼 이제 GCS (구글 클라우드 스토리지)로 옮겨보겠습니다. EXPORT 버튼 클릭 > Export to GCS 클릭

• GCS 버킷을 선택하고, 내보내기 포맷을 선택합니다. 압축도 가능합니다. 

• 보내려고 하면 용량이 1GB보다 커서 아래와 같은 에러 메세지가 뜹니다. 하지만 친절하게 해결방법을 알려줍니다. 와일드카드를 사용하라고 하네요.

• 와일드 카드 사용 관련 공식 문서 내용입니다. 

• 경로에 와일드카드를 써주고 Export 버튼을 누릅니다.

• Job 하나가 열심히 돌고 있습니다. jin_flight 테이블의 데이터를 jin_pro_test 버킷으로 내보내기(export) 하고 있네요. 열심히 일하는 모습을 보니 흐뭇합니다.

• Job이 완료되고 GCS에 가서 확인해 보면 아래와 같이 export 된 모습을 볼 수 있습니다. 그리고 클릭해서 로컬에 다운로드 받으시면 됩니다. 

• 그러나! UI에서 하면 간지가 안납니다. 클라우드 쉘을 열고 bq 명령를 사용해서 해보겠습니다. 같은 테이블을 클라우드 스토리지에 파일명을 jin-ganji로 나누어서 저장하겠습니다.

• 버킷에 잘 저장되었는지 확인하면 끝입니다. 간지 좔좔?