Gmail을 SQLite로 저장하기

 (github.com/marcboeker)
2P by GN⁺ | ★ favorite | 댓글 1개
  • Gmail to SQLite는 Gmail 메시지를 로컬 SQLite 데이터베이스로 동기화해 분석과 보관에 활용하는 Python 애플리케이션임
  • 기본 동작은 새 메시지만 내려받는 증분 동기화이며, 전체 동기화 옵션으로 모든 메시지를 내려받고 삭제 여부도 감지할 수 있음
  • 메시지 가져오기는 멀티스레드 병렬 처리를 사용하며, 지수 백오프 기반 자동 재시도와 CTRL+C 처리 같은 오류·종료 대응을 포함함
  • 실행에는 Python 3.8 이상, Gmail API가 활성화된 Google Cloud Project, OAuth 2.0 credentials.json 파일이 필요함
  • 저장된 데이터는 발신자, 수신자, 라벨, 본문, 크기, 읽음 여부, 발신 여부, 삭제 여부 등을 포함해 SQL로 Gmail 사용 패턴을 직접 분석할 수 있음

Gmail 메시지 로컬 동기화 도구

  • Gmail to SQLite는 Gmail 메시지를 로컬 SQLite 데이터베이스에 저장하는 Python 애플리케이션임
  • 목적은 Gmail 데이터를 분석하고 보관할 수 있게 만드는 것임
  • 코드베이스 전반에 타입 힌트를 적용해 타입 안전성을 갖추고 있음

동기화 방식과 안정성

  • 기본 동기화는 증분 동기화로 동작해 새 메시지만 다운로드함
  • --full-sync 옵션을 사용하면 전체 메시지를 동기화하고 Gmail에서 삭제된 메시지를 감지함
  • 메시지 가져오기는 멀티스레드 병렬 처리로 수행해 성능을 높임
  • 오류 처리에는 자동 재시도와 지수 백오프가 포함됨
  • CTRL+C를 누르면 우아한 종료 절차가 실행됨
    • 새 작업 수락을 중단함
    • 실행 중인 작업이 끝나기를 기다림
    • 완료된 작업의 진행 상태를 저장함
    • 정상 종료함
  • CTRL+C를 한 번 더 누르면 즉시 종료함

설치와 준비 조건

  • 실행 환경에는 Python 3.8 이상이 필요함
  • Gmail API가 활성화된 Google Cloud Project가 필요함
  • OAuth 2.0 인증 파일 credentials.json이 프로젝트 루트에 있어야 함
  • 설치 흐름은 저장소를 클론한 뒤 uv sync로 의존성을 설치하는 방식임
  • Gmail API 인증 설정은 Google Cloud Console에서 프로젝트를 만들거나 선택하고, Gmail API를 활성화한 뒤 Desktop application용 OAuth 2.0 자격 증명을 생성해 credentials.json으로 저장함

명령어 사용법

  • 기본 증분 동기화는 다음처럼 실행함
python main.py sync --data-dir ./data

# or: uv run main.py sync --data-dir ./data
  • 전체 동기화와 삭제 감지는 --full-sync를 사용함
python main.py sync --data-dir ./data --full-sync
  • 특정 메시지만 동기화하려면 sync-message와 --message-id를 사용함
python main.py sync-message --data-dir ./data --message-id MESSAGE_ID
  • 삭제된 메시지만 감지하고 표시하려면 sync-deleted-messages를 사용함
python main.py sync-deleted-messages --data-dir ./data
  • 워커 스레드 수는 --workers로 지정할 수 있으며, 기본값은 CPU 코어 수임
python main.py sync --data-dir ./data --workers 8
  • 명령줄 인자는 다음과 같음
    • command: 필수이며 sync, sync-message, sync-deleted-messages 중 하나
    • --data-dir: 필수이며 SQLite 데이터베이스가 저장될 디렉터리
    • --full-sync: 선택 사항이며 전체 동기화를 강제함
    • --message-id: sync-message에서 필수이며 동기화할 특정 메시지 ID
    • --workers: 선택 사항이며 워커 스레드 수
    • --help: 명령과 옵션 도움말 표시

SQLite 스키마와 분석 예시

  • 생성되는 SQLite 데이터베이스의 messages 테이블은 Gmail 메시지 분석에 필요한 필드를 포함함
    • message_id: 고유 Gmail 메시지 ID
    • thread_id: Gmail 스레드 ID
    • sender: 이름과 이메일을 담은 JSON 발신자 정보
    • recipients: to, cc, bcc 유형별 수신자 JSON
    • labels: Gmail 라벨 배열
    • subject: 메시지 제목
    • body: 일반 텍스트 메시지 본문
    • size: 바이트 단위 메시지 크기
    • timestamp: 메시지 시각
    • is_read: 읽음 상태
    • is_outgoing: 사용자가 보낸 메시지 여부
    • is_deleted: Gmail에서 삭제된 메시지 여부
    • last_indexed: 마지막 동기화 시각
  • 발신자별 이메일 수를 집계할 수 있음
SELECT sender->>'$.email', COUNT(*) AS count
FROM messages
GROUP BY sender->>'$.email'
ORDER BY count DESC
  • 읽지 않은 이메일을 발신자별로 집계해 흥미 없는 이메일을 많이 보내는 발신자를 확인할 수 있음
SELECT sender->>'$.email', COUNT(*) AS count
FROM messages
WHERE is_read = 0
GROUP BY sender->>'$.email'
ORDER BY count DESC
  • strftime을 사용해 연도, 월, 일, 요일, 시간 단위로 이메일 수를 집계할 수 있음
SELECT strftime('%Y', timestamp) AS period, COUNT(*) AS count
FROM messages
GROUP BY period
ORDER BY count DESC
  • 본문에 newsletter 또는 unsubscribe가 포함된 메일을 찾아 뉴스레터를 발신자별로 묶을 수 있음
SELECT sender->>'$.email', COUNT(*) AS count
FROM messages
WHERE body LIKE '%newsletter%' OR body LIKE '%unsubscribe%'
GROUP BY sender->>'$.email'
ORDER BY count DESC
  • 발신자별 총 메일 크기와 큰 이메일 발신자를 MB 단위로 확인할 수 있음
SELECT sender->>'$.email', sum(size)/1024/1024 AS size
FROM messages
GROUP BY sender->>'$.email'
ORDER BY size DESC
  • 자신에게 보낸 메일 수를 recipients JSON과 sender 이메일 조건으로 계산할 수 있음
SELECT count(*)
FROM messages
WHERE EXISTS (
  SELECT 1
  FROM json_each(messages.recipients->'$.to')
  WHERE json_extract(value, '$.email') = 'foo@example.com'
)
AND sender->>'$.email' = 'foo@example.com'
  • 수신한 메일 중 발신자별 총 용량이 큰 순서를 확인할 수 있음
SELECT sender->>'$.email', sum(size)/1024/1024 as total_size
FROM messages
WHERE is_outgoing=false
GROUP BY sender->>'$.email'
ORDER BY total_size DESC
  • 삭제된 메시지는 is_deleted=1 조건으로 조회함
SELECT message_id, subject, timestamp
FROM messages
WHERE is_deleted=1
ORDER BY timestamp DESC

함께 보면 좋은 글 β

GN⁺   [-]
Hacker News 의견들

  • 스키마에서 특정 헤더를 따로 빼낸 이유가 궁금함. recipients, subject, sender를 JSON 필드로 둘 수도 있지만, headers 하나에 전부 넣고 메시지의 나머지 헤더까지 담을 수도 있음
    성능 때문이라면 headers를 단일 JSON blob으로 두고 필요한 필드는 생성 컬럼으로 만들면 됨. 예를 들어 subject는 json_extract("headers", '$.Subject')로 만들고 인덱스를 걸 수 있음
    이 모델은 사용자가 자기 쿼리에 필요한 인덱싱된 생성 컬럼을 ALTER TABLE로 추가할 수 있어서 강력했음. DKIM 상태도 "Dkim-Signature"를 뽑아 컬럼과 인덱스를 만든 뒤 GROUP BY하면 됨

    • 실제로는 생성 컬럼도 필요 없고, SQLite는 표현식 인덱스를 지원함. 예를 들어 CREATE INDEX subjectidx ON messages(json_extract(headers, '$.Subject'))처럼 만들면 해당 표현식을 참조하는 곳에서 인덱스를 사용함
      이런 식으로 인덱스를 만든 뒤, 메인 테이블을 ALTER해서 생성 컬럼을 추가하기보다 해당 표현식을 쓰는 VIEW를 만드는 편이 유용했음
    • 일회성 쿼리를 위해 인덱스를 추가하는 건 나쁜 습관처럼 보임
      보통 꾸준히 쓰게 될 컬럼은 따로 빼는 편을 선호함. 이메일 헤더처럼 안정적인 대상이라면 더 그렇고, headers 컬럼이 스키마 변경은 조금 쉽게 만들 수 있어도 쓰기 시점의 고통을 읽기 시점의 고통으로 바꾸는 셈이며 조용히 실패할 여지도 남김
    • PostgreSQL로 시스템을 키워갈 때 비슷한 패턴을 자주 씀. 처음엔 필요하다고 아는 필드를 생각해 테이블을 만들고, 남는 메타데이터는 JSON 컬럼에 넣어 둠
      두 달쯤 지나 실제로 필요한 필드가 보이면 JSON에서 채워 넣고, API가 계속 최신으로 유지하게 하거나 뷰를 만들거나 함. “그냥 전부 MongoDB에 넣자”나 “그냥 파일 시스템에 두자”에서 오는 성장통을 피하는 데 꽤 도움이 됐고 비용도 크지 않았음
    • dkim 컬럼을 NOT NULL로 정의했는데, 이메일 메시지에 Dkim-Signature 헤더가 없으면 어떻게 되는지 궁금함

  • 몇 년 전에 Gmail 같은 대량 이메일 시각화 도구를 만들었음: https://github.com/terhechte/postsack

    • 꽤 멋짐. 디스크 사용량 시각화 도구와 비슷하지만, 디스크 사용량보다는 메일의 총량에 초점을 맞춘 것 같음
      크기 옵션도 있는지 궁금함. 어떤 발신자가 내 저장공간을 가장 많이 쓰는지 보고 싶음. 그리고 웹사이트 SSL 인증서가 만료돼 있음
    • 흥미로워 보임. README의 gmvault 링크는 이제 죽었는데, 이게 맞는지 궁금함: https://github.com/gaubert/gmvault
    • 흥미로워 보임. 예전에 qdirstat로 비슷한 걸 직접 해봤지만, 날짜 폴더처럼 이메일을 특정 방식으로 배치해야 하고 다른 기준으로 다시 자르기 어려웠음
      반면 qdirstat 캐시 파일은 만들기 쉬워서 파일처럼 생긴 여러 대상을 시각화하는 데 쓸 수 있음

  • 이제는 앱 전용 비밀번호로도 로그인할 수 없고, OAuth 클라이언트를 만들고 OAuth 흐름을 거쳐야 한다는 점이 정말 아쉬움. 내 이메일인데도 Google이 내가 접근하기 위한 공개 표준을 빼앗아 감

    • 무료 Gmail 주소로 받는 스팸의 양과, Gmail 서버에서 비-Gmail 계정으로 오는 스팸의 양을 보면 점점 탈Google 쪽으로 마음이 기움
      특히 내 프리랜서 이메일이 수신자 시스템에서 스팸으로 들어간다는 정보를 점점 더 많이 받음. 다만 Google 생태계에 익숙해진 습관을 어떻게 버릴지는 막막함
    • 왜 앱 전용 비밀번호는 공개 표준이고 OAuth는 아니라고 보는지 궁금함
    • 앱 비밀번호를 쓰면 IMAP 전체 접근이 가능하다는 점에서 무슨 말을 하려는지 잘 모르겠음

  • 최근 내 앱 https://github.com/rumca-js/Django-link-archive에 Gmail을 통합하려고 했는데 시간을 너무 많이 썼고, Gmail 지원은 할 가치가 없다고 판단함
    Gmail to SQLite는 자격 증명 설정을 6단계로 설명하지만, 내 경우엔 그렇지 않았음. 6단계 뒤에도 Google은 앱이 게시되지 않았다고 해서 게시해야 했고, Workspace 사용자가 아니라 내부 앱으로 둘 수 없다고 했고, 외부 앱으로 바꾸자 검증 전에는 쓸 수 없다고 했음
    검증 과정에서는 도메인, 주소, 기타 세부 정보, 범위에 대한 정당화, 앱 사용 방식을 설명하는 동영상까지 요구했고, 제출한 데이터를 검증하는 데 시간이 걸린다고 했음. 전체가 설정 미로 같고, 사용자가 Google이 요구하는 장애물을 넘게 만드는 건 너무 과함

    • Google이 API 키 하나 받는 데도 사람들에게 시키는 절차는 완전히 말도 안 됨. 왜 이렇게까지 나쁜지 아는 사람이 있는지 궁금함
    • 그냥 예전 방식의 IMAP과 앱 비밀번호를 쓰면 됨. Google의 장애물에 맞춰 뛰어넘을 필요 없음

  • 지금 존재하는 최고의 오픈소스 Gmail 백업 소프트웨어가 뭔지 궁금함. 첨부파일 보관까지 포함해서 이런 걸 구성해 본 사람이 있는지 궁금함

    • https://github.com/GAM-team/got-your-back가 있음. 오픈소스이고, 재개 기능이 있어서 백업과 복원이 결국 완료됨
      참고로 https://www.mailstore.com/en/products/mailstore-home/도 있음. 오픈소스는 아니지만 인덱스가 있는 GUI라 로컬 메일 검색에 좋고, 재개는 백업에만 돼서 대용량 복원은 보통 실패함
    • 정확히 원하는 답은 아닐 수 있지만, Google에는 Takeout이라는 서비스가 있어서 Gmail을 포함한 모든 Google 서비스 데이터의 백업을 요청하고 내려받을 수 있음
      몇 달마다 이걸 실행하라고 알림을 걸어 두고 로컬 백업을 갱신함. 기억으로는 gzip 압축된 mbox 파일로 내려옴
    • IMAP 클라이언트를 쓰고 오프라인/다운로드 모드로 설정하면 전체를 내려받아 로컬에 저장할 수도 있음. Evolution에서는 “오프라인 모드”라고 부르는 것 같은데 Thunderbird나 다른 클라이언트에서는 이름이 다를 수 있음

  • 이건 “Gmail to SQLite”가 아니라 “IMAP to SQLite” 같은 이름이어야 하지 않나 싶음. 왜 특정 이메일 제공자 하나에 묶는지 모르겠음

    • 실제로 Gmail 전용이기 때문임. OAuth와 아마 API 접근을 사용하고 있음
      IMAP은 훨씬 어렵고 훨씬 느리며, Google의 대역폭 제한에도 묶임
    • 몇 년 동안 IMAP으로 Gmail 계정을 백업하려고 했지만, Gmail 전용 도구를 포함해 한 번도 성공하지 못했음. 제일 나은 동기화 도구도 한 달 동안 돌다가 특정 메일을 가져오지 못하는 지점에 걸렸음
      너무 차가운 저장소에 있어서 시간 초과가 난 건지 모르겠음. 그래서 Google의 독점 API를 쓰는 쪽이 더 잘 작동할 수 있다는 건 이해함
      요즘은 Google Takeout에 mbox가 포함되고 제대로 작동하며 꽤 빠르지만, 지속적인 업데이트는 안 됨. 결국 다른 메일 제공자인 Infomaniak으로 옮겼고, 예전에 내 메일 도메인을 써 둔 나 자신에게 감사했음

  • 전체 텍스트 검색도 켤 수 있으면 좋겠음

    • 검색 회사가 운영하는 것치고 Gmail의 전체 텍스트 검색이 놀랄 만큼 별로라고 느낌

  • 어제 나도 같은 걸 만들었음. 수신자 이메일을 도메인별로 나열하고 싶었기 때문임. 코드는 엉망이지만 여기 있음: https://github.com/hugoferreira/gmail-sqlite-db

  • PostgreSQL 기반 IMAP 서버 Archiveopteryx가 조금 떠오름: https://github.com/aox/aox
    AOX의 스키마는 늘 좋아 보였지만 실제로 제대로 써보지는 못했음. 주 용도는 데일리 드라이버 IMAP 서버가 아니라 메일 분석과 검색이었음

    • Manitou-Mail도 떠오름. 데일리 드라이버로 쓸 수 있는 강력한 PostgreSQL 기반 전용 메일 클라이언트이고 꽤 견고함: https://www.manitou-mail.org/

  • 여기서 대역폭 비용은 어떻게 되는지 궁금함. 40GB 이상 Gmail 계정을 가진 입장에서 이 도구로 전송하면 요금이 청구되는지 알고 싶음
    고치기는 쉬움. Google Takeout이 무료인 것 같으니 먼저 내려받고 파일을 파싱하면 됨. 그래도 바로 시작한다는 관점에서는 이 도구가 더 빠를 듯함

답변달기