3

I'm designing a Data Warehouse system. I need a program that gets: multiple datatypes, joins them and then perform fast queries on them.

Since the sources are in the order of 250 GB (per table) I guess we are in the Big Data area.

I can't know beforehand the types of queries that will be performed on this system, so I would need something schema-less. Also this system needs to take care of the fact that we have daily snapshots and so it should be able to query them in a reasonable way (more info here).

I know about Apache Drill, but it can't query daily snapshots (see the example in the linked question).

1

Reading this post and your other post, I don't quite understand why you feel it needs to be schema-less. Schema-less is generally a term used with big data (Hadoop in particular) where you have data coming from multiple sources that you want to load into a data warehouse for later use. You then configure the schema when you're ready to make use of the data.

For your large data sets you mention taking daily snapshots and while this will do the job - you are going to end up with a massive overhead in data volume. A better option is to turn on change data capture (CDC). CDC will log only the changes to the data rather than taking a complete snapshot of everything that is in it. So you're not wasting space for all the unchanged records. Some form of CDC is supported by most modern database engines, but there are also 3rd party tools that can do it.

I highly recommend that you seek the advice of a 3rd party consulting firm that specialises in data warehousing to help you manage this. What you're looking for is expertise as well as software recommendations.

| improve this answer | |
0

You won't get (normal) joins in a database like MongoDB, so that's out of question but just why inability to know queries beforehand should necessarily imply no schema? Your data does have some structure after all?

From the link to another question it seems like you need fast text searches (LIKE/ILIKE?).

Well, for that new versions of Postgres would fit the bill just fine, provided you make use of pg_trgm extension (http://www.postgresql.org/docs/9.1/static/pgtrgm.html). It implements index (as opposed to sequential scan) searches with LIKE/ILIKE operator with wildcards.

Thanks to this extension I have managed to create phenomenally fast search engine on ~TB sized PG database.

I have also written a boolean-like query engine using pyparsing Python module that has typical AND, OR, NOT operators and keywords corresponding to (pg_trgm-indexed) columns. It translates high-level query into SQL (actually SQLAlchemy Core SQL Expressions). This way you can query the DB in quite a flexible way while still getting results very quickly. I don't know if search engine-like functionality is what you need, but I'm sure a grammar for this could be developed quite easily using pyparsing.

If you need something involving more of numerical computing, PyTables is extremely fast for operation on out-of-memory datasets (although string search operators are somewhat rudimentary there).

| improve this answer | |

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.