3 ms·
So, first thing is to figure out how to prevent the harm: Go and document how the current "system" works. Write down how the files are generated, how they're l
by angersock 11y ago
So, first thing is to figure out how to prevent the harm:
Go and document how the current "system" works. Write down how the files are generated, how they're laid out, what data they contain, what cleaning you have to do, and so forth. Write down what operations you are asked to do on them and how you do those operations.
Having done that, you can now change things as much as you want without fear of breaking things.
Next, throw this stuff into Postgres, either in tables or in unstructured jsonb columns. It should be simple to make the scripts periodically import the rows or blobs into the database, and once it's all in there, reporting via SQL becomes a lot easier.
The first time you can produce a useful report this way the other folks should be pretty happy.
That should be enough to get you going--don't worry about more esoteric solutions until you need them.
- i_am_lost 11y agoThanks a ton, angersock! Any particular reason you recommend starting with Postgres?
- dougabug 11y agoPostgres is pretty mature, robust and easy to use, it does a reasonably good job of adopting ANSI SQL standards. It has partitioning and replication support out of the box, scales well across multiple processors, large amounts of RAM, has a decent query planner, performs sequential scans and hash joins efficiently, parallel scans, can do a form of index only scan as well as bitmap index conversions, supports data warehouse friendly functions such as analytic/windowing functions as well as connect-by clauses and (recursive and non-recursive) CTE's, pipelined table functions and custom aggregate functions. It also has Foreign Data Wrappers (FDW), so you can map your CSV's and other databases to virtual tables/partitions, MVCC concurrency (readers don't block writers and writers don't block readers) and row level locks, you can set the transaction isolation level appropriate to your requirements, logical constraints (foreign keys, uniqueness, CHECK constraints, uniqueness within intervals), extensible domain indexes (GIST/GIN, GIS geospatial indexes, full text search, k-Nearest Neighbor search support). Basically, it has a ton of functionality for data analysis, it's easy to use, reasonably efficient and reliable. You could build a whole company on it (Instagram, Pandora, Skype, and many others) and there are many SaaS options (eg Heroku, Amazon).
- i_am_lost 11y agoWow! thanks for the comprehensive reply!