1

I'm going to receive a large group of CSV files daily. Additionally I have 1 billion records of data (from those CSV files) but its not a fixed number (1b records daily). They're going to be growing and I need to store them in a DB. Also there are some extra points:

  1. There's no update
  2. There's no join and relationship
  3. Select a bunch of rows and group by
  4. Write intensive is so more than read
  5. I don't need normalisation

I had a bench between MySQL InnoDB and MyISAM. MyISAM was more better than InnoDB (because I have no normalisation) but MySQL is not a good approach because I have no relation.

I also checked MongoDB but with 50GB of CSV data it used 150Gb of storage!

I know I can use CSV files but I need a database approach. A database has lots of tools (like simple GROUP BY queries) and advantages like updates, bug fixes, security stuff, read and write performance, replication etc.

So I think I need a NoSQL DB which can do distributed write and support above extra points. But I don't know which NoSQL one (ones) is better for me.

I'm using Linux (CentOS).

  • What OS does it have run on? What are you willing to pay? Please edit your question and take Thomas' comment into account. – Jan Doggen May 23 '16 at 15:37
  • I'm constantly amazed by how badly people read. You were asked several questions and missed one. – Jan Doggen May 23 '16 at 17:43
  • Regardless of what tool you use, you can have some really large single files. Will your OS handle those huge files? Is it a 64-bit OS? This sounds like an interesting project. I wonder how long a select would take on 10 billion records. – Bulrush May 24 '16 at 19:07
  • @Bulrush, Yes it's 64. But I think I/O is more slow than database. and some tools can help in a huge bunch of data. – coditori May 25 '16 at 4:29
  • @Masood. Why do you think a single table database has slower access when you don't even have a single join or relation to another table? Databases are super optimized to filter and select data and no relations mean they are even faster in this case. – Bulrush May 25 '16 at 12:18
1

I used to manage informations like these.

I used PostgreSQL, where you have the advantage of a relational db and Nosql.
You can store in a single record, normalized data, like import data, an id, etc.. and an array, or a json, or even an Hstore.

Plus: it has native compression.

  • Thanks but I don't need normalization. PostgreSQL is a relational one as I said I have no relation. – coditori May 24 '16 at 9:33
  • @MasoodAfrashteh I understud. You can use Postgresql without normalization. Postgresql support unstructured data. You want to store a single record per CSV line No problem, you can store it raw, or split in an array of text. Or json, This is unstructured data with a real stable database(even faster than mongo on it's own field). – user_0 May 24 '16 at 9:41
0

I would suggest using Pandas with the .csv information converted to HDF5 format probably by Pandas itself. HDF5 is generally faster for than SQL star schema.

Not itself a database Pandas includes fast searches, selection, reshaping, group by, etc. and can interface with a large number of databases.

Pandas is Free, gratis & Open Source, and is actively maintained. It interfaces with tools like ipython, jupyter, matplotlib, etc., for improved manipulation and visualization of the data.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.