Elasticsearch插件：elasticsearch-sql安装和使用

使用此插件，您可以使用熟悉的SQL语法查询elasticsearch。您还可以在SQL中使用ES函数。

有两种方法可以使用此插件：

使用其余的api

http://localhost:9200/_sql?sql=select * from indexName limit 10

2. 或者通过浏览器访问http://localhost:9200/_plugin/sql

Web前端概述

安装

安装为插件：版本（sql插件与Elasticsearch的版本对应关系）

elasticsearch version

latest version

remarks

branch

1.x

1.7.6

tested against elastic 1.7.6

elastic1.x

2.0.0

2.0.4

delete commands not supported

elastic2.0

2.1.0

2.1.0.2

delete commands not supported

elastic2.1

2.1.1

2.1.1.1

delete commands not supported

elastic2.1.1

2.1.2

2.1.2.0

delete commands not supported

elastic2.1.2

2.2.0

2.2.0.1

delete commands not supported

elastic2.2.0

2.2.1

2.2.1.0

delete commands not supported

elastic2.2.1

2.3.0

2.3.0.0

delete commands not supported

elastic2.3.0

2.3.1

2.3.1.1

delete commands not supported

elastic2.3.1

2.3.2

2.3.2.0

delete commands not supported

elastic2.3.2

2.3.3

2.3.3.0

delete commands not supported

elastic2.3.3

2.3.4

2.3.4.0

delete commands not supported

elastic2.3.4

2.3.5

2.3.5.0

delete commands not supported

elastic2.3.5

2.4.0

2.4.0.1

delete commands not supported

elastic2.4.0

2.4.1

2.4.1.0

delete commands not supported

elastic2.4.1

2.4.2

2.4.2.1

delete commands not supported

elastic2.4.2

2.4.3

2.4.3.0

delete commands not supported

elastic2.4.3

2.4.4

2.4.4.0

delete commands not supported

elastic2.4.4

2.4.5

2.4.5.0

delete commands not supported

elastic2.4.5

2.4.6

2.4.6.0

delete commands not supported

elastic2.4.6

5.0.1

5.0.1.0

delete commands not supported

elastic5.0.1

5.1.1

5.1.1.0

delete commands not supported

elastic5.1.1

5.1.2

5.1.2.0

delete commands not supported

elastic5.1.2

5.2.0

5.2.0.0

delete commands not supported

elastic5.2.0

5.2.1

5.2.1.0

delete commands not supported

elastic5.2.1

5.2.2

5.2.2.0

delete commands not supported

elastic5.2.2

5.3.0

5.3.0.0

delete commands not supported

elastic5.3.0

5.3.1

5.3.1.0

delete commands not supported

elastic5.3.1

5.3.2

5.3.2.0

delete commands not supported

elastic5.3.2

5.3.3

5.3.3.0

delete commands not supported

elastic5.3.3

5.4.0

5.4.0.0

delete commands not supported

elastic5.4.0

5.4.1

5.4.1.0

delete commands not supported

elastic5.4.1

5.4.2

5.4.2.0

delete commands not supported

elastic5.4.2

5.4.3

5.4.3.0

delete commands not supported

elastic5.4.3

5.5.0

5.5.0.1

delete commands not supported

elastic5.5.0

5.5.1

5.5.1.0

delete commands not supported

elastic5.5.1

5.5.2

5.5.2.0

delete commands not supported

elastic5.5.2

5.5.3

5.5.3.0

delete commands not supported

elastic5.5.3

5.6.0

5.6.0.0

delete commands not supported

elastic5.6.0

5.6.1

5.6.1.0

delete commands not supported

elastic5.6.1

5.6.2

5.6.2.0

delete commands not supported

elastic5.6.2

5.6.3

5.6.3.0

delete commands not supported

elastic5.6.3

5.6.4

5.6.4.0

delete commands not supported

elastic5.6.4

5.6.5

5.6.5.0

delete commands not supported

elastic5.6.5

5.6.6

5.6.6.0

elastic5.6.6

5.6.7

5.6.7.0

elastic5.6.7

5.6.8

5.6.8.0

elastic5.6.8

5.6.9

5.6.9.0

elastic5.6.9

5.6.10

5.6.10.0

elastic5.6.10

6.0.0

6.0.0.0

elastic6.0.0

6.0.1

6.0.1.0

elastic6.0.1

6.1.0

6.1.0.0

elastic6.1.0

6.1.1

6.1.1.0

elastic6.1.1

6.1.2

6.1.2.0

elastic6.1.2

6.1.3

6.1.3.0

elastic6.1.3

6.1.4

6.1.4.0

elastic6.1.4

6.2.0

6.2.0.0

elastic6.2.0

6.2.1

6.2.1.0

elastic6.2.1

6.2.2

6.2.2.0

elastic6.2.2

6.2.3

6.2.3.0

elastic6.2.3

6.2.4

6.2.4.0

elastic6.2.4

6.3.0

6.3.0.0

elastic6.3.0

6.3.1

6.3.1.0

elastic6.3.1

不同Elasticsearch版本的安装命令

Elasticsearch 1.x

./bin/plugin -u https://github.com/NLPchina/elasticsearch-sql/releases/download/1.7.6/elasticsearch-sql-1.7.6.zip --install sql
1

Elasticsearch 2.0.0

./bin/plugin install https://github.com/NLPchina/elasticsearch-sql/releases/download/2.0.4/elasticsearch-sql-2.0.4.zip 
1

Elasticsearch 2.1.0

./bin/plugin install https://github.com/NLPchina/elasticsearch-sql/releases/download/2.1.0.2/elasticsearch-sql-2.1.0.2.zip 
1

Elasticsearch 2.1.1

./bin/plugin install https://github.com/NLPchina/elasticsearch-sql/releases/download/2.1.1.1/elasticsearch-sql-2.1.1.1.zip 
1

Elasticsearch 2.1.2

./bin/plugin install https://github.com/NLPchina/elasticsearch-sql/releases/download/2.1.2.0/elasticsearch-sql-2.1.2.0.zip 
1

Elasticsearch 2.2.0

./bin/plugin install https://github.com/NLPchina/elasticsearch-sql/releases/download/2.2.0.1/elasticsearch-sql-2.2.0.1.zip 
1

Elasticsearch 2.2.1

./bin/plugin install https://github.com/NLPchina/elasticsearch-sql/releases/download/2.2.1.0/elasticsearch-sql-2.2.1.0.zip 
1

Elasticsearch 2.3.0

./bin/plugin install https://github.com/NLPchina/elasticsearch-sql/releases/download/2.3.0.0/elasticsearch-sql-2.3.0.0.zip 
1

Elasticsearch 2.3.1

./bin/plugin install https://github.com/NLPchina/elasticsearch-sql/releases/download/2.3.1.1/elasticsearch-sql-2.3.1.1.zip 
1

Elasticsearch 2.3.2

./bin/plugin install https://github.com/NLPchina/elasticsearch-sql/releases/download/2.3.2.0/elasticsearch-sql-2.3.2.0.zip 
1

Elasticsearch 2.3.3

./bin/plugin install https://github.com/NLPchina/elasticsearch-sql/releases/download/2.3.3.0/elasticsearch-sql-2.3.3.0.zip 
1

Elasticsearch 2.3.4

./bin/plugin install https://github.com/NLPchina/elasticsearch-sql/releases/download/2.3.4.0/elasticsearch-sql-2.3.4.0.zip 
1

Elasticsearch 2.3.5

./bin/plugin install https://github.com/NLPchina/elasticsearch-sql/releases/download/2.3.5.0/elasticsearch-sql-2.3.5.0.zip 
1

Elasticsearch 2.4.0

./bin/plugin install https://github.com/NLPchina/elasticsearch-sql/releases/download/2.4.0.1/elasticsearch-sql-2.4.0.1.zip
1

Elasticsearch 2.4.1

./bin/plugin install https://github.com/NLPchina/elasticsearch-sql/releases/download/2.4.1.0/elasticsearch-sql-2.4.1.0.zip
1

Elasticsearch 2.4.2

./bin/plugin install https://github.com/NLPchina/elasticsearch-sql/releases/download/2.4.2.1/elasticsearch-sql-2.4.2.1.zip
1

Elasticsearch 2.4.3

./bin/plugin install https://github.com/NLPchina/elasticsearch-sql/releases/download/2.4.3.0/elasticsearch-sql-2.4.3.0.zip
1

Elasticsearch 2.4.4

./bin/plugin install https://github.com/NLPchina/elasticsearch-sql/releases/download/2.4.4.0/elasticsearch-sql-2.4.4.0.zip
1

Elasticsearch 2.4.5

./bin/plugin install https://github.com/NLPchina/elasticsearch-sql/releases/download/2.4.5.0/elasticsearch-sql-2.4.5.0.zip
1

Elasticsearch 2.4.6

./bin/plugin install https://github.com/NLPchina/elasticsearch-sql/releases/download/2.4.6.0/elasticsearch-sql-2.4.6.0.zip
1

Elasticsearch 5.0.1

./bin/elasticsearch-plugin install https://github.com/NLPchina/elasticsearch-sql/releases/download/5.0.1/elasticsearch-sql-5.0.1.0.zip
1

Elasticsearch 5.1.1

./bin/elasticsearch-plugin install https://github.com/NLPchina/elasticsearch-sql/releases/download/5.1.1.0/elasticsearch-sql-5.1.1.0.zip
1

Elasticsearch 5.1.2

./bin/elasticsearch-plugin install https://github.com/NLPchina/elasticsearch-sql/releases/download/5.1.2.0/elasticsearch-sql-5.1.2.0.zip
1

Elasticsearch 5.2.0

./bin/elasticsearch-plugin install https://github.com/NLPchina/elasticsearch-sql/releases/download/5.2.0.0/elasticsearch-sql-5.2.0.0.zip
1

Elasticsearch 5.2.1

./bin/elasticsearch-plugin install https://github.com/NLPchina/elasticsearch-sql/releases/download/5.2.1.0/elasticsearch-sql-5.2.1.0.zip
1

Elasticsearch 5.2.2

./bin/elasticsearch-plugin install https://github.com/NLPchina/elasticsearch-sql/releases/download/5.2.2.0/elasticsearch-sql-5.2.2.0.zip
1

Elasticsearch 5.3.0

./bin/elasticsearch-plugin install https://github.com/NLPchina/elasticsearch-sql/releases/download/5.3.0.0/elasticsearch-sql-5.3.0.0.zip
1

Elasticsearch 5.3.1

./bin/elasticsearch-plugin install https://github.com/NLPchina/elasticsearch-sql/releases/download/5.3.1.0/elasticsearch-sql-5.3.1.0.zip
1

Elasticsearch 5.3.2

./bin/elasticsearch-plugin install https://github.com/NLPchina/elasticsearch-sql/releases/download/5.3.2.0/elasticsearch-sql-5.3.2.0.zip
1

Elasticsearch 5.3.3

./bin/elasticsearch-plugin install https://github.com/NLPchina/elasticsearch-sql/releases/download/5.3.3.0/elasticsearch-sql-5.3.3.0.zip
1

Elasticsearch 5.4.0

./bin/elasticsearch-plugin install https://github.com/NLPchina/elasticsearch-sql/releases/download/5.4.0.0/elasticsearch-sql-5.4.0.0.zip
1

Elasticsearch 5.4.1

./bin/elasticsearch-plugin install https://github.com/NLPchina/elasticsearch-sql/releases/download/5.4.1.0/elasticsearch-sql-5.4.1.0.zip
1

Elasticsearch 5.4.2

./bin/elasticsearch-plugin install https://github.com/NLPchina/elasticsearch-sql/releases/download/5.4.2.0/elasticsearch-sql-5.4.2.0.zip
1

Elasticsearch 5.4.3

./bin/elasticsearch-plugin install https://github.com/NLPchina/elasticsearch-sql/releases/download/5.4.3.0/elasticsearch-sql-5.4.3.0.zip
1

Elasticsearch 5.5.0

./bin/elasticsearch-plugin install https://github.com/NLPchina/elasticsearch-sql/releases/download/5.5.0.0/elasticsearch-sql-5.5.0.1.zip
1

Elasticsearch 5.5.1

./bin/elasticsearch-plugin install https://github.com/NLPchina/elasticsearch-sql/releases/download/5.5.1.0/elasticsearch-sql-5.5.1.0.zip
1

Elasticsearch 5.5.2

./bin/elasticsearch-plugin install https://github.com/NLPchina/elasticsearch-sql/releases/download/5.5.2.0/elasticsearch-sql-5.5.2.0.zip
1

Elasticsearch 5.5.3

./bin/elasticsearch-plugin install https://github.com/NLPchina/elasticsearch-sql/releases/download/5.5.3.0/elasticsearch-sql-5.5.3.0.zip
1

Elasticsearch 5.6.0

./bin/elasticsearch-plugin install https://github.com/NLPchina/elasticsearch-sql/releases/download/5.6.0.0/elasticsearch-sql-5.6.0.0.zip
1

Elasticsearch 5.6.1

./bin/elasticsearch-plugin install https://github.com/NLPchina/elasticsearch-sql/releases/download/5.6.1.0/elasticsearch-sql-5.6.1.0.zip
1

Elasticsearch 5.6.2

./bin/elasticsearch-plugin install https://github.com/NLPchina/elasticsearch-sql/releases/download/5.6.2.0/elasticsearch-sql-5.6.2.0.zip
1

Elasticsearch 5.6.3

./bin/elasticsearch-plugin install https://github.com/NLPchina/elasticsearch-sql/releases/download/5.6.3.0/elasticsearch-sql-5.6.3.0.zip
1

Elasticsearch 5.6.4

./bin/elasticsearch-plugin install https://github.com/NLPchina/elasticsearch-sql/releases/download/5.6.4.0/elasticsearch-sql-5.6.4.0.zip
1

Elasticsearch 5.6.5

./bin/elasticsearch-plugin install https://github.com/NLPchina/elasticsearch-sql/releases/download/5.6.5.0/elasticsearch-sql-5.6.5.0.zip
1

Elasticsearch 5.6.6

./bin/elasticsearch-plugin install https://github.com/NLPchina/elasticsearch-sql/releases/download/5.6.6.0/elasticsearch-sql-5.6.6.0.zip
1

Elasticsearch 5.6.7

./bin/elasticsearch-plugin install https://github.com/NLPchina/elasticsearch-sql/releases/download/5.6.7.0/elasticsearch-sql-5.6.7.0.zip
1

Elasticsearch 5.6.8

./bin/elasticsearch-plugin install https://github.com/NLPchina/elasticsearch-sql/releases/download/5.6.8.0/elasticsearch-sql-5.6.8.0.zip
1

Elasticsearch 5.6.9

./bin/elasticsearch-plugin install https://github.com/NLPchina/elasticsearch-sql/releases/download/5.6.9.0/elasticsearch-sql-5.6.9.0.zip
1

Elasticsearch 5.6.10

./bin/elasticsearch-plugin install https://github.com/NLPchina/elasticsearch-sql/releases/download/5.6.10.0/elasticsearch-sql-5.6.10.0.zip
1

Elasticsearch 6.0.0

./bin/elasticsearch-plugin install https://github.com/NLPchina/elasticsearch-sql/releases/download/6.0.0.0/elasticsearch-sql-6.0.0.0.zip
1

Elasticsearch 6.0.1

./bin/elasticsearch-plugin install https://github.com/NLPchina/elasticsearch-sql/releases/download/6.0.1.0/elasticsearch-sql-6.0.1.0.zip
1

Elasticsearch 6.1.0

./bin/elasticsearch-plugin install https://github.com/NLPchina/elasticsearch-sql/releases/download/6.1.0.0/elasticsearch-sql-6.1.0.0.zip
1

Elasticsearch 6.1.1

./bin/elasticsearch-plugin install https://github.com/NLPchina/elasticsearch-sql/releases/download/6.1.1.0/elasticsearch-sql-6.1.1.0.zip
1

Elasticsearch 6.1.2

./bin/elasticsearch-plugin install https://github.com/NLPchina/elasticsearch-sql/releases/download/6.1.2.0/elasticsearch-sql-6.1.2.0.zip
1

Elasticsearch 6.1.3

./bin/elasticsearch-plugin install https://github.com/NLPchina/elasticsearch-sql/releases/download/6.1.3.0/elasticsearch-sql-6.1.3.0.zip
1

Elasticsearch 6.1.4

./bin/elasticsearch-plugin install https://github.com/NLPchina/elasticsearch-sql/releases/download/6.1.4.0/elasticsearch-sql-6.1.4.0.zip
1

Elasticsearch 6.2.0

./bin/elasticsearch-plugin install https://github.com/NLPchina/elasticsearch-sql/releases/download/6.2.0.0/elasticsearch-sql-6.2.0.0.zip
1

Elasticsearch 6.2.1

./bin/elasticsearch-plugin install https://github.com/NLPchina/elasticsearch-sql/releases/download/6.2.1.0/elasticsearch-sql-6.2.1.0.zip
1

Elasticsearch 6.2.2

./bin/elasticsearch-plugin install https://github.com/NLPchina/elasticsearch-sql/releases/download/6.2.2.0/elasticsearch-sql-6.2.2.0.zip
1

Elasticsearch 6.2.3

./bin/elasticsearch-plugin install https://github.com/NLPchina/elasticsearch-sql/releases/download/6.2.3.0/elasticsearch-sql-6.2.3.0.zip
1

Elasticsearch 6.2.4

./bin/elasticsearch-plugin install https://github.com/NLPchina/elasticsearch-sql/releases/download/6.2.4.0/elasticsearch-sql-6.2.4.0.zip
1

Elasticsearch 6.3.0

./bin/elasticsearch-plugin install https://github.com/NLPchina/elasticsearch-sql/releases/download/6.3.0.0/elasticsearch-sql-6.3.0.0.zip
1

Elasticsearch 6.3.1

./bin/elasticsearch-plugin install https://github.com/NLPchina/elasticsearch-sql/releases/download/6.3.1.0/elasticsearch-sql-6.3.1.1.zip
1

After doing this, you need to restart the Elasticsearch server. Otherwise you may get errors likeInvalid index name [sql], must not start with '']; ","status":400}.

执行此操作后，您需要重新启动Elasticsearch服务器。否则你可能会收到错误

Invalid index name [sql], must not start with '']; ","status":400}。
1

重新启动Elasticsearch服务器

#停止Elasticsearch进程
kill `ps -ef | grep Elasticsearch | grep -v grep | awk '{print $2}'`
#启动Elasticsearch进程
/elasticsearch-5.4.3/bin/elasticsearch -d
1
2
3
4

基本用法

在elasticsearch 1.x / 2.x上，可以直接访问elasticsearch-sql web前端页面：

http://localhost:9200/_plugin/sql/
1

在elasticsearch 5.x / 6.x上,需要先下载个网站插件download and extract site.

具体下载地址是：

https://github.com/NLPchina/elasticsearch-sql/releases/download/5.4.1.0/es-sql-site-standalone.zip
1

启动插件:

cd site-server
npm install express --save
node node-server.js 
1
2
3

简单的查询

http://localhost:9200/_sql?sql=select * from indexName limit 10
解释SQL

http://localhost:9200/_sql/_explain?sql=select * from indexName limit 10

SQL用法

简单查询

  SELECT * FROM bank WHERE age >30 AND gender = 'm'
1

聚合查询

  select COUNT(*),SUM(age),MIN(age) as m, MAX(age),AVG(age)
  FROM bank GROUP BY gender ORDER BY SUM(age), m DESC
1
2

删除

  DELETE FROM bank WHERE age >30 AND gender = 'm'
1

超越SQL

  SELECT address FROM bank WHERE address = matchQuery('880 Holmes Lane') ORDER BY _score DESC LIMIT 3
1

聚合

range age group 20-25,25-30,30-35,35-40

  SELECT COUNT(age) FROM bank GROUP BY range(age, 20,25,30,35,40)
1

range date group by day

  SELECT online FROM online GROUP BY date_histogram(field='insert_time','interval'='1d')
1

range date group by your config

  SELECT online FROM online GROUP BY date_range(field='insert_time','format'='yyyy-MM-dd' ,'2014-08-18','2014-08-17','now-8d','now-7d','now-6d','now')
1

ES Geographic（地理位置）

  SELECT * FROM locations WHERE GEO_BOUNDING_BOX(fieldname,100.0,1.0,101,0.0)
1

Select type
```
  SELECT * FROM indexName/type
1
```

SQL功能

SQL Select
SQL Delete
SQL Where
SQL Order By
SQL Group By
SQL AND & OR
SQL Like
SQL COUNT distinct
SQL In
SQL Between
SQL Aliases
SQL Not Null
SQL(ES) Date
SQL avg()
SQL count()
SQL last()
SQL max()
SQL min()
SQL sum()
SQL Nulls
SQL isnull()
SQL now()
SQL floor
SQL split
SQL trim
SQL log
SQL log10
SQL substring
SQL round
SQL sqrt
SQL concat_ws
SQL union and minus

支持JDBC (处于试验阶段的功能)

Check details :JDBC Support

public void testJDBC() throws Exception {
        Properties properties = new Properties();
        properties.put("url", "jdbc:elasticsearch://127.0.0.1:9300/" + TestsConstants.TEST_INDEX);
        DruidDataSource dds = (DruidDataSource) ElasticSearchDruidDataSourceFactory.createDataSource(properties);
        Connection connection = dds.getConnection();
        PreparedStatement ps = connection.prepareStatement("SELECT  gender,lastname,age from  " + TestsConstants.TEST_INDEX + " where lastname='Heath'");
        ResultSet resultSet = ps.executeQuery();
        List result = new ArrayList();
        while (resultSet.next()) {
              System.out.println(resultSet.getString("lastname") + "," + resultSet.getInt("age") + "," + resultSet.getString("gender"))
        }
        ps.close();
        connection.close();
        dds.close();
    }
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

Beyond sql features

ES TopHits
ES MISSING
ES STATS
ES GEO_INTERSECTS
ES GEO_BOUNDING_BOX
ES GEO_DISTANCE
ES GEOHASH_GRID aggregation

参考来源：https://github.com/NLPchina/elasticsearch-sql

)

相关阅读:
ClickHouse 数据插入、更新与删除操作 SQL
openGL通过点绘制图形
 Qt扫盲-QSqlQueryModel理论总结
 【ELM】动态自适应可变加权极限学习机ELM预测（Matlab代码实现）
Redis 好友关注-朋友圈
 Spring（六）注解开发
 Linux 查看进程和线程
 Springboot+Redis执行lua脚本
 Centos7 安装JDK1.8详细过程
 QEMU模拟mini2440开发环境
原文地址：https://blog.csdn.net/web13985085406/article/details/126362082