Mental Models 4 Life

If you have ever wondered how we can connect Tableau to Elasticsearch, this blog post should be of some interest to you.

As of Sep 2016, there is no native Elasticsearch connector provided by either Elastic or Tableau, but here’s a tool someone put together which seems to work but require some effort on the part of your friendly Windows/Unix administrator. (It’s recommended by my local Tableau engineer.)

https://github.com/mradamlacey/elasticsearch-tableau-connector.

Here’s another little useful trick to connect Tableau to Elasticsearch, via Greenplum.

The Tableau to Greenplum connection is just ODBC/JDBC so that part is easy. This reduces the problem of connecting Tableau to Elasticsearch to the problem of connecting Greenplum to Elasticsearch, and that turns out to be straightforward too: In Greenplum, one can create a so-called  External Web Table that allows you to run an arbitrary script, and _that_ script can be a call to Elasticsearch.

Here’s a simple example.

Suppose you have indexed the OpenAddresses database in an Elasticsearch index called address_database, which is accessible through a server called esnode on port 9200. You can easily interact with that Elasticsearch server via a command like

   curl $GET 'esnode:9200/address_database/_search?q=myquerystring' 

Now we just need to set that up inside a Greenplum external web table, with the query string parameterised via meta-programming. There are different ways to do this, and this is how it looks in PL/pgSQL.

CREATE OR REPLACE FUNCTION esearch(query text) RETURNS SETOF TEXT AS $$

DECLARE

rs text;

BEGIN

-- First create an external web table pointing to Elasticsearch with the query string

EXECUTE 'DROP EXTERNAL TABLE IF EXISTS esearch_tab';

EXECUTE 'CREATE EXTERNAL WEB TABLE esearch_tab ( res text ) EXECUTE'  || ' ''curl $GET ''''esnode:9200/address_database/_search?q=' || query || '&size=100'''''' ON MASTER' || ' FORMAT ''TEXT'' (DELIMITER ''|'');';

-- Now parse out the returned JSON object, assuming desired field is message

FOR rs IN (SELECT (regexp_matches(res, E'\"message\"\:\"[^\"]+\"', 'g'))[1] AS a FROM esearch_tab) LOOP

RETURN NEXT trim(leading E'\"message\"\:' FROM rs);

END LOOP;

RETURN;

END;

$$ LANGUAGE plpgsql; 

Now we are ready. Executing this query

SELECT esearch(‘SALISBURY’);

returns

138.63, -34.769, 173-175, SALISBURY HIGHWAY, , SALISBURY, , SA, 5108….

151.52, -32.152, 2940, SALISBURY ROAD, , SALISBURY, , NSW, 2420, , …

…..

Now, with a custom SQL query in Tableau, you can visualise Elasticsearch data in Tableau. You can now also join existing tables in Greenplum with data from Elasticsearch in the same SQL query.

Of course, once data land in Tableau, you can do much more fancy visualisations than what is available in Kibana. Also, now you can export data to a CSV from Tableau if you want, a feature that is still not available in Kibana.