3 ms·

> What ideas do you have on how one can accelerate scientific research? I work in genetics (as a software engineer). If there was a major flaw in current scie

> What ideas do you have on how one can accelerate scientific research? I work in genetics (as a software engineer). If there was a major flaw in current scientific research (that involves software), it's that most labs care more about getting published than they do about the reproduce-ability and validation of their work. This means most of the software written in research is ad-hoc, write once, and often never looked at again. It was put together for the sole-purpose of producing some output that could be put in a paper and then lost to time. A current "holy grail" of software in research would be to fix that: empower other labs to validate and reuse the software written and reproduce the work of other labs with different data sets. And it is actively being worked on in a couple places (that I know of, perhaps more): * https://genepattern-notebook.org/ https://genepattern-notebook.org/ * https://app.terra.bio/ https://app.terra.bio/ * https://software.broadinstitute.org/wdl/ https://software.broadinstitute.org/wdl/ Some of these are just about giving the community a common framework to use for their software (CWL, WDL, Jupyter), others are about data storage and making it easily accessible for others to use in the cloud for reproducing results. If you want to have a impact, joining one of these groups would probably put you in a much closer position to doing that. If you just wanted to work on something in your spare time that would be incredibly valuable, then might I suggest this: It's amazing how much work is done in the scientific community using CSV/TSV files (usually gzipped). And most of that work is done via perl, sed, and awk. And often these are huge I'm working with a VCF file (TSV) right now that's 2 TB in size ZIPPED! It's crazy. Researchers often don't have the time, resources, or know-how to put together a simple Spark cluster and use it. 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!

mariushn 7y ago
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
ims 7y ago
Is anyone using Athena on AWS for tasks like this? It goes without saying that the documentation is hit or miss, but SQL-ish queries of flat files on S3 (even gzipped) can be a nice way to get the same result without managing Spark instances.
- massung 7y ago
  Obviously, I can't speak for "anyone", but all the groups I work with do not. There are a myriad of reasons for this, and reasonable people could effectively argue to the merits of those reasons. It's also important to remember that some of the reasons AWS, Google, and other cloud services are often NOT used are legal in nature. For example, some EU laws prohibit any personally identifiable (genetic) data from studies being put in the cloud. So, even if summary statistics - or data with PII data removed - can be put in the cloud, work has to be done on that data to remove it.
Something1234 7y ago
DBeaver has some kind of engine which allows you to write sql queries against csv files. I think mysql also has csv storage engine which you may find useful to hack into something.

mariushn 7y ago

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

ims 7y ago

Is anyone using Athena on AWS for tasks like this? It goes without saying that the documentation is hit or miss, but SQL-ish queries of flat files on S3 (even gzipped) can be a nice way to get the same result without managing Spark instances.

massung 7y ago
Obviously, I can't speak for "anyone", but all the groups I work with do not. There are a myriad of reasons for this, and reasonable people could effectively argue to the merits of those reasons. It's also important to remember that some of the reasons AWS, Google, and other cloud services are often NOT used are legal in nature. For example, some EU laws prohibit any personally identifiable (genetic) data from studies being put in the cloud. So, even if summary statistics - or data with PII data removed - can be put in the cloud, work has to be done on that data to remove it.

Something1234 7y ago

DBeaver has some kind of engine which allows you to write sql queries against csv files. I think mysql also has csv storage engine which you may find useful to hack into something.