A set of tools to query Elasticsearch with SQL
Tutorial in Chinese: https://segmentfault.com/a/1190000003502849
As Monitor Plugin
./plugin.sh https://url-to-params
The url points to a file with format：
- first line: elasticsearch http url
- remaining lines: sql
For example
http://es_hosts
SELECT count(*) AS value FROM gs_plutus_debug
WHERE "timestamp" > now() - INTERVAL '5 minutes';
SAVE RESULT AS gs_plutus_debug.count;
SELECT count(*) AS value FROM gs_api_track
WHERE "@timestamp" > now() - INTERVAL '5 minutes';
SAVE RESULT AS gs_api_track.count;
Basic authentication is supported
cat << EOF | python -m es_sql http://xxx:8000/
VAR username=hello;
VAR password=world;
SELECT count(*) FROM my_index
EOF
Can also use SQL to query Elasticsearch cluster health stats
SELECT * FROM _cluster_health
SELECT * FROM _cluster_state
SELECT * FROM _cluster_stats
SELECT * FROM _cluster_pending_tasks
SELECT * FROM _cluster_reroute
SELECT * FROM _nodes_stats
SELECT * FROM _nodes_info
SELECT * FROM _indices_stats
SELECT * FROM _indices_stats.all
SELECT * FROM _indices_stats.[index_name]
The output will be a JSON array containing data points
As Console Command
For example
cat << EOF | python -m es_sql http://es_hosts
SELECT "user", "oid", max("@timestamp") as value FROM gs_api_track_
GROUP BY "user", "oid" WHERE "@timestamp" > 1454239084000
EOF
python -m es_sql can be es-sql if pip install es-sql
As Python Library
pip install es-sql
import es_sql
es_sql.execute_sql(
'http://127.0.0.1:9200',
'SELECT COUNT(*) FROM your_index WHERE field=%(param)s',
arguments={'param': 'value'})
For more information: https://github.com/taowen/es-monitor/tree/master/es_sql
As HTTP Api
Start http server (gunicorn)
python -m explorer
Translate SQL to Elasticsearch DSL request
$ cat << EOF | curl -X POST -d @- http://127.0.0.1:8000/translate
SELECT * FROM quote WHERE symbol='AAPL'
EOF
{
"data": {
"indices": "quote*",
"query": {
"term": {
"symbol": "AAPL"
}
}
},
"error": null
}
Use SQL to query Elasticsearch
$ cat << EOF | curl -X POST -d @- http://127.0.0.1:8000/search?elasticsearch=http://127.0.0.1:9200
SELECT COUNT(*) FROM quote WHERE symbol='AAPL'
EOF
{
"data": {
"result": [
{
"COUNT(*)": 8790
}
]
},
"error": null
}
Use SQL with arguments
$ cat << EOF | curl -X POST -d @- http://127.0.0.1:8000/search_with_arguments
{
"elasticsearch":"http://127.0.0.1:9200",
"sql":"SELECT COUNT(*) FROM quote WHERE symbol=%(param1)s",
"arguments":{"param1":"AAPL"}
}
EOF
{
"data": {
"result": [
{
"COUNT(*)": 8790
}
]
},
"error": null
}