Stored Scripts as UDFs in Elasticsearch SQL

[davemoore-]

It would be great to use stored scripts as user-defined functions (UDFs) in Elasticsearch SQL for arbitrary string manipulations, computations, and aggregations.

Proposed Syntax

Proposed syntax for calling a stored script as a UDF in Elasticsearch SQL:

script-name([arg=value[, ...]])

Breakdown:

• script-name is the name of the stored script.
• arg is the name of a variable referenced in the stored script.
• value is the value of the arg.
• Arguments must be passed as key-value pairs instead of positional arguments, because stored scripts have no concept of positional arguments.
• Zero, one, or more arguments may be passed to the script.

Alternatively:

script.script-name([arg=value[, ...]])

• Same as the example before, but also prefix the script names with a namespace (e.g. script.) so that we don't need to worry about conflicts with reserved keywords such as "select".

Example

Let's assume someone creates this stored script:

POST _scripts/calculate-score
{
  "script": {
    "lang": "painless",
    "source": "Math.log(_score * 2) + params.my_modifier"
  }
}

Example query using the example script:

SELECT calculate-score(score=_score, params.my_modifier=10) FROM scores;

Alternatively, using the prefixed syntax:

SELECT script.calculate-score(score=_score, params.my_modifier=10) FROM scores;

[elasticmachine]

Pinging @elastic/es-search-aggs

[nik9000]

Huh. I'm not sure if I love this idea of hate it.

Like, we tend to like giving folks the option to use inline scripts in addition to stored scripts. And we don't want to expose all of the stored scripts to the SQL compiler in case they don't make sense. But stored scripts is a somewhat analogous concept to UDFs so it feels kind of right too.

So, yeah, I think this is a great proposal, not because I think we should do it as proposed, but because I think it gives us a great way to start thinking about UDFs.

[elasticsearchmachine]

Pinging @elastic/es-analytical-engine (Team:Analytics)

[wchaparro]

Closing as not planned, superceded by our work in ES|QL. Please reopen if you have concerns.