CODEWITHSUNDEEP
elasticsearchcanvaskibanaelasticsearch-sql
Lobotomeh
Lobotomeh

Reputation: 85

Elasticsearch SQL query in Canvas : it doesn't work like SQL?

I begin to work with the Canvas section in Kibana - and to retrieve data, it uses Elasticsearch SQL.

What I try to do is to retrieve the count of several values ; and I need to group certain values together - the ones that start with the same letters.

My SQL query looks like this :

SELECT 
(SELECT COUNT(*) FROM logs WHERE status LIKE 'missingValue%'),
(SELECT COUNT(*) FROM logs WHERE status LIKE 'errorValue%'),
(SELECT COUNT(*) FROM logs WHERE status='exactErrorValue'),
(SELECT COUNT(*) FROM logs WHERE status='anotherExactErrorValue')

When I test this query, using SQL and a little database, it works

Now, I want to make this work inside an element of my canvas. I choose a horizontal bar chart to represent it.

This is my elasticsearch SQL query :

SELECT 
(SELECT COUNT(*) FROM "monitoring-func-*" 
WHERE status LIKE 'missingValue%'),
(SELECT COUNT(*) FROM "monitoring-func-*"
WHERE status LIKE 'errorValue%'),
(SELECT COUNT(*) FROM "monitoring-func-*" 
WHERE status='exactErrorValue'),
(SELECT COUNT(*) FROM "monitoring-func-*" 
WHERE status='anotherExactErrorValue')

And I get this error :

        {
      "error": {
        "message": "[essql] > Unexpected error from Elasticsearch: [unresolved_exception] Invalid call to nullable on an unresolved object ScalarSubquery[With[{}]
\\_Project[[?COUNT(?*)]]
\\_Filter[(status) REGEX (LikePattern)#5139]
 \\_UnresolvedRelation[[][index=monitoring-func-*],null,Unknown index [monitoring-func-*]],5142] AS ?"
      }
    }

Seeing "unknown Index", I first thought that the wildcard was the problem.

But it's not, it's perfectly fine in my others Elasticsearch queries.

Is there something about the Subqueries, the multiple SELECT, that Elasticsearch SQL doesn't handle well ? I didn't find any ressource or topics on this, but maybe I've searched the wrong way.

Upvotes: 1

Views: 2775

Answers (1)

Tim Schnell
Tim Schnell

Reputation: 11

Depending on your Elasticsearch version, essql either doesn't support subqueries or it is very limited, here is the documentation.

Upvotes: 1

Related Questions