Sorry, but "making an API that gets you this data" is the wrong answer.
Most APIs are glorified wrappers around individual record-level operations like- get me this user- or constrained searches that return a portion of the data, maybe paginated. Reporting needs to see all the data. This is a completely different query and service delivery pattern.
What happens to your API service written in a memory managed/garbage-collected language when you ask it to pull all the data from its bespoke database, pass it through its memory space, then send it back down the caller? It goes into GC hell, is what.
What happens when your API service when it issues queries for a consistent view of all the data and winds up forcing the database to lock tables? It stops working for users, is what.
There are so many ways to fail when your microservice starts pretending it is a database. It is not. Databases are dedicated services, not libraries, for a reason.
It is also true that analysts should not be given access to service databases, because the schema and semantics are likely to change out from under them.
The least bad solution? The engineering team is responsible for delivering either semantic events or doing the batch transformation themselves into a model that the data team can consume. It's a data delivery format, not an API.
>It is also true that analysts should not be given access to service databases, because the schema and semantics are likely to change out from under them.
Its not perfect but what we do is create a bunch of table views that represent each of the core data types in the system. We can then do all of the complex joins to collect the data analysts want in to an easy to query table as well as trying to keep the views consistent even as the db changes.
The service will need to read all its data and put it into objects, then extract the data from the objects to report it, then garbage collect all of that. For every single record in its entire data set.
You could say but oh, why not just return the underlying data without making objects? Well now you are exposing the underlying data format, which is what we’re trying to avoid by giving this job to the service.
And thus such patterns lead to the absurdity where 90% of enterprise apps do little actual computations beside serializing and deserializing JSON (or XML if a "legacy" app).
It's remarkable what you can do with just functions and nested data structures. Used to be big on the whole OOP thing, data roles, so much effort for so little.
Now I try to think about problems as "I have input data of shape X, I need shape Y" and fractally break it down into smaller shape-changes. I am kinda starting to get what those functional programmers are yammering on about.
The parent comment said “is asked for all records..GC hell “.
Since a micro service deals with only its own data and reporting is then across services, we’d need to query across services to get data and make sense of it. If we’d ever need to query all records, then such records would become domain objects in the micro services first before being passed along. A large number of domain objects would require a large amount of memory. Processing and releasing domain objects will result in GC on the released objects.
Wait, I would assume that the people in need of reporting would have a pretty good idea of what those reports should look like. That means you know exactly what data needs to be read from a data store optimized for reporting. Each micro-service contributes their share of data to a data store optimized for reading. This is a text-book use case for a non-relational document store. I'm really not seeing what's so difficult about building such a process.
Reporting and non-relational are like oil and water, coming from experience working with people who make reports.
It’s not like they come up with every report they think they might need while the micro service is being architected. They come up with a new report long after engineers have moved on. If it’s a SQL database, no problem. If it’s some silly resumeware data store, then what?
Most APIs are glorified wrappers around individual record-level operations like- get me this user- or constrained searches that return a portion of the data, maybe paginated. Reporting needs to see all the data. This is a completely different query and service delivery pattern.
What happens to your API service written in a memory managed/garbage-collected language when you ask it to pull all the data from its bespoke database, pass it through its memory space, then send it back down the caller? It goes into GC hell, is what.
What happens when your API service when it issues queries for a consistent view of all the data and winds up forcing the database to lock tables? It stops working for users, is what.
There are so many ways to fail when your microservice starts pretending it is a database. It is not. Databases are dedicated services, not libraries, for a reason.
It is also true that analysts should not be given access to service databases, because the schema and semantics are likely to change out from under them.
The least bad solution? The engineering team is responsible for delivering either semantic events or doing the batch transformation themselves into a model that the data team can consume. It's a data delivery format, not an API.