Using Elastic SQL with Vega

Elastic Stack Kibana
1

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.

2

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
3

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
4

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
5

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.