`

Elasticsearch 6.3.0 SQL功能使用案例分享

阅读更多
The best elasticsearch highlevel java rest api-----bboss      

Elasticsearch 6.3.0 官方新增的SQL功能非常不错,本文以实际案例来介绍其使用方法:

  • 通过sql实现检索功能(代码中直接操作sql,从配置中加载sql)
  • 将sql转换为dsl功能
  • 准备工作:集成Elasticsearch Restful API

1.代码中的sql检索
    @Test
	public void testQuery(){
		ClientInterface clientUtil = ElasticSearchHelper.getRestClientUtil();
		String json = clientUtil.executeHttp("/_xpack/sql?format=txt",
				"{\"query\": \"SELECT * FROM dbclobdemo\"}",
				ClientInterface.HTTP_POST
				);
		System.out.println(json);

		json = clientUtil.executeHttp("/_xpack/sql?format=json",
				"{\"query\": \"SELECT * FROM dbclobdemo\"}",
				ClientInterface.HTTP_POST
		);
		System.out.println(json);
	}

执行的结果在本文的最后给出。

2.sql转换为dsl
可以将sql转换为dsl语句
   public void testTranslate(){
		ClientInterface clientUtil = ElasticSearchHelper.getRestClientUtil();
		String json = clientUtil.executeHttp("/_xpack/sql/translate",
				"{\"query\": \"SELECT * FROM dbclobdemo\"}",
				ClientInterface.HTTP_POST
		);
		System.out.println(json);

	}

sql转换为dsl的结果:

{
    "size": 1000,
    "_source": {
        "includes": [
            "author",
            "content",
            "docClass",
            "docabstract",
            "keywords",
            "mediapath",
            "newpicPath",
            "parentDetailTpl",
            "picPath",
            "publishfilename",
            "secondtitle",
            "subtitle",
            "title",
            "titlecolor"
        ],
        "excludes": []
    },
    "docvalue_fields": [
        "auditflag",
        "channelId",
        "count",
        "createtime",
        "createuser",
        "detailtemplateId",
        "docLevel",
        "docsourceId",
        "doctype",
        "documentId",
        "docwtime",
        "flowId",
        "isdeleted",
        "isnew",
        "ordertime",
        "publishtime",
        "seq",
        "status",
        "version"
    ],
    "sort": [
        {
            "_doc": {
                "order": "asc"
            }
        }
    ]
}

3.配置文件管理sql并实现sql检索
定义一个包含sql的dsl配置文件,sql语句中包含一个channelId检索条件:
<properties>
    <!--
        sql query

    -->
    <property name="sqlQuery">
        <![CDATA[
         {"query": "SELECT * FROM dbclobdemo where channelId=#[channelId]"}
        ]]>
    </property>


</properties>

加载配置文件并实现sql检索操作 ,从外部传入检索的条件channelId
    public void testSQLQueryFromDSL(){
		ClientInterface clientUtil = ElasticSearchHelper.getConfigRestClientUtil("esmapper/sql.xml");//初始化一个加载sql配置文件的es客户端接口
		//设置sql查询的参数
		Map params = new HashMap();
		params.put("channelId",1);
		String json = clientUtil.executeHttp("/_xpack/sql","sqlQuery",params,
				ClientInterface.HTTP_POST
		);
		System.out.println(json);//打印检索结果

	}

输出检索的结果为:
{
    "columns": [
        {
            "name": "auditflag",
            "type": "long"
        },
        {
            "name": "author",
            "type": "text"
        },
        {
            "name": "channelId",
            "type": "long"
        },
        {
            "name": "content",
            "type": "text"
        },
        {
            "name": "count",
            "type": "long"
        },
        {
            "name": "createtime",
            "type": "date"
        },
        {
            "name": "createuser",
            "type": "long"
        },
        {
            "name": "detailtemplateId",
            "type": "long"
        },
        {
            "name": "docClass",
            "type": "text"
        },
        {
            "name": "docLevel",
            "type": "long"
        },
        {
            "name": "docabstract",
            "type": "text"
        },
        {
            "name": "docsourceId",
            "type": "long"
        },
        {
            "name": "doctype",
            "type": "long"
        },
        {
            "name": "documentId",
            "type": "long"
        },
        {
            "name": "docwtime",
            "type": "date"
        },
        {
            "name": "flowId",
            "type": "long"
        },
        {
            "name": "isdeleted",
            "type": "long"
        },
        {
            "name": "isnew",
            "type": "long"
        },
        {
            "name": "keywords",
            "type": "text"
        },
        {
            "name": "mediapath",
            "type": "text"
        },
        {
            "name": "newpicPath",
            "type": "text"
        },
        {
            "name": "ordertime",
            "type": "date"
        },
        {
            "name": "parentDetailTpl",
            "type": "text"
        },
        {
            "name": "picPath",
            "type": "text"
        },
        {
            "name": "publishfilename",
            "type": "text"
        },
        {
            "name": "publishtime",
            "type": "date"
        },
        {
            "name": "secondtitle",
            "type": "text"
        },
        {
            "name": "seq",
            "type": "long"
        },
        {
            "name": "status",
            "type": "long"
        },
        {
            "name": "subtitle",
            "type": "text"
        },
        {
            "name": "title",
            "type": "text"
        },
        {
            "name": "titlecolor",
            "type": "text"
        },
        {
            "name": "version",
            "type": "long"
        }
    ],
    "rows": [
        [
            0,
            "不详",
            1,
            "asdfasdfasdfasdfsdf<img name=\"imgs\" src=\"../gencode7.png\" _ewebeditor_pa_src=\"http%3A%2F%2Flocalhost%2Fcms%2FsiteResource%2Ftest%2F_webprj%2Fgencode7.png\"><br>\r\nasdfasdf<img name=\"imgs\" src=\"content_files/20180505101457109.png\" _ewebeditor_pa_src=\"http%3A%2F%2Flocalhost%2Fcms%2FsiteResource%2Ftest%2F_webprj%2Fnews%2Fcontent_files%2F20180505101457109.png\"><br>\r\n<br>",
            0,
            "2018-04-12T14:16:02.000Z",
            1,
            1,
            "普通分类",
            1,
            "无asdfasdf",
            1,
            0,
            1,
            "2018-05-06T03:30:04.000Z",
            2,
            0,
            0,
            "news",
            "uploadfiles/201803/gencode4.png",
            "",
            "2018-04-12T14:06:45.000Z",
            "1",
            "uploadfiles/201803/gencode1.png",
            "asdf.html",
            "2018-04-14T14:36:12.000Z",
            "",
            0,
            11,
            "asdf",
            "adsf",
            "#000000",
            1
        ]
    ]
}

4.完整的demo
https://gitee.com/bbossgroups/eshelloword-booter

https://github.com/bbossgroups/eshelloword-booter

5.开发交流
elasticsearch sql官方文档:

https://www.elastic.co/guide/en/elasticsearch/reference/current/xpack-sql.html

elasticsearch技术交流群:166471282

elasticsearch微信公众号:
1
0
分享到:
评论

相关推荐

    SpringBoot 操作 ElasticSearch6.3.0,提供完整的Util进行增删改查

    在本项目中,我们将探讨如何使用SpringBoot框架与Elasticsearch 6.3.0集成,实现对Elasticsearch数据库的高效操作。SpringBoot简化了配置过程,使得开发者能够快速地构建可部署的Java应用。而Elasticsearch则是一个...

    elasticsearch-6.3.0.tar

    Elasticsearch是一个强大的开源搜索引擎,它基于Lucene库构建,提供了分布式、实时、高可用性的搜索和分析功能。在处理中文文档时,由于中文的特殊性(无明显空格分隔词),需要借助合适的分词器来实现有效的索引和...

    elasticsearch-analysis-ik-6.3.0 elasticsearch- 6.3.0 分词jar包

    总的来说,"elasticsearch-analysis-ik-6.3.0"是Elasticsearch优化中文搜索体验的重要组件,通过合理的配置和使用,可以大大提高文本检索的效率和准确性。同时,结合其他如拼音分析的插件,可以进一步完善搜索功能,...

    elasticsearch-6.3.0-API文档-中文版.zip

    赠送jar包:elasticsearch-6.3.0.jar; 赠送原API文档:elasticsearch-6.3.0-javadoc.jar; 赠送源代码:elasticsearch-6.3.0-sources.jar; 赠送Maven依赖信息文件:elasticsearch-6.3.0.pom; 包含翻译后的API文档...

    elasticsearch-6.3.0.rar

    elasticsearch-6.3.0.rar

    elasticsearch-sql-7.9.2.0(maven包)

    Elasticsearch SQL是将SQL查询语句引入到Elasticsearch中的一个插件,它使得习惯于使用SQL的用户能够更加方便地与Elasticsearch进行交互。这个"elasticsearch-sql-7.9.2.0(maven包)"是针对Elasticsearch 7.9.2版本的...

    elasticsearch-6.3.0.tar.gz

    2. **解压**:使用`tar`命令解压文件,如`tar -zxvf elasticsearch-6.3.0.tar.gz`,这将在当前目录下创建一个名为`elasticsearch-6.3.0`的目录。 3. **配置**:进入解压后的目录`cd elasticsearch-6.3.0`,根据需求...

    elasticsearch-sql-2.4.3.0.zip 插件 安装包

    Elasticsearch SQL插件是为Elasticsearch设计的一个强大工具,允许用户通过SQL查询语言来操作Elasticsearch的数据。此插件的版本为2.4.3.0,提供了与传统关系型数据库相似的交互方式,使那些熟悉SQL语法的用户能够更...

    elasticsearch sql支持增删改查且支持mybatis集成

    Elasticsearch SQL是Elasticsearch的一个插件,它允许用户使用SQL语法来查询、操作Elasticsearch的数据。在早期版本中,Elasticsearch SQL主要局限于只支持SELECT查询,即数据的检索操作。然而,随着技术的发展,这...

    elasticsearch-v6.3.0镜像3

    elasticsearch-v6.3.0镜像3

    ElasticSearch SQL 教程

    本视频教程是介绍 ElasticSearch SQL 功能的视频。 If you (or others you know) are versed in SQL statements and newer to the Elasticsearch query syntax but want to benefit from the power Elasticsearch, ...

    elasticsearch-6.3.0-API文档-中英对照版.zip

    赠送jar包:elasticsearch-6.3.0.jar; 赠送原API文档:elasticsearch-6.3.0-javadoc.jar; 赠送源代码:elasticsearch-6.3.0-sources.jar; 赠送Maven依赖信息文件:elasticsearch-6.3.0.pom; 包含翻译后的API文档...

    elasticsearch-sql安装使用文档.docx

    Elasticsearch(简称ES)是一个强大的分布式全文搜索引擎,而Elasticsearch-SQL插件则使得我们能够通过熟悉的SQL语法来查询ES中的数据,极大地降低了使用门槛,尤其对熟悉关系型数据库的开发者而言。下面我们将详细...

    elasticsearch-analysis-ik-6.3.0.zip

    elasticsearch-analysis-ik-6.3.0.zip elasticsearch-analysis-ik-6.3.0.zip elasticsearch-analysis-ik-6.3.0.zip

    elasticsearch-sql插件(es2.2.0版本)

    确保使用Elasticsearch的安全功能,如认证和授权,来限制谁可以执行SQL查询。 **优化与注意事项** 1. **性能优化**: SQL查询可能不如原生的Elasticsearch查询高效,因此在处理大量数据时,需要考虑查询性能。避免...

    elasticsearch-sql-6.2.2.0

    同时,"你也可以在SQL中使用ES函数"进一步强调了Elasticsearch-SQL的灵活性,它不仅支持标准的SQL语法,还允许用户调用Elasticsearch特有的函数,如聚合、地理位置处理等,以实现更复杂的查询和分析需求。...

    elasticsearch-6.3.0.tar.zip

    4. 安全性:Elasticsearch 6.3.0 提供了内置的安全功能,包括用户身份验证、角色管理、权限控制等,增强了系统的安全性。 二、安装与启动 1. 下载:首先,你需要从官方网站下载 elasticsearch-6.3.0.tar.gz 文件,...

    elasticsearch-6.3.0-msi

    在官网下载太慢,遂移至此处,方便大家的下载使用。。。。。。。。。。。。。。。。。。。

    elasticsearch-sql-master.zip

    Elasticsearch-SQL是针对流行的全文搜索引擎Elasticsearch开发的一款插件,它允许用户使用熟悉的SQL语法来查询Elasticsearch数据。这款工具由国内开发者编写,极大地简化了对Elasticsearch的数据操作,尤其是对于...

Global site tag (gtag.js) - Google Analytics