I’m looking for a good architecture to be able to efficiently query data currently stored in NoSQL dbs (specifically DocumentDB).
We have a number of microservices that manage various entities (say client, product etc). Each store their data locally (in DocumentDB). We want to create another microservice that provides the ability for real time (latency on the order of seconds) ad-hoc queries over this data.
One option is to replicate all this data and store it in an SQL db, and build the query service on top of it. I expect this would make the queries quite fast, especially if we index all columns. (Of course, since this data keeps changing, we’d listen to a message queue for db updates.)
Is this the best way? How do companies go about building ad-hoc query functionality of NoSQL data? This seems like a problem that many large companies would have to solve. (I am new to NoSQL and microservice architecture.)
DocumentDB supports SQL querying over unstructured data so that you don't have to create separate database for ingestion and query: https://learn.microsoft.com/en-us/azure/documentdb/documentdb-sql-query#Aggregates
If you explain what kinds of queries your microservices need, or what limitations you've run into, we can provide targeted answers.