Improve your RAG solution by converting natural language queries to Elasticsearch SQL by using watsonx.ai

Ever wondered how to tap into the power of Elasticsearch without wrestling with its entire Query DSL? Or how to let non-technical teammates ask questions like, “Which employees left this year?” and automatically run the right Elasticsearch SQL? In this comprehensive guide, you’ll learn exactly how to do both—combining the simplicity of SQL and the intelligence of watsonx.ai to handle everything from basic lookups to advanced text searches.

What is Elasticsearch SQL?

Elasticsearch SQL provides an SQL-based interface to query Elasticsearch data. It allows querying Elasticsearch indices as if they were traditional database tables, enabling users familiar with SQL to leverage Elasticsearch without needing to master its native Query DSL syntax.

The key capabilities for Elasticsearch SQL include:

• SQL Compatibility: Supports standard SQL operations like SELECT, WHERE, GROUP BY, and aggregation functions.
• Indexing and Metadata: Elasticsearch indexes serve as tables, while documents act as rows. Metadata dictionaries (SHOW TABLES, DESCRIBE) allow schema exploration (Metadata Commands).
• Date Handling: Date fields support various functions (YEAR(), MONTH(), DATE_TRUNC(), etc.), providing flexible date manipulation (Date Functions).
• Full-text Search: Special functions like MATCH() enable powerful text search, integrating Elasticsearch's full-text capabilities within SQL queries (Match Queries).

Real-world use cases for Elasticsearch SQL include:

• Search Applications: Quickly implement search functionality using SQL syntax, allowing fast and intuitive development.
• Analytics and Reporting: Easily aggregate, analyze, and visualize large datasets by integrating Elasticsearch SQL with Business Intelligence (BI) tools.

Elasticsearch vs. traditional SQL

Traditional SQL queries are designed primarily for structured, relational data and rely on fixed schemas and exact-match logic (or basic pattern matching with LIKE). In contrast, Elasticsearch queries harness advanced text analysis, tokenization, and relevance scoring, making them far more flexible and efficient when handling unstructured or semi-structured data. With features like fuzzy matching, proximity and span queries, and dynamic query templating, Elasticsearch provides a richer, more nuanced search experience—enabling rapid, distributed searches across massive datasets that traditional SQL simply cannot match. This combination of power, flexibility, and scalability makes Elasticsearch queries significantly better suited for modern search and analytics use cases.

Why ElasticSearch SQL and not ElasticSearch Query DSL

We are relying on LLMs to generate the queries from Natural Language, SQL syntax is readily available in the training data set of these models, we can expect higher accuracy for a wide range of queries.

Prerequisites

You need to:

1. Clone the repo
2. Set up Elasticsearch
3. Connect to watsonx.ai
4. Download the same data set
5. Set up your Python environment

Clone the repo

git clone https://github.com/IBM/NL2ESQL-with-watsonx/

Set up Elasticsearch

First, you need to follow this guide to setup watsonx Discovery to get the db and Kibana.

Then, you need to specify the following credentials in the .env

ELASTIC_URL=""
ELASTIC_USERNAME=""
ELASTIC_PASSWORD=""

Connect to watsonx.ai

To set up natural language processing to Elasticsearch SQL generation, you need to connect with watsonx.ai by following the steps here. You can learn more about using watsonx.ai for generating SQL from text with LLMs in this tutorial, which also helps you get set up using watsonx.ai.

Then, you need following credentials in your .env

IBM_CLOUD_API_KEY=""
WATSONX_ENDPOINT=""
WATSONX_PROJECT_ID=""

Download the data set

For this tutorial, we use this Kaggle Employee Dataset.

Setup your python envinorment

1. Create a virtual environment:

   python3.11 -m venv venv
   source venv/bin/activate  # On Windows: venv\Scripts\activate

   Copy codeCopied!

2. Install dependencies:

   pip install -r requirements.txt

   Copy codeCopied!

Steps

Step 1. Indexing employee data in Elasticsearch

The Indexing code does the following:

1. Load environment variables (such as the Elasticsearch credentials).
2. Create an Elasticsearch client.
3. Read the employee_data.csv, format date columns, and convert them to ISO 8601.
4. Save the formatted data to both CSV and JSON.
5. Define an index mapping for our employee_data index in Elasticsearch.
6. Create the index if it doesn’t already exist.
7. Bulk-load the data from the JSON file into Elasticsearch.

Step 2: Inferencing LLMs

We have added a Python watsonx wrapper class (WatsonxWrapper) that handles watsonx.ai LLM calls, loading environment variables for the credentials, and abstracting away the text generation and streaming logic. It includes synchronous inference generate_text and streaming inference generate_text_stream.

This class will be our main interface to query the LLM for natural language to Elasticsearch SQL conversion as well as for automated metadata generation.

Step 3. Automating metadata creation for Elasticsearch fields

To make the our natural language to Elasticsearch SQL, we need create a metadata dictionary of our index, so that the LLM can generate Elasticsearch SQL. We’ll use watsonx.ai to generate natural language descriptions of each field, along with sample values, data types, and so on. This is helpful for:

• Documenting our data automatically
• Building data catalogs or self-service analytics tools

Step 4. Converting natural language queries to Elasticsearch SQL with watsonx.ai

After indexing, we can now harness watsonx.ai to interpret natural language queries and convert them into Elasticsearch SQL. We then execute the generated SQL queries against Elasticsearch, returning the results in a tabular form (using pandas DataFrames).

In this core code file, nl2eql, these steps are taken:

1. Load environment variables and set up the Elasticsearch client.
2. Import the WatsonxWrapper, custom prompts, and parameters.
3. Define a list of questions.
4. Generate Elasticsearch SQL using the watsonx.ai model.
5. Execute the SQL and display the results.

Checkout the prompts used here

Step 5. Run the demo

We have created a simple streamlit app where you can test the results:

streamlit run streamlit_app.py

Summary

In this tutorial, you learned how to perform advanced RAG by using the power of watsonx.ai and Elasticsearch SQL.

To explore more Elasticsearch SQL features, please check out these elasticsearch_queries.