Datagrip 默认没有 Elasticsearch 相应的驱动及连接，需如下进行操作

 

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

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

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

# 解压
unzip elasticsearch-6.7.0.zip

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

# 验证
http://192.168.10.92:9200

 

 

1.下载ES驱动 ,并复制到datagrip (jdbc_drivers)驱动目录下--Elasticsearch从6.3版本开始内置支持 SQL

ES驱动

或官网下载

ES官网JDBC驱动

2.添加驱动到datagrip

3.添加连接数据源

报错信息：Failed to retrieve table types. current license is non-compliant for [jdbc] (326 ms)

# curl -XGET http://localhost:9200/_license
{
  "license" : {
    "status" : "active",
    "uid" : "e5e0e752-75f8-486f-be75-a82ca86ca707",
    "type" : "basic",
    "issue_date" : "2020-12-15T01:53:31.620Z",
    "issue_date_in_millis" : 1607997211620,
    "max_nodes" : 1000,
    "issued_to" : "elk-es",
    "issuer" : "elasticsearch",
    "start_date_in_millis" : -1
  }
}

修改成30天试用版

# curl -X POST "localhost:9200/_license/start_trial?acknowledge=true&pretty"
{
  "acknowledged" : true,
  "trial_was_started" : true,
  "type" : "trial"
}

或者  修改配置

（一）在ES安装路径下D:\elasticsearch-xx\modules\x-pack\x-pack-core目录下找到x-pack-core-6.3.1.jar复制一份出来

（二）新建两个java文件分别命名为LicenseVerifier.java和XPackBuild.java，将下面代码黏贴

LicenseVerifier.java

package org.elasticsearch.license;
 
import java.nio.*;
import org.elasticsearch.common.bytes.*;
import java.util.*;
import java.security.*;
import org.elasticsearch.common.xcontent.*;
import org.apache.lucene.util.*;
import org.elasticsearch.core.internal.io.*;
import java.io.*;
 
 
public class LicenseVerifier
{
    public static boolean verifyLicense(final License license, final byte[] encryptedPublicKeyData) {
       return true;
    }
    
    public static boolean verifyLicense(final License license) {
         return true;
    }
}

XPackBuild.java

package org.elasticsearch.xpack.core;
 
import org.elasticsearch.common.io.*;
import java.net.*;
import org.elasticsearch.common.*;
import java.nio.file.*;
import java.io.*;
import java.util.jar.*;
 
public class XPackBuild
{
    public static final XPackBuild CURRENT;
    private String shortHash;
    private String date;
    
    @SuppressForbidden(reason = "looks up path of xpack.jar directly")
    static Path getElasticsearchCodebase() {
        final URL url = XPackBuild.class.getProtectionDomain().getCodeSource().getLocation();
        try {
            return PathUtils.get(url.toURI());
        }
        catch (URISyntaxException bogus) {
            throw new RuntimeException(bogus);
        }
    }
    
    XPackBuild(final String shortHash, final String date) {
        this.shortHash = shortHash;
        this.date = date;
    }
    
    public String shortHash() {
        return this.shortHash;
    }
    
    public String date() {
        return this.date;
    }
    
    static {
        final Path path = getElasticsearchCodebase();
        String shortHash = null;
        String date = null;
        Label_0157: {
           
            shortHash = "Unknown";
            date = "Unknown";
        }
        CURRENT = new XPackBuild(shortHash, date);
    }
}

(三)使用javac 编译这两个java文件，需要引入import的依赖，使用-cp参数，需要依赖原本的x-pack-core-6.3.1.jar，lucene-core-7.3.1.jar，elasticsearch-6.3.0.jar，elasticsearch-core-6.3.0.jar这四个java包（windows下使用分号分隔jar路径，linux下使用冒号分隔，后三个jar包的路径为D:\elasticsearch-6.3.0\lib） ``` javac -cp "x-pack-core-6.3.0.jar路径;lucene-core-7.3.1.jar路径;elasticsearch-6.3.0.jar路径;elasticsearch-core-6.3.0.jar路径" LicenseVerifier.java路径 javac -cp "x-pack-core-6.3.0.jar路径;lucene-core-7.3.1.jar路径;elasticsearch-6.3.0.jar路径;elasticsearch-core-6.3.0.jar路径" XPackBuild.java路径

(四)将编译生成的LicenseVerifier.class覆盖复制出来的x-pack-core-6.3.0.jar中目录org/elasticsearch/license下的LicenseVerifier.class，将生成的XPackBuild.class覆盖org/elasticsearch/xpack/code目录下的XPackBuild.class。 (五)将覆盖成功后的x-pack-core-6.3.0.jar复制回D:\elasticsearch-6.3.0\modules\x-pack\x-pack-core目录下覆盖原来的。 (六)获取 license 证书

       ①：https://license.elastic.co/registration填些用户名，邮箱（重要，获取下载链接），Country选择China，其他信息随意填写，点击Send ! 

               https://www.elastic.co/subscriptions

               

       ②：打开邮箱获取的地址，将下载后的文件改名为license.json

          

      ③修改文件中的内容，将两个属性改为以下

           将 "type":"basic" 替换为 "type":"platinum" # 基础班变更为铂金版
           将 "expiry_date_in_millis":1561420799999替换为 "expiry_date_in_millis":3107746200000# 1年变为50年

     ④使用curl替换 license(license.json指的是刚刚下载修改属性后的证书，要开启elasticsearch服务)

        curl -XPUT 'http://127.0.0.1:9200/_xpack/license?acknowledge=true' -d @license.json

       

   ⑤可能遇到Cannot install a [PLATINUM] license unless TLS is configured or security is disabled的错误，解决方法在elasticsearch.yml新增：

       xpack.security.enabled: false

   ⑥上传后查看证书时间http://127.0.0.1:9200/_license

(五)到此结束！

 

package com.hts;  
import java.sql.Connection;  
import java.sql.DriverManager;  
import java.sql.ResultSet;  
import java.sql.Statement;  
import java.util.Properties;  

public class ESJdbcTest {  

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

public static Properties connectionProperties(){  
Properties properties = new Properties();  
        //如果集群设置了密码
        //properties.put("user", "test_admin");  
        //properties.put("password", "x-pack-test-password");  
return properties;  
}  
public static void main(String[] args) {  

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 firstname, account_number FROM bank ORDER BY account_number DESC LIMIT 5");  
while(results.next()){  
System.out.println(results.getString("firstname"));  
}  
}catch (Exception e){  
e.printStackTrace();  
}  
 
}  

}