데이터 저장에 구글 스프레드시트와 API 활용하기 with Python

 현재 모 스타트업 기업에서 인턴을 하고 있다. 이곳에서, 기업들의 각종 데이터를 지정해둔 사이트들에서 크롤링해서 사용자에게 보여 주는 웹 서비스를 제작하고 있다. 중요한 건, 크롤링에 필요한 정보들이 수시로 변해서, 이 정보를 유동적으로 수정하고, 삭제하고, 지정할 수 있어야 했다. 처음에는 데이터베이스를 통해 이 정보를 취급하려고 했었다.

 하지만, 이렇게 하기엔 문제가 있었다.

 

 

1. 이 서비스가 운영될 환경엔 전문 개발자가 없다. 따라서 DB에 대한 지식이 없는 관리자가 쉽게 데이터에 접근하고 수정할 수 있어야 하고, 그런 의미에서 DBMS를 통해 접속해서 데이터를 관리하는 건 부적절하다.

 

2. 그럼, 미리 데이터에 대한 질의나 수정 등 가능한 명령사항들을 래핑해서 일반적인 관리자들(DB에 대한 지식이 없는)이 이용 가능한 서비스로 만들 수도 있다. 하지만, 이 방법은 서비스의 규모나 가용 자원에 비해 너무 큰 작업이라고 생각했다.

 

 

 이러한 사유로 DB를 사용하는 건 포기했다. 대신, 나는 다른 점을 주목했다.

 

1. 여러 가지 정보를 크롤링하는데, 그 중 가장 자주 일어나는 크롤링도 겨우 일 1회이다. 데이터 특성상 실시간으로 모을 필요는 없었다.

 

2. 기업의 정보는 생각보다 자주 바뀐다. 따라서 수정 작업이 빈번할 것이다. 또한, 단순히 크롤링을 위한 URL 데이터 뿐만 아니라, 기업에 대한 다른 데이터들도 저장하고 있었고, 이 데이터들은 정해진 형식이 있는 경우가 많다.

 

 

 

 그래서, 나는 마땅한 대안을 찾다가 구글 스프레드시트를 주목했고, 읽기/쓰기 관련 API도 있다는 걸 알게 되고 데이터 저장을 위해 구글 스프레드시트를 사용하기로 결정했다. 

 

 

 혹시 오해할까봐 미리 적자면, '크롤링 할 대상에 대한 정보'를 구글 스프레드시트에 저장하는 것이고, '크롤링 한 결과'는 최소 수만 단위니까 당연히 DB에 저장했다. 구글 스프레드시트 입출력이 당연히 DBMS보다는 CRUD 관련 성능이 좋지 않다. 당장 사내에서 사용하는 시트 15개 X 시트당 약 5천행씩 존재하는 스프레드시트도 열 때마다 로딩을 몇초씩 기다려야 한다. 그러니까, 아래 방법은 데이터가 수백~수천 개 단위로, 비교적 크지 않을 떄만 활용할 수 있는 방법이다.

 

 

 

1. 스프레드시트 준비

 

 이 부분은 따로 설명이 필요 없을 것 같다. 다만, 공개 데이터가 아니니까 당연히 비공개 스프레드시트로 하고 권한도 적절히 설정한다. 이 부분이 스프레드시트를 사용할 때 큰 장점중 하나라고 생각한다. 사용자에게 수정/열람/공개 권한 등을 손쉽게 설정할 수 있다. 

 

 

2. API 사용 준비

 

 

 일단 결론부터 미리 설명하자면, 이런식으로 스프레드시트 공유자에 봇이 추가되도록 하는 게 목표다. 그러려면 우선 구글 API를 사용할 수 있도록 준비해야 하는데, https://console.developers.google.com/apis에 들어간다.

 

 

 

 나는 현재 프로젝트가 하나 있기에, 지금 보이는 화면과 조금 다를텐데, 맨 위의 google APIs 옆에 아마 '프로젝트 없음'으로 뜰 것이다. 그걸 누르면 사진과 같이 프로젝트 선택이 뜨는데, 나는 만들어 뒀던 게 있어서 기존 프로젝트가 뜨지만, 아마 아무 것도 없을 것이다. 이제 새 프로젝트를 눌러서 프로젝트를 만들면 된다. 이 이후엔 읽어보고 특별한 사항이 없다 싶으면 예스맨 하면 된다.

 

 

 

 

 

 이제 프로젝트가 만들어졌고, 구글 스프레드시트 API를 사용하도록 설정해야 한다. 하라는 대로 API 및 서비스 사용 설정을 누르자. 그러면 크롬 웹스토어랑 비슷한 화면이 뜰 텐데 검색창에 sheet만 쳐도 아래와 같은 API가 나올 것이다.

 

 

 

 

 

 사용 설정 버튼을 누르면 된다.

 

 

 그러면 이 화면이 뜰 텐데, 아직 이 상태론 사용할 수 없다. 내가 누구인지 확인하기 위해 사용자 인증 정보를 만들어야 한다. 친절하게 만들라고 위에 버튼까지 띄워줬으니 만들러 가자.

 

 

 

 

다음과 같은 화면이 뜰 텐데, 내가 썼던 방법을 소개하자면 나머지는 무시하고 위에서 '서비스 계정' 링크를 클릭한다.

 

 

 

 

서비스 계정이 당연히 없을 것이다. '서비스 계정 만들기'를 클릭한다.

 

 

 

원하는 대로 서비스 계정 이름과 설명을 넣는다. ID는 자동으로 만들어진다.

 

 

 

 

계정 권한은 다음과 같이 준다. '프로젝트-편집자'를 택했다. 사실 이 부분이 효과가 있는지는 모르겠는데, 혹시 몰라서 일단 줬다. 읽기 권한만 필요하면 뷰어만 줘도 될 것이다. (요 부분은 정확히 아시는분이 있으면 댓글 부탁드리겠습니다..)

 

 

 

 마지막으로 밑의 '키 만들기'를 누른다. JSON 형식으로 저장하면 된다. 이 키를 이용하여, 스프레드시트에 엑세스 할 것이다. 매우 중요한 파일이고 복구가 되지 않으므로 분실하지 않도록 함은 물론이고, 유출되지도 않아야 한다. 그래서 git을 사용해 관리할 땐, private 레포가 아니라면 꼭 이 파일은 ignore 해주자. 처음 생성하면 이름이 매우 길고 쓰기 힘드므로 적당히 UserKey.json 정도의 이름으로 바꿔주자.

 

 

 마지막으로, 스프레드시트에 해당 서비스 계정의 이메일을 스프레드시트에 공유 설정하자. 권한은 해당 봇이 사용할 만큼 주면 된다. 해당 이메일은 서비스 계정 탭에서도 볼 수 있고, 잘 모르겠다면 방금 받은 json 파일의 'client-email' 항목에 있다.

 

 

 

 이제 사용 준비가 완료되었다.

 

import gspread, os
from oauth2client.service_account import ServiceAccountCredentials

# 구글 API 사용을 위한 상수들
scope = ['https://spreadsheets.google.com/feeds',
         'https://www.googleapis.com/auth/drive']
key_file_name = 'UserKey.json' # 아까 받은 json 인증키 파일 경로가 들어가면 됨.

credentials = ServiceAccountCredentials.from_json_keyfile_name(key_file_name, scope)
spreadsheet = gspread.authorize(credentials).open("<스프레드시트 이름>")

pip install gspread, oauth2client 를 통해 필요한 라이브러리를 설치한다.

이제 spreadsheet를 통해 원하는 대로 조작하면 된다.

 

 

3. API 활용하기

 

 스프레드시트 API에 대해서는 구글 API 문서나 다른 블로거분들의 훌륭한 글이 많아서 따로 언급하진 않지만, 사용에 주의사항이 있다.

 

 

 구글 Sheet API 무료 이용 한도이다. 기본적으로는 무제한이지만, 개인당 매 100초당 100회, 즉 초당 1개씩꼴의 요청만 보낼 수 있다. 즉, loop문 속에서 셀 하나씩 가져오는 식으로 코딩하면 바로 한도에 걸린다. 따라서, 다음과 같은 식으로 코딩해야 한다.

 

    worksheet = spreadsheet.worksheet('<해당 시트 이름>')
    
    product_url_list = worksheet.col_values(3)
    product_code_list = worksheet.col_values(4)

    product_url_list = product_url_list[2:-1] # 필요한 만큼 가공
    product_code_list = product_code_list[2:-1] # 필요한 만큼 가공

    return list(zip(product_url_list, product_code_list))

 

 

 내가 사용했던 시트의 경우 단순화하면 이런 내용이었다.

데이터 목록
코드	바코드
81048	810483
82069	820697
...	...
합계 데이터 행

 나는 코드와 바코드 데이터만 필요하고, 데이터 목록이나 합계 같은건 필요없었다.  col_values(n) 같은 경우엔 해당하는 n열의 데이터를 모두 가져온다. 따라서 요청 한번으로 해당 열의 정보를 모두 가져오고, slicing을 통해 필요없는 데이터를 쳐냈다. 위의 경우엔 앞의 2행과 마지막 1행이 필요가 없으니 [2:-1] 을 통해 간단하게 가공했다. 지금 같은 경우는 간단한 경우고, 복잡한 처리가 필요할 땐 slicing하는 부분에 함수를 각각 따로 만들어서 처리하는 부분을 만들면 된다. 마지막엔 열 단위로 나눈 데이터를 다시 묶었다. 위의 경우엔 [('81048', '810483'), ('82069','820697'), ...] 과 같은 식으로 반환될 것이다.

 

 

 

4. 스프레드시트 사용의 장점 및 단점

 

 위에서도 언급했다시피 데이터 수정과 열람, 삭제가 자유로움은 물론, 데이터 형식을 강제하여 규칙성을 유지할 수 있고, 필요한 최소한의 열만을 입력할 수 있다. 한 가지 내가 겪은 사례를 들자면, 나는 어떤 열에 숫자가 들어가되, 무조건 6자리로 들어가도록 해야 한다. 13이 들어가도 000013 이런식으로 들어가야 한다. 이것을 스프레드시트에서 서식 -> 숫자 -> 추가 형식에서 직접 지정을 했다.

위와 같이 지정하면 그냥 13을 입력해도 000013 과 같은 식으로 자동으로 0을 채워준다.

 

 

 엑셀 함수 역시 활용할 수 있는데, 예를 들어 이 함수를 통해 해당 셀을 참고하여, 옆 셀이 비었으면 '비상장', 빈 셀이 아니라면 '상장사'를 자동으로 입력한다. 이런 식으로 함수를 활용하면 사용자가 직접 타이핑할 데이터를 줄일 수 있다. 사용자의 부담이 줄어듬은 물론이고, 예기치 못한 오타 역시 방지할 수 있다. 이외에도 특정 열에 중복을 허용하지 않도록 함으로서, 기본 키의 역할을 하도록 할 수도 있다.

 

 

 

 다만, 이러한 스프레드시트를 통한 DB의 대체는, 데이터 읽기가 겨우 일 1회만 일어날 정도로 드물고, 데이터의 양이 수천 행 정도로 비교적 적었기에 가능했다. 실제로 데이터의 실시간 반영에 대해서도 많이 고민했는데, 결국 스프레드시트의 한계를 인정하고, 새벽 시간에만 데이터를 한꺼번에 받아와서 읽도록 했다. 

 

 

 최종적으로 완성한 시스템의 구조는, 스프레드시트의 데이터를 새벽 2시에 한꺼번에 읽어와서 서버의 DB에 기존 테이블을 삭제한 후 새로 받은 데이터를 넣고(이 과정이 행 500개 기준으로 10초 조금 안되게 걸린다.), 그 데이터를 기반으로 크롤링을 하도록 했다. 기존 테이블을 삭제하는 이유는, 엑셀에서 어떤 데이터가 변경되거나 삭제되었는지 아는 방법이 까다롭고, 매 행을 대조하는 식으로 억지로 구현할 바에는 그냥 매번 데이터를 새로 받아 쓰는 게 차라리 더 편했기 때문이다. 큰 데이터에 적합하지 않은 이유는 이러한 까닭도 있다.

 

 

 마지막으로, 사용자가 엑셀에 대해 잘 모르면 효율이 떨어질 것이다. 함수나 서식 지정 같은 요소를 모르면 위처럼 데이터의 형식성을 강제할 수 없고, 결국 타이핑하는 사람에게 맡겨야 할 수밖에 없으니..

 

 

5. 결론

 

 사실 처음 이 프로젝트에 대해 들었을 땐 이런 방법을 쓰리라곤 상상도 못했다. 사실 이 방법이 다루는 데이터가 많아야 수만~수십만 개 수준인 소규모 프로젝트라 가능했겠지만, 그래도 꽤나 재미있는 발상으로 극복해 낸 것 같다. 그래도 스프레드시트와 DB를 동시에 사용하면서, DB의 장점과 단점을 동시에 경험해 볼 수 있는 기회였다.