Use Elasticsearch SQL with Spring Data Elasticsearch

Motivation

In Spring Data Elasticsearch recently a issue was raised to include to possibility to use Elasticsearch SQL . While it is easy with Spring Data Elasticsearch 5.1 to use this directly from within an application, the integration into Spring Data Elasticsearch is more complicated, as the returned values (column definitions and rows) cannot easily and automatically be mapped on to the entities that Spring Data works with, and the response might not only contain column definition, row values, but cursor information as well.

So while this feature might be introduced into Spring Data Elasticsearch in the future, I’ll show how Elasticsearch SQl can be used in the current version 5.1 of Spring Data Elasticsearch.

Prerequesites

You are using the current version of Spring Data Elasticsearch and have your application configured to connect to your Elasticsearch cluster. You might like to check out the documentation for this.

A sample piece of code

The following code shows a REST controller that takes a query and sends that to Elasticsearch:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41

import org.springframework.data.elasticsearch.client.elc.ElasticsearchTemplate;
import org.springframework.data.elasticsearch.core.ElasticsearchOperations;
import org.springframework.http.HttpHeaders;
import org.springframework.http.MediaType;
import org.springframework.http.ResponseEntity;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.RestController;

@RestController
@RequestMapping("/esql")
public class EsqlController {

    private final ElasticsearchOperations operations;

    public EsqlController(ElasticsearchOperations operations) {
        this.operations = operations;
    }

    @GetMapping
    public ResponseEntity<String> query(@RequestParam(value = "query", required = true) String queryParam) {

        if (operations instanceof ElasticsearchTemplate template) {
            var response = template.execute(client ->
                    client.sql()
                            .query(qb -> qb
                                    .query(queryParam)
                                    .format("json")
                            )
            );
            var jsonString = response.toString();

            return ResponseEntity.ok()
                    .header(HttpHeaders.CONTENT_TYPE, MediaType.APPLICATION_JSON_VALUE)
                    .body(jsonString);
        }

        return ResponseEntity.internalServerError().body("Cannot execute esql");
    }
}

• line 5: We just inject the usual ElasticsearchOperations into a controller class.
• line 24: We need to cast the injected ElasticsearchOperations to ElasticsearchTemplate to be able to use the execute method.
• line 26: Within the execute method we have access to the Elasticsearch client and can use the sql() method to create an ElasticsearchSqlClient.
• lines 28/29: We configure the query and the format of the result. The format here must be json. Although the ElasticsearchSQL interface provided with the REST interface of Elasticsearch allows to specify the format, the Elasticsearch Java client does not, it always tries to parse a JSON result.

We then convert the returned object to a string and return it as the response.

The returned data contains rows and within them columns, the following code snippet shows how they could be printed out:

AtomicInteger rowNum=new AtomicInteger();
        response.rows().forEach(row->{AtomicInteger colNum=new AtomicInteger();
        row.forEach(value->{
        System.out.println(String.format("row %d, col %d: %s",rowNum.get(),colNum.get(),value.toJson()));
        colNum.getAndIncrement();
        });
        rowNum.getAndIncrement();
        });

The AtomicInteger are needed to be able to use them within the lambda expressions.

A call to this REST controller might look like this if the query is 'select "first-name","last-name" from person limit 10':

GET/esql?query=select+%22first-name%22%2C%22last-name%22+from+person+limit+10HTTP/1.1
        Host:localhost:9090

Conclusion

Using Elasticsearch SQL with Spring Data Elasticsearch is possible, but processing the response is a little work.