ElasticSearch Connector for JAVA

Read / write ElasticSearch data inside your app; perform many ElasticSearch operations without coding, just use easy to use high performance API Connector for ElasticSearch

In this article you will learn how to quickly and efficiently integrate ElasticSearch data in JAVA. We will use high-performance ElasticSearch Connector to easily connect to ElasticSearch and then access the data inside JAVA.

Let's follow the steps below to see how we can accomplish that!

Download Documentation

ElasticSearch Connector for JAVA is based on ZappySys API Driver which is part of ODBC PowerPack. It is a collection of high-performance ODBC drivers that enable you to integrate data in SQL Server, SSIS, a programming language, or any other ODBC-compatible application. ODBC PowerPack supports various file formats, sources and destinations, including REST/SOAP API, SFTP/FTP, storage services, and plain files, to mention a few.

Connect to ElasticSearch in other apps

Connect to ElasticSearch in SSIS

Connect to ElasticSearch in SQL Server

Connect to ElasticSearch in Power BI

Connect to ElasticSearch in SSRS

Connect to ElasticSearch in Informatica

Connect to ElasticSearch in MS Access

Connect to ElasticSearch in MS Excel

Connect to ElasticSearch in SSAS

Connect to ElasticSearch in C#

Connect to ElasticSearch in Python

Connect to ElasticSearch in Tableau

Connect to ElasticSearch in SAP Crystal Reports

Connect to ElasticSearch in Azure Data Factory (Pipeline)

Connect to ElasticSearch in Azure Data Factory (SSIS)

Connect to ElasticSearch in Talend Studio

Connect to ElasticSearch in UiPath

Connect to ElasticSearch in PowerShell

Connect to ElasticSearch in ODBC

Create Data Source in ZappySys Data Gateway based on API Driver

Download and install ODBC PowerPack.
Search for gateway in start menu and Open ZappySys Data Gateway:
Go to Users Tab to add our first Gateway user. Click Add; we will give it a name tdsuser and enter password you like to give. Check Admin option and click OK to save. We will use these details later when we create linked server:
Now we are ready to add a data source. Click Add, give data source a name (Copy this name somewhere, we will need it later) and then select Native - ZappySys API Driver. Finally, click OK. And it will create the Data Set for it and open the ZS driver UI.

ElasticsearchDSN
When the Configuration window appears give your data source a name if you haven't done that already, then select "ElasticSearch" from the list of Popular Connectors. If "ElasticSearch" is not present in the list, then click "Search Online" and download it. Then set the path to the location where you downloaded it. Finally, click Continue >> to proceed with configuring the DSN:

ElasticsearchDSN

ElasticSearch

Now it's time to configure the Connection Manager. Select Authentication Type, e.g. Token Authentication. Then select API Base URL (in most cases, the default one is the right one). More info is available in the Authentication section.

Basic Authentication (UserId/Password) [Http]

Steps how to get and use ElasticSearch credentials

For Local / Hosted Instance by you

Get your userid / password and enter on the connection UI

For Managed Instance (By Bonsai search)

If your instance is hosted by bonsai then perform these steps to get your credentials for API call

Go to https://app.bonsai.io/clusters/{your-instance-id}/tokens
Copy Access Key and Access Secret and enter on the connection UI. Click Test connection.
If your Cluster has no data you can generate sample data by visiting this URL and click Add Sample Data https://{your-cluster-id}.apps.bonsaisearch.net/app/home#/tutorial_directory

Fill in all required parameters and set optional parameters if needed:

ElasticsearchDSN

ElasticSearch

Basic Authentication (UserId/Password) [Http]

http://localhost:9200

Optional Parameters
User Name (or Access Key)
Password (or Access Secret)
Ignore certificate related errors

Windows Authentication (No Password) [Http]

Fill in all required parameters and set optional parameters if needed:

ElasticsearchDSN

ElasticSearch

Windows Authentication (No Password) [Http]

http://localhost:9200

Optional Parameters
Ignore certificate related errors

Once the data source connection has been configured, it's time to configure the SQL query. Select the Preview tab and then click Query Builder button to configure the SQL query:

ZappySys API Driver - ElasticSearch

Read / write ElasticSearch data inside your app; perform many ElasticSearch operations without coding, just use easy to use high performance API Connector for ElasticSearch

ElasticsearchDSN
Start by selecting the Table or Endpoint you are interested in and then configure the parameters. This will generate a query that we will use in JAVA to retrieve data from ElasticSearch. Hit OK button to use this query in the next step.
```
SELECT * FROM Indexes
```
Some parameters configured in this window will be passed to the ElasticSearch API, e.g. filtering parameters. It means that filtering will be done on the server side (instead of the client side), enabling you to get only the meaningful data much faster.
Now hit Preview Data button to preview the data using the generated SQL query. If you are satisfied with the result, use this query in JAVA:
ZappySys API Driver - ElasticSearch

Read / write ElasticSearch data inside your app; perform many ElasticSearch operations without coding, just use easy to use high performance API Connector for ElasticSearch

ElasticsearchDSN
```
SELECT * FROM Indexes
```
You can also access data quickly from the tables dropdown by selecting <Select table>.

A WHERE clause, LIMIT keyword will be performed on the client side, meaning that the whole result set will be retrieved from the ElasticSearch API first, and only then the filtering will be applied to the data. If possible, it is recommended to use parameters in Query Builder to filter the data on the server side (in ElasticSearch servers).
Click OK to finish creating the data source.
Most Important Step: Now, After creating or modifying a data source in ZappySys Data Gateway, make sure to click the SAVE button to preserve your changes.
Then, RESTART the Data Gateway service to ensure all changes are properly applied.

Skipping either step may result in the new settings not taking effect.

Read data in Java from the DSN

Java code to get the data:

"jdbc:sqlserver://localhost:5000;databasename=ElasticsearchDSN;user=tdsuser;password=test"
When you run the code it will make the API call and read the data:

Here is Java program's code in text format:


//Step-1: Install ZappySys ODBC PowerPack and Configure Data Gateway

//Step-2:Assuming the Microsoft SQL Server JDBC Driver is in below folder
//C:\Program Files\Microsoft Jdbc Driver 6.0 for SQL Server\sqljdbc_6.0\enu\auth\x64
package padaone;

import java.sql.*;

public class zappy {

    public static void main(String[] args) {

        // Create a variable for the connection string.
        String connectionUrl = "jdbc:sqlserver://localhost:5000;databasename=ElasticsearchDSN;user=test;password=test";

        // Declare the JDBC objects.
        Connection con = null;
        Statement stmt = null;
        ResultSet rs = null;

            try {
                // Establish the connection.
                Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
                con = DriverManager.getConnection(connectionUrl);

                // Create and execute an SQL statement that returns some data.
                String SQL = "SELECT Country , SUM(UnitPrice * Quantity) Total " 
                           + "FROM value " 
                           + "GROUP BY Country "
                           + "WITH (SRC='https://services.odata.org/V3/Northwind/Northwind.svc/Invoices?$format=json')";
                stmt = con.createStatement();
                rs = stmt.executeQuery(SQL);

                // Iterate through the data in the result set and display it.
                while (rs.next()) {
                    System.out.println(rs.getString(1) + " " + rs.getString(2));
                }
            }

        // Handle any errors that may have occurred.
        catch (Exception e) {
            e.printStackTrace();
        } 
        
        finally {
            if (rs != null) try { rs.close(); } catch (Exception e) {}
            if (stmt != null) try { stmt.close(); } catch (Exception e) {}
            if (con != null) try { con.close(); } catch (Exception e) {}
        }
    }
}