Feature request to have an SQL language to access the Nomad state database

[Kamilcuk]

Proposal

I would want to propose to create an API to make SQL-ish queries to the Nomad database, so that queries can be executed server side. This will reduce network usage and database usage significantly, because clients will get the exact data they need, instead of downloading all the data and traversing it.

The API does not have to be SQL, however as a database language this looks like a natural choice. Currently, to access Nomad database, it's only possible to use a REST-ish API. I also think that instead of small solutions like Jobs/evaluation/allocations HTTP API filter it might be beneficial to just approach the problem generically.

Use-cases

For example, the administrator like me might want to generate a stacked bar graph to show current jobs executing on every node within the cluster. In the graph, each bar represents a different job. Such graphs may be very useful to analyze current and historical usage of a Nomad cluster.

Attempted Solutions

The current solution is a python program, that listens to Nomad event stream, and saves all JSON events to a Postgres database with allocations, evaluations, nodes and jobs indexed by ID field. The database can then be queried from Grafana to make JOINs between allocations, nodes and evaluations to generate needed data and present the data.

[jrasell]

Hi @Kamilcuk and thanks for the suggestion. This is something we have discussed internally a few times, however, we don't have anything concrete on this and would likely need some implementation discussions before making any decision.