Lens powered by ES|QL - technical spike

[teresaalvarezsoler]

Description
In a future state all Lens visualizations should be powered by ES|QL. We need to draft a plan of how this is going to work and how much it will take.

Research questions

• How can we replace the backend translation from Lens formBased configurato to ES|QL for Lens? Code some examples (date histogram + count, date histogram + avg, breakdown by, formula)
• How much effort we estimate this migration can take until we migrate all Lens visualizations?
• Which of the missing features from Elasticsearch (some aggregations and field type support) are required? Can we keep two systems running in parallel or do we need to reach total feature parity in order to do the translation to ES|QL in production?
• understand the differences between the current date histogram auto-bucket logic in Kibana/esagg and the one with ES|QL: can we provide the same behaviour with auto and fixed/custom intervals?

note: multi-layer is out of scope for this spike

Some more in-depth implementation questions in this document

Timeboxed in 1 week

[elasticmachine]

Pinging @elastic/kibana-visualizations (Team:Visualizations)

[teresaalvarezsoler]

When we have some Lens examples running with ES|QL let's test this scenario: #186366. If this problem doesn't exist anymore in Lens, we can close the issue, otherwise, we will need to work on it at some point to fix it. cc @stratoula @markov00 .

[markov00]

Updates on the current Technical Spike:

• @ppisljar created a POC to understand the technical feasibility lens esql generation #196049
• the POC covers most if not all the possible functionalities available in Lens DSL that are convertible in ES|QL
• The POC also shows that is possible to add more and more functionalities with ease with a graceful fallback to DSL with no overhead for now.
• multilayer is supported the way multilayering was done in Lens: issuing multiple queries.

Answers the spike questions:

How can we replace the backend translation from Lens formBased operations to ES|QL for Lens? Code some examples (date ?histogram + count, date histogram + avg, breakdown by, formula)

Each operation on a Lens form_based operation can be translated to ES|QL command. Whenever the command is unavailable or not supported (like timeshift) the entire query generation can be routed to use DSL instead.

How much effort we estimate this migration can take until we migrate all Lens visualizations?

From the POC to a production-ready version we need to:

1. add an overall testing suite across all the available functions
2. validate all the configurations with the current ES|QL specs (for example, disable ES|QL generation if a particular field type is not supported)
3. replace current ES|QL string concatenation with named parameters to avoid any issues around sanitization and possible injections.
4. integrate or replace all the required features to reach the feature parity.
   It is a bit hard to estimate this, but it can be considered a Large task (if don't consider point 4 and we just use the simplified approach for 3).

Which of the missing features from Elasticsearch (some aggregations and field type support) are required? Can we keep two systems running in parallel or do we need to reach total feature parity in order to do the translation to ES|QL in production?

There isn't an overhead that blocks us from keeping both systems in parallel. I also suggest adding a switch (better in the kibana.yml or per space) that allows an admin to switch off the ES|QL generation in Lens if issues arise as an escape hatch.

In this way, we can start delivering minimal functionalities and add them whenever they are ready in ESQL without the need for a full one-shot switch on such a huge code surface. I also propose to switch off DSL whenever we can fully trust our ES|QL transforms.

understand the differences between the current date histogram auto-bucket logic in Kibana/esagg and the one with ES|QL: can we provide the same behaviour with auto and fixed/custom intervals?

There are differences between the current Kibana auto bucket logic and the provided functionality in ES|QL:

In ES|QL you specify the target number of buckets, if you increase/decrease this parameter, the automatically computed granularity will change

WHERE @timestamp >= ?_tstart AND @timestamp < ?_tend
...
BY BUCKET(@timestamp, 150, ?_tstart, ?_tend)

You can specify a WHERE clause + a fixed bucket size to get the fixed interval

WHERE @timestamp >= ?_tstart AND @timestamp < ?_tend
...
BY BUCKET(@timestamp, 1 week)

Because the first command produces different results than ours (even if we set the same amount of buckets it could result in different intervals), we can use the second command here and we can specify our computed interval.

The only identified problem for now with this strategy will appear whenever the ES|QL query is shown to the Lens user: the query will appear with a fixed interval that breaks the "auto-interval" logic.
We can probably replace that interval with an ES|QL variable like ?_auto_interval, solving this problem in Lens, but then the problem could surface in other places where the same ES|QL query is executed: if we don't take care of the execution context and how those variables/params are injected into the query we can start seeing failing queries here and there.

In my horizon, I see the ES|QL feature parity with Lens closer in time than the ES|QL editing experience in Lens (clicky clicky + esql). If that is the case, whenever we will have a feature parity status, we can drop the DSL aggregations and replace completely the auto-interval logic with the default one used in ES|QL, removing completely the requirement of having an ES|QL variable for that.

[teresaalvarezsoler]

You can specify a WHERE clause + a fixed bucket size to get the fixed interval

@markov00 is this a truly fixed interval or a "minimum interval" as it is working in Lens today? If it is a fixed interval, then it will produce different results for large time ranges.

[markov00]

Is a truly fixed interval. We are going to discuss this with @stratoula and @ppisljar this week to find the right solution for that