Everything Elasticsearch SQL Should Be

Full SQL coverage that no other tool offers — DDL, DML, DQL, Window Functions, Materialized Views, JDBC, and Arrow Flight SQL.

DDL

Schema Management with SQL

No more REST API calls with JSON payloads. Create, alter, and drop Elasticsearch indexes using familiar SQL DDL statements. Works across ES 6-9 with a single syntax.

✓ CREATE TABLE with typed columns and PRIMARY KEY
✓ ALTER TABLE ADD/DROP COLUMN
✓ DROP TABLE with IF EXISTS support
✓ SHOW TABLES / DESCRIBE TABLE

CREATE TABLE products (
  id KEYWORD,
  name TEXT FIELDS(raw KEYWORD),
  price DOUBLE,
  category KEYWORD,
  created_at DATE,
  PRIMARY KEY (id)
);

ALTER TABLE products
  ADD COLUMN IF NOT EXISTS tags KEYWORD;

INSERT INTO products (id, name, price, category)
VALUES ('p1', 'Widget Pro', 49.99, 'tools');

UPDATE products
SET price = 39.99
WHERE category = 'tools' AND price > 45;

DELETE FROM products
WHERE created_at < '2024-01-01';

DML

Data Manipulation with SQL

Insert, update, and delete documents using standard SQL. No Bulk API JSON, no client library code — just SQL.

✓ INSERT INTO with values
✓ UPDATE with WHERE conditions
✓ DELETE FROM with filtering
✓ COPY INTO FROM for bulk loading

DQL

Window Functions

Elastic SQL doesn't support window functions. SoftClient4ES translates SQL window functions into Elasticsearch aggregations, enabling analytics use cases that were previously impossible with SQL over Elasticsearch.

✓ SUM, COUNT, MIN, MAX, AVG with PARTITION BY
✓ FIRST_VALUE, LAST_VALUE with PARTITION BY + ORDER BY
✓ ARRAY_AGG with PARTITION BY + ORDER BY
✓ Available across all ES versions (6-9)

SELECT name, department, salary,
  AVG(salary) OVER (
    PARTITION BY department
  ) AS dept_avg,
  FIRST_VALUE(name) OVER (
    PARTITION BY department
    ORDER BY salary DESC
  ) AS top_earner
FROM employees;

SELECT
  o.id, o.customer,
  items.name, items.quantity,
  SUM(items.price * items.quantity)
    OVER (PARTITION BY o.id)
    AS total_price
FROM orders o
JOIN UNNEST(o.items) AS items
WHERE items.quantity >= 1
ORDER BY o.id ASC;

DQL

Multi-Nested Queries

Elastic SQL limits nested field access to a single level. SoftClient4ES handles multi-level nesting recursively with JOIN UNNEST, parent-level aggregations, and window functions over nested arrays.

✓ JOIN UNNEST on ARRAY<STRUCT> columns
✓ Multi-level nesting handled recursively
✓ Parent-level aggregations over nested arrays
✓ Window functions combined with JOIN UNNEST

UNIQUE

Materialized Views

No other tool offers this for Elasticsearch. Elasticsearch has no native cross-index JOIN — Materialized Views bridge this gap by denormalizing data from multiple indices into a single queryable view. Also supports aggregations, computed columns, and scheduled refresh. Rollups are deprecated, ES|QL materialized views are just a GitHub issue — SoftClient4ES delivers this today.

✓ Cross-index JOINs — combine data from multiple ES indices
✓ SQL aggregations, computed columns, GROUP BY
✓ REFRESH MATERIALIZED VIEW (schedulable)
✓ No ES license required for manual refresh

-- Cross-index JOIN: ES can't do this natively
CREATE MATERIALIZED VIEW order_details AS
SELECT
  o.id, o.order_date,
  c.name AS customer_name,
  c.segment,
  SUM(o.amount) AS total
FROM orders o
JOIN customers c ON o.customer_id = c.id
GROUP BY o.id, o.order_date, c.name, c.segment;

REFRESH MATERIALIZED VIEW order_details;

# Python ADBC client
import adbc_driver_flightsql.dbapi as flight

conn = flight.connect(
    "grpc://localhost:32010"
)
cursor = conn.cursor()
cursor.execute("""
    SELECT category, SUM(amount)
    FROM orders GROUP BY category
""")
# Arrow columnar format — zero-copy
df = cursor.fetch_arrow_table().to_pandas()

FIRST IN MARKET

Arrow Flight SQL + ADBC

The first and only Arrow Flight SQL server for Elasticsearch. 10-100x faster data transfer than JDBC/ODBC. ADBC is already the standard in modern data platforms — adopted by Databricks, DuckDB, and the Python analytics ecosystem.

✓ Apache Arrow columnar format
✓ gRPC-based Flight SQL protocol
✓ Connect from Python, DuckDB, Go via standard ADBC clients
✓ Java ADBC driver for in-process JVM access
✓ Integrates into lakehouse architectures

Full Competitive Comparison

Filter by category. See exactly where SoftClient4ES leads.

Feature	SoftClient4ES	Elastic SQL	CData	NLPchina	Trino ES	OpenSearch
SELECT queries	Yes	Yes	Yes	Yes	Yes	Yes
DDL (CREATE/ALTER/DROP)	Yes	No	No	No	No	No
DML (INSERT/UPDATE/DELETE)	Yes	No	Yes	No	No	No
Window functions	Yes	No	No	No	Yes	No
Multi-nested queries	Yes	Limited	No	No	No	Limited
COPY INTO (bulk load)	Yes	No	No	No	No	No
Materialized Views	Yes (ES 7.5+)	No (roadmap)	No	No	No	No (roadmap)
JDBC Driver	Type 4	Paid license	Yes	No	Yes (Trino)	Yes
Arrow Flight SQL	Yes	No	No	No	Via Trino	No
ADBC Driver	Yes	No	No	No	No	No
REPL	Yes	No	No	Web UI	CLI	No
ES 6 support	Yes	Deprecated	Yes (2.2+)	Yes	Yes	No
ES 7 support	Yes	Yes	Yes	Yes	Yes	No
ES 8 support	Yes	Yes	Yes	Yes	Yes	No
ES 9 support	Yes	Yes	Unknown	Yes	Unknown	No
Unified API (cross-version)	GatewayApi	No	Yes	No	N/A	No
No ES license required	Core: Yes	Paid	N/A	Yes	Yes	Yes
Deployment model	External client	Built-in	External driver	ES plugin	Cluster	Built-in

Licensing: SoftClient4ES core is Apache 2.0. Extensions (Materialized Views, JDBC, ADBC, Arrow Flight SQL) are Elastic v2 license.

Convinced? Get started in 5 minutes.

Quick Start Read the Docs