ESQL: Result cache

[nik9000]

Description

_search has a lovely cache that can make certain agg requests very fast because we can serve them from memory. Specifically, the index has to be the same and the query has to be the same modulo some rewrites. ESQL has nothing like this and probably should.

[elasticsearchmachine]

Pinging @elastic/es-analytics-geo (Team:Analytics)

[philippkahr]

Assuming I do the following:

from logs-*
| where host LIKE "a*b"
| dissect message "%{} authenticated using NTLM %{version}"
| keep @timestamp, host, version

Now I want this to be a line chart showing me the amount of NTLM auths depending on the version. Next to the line chart I want to see a table with host + login timestamp + version.

That means I need to copy and paste and run the same ESQL command twice.

If I think about this, I might have 5,10,15 visualisations that are based off of the same ESQL command, just showing different flavors.

What I would love to do because of this is:

• Link those visualisations, so I only have to edit, write and change the ESQL command in one place.
• Have those visualisations use some form of cache. Since all visualisations are based on the exact same ESQL command and showing it as a bar, line, or table is just a different rendering.

[nik9000]

If I think about this, I might have 5,10,15 visualisations that are based off of the same ESQL command, just showing different flavors.

I think this is a separate, but related, thing to caching. ES|QL doesn't have lovely request cache and it really could, but probably only for some queries. We could make that work. We have a whole language there, after all.

I think what you are talking about is sort of "custom commands". I'd love for you to be able to, say, make a function that is those commands. So you could do, like, MY_FROM_NTML_LOGS and it is those four lines.

Now these custom commands and caching sort of come together in the world of materialized views.

I want all three of these things.