{"id":101052,"date":"2020-01-02T10:00:39","date_gmt":"2020-01-02T08:00:39","guid":{"rendered":"https:\/\/www.javacodegeeks.com\/?p=101052"},"modified":"2019-12-17T11:31:31","modified_gmt":"2019-12-17T09:31:31","slug":"elasticsearch-sql","status":"publish","type":"post","link":"https:\/\/www.javacodegeeks.com\/2020\/01\/elasticsearch-sql.html","title":{"rendered":"Elasticsearch SQL"},"content":{"rendered":"

The Elasticsearch engine<\/h2>\n

Elasticsearch is one of the most widely search engines being used in a number of production deployments today. It is based on the Lucene search library and one of the key features it provides is a JSON-based query DSL on top of Lucene that provides an easier to use mechanism for interacting with the search engine. However the query DSL is very specific to Elasticsearch. The SQL support introduced in Elasticsearch 6.3 has brought a standard mechanism for running queries against the search engine and has been one step further towards easier adoption by developers already familiar with SQL. Although SQL has been initially designed for use with relational database management systems it has been implemented in a wide range of other systems (such as NoSQL databases). Take for example SQL supported provided in a distributed data processing engine like Apache Spark or a distributed cache-based computation system like Apache Ignite where SQL is one of the core query facilities provided. In this article we will explore how Elasticsearch SQL works.<\/p>\n

Preliminary setup<\/h2>\n

In order to try the examples in the article you need to have a local Elasticsearch (at least 6.3) instance started. In this article we are going to use latest Elasticsearch 7.5. We will create a posts<\/strong> index that holds posts from a forum. We will be using the Elasticsearch Java client to feed data into the index and we are not going to provide explicit mapping for the fields of the index (for the purpose of simplicity we will let Elasticsearch automatically create it for us). First we will create a Maven project with a dependency to the Elasticsearch Java high level client (the old Elasticsearch HTTP client is deprecated and planned for removal in Elasticsearch 8.0):<\/p>\n

\n
\n\n\n\n
\n
1<\/div>\n
2<\/div>\n
3<\/div>\n
4<\/div>\n
5<\/div>\n<\/td>\n
\n
\n
<dependency><\/code><\/div>\n
    <\/code><groupId>org.elasticsearch.client<<\/code>\/groupId<\/code>><\/code><\/div>\n
    <\/code><artifactId>elasticsearch-rest-high-level-client<<\/code>\/artifactId<\/code>><\/code><\/div>\n
    <\/code><version>7.5.0<<\/code>\/version<\/code>><\/code><\/div>\n
<<\/code>\/dependency<\/code>><\/code><\/div>\n<\/div>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n<\/div>\n

We will create 10000 generated post documents in the posts<\/strong> index using the following piece of code:<\/p>\n

\n
\n\n\n\n
\n
01<\/div>\n
02<\/div>\n
03<\/div>\n
04<\/div>\n
05<\/div>\n
06<\/div>\n
07<\/div>\n
08<\/div>\n
09<\/div>\n
10<\/div>\n
11<\/div>\n
12<\/div>\n
13<\/div>\n
14<\/div>\n
15<\/div>\n
16<\/div>\n
17<\/div>\n
18<\/div>\n
19<\/div>\n
20<\/div>\n
21<\/div>\n<\/td>\n
\n
\n
RestHighLevelClient client = <\/code>new<\/code> RestHighLevelClient(<\/code><\/div>\n
                <\/code>RestClient.builder(<\/code><\/div>\n
                        <\/code>new<\/code> HttpHost(<\/code>\"localhost\"<\/code>, <\/code>9200<\/code>, <\/code>\"http\"<\/code>)));<\/code><\/div>\n
        <\/code> <\/div>\n
        <\/code>String[] possibleUsers = <\/code>new<\/code> String[] {<\/code>\"Martin\"<\/code>, <\/code>\"Jim\"<\/code>, <\/code>\"John\"<\/code>};<\/code><\/div>\n
        <\/code>String[] possibleDates = <\/code>new<\/code> String[] {<\/code>\"2019-12-15\"<\/code>, <\/code>\"2019-12-16\"<\/code>, <\/code>\"2019-12-17\"<\/code>};<\/code><\/div>\n
        <\/code>String[] possibleMessages = <\/code>new<\/code> String[] {<\/code>\"Hello, Javaadvent !\"<\/code>,<\/code><\/div>\n
                <\/code>\"Cool set of blog posts. We want more !\"<\/code>,<\/code><\/div>\n
                <\/code>\"Elasticsearch SQL is great.\"<\/code>};<\/code><\/div>\n
        <\/code> <\/div>\n
        <\/code>for<\/code>(<\/code>int<\/code> i = <\/code>1<\/code>; i <= <\/code>10000<\/code>; i++) {<\/code><\/div>\n
            <\/code>Map<String, Object> jsonMap = <\/code>new<\/code> HashMap<>();<\/code><\/div>\n
            <\/code>jsonMap.put(<\/code>\"user\"<\/code>, possibleUsers[ThreadLocalRandom.current().nextInt(<\/code>0<\/code>, <\/code>3<\/code>)]);<\/code><\/div>\n
            <\/code>jsonMap.put(<\/code>\"date\"<\/code>, possibleDates[ThreadLocalRandom.current().nextInt(<\/code>0<\/code>, <\/code>3<\/code>)]);<\/code><\/div>\n
            <\/code>jsonMap.put(<\/code>\"message\"<\/code>, possibleMessages[ThreadLocalRandom.current().nextInt(<\/code>0<\/code>, <\/code>3<\/code>)]);<\/code><\/div>\n
            <\/code>IndexRequest request = <\/code>new<\/code> IndexRequest(<\/code>\"posts\"<\/code>)<\/code><\/div>\n
                <\/code>.id(String.valueOf(i)).source(jsonMap); <\/code><\/div>\n
            <\/code>client.index(request, RequestOptions.DEFAULT);<\/code><\/div>\n
        <\/code>}<\/code><\/div>\n
        <\/code> <\/div>\n
        <\/code>client.close();<\/code><\/div>\n<\/div>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n<\/div>\n

Running the SQL queries<\/h2>\n

We can use Kibana to query all the documents where the username is Martin<\/strong> as follows:

<\/div> <\/div><\/p>\n
\n
\n\n\n\n
\n
1<\/div>\n
2<\/div>\n
3<\/div>\n
4<\/div>\n<\/td>\n
\n
\n
POST <\/code>\/_sql<\/code>?<\/code>format<\/code>=txt<\/code><\/div>\n
{<\/code><\/div>\n
    <\/code>\"query\"<\/code>: <\/code>\"SELECT * FROM posts where user = 'Martin'\"<\/code><\/div>\n
}<\/code><\/div>\n<\/div>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n<\/div>\n

Another example would be to count all the documents that contain the word Javaadvent<\/strong> in the message<\/strong> field:<\/p>\n

\n
\n\n\n\n
\n
1<\/div>\n
2<\/div>\n
3<\/div>\n
4<\/div>\n<\/td>\n
\n
\n
POST <\/code>\/_sql<\/code>?<\/code>format<\/code>=txt<\/code><\/div>\n
{<\/code><\/div>\n
    <\/code>\"query\"<\/code>: <\/code>\"SELECT count(*) FROM posts where message like '%Javaadvent%'\"<\/code><\/div>\n
}<\/code><\/div>\n<\/div>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n<\/div>\n

Now if you want to run the above queries in your Java application you have a few options:<\/p>\n