I have one file with 567 rows and 16,382 columns, most of which are floating point numbers. I have another file with 117,493 rows but only 3 columns. The contents deal with biology and genetics.
I have no idea what the numbers mean in the files mean. This is a college project where I have to be able to query different things given the files without knowing what the contents are. I am assuming we are not supposed to normalize the data because that would require understanding of the contents. We are graded on performance and reason for choosing which databases.
File 1: (567 rows, 16,382 columns)
I have been getting people surprised at the number of columns in this file. The columns are mostly genetic information with a bunch of numbers and cannot be normalized. I initially thought Postgresql would be good for File 1, but I read they are a row-oriented database, so it would be horrible. I read about Cassandra being good for column-oriented look-ups, but the problem is the most of the column and rows contain data and is mostly structured, except for a few areas with no values. Would it still be a better idea to use Cassandra?
Operations for File 1:
- simple look-up
- find mean
- find standard deviation
I am not able to confirm on the general performance between SQL and NoSQL on aggregate functions such as calculating mean and standard deviation. So this is another factor in selecting my database. I would assume Cassandra is faster than Postgresql here since Cassandra is column-oriented.
File 2: (117,493 rows, 3 columns)
Every single row and column contains data. I am guessing that Postgresql is good here since there are only 3 column but 117,493 rows, Postgresql is row-oriented, and there are no missing data. Is there a better NoSQL alternative in this case? Would a key-value store NoSQL be better since this will mostly be used for retrieval?
Operations for File 2:
- simple look-up
Most of the large files I have are related by some kind of ID, such as File 1 and File 2. If I use Postgresql for File 2 and Cassandra for File 1, is there a considerable performance loss? Usually you would store related tables in an RDMS, but does the number of columns for File 1 mean it is better to use a hybrid approach?