CODEWITHSUNDEEP
jsonnosqletldenormalizationdenormalized
Philipp
Philipp

Reputation: 916

Generate denormalized view of normalized relational data

I am part of a project that is developing a CQRS-based application. The application will get all of its data from a NoSQL store where it retrieves it in a denormalized format. However, the actual data resides in a relational system. The NoSQL store serves as a view of the relational data. This view should be updated at a regular interval (probably a matter of every few minutes).

I am unsure as to what technology should be used to automate this update procedure. The denormalized view is JSON which could be generated inside an SQL query, but this would make the update procedure database vendor specific which will become a problem. I tried using Pentaho Kettle, but building a truly denormalized structure turned out to be incredibly tedious and time consuming.

What technology is appropriate considering that it should be easily maintainable and adaptable?

I am considering writing Python scripts, but I am unsure as to how maintainable they will be.

The NoSQL store is Couchbase and the RDBMS used for development is MariaDB.

Upvotes: 0

Views: 190

Answers (1)

Philipp
Philipp

Reputation: 916

A solution that seems to work well is using Pentaho Kettle solely for querying the database and updating the NoSQL store. The JSON gets generated as part of the query (most JSON functions are standardized in between RDBMS so this is fine). Because my DB user cannot use JSON_ARRAYAGG all sub-queries are expressed as serialized JSON which is parsed and replaced with the actual JSON in the Kettle transformation.

Upvotes: 0

Related Questions