文章目录



简述

Elasticsearch6.3开始内置支持 SQL，我们可以像操作 MySQL一样使用 Elasticsearch，这样我们就可以减少 DSL 的学习成本，这个 SQL 模块是属于 X-Pack 的一部分。Elasticsearch SQL 主要有以下几个特点：


  2. 允许我们在 Elasticsearch 使用 SQL 查询其中的数据；
  3. 支持 REST 、 JDBC 以及命令行来查询数据，任何客户端都可以使用 SQL 在 Elasticsearch 中本地搜索和聚合数据；
  4. 内部是将 SQL 翻译成 DSL 来查询数据的

安装ES

wget https://artifacts.elastic.co/downloads/elasticsearch/elasticsearch-6.3.0.zip
wget https://artifacts.elastic.co/downloads/elasticsearch/elasticsearch-6.3.0.zip.sha512

# 安装shasum
yum install perl-Digest-SHA -y
# 查看文件hash值 ，查看文件是否完整
shasum -a 512 -c elasticsearch-6.3.0.zip.sha512 

# 解压
unzip elasticsearch-6.3.0.zip

# 启动
cd elasticsearch-6.3.0/
./bin/elasticsearch

# 查看集群基本信息
http://10.250.140.14:9987

安装ES

注：ES6.3开始已经内置安装了x-pack.

#下载
wget https://artifacts.elastic.co/downloads/elasticsearch/elasticsearch-6.3.0.zip
wget https://artifacts.elastic.co/downloads/elasticsearch/elasticsearch-6.3.0.zip.sha512

# 安装shasum
yum install perl-Digest-SHA -y

# 查看文件hash值 ，查看文件是否完整
shasum -a 512 -c elasticsearch-6.3.0.zip.sha512 

# 解压
unzip elasticsearch-6.3.0.zip

# 启动
cd elasticsearch-6.3.0/
./bin/elasticsearch

# 验证
http://10.10.140.14:9200

代码示例

package com.lgd.es.test;

import org.junit.Test;

import java.sql.*;
import java.util.Properties;

/**
 * <p>Project: samples-all</p>
 * <p>Package: com.lgd.es.test</p>
 * <p>Title: </p>
 * <p>Description: </p>
 *
 * @author guodong.li
 * @version 1.0.0
 * @date 2018/6/15
 */

public class JdbcTest {

    String driver = "org.elasticsearch.xpack.sql.jdbc.jdbc.JdbcDriver";
    String elasticsearchAddress = "10.12.140.14:9200";

    public Properties connectionProperties(){
        Properties properties = new Properties();
//        properties.put("user", "test_admin");
//        properties.put("password", "x-pack-test-password");
        return properties;
    }

    @Test
    public void test(){
        try {
            Class.forName(driver);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
        String address = "jdbc:es://http://" + elasticsearchAddress;
        Properties connectionProperties = connectionProperties();

        try (Connection connection = DriverManager.getConnection(address, connectionProperties);
            Statement statement = connection.createStatement();
             ResultSet results = statement.executeQuery(
                     "SELECT name, page_count FROM library ORDER BY page_count DESC LIMIT 5")) {

            while(results.next()){
                System.out.println(results.getString(1));
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

执行结果

使用JDBC连接ElasticSearch6.3（ElasticSearch SQL JDBC）_elasticsearch sql

异常处理

current license is non-compliant for [jdbc]

java.sql.SQLInvalidAuthorizationSpecException: current license is non-compliant for [jdbc]
  at org.elasticsearch.xpack.sql.client.shared.JreHttpUrlConnection$SqlExceptionType.asException(JreHttpUrlConnection.java:306)
  at org.elasticsearch.xpack.sql.client.shared.JreHttpUrlConnection.parserError(JreHttpUrlConnection.java:183)
  at org.elasticsearch.xpack.sql.client.shared.JreHttpUrlConnection.request(JreHttpUrlConnection.java:158)
  at org.elasticsearch.xpack.sql.client.HttpClient.lambda$post$0(HttpClient.java:101)
  at org.elasticsearch.xpack.sql.client.shared.JreHttpUrlConnection.http(JreHttpUrlConnection.java:62)
  at org.elasticsearch.xpack.sql.client.HttpClient.lambda$post$1(HttpClient.java:100)
  at java.security.AccessController.doPrivileged(Native Method)
  at org.elasticsearch.xpack.sql.client.HttpClient.post(HttpClient.java:99)
  at org.elasticsearch.xpack.sql.client.HttpClient.query(HttpClient.java:77)
  at org.elasticsearch.xpack.sql.jdbc.net.client.JdbcHttpClient.query(JdbcHttpClient.java:51)
  at org.elasticsearch.xpack.sql.jdbc.jdbc.JdbcStatement.initResultSet(JdbcStatement.java:162)
  at org.elasticsearch.xpack.sql.jdbc.jdbc.JdbcStatement.execute(JdbcStatement.java:153)
  at org.elasticsearch.xpack.sql.jdbc.jdbc.JdbcStatement.executeQuery(JdbcStatement.java:42)

原因：

JDBC需要一个白金（或试用）许可证。

查看官网：https://www.elastic.co/subscriptions

使用JDBC连接ElasticSearch6.3（ElasticSearch SQL JDBC）_java_02

解决方法：

Cannot install a [PLATINUM] license unless TLS is configured or security is disabled

原因：

除非配置了TLS或禁用安全性，否则无法安装[白金]许可证。

解决方法：

elasticsearch.yml新增：

xpack.security.enabled: false