GmailをSQLiteに保存する

 (github.com/marcboeker)
2 ポイント 投稿者 GN⁺ 2025-05-11 | 1件のコメント | WhatsAppで共有
  • 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

関連記事

1件のコメント

 
GN⁺ 2025-05-11
Hacker News のコメント

  • スキーマで特定のヘッダーを別に切り出した理由が気になる。recipients、subject、senderを JSON フィールドにすることもできるが、headers 1つに全部入れて、メッセージの残りのヘッダーまで含めることもできる
    性能のためなら、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 カラムに入れておく
      2か月ほど経って実際に必要なフィールドが見えてきたら、JSON から埋め込み、API が常に最新に保つようにしたり、ビューを作ったりする。「とりあえず全部 MongoDB に入れよう」や「とりあえずファイルシステムに置こう」から来る成長痛を避けるのにかなり役立ったし、コストも大きくなかった
    • dkim カラムを NOT NULL と定義しているが、メールメッセージに Dkim-Signature ヘッダーがなかったらどうなるのか気になる

  • 数年前に Gmail のような大量メール可視化ツールを作った: https://github.com/terhechte/postsack

    • かなり良い。ディスク使用量の可視化ツールに似ているが、ディスク使用量よりもメールの総量に焦点を当てているようだ
      サイズのオプションもあるのか気になる。どの送信者が自分のストレージを最も多く使っているのか見たい。それと、Web サイトの 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 キーを1つ取得するだけでも人々にさせる手順は、まったくもって理不尽だ。なぜここまでひどいのか知っている人がいるのか気になる
    • 普通に昔ながらの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」のような名前であるべきではないかと思う。なぜ特定のメールプロバイダー1つに結び付けるのか分からない

    • 実際には Gmail専用 だからだ。OAuthと、おそらくAPIアクセスを使っている
      IMAPははるかに難しく、はるかに遅く、Googleの帯域幅制限にも縛られる
    • 何年もの間、IMAPでGmailアカウントをバックアップしようとしてきたが、Gmail専用ツールも含めて一度も成功しなかった。いちばんましな同期ツールでも、1か月動かした末に特定のメールを取得できない地点で詰まった
      あまりにもコールドなストレージにあってタイムアウトしたのかどうかは分からない。だから、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は無料のようなので、まずダウンロードしてファイルをパースすればいい。それでも、すぐに始めるという観点では、このツールのほうが速そうだ