SoftClient4ES/README.md at main · SOFTNETWORK-APP/SoftClient4ES

🌐 Website: softclient4es.dev/

SoftClient4ES is a powerful SQL gateway for Elasticsearch. Query, manipulate, and manage your Elasticsearch data using familiar SQL syntax — including cross-index JOINs, which Elasticsearch has no native support for — through an interactive REPL client, a JDBC driver, an Arrow Flight SQL server, or as a Scala library.

🎬 See It Run

Two indices created, bulk-loaded from JSON with COPY INTO, aggregated across a cross-index JOIN with GROUP BY / HAVING, then materialised into a new index with CREATE TABLE … AS SELECT — and that new index joined straight back, because the result of a JOIN is a first-class index like any other. One REPL session. No ETL, no second copy of your data.

_{Every response and latency above is real output captured from a live Elasticsearch 8.18.3 on a free Community licence. The typing pace is edited for length — no recorder can drive the REPL's line editor without stalling, so the session is composed from captured output rather than screen-recorded. The seven statements, verbatim: demo/cast-src/final.sql}

🏗️ Architecture

⚡ Quick Start — REPL Client

Get started in seconds with the interactive SQL client:

Installation

Linux / macOS:

curl -fsSL https://raw.githubusercontent.com/SOFTNETWORK-APP/SoftClient4ES/main/install.sh | bash

Windows (PowerShell):

irm https://raw.githubusercontent.com/SOFTNETWORK-APP/SoftClient4ES/main/install.ps1 | iex

Windows (cmd.exe, when .ps1 files are blocked): one file — it fetches install.ps1 if it is not beside it (from a pinned release tag, SHA-256 verified), takes the same flags, and only launches it with -ExecutionPolicy Bypass, for that one process.

curl -O https://raw.githubusercontent.com/SOFTNETWORK-APP/SoftClient4ES/main/install.cmd && install.cmd

Connect and Query

softclient4es --host localhost --port 9200

-- Create a table (index)
CREATE TABLE users (
  id KEYWORD,
  name TEXT FIELDS(
    raw KEYWORD
  ) OPTIONS (fielddata = true),
  email KEYWORD,
  age INTEGER,
  created_at DATE,
  PRIMARY KEY (id)
);

-- Insert data
INSERT INTO users (id, name, email, age) 
VALUES ('1', 'Alice', 'alice@example.com', 30);

-- Query with SQL
SELECT name, email, age FROM users WHERE age > 25 ORDER BY name;

-- Update records
UPDATE users SET age = 31 WHERE id = '1';

-- Show tables
SHOW TABLES LIKE 'user%';

📖 Full REPL Documentation

🎯 Why SoftClient4ES?

Feature	Benefit
🔗 Cross-index JOINs	INNER / LEFT / RIGHT / FULL JOINs across indices — and across clusters — no ETL required
🗣️ SQL Interface	Use familiar SQL syntax — no need to learn Elasticsearch DSL
🔄 Version Agnostic	Single codebase for Elasticsearch 6, 7, 8, and 9
⚡ Interactive REPL	Auto-completion, syntax highlighting, persistent history
🔌 JDBC Driver	Connect from DBeaver, Tableau, or any JDBC-compatible tool
🏹 Arrow Flight SQL	Zero-copy columnar access for DuckDB, Python, Apache Superset
🔒 Type Safe	Compile-time SQL validation for Scala applications
🚀 Stream Powered	Akka Streams for high-performance bulk operations
🛡️ Production Ready	Built-in error handling, validation, and rollback
📊 Materialized Views	Precomputed, auto-refreshed JOINs and aggregations

📋 SQL Support

DDL — Data Definition Language

CREATE TABLE products (
  id KEYWORD,
  name TEXT FIELDS(
    raw KEYWORD
  ) OPTIONS (fielddata = true),
  email KEYWORD,
  price DOUBLE,
  tags KEYWORD,
  PRIMARY KEY (id)
);

ALTER TABLE products ADD COLUMN stock INTEGER;
DESCRIBE TABLE products;
DROP TABLE old_products;
TRUNCATE TABLE logs;

📖 DDL Documentation

DML — Data Manipulation Language

INSERT INTO products (id, name, price) VALUES ('p1', 'Laptop', 999.99);
UPDATE products SET price = 899.99 WHERE id = 'p1';
DELETE FROM products WHERE price < 10;
COPY INTO products FROM '/data/products.json';

📖 DML Documentation

DQL — Data Query Language

SELECT name, price, COUNT(*) as sales
FROM products
WHERE category = 'electronics'
GROUP BY name, price
HAVING COUNT(*) > 10
ORDER BY sales DESC
LIMIT 100;

Subqueries and derived tables are part of DQL too — since engine 0.24.0:

-- Subquery in WHERE — IN / NOT IN, EXISTS / NOT EXISTS, a scalar comparison,
-- or a quantified one (= ANY | SOME, <> ALL, > ALL, >= ANY, …)
SELECT name FROM employees
WHERE department_id IN (SELECT id FROM departments WHERE region = 'EU');

-- Derived table in FROM (also valid in JOIN, nested to any depth)
SELECT d.category, d.n
FROM (SELECT category, COUNT(*) AS n FROM bi_events GROUP BY category) d
WHERE d.n > 10;

-- Correlated subquery — the body reads the outer row
SELECT c.name FROM customers c
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id);

Uncorrelated WHERE subqueries run on Elasticsearch itself, so they work on every surface — including a plain REPL with no extensions.
Derived tables and correlated subqueries run on the relational engine — since engine 0.24.0 with arrow-extensions 0.3.4 (softclient4es-arrow-extensions), the same engine that executes cross-index JOINs: the REPL's default install, the JDBC driver, the ADBC driver, the Arrow Flight SQL server and Federation all carry it. A venue without it refuses the statement with a clear error instead of executing it against the first index named.
Non-recursive CTEs run on that same relational engine — a CTE reference is a derived table, so it carries the same venue requirement. A WITH clause is accepted at the top of a SELECT only; WITH RECURSIVE and CTE column lists (WITH a (x, y) AS …) are refused by name.
Set operators run on that engine too — UNION / UNION DISTINCT, INTERSECT / INTERSECT ALL, EXCEPT / EXCEPT ALL. UNION ALL is the exception: Elasticsearch answers it directly, branch by branch, at every venue. Branches are matched by column position, and the result takes the first branch's names.

Supported features: cross-index JOINs, JOIN UNNEST, subqueries, derived tables, CTEs, set operators, window functions, aggregations, nested fields, geospatial queries, and more.

📖 DQL Documentation

Cross-Index JOINs — the feature Elasticsearch doesn't have

Stop ETL'ing Elasticsearch into your warehouse just to JOIN it. Elasticsearch has no native cross-index JOIN — SoftClient4ES adds one at query time, on every surface: the REPL, the JDBC driver, the ADBC driver, the Arrow Flight SQL server, and Federation.

SELECT o.id, o.amount, c.name AS customer_name, c.email
FROM orders AS o
JOIN customers AS c ON o.customer_id = c.id
WHERE o.status = 'completed'
ORDER BY o.amount DESC
LIMIT 10;

INNER / LEFT / RIGHT / FULL OUTER joins — plus JOIN UNNEST for nested arrays
Same-cluster joins (each table becomes an ES sub-query with WHERE push-down; the join runs in-process on an embedded engine) — cross-cluster and multi-cluster joins via Federation
Free in Community — up to 2 cross-index JOINs per query on a single cluster, on all client drivers

📖 Cross-Index JOIN Documentation 📝 How it works: The JOIN Matrix

Materialized Views

Precomputed, automatically refreshed query results stored as Elasticsearch indices — ideal for denormalizing JOINs, precomputing aggregations, and enriching data across indices.

CREATE OR REPLACE MATERIALIZED VIEW orders_with_customers_mv
REFRESH EVERY 10 SECONDS
WITH (delay = '1s', user_latency = '1s')
AS
SELECT
  o.id,
  o.amount,
  c.name AS customer_name,
  c.email,
  UPPER(c.name) AS customer_name_upper
FROM orders AS o
JOIN customers AS c ON o.customer_id = c.id
WHERE o.status = 'completed';

-- Query like a regular table
SELECT * FROM orders_with_customers_mv WHERE customer_name = 'Alice';

-- Inspect
DESCRIBE MATERIALIZED VIEW orders_with_customers_mv;
SHOW CREATE MATERIALIZED VIEW orders_with_customers_mv;
SHOW MATERIALIZED VIEW STATUS orders_with_customers_mv;

Under the hood, materialized views orchestrate Elasticsearch transforms, enrich policies, ingest pipelines, and watchers — all generated from a single SQL statement.

📖 Materialized Views Documentation

🔌 JDBC Driver

Connect to Elasticsearch from any JDBC-compatible tool — DBeaver, Tableau, DataGrip, DbVisualizer, or any Java/Scala application.

Driver Setup

Download the self-contained fat JAR (all dependencies included) for your Elasticsearch version:

Elasticsearch Version	Download
ES 6.x	`softclient4es6-jdbc-driver-0.3.4.jar`
ES 7.x	`softclient4es7-jdbc-driver-0.3.4.jar`
ES 8.x	`softclient4es8-jdbc-driver-0.3.4.jar`
ES 9.x	`softclient4es9-jdbc-driver-0.3.4.jar`

Each jar has a .sha256 file next to it on the release page; check a download with shasum -a 256 -c softclient4es8-jdbc-driver-0.3.4.jar.sha256.

Java 11+ recommended (17+ for ES 9.x): cross-index JOINs require Java 11+ — the embedded JOIN engine is built on Apache Arrow 18.x, which ships Java-11 bytecode.

JDBC URL:    jdbc:elastic://localhost:9200
Driver class: app.softnetwork.elastic.jdbc.ElasticDriver

🏹 Arrow Flight SQL

Zero-copy columnar access to Elasticsearch over gRPC — for DuckDB, Python, Apache Superset, and any Arrow Flight SQL client.

Server Setup (Docker)

docker run -p 32010:32010 \
  -e ES_HOST=elasticsearch \
  -e ES_PORT=9200 \
  -e ES_USER=elastic \
  -e ES_PASSWORD=changeme \
  softnetwork/softclient4es8-arrow-flight-sql:latest

Python + DuckDB

import adbc_driver_flightsql.dbapi as flight_sql
import duckdb

conn = flight_sql.connect("grpc://localhost:32010")
cursor = conn.cursor()
cursor.execute("SELECT * FROM ecommerce")
table = cursor.fetch_arrow_table()   # zero-copy Arrow table

duckdb.sql("SELECT category, SUM(total_price) AS revenue FROM table GROUP BY category")

Live Demo

All three profiles live in the same compose file, demo/docker-compose.yml — run every command below from the demo/ directory. Note that superset-flight and grafana both publish host port 32010, so start one at a time.

DuckDB + Python pipeline

cd demo
docker compose --profile duckdb up

Apache Superset BI dashboards

cd demo
docker compose --profile superset-flight up

Grafana dashboards

cd demo
docker compose --profile grafana up

Apache Superset	Grafana

📖 Arrow Flight SQL Documentation 📊 Benchmark: Ten million rows out of Elasticsearch, measured against Trino
📖 ADBC Driver Documentation

🛠️ Scala Library Integration

For programmatic access, add SoftClient4ES to your project.

Client Library Matrix

Elasticsearch	Artifact	Scala	JDK
6.x	`softclient4es6-jest-client`	2.12, 2.13	8+
6.x	`softclient4es6-rest-client`	2.12, 2.13	8+
7.x	`softclient4es7-rest-client`	2.12, 2.13	8+
8.x	`softclient4es8-java-client`	2.12, 2.13	8+
9.x	`softclient4es9-java-client`	2.13 only	17+

JDK versions above apply to the client libraries themselves. Cross-index JOINs (via the arrow extensions, the JDBC/ADBC drivers) require Java 11+ — see Cross-Index JOIN.

sbt Setup

// build.sbt
resolvers += "Softnetwork" at "https://softnetwork.jfrog.io/artifactory/releases/"

// Choose your Elasticsearch version
libraryDependencies += "app.softnetwork.elastic" %% "softclient4es8-java-client" % "0.24.0"
// Add the community extensions for materialized views (optional)
libraryDependencies += "app.softnetwork.elastic" %% "softclient4es-community-extensions" % "0.3.3"
// Add the arrow extensions for cross-index JOIN (required for JOINs; Java 11+)
libraryDependencies += "app.softnetwork.elastic" %% "softclient4es-arrow-extensions" % "0.3.4"
// Add the JDBC driver if you want to use it from Scala (optional)
libraryDependencies += "app.softnetwork.elastic" %% "softclient4es-jdbc-driver" % "0.3.4"

import app.softnetwork.elastic.client._

val client = ElasticClientFactory.create()

// SQL queries
val results = client.search(SQLQuery("SELECT * FROM users WHERE age > 25"))

// Type-safe queries with compile-time validation
case class User(id: String, name: String, age: Int)
val users: Source[User, NotUsed] = client.scrollAs[User](
  "SELECT id, name, age FROM users WHERE active = true",
  client.defaultScrollConfig // the macro needs an explicit config; this one carries elastic.scroll.*
)

📖 API Documentation

✨ Key Features

🔀 Zero-Downtime Mapping Migration

Automatically migrate index mappings with rollback support:

client.updateMapping("users", newMapping) // Handles backup, reindex, and rollback

📖 Mapping Migration Guide

📦 High-Performance Bulk Operations

Stream millions of documents with backpressure handling:

client.bulkFromFile("/data/products.parquet", format = Parquet, idKey = Some("id"))

Supported formats: JSON, NDJSON, Parquet, Delta Lake

📖 Bulk API Guide

🔍 Smart Scroll API

Automatically selects the optimal strategy (PIT, search_after, or scroll):

client.scroll(SQLQuery("SELECT * FROM logs WHERE level = 'ERROR'"))
  .runWith(Sink.foreach(processDocument))

📖 Scroll API Guide

🔗 Akka Persistence Integration

Seamlessly sync event-sourced systems with Elasticsearch.

📖 Event Sourcing Guide

📚 Documentation

Topic	Link
REPL Client	📖 Documentation
SQL Reference	📖 Documentation
Cross-Index JOINs	📖 Documentation
API Reference	📖 Documentation
Materialized Views	📖 Documentation
DDL Statements	📖 Documentation
Arrow Flight SQL	📖 Documentation
ADBC Driver	📖 Documentation
JDBC Driver	📖 Documentation
BI Tool Integrations	📖 Documentation
Federation Operator Guide	📖 Documentation
Known Limitations	📖 Documentation

📦 Editions and Licensing

SoftClient4ES uses a dual-license model:

Core (SQL engine, REPL client, Scala library) — Apache License 2.0 (open source)
JDBC Driver, Arrow Flight SQL, ADBC Driver, and Materialized Views — Elastic License 2.0 (free to use, not open source)

Editions & pricing

Every tier has every feature — including all client drivers. You pay for scale, metered by quotas, not for unlocking capabilities.

The two things Elasticsearch cannot do natively — and that DIY can't either — are available on every tier:

Query-time cross-index JOIN — on every surface (REPL, JDBC, ADBC, Arrow Flight SQL, Federation). JOIN depth is metered.
Persisted Materialized Views — pre-joined / pre-aggregated indices.

	Community	Pro	Enterprise
Price	Free	€119/mo · €1,190/yr · $129/$1,290	from €12,000/year
Full SQL (DDL · DML · DQL · window functions)	Yes	Yes	Yes
Client drivers — JDBC · ADBC · REPL	Free	Free	Free
Arrow Flight SQL server	Yes	Yes	Yes
Cross-index JOINs per query	2	5	Unlimited
Federation across ES clusters	1 ES cluster	up to 5 ES clusters	Unlimited
Materialized Views	1	50	Unlimited
Max query results	10,000	1,000,000	Unlimited
ES 6 / 7 / 8 / 9 support	Yes	Yes	Yes
Support	Community	Email / 48h	Priority / 4h SLA
SSO · air-gapped licensing · custom quotas	—	—	Yes

Single-cluster cross-index JOINs and one Materialized View are free in Community — taste both superpowers, then scale up by cluster count, JOIN depth, and MV volume. Federation meters across ES clusters specifically in this release; non-ES backends are a future-release concern.

What happens at a cap (every number is enforced, not aspirational): exceeding maxJoins rejects the query before execution; exceeding maxClusters makes the federation sidecar fail to start (CrashLoop) by design; the Nth+1 Materialized View returns HTTP 402; over-quota query results are truncated with a warning (no LIMIT) or return HTTP 402 (explicit LIMIT). JOIN inputs are never capped — only the joined output is.

Start a 30-day Pro trial at portal.softclient4es.com/signup, buy at portal.softclient4es.com/pricing, or see the full pricing page for the tier matrix and FAQ.

Elasticsearch License Requirements

The JDBC driver and materialized views work on free/basic Elasticsearch clusters with the following exception:

Elasticsearch Feature	Required ES License
Transforms (continuous data sync)	Free / Basic (ES 7.5+)
Enrich Policies (JOIN enrichment)	Free / Basic (ES 7.5+)
Watcher (auto-refresh enrich policies)	Trial, or a subscription that includes it

Materialized views with JOINs rely on Elasticsearch Watcher to automatically re-execute enrich policies when lookup table data changes. On a Basic cluster, CREATE MATERIALIZED VIEW still succeeds and returns a warning: the view is created and immediately queryable, and REFRESH MATERIALIZED VIEW <name> — which re-executes exactly what the watcher would have — works on every license. Only the scheduled refresh is unavailable; run it from an external scheduler (cron, Kubernetes CronJob, Airflow) instead. See the Materialized Views documentation for details.

🗺️ Roadmap

📝 Blog Posts

Longer reads on how the engine works and how it holds up. The full archive is at softclient4es.dev/blog — free to read, no account.

🆕 New — Ten Million Rows Out of Elasticsearch, Parsed Once — measured against Trino: bytes off the cluster, client memory, then wall time
The JOIN Matrix: How Cross-Index JOIN Actually Works on Elasticsearch — the execution model behind cross-index JOINs, and where it stops
Connect DBeaver to Elasticsearch. Yes, Really. — the JDBC driver in a real desktop SQL client, step by step
Elasticsearch Queries That Never Break in Production — the field-name typo fails the build instead of production
Stop Rewriting Your Elasticsearch Code Every Version Upgrade — why the client API stays the same across ES 6 → 9

🤝 Contributing

Contributions are welcome! See CONTRIBUTING.md for guidelines.

📄 License

The core SQL engine and REPL client are licensed under the Apache License 2.0 — see LICENSE for details.

The JDBC driver, Arrow Flight SQL server, ADBC driver, and Materialized Views extension are licensed under the Elastic License 2.0 — free to use, not open source.

💬 Support

Built with ❤️ by the SoftNetwork team

FilesExpand file tree

README.md

Latest commit

History