Amazon distributes its own version of the ElasticSearch stack. To be honest from the Elastic Search point of view, I would consider this move as an act of war. However both sides have decent arguments, so I will only focus on what we get.

We can probably split the functionalities added by AWS to the open source version of the product into the following categories:

  • Security
  • Alerting
  • Performance Monitoring
  • SQL / JDBC

The three first topics are not that interesting in my case because there was already a few options that could be used to cover them. (ElastAlert / Cerebro / NGINX proxy…)

The SQL option is cool because it is far easier to write SQL statements compared to big JSON search definitions but at the end it does not change that much for me either.

If you link the SQL option with a JDBC driver it changes a lot of things, because it means that ElasticSearch becomes a first class citizen for software that connects to databases via JDBC.

To name a few:

Installation

In order to use SQL with ElasticSearch, the first step is to build the SQL plugin by cloning the AWS repo here.

Compiling the component requires two things:

  • A JDK 11 with a JAVA_HOME set
  • The gradle build system

These two software can be installed using homebrew on a Mac.

If everything goes well, you will get a zip file in the /build/distributions/opendistro_sql-0.7.0.0.zip.

The last thing is to simply copy the zip inside the plugin directory of the elastic search nodes. In fact, you only need to copy it in the nodes that are accessed by the clients.

Important note 1: You can deploy the sql plugin on a regular elastic search node, it does not have to be a node based on AWS open distro.

Important note 2: The plugin is built against version 6.5.4 of ElasticSearch and thus the ElasticSearch version stored in the plugin-descriptor.properties file must be updated in order to match your node version. In my case, I was using a 6.6.1 version.

If everything goes well, you should be able to write SQL statements as shown below;

Screenshot 2019-03-16 at 11.18.14

Connecting to ElasticSearch via JDBC

Connecting to ElasticSearch via JDBC is not really that different from connecting to a classic relational database.

We will use Jasper Studio (Download Here) to build a basic report. Download the JDBC driver from amazon here.

Create a JDBC connection as shown on the following screenshot. Don’t forget to add the JDBC driver via the driver panel.

Screenshot 2019-03-16 at 11.23.05

Screenshot 2019-03-16 at 11.37.41

You are ready to go. Simply write your SQL as you would do with a relational database.