elasticsearch-sql

0.简介

ES查询有点复杂(term match bool agg filter geo)，所以现在就有插件用SQL查ES。目前只支持查询，不支持写入

1.elasticsearch安装sql插件

github地址：https://github.com/NLPchina/elasticsearch-sql

# 安装插件 (elasticsearch 6.4.0)
./bin/elasticsearch-plugin install https://github.com/NLPchina/elasticsearch-sql/releases/download/6.4.0.0/elasticsearch-sql-6.4.0.0.zip

2.SQL使用(data索引是之前创建的)

# 获取索引列表
POST /_xpack/sql?format=txt
{
    "query": "SHOW tables"
}

# 查询data索引
POST /_xpack/sql?format=txt
{
    "query": "DESC data"
}

# 查询data索引有哪些字段 
POST /_xpack/sql?format=txt
{
    "query": "SHOW COLUMNS IN data"
}

# 查询目前支持的函数
POST /_xpack/sql?format=txt
{
    "query": "SHOW FUNCTIONS"
}

# 查询data索引下数据
POST /_xpack/sql?format=txt
{
    "query": "SELECT * FROM data"
}

# 有空自己去看看
官方文档：https://www.elastic.co/guide/en/elasticsearch/reference/current/sql-syntax-select.html

3.java使用elasticsearch-sql

# 下载驱动
https://www.elastic.co/downloads/jdbc-client

4.测试(MMP 需要白金账号 有空还是自己写一套吧)

Test
public void test() throws Exception {

    String url = "jdbc:es://172.22.2.133:9200";

    Properties properties = new Properties();
    // MMP 需要白金账号
    properties.put("user", "");
    properties.put("password", "");

    JdbcDataSource dataSource = new JdbcDataSource();
    dataSource.setUrl(url);
    dataSource.setProperties(properties);

    Connection connection = dataSource.getConnection();
    Statement statement = connection.createStatement();
    ResultSet results = statement.executeQuery("SELECT name, age, address FROM data");
    while (results.next()) {
        String name = results.getString(results.findColumn("name"));
        String address = results.getString(results.findColumn("address"));
        String age = results.getString(results.findColumn("age"));

        System.out.println("name:" + name + " age:" + age + " address:{3}" + address);
    }
}

源码：https://gitee.com/jsjack_wang/springboot-demo dev-es-sql分支