What database technology?
Discussion
I have a dataset of around 40 million records that is growing at a rate of circa 2 million per year. Essentially, it should be one big table, but right now it's a series of somewhere between 3,000 and 4,000 different CSV files. If we need the data, we can find it but making comparisons is of course a nightmare!
Each record has 26 columns of data (not including indexes etc).
Each piece of data is simple - either a number or a bunch of characters. There's no complex objects.
The data doesn't change - once it's in the system it's in the system, it's not hot data, it's updated in batches at several points throughout the year, not on a "live" basis.
I want to collate all this information into a single central database that we could then write a program to allow complex reporting and filtering on this data. I don't see any benefit to splitting the data into separate shards, but could be persuaded otherwise.
Initially, this is for internal use but in time, I want to be able to sell subscriptions for other people to access this information.
What would an optimum DB solution be - I know the number of records is a lot in real terms, but not too much in computing terms!
My initial reaction would be a MySQL database using PHP for reporting as and when required. I am aware that this may well not be the optimum route. Can anyone make any suggestions as to what DB technology would be best to use for this quantity of data and why it's better than MySQL?
Each record has 26 columns of data (not including indexes etc).
Each piece of data is simple - either a number or a bunch of characters. There's no complex objects.
The data doesn't change - once it's in the system it's in the system, it's not hot data, it's updated in batches at several points throughout the year, not on a "live" basis.
I want to collate all this information into a single central database that we could then write a program to allow complex reporting and filtering on this data. I don't see any benefit to splitting the data into separate shards, but could be persuaded otherwise.
Initially, this is for internal use but in time, I want to be able to sell subscriptions for other people to access this information.
What would an optimum DB solution be - I know the number of records is a lot in real terms, but not too much in computing terms!
My initial reaction would be a MySQL database using PHP for reporting as and when required. I am aware that this may well not be the optimum route. Can anyone make any suggestions as to what DB technology would be best to use for this quantity of data and why it's better than MySQL?
SQL Server 2019 is worth a look.
There a free version which is more than adequate for your needs: https://www.microsoft.com/en-us/sql-server/sql-ser...
It integrates well with most things - PowerBI (obviously, as it's a MS product), Access and Excel.
Having said that, any of the well know databases will do the job. The advantage of SQL server is its easier integration with other MS product.
There a free version which is more than adequate for your needs: https://www.microsoft.com/en-us/sql-server/sql-ser...
It integrates well with most things - PowerBI (obviously, as it's a MS product), Access and Excel.
Having said that, any of the well know databases will do the job. The advantage of SQL server is its easier integration with other MS product.
The Moose said:
Initially, this is for internal use but in time, I want to be able to sell subscriptions for other people to access this information.
Just wanted to highlight that this is quite a different question to the rest of your post.For internal use you can pretty easily just throw something together using PowerBI / Tableau etc, possibly even just using your existing CSV files as the data source.
If you want to provide a service to users you need to think about how you'll control user access, billing, availability, monitoring and so on.
To my mind, you'd want to look to a cloud platform that can provide at least some of those things out of the box. To prevent you having to re-invent the wheel. You might want to research what is available (and the cost) now and let that have input to your tech choice now so that you don't have to re-implement later because the solution you pick now doesn't give you what you need later.
Aside from that, I'd also want to think about how new data will get added, can you set up an automatic ingestion path ? for example on Azure you could confgiure a blob store to receive files via email, set up an app with a trigger of "file added", which kicks of a data factory job to import the data from the file in to a database (whatever that might be). I'd be fairly sure most other platforms would let you do something similar.
Crafty_ said:
sensible stuff
This.However what skills does your team have? Or are you bringing the skills in - daily rates and availability of resources can influence your choice.
While it sounds like a straightforward implementation, there are many solutions (as highlighted above) and if you are at some point you are thinking about reselling to 3rd parties then you REALLY want to invest in some time from an experienced systems architect. You also need to ensure you have the skills available to maintain this.
If vernturing into the world of reselling and not simply internal clients then you have to consider things like security models, availability, backups, GDPR etc etc etc.
Pay for good advice, get a proper implementation strategy and plan - if you are to make this a commercial offering in future it will pay-off many times over.
Order66 said:
Crafty_ said:
sensible stuff
This.However what skills does your team have? Or are you bringing the skills in - daily rates and availability of resources can influence your choice.
While it sounds like a straightforward implementation, there are many solutions (as highlighted above) and if you are at some point you are thinking about reselling to 3rd parties then you REALLY want to invest in some time from an experienced systems architect. You also need to ensure you have the skills available to maintain this.
If vernturing into the world of reselling and not simply internal clients then you have to consider things like security models, availability, backups, GDPR etc etc etc.
Pay for good advice, get a proper implementation strategy and plan - if you are to make this a commercial offering in future it will pay-off many times over.
(for internal use, any SQL system will cope with what you're after. However make sure you set up some decent query containment in any view you set up or performance will get a bit slow of you have 40 mill records in the same table)
Thank you for the replies. A few responses:
Agreed - do you have any suggestions of cloud platforms to start looking at?
shopper150 said:
Watching closely, I am looking for a similar solution.
I’ve shortlisted PowerBI & Tableau.
I haven't used either of these tools before, but they appear to do far more than I need! Tableau is hugely expensive.I’ve shortlisted PowerBI & Tableau.
anonymous said:
[redacted]
I agree.anonymous said:
[redacted]
Fair enough - appreciate the comment.nyt said:
SQL Server 2019 is worth a look.
Agreed, although I would prefer an open source platform and having used both My and Ms SQL in years past, I don't know what MS brings to the table - and we're more familiar with My.Crafty_ said:
If you want to provide a service to users you need to think about how you'll control user access, billing, availability, monitoring and so on.
To my mind, you'd want to look to a cloud platform that can provide at least some of those things out of the box. To prevent you having to re-invent the wheel. You might want to research what is available (and the cost) now and let that have input to your tech choice now so that you don't have to re-implement later because the solution you pick now doesn't give you what you need later.
Great post, thank you!To my mind, you'd want to look to a cloud platform that can provide at least some of those things out of the box. To prevent you having to re-invent the wheel. You might want to research what is available (and the cost) now and let that have input to your tech choice now so that you don't have to re-implement later because the solution you pick now doesn't give you what you need later.
Agreed - do you have any suggestions of cloud platforms to start looking at?
Crafty_ said:
Aside from that, I'd also want to think about how new data will get added, can you set up an automatic ingestion path ? for example on Azure you could confgiure a blob store to receive files via email, set up an app with a trigger of "file added", which kicks of a data factory job to import the data from the file in to a database (whatever that might be). I'd be fairly sure most other platforms would let you do something similar.
Records are not added on an hourly/daily basis. New data will be added in large batches on a handful of occasions each year. The way I see it happening is uploading the new data to a staging environment and after testing, push the database to the live environment.MySQL is still open source and free to use but now owned by Oracle, might be worth a look at MariaDB which is the fork from when Oracle bought Sun.
Not a huge number of rows in the scheme of things but may be worth looking at partitioning the table.
SQL express would do it but you have a 10gb dB size limit, guess you aren’t near that if it’s just text.
Not a huge number of rows in the scheme of things but may be worth looking at partitioning the table.
SQL express would do it but you have a 10gb dB size limit, guess you aren’t near that if it’s just text.
x5x3 said:
this is worth a look if you do not already have My SQL/Apache/PHP setup - https://www.mamp.info/en/
Thank you - I'm going to get it set up on the cloud 
anonymous said:
[redacted]
It really doesn't make any sense to run any database of any meaningful scale on a shared server.a mysql server trying to run OLAP workloads is going to need a lot of memory, as soon as it comes under memory pressure it is going to slow down, which normally has a knock on affect on the application that will increase its memory usage, and you end up with a death spiral.
x5x3 said:
did you even read the OP's original post?
Of course I read it, why else would I be posting?Are you going to elaborate on what you are getting at?
What have i missed that would make it still make sense for him in 2019 to be using a 'mamp' stack or the far more common lamp stack rather than a separate DB, when he has 40million rows, 26 columns and wants to do complex reporting on that data?
nyt said:
SQL Server 2019 is worth a look.
There a free version which is more than adequate for your needs: https://www.microsoft.com/en-us/sql-server/sql-ser...
It integrates well with most things - PowerBI (obviously, as it's a MS product), Access and Excel.
Having said that, any of the well know databases will do the job. The advantage of SQL server is its easier integration with other MS product.
This is what I'd do. Spin up an AWS RDS server, and then import.There a free version which is more than adequate for your needs: https://www.microsoft.com/en-us/sql-server/sql-ser...
It integrates well with most things - PowerBI (obviously, as it's a MS product), Access and Excel.
Having said that, any of the well know databases will do the job. The advantage of SQL server is its easier integration with other MS product.
But then I've been dealing with MS and AWS a while now.
Why on earth would you use aws, or mamp or anything complicated.
OP said his data isn't huge, and whilst he plans to one day make it available externally, it's internal for foreseeable future.
Keep offline any free database product, SQL server or anything else, backup after the few annual updates happen.
Why pay into Amazon's pockets needlessly or overcomplicate it. If it ever does need to be external, those decisions can be taken then.
OP said his data isn't huge, and whilst he plans to one day make it available externally, it's internal for foreseeable future.
Keep offline any free database product, SQL server or anything else, backup after the few annual updates happen.
Why pay into Amazon's pockets needlessly or overcomplicate it. If it ever does need to be external, those decisions can be taken then.
Edited by hyphen on Monday 30th December 04:34
hyphen said:
Why on earth would you use aws, or mamo or anything complicated.
Because it's really not complicated. I'd want it to be always available, easy for everyone to access, and I don't want to run a server and all the costs and maintenance BS that comes with it.
I already have stuff in AWS, so the extra costs would be negligible.
It's what *I* would do.
Gassing Station | Computers, Gadgets & Stuff | Top of Page | What's New | My Stuff