Elasticsearch-JDBC使用

让你通过sql操作elasticsearch

Sam

2022年5月27日 • 5 min read

入门案例
https://ci.bbossgroups.com/#/Elasticsearch-JDBC
https://blog.csdn.net/m4330187/article/details/118459654
https://www.sohu.com/a/239768868_827544

一、前言

Elasticsearch 6.3以后的版本可以通过jdbc操作es，该功能还在不断的完善当中，本文介绍Elasticsearch-JDBC使用方法。

目前 Elasticsearch 的支持 SQL 命令只有以下几个：

命令	说明
DESC table	用来描述索引的字段属性
SHOW COLUMNS	功能同上，别名
SHOW FUNCTIONS	列出支持的函数列表，支持过滤
SHOW TABLES	返回索引列表
SELECT..FROM table_name WHERE..GROUP BY..HAVING..ORDER BY..LIMIT..	执行查询的sql

二、使用教程

1、导入es-jdbc maven依赖：

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

在pom中添加elastic maven库 
<repositories>
  <repository>
    <id>elastic.co</id>
    <url>https://artifacts.elastic.co/maven</url>
  </repository>
</repositories>

如果是Elasticsearch 6.3.x(版本号务必与elasticsearch版本号保持一致)，导入下面的依赖：

<dependency>
  <groupId>org.elasticsearch.plugin</groupId>
  <artifactId>jdbc</artifactId>
  <version>6.5.9</version>
</dependency>

如果是Elasticsearch 6.4.x.6.5.x,7.x(版本号务必与elasticsearch版本号保持一致)，导入以下依赖：

<dependency>
  <groupId>org.elasticsearch.plugin</groupId>
  <artifactId>x-pack-sql-jdbc</artifactId>
  <version>7.10.0</version>
</dependency>

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

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

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 {
    //启动数据源，初始化数据源
    @Before
    public void initDBSource(){

        SQLUtil.startPool("es",//ES数据源名称 for Elasticsearch 6.4.x,+
                "org.elasticsearch.xpack.sql.jdbc.EsDriver",//ES jdbc驱动
                "jdbc:es://http://192.168.137.1:9200?timezone=UTC&page.size=250",//es链接串
                "elastic","changeme",//es x-pack账号和口令
                "SELECT 1 AS result" //数据源连接校验sql
        );
    }

    /**
     * 执行一个查询
     * @throws SQLException
     */
    @Test
    public void testSelect() throws SQLException {

        //执行查询，将结果映射为HashMap集合
         List<HashMap> data =    SQLExecutor.queryListWithDBName(HashMap.class,"es","SELECT SCORE() as score,* FROM dbclobdemo ");
         System.out.println(data);

        data =    SQLExecutor.queryListWithDBName(HashMap.class,"es","SELECT SCORE() as score,* FROM dbclobdemo where detailtemplateId=?",1);
        System.out.println(data);
    }

    @Test
    public void testQuery() throws SQLException {
        initDBSource();//启动数据源
        //执行查询，将结果映射为HashMap集合,全文检索查询
        List<HashMap> data =    SQLExecutor.queryListWithDBName(HashMap.class,
                "es","SELECT * FROM hawkeye-auth-service-web-api-index-2018-06-30 where match(url_group,'synchronize_info')");
        System.out.println(data);
        //关键词精确查找
        data =    SQLExecutor.queryListWithDBName(HashMap.class,
                "es","SELECT * FROM hawkeye-auth-service-web-api-index-2018-06-30 where url_group.keyword = ?","synchronize_info");
        System.out.println(data);
    }

    /**
     * 进行模糊搜索，Elasticsearch 的搜索能力大家都知道，强！在 SQL 里面，可以用 match 关键字来写，如下：
     * @throws SQLException
     */
    @Test
    public void testMatchQuery() throws SQLException {

        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 {

        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 {

        List<HashMap> data =    SQLExecutor.queryListWithDBName(HashMap.class,"es","SHOW tables");
        System.out.println(data);
    }

    /**
     * 如 dbclob 开头的索引，注意通配符只支持 %和 _，分别表示多个和单个字符（什么，不记得了，回去翻数据库的书去！）
     * @throws SQLException
     */
    @Test
    public void testShowTablePattern() throws SQLException {

        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 {

        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 {

        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);

    }
}

如果执行的时候报错，es需获取license

可以采用正式的license或者在elasticsearch.yml文件最后添加以下配置即可：
xpack.license.self_generated.type: trial

三、注意事项

索引名含"-" 或 sql含中文

当索引名包含"-"或sql含中文时，执行查询有可能报错，前后需加上\"

_匹配一个字符，%匹配多个字符
select name as \"姓名\" from \"record-index\"

模糊搜索

_匹配一个字符，%匹配多个字符
select * from \"record-index\" where match(name, '测试_')

curl查询

POST _xpack/sql?format=txt
{
    "query": "select * from \"person-record-139\" where match(name, '兰_')"
}