1

We recently deployed a new 'data lake' on the Azure platform. The decision-making process had been messy and I cannot shake the feeling that we have bought an expensive, unwieldy database while we had much better and cheaper options.

Our requirements are pretty basic: we have about 7 GB of data, most of it comes from a daily database dump (CSV).
The data is used for reporting (currently query results are exported to excel, something like PowerBi will be used in the future) and analysis. With the amount of data we currently have, a full Big Data solution like Cloudera or MapR is unneccessary I think (but maybe I'm wrong).

In our current solution the ETL process that reads in the CSV dump and the database are managed by a third party. Using MSSQL management studio or SquirrelSQL, we can run queries and export the results. We have write-access to part of the database and I need to be able to design ETL processes to supplement the data already there.

This is the first glitch: I have not been able to automatically/programatically read in a flat file into the DB. In addition, the DB is not particulary fast and we are having trouble implementing logging/audit trail.

In my experience Postgres on CentOS is fast and logs DB access pretty much out-of-the box. Automating the import of flat files is no problem either.

What are better solutions than our current Azure setup?

Improve this question
1
  • 1
    Please note that this site doesn't feature requests for product comparisions: SR is about suggesting specific software for specific needs you define. For details, see: Is tool x versus tool y a fair question? . I've adjusted your question to make it on-topic. Might be a good idea to state a budget as well.
    – Izzy ♦
    Jul 5, 2018 at 11:31

1 Answer 1

Reset to default
1

If your flat file is CSV mysql has a built in import tool, as I am sure other products have a similar feature.

Logging of course can be setup.

Now with this small data set you have many options if you are thing local hardware.

  1. SSD have 500-550mb/s
  2. M.2 SSD have 2000-3000mb/s
  3. Add RAM to the computer, and setup a RAM drive.

Since you are using the cloud you could be bottlenecked by the speed of your internet.

Also having properly designed database also affects speed. Knowing when and how to create indexes to speed up data access. Many things can be done to optimize queries by someone trained in SQL.

Improve this answer
1
  • The devil is in the detail: bulk import seems much harder in Azure mssql then i remember from postgres. I have not found a way to do it yet. The ETL process makes it work by mounting Azure storage but I dont have that option as i'm not on the same network. So the bulk import feature is not something all DB's really share.
    – Ivana
    Jul 6, 2018 at 9:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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