文档章节

Elasticsearch 6.3.0 SQL功能使用案例分享

bboss
 bboss
发布于 06/23 01:23
字数 1399
阅读 2890
收藏 70
点赞 4
评论 4

The best elasticsearch highlevel java rest api-----bboss       

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

  • 通过sql实现检索功能(代码中直接操作sql,从配置中加载sql)
  • 将sql转换为dsl功能
  • 使用es jdbc
  • 准备工作:集成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 使用es jdbc

可以通过jdbc操作es,首先在工程中导入es jdbc maven坐标:

导入elasticsearch jdbc驱动和bboss持久层
<dependency>
  <groupId>org.elasticsearch.plugin</groupId>
  <artifactId>jdbc</artifactId>
  <version>6.3.0</version>
</dependency>
<dependency> 
    <groupId>com.bbossgroups</groupId> 
    <artifactId>bboss-persistent</artifactId> 
    <version>5.0.7.1</version> 
</dependency> 

在pom中添加elastic maven库 

<repositories>
  <repository>
    <id>elastic.co</id>
    <url>https://artifacts.elastic.co/maven</url>
  </repository>
</repositories>

通过jdbc驱动执行elasticsearch sql相关功能

  • 启动es数据源
  • 执行elasticsearch sql相关功能

直接看执行各种sql功能的代码ESJdbcTest

package com.frameworkset.sqlexecutor;
/*
 *  Copyright 2008 biaoping.yin
 *
 *  Licensed under the Apache License, Version 2.0 (the "License");
 *  you may not use this file except in compliance with the License.
 *  You may obtain a copy of the License at
 *
 *      http://www.apache.org/licenses/LICENSE-2.0
 *
 *  Unless required by applicable law or agreed to in writing, software
 *  distributed under the License is distributed on an "AS IS" BASIS,
 *  WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 *  See the License for the specific language governing permissions and
 *  limitations under the License.
 */

import com.frameworkset.common.poolman.SQLExecutor;
import com.frameworkset.common.poolman.util.SQLUtil;
import org.junit.Test;

import java.sql.SQLException;
import java.util.HashMap;
import java.util.List;

public class ESJdbcTest {

	public void initDBSource(){
		SQLUtil.startPool("es",//ES数据源名称
				"org.elasticsearch.xpack.sql.jdbc.jdbc.JdbcDriver",//ES jdbc驱动
				"jdbc:es://http://127.0.0.1:9200/timezone=UTC&page.size=250",//es链接串
				"elastic","changeme",//es x-pack账号和口令
				"SHOW tables 'dbclob%'" //数据源连接校验sql
		);
	}

	/**
	 * 执行一个查询
	 * @throws SQLException
	 */
	@Test
	public void testSelect() throws SQLException {
		initDBSource();//启动数据源
		//执行查询,将结果映射为HashMap集合
		 List<HashMap> data =	SQLExecutor.queryListWithDBName(HashMap.class,"es","SELECT SCORE() as score,content as content FROM dbclobdemo");
		 System.out.println(data);
	}

	/**
	 * 进行模糊搜索,Elasticsearch 的搜索能力大家都知道,强!在 SQL 里面,可以用 match 关键字来写,如下:
	 * @throws SQLException
	 */
	@Test
	public void testMatchQuery() throws SQLException {
		initDBSource();
		List<HashMap> data =	SQLExecutor.queryListWithDBName(HashMap.class,"es","SELECT SCORE(), * FROM dbclobdemo WHERE match(content, '_ewebeditor_pa_src') ORDER BY documentId DESC");
		System.out.println(data);

		/**
		 *还能试试 SELECT 里面的一些其他操作,如过滤,别名,如下:
		 */
		data =	SQLExecutor.queryListWithDBName(HashMap.class,"es","SELECT SCORE() as score,title as myname FROM dbclobdemo  as mytable WHERE match(content, '_ewebeditor_pa_src') and (title.keyword = 'adsf' OR title.keyword ='elastic') limit 5 ");
		System.out.println(data);
	}
	/**
	 * 分组和函数计算
	 */
	@Test
	public void testGroupQuery() throws SQLException {
		initDBSource();
		List<HashMap> data =	SQLExecutor.queryListWithDBName(HashMap.class,"es","SELECT title.keyword,max(documentId) as max_id FROM dbclobdemo as mytable group by title.keyword limit 5");
		System.out.println(data);


	}


	/**
	 * 查看所有的索引表
	 * @throws SQLException
	 */
	@Test
	public void testShowTable() throws SQLException {
		initDBSource();
		List<HashMap> data =	SQLExecutor.queryListWithDBName(HashMap.class,"es","SHOW tables");
		System.out.println(data);
	}

	/**
	 * 如 dbclob 开头的索引,注意通配符只支持 %和 _,分别表示多个和单个字符(什么,不记得了,回去翻数据库的书去!)
	 * @throws SQLException
	 */
	@Test
	public void testShowTablePattern() throws SQLException {
		initDBSource();
		List<HashMap> data =	SQLExecutor.queryListWithDBName(HashMap.class,"es","SHOW tables 'dbclob_'");
		System.out.println(data);
		data =	SQLExecutor.queryListWithDBName(HashMap.class,"es","SHOW tables 'dbclob%'");
		System.out.println(data);
	}
	/**
	 * 查看索引的字段和元数据
	 * @throws SQLException
	 */
	@Test
	public void testDescTable() throws SQLException {
		initDBSource();
		List<HashMap> data =	SQLExecutor.queryListWithDBName(HashMap.class,"es","DESC dbclobdemo");
		System.out.println(data);
		data =	SQLExecutor.queryListWithDBName(HashMap.class,"es","SHOW COLUMNS IN dbclobdemo");
		System.out.println(data);
	}

	/**
	 * 不记得 ES 支持哪些函数,只需要执行下面的命令,即可得到完整列表
	 * @throws SQLException
	 */
	@Test
	public void testShowFunctin() throws SQLException {
		initDBSource();
		List<HashMap> data =	SQLExecutor.queryListWithDBName(HashMap.class,"es","SHOW FUNCTIONS");
		System.out.println(data);
		//同样支持通配符进行过滤:
		data =	SQLExecutor.queryListWithDBName(HashMap.class,"es","SHOW FUNCTIONS 'S__'");
		System.out.println(data);

	}
}

5.完整的demo

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

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

6.开发交流

elasticsearch sql官方文档:

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

elasticsearch技术交流群:166471282

elasticsearch微信公众号:

bboss微信公众号:bbossgroups

© 著作权归作者所有

共有 人打赏支持
bboss

bboss

粉丝 87
博文 52
码字总数 50371
作品 8
长沙
程序员
加载中

评论(4)

JornTang
JornTang
不错不错加油
吐槽的达达仔
吐槽的达达仔
日期怎么处理呢??
光脚满地跑
光脚满地跑
这个新版本的功能真不错,映射sql的方法可以很轻松的构建结果集咯。感谢博主~
bboss
bboss
What's your problem?
bboss elasticsearch v5.0.7.8 发布

The best elasticsearch highlevel java rest api-----bboss bboss elasticsearch v5.0.7.8发布 功能改进 1.新增spring boot starter模块,全面支持spring boot starter风格,支持单ES集群和......

bboss
06/27
0
0
bboss elasticsearch v5.0.6.2 发布

The best elasticsearch highlevel java rest api-----bboss bboss elasticsearch v5.0.6.2 发布 v5.0.6.2新增功能及改进: 1.升级最新的bboss版本到5.0.5.7 2.新增bboss es rest boot模块,......

bboss
04/29
0
0
当ES赶超Redis,这份ES进修攻略不容错过!

从4月DB-Engines最新发布的全球数据库排名中,我们赫然发现ElasticSearch逆袭超越了Redis,从原先的第9名上升至第8名,而Redis则落后一名,排在了其后。 事实上,这场逆袭并不算太让人意外。...

DBAplus社群
04/15
0
0
elasticsearch 集群部署

Elasticsearch是一个分布式搜索服务,提供Restful API,底层基于Lucene,采用多shard的方式保证数据安全,并且提供自动resharding的功能,github等大型的站点也都采用Elasticsearch作为其搜索...

wx5b30a7c097b85
06/27
0
0
bboss v5.0.6.8 发布,持久支持Elasticsearch SQL

bboss v5.0.6.8发布,持久层支持Elasticsearch SQL和Elasticsearch JDBC. v5.0.6.8功能改进 持久层支持支持Elasticsearch SQL,使用参考文档:玩转Elasticsearch SQL功能 解决持久层/elasti...

bboss
07/02
0
0
Java搜索引擎选择: Elasticsearch与Solr(转)

Elasticsearch简介 Elasticsearch是一个实时的分布式搜索和分析引擎。它可以帮助你用前所未有的速度去处理大规模数据。 它可以用于全文搜索,结构化搜索以及分析,当然你也可以将这三者进行组...

easonjim
2017/11/13
0
0
spring-data-elasticsearch 基本案例详解(三)

『 风云说:能分享自己职位的知识的领导是个好领导。 』 运行环境:JDK 7 或 8,Maven 3.0+ 技术栈:SpringBoot 1.5+, Spring Data Elasticsearch 1.5+ ,ElasticSearch 2.3.2 本文提纲 一、...

夜黑人模糊灬
05/13
0
0
Elasticsearch如何实现 SQL语句中 Group By 和 Limit 的功能

有 SQL 背景的同学在学习 Elasticsearch 时,面对一个查询需求,不由自主地会先思考如何用 SQL 来实现,然后再去想 Elasticsearch 的 Query DSL 如何实现。那么本篇就给大家讲一条常见的 SQ...

rockybean
05/21
0
0
Elasticsearch中文分词研究

一、ES分析器简介 ES是一个实时搜索与数据分析引擎,为了完成搜索功能,必须对原始数据进行分析、拆解,以建立索引,从而实现搜索功能; ES对数据分析、拆解过程如下: 首先,将一块文本分成...

zhaipengfei1231
04/18
0
0
ElasticSearch2.3.3离线搭建(windows+Linux)

1、ElasticSearch介绍 ElasticSearch 是一个基于Lucene的搜索服务器。它提供了一个分布式多用户能力的全文搜索引擎,基于RESTful web接口。Elasticsearch 是用Java开发的,并作为Apache许可条...

Coder2018
2017/05/23
0
3

没有更多内容

加载失败,请刷新页面

加载更多

下一页

华为nova3超级慢动作酷玩抖音,没有办法我就是这么强大

华为nova3超级慢动作酷玩抖音,没有办法我就是这么强大!华为nova3超级慢动作酷玩抖音,没有办法我就是这么强大! 在华为最新发布的nova 3手机上,抖音通过华为himedia SDK集成了60fps、超级...

华为终端开放实验室
16分钟前
0
0
多 SSH Key 实现同一台服务器部署多 Git 仓库

本文以以下需求为背景,介绍详细的做法: 需在同一台服务器同时部署两个不同的 Github 仓库(对 Bitbucket 等 git 服务同样适用) root 用户可在远程登录 SSH 后附上预期的 SSH Key 进行 gi...

yeahlife
18分钟前
0
0
003. es6数值的扩展

一、普通扩展 Number 方法,将字符串、数值转为十进制 : Number('0b111') Number.isFinite() 用来检查一个数值是否为有限的:Number.isFinite(15) Number.isNan() 用来检查一个值是否为NaN N...

秋季长青
32分钟前
0
0
rabbitmq学习(二)

基本队列:Producer直接发送信息到Queue中,Consumer接收Queue发送过来的信息 简而言之,一个生产者发送信息,一个消费者接收信息。 获取连接工具类: package com.example.demo.utils;i...

人觉非常君
37分钟前
0
0
C语言数组和指针的语法糖

对于C语言,我可以这样秀:比如当创建一个数组arr[n]之后,一般我们去遍历数组的时候是for (int i = 0; i < n; i++) { a[i]; }但是我知道下表访问符[]是个语法糖,也就是说a[i]在编译器看来是...

ustbgaofan
41分钟前
0
0
Call to undefined function bcmath()的解决方法

乐意黎的ECS主机环境,Centos7.2 + PHP7 由于使用了bcdiv()函数,运行时总在抛错。 Fatal error: Call to undefined function bcmath() in /usr/loca/apache/htdocs/... on line 4 一查得知:......

dragon_tech
46分钟前
0
0
css优先级

..

architect刘源源
51分钟前
0
0
【转】Twitter的分布式自增ID算法snowflake

结构 snowflake的结构如下(每部分用-分开): 0 - 0000000000 0000000000 0000000000 0000000000 0 - 00000 - 00000 - 000000000000 第一位为未使用,接下来的41位为毫秒级时间(41位的长度可以...

talen
55分钟前
0
0
hive支持行级修改

Hive从0.14版本开始支持事务和行级更新,但缺省是不支持的,需要一些附加的配置。要想支持行级insert、update、delete,需要配置Hive支持事务。 一、Hive具有ACID语义事务的使用场景 1. 流式...

hblt-j
今天
0
0
Python---scrapy框架的学习

scrapy框架的学习 先熟悉下scrapy项目结构: scrapyTest/scrapyTest/ _init_.py用来初始化项目信息 scrapyTest/scrapyTest/ items.py文件为爬虫项目的数据容器文件,主要用来定义我们的数据....

android-key
今天
1
0

没有更多内容

加载失败,请刷新页面

加载更多

下一页

返回顶部
顶部