Using SQL in Elasticsearch
Use SQL in Elasticsearch to download data from the search index.
Important Information
Credentials for Elasticsearch https://localhost:9200
Username : admin
Password : xxxxx
Viewing Results from Elasticsearch
Using SQL, you can view search results directly from Elasticsearch using the following URL
https://localhost:9200/_opendistro/_sql?sql=select * from idx001
You can also export the results in CSV format using the following URL
https://localhost:9200/_opendistro/_sql?format=csv&sql=select url, title from idx001
Basic Queries
Use the following queries for specific search results you need.
Queries for specific file types:
https://localhost:9200/_opendistro/_sql?sql=select * from idx001 where contenttype='pdf'
Queries for specific URL or filename:
https://localhost:9200/_opendistro/_sql?sql=select * from idx001 where url='http://www.bbc.com'
Queries for specific keywords:
https://localhost:9200/_opendistro/_sql?sql=select * from idx001 where keywords='cnn'
https://localhost:9200/_opendistro/_sql?sql=select * from idx001 where keywords IN ('cnn','world')
Results from multiple indices:
https://localhost:9200/_opendistro/_sql?format=csv&sql=select * from idx001,idx003,idx003
Results with only URLs from indices:
https://localhost:9200/_opendistro/_sql?format=csv&sql=select url from idx001,idx002
Query with ORDER BY:
https://localhost:9200/_opendistro/_sql?format=csv&sql=select uid, title from idx001,idx002 order by size
Query with LIMIT:
https://localhost:9200/_opendistro/_sql?format=csv&sql=select uid from idx001,idx002 limit 10
Query with INCLUDE, EXCLUDE:
Use exclude ('fieldname') and include ('fieldname') to exclude fields or include fields in display. Partial name of fields can be given along with * character.
https://localhost:9200/_opendistro/_sql?format=csv&sql=select exclude('a*' from idx001,idx003
https://localhost:9200/_opendistro/_sql?format=csv&sql=select exclude('c*') from idx001,idx003
Important Information:
All fields within Elasticsearch can be accessed using SQL.
Aggregations
Using GROUP BY and COUNT:
Group by and count together can be used to determine the count of file types in results.
https://localhost:9200/_opendistro/_sql?format=csv&sql=select count(*) from idx001 group by contenttype
Group by can be also used to determine sentiment in sentiment analysis.
https://localhost:9200/_opendistro/_sql?format=csv&sql=select count(*) from idx001 group by content.sentiment
Query with sum function:
https://localhost:9200/_opendistro/_sql?format=csv&sql=SELECT sum(size) from idx001
Queries with min and max functions:
https://localhost:9200/_opendistro/_sql?sql=select min(size) from idx001
https://localhost:9200/_opendistro/_sql?sql=select max(size) from idx001
Queries with statistic functions:
https://localhost:9200/_opendistro/_sql?sql=select avg(size) from idx001
https://localhost:9200/_opendistro/_sql?sql=select extended_stats(size) from idx001
https://localhost:9200/_opendistro/_sql?sql=select percentiles(size) from idx001
Beyond SQL features
Query with IS MISSING, IS NOT MISSING:
https://localhost:9200/_opendistro/_sql?format=csv&sql=select * from idx001 where author is missing
https://localhost:9200/_opendistro/_sql?format=csv&sql=select * from idx001 where author is not missing
Query to search for multiple terms using IN_TERMS:
https://localhost:9200/_opendistro/_sql?format=csv&sql=select * from idx001 where author= IN_TERMS(hattie,alis,george)
Query to search for a specific term using TERM:
https://localhost:9200/_opendistro/_sql?format=csv&sql=select * from idx001 where author= TERM(george)
Special queries related to statistics that can be used in Elasticsearch:
https://localhost:9200/_opendistro/_sql?format=csv&sql=SELECT extended_stats(age) FROM idx001
https://localhost:9200/_opendistro/_sql?format=csv&sql=SELECT percentiles(age) FROM idx001
Updated almost 3 years ago