Do you want to visualize your Elasticsearch data in Tableau? Are you looking for a simple fix? If yes, you’ve landed at the right page! This article provides you with a step-by-step solution to help you connect Elasticsearch to Tableau in a matter of minutes. 

This article aims at answering all your queries about connecting Elasticsearch to Tableau. Follow our easy guide to master the skill of connecting Elasticsearch to Tableau using various methods.

Introduction to Elasticsearch

Elasticsearch is an open-source search engine, developed in Java. It is essentially a No-SQL database and an analytic engine built on top of Lucene. It provides real-time performance and quick search results primarily because it performs an index-based search instead of a text-based one.

Some key features of Elasticsearch:

  • Scalable: It runs smoothly, even on systems containing 1000’s of nodes and scales automatically.  
  • Document Oriented: It uses the documents in a JSON format by default. It is simple, concise, and supports most programming languages.
  • Schema Support: There is no need to provide an index or a data type beforehand, objects are automatically mapped when they get indexed with new properties.
  • Instance Search: It provides a search as you type feature, to help users extract relevant results quickly.

For further information on Elasticsearch, you can check the official site here.

Introduction to Tableau

Tableau is a powerful business intelligence tool used to turn raw data into an understandable format. It is a tool popularly used to visualize data and can be understood even by a non-technical user. 

It creates visualizations with the help of dashboards and worksheets, this helps users perform real-time analysis in a very fast and secure manner. It doesn’t require any programming skill or technical background to operate it.

For further information on Tableau, you can check the official site here.

Prerequisites

  • Working knowledge of Tableau.
  • Tableau installed at the host workstation.
  • A general idea of using the command-line.
  • A general idea about Python and its libraries.

Methods to connect Elasticsearch to Tableau

There are many ways in which you can establish a connection between Elasticsearch & Tableau:

Method 1: Using ODBC to connect Elasticsearch to Tableau

ODBC drivers can also be used to connect Elasticsearch to Tableau. The ODBC driver can add a data source such as Elasticsearch and then connect with Tableau. 

This can be implemented using the following steps:

Step 1: Installing the Elasticsearch ODBC driver

Download the latest version of ODBC driver for Elasticsearch on your system from the official Elasticsearch site. You can use the following link to download the driver:  

https://www.elastic.co/downloads/odbc-client

Once the ODBC driver is installed on your system, launch it to start the DSN set up process. Choose the correct 64 or 32-bit version and select the System DSN tab. To start it, you can click search the menu for ODBC Data source administrator.

Elasticsearch to Tableau: ODBC Option.

Click on the drivers’ tab to check if the driver was installed or not.

Elasticsearch to Tableau: ODBC Driver set up.

Step 2: Setting up the DSN for Elasticsearch

There are three ways in which you can configure DSN for Elasticsearch: User DSN, System DSN, or File DSN. Click on the add button and select the Elasticsearch ODBC driver.

Elasticsearch to Tableau: Selecting ODBC Driver.

Click on finish. The Elasticsearch DSN setup box will now appear, where you need to fill in details regarding the connection settings and authentication:

Elasticsearch to Tableau: DSN Set up.

Fill the necessary fields as follows:

  • Name: Enter the name of the DSN you want to create.

  • Description: It provides information about the connection in the form of short notes.

  • Cloud Id: It is a string that encodes the connection parameter, and helps establish a connection with the elasticsearch cloud serv

    ice.
  • Hostname: This represents the IP address of the instance, the driver will connect to.
  • Port: Elasticsearch uses the port 9200 by default, however, you can fill in the correct port number as per your cluster.
  • Username: Enter the username in the correct format to connect with the Elasticsearch database.
  • Passwords: Enter your password here.

Elasticsearch ODBC driver provides a facility to log the API calls. This greatly helps in troubleshooting and can be enabled using the tracing tab under the administrator field.

Elasticsearch to Tableau: Enabling Tracing.

The calls made by the applications are logged in the system by default. To keep track of both the application and driver calls, you can enable driver logging. Every time a connection is established, a new log will be created for the driver. Set the log level to DEBUG.

Elasticsearch to Tableau: Selecting DEBUG Level.

 

Once you have configured host, port and authentication parameters, you can use the test connection option. This will help verify the connection status.

Elasticsearch to Tableau: Succesful connection.

This is how you can set up the DSN for connecting Elasticsearch to Tableau.

Step 3: Connecting to Tableau

Once you have made the necessary configuration changes, open tableau. Now, from the connect columns on the left select the other databases (ODBC) option.

In the ODBC dialogue box, select the Elasticsearch DSN from a drop-down list and click on ok.

Elasticsearch to Tableau: Tableau Connectors.

You might need to provide the connection details such as username and password. A new DSN editor window will open up if the credentials are required again, otherwise, the fields get filled automatically upon selecting the correct DSN. 

Elasticsearch to Tableau: ODBC Connection in Tableau.

Select the data source name option and give a unique name to the database you are using. It’s considered a good practice to have a unique name as it makes it much easier for users to identify the database from which data is being fetched.

Elasticsearch to Tableau: Tables in Tableau.

To select the desired schema, you can use the schema drop-down list from the column on the left. You can also perform a text-based search to find the desired option. Now similarly find and select the desired table and drag it onto the canvas.

This is how you can connect Elasticsearch to the tableau.

Limitations of this method:

  • This method sometimes requires using third-party tools, which essentially is an extra tool that needs to be maintained.
  • When dealing with complex JSON, errors related to the table normalisation & joins occur frequently.
  • This method results in service delays due to interruptions such as hardware or software updations. This can lead to a complete loss of connection.

Method 2: Exporting Elasticsearch data as JSON

One unique way to connect Elasticsearch to Tableau is to export data from Elasticsearch in JSON format and then use the Tableau’s JSON connector to leverage and analyse the data. Use the following steps to start the process:

Export Elasticsearch data using Python

To connect Elasticsearch to Tableau, first extract data with Python by using the following steps:

Step 1: Install the Elasticsearch library

You can use the following command to install the library:

pip install elasticsearch

Similarly, install the NumPy and pandas library:

pip install pandas & pip install numpy

This method requires making API based requests to fetch data from the database. This can be done by using the Elasticsearch class in Python.

from elasticsearch import Elasticsearch
Step 2: Create an instance for Elasticsearch class

Create an instance for the Elasticsearch class and then use it to make an API call. You can do this using the following lines of code:

E_client=Elasticsearch([{‘host’: ‘localhost’, ‘port’: 9200}]) //This will help connect to the cluster.
R=E_client.search(index= “some_val”,body={},size=99)

This command fetches 10 documents by default. If you want to fetch a greater number of documents, you must specify the number explicitly. Ensure you don’t fetch a very large number as it can easily affect the performance.

Step 3: Document Retrieval Using an API call.

You can store the documents retrieved from the API call in a dictionary. Iterate over this dictionary to get the documents as follows:

E_docs=R[“hits”][“hits”]
Elasticsearch to Tableau: Using API Calls.
Step 4: Using the pandas .series() method

To easily export data in the desired format, you need to convert the documents dictionary into a pandas.core.series.Series object. You can do this as follows:

list_dict = []
d1={}
d2={}
d3={}
list_dict+=[d1,d2,d3]
list_series=[] # This is an empty list
for num,doc in enumerate(list_dict):
             list_series+=[pandas.Series(doc)]
Elasticsearch to Tableau: Accessing elements of the list.
Step 5: Creating a pandas data frame Object

Begin by creating an empty object as follows:

docs= pandas.DataFrame()
Step 6: Enumerating and appending the series object

To iterate through the documents efficiently, make use of the enumerate function in a python for-loop as follows:

For num,docs in enumerate (E_docs):
            Source_data = docs[“_source”]
            _id = docs[“_id”]
            doc_data = pandas.Series(source_data, name = _id)  
            docs=docs.append(doc_data)
Step 7: Using .to_json() Function

The inherent methods of Pandas Series and DataFrame objects allow streamlined exporting of different file formats including to_html(), to_json(), and to_csv().

json_export = docs.to_json() # return JSON data
print ("nJSON data:", json_export)

This is how you can extract data as JSON to connect Elasticsearch to Tableau.

Using Tableau’s JSON Connector

Open the JSON connector and select the JSON file you have just exported. Specify/select the required schema as JSON is unstructured in its design and requires users to manually select them.

Elasticsearch to Tableau: Tableau JSON Connector.

Once you have selected the schema, you will get an output representing data in a flattened way as follows:

Elasticsearch to Tableau: Tableau Data.

You can now analyse data using various visualizations and gain a better insight about what your data is representing using Tableau.

This is how you can connect Elasticsearch to Tableau in a unique way.

Limitations of this method:

  • This method requires a lot of manual inputs from the user and requires the users to update the JSON file every time they want to update the visualization.
  • JSON file connector has limited functionality, it is not possible to join multiple JSON files.
  • It hampers performance by being dependent on the JSON files stored on the device rather than utilising the power of the database

Conclusion

This article introduces you to the various methods that can be used to connect Elasticsearch to Tableau. It also provides in-depth knowledge about the concepts behind every step to help you understand and implement them efficiently. These methods, however, can be challenging especially for a beginner & this is where Hevo saves the day.

visit our website to explore hevo

Hevo Data, a No-code Data Pipeline helps you transfer data from a source of your choice in a fully-automated and secure manner without having to write the code repeatedly. Hevo with its strong integration with 150+ sources, allows you to not only export & load marketing data but also transform & enrich your data & make it analysis-ready in a jiffy. 

Want to take Hevo for a spin? sign up here for a 14-day free trial! Check out our unbeatable pricing to help you choose the right plan for your data needs.

Tell us about your experience of connecting Elasticsearch to Tableau! Share your thoughts in the comments section below.

Divij Chawla
Marketing Operations and Analytics Manager, Hevo Data

Divij Chawla is interested in data analysis, software architecture, and technical content creation. With extensive experience driving Marketing Operations and Analytics teams, he excels at defining strategic objectives, delivering real-time insights, and setting up efficient processes. His technical expertise includes developing dashboards, implementing CRM systems, and optimising sales and marketing workflows. Divij combines his analytical skills with a deep understanding of data-driven solutions to create impactful content and drive business growth.

Visualize Your Elasticsearch Data In Tableau Easily