History: Add SQL querying capabilities

[amotl]

Hi there,

at Hiveeyes, we needed to discover all Grafana dashboards with only a single edit, i.e. without any further activity.

With this patch, adding SQL querying capabilities to the dashboard version history, it is very easy, thanks to pandas and DuckDB [^1].

SELECT uid, url, COUNT(version) as number_of_edits
FROM dashboard_versions
GROUP BY uid, url
HAVING number_of_edits=1

With kind regards, Andreas.

[^1]: While this example does not emphasize DuckDB's performance characteristics, it is a perfect showcase example how convenient it is to add SQL querying capabilities to data on the library level.

/cc @jangaraj, @hannes, @mytherin

[amotl]

Examples

SQL aggregation with JSON output

grafana-wtf log --format=json --sql="
  SELECT uid, url, COUNT(version) as number_of_edits
  FROM dashboard_versions
  GROUP BY uid, url
  HAVING number_of_edits=1
"

[
    {
        "uid": "0facG2vnz",
        "url": "https://swarm.hiveeyes.org/grafana/d/0facG2vnz/new-dashboard-copy",
        "number_of_edits": 1
    },
    {
        "uid": "0sR-oYkZz",
        "url": "https://swarm.hiveeyes.org/grafana/d/0sR-oYkZz/hiveeyes-01d8v8b3as97fzj9c3hj1ttgt2",
        "number_of_edits": 1
    },
]

SQL aggregation with YAML output

grafana-wtf log --format=yaml --sql="
  SELECT url
  FROM dashboard_versions
  GROUP BY uid, url
  HAVING COUNT(version)=1
"

- url: https://swarm.hiveeyes.org/grafana/d/0facG2vnz/new-dashboard-copy
- url: https://swarm.hiveeyes.org/grafana/d/0sR-oYkZz/hiveeyes-01d8v8b3as97fzj9c3hj1ttgt2