How to configure OpenSearch/ElasticSearch SQL plug-in as a datasource in Grafana?

Question

OpenSearch (or ElasticSearch) has a SQL plug-in that is accessible via /_plugins/_sql URL.

I can run a SQL Query on the ES Dashboard using a query like this:

POST /_plugins/_sql
{
  "query": "SELECT test_name,sum(run-time) FROM test_metrics GROUP BY test_name"
}

I'm trying to figure out how can I add this SQL plug-in as a datasource in Grafana so that I can run a similar query via Grafana?

Answer 1

Thanks for @Jan Garaj for the pointer, I was able to find a plugin that works for this purpose.

1. Install grafana-infinity-datasource plugin
2. Configure your authentication settings if applicable
3. Use these settings to configure your datasource
   • Type: JSON
   • Parser: Backend
   • Source: URL
     • URL: <OPENSEARCH_SERVICE_IP>/_plugin/_sql
4. Use these for your URL Options
   • HTTP Method: POST
   • Body Type: Raw
     • Body Content Type: JSON
     • Body Content:
       • e.g. { "query": "SELECT * FROM ES_DATA_INDEX_NAME }
5. Configure your parsing options to map your data
   • Parsing options & Result fields: datarows
   • Columns: Map your fields (in order) to desired column-names
     • e.g. Selector: 0 as: time