ES|QL - Completion command

[afoucret]

Tracked in #123043

The completion command will be used to execute a completion through an LLM using ES}QL.

Command grammar

| COMPLETION <prompt> WITH <inferenceId> (AS <targetField>)

where

param	required	description
prompt	required	Provides the prompt to be executed for the row.The prompt can be built from a mix a constant and field values.
inferenceId	required	Endpoint used to do the completion(can be optional when we will have a default LLM in EIS).
targetField	optionaldefault: completion	Name of the result column.

Usage example:

Row by row

FROM search-movies
| WHERE match(plot, "Gandalf the grey")
| EVAL prompt= CONCAT(
      "Extract movie genre:\n",
       "<title>", title, "</title>",
       "<actors>", MV_CONCAT(actors, ","), "</actors">,
       "<plot>", plot, "</plot"
    )
| COMPLETION prompt WITH "openai-completion" AS genre
| DROP prompt

Aggregated rows:

FROM business-news
| WHERE industry:technology AND date:today
| EVAL company_data = CONCAT(
        "<title>", title, "</title>",
        "<summary>", summary, "</summary">
        "<article>", title, "</article"
    )
| STATS company_data = VALUES(company_data) BY company
| EVAL prompt=CONCAT(
        Summarize important news for the copmany\n",
        "<company>", company, "</company>\n",
        MV_CONCAT(company_data, "\n")
    )
| COMPLETION prompt WITH "openai-completion" AS company_news

Development

• Grammar and logical plan ([ES|QL] COMPLETION command grammar and logical plan #126319)
• Logical plan analysis ([ES|QL] COMPLETION command analysis. #126677)
• Logical plan optimization ([ES|QL] COMPLETION command logical plan optimizer #126763)
• Physical plan and mapping (([ES|QL] COMPLETION command physical plan #126766)
• CompletionOperator (based on an aync operator)
• End to end tests
• Add telemetry
• Doc
• Tech preview (available in non-snapshot builds)

[elasticsearchmachine]

Pinging @elastic/es-search-relevance (Team:Search Relevance)

[elasticsearchmachine]

Pinging @elastic/search-eng (Team:SearchOrg)

[elasticsearchmachine]

Pinging @elastic/search-relevance (Team:Search - Relevance)

[Mikaayenson]

👋 This is great to see. Aligns really well with what we're proposing here

[afoucret]

@Mikaayenson Also, the implementation will be using the ES|QL inference infrastructure we have created for the Rerank command command which should be merged very soon as a tech preview (see PR here #123074).

[ioanatia]

I have to say I much prefer the original COMPLETION proposal that was working row by row:

FROM movies
  | WHERE title:"star wars"
  | COMPLETION "Summarize the provided document" USING title, overview=SUBSTRING(overview, 0, 100), actors WITH "completionInferenceId" AS summary

If we want to use chat completion on aggregate values, we should just add that as an option to COMPLETION.
However I don't see this as an immediate requirement for COMPLETION.

[Mikaayenson]

@afoucret / @ioanatia thanks for the conversation today! I've gone ahead and linked this PR to the RFC

This proposal aligns well with the upcoming COMPLETION ESQL command that has different syntax but generally meets the needs, with a few caveats that will be sidelined for now.

• Referencing the modelID within the query adds validation complications for prebuilt rules. Ideally this will be optional in the future. Elastic Security will ship an always-on prebuilt llm model/option. It would be great if this could be leveraged to also ease the query development/management. (future goal)
• COMPLETIONS return text. Ideally we will be able to specify structured return types like DOUBLE so that we do not have to introduce additional steps to obtain a probability score.
• One other use case to mention here is the ability to pseudo perform entity resolution based on the COMPLETION results. (e.g. COMPLETION "These users are the same user." USING proofpoint.user_name, ,endpoint.user_name WITH "completionInferenceId" AS entity_resolution_score)

[elasticsearchmachine]

Pinging @elastic/kibana-esql (ES|QL-ui)