使用此插件,您可以使用熟悉的SQL语法查询elasticsearch。您还可以在SQL中使用ES函数。
有两种方法可以使用此插件:
-
使用其余的api
http://localhost:9200/_sql?sql=select * from indexName limit 10
2. 或者通过浏览器访问http://localhost:9200/_plugin/sql
安装
安装为插件:版本(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
Elasticsearch 2.0.0
./bin/plugin install https://github.com/NLPchina/elasticsearch-sql/releases/download/2.0.4/elasticsearch-sql-2.0.4.zip
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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}。
重新启动Elasticsearch服务器
#停止Elasticsearch进程
kill `ps -ef | grep Elasticsearch | grep -v grep | awk '{print $2}'`
#启动Elasticsearch进程
/elasticsearch-5.4.3/bin/elasticsearch -d
基本用法
在elasticsearch 1.x / 2.x上,可以直接访问elasticsearch-sql web前端页面:
http://localhost:9200/_plugin/sql/
在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
启动插件:
cd site-server
npm install express --save
node node-server.js
-
简单的查询
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'
-
聚合查询
select COUNT(*),SUM(age),MIN(age) as m, MAX(age),AVG(age) FROM bank GROUP BY gender ORDER BY SUM(age), m DESC
-
删除
DELETE FROM bank WHERE age >30 AND gender = 'm'
超越SQL
-
Search
SELECT address FROM bank WHERE address = matchQuery('880 Holmes Lane') ORDER BY _score DESC LIMIT 3
-
聚合
-
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)
-
range date group by day
SELECT online FROM online GROUP BY date_histogram(field='insert_time','interval'='1d')
-
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')
-
-
ES Geographic(地理位置)
SELECT * FROM locations WHERE GEO_BOUNDING_BOX(fieldname,100.0,1.0,101,0.0)
-
Select type
SELECT * FROM indexName/type
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<String> result = new ArrayList<String>();
while (resultSet.next()) {
System.out.println(resultSet.getString("lastname") + "," + resultSet.getInt("age") + "," + resultSet.getString("gender"))
}
ps.close();
connection.close();
dds.close();
}
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
)