2

In our organization, some people are working on setting up Hadoop with a lot of security restrictions etc. By the rate of progress, this seems complex, especially considering security restrictions, large variety of data-sources present etc. I am in another part of the organization, and in our group, the amount of data currently generated is not so high as to need Hadoop or Spark, at least for a long while. I am also building a small application which will needs a proper database.

Based on a back-of-the-envelope calculation, a single group in my smaller department generates about 25GB of data (images, log files, xlsx, ppts) etc per year, and ~ 10mb of numerical data that is stored in excel workbooks. Right now all these are stored in flat files (Excel files with numerical data, images, log files), because a lot of the work we do is non-routine (my part of the org is mostly a research org) and changes from day to day. So a lot of times we have to inspect images manually, as there is no way to do any automated image analysis for the kind of features we are looking for. In total, across all groups in my part of the org, we might be generating ~ 10TB of data per year (assuming 200 groups, and 2x multiplier to account for growth in data-volume per year, 200TB in 20 years), most of which reside in flat file systems.

We use a Excel template where people enter numerical data and then multiple people can simultaneously access the data, and generate reports.

Currently, the main problems I have to address is as follows:

  1. The Excel workbook that we use can only be accessed by 1 user at a time, so it causes a lot of conflicts
  2. If we store Excel files larger than say > 10mb, because its stored on a network, it becomes painful to open the workbook, so I need to chose a database which is not too complex so I can demo a prototype within a reasonable time.
  3. The linked data (numerical data along with blob data) that is stored in the database and or file-system needs to be able to transition over to hadoop/spark or distributed databases.

I was thinking of the following route:

  1. Just move to network share on Excel workbooks, so that multiple users can start access workbooks independently without seeking permission from the person who has the workbook open(using legacy sharing): https://www.presentationpoint.com/blog/multiple-users-excel-2016-datasheet/. The binary data will be stored on the file system, while numerical data is stored in Excel.
  2. Next, instead of using co-authoring (OneDrive) and because we have to start using a proper database, I would create a macro in excel which users would pretty much click to push the user generated numerical data (along with links to the binary data) into a database. The binary data will still reside on the file system, but possibly copy it over to a second database (Database2), so that it can be transitioned to distributed databases in future. Choose between Postgres or SQLite, (leaning towards SQLite, for individual groups for prototyping, as it seems to pretty widely used, has a large community, probably low bugs/maintanance cost). Each group (~ 200 total) would maintain their own PostgreSQL/Sqlite databases, till the distributed database becomes ready.
  3. In veeeeeeeeeeeeeeeeeery very long term future when we have to scale to Hadoop/Spark (assuming we hit the SQLite limit in 5 years), we can extract the data out of this database and push it to Hadoop/Spark using some convertor (https://stackoverflow.com/a/28240430/4752883, https://stackoverflow.com/a/40677622/4752883)

The reason for choosing SQLlite over PostgreSQL is that SQLite itself supports around 140TB of datastorage. SQLite seems to support multiple concurrent users (https://stackoverflow.com/questions/5102027/can-sqlite-support-multiple-users). Postgres has more capabilities, but will require a lot more resources and maintenance. I think in the long term, we probably have to go to Hadoop/Spark because the data-volumes are likely to grow for sure, but Hadoop is much more complex to manage and administer especially considering the security considerations etc.

Questions

  1. What are the drawbacks of this approach (what am I not thing about)?
  2. Some people have told me to directly jump to Hadoop, and some have told me to just SQL type databases, till we actually start needing a lot more data. If you were trying to chose a database, while knowing for sure that maybe in couple of years you will probably need Hadoop would you chose Hadoop or SQL-type databases in this scenario, for the step#2?

closed as too broad by mustaccio, Colin 't Hart, Marco, MDCCL, Max Vernon Aug 21 '18 at 18:13

Please edit the question to limit it to a specific problem with enough detail to identify an adequate answer. Avoid asking multiple distinct questions at once. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.

  • 2
    You might be interested in this: enterprisedb.com/blog/data-database-vs-file-system – a_horse_with_no_name Aug 20 '18 at 7:03
  • Note that the link in the above comment is for PostgreSQL - you'd have to check and see how good SQLite is at storing binary data (like images) DB. – RDFozz Aug 20 '18 at 21:56
  • @mustaccio.. I thought I provided a lot of detailed information, along with references for datapoints based on my search for answers to the questions posed in this post. I am however, a bit new to databases. Can you clarify what more information I can provide to narrow down the question? – alpha_989 Aug 24 '18 at 17:26
2

Hadoop is a mistake. You only talk about "quantity" of bits, you don't say at all how you're going to make use of them "25GB of data (images, log files, xlsx, ppts)." An RDBMS, Hadoop, and Spark all make shitty file systems. If what you want is a file system, use a filesystem -- check out ZFS. There are real concrete reasons for this. For those reasons, I suggest checking out this

Now let's assume you were generating 25 GB of actual relational data, and not blobs -- you would have no reason for any of those horizontal map/reduce architectures. Over 20 years, you would have 750 GB of data. This is very easily handeled by PostgreSQL. Especially with PARTITIONS.

tldr; move the blobs to the file system, and use PostgreSQL. Nothing about this workload requires Hadoop or Spark, and the only thing you can expect barking up that tree is fewer third-party tools, and substantially more difficulty in development and integration, and a lack of basic transitional abilities that you can likely use now.

If we store more data than say > 10mb, because its stored on a network, it becomes painful to open the workbook, so I need to chose a database which is not too complex so I can demo a prototype within a reasonable time.

Use a better network file system and look into this. There is no way this should be slow. Also because you're using Excel check into their shared workbook feature

  • thanks for your recommendation. Yeah.. I was actually thinking about the Excel shared workbook feature that you mentioned. Microsoft now calls it legacy sharing, because they are pushing for putting everything in OneDrive (with co-authoring). – alpha_989 Aug 19 '18 at 20:38
  • Depending on how many Excel Templates you have, you may want to consider migrating to a webapps yourself and just moving away from Excel. It's usually a very easy process. – Evan Carroll Aug 19 '18 at 20:48
  • I just added some more clarifications to the questions. Actually Also 25GB/year is only for my group.. for all groups its ~ 200TB (20years, 2x multiplier to account for rate of growth in data volume/year). Of this only 80GB (0.4%) is numerical data. I actually prefer file systems compared to Hadoop.. and will also recommend improving the file system. I understand that Postgres can hanle upto 131Exabytes, but probably nobody would do that in production scenarios (blog.2ndquadrant.com/postgresql-maximum-table-size). Wondering if you would do it differently given the changes? – alpha_989 Aug 19 '18 at 20:57
  • 10 TB/year is a lot of data. I have no idea how you're calculating that. It seems way high. – Evan Carroll Aug 19 '18 at 21:01
  • 2
    I have no idea how to help you. I doubt your numbers are real. You're planning on being four times bigger than Amazon was in 2010 with 45 TB of data. That's absurd for most people. The question about whether you're RDBMS or map/reduce is primarily one of whether or not your index can fit in RAM within your budget. You can reasonably conclude at that scale, it won't. However, that's a bizarre amount of data. And I would expect you have to have multiple people making six digits a year, and multiple consultants to help them out if you were a company of that magnitude. – Evan Carroll Aug 19 '18 at 21:18

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