ElasticSearch and Jasper Reports – finally together

This post is the first in a series of posts that will accompany the release of our ElasticJasper software in the wild world of the open source. It may be not perfect, we have been postponing its release as open source for a while because we can make it even better, but we have been using it daily in production environments and – while far from perfect – has been very useful.

Adapting the data view of ElasticSearch to the one that Jasper Reports has was not simple. Some sacrifices were made to make it simple to use while keeping most of the elasticity – no pun intended – of ElasticSearch. One of the compromises – as we will see in detail later – has been to make every datasource created with the adapter dedicated to an aspect of the many possible ways that ElasticSearch returns the data.

All  that it’s needed – and that we use – to compile the plugin and the server adapter is available right now on GitHub. You can start cloning and forking from our Open Initiative Repository.

But lets dive right in and see what this release is all about.

Installing the plugin

In this first post we will see how to install the plugin in Jaspersoft Studio and start creating some reports. It will help if you have a Kibana3 installation somewhere, since this would make creating your queries quite simple – at least for hits and facets.

Open up your Jaspersoft Studio – versions 5.6.0 and above are supported – and choose the “Install New Software…” entry in the “Help” menu. If you – like me – are wondering why installing software is in the context of helping, I have no answer to that.

At this point you can choose to add a repository. The repository you need to add – where the binaries of the plugin are currently archived – is at the moment hosted also on GitHub. Pick any name for the repository and for the “Location” enter:

https://github.com/WedjaaOpen/ElasticJasperSite/raw/master/

  You should now be presented with something that looks like:

Select the only available plugin and click “Next“. You will then be prompted to accept the license, then you will be warned that you are installing unsigned content and finally you will be asked to restart. Do all of the above and you have installed ElasticJasper!

Creating an adapter

When Jaspersoft Studio starts up again right click on “Data Adapters” – in the “Repository” tab – and choose to create a new adapter. In the list of the available adapters you should now see one called “ElasticSearch DataSource”. Select this one and click “Next” – this is where the first magic happens!

This bit needs some explanation,  but it shouldn’t be too difficult if you are already familiar with ElasticSearch.

• Indexes and Types: you can leave these empty or list – separated by commas – the indexes and types of documents that you want the queries to apply to. If you leave it empty the queries will be ran on all the indexes and document types;
• Hostname, Port and Cluster: these parameters are quite self explanatory. “elastic search” is the default cluster name, so if you haven’t changed it you should leave this at the default value; same thing goes for the port – 9300 is the API port, don’t confuse it with the web services 9200 port. Practically you should just change the hostname to the name or address of one of the nodes of your ElasticSearch installation, the client will find the others;
• Username and Password: if access to your ES server is protected by username and password, as you may have already guessed, this is where you would enter them;
• Query Mode: this is the odd bit. When you run ES queries you may end up with results that are either hits – if you searched for actual documents, facets – used to get metrics and other summary information about your data or aggregations – the latest and best way to get summary data out of ES. Or – if your query is complex enough – the response could contain all of the above. To make it easier on the parser – also because it would be difficult to mangle all these different types of data in the same table format – you will have to have different adapters for the three different types of queries. So one adapter will specialise in getting hits from the ES cluster, one will get facets and one will get aggregations.

In our first example we are going to get some facets data – getting it by copying a query from Kibana3. So you may want  to select “Facets Mode” as your query mode once you have setup your ES connection. Press “Test”, and if all is well you have created your first ElasticSearch to Jasper Reports adapter.

Using your adapter

Start by creating a report as usual – right clicking on the projects menu and choosing to create a  new report, when you get to the choice of an adapter choose the one that you have created before. You will see that you will be presented with a text area for you to enter the ES query. We want facets – that’s the kind of adapter we have created – so we can write a query along these lines:

{ "facets": {   "LostConnections": {     "date_histogram": {       "field": "dt",       "interval": "1d"     },     "global": true,     "facet_filter": {       "fquery": {         "query": {           "filtered": {             "query": {               "query_string": {                 "query": "evt:\"LostConnection\""               }             },             "filter": {               "bool": {                 "must": [                   {                     "match_all": {}                   }                 ]               }             }             }           }       }     }   } }, "size": 0}

What this query is all about: it creates a facet query named “LostConnections”. This facet is a “date_histogram” facet, in other words: it will collect data grouped by time – the field containing the time is called “dt” and the interval we have chosen is one day. The query is filtering, returning only the count of documents that have a field “evt” matching “LostConnection”.

In other words: it returns for each day connections where reported lost how many times it happened.

You can now click “Next” – the adapter will show you a list of fields that the query returned, do your usual business with it, selecting the fields you want to report on and then the ones you want to group.

Your report is served!

A couple of things

There are a couple of things that come handy when handling reports that are using data coming back from ElasticSearch. Following, in some kind of random order, are a couple of things I wish I knew myself when I started playing with ElasticSearch and JasperReports.

Time – the ElasticSearch way

ES uses Unix Epoch time to represent timestamps. In other words: the number of seconds since the Epoch. These timestamps are king of meaningless to the human eye. I started creating formatters and adding them as parameters in the reports, so that I can instantiate them once and use them all over the reports.

I can create then a parameter called “WEEKDAYFORMATTER”, of type java.text.DateFormat, and use the following expression to populate it:

$P{REPORT_FORMAT_FACTORY}.createDateFormat("yyyy/MM/dd EEE", $P{REPORT_LOCALE}, TimeZone.getTimeZone("GMT+2"))

What this does is to create a date formatted with a weekday in it, using the report locale and the Italian timezone (GMT+2). The string this formatter would be giving out is something in the lines of: “2014/09/12 Fri”.

The way you then use it to format a timestamp field would be using an expression like:

$P{WEEKDAYFORMATTER}.format(new Date($F{Time}))

Where “Time” is the field I want formatted in the report. You can change format, locales and timezones and just have one formatter for each flavour that you want to use in the reports.

Grouping – not without sorting

If you decide to group by some of the fields returned by an ES query you have to remember that there is no guarantee that the fields will be returned sorted. You may end up with a report with an alternating series of the same groups, and it’s not nice.

To make sure that this does not happen you have to ways:

1. Using the wizard to create the grouping: there is a checkbox on the wizard panel you will see when selecting the fields to group with, it allows you to automatically sort by the selected fields – check it!
2. You created the grouping – what now? You created the group on the wizard, forgot to check the box, now you report has a lot of repeating groups. What to do? Simple: add the fields in the “Sort Fields” node of the design outline. Right click on it and create a “Sort Field“.

Subreports – when to create new connections

When you create sub-reports you can recycle the connection from the master report. That’s all fine, until you want to use some kind of different query results in the subreport. So: your master report is using a ElasticJasper adapter for facets and you want to use aggregations on the sub reports: you will need to create new adapters for these sub reports. I know – it sucks, but so many things suck in life, why would you want to be worried about this one?

If your main report is an empty vessel for your sub reports – and it has a data adapter you want to use on your sub reports, but a query that returns no data in it, remember to check the “When no data” property of the report – it’s on the report root node in the outline under the “Report” tab of the properties. It needs to be: “All Sections No Detail” so that it will run the sub reports.