Using Elastic SQL with Vega

Hi Kibana team,

I have this Elastic SQL query which works as expected:

POST /_sql?format=txt
{
    "query": "SELECT id AS Images, (MAX(pull_count)-MIN(pull_count)) AS Pulls_Added FROM \"project1\" GROUP BY id"
}

Result:

Images       |  Pulls_Added  
-------------------------------
image_1          |16.0       
image_2          |27.0       
image_3          |206.0       
image_4          |38.0        

I need to visualize this result in Kibana dashboard and I learned that Vega may be way to go because it supports Elastic SQL (not sure if Vega is the only visualization which supports it). Problem is that I can't find any example of using Elastic SQL with Vega visualization and can't figure out the syntax.
Please help me.

Vega doesn't support ES SQL at the moment. This is the issue to track progress on that task: https://github.com/elastic/kibana/issues/62333

For Vega, you would have to switch to regular Elasticsearch aggregation queries.

If you want to leverage ESSQL, you can use Canvas. It's not possible to put those on a dashboard yet, but Canvas offers a lot of tools to build a nice "dashboard" there.

1 Like

Just to complement @flash1293 answer, you could still use Vega but you have to convert your query to Elasticsearch DSL:

GET project1/_search
{
  "size": 0,
  "aggs": {
    "Images": {
      "terms": {
        "field": "id",
        "size": 100
      },
      "aggs": {
        "pull_count_stats": {
          "stats": {
            "field": "pull_count"
          }
        },
        "Pulls_Added": {
          "bucket_script": {
            "buckets_path": {
              "max": "pull_count_stats.max",
              "min": "pull_count_stats.min"
            },
            "script": "return params.max - params.min"
          }
        }
      }
    }
  }
}
1 Like

Thank you @Luca_Belluccini, this is exactly what I was looking for! After adding this query to my Vega visualization i got desired result! I wasn't aware that this is achievable without using ESSQL.

1 Like