文档首页/ MapReduce服务 MRS/ 组件操作指南（安卡拉区域）/ 使用Elasticsearch/ 在SQL中使用UDF
更新时间：2024-11-29 GMT+08:00
查看PDF
分享

在SQL中使用UDF

操作场景

Elasticsearch中的SQL能力是基于开源OpenDistro中的SQL插件，并且在该插件上进行增强。本文介绍基于OpenDistro的SQL插件和Elasticsearch的Painless实现SQL支持UDF的特性。

这里的UDF功能为将索引中的某个字段的值做一些计算、转换后得到的自定义字段，例如：

  • 将firstname字段和lastname字段进行拼接返回一个叫name的自定义字段。
  • 将某数值字段的值乘2后返回新字段。
  • 将某字符串字段的值转为大写。

前提条件

安全模式下需要根据当前的鉴权模式进行如下权限配置（普通模式请忽略）：

  • 在基于用户和角色的鉴权模式下，参考基于用户和角色的鉴权进行配置。
    • 使用script创建UDF，用户需要在elasticsearch用户组中。
    • 在SQL中使用UDF，用户需要在supergroup用户组中。
  • 在Ranger鉴权模式下，参考添加Elasticsearch的Ranger访问权限策略进行配置， 并且在“Permissions”中额外勾选“script”权限和“read”权限。

UDF的编写及验证

UDF的编写在Elasticsearch中表现为创建一个stored script。

  • 样例1： 
    # 创建 script calculate_multiplier
POST _scripts/calculate_multiplier
{
  "script": {
    "lang": "painless",
    "source": "doc[params['field']].value * params['multiplier'] "
  }
}

# 删除script calculate_multiplier
DELETE _scripts/calculate_multiplier
  • 样例2： 
    # 创建 script to_upper
POST _scripts/to_upper
{
  "script": {
    "lang": "painless",
    "source": "params['_source'][params['field']].toUpperCase()"
  }
}
# 删除script to_upper
DELETE _scripts/to_upper
  • lang：表示script支持的语言，script作为UDF使用时，lang需要指定为painless。

    painless的语法参考如下链接：

    https://www.elastic.co/guide/en/elasticsearch/painless/7.10/painless-lang-spec.html

  • source：用户自定义的UDF中的操作。

    上述•样例1：中的source的含义为：获取参数field字段的内容，并且乘以参数multiplier的值。

    上述•样例2：中的source的含义为：获取参数field字段的内容，并且将其转为大写。

    source中可使用的操作方法可以参考如下链接：

    https://www.elastic.co/guide/en/elasticsearch/painless/7.10/painless-api-reference-field-java-lang.html#painless-api-reference-field-String

    https://www.elastic.co/guide/en/elasticsearch/painless/7.10/painless-api-reference-shared-java-lang.html#painless-api-reference-shared-Number

  • 参数类型支持int、long、string、double四种类型。
  • script id：上述实例中“calculate_multiplier”、“to_upper”为script的唯一标识，即script id。

    若上述编写的script作为UDF在SQL中使用，script id仅支持字母、数字、中划线和下划线，不支持其他特殊字符。

  • doc[]：表示从doc_values中获取指定字段的值，数值类型和keyword类型默认支持doc_values，text类型不支持doc_values。
  • 如果需要在script中获取不支持docvalue的字段（例如text类型）的内容需要使用“_ params['_source']”，即在source中获取该字段的值。从_source中获取效率较低，应该尽量避免使用。
  • UDF需要在具体的索引上进行操作，若使用了形如doc['my_field']的具体字段，需要确保使用到该UDF的索引中存在该my_field字段，否则在使用时候会遇到异常。

上述脚本是否可以按照预期运行，可以使用类似如下的命令进行验证。

建议在编写完stored script后验证script的有效性。

PUT accounts/_bulk?refresh
{"index":{"_id":"1"}}
{"account_number":1,"balance":39225,"firstname":"Amber","lastname":"Duke","age":32,"gender":"M","address":"880 Holmes Lane","employer":"Pyrami","email":"amberduke@pyrami.com","city":"Brogan","state":"IL"}
{"index":{"_id":"2"}}
{"account_number":6,"balance":5686,"firstname":"Hattie","lastname":"Bond","age":36,"gender":"M","address":"671 Bristol Street","employer":"Netagy","email":"hattiebond@netagy.com","city":"Dante","state":"TN"}
{"index":{"_id":"3"}}
{"account_number":13,"balance":32838,"firstname":"Nanette","lastname":"Bates","age":28,"gender":"F","address":"789 Madison Street","employer":"Quility","email":"nanettebates@quility.com","city":"Nogal","state":"VA"}

GET accounts/_search
{
  "script_fields": {
    " script filled": {
      "script": { 
        "id": "to_upper", 
        "params": {
          "field": "firstname"
        }
      }
    }
  }
}
  • accounts：为索引名称。
  • to_upper：为stored script的名称。
  • params中的内容为参数列表。

在SQL中使用UDF

本小节介绍如何在SQL中以UDF的形式使用script。

在SQL中使用UDF时，格式为：

UDF_calculate_multiplier(field=age,multiplier=2)
  • UDF_ ：在SQL中使用UDF的固定开头。
  • calculate_multiplier：在Elasticsearch中创建的script的名称。仅支持字母、数字、中划线和下划线。
  • 括号中的内容表示参数列表，key和value用等号隔开，多个参数用英文逗号隔开，不论是否包含参数，括号都需要添加。
  • 在SQL中使用UDF时“UDF_”会作为标识UDF的关键字来使用，不支持查询以“UDF_”开头的字段名，以“UDF_”开头的字段会引起歧义，导致SQL中词法、语法分析异常。

UDF支持的场景列表

  • SELECT中UDF计算结果作为自定义字段 
    POST _opendistro/_sql/
{
  "query":"SELECT UDF_calculate_multiplier(field=age,multiplier=2) FROM accounts"
}
 
POST _opendistro/_sql/
{
  "query":"SELECT UDF_to_upper(field=firstname) FROM accounts"
}
  • SELECT UDF与其他字段一起做查询 
    POST _opendistro/_sql/
{
  "query":"SELECT UDF_to_upper(field=firstname)，firstname FROM accounts"
}
  • UDF支持别名 
    POST _opendistro/_sql/
{
  "query":"SELECT UDF_calculate_multiplier(field=age,multiplier=2) as doubleAge FROM accounts"
}
  • UDF支持和where条件组合使用 
    POST _opendistro/_sql/
{
"query":"SELECT firstname, UDF_calculate_multiplier(field=age,multiplier=2)  as udfalias FROM accounts WHERE age > 10"
}
  • UDF支持和limit条件组合使用 
    POST _opendistro/_sql/
{
  "query":"SELECT age, UDF_calculate_multiplier(field=age,multiplier=2)  as pv FROM accounts  limit 1"
}
  • 支持多个UDF一起使用 
    POST _opendistro/_sql/
{
  "query":" SELECT UDF_calculate_multiplier(field=age,multiplier=2),UDF_to_upper(field=firstname) FROM accounts"
}
  • 支持与group by一起使用 
    POST _opendistro/_sql/
{
  "query":" SELECT UDF_calculate_multiplier(field=age,multiplier=2) as pv FROM accounts WHERE age > 0 GROUP BY pv"
}
  • 支持作为sum()、avg()、count()、min()、max()聚合的参数，前提条件为该UDF的返回值必须为数值类型 
    POST _opendistro/_sql/
{
  "query":" SELECT sum(UDF_calculate_multiplier(field=age,multiplier=2)) FROM accounts"
}

在SQL子查询中使用UDF

在SQL中使用UDF不支持多层嵌套，仅支持一层子查询，即将内层查询的结果作为外层的UDF的参数来使用。

如果UDF仍然需要作用在索引上，则需要在外层查询中增加一个参数“index=索引名”，用来指定该UDF在哪个索引上进行计算，该参数仅在外层SQL的UDF中生效，若在内层UDF中配置该参数将会被忽略。

“index=索引名”默认为所有索引，若SQL为嵌套查询时，必须配置该参数，如果没有配置则可能存在某个索引中不存在UDF中定义的字段而导致异常。

嵌套UDF的使用示例：

POST _opendistro/_sql/
{
  "query" : "SELECT UDF_calculate_multiplier(field=age,multiplier='num',index=accounts) FROM (SELECT account_number as num from accounts limit 3) as a"
}
  • 外层查询中仅支持SELECT和FROM，不支持与其他关键字组合使用。
  • 内层查询的结果和在外层UDF中使用的内层查询的结果需要有别名，否则语法会有异常。
  • 在外层UDF中使用的内层查询结果的别名应该唯一表示，避免歧义。
  • 内层查询的结果作为外层查询的参数时，在外层的UDF中直接使用子查询的字段的别名即可。即上述例子中的“num”，而不是使用“a.num”。

在包含子查询的SQL中使用UDF时，由于UDF是将子查询的结果作为UDF的参数来使用，无法下推给Elasticsearch，需要将子查询的每一条结果都与Elasticsearch再交互一次，即在子查询中查询会放大，一条SQL会转换为内层查询获取的size条查询，为了保证查询性能，需要尽可能减少查询返回的size，该参数使用“opendistro.query.size_limit”来控制，默认值为“200”。

父主题： 使用Elasticsearch