3 ms·

Thanks so much, mussung, for your excellent practical feedback! Both alternatives that you listed are very tempting. May I please ask you some followup questio

Thanks so much, mussung, for your excellent practical feedback! Both alternatives that you listed are very tempting. May I please ask you some followup questions? There's no email in your profile. My email is marius.andreiana@gmail.com > A command line tool that allowed someone to run SQL (or SQL-like) commands on a gzipped CSV file FAST would be invaluable. And if it could JOIN across CSV files ... wow! What prevents one importing each CSV in a postgres db as tables, creating indexes and then start running queries? Disk space availability? (My local drive is only 1TB)

massung 7y ago
mariushn, I've sent you an email. > What prevents one importing each CSV in a postgres db as tables, creating indexes and then start running queries? There are many reasons: * Experience/knowledge. Many labs don't have anyone experienced with databases. * Security. Without proper dev ops a local DB is often out of the question. And when dealing with PII genetic data, [cloud] security can be a major concern. * Funding. Machines cost money. AWS RDS instance cost money. Maintaining them costs money. Dev ops costs money. etc. * Often times the queries being done are simple. For example, you may have a giant CSV with cross-ethnic trait data, but only need samples of African decent with a beta value > 0.1. Sure, you could spin up a database, load the entire thing into a table (O(N) + disk space + time), then index it (now it's O(2N) + more disk space + more time), and then finally run your query. Or you can just O(N) run over the CSV once and output the results with no extra disk space or "wasted" (perception of the researcher) time. Finally, don't fool yourself about the capabilities of researchers. Many are code-savvy, but lack experience. Writing a SQL query is easy. Loading multiple TB of data into a relational database, indexed properly, and done in a manner that won't take days of time is a level higher.
sitkack 7y ago
Two things that might be of use 1. SQLite can easily import multiple GB of CSV data, process it in memory, or persisted to disk, a great tool for analyzing datasets up to the low 10s of gigs. 2. XSV, https://github.com/BurntSushi/xsv https://github.com/BurntSushi/xsv

massung 7y ago

mariushn, I've sent you an email. > What prevents one importing each CSV in a postgres db as tables, creating indexes and then start running queries? There are many reasons: * Experience/knowledge. Many labs don't have anyone experienced with databases. * Security. Without proper dev ops a local DB is often out of the question. And when dealing with PII genetic data, [cloud] security can be a major concern. * Funding. Machines cost money. AWS RDS instance cost money. Maintaining them costs money. Dev ops costs money. etc. * Often times the queries being done are simple. For example, you may have a giant CSV with cross-ethnic trait data, but only need samples of African decent with a beta value > 0.1. Sure, you could spin up a database, load the entire thing into a table (O(N) + disk space + time), then index it (now it's O(2N) + more disk space + more time), and then finally run your query. Or you can just O(N) run over the CSV once and output the results with no extra disk space or "wasted" (perception of the researcher) time. Finally, don't fool yourself about the capabilities of researchers. Many are code-savvy, but lack experience. Writing a SQL query is easy. Loading multiple TB of data into a relational database, indexed properly, and done in a manner that won't take days of time is a level higher.

sitkack 7y ago

Two things that might be of use 1. SQLite can easily import multiple GB of CSV data, process it in memory, or persisted to disk, a great tool for analyzing datasets up to the low 10s of gigs. 2. XSV, https://github.com/BurntSushi/xsv https://github.com/BurntSushi/xsv