跳至主要內容

Apache Calcite 动态数据管理框架-06-Es 整合 contains 方法如何使用？

老马啸西风2018年11月15日大约 4 分钟Searchapachesearchindexessh

思路

我们直接看一下 calcite es 中的例子代码，本地测试验证一下。

拓展阅读

https://github.com/quxiucheng/apache-calcite-tutorial

https://github.com/apache/calcite

代码位置

具体见；

https://github.com/apache/calcite/tree/main/elasticsearch/src/test/java/org/apache/calcite

我们可以把代码下载下来。

实战测试

准备工作

参见 Apache Calcite 动态数据管理框架-05-java 访问 ES 整合 apache calcite，使用标准 SQL 访问 ES

数据结构

$ curl -X GET "http://localhost:9200/booksmapping/_mapping"

如下：

{"booksmapping":{"mappings":{"properties":{"author":{"type":"keyword"},"description":{"type":"text"},"id":{"type":"keyword"},"price":{"type":"float"},"publish_time":{"type":"date","format":"yyyy-MM-dd"},"title":{"type":"text","analyzer":"standard"}}}}}

实现思路-contains

一开始尝试了很多方法，发现都不对。

calcite ES like 不支持，看到 contains 应该是支持的，但是 SQL 应该如何编写？

同时发现网上的资料其实并不多，也可能是搜索的问题。

当然最后还是回到了 es 的官方代码用例。

官方例子

MatchTest#testMatchQuery

/**
   * Tests the ElasticSearch match query. The match query is translated from
   * CONTAINS query which is build using RelBuilder, RexBuilder because the
   * normal SQL query assumes CONTAINS query is for date/period range.
   *
   * Equivalent SQL query:
   *
   * 
   * select * from zips where city contains 'waltham'
   * 
   *
   * ElasticSearch query for it:
   *
   * 
   * {"query":{"constant_score":{"filter":{"match":{"city":"waltham"}}}}}
   * 
   */

这里演示了 contains 的用法，会被转换为 ES 中的 match 查询（包含部分匹配）。

实际测试

maven 版本依赖

calcite-elasticsearch 已经是当前最新的版本，v1.36.0，发现依然不支持。



    4.0.0
    
        org.example
        calcite-learn
        1.0-SNAPSHOT
    

    calcite-learn-es

    
        8
        8
        UTF-8

        1.36.0
    

    
        
            org.apache.calcite
            calcite-core
            ${calcite.version}
        
        
        
            org.apache.calcite
            calcite-elasticsearch
            ${calcite.version}
        

        
        
            org.apache.calcite
            calcite-avatica
            1.6.0
        

        
            com.google.protobuf
            protobuf-java
            3.17.3 
        

        
            org.elasticsearch.client
            elasticsearch-rest-high-level-client
            7.15.0 
        

        
            com.alibaba
            fastjson
            1.2.78

实现

我们主要看最上面的 contains 用法：

private static void showContains(CalciteConnection calciteConnection) {
        try {
            System.out.println("CONTAINS ------------------------- ");
            String sql = "SELECT * FROM booksmapping WHERE title contains 'Java'";
            Statement statement = calciteConnection.createStatement();
            ResultSet resultSet = statement.executeQuery(sql);
            // 6.遍历打印查询结果集
            System.out.println(ResultSetUtil.resultString(resultSet));
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

发现依然还是报错。

报错

报错信息如下：

Exception in thread "main" java.lang.RuntimeException: java.sql.SQLException: Error while executing SQL "SELECT * FROM booksmapping WHERE title CONTAINS 'Java'": From line 1, column 34 to line 1, column 54: Cannot apply 'CONTAINS' to arguments of type ' CONTAINS '. Supported form(s): ' CONTAINS '
' CONTAINS '
' CONTAINS '
' CONTAINS '
	at org.example.CalciteElasticsearchViewAutoByMappingContainsExample.showContains(CalciteElasticsearchViewAutoByMappingContainsExample.java:81)
	at org.example.CalciteElasticsearchViewAutoByMappingContainsExample.main(CalciteElasticsearchViewAutoByMappingContainsExample.java:65)
Caused by: java.sql.SQLException: Error while executing SQL "SELECT * FROM booksmapping WHERE title CONTAINS 'Java'": From line 1, column 34 to line 1, column 54: Cannot apply 'CONTAINS' to arguments of type ' CONTAINS '. Supported form(s): ' CONTAINS '
' CONTAINS '
' CONTAINS '
' CONTAINS '
	at org.apache.calcite.avatica.Helper.createException(Helper.java:56)
	at org.apache.calcite.avatica.Helper.createException(Helper.java:41)
	at org.apache.calcite.avatica.AvaticaStatement.executeInternal(AvaticaStatement.java:164)
	at org.apache.calcite.avatica.AvaticaStatement.executeQuery(AvaticaStatement.java:228)
	at org.example.CalciteElasticsearchViewAutoByMappingContainsExample.showContains(CalciteElasticsearchViewAutoByMappingContainsExample.java:77)
	... 1 more
Caused by: org.apache.calcite.runtime.CalciteContextException: From line 1, column 34 to line 1, column 54: Cannot apply 'CONTAINS' to arguments of type ' CONTAINS '. Supported form(s): ' CONTAINS '
' CONTAINS '
' CONTAINS '
' CONTAINS '
	at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
	at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
	at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
	at java.lang.reflect.Constructor.newInstance(Constructor.java:423)
	at org.apache.calcite.runtime.Resources$ExInstWithCause.ex(Resources.java:507)
	at org.apache.calcite.sql.SqlUtil.newContextException(SqlUtil.java:948)
	at org.apache.calcite.sql.SqlUtil.newContextException(SqlUtil.java:933)
	at org.apache.calcite.sql.validate.SqlValidatorImpl.newValidationError(SqlValidatorImpl.java:5517)
	at org.apache.calcite.sql.SqlCallBinding.newValidationSignatureError(SqlCallBinding.java:399)
	at org.apache.calcite.sql.type.OperandTypes$PeriodOperandTypeChecker.checkSingleOperandType(OperandTypes.java:1315)
	at org.apache.calcite.sql.fun.SqlOverlapsOperator.checkOperandTypes(SqlOverlapsOperator.java:104)
	at org.apache.calcite.sql.SqlOperator.validateOperands(SqlOperator.java:498)
	at org.apache.calcite.sql.SqlOperator.deriveType(SqlOperator.java:607)
	at org.apache.calcite.sql.SqlBinaryOperator.deriveType(SqlBinaryOperator.java:178)
	at org.apache.calcite.sql.validate.SqlValidatorImpl$DeriveTypeVisitor.visit(SqlValidatorImpl.java:6575)
	at org.apache.calcite.sql.validate.SqlValidatorImpl$DeriveTypeVisitor.visit(SqlValidatorImpl.java:6562)
	at org.apache.calcite.sql.SqlCall.accept(SqlCall.java:166)
	at org.apache.calcite.sql.validate.SqlValidatorImpl.deriveTypeImpl(SqlValidatorImpl.java:1926)
	at org.apache.calcite.sql.validate.SqlValidatorImpl.deriveType(SqlValidatorImpl.java:1911)
	at org.apache.calcite.sql.validate.SqlValidatorImpl.validateWhereOrOn(SqlValidatorImpl.java:4590)
	at org.apache.calcite.sql.validate.SqlValidatorImpl.validateWhereClause(SqlValidatorImpl.java:4576)
	at org.apache.calcite.sql.validate.SqlValidatorImpl.validateSelect(SqlValidatorImpl.java:3829)
	at org.apache.calcite.sql.validate.SelectNamespace.validateImpl(SelectNamespace.java:61)
	at org.apache.calcite.sql.validate.AbstractNamespace.validate(AbstractNamespace.java:88)
	at org.apache.calcite.sql.validate.SqlValidatorImpl.validateNamespace(SqlValidatorImpl.java:1154)
	at org.apache.calcite.sql.validate.SqlValidatorImpl.validateQuery(SqlValidatorImpl.java:1125)
	at org.apache.calcite.sql.SqlSelect.validate(SqlSelect.java:282)
	at org.apache.calcite.sql.validate.SqlValidatorImpl.validateScopedExpression(SqlValidatorImpl.java:1091)
	at org.apache.calcite.sql.validate.SqlValidatorImpl.validate(SqlValidatorImpl.java:797)
	at org.apache.calcite.sql2rel.SqlToRelConverter.convertQuery(SqlToRelConverter.java:607)
	at org.apache.calcite.prepare.Prepare.prepareSql(Prepare.java:257)
	at org.apache.calcite.prepare.Prepare.prepareSql(Prepare.java:220)
	at org.apache.calcite.prepare.CalcitePrepareImpl.prepare2_(CalcitePrepareImpl.java:666)
	at org.apache.calcite.prepare.CalcitePrepareImpl.prepare_(CalcitePrepareImpl.java:519)
	at org.apache.calcite.prepare.CalcitePrepareImpl.prepareSql(CalcitePrepareImpl.java:487)
	at org.apache.calcite.jdbc.CalciteConnectionImpl.parseQuery(CalciteConnectionImpl.java:236)
	at org.apache.calcite.jdbc.CalciteMetaImpl.prepareAndExecute(CalciteMetaImpl.java:702)
	at org.apache.calcite.avatica.AvaticaConnection.prepareAndExecuteInternal(AvaticaConnection.java:677)
	at org.apache.calcite.avatica.AvaticaStatement.executeInternal(AvaticaStatement.java:157)
	... 3 more
Caused by: org.apache.calcite.sql.validate.SqlValidatorException: Cannot apply 'CONTAINS' to arguments of type ' CONTAINS '. Supported form(s): ' CONTAINS '
' CONTAINS '
' CONTAINS '
' CONTAINS '
	at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
	at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
	at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
	at java.lang.reflect.Constructor.newInstance(Constructor.java:423)
	at org.apache.calcite.runtime.Resources$ExInstWithCause.ex(Resources.java:507)
	at org.apache.calcite.runtime.Resources$ExInst.ex(Resources.java:601)
	... 37 more

错误原因

可以发现，最主要的还是 contains 支持的类别问题。

Cannot apply 'CONTAINS' to arguments of type ' CONTAINS '. Supported form(s): ' CONTAINS '
' CONTAINS '
' CONTAINS '
' CONTAINS '

不支持 varchar 字符串的 contains。

正如上面注释中写的一样，正常情况下 SQL query 已经限制了 contains 的类别。

   * Tests the ElasticSearch match query. The match query is translated from
   * CONTAINS query which is build using RelBuilder, RexBuilder because the
   * normal SQL query assumes CONTAINS query is for date/period range.
   *

github 提了一个疑问：

https://github.com/apache/calcite/pull/1530

如何解决这个问题呢？

???

自己重新实现，在 es adaptor 的基础上拓展?

小结

解决问题的方式还算比较多，但是这里考虑的场景估计还是不够全面。

但是整体的思路是没有问题的，通过 view 简化 sql，不需要修改 calcite 的源码。

参考资料

https://calcite.apache.org/docs/elasticsearch_adapter.html

贡献者

binbin.hou
我是老马，期待与你的下次重逢
Copyright © 2025 老马啸西风