使用此插件,您可以使用熟悉的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 like Invalid 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