Support for iterating over query results

[marevers]

This PR adds a new function to the exporter which allows you to deal with dynamic database / schema structures. E.g. you have a multi-tenant PostgreSQL database which has multiple schemas. These schemas contain the same tables and you want to run a collection of queries over each relevant schema without knowing them by name.

Under the job configuration, you can add the following:

  iterator:
    # sql is the SQL to execute to retrieve the list of values to iterate over -
    # query result must be a single column
    sql: "SELECT schema_name FROM information_schema.schemata WHERE schema_name LIKE '%my_structure%'"
    # placeholder should be present in the original query and not also used as an environment variable
    # e.g. {{PLACEHOLDER}} - it will be replaced by the values retrieved by the query
    placeholder: SCHEMA_NAME
    # label is the label name to which the iterator value gets assigned
    label: structure_schema

This will retrieve a list of schemas according to the query specified and then run any query in the job that has the given placeholder ({{SCHEMA_NAME}}) in a loop, once for every result of the aforementioned query. Before running the query, the placeholder is replaced by the value retrieved. The value is also added to a Prometheus label configured under iterator.label to ensure non-duplicate results.

It is safe to combine both queries with and without an iterator placeholder in the same job if iterator is specified. Queries without the placeholder will be ran normally.

This is an easier option than using something like dynamic SQL, because it requires no changes to the original query and does not suffer from the performance implications that dynamic SQL can often have.

[marevers]

@dewey is this something you want to support? It comes in very handy in our case. If so, please let me know. I will rebase so you can merge.

[dewey]

Hey @marevers, I unfortunately don't have much time to put towards this project, so I'm focusing on smaller PRs that I can review without much context. Thanks for pinging me again on this one.

I think it would be valuable to have, could you go ahead and rebase please? Then I'll go ahead and merge it as it sounds like it's working in production for you without negative effects.

[marevers]

@dewey Thanks, we are running this in production now for a while and it works well. Fixed the conflict and formatting.

[dewey]

I've released a new version and the Docker image is pushing right now. Thanks for the contribution!