Lda result database persistence

lda-result-database-persistence

Summary

Persists Latent Dirichlet Allocation (LDA) topic modeling results from gensim into a PostgreSQL database for downstream visualization and querying. This skill bridges the output of unsupervised topic inference on metabolomics mass spectrometry data with a persistent backend suitable for web application access.

When to use

After running gensim LDA on a corpus of MS2 fragmentation features, when you need to store the LDA results (topics, document-topic assignments, term-topic distributions) in a PostgreSQL database so they can be queried and visualized by a Django web application or other downstream consumers. Use this skill when the LDA result JSON file has been successfully generated and you are ready to make those results accessible to the ms2ldaviz web interface.

When NOT to use

• The LDA result JSON file has not yet been generated by gensim (run gensim LDA step first).
• PostgreSQL server is not running or is unreachable on the configured host/port.
• You only need to visualize or analyze LDA results locally without persistence to a shared database.

Inputs

• LDA result JSON file (e.g., myexp.ldaresult.json) from gensim
• PostgreSQL connection credentials (username, password via environment or .env)
• Experiment identifier (string)
• Optional: pre-tarred gensim LDA dataset directory (e.g., myexp.lda.gensim.tar.gz) for insert_gensim workflow

Outputs

• PostgreSQL database records containing topic models, document-topic matrix, term-topic distributions
• Experiment metadata record linked to LDA results
• Web-accessible LDA visualization data (via Django ORM)
• Optional: tarballed gensim LDA format files for alternative storage

How to apply

Execute the ./run_gensim.py insert command with three required parameters: the path to the gensim LDA result JSON file (e.g., myexp.ldaresult.json), a PostgreSQL username (e.g., stefanv), and an experiment identifier (e.g., myexp). The script parses the JSON file containing topic distributions, document-topic allocations, and vocabulary, then transforms and loads these structures into the PostgreSQL schema. Verify successful insertion by confirming that no error messages are returned and that the experiment record is queryable from the database (e.g., via Django ORM or direct SQL query). If using the faster gensim-format insertion workflow, use ./run_gensim.py insert_gensim instead with a pre-tarred gensim LDA dataset directory (e.g., myexp.lda.gensim.tar.gz) to avoid reprocessing LDA metadata.

Related tools

• PostgreSQL (Database backend for persisting LDA results and experiment metadata)
• gensim (Generates LDA topic models and produces result JSON files consumed by insert command)
• Python (Language used to implement run_gensim.py insert and insert_gensim commands)
• Docker (Container platform for running PostgreSQL service during development and deployment)
• Django (Web framework that accesses persisted LDA results via ORM and serves visualizations) — github.com/sdrogers/ms2ldaviz

Examples

./run_gensim.py insert myexp.ldaresult.json stefanv myexp

Evaluation signals

• No error or exception messages returned from the insert command execution.
• Database query confirms presence of experiment record with matching identifier and linked topic/document-topic records.
• LDA topic data (e.g., top terms per topic, document-topic weights) is retrievable via Django ORM or direct SQL.
• Web interface at /uploads/ or experiment detail page successfully renders LDA visualization (D3 plots, topic tables) without data fetch errors.
• Row count in LDA-related database tables (e.g., topics, documents, document_topics) matches expected cardinality from the input JSON file.

Limitations

• The insert command requires the PostgreSQL service to be running and credentials to be correctly configured; initialization errors will halt the process without partial rollback.
• The insert workflow is slower than insert_gensim because it reprocesses LDA metadata from the full JSON file; use insert_gensim with pre-tarred gensim datasets for faster batch loading.
• The LDA result JSON schema must match the expected structure output by gensim; mismatched or corrupted JSON files will cause parsing errors.
• No direct conflict resolution is implemented; inserting a second experiment with the same identifier will likely cause key constraint violations or overwrite prior records, depending on schema design.

Evidence

• [readme] Insert lda result into db: "3. Insert lda result into db"
• [readme] Insert command consumes LDA result JSON and loads into database: "./run_gensim.py insert myexp.ldaresult.json stefanv myexp"
• [readme] insert_gensim command for faster insertion of gensim format LDA results: "./run_gensim.py insert_gensim myexp.corpus.json myexp.lda.gensim stefanv myexp"
• [readme] Gensim results can also be uploaded via web interface by tarring and submitting form: "The gensim result must be tarballed with for example tar -zcf myexp.lda.gensim.tar.gz myexp.lda.gensim* and then uploaded in the form."
• [readme] PostgreSQL database required for persistence: "docker run --name some-pg -d -p 5432:5432 -e POSTGRES_PASSWORD=j7z3rL40w9 -e POSTGRES_USER=django postgres"
• [readme] LDA result JSON file from gensim step: "The insert step consumes the LDA result JSON file (myexp.ldaresult.json) and loads it into the database"