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\n\n\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 We will create 10000 generated post documents in the posts<\/strong> index using the following piece of code:<\/p>\n\n\n We can use Kibana to query all the documents where the username is Martin<\/strong> as follows:<\/p>\n\n\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 Now if you want to run the above queries in your Java\napplication you have a few options:<\/p>\n\n\n\n You can use pretty much any REST client for Java in order to use the second option but we will use the low-level Elasticsearch REST client:<\/p>\n\n\n The following block of code returns only 10 documents from the posts<\/strong> index:<\/p>\n\n\n To see how is the SQL query executed behind the scenes you can use the translate API provided under the \/_sql\/translate endpoint. We can run the following in Kibana If we want to see what is the query DSL generated for the previous SQL query:<\/p>\n\n\n And we should get a result similar to the following:<\/p>\n\n\n We demonstrated how we can execute basic SQL queries. The\nElasticsearch SQL engine is quite rich and includes:<\/p>\n\n\n\n In terms of the SQL implementation itself you can refer to\nthe supported\nSQL commands<\/a> and SQL\nfunctions and operators reference documentation<\/a> <\/p>\n\n\n\n In this article we demonstrated how to use Elasticsearch SQL\nto interact with the Elasticsearch engine. There are high chances that this\nmechanism becomes more preferable to use than the JSON-based query DSL. However\nElasticsearch SQL is not a replacement for it but is rather built on top of it and\nfulfills to plethora of features provided by the search engine. <\/p>\n","protected":false},"excerpt":{"rendered":" The Elasticsearch engine 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 […]<\/p>\n","protected":false},"author":50,"featured_media":1097,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"inline_featured_image":false,"jetpack_post_was_ever_published":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":""},"categories":[381],"tags":[484,251,311,485],"coauthors":[354],"class_list":["post-2178","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-christmas-2019-is-coming","tag-elasticsearch","tag-java","tag-rest","tag-sql"],"yoast_head":"\n\n<dependency>\n <groupId>org.elasticsearch.client<\/groupId>\n <artifactId>elasticsearch-rest-high-level-client<\/artifactId>\n <version>7.5.0<\/version>\n<\/dependency>\n<\/pre><\/div>\n\n\n
\n\tRestHighLevelClient client = new RestHighLevelClient(\n\t\t RestClient.builder(\n\t\t new HttpHost("localhost", 9200, "http")));\n\t\t\n\t\tString[] possibleUsers = new String[] {"Martin", "Jim", "John"};\n\t\tString[] possibleDates = new String[] {"2019-12-15", "2019-12-16", "2019-12-17"};\n\t\tString[] possibleMessages = new String[] {"Hello, Javaadvent !",\n\t\t\t\t"Cool set of blog posts. We want more !",\n\t\t\t\t"Elasticsearch SQL is great."};\n\t\t\n\t\tfor(int i = 1; i <= 10000; i++) {\n\t\t\tMap<String, Object> jsonMap = new HashMap<>();\n\t\t\tjsonMap.put("user", possibleUsers[ThreadLocalRandom.current().nextInt(0, 3)]);\n\t\t\tjsonMap.put("date", possibleDates[ThreadLocalRandom.current().nextInt(0, 3)]);\n\t\t\tjsonMap.put("message", possibleMessages[ThreadLocalRandom.current().nextInt(0, 3)]);\n\t\t\tIndexRequest request = new IndexRequest("posts")\n\t\t\t .id(String.valueOf(i)).source(jsonMap); \n\t\t\tclient.index(request, RequestOptions.DEFAULT);\n\t\t}\n\t\t\n\t\tclient.close();\n\n<\/pre><\/div>\n\n\nRunning the SQL queries<\/h2>\n\n\n\n
\nPOST \/_sql?format=txt\n{\n "query": "SELECT * FROM posts where user = 'Martin'"\n}\n<\/pre><\/div>\n\n\n\nPOST \/_sql?format=txt\n{\n "query": "SELECT count(*) FROM posts where message like '%Javaadvent%'"\n}\n<\/pre><\/div>\n\n\n\n<dependency>\n <groupId>org.elasticsearch.client<\/groupId>\n <artifactId>elasticsearch-rest-client<\/artifactId>\n <version>7.5.0<\/version>\n<\/dependency>\n<\/pre><\/div>\n\n\n
\n\t\tRestClient restClient = RestClient.builder(\n\t\t\t new HttpHost("localhost", 9200, "http")).build();\n\t\t\n\t\tRequest request = new Request("POST", "\/_sql");\n\t\trequest.setJsonEntity("{\\"query\\":\\"SELECT * FROM posts limit 10\\"}");\n\t\tResponse response = restClient.performRequest(request);\n\t\tString responseBody = EntityUtils.toString(response.getEntity()); \n\t\tSystem.out.println(responseBody);\n\t\trestClient.close();\n\n<\/pre><\/div>\n\n\n\nPOST \/_sql\/translate\n{\n "query": "SELECT * FROM posts limit 10",\n "fetch_size": 10\n}\n<\/pre><\/div>\n\n\n\n{\n "size" : 10,\n "_source" : {\n "includes" : [\n "message",\n "user"\n ],\n "excludes" : [ ]\n },\n "docvalue_fields" : [\n {\n "field" : "date",\n "format" : "epoch_millis"\n }\n ],\n "sort" : [\n {\n "_doc" : {\n "order" : "asc"\n }\n }\n ]\n}\n<\/pre><\/div>\n\n\nElasticsearch SQL features <\/h2>\n\n\n\n
Conclusion <\/h2>\n\n\n\n