🌐 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.
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
Get started in seconds with the interactive SQL client:
Linux / macOS:
curl -fsSL https://raw.githubusercontent.com/SOFTNETWORK-APP/SoftClient4ES/main/install.sh | bashWindows (PowerShell):
irm https://raw.githubusercontent.com/SOFTNETWORK-APP/SoftClient4ES/main/install.ps1 | iexWindows (cmd.exe, when .ps1 files are blocked): one file — it fetches install.ps1 if it is not beside it, 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.cmdsoftclient4es --host localhost --port 9200-- Create a table (index)CREATETABLEusers (
id KEYWORD,
name TEXT FIELDS(
raw KEYWORD
) OPTIONS (fielddata = true),
email KEYWORD,
age INTEGER,
created_at DATE,
PRIMARY KEY (id)
);
-- Insert dataINSERT INTO users (id, name, email, age) VALUES ('1', 'Alice', 'alice@example.com', 30);
-- Query with SQLSELECT name, email, age FROM users WHERE age >25ORDER BY name;
-- Update recordsUPDATE users SET age =31WHERE id ='1';
-- Show tables
SHOW TABLES LIKE'user%';| 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 |
CREATETABLEproducts (
id KEYWORD,
name TEXT FIELDS(
raw KEYWORD
) OPTIONS (fielddata = true),
email KEYWORD,
price DOUBLE,
tags KEYWORD,
PRIMARY KEY (id)
);
ALTERTABLE products ADD COLUMN stock INTEGER;
DESCRIBE TABLE products;
DROPTABLE old_products;
TRUNCATE TABLE logs;INSERT INTO products (id, name, price) VALUES ('p1', 'Laptop', 999.99);
UPDATE products SET price =899.99WHERE id ='p1';
DELETEFROM products WHERE price <10;
COPY INTO products FROM'/data/products.json';SELECT name, price, COUNT(*) as sales
FROM products
WHERE category ='electronics'GROUP BY name, price
HAVINGCOUNT(*) >10ORDER BY sales DESCLIMIT100;Supported features: cross-index JOINs, JOIN UNNEST, window functions, aggregations, nested fields, geospatial queries, and more.
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.
SELECTo.id, o.amount, c.nameAS customer_name, c.emailFROM orders AS o
JOIN customers AS c ONo.customer_id=c.idWHEREo.status='completed'ORDER BYo.amountDESCLIMIT10;INNER/LEFT/RIGHT/FULL OUTERjoins — plusJOIN UNNESTfor nested arrays- Same-cluster joins (each table becomes an ES sub-query with
WHEREpush-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
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')
ASSELECTo.id,
o.amount,
c.nameAS customer_name,
c.email,
UPPER(c.name) AS customer_name_upper
FROM orders AS o
JOIN customers AS c ONo.customer_id=c.idWHEREo.status='completed';
-- Query like a regular tableSELECT*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
Connect to Elasticsearch from any JDBC-compatible tool — DBeaver, Tableau, DataGrip, DbVisualizer, or any Java/Scala application.
Download the self-contained fat JAR for your Elasticsearch version:
| Elasticsearch Version | Artifact |
|---|---|
| ES 6.x | softclient4es6-jdbc-driver-0.2.5.jar |
| ES 7.x | softclient4es7-jdbc-driver-0.2.5.jar |
| ES 8.x | softclient4es8-jdbc-driver-0.2.5.jar |
| ES 9.x | softclient4es9-jdbc-driver-0.2.5.jar |
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
Maven:
<dependency>
<groupId>app.softnetwork.elastic</groupId>
<artifactId>softclient4es8-jdbc-driver</artifactId>
<version>0.2.5</version>
</dependency>Gradle:
implementation 'app.softnetwork.elastic:softclient4es8-jdbc-driver:0.2.5'sbt:
libraryDependencies +="app.softnetwork.elastic"%"softclient4es8-jdbc-driver"%"0.2.5"The JDBC driver JARs are Scala-version-independent (no _2.12 or _2.13 suffix) and include all required dependencies.
Zero-copy columnar access to Elasticsearch over gRPC — for DuckDB, Python, Apache Superset, and any Arrow Flight SQL client.
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:latestimportadbc_driver_flightsql.dbapiasflight_sqlimportduckdbconn=flight_sql.connect("grpc://localhost:32010")
cursor=conn.cursor()
cursor.execute("SELECT * FROM ecommerce")
table=cursor.fetch_arrow_table() # zero-copy Arrow tableduckdb.sql("SELECT category, SUM(total_price) AS revenue FROM table GROUP BY category")docker compose --profile duckdb updocker compose --profile superset-flight updocker compose --profile grafana up| Apache Superset | Grafana |
|---|---|
 |  |
📖 Arrow Flight SQL Documentation
📖 ADBC Driver Documentation
For programmatic access, add SoftClient4ES to your project.
| 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.
// build.sbt
resolvers +="Softnetwork" at "https://softnetwork.jfrog.io/artifactory/releases/"// Choose your Elasticsearch version
libraryDependencies +="app.softnetwork.elastic"%%"softclient4es8-java-client"%"0.20.4"// Add the community extensions for materialized views (optional)
libraryDependencies +="app.softnetwork.elastic"%%"softclient4es-community-extensions"%"0.2.4"// Add the arrow extensions for cross-index JOIN (required for JOINs; Java 11+)
libraryDependencies +="app.softnetwork.elastic"%%"softclient4es-arrow-extensions"%"0.2.5"// Add the JDBC driver if you want to use it from Scala (optional)
libraryDependencies +="app.softnetwork.elastic"%%"softclient4es-jdbc-driver"%"0.2.5"importapp.softnetwork.elastic.client._valclient=ElasticClientFactory.create()
// SQL queriesvalresults= client.search(SQLQuery("SELECT * FROM users WHERE age > 25"))
// Type-safe queries with compile-time validationcaseclassUser(id: String, name: String, age: Int)
valusers: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.*
)Automatically migrate index mappings with rollback support:
client.updateMapping("users", newMapping) // Handles backup, reindex, and rollbackStream millions of documents with backpressure handling:
client.bulkFromFile("/data/products.parquet", format =Parquet, idKey =Some("id"))Supported formats: JSON, NDJSON, Parquet, Delta Lake
Automatically selects the optimal strategy (PIT, search_after, or scroll):
client.scroll(SQLQuery("SELECT * FROM logs WHERE level = 'ERROR'"))
.runWith(Sink.foreach(processDocument))Seamlessly sync event-sourced systems with Elasticsearch.
| 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 |
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)
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.
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 VIEWstill 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.
- JDBC driver for Elasticsearch
- Materialized views with JOINs and aggregations
- Arrow Flight SQL server (gRPC, Docker)
- ADBC driver (in-process, columnar)
- Cross-index JOINs
- Advanced monitoring dashboard
- Additional SQL functions
- ES|QL bridge
- Stop Rewriting Your Elasticsearch Code Every Version Upgrade
- Elasticsearch Queries That Never Break in Production
- It's 3 AM. Production Is Down. Your Only Tool Is curl.
- Elasticsearch Schema Management Was Hell. Then Someone Typed SQL.
- A 47-Line curl Script to Insert One Document. Seriously.
- Connect DBeaver to Elasticsearch. Yes, Really.
Contributions are welcome! See CONTRIBUTING.md for guidelines.
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.
Built with ❤️ by the SoftNetwork team