Menyimpan Gmail ke SQLite

 (github.com/marcboeker)
2 poin oleh GN⁺ 2025-05-11 | 1 komentar | Bagikan ke WhatsApp
  • Gmail to SQLite adalah aplikasi Python yang menyinkronkan pesan Gmail ke database SQLite lokal agar dapat digunakan untuk analisis dan pengarsipan
  • Perilaku default-nya adalah sinkronisasi inkremental yang hanya mengunduh pesan baru, sementara opsi sinkronisasi penuh dapat mengunduh semua pesan dan juga mendeteksi status penghapusan
  • Pengambilan pesan menggunakan pemrosesan paralel multi-thread, serta mencakup penanganan error dan penghentian seperti percobaan ulang otomatis berbasis exponential backoff dan penanganan CTRL+C
  • Untuk menjalankannya diperlukan Python 3.8 atau lebih baru, Google Cloud Project dengan Gmail API yang diaktifkan, serta file OAuth 2.0 credentials.json
  • Data yang disimpan mencakup pengirim, penerima, label, isi pesan, ukuran, status sudah dibaca, status terkirim, status terhapus, dan lainnya sehingga pola penggunaan Gmail dapat dianalisis langsung dengan SQL

Alat sinkronisasi lokal pesan Gmail

  • Gmail to SQLite adalah aplikasi Python yang menyimpan pesan Gmail ke database SQLite lokal
  • Tujuannya adalah memungkinkan data Gmail untuk dianalisis dan diarsipkan
  • Seluruh codebase menerapkan type hint sehingga memiliki keamanan tipe

Metode sinkronisasi dan keandalan

  • Sinkronisasi default berjalan sebagai sinkronisasi inkremental, hanya mengunduh pesan baru
  • Jika menggunakan opsi --full-sync, semua pesan akan disinkronkan dan pesan yang dihapus dari Gmail akan dideteksi
  • Pengambilan pesan dilakukan dengan pemrosesan paralel multi-thread untuk meningkatkan performa
  • Penanganan error mencakup percobaan ulang otomatis dan exponential backoff
  • Saat menekan CTRL+C, prosedur penghentian yang rapi akan dijalankan
    • Berhenti menerima pekerjaan baru
    • Menunggu pekerjaan yang sedang berjalan selesai
    • Menyimpan status progres dari pekerjaan yang telah selesai
    • Keluar secara normal
  • Jika CTRL+C ditekan sekali lagi, program akan langsung berhenti

Instalasi dan prasyarat

  • Lingkungan eksekusi memerlukan Python 3.8 atau lebih baru
  • Diperlukan Google Cloud Project dengan Gmail API yang telah diaktifkan
  • File kredensial OAuth 2.0 credentials.json harus berada di root proyek
  • Alur instalasinya adalah meng-clone repositori lalu memasang dependensi dengan uv sync
  • Pengaturan autentikasi Gmail API dilakukan dengan membuat atau memilih project di Google Cloud Console, mengaktifkan Gmail API, lalu membuat kredensial OAuth 2.0 untuk Desktop application dan menyimpannya sebagai credentials.json

Cara menggunakan perintah

  • Sinkronisasi inkremental dasar dijalankan seperti berikut
python main.py sync --data-dir ./data

# or: uv run main.py sync --data-dir ./data
  • Untuk sinkronisasi penuh dan deteksi penghapusan, gunakan --full-sync
python main.py sync --data-dir ./data --full-sync
  • Untuk menyinkronkan pesan tertentu saja, gunakan sync-message dan --message-id
python main.py sync-message --data-dir ./data --message-id MESSAGE_ID
  • Untuk hanya mendeteksi dan menampilkan pesan yang dihapus, gunakan sync-deleted-messages
python main.py sync-deleted-messages --data-dir ./data
  • Jumlah worker thread dapat ditentukan dengan --workers, dan nilai default-nya adalah jumlah core CPU
python main.py sync --data-dir ./data --workers 8
  • Argumen command line adalah sebagai berikut
    • command: wajib, salah satu dari sync, sync-message, sync-deleted-messages
    • --data-dir: wajib, direktori tempat database SQLite akan disimpan
    • --full-sync: opsional, memaksa sinkronisasi penuh
    • --message-id: wajib untuk sync-message, ID pesan tertentu yang akan disinkronkan
    • --workers: opsional, jumlah worker thread
    • --help: menampilkan bantuan perintah dan opsi

Skema SQLite dan contoh analisis

  • Tabel messages pada database SQLite yang dibuat berisi field yang diperlukan untuk analisis pesan Gmail
    • message_id: ID pesan Gmail unik
    • thread_id: ID thread Gmail
    • sender: informasi pengirim dalam JSON yang berisi nama dan email
    • recipients: JSON penerima berdasarkan jenis to, cc, bcc
    • labels: array label Gmail
    • subject: subjek pesan
    • body: isi pesan dalam teks biasa
    • size: ukuran pesan dalam byte
    • timestamp: waktu pesan
    • is_read: status sudah dibaca
    • is_outgoing: apakah pesan dikirim oleh pengguna
    • is_deleted: apakah pesan dihapus dari Gmail
    • last_indexed: waktu sinkronisasi terakhir
  • Jumlah email dapat diagregasi berdasarkan pengirim
SELECT sender->>'$.email', COUNT(*) AS count
FROM messages
GROUP BY sender->>'$.email'
ORDER BY count DESC
  • Email yang belum dibaca dapat diagregasi berdasarkan pengirim untuk melihat pengirim yang banyak mengirim email yang kurang menarik
SELECT sender->>'$.email', COUNT(*) AS count
FROM messages
WHERE is_read = 0
GROUP BY sender->>'$.email'
ORDER BY count DESC
  • Dengan strftime, jumlah email dapat diagregasi berdasarkan tahun, bulan, hari, hari dalam minggu, dan jam
SELECT strftime('%Y', timestamp) AS period, COUNT(*) AS count
FROM messages
GROUP BY period
ORDER BY count DESC
  • Email yang isi pesannya mengandung newsletter atau unsubscribe dapat ditemukan untuk mengelompokkan newsletter berdasarkan pengirim
SELECT sender->>'$.email', COUNT(*) AS count
FROM messages
WHERE body LIKE '%newsletter%' OR body LIKE '%unsubscribe%'
GROUP BY sender->>'$.email'
ORDER BY count DESC
  • Total ukuran email per pengirim dan pengirim email besar dapat dilihat dalam satuan MB
SELECT sender->>'$.email', sum(size)/1024/1024 AS size
FROM messages
GROUP BY sender->>'$.email'
ORDER BY size DESC
  • Jumlah email yang dikirim ke diri sendiri dapat dihitung dengan kondisi pada JSON recipients dan email 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'
  • Dari email yang diterima, urutan berdasarkan total kapasitas terbesar per pengirim dapat dilihat
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
  • Pesan yang dihapus dapat diambil dengan kondisi is_deleted=1
SELECT message_id, subject, timestamp
FROM messages
WHERE is_deleted=1
ORDER BY timestamp DESC

Bacaan terkait

1 komentar

 
GN⁺ 2025-05-11
Opini Hacker News

  • Saya penasaran mengapa header tertentu dipisahkan dalam skema. recipients, subject, dan sender bisa saja dijadikan field JSON, tetapi semuanya juga bisa dimasukkan ke satu headers, termasuk sisa header pesan lainnya
    Jika alasannya performa, headers bisa disimpan sebagai satu blob JSON, lalu field yang diperlukan dibuat sebagai generated column. Misalnya subject bisa dibuat dengan json_extract("headers", '$.Subject') dan diberi indeks
    Model ini kuat karena pengguna bisa menambahkan generated column yang terindeks sesuai kebutuhan kueri mereka sendiri dengan ALTER TABLE. Status DKIM juga bisa dilakukan dengan mengekstrak "Dkim-Signature", membuat kolom dan indeks, lalu melakukan GROUP BY

    • Sebenarnya generated column pun tidak diperlukan, karena SQLite mendukung expression index. Misalnya, jika membuat CREATE INDEX subjectidx ON messages(json_extract(headers, '$.Subject')), indeks itu akan digunakan di tempat yang merujuk ekspresi tersebut
      Setelah membuat indeks seperti ini, akan lebih berguna membuat VIEW yang memakai ekspresi tersebut daripada melakukan ALTER pada tabel utama untuk menambahkan generated column
    • Menambahkan indeks untuk kueri sekali pakai terlihat seperti kebiasaan buruk
      Biasanya saya lebih suka memisahkan kolom yang akan digunakan secara berkelanjutan. Apalagi untuk target yang stabil seperti header email; meski kolom headers bisa membuat perubahan skema sedikit lebih mudah, itu sama saja memindahkan rasa sakit dari saat penulisan ke saat pembacaan, dan menyisakan kemungkinan gagal diam-diam
    • Saya sering memakai pola serupa saat mengembangkan sistem dengan PostgreSQL. Awalnya saya membuat tabel dengan memikirkan field yang saya tahu diperlukan, lalu memasukkan metadata sisanya ke kolom JSON
      Setelah sekitar dua bulan, ketika field yang benar-benar diperlukan mulai terlihat, saya mengisinya dari JSON dan membuat API terus menjaganya tetap mutakhir, atau membuat view. Ini cukup membantu menghindari growing pain dari pendekatan “masukkan saja semuanya ke MongoDB” atau “taruh saja di file system”, dan biayanya juga tidak besar
    • Kolom dkim didefinisikan sebagai NOT NULL; saya penasaran apa yang terjadi jika pesan email tidak memiliki header Dkim-Signature

  • Beberapa tahun lalu saya membuat alat visualisasi email dalam jumlah besar seperti Gmail: https://github.com/terhechte/postsack

    • Cukup keren. Mirip alat visualisasi penggunaan disk, tetapi tampaknya lebih berfokus pada jumlah total email daripada penggunaan disk
      Saya penasaran apakah ada opsi ukuran juga. Saya ingin melihat pengirim mana yang paling banyak memakai ruang penyimpanan saya. Dan sertifikat SSL situs webnya sudah kedaluwarsa
    • Terlihat menarik. Tautan gmvault di README sekarang sudah mati; saya penasaran apakah ini yang benar: https://github.com/gaubert/gmvault
    • Terlihat menarik. Dulu saya pernah mencoba sendiri hal serupa dengan qdirstat, tetapi email harus disusun dengan cara tertentu, seperti folder berdasarkan tanggal, dan sulit dipotong ulang berdasarkan kriteria lain
      Sebaliknya, file cache qdirstat mudah dibuat, sehingga bisa dipakai untuk memvisualisasikan berbagai objek yang tampak seperti file

  • Sangat disayangkan bahwa sekarang kita tidak bisa lagi login bahkan dengan kata sandi khusus aplikasi, dan harus membuat OAuth client serta melewati alur OAuth. Padahal ini email saya sendiri, tetapi Google mengambil standar terbuka untuk saya mengaksesnya

    • Melihat jumlah spam yang masuk ke alamat Gmail gratis, dan jumlah spam yang datang dari server Gmail ke akun non-Gmail, saya semakin condong ke arah de-Google
      Terutama, saya makin sering mendapat informasi bahwa email freelance saya masuk spam di sistem penerima. Namun saya masih bingung bagaimana melepaskan kebiasaan yang sudah terbentuk di ekosistem Google
    • Saya penasaran mengapa Anda menganggap kata sandi khusus aplikasi sebagai standar terbuka, sedangkan OAuth bukan
    • Saya kurang paham maksudnya, mengingat dengan kata sandi aplikasi kita bisa mendapat akses IMAP penuh

  • Baru-baru ini saya mencoba mengintegrasikan Gmail ke aplikasi saya https://github.com/rumca-js/Django-link-archive, tetapi menghabiskan terlalu banyak waktu, dan saya memutuskan dukungan Gmail tidak layak
    Gmail to SQLite menjelaskan pengaturan kredensial dalam 6 langkah, tetapi dalam kasus saya tidak seperti itu. Setelah 6 langkah itu, Google mengatakan aplikasinya belum dipublikasikan sehingga harus dipublikasikan, lalu karena saya bukan pengguna Workspace, aplikasinya tidak bisa dibuat sebagai aplikasi internal, dan ketika saya mengubahnya menjadi aplikasi eksternal, Google mengatakan aplikasi itu tidak bisa digunakan sebelum diverifikasi
    Dalam proses verifikasi, mereka meminta domain, alamat, berbagai detail lain, justifikasi untuk scope, bahkan video yang menjelaskan cara aplikasi digunakan, dan mengatakan perlu waktu untuk memverifikasi data yang dikirim. Semuanya terasa seperti labirin pengaturan, dan terlalu berlebihan membuat pengguna harus melewati rintangan yang diminta Google

    • Prosedur yang Google paksa orang jalani hanya untuk mendapatkan satu API key benar-benar tidak masuk akal. Saya penasaran apakah ada yang tahu mengapa ini bisa seburuk itu
    • Pakai saja IMAP gaya lama dan kata sandi aplikasi. Tidak perlu melompati rintangan Google

  • Saya penasaran apa perangkat lunak pencadangan Gmail open source terbaik yang ada saat ini. Juga penasaran apakah ada yang pernah menyusun solusi seperti ini termasuk penyimpanan lampiran

    • Ada https://github.com/GAM-team/got-your-back. Ini open source, dan punya fitur resume sehingga pencadangan dan pemulihan pada akhirnya selesai
      Sebagai referensi, ada juga https://www.mailstore.com/en/products/mailstore-home/. Bukan open source, tetapi karena berupa GUI dengan indeks, ini bagus untuk mencari email lokal, dan resume hanya tersedia untuk pencadangan sehingga pemulihan berukuran besar biasanya gagal
    • Mungkin bukan jawaban persis yang diinginkan, tetapi Google punya layanan bernama Takeout, yang memungkinkan kita meminta dan mengunduh cadangan data dari semua layanan Google, termasuk Gmail
      Saya memasang pengingat untuk menjalankannya setiap beberapa bulan dan memperbarui cadangan lokal. Kalau tidak salah, hasilnya berupa file mbox yang dikompresi gzip
    • Jika memakai klien IMAP dan mengaturnya ke mode offline/unduh, kita juga bisa mengunduh semuanya dan menyimpannya secara lokal. Di Evolution sepertinya disebut “mode offline”, tetapi di Thunderbird atau klien lain namanya bisa berbeda

  • Rasanya ini seharusnya dinamai semacam “Gmail to SQLite”, melainkan “IMAP to SQLite”. Saya tidak mengerti kenapa harus diikat ke satu penyedia email tertentu

    • Karena memang khusus Gmail. Ini memakai OAuth dan kemungkinan akses API
      IMAP jauh lebih sulit dan jauh lebih lambat, serta juga terikat oleh batasan bandwidth Google
    • Selama beberapa tahun saya mencoba mencadangkan akun Gmail lewat IMAP, tetapi tidak pernah berhasil sekalipun, termasuk dengan alat khusus Gmail. Alat sinkronisasi terbaik pun berjalan selama sebulan lalu tersangkut di titik tertentu karena tidak bisa mengambil email tertentu
      Entah apakah email itu berada di penyimpanan yang terlalu dingin sehingga timeout. Jadi saya paham kalau memakai API proprietary Google bisa bekerja lebih baik
      Sekarang Google Takeout menyertakan mbox, berfungsi dengan benar, dan cukup cepat, tetapi tidak menyediakan pembaruan berkelanjutan. Pada akhirnya saya pindah ke penyedia email lain, Infomaniak, dan saya berterima kasih kepada diri saya dulu karena sudah memakai domain email saya sendiri

  • Akan bagus kalau pencarian teks penuh juga bisa diaktifkan

    • Untuk layanan yang dijalankan oleh perusahaan pencarian, menurut saya pencarian teks penuh Gmail secara mengejutkan buruk

  • Kemarin saya juga membuat hal yang sama. Karena saya ingin mencantumkan email penerima berdasarkan domain. Kodenya berantakan, tapi ada di sini: https://github.com/hugoferreira/gmail-sqlite-db

  • Ini sedikit mengingatkan saya pada server IMAP berbasis PostgreSQL, Archiveopteryx: https://github.com/aox/aox
    Skema AOX selalu tampak bagus, tetapi saya belum pernah benar-benar menggunakannya dengan baik. Kegunaan utamanya adalah analisis dan pencarian email, bukan sebagai server IMAP harian

    • Saya juga teringat Manitou-Mail. Ini klien email khusus berbasis PostgreSQL yang kuat dan bisa dipakai sehari-hari, serta cukup solid: https://www.manitou-mail.org/

  • Saya penasaran bagaimana biaya bandwidth di sini. Sebagai pemilik akun Gmail 40GB lebih, saya ingin tahu apakah saya akan dikenai biaya jika mentransfer dengan alat ini
    Mudah diperbaiki. Google Takeout sepertinya gratis, jadi bisa unduh dulu lalu parsing filenya. Meski begitu, dari sisi langsung mulai, alat ini tampaknya lebih cepat