Datagrip 2020添加 Elasticsearch 连接

Datagrip 2020添加 Elasticsearch 连接

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

#下载ES
wget .7.0.zip
wget .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.版本开始内置支持 SQL

ES驱动

或下载

ESJDBC驱动

2.添加驱动到datagrip

.添加连接数据源

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

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

修改成0天试用版

# 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..1.jar复制一份出来

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

LicenseVerifier.java

package license;import *;
import bytes.*;
import java.util.*;
import java.security.*;
import xcontent.*;
import org.apache.lucene.util.*;
import 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 ;import io.*;
import java.*;
import *;
import file.*;
import java.io.*;
import java.util.jar.*;public class XPackBuild
{public static final XPackBuild CURRET;private String shortHash;private String date;@SuppressForbidden(reason = looks up path of xpack.jar directly)static Path getElasticsearchCodebase() {final URL url = getProtectionDomain().getCodeSource().getLocation();try {return PathUtils.get(());}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;}CURRET = new XPackBuild(shortHash, date);}
}

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

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

       ①：，邮箱（重要，获取下载链接），Country选择China，其他信息随意填写，点击Send ! 

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

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

           将 type:basic 替换为 type:platinum # 基础班变更为铂金版
           将 expiry_date_in_millis:1561420799999替换为 expiry_date_in_millis:107746200000# 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 [PLATIUM] license unless TLS is configured or security is disabled的错误，解决方法在elasticsearch.yml新增：

       xpack.: 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 = 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 = ();  
ResultSet results = (
SELECT firstname, account_number FROM bank ORDER BY account_number DESC LIMIT 5);  
while(()){  
println(results.getString(firstname));  
}  
}catch (Exception e){  
e.printStackTrace();  
}  }  }