Connect Elasticsearch to Tableau: 2 Easy Methods

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
• Method 2: Exporting Elasticsearch data as JSON

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 Elasticsearch ODBC driver  
• Step 2: Setting up the DSN for Elasticsearch
• Step 3: Connecting to Tableau

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.

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

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.

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

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.

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.

 

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

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.

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. 

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.

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
• Using Tableau’s JSON connector

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
• Step 2: Create an instance for Elasticsearch class
• Step 3: Document retrieval using an API call.
• Step 4: Using the pandas .series() method
• Step 5: Creating a pandas data frame object
• Step 6:  Enumerating and appending the series object
• Step 7: Using .to_json() Function

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”]

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)]

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.

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

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.