Monday, August 24, 2015

Elasticsearch Business Intelligence & Reporting

Blend Elasticsearch and MySQL Data on the Fly Using Knowi

UPDATED: December 2018

This integration combines Elasticsearch query and search capabilities to easily produce shareable visual insights quickly. In addition, for the very first time, our join capabilities provide unified insights into Elasticsearch as well other NoSQL and SQL based datasources, eliminating cumbersome engineering and heavy ETL processes into relational databases.  

To get started, visit our Elasticsearch page, which is preconfigured with a demo Elasticsearch cluster settings to get started.

In the following example, we’ll walk through the following:
  • Connect to a demo Elasticsearch cluster
  • Execute queries against it
  • Join the results with some data in MySQL
  • Visualize, track and share the results

The following animated gif shows how to connect to our ElasticSearch cluster. 

(Note: If the cluster is behind your firewall, please use our agent)

Let's also set up connectivity to our demo MySQL datasource using the default settings -  that we’ll use for Joins later.

Once the datasource is set up, click on the Configure Queries link:
  1. Specify the Elasticsearch JSON query. In our example, let's search for matches for all documents that contain Transactional.
  2.   {
         "query": {
             "query_string": {
                 "query": "Transactional*"
  3. Click on Preview to return all documents for that query. This returns the first 1000 matches. 

  4. Let's add some Cloud9QL to post-process the data further. Note that the Cloud9QL is optional, and is not a replacement to Elasticsearch query - it provides a powerful way to complement Elasticsearch query to transform the results. In the following example, let's transform the results further to get the sum of the sent field, grouped by customer. 
  5. select sum(sent) as Sent, customer 
    group by customer
    Click on Preview to immediately see the updated results.

  6. Joins: Now that we have some basic data from Elasticsearch, say that we need to join this data with customer details that exist in a MySQL database. 
    • Click on Join --> Select the new MySQL datasource --> Under Query Generator, select the customer table
    • Add customer=customer in the Join Fields box. This implies that customer fields from the Elasticsearch query will be used to lookup (inner join) against customer field in the MySQL query.
            More on Joins:
            Click on Preview.
ElasticSearch Query

The results now include customer details from MySQL combined together with the data from Elasticsearch.

5. Click on 'Save' to save it, then add it to a dashboard to easily share and embed it.