SQL for Elasticsearch · Part 4

Elasticsearch Schema Management Was Hell. Then Someone Typed SQL.

CREATE TABLE, ALTER TABLE and zero-downtime migrations on Elasticsearch, expressed as SQL DDL.

March 3, 2026 ·5 min read

This is Part 4 of the SoftClient4ES series. Previously: Elasticsearch Queries That Never Break in Production, Stop Rewriting Your Elasticsearch Code Every Version Upgrade, and It’s 3 AM. Production Is Down. Your Only Tool Is curl.

The Ticket That Ruined a Friday

The Jira ticket said:

“Add phone_number field to the users index.”

Estimated time: 30 minutes.

Here’s what actually happened to the team:

Step 1: Write the new mapping JSON (45 minutes — nested objects are tricky)
Step 2: Create users_v2 index with the new mapping (5 minutes)
Step 3: Reindex 50M documents from users to users_v2 (2 hours)
Step 4: Update the alias to point to users_v2 (2 minutes)
Step 5: Delete users_v1 (1 minute)
Step 6: Discover that Step 3 failed silently on 12,000 documents (next morning)
Step 7: Start over.

Total time: 1 day. For one field.

And that was just staging. There was still pre-prod. And production.

Anyone who has managed Elasticsearch schemas in production has lived some version of this story. A “simple” field addition turns into a multi-hour, multi-step ritual of JSON crafting, reindexing prayers, and alias gymnastics. One wrong bracket, one typo in a mapping definition, and the whole pipeline collapses.

The JSON Mapping Problem

This is what creating an index looks like with the Elasticsearch REST API:

PUT /users
{
  "mappings": {
    "properties": {
      "id": { "type": "keyword" },
      "name": {
        "type": "text",
        "fields": {
          "raw": { "type": "keyword" }
        },
        "fielddata": true
      },
      "email": { "type": "keyword" },
      "age": { "type": "integer" },
      "department": {
        "type": "object",
        "properties": {
          "name": { "type": "text" },
          "zip_code": { "type": "keyword" }
        }
      },
      "tags": { "type": "keyword" }
    }
  },
  "settings": {
    "number_of_shards": 1,
    "number_of_replicas": 1
  }
}

30 lines. 7 levels of nesting. 14 pairs of curly braces. One misplaced comma and the whole thing fails with a cryptic parsing error.

Now look at the same thing in SQL:

CREATE TABLE users (
  id KEYWORD,
  name VARCHAR FIELDS (raw KEYWORD) OPTIONS (fielddata = true),
  email KEYWORD,
  age INT,
  department STRUCT FIELDS (
    name VARCHAR,
    zip_code KEYWORD
  ),
  tags KEYWORD,
  PRIMARY KEY (id)
);

10 lines. Flat, readable, declarative. Anyone who has ever used a relational database can read this.

No brackets to count. No nesting to track. No “did I put a comma after the last property?” anxiety.

DDL for Elasticsearch

SoftClient4ES brings a full Data Definition Language to Elasticsearch. Not a half-baked “CREATE INDEX” wrapper — a complete, standards-inspired DDL with CREATE, ALTER, DROP, DESCRIBE, and more.

Let’s walk through it.

CREATE TABLE

The basics:

CREATE TABLE users (
  id INT NOT NULL,
  name VARCHAR DEFAULT 'anonymous',
  birthdate DATE,
  age INT SCRIPT AS (DATE_DIFF(birthdate, CURRENT_DATE, YEAR)),
  PRIMARY KEY (id)
);

That single statement generates:

An Elasticsearch index called users
A mapping with all field types properly translated
A default ingest pipeline (users_ddl_default_pipeline) that handles:
Document ID from the primary key
The DEFAULT ‘anonymous’ logic for name
The computed age field via a Painless script

Computed columns (SCRIPT AS) are a standout feature. That age field is automatically calculated at ingest time — no application code, no custom pipeline.

Structured Data: STRUCT and `ARRAY<STRUCT>`

Elasticsearch object and nested types map naturally:

CREATE TABLE users (
  id INT NOT NULL,
  profile STRUCT FIELDS (
    first_name VARCHAR NOT NULL,
    last_name VARCHAR NOT NULL,
    address STRUCT FIELDS (
      street VARCHAR,
      city VARCHAR,
      zip KEYWORD
    ),
    join_date DATE,
    seniority INT SCRIPT AS (DATE_DIFF(profile.join_date, CURRENT_DATE, DAY))
  ),
  PRIMARY KEY (id)
);

Multi-level nesting. Computed fields inside structs. All from a SQL statement.

For arrays of objects (Elasticsearch nested type):

CREATE TABLE store (
  id INT NOT NULL,
  products ARRAY<STRUCT> FIELDS (
    name VARCHAR NOT NULL,
    description VARCHAR NOT NULL,
    price BIGINT NOT NULL
  ),
  PRIMARY KEY (id)
);

Partitioned Tables

Need time-based indices? One clause:

CREATE TABLE events (
  id INT,
  event_date DATE,
  payload VARCHAR,
  PRIMARY KEY (id)
)
PARTITION BY event_date (MONTH);

This generates an index template. Documents are automatically routed to monthly indices: events-2026-01, events-2026-02, etc. Supported granularities: YEAR, MONTH, WEEK, DAY.

CREATE TABLE AS SELECT

Clone and transform in one shot:

CREATE TABLE active_users AS
SELECT id, name, email FROM users
WHERE status = 'active';

Schema is inferred. Index is created. Data is bulk-copied. One statement.

ALTER TABLE: Zero-Downtime Migrations

This is where it gets interesting.

Remember the “add phone_number” saga from the beginning? With SoftClient4ES:

ALTER TABLE users ADD COLUMN phone_number KEYWORD;

One line. Done.

Behind the scenes, the engine:

Computes a structural diff between the current schema and the target
Creates a temporary index with the new mapping
Reindexes all documents
Performs an atomic alias swap
Cleans up the old index

If any step fails? Automatic rollback. Original index untouched.

ALTER TABLE is comprehensive:

-- Add a column (idempotent)
ALTER TABLE users ADD COLUMN IF NOT EXISTS phone_number KEYWORD;
-- Drop a column
ALTER TABLE users DROP COLUMN IF EXISTS old_field;
-- Rename a column
ALTER TABLE users RENAME COLUMN phone TO phone_number;
-- Change a type (safe - with automatic reindexing)
ALTER TABLE users ALTER COLUMN age SET DATA TYPE BIGINT;
-- Add a computed column
ALTER TABLE users ALTER COLUMN full_name SET SCRIPT AS (
  CONCAT(first_name, ' ', last_name)
);
-- Change analyzer options
ALTER TABLE users ALTER COLUMN name SET OPTIONS (analyzer = 'french');
-- Add a sub-field to a STRUCT
ALTER TABLE users ALTER COLUMN profile SET FIELD followers INT;
-- Set a default value
ALTER TABLE users ALTER COLUMN status SET DEFAULT 'active';
-- Add a comment
ALTER TABLE users ALTER COLUMN email SET COMMENT 'Primary contact email';

Type changes are validated: compatible types trigger automatic reindexing, incompatible types are rejected. No silent data corruption.

Schema Inspection: Know What You Have

Three commands that every DBA knows by heart:

-- List all indices (with pattern filtering)
SHOW TABLES;
SHOW TABLES LIKE 'user%';
-- Describe a table's schema
DESCRIBE TABLE users;
-- Reverse-engineer the exact CREATE TABLE statement
SHOW CREATE TABLE users;

SHOW CREATE TABLE is particularly powerful. It outputs the exact SQL needed to recreate the index — including all options, defaults, scripts, comments, and struct definitions. Perfect for version control.

Pipelines, Watchers, and Enrich Policies

DDL goes beyond tables. SoftClient4ES provides SQL syntax for Elasticsearch’s operational features.

Ingest Pipelines

CREATE OR REPLACE PIPELINE user_enrichment_pipeline
WITH PROCESSORS (
    SET (
        field = "status",
        if = "ctx.status == null",
        description = "status DEFAULT 'active'",
        ignore_failure = true,
        value = "active"
    ),
    ENRICH (
        policy_name = "department_info",
        field = "dept_id",
        target_field = "department",
        max_matches = 1,
        ignore_missing = true
    )
);

Watchers (Monitoring & Alerting)

CREATE OR REPLACE WATCHER high_error_rate AS
  EVERY 5 MINUTES
  FROM logs-* WHERE level = 'ERROR' WITHIN 5 MINUTES
  WHEN ctx.payload.hits.total > 100 DO
  notify LOG "High error rate: {{ctx.payload.hits.total}} errors in the last 5 minutes" AT ERROR
END

Enrich Policies

-- Create the policy
CREATE ENRICH POLICY user_enrichment
  FROM users
  ON user_id
  ENRICH name, email, department;
-- Execute it (builds the enrich index)
EXECUTE ENRICH POLICY user_enrichment;

All of this is SQL. All of it is version-controllable. All of it works on Elasticsearch 6, 7, 8, and 9.

Schema as Code: CI/CD Integration

Because DDL statements are idempotent (IF NOT EXISTS, IF EXISTS, OR REPLACE), they fit naturally into CI/CD pipelines:

-- migrations/v1_initial.sql
CREATE TABLE IF NOT EXISTS users (
  id INT NOT NULL,
  name VARCHAR DEFAULT 'anonymous',
  email KEYWORD,
  PRIMARY KEY (id)
);
CREATE TABLE IF NOT EXISTS orders (
  id INT NOT NULL,
  user_id INT NOT NULL,
  amount DOUBLE,
  status KEYWORD DEFAULT 'pending',
  created_at TIMESTAMP,
  PRIMARY KEY (id)
);

-- migrations/v2_add_phone.sql
ALTER TABLE users ADD COLUMN IF NOT EXISTS phone_number KEYWORD;
ALTER TABLE users ALTER COLUMN name SET OPTIONS (analyzer = 'french');

# CI pipeline — apply migrations
softclient4es --host $ES_HOST -f migrations/v1_initial.sql
softclient4es --host $ES_HOST -f migrations/v2_add_phone.sql

Version-controlled. Repeatable. Auditable. Applied the same way in staging, pre-prod, and production.

Compare this to the typical approach: a folder full of curl scripts, a wiki page with “Step 1: run this PUT request, Step 2: run this POST request, Step 3: pray”, and a senior engineer who is the only person who knows the right order.

The Type System

SoftClient4ES provides a clean SQL-to-Elasticsearch type mapping:

SQL Type	Elasticsearch Mapping
`INT`	`integer`
`BIGINT`	`long`
`DOUBLE`	`double`
`REAL`	`float`
`BOOLEAN`	`boolean`
`VARCHAR` / `TEXT`	`text`
`KEYWORD`	`keyword`
`DATE`	`date`
`TIMESTAMP`	`date`
`STRUCT`	`object`
`ARRAY<STRUCT>`	`nested`
`GEO_POINT`	`geo_point`

No more looking up “was it integer or int?” or “is it text or string?” in the Elasticsearch documentation. SQL types are the universal language.

Before & After

Task	Before (JSON + REST API)	After (SQL)
Create an index	30-line JSON PUT request	`CREATE TABLE users (...);`
Add a field	Create new index, reindex 50M docs, swap alias, cleanup	`ALTER TABLE users ADD COLUMN phone KEYWORD;`
Add a computed field	Write custom Painless script, inject into pipeline manually	`SCRIPT AS (DATE_DIFF(birthdate, CURRENT_DATE, YEAR))`
Inspect schema	`GET /users/_mapping` → parse nested JSON	`DESCRIBE TABLE users;`
Version-control schema	Copy-paste JSON into wiki	`SHOW CREATE TABLE users;` → commit to git
Migrate across environments	Custom shell scripts per environment	`softclient4es -f migrations/v2.sql`
Rollback failed migration	Manual — hope you have a backup	Automatic — built into ALTER TABLE

Getting Started

Install the REPL

# macOS / Linux
curl -fsSL https://raw.githubusercontent.com/SOFTNETWORK-APP/softclient4es/main/install.sh | bash

Create Your First Table

softclient4es --host localhost --port 9200

sql> CREATE TABLE users (
  ->   id INT NOT NULL,
  ->   name VARCHAR DEFAULT 'anonymous',
  ->   email KEYWORD,
  ->   created_at TIMESTAMP,
  ->   PRIMARY KEY (id)
  -> );
✅ Table created (42ms)

sql> DESCRIBE TABLE users;

| Field      | Type      | Null | Key | Default   | Comment | Script | Extra |
|------------|-----------|------|-----|-----------|---------|--------|-------|
| id         | INT       | no   | PRI | NULL      |         |        | ()    |
| name       | VARCHAR   | yes  |     | anonymous |         |        | ()    |
| email      | KEYWORD   | yes  |     | NULL      |         |        | ()    |
| created_at | TIMESTAMP | yes  |     | NULL      |         |        | ()    |

sql> SHOW CREATE TABLE users;

CREATE OR REPLACE TABLE users (
  id INT NOT NULL,
  name VARCHAR DEFAULT 'anonymous',
  email KEYWORD,
  created_at TIMESTAMP,
  PRIMARY KEY (id)
)

Quick Reference

Table Operations

-- Create
CREATE TABLE t (col TYPE, ..., PRIMARY KEY (col));
CREATE TABLE IF NOT EXISTS t (...);
CREATE OR REPLACE TABLE t (...);
CREATE TABLE t AS SELECT ... FROM source;
-- Modify
ALTER TABLE t ADD COLUMN [IF NOT EXISTS] col TYPE;
ALTER TABLE t DROP COLUMN [IF EXISTS] col;
ALTER TABLE t RENAME COLUMN old TO new;
ALTER TABLE t ALTER COLUMN col SET DATA TYPE new_type;
ALTER TABLE t ALTER COLUMN col SET SCRIPT AS (expr);
ALTER TABLE t ALTER COLUMN col SET DEFAULT value;
ALTER TABLE t ALTER COLUMN col SET OPTIONS (key = value);
ALTER TABLE t ALTER COLUMN col ADD FIELD sub_col TYPE;
-- Delete
DROP TABLE [IF EXISTS] t;
TRUNCATE TABLE t;
-- Inspect
SHOW TABLES [LIKE 'pattern'];
DESCRIBE TABLE t;
SHOW CREATE TABLE t;

Pipeline & Watcher Operations

-- Pipelines
CREATE [OR REPLACE] PIPELINE p WITH PROCESSORS (...);
ALTER PIPELINE p (ADD PROCESSOR ..., DROP PROCESSOR ...);
DROP PIPELINE [IF EXISTS] p;
-- Watchers
CREATE [OR REPLACE] WATCHER w AS ... END;
DROP WATCHER [IF EXISTS] w;
-- Enrich Policies (ES 7.5+)
CREATE [OR REPLACE] ENRICH POLICY ep FROM idx ON field ENRICH f1, f2;
EXECUTE ENRICH POLICY ep;
DROP ENRICH POLICY [IF EXISTS] ep;

Resources

DDL Documentation: Full DDL Reference
REPL Documentation: Full REPL Guide
GitHub: SoftClient4ES Repository
Discussions: Ask Questions
Website: softclient4es.dev

How does your team manage Elasticsearch schema changes today? If the answer involves a wiki page, a folder of curl scripts, and one person who “knows the right order” — there might be a better way.

P.S. — DDL works on Elasticsearch 6, 7, 8, and 9. Enrich policies and materialized views require ES 7.5+.

Next in the series: A 47-Line curl Script to Insert One Document. Seriously. — how DML brings INSERT, UPDATE, DELETE, and COPY INTO to Elasticsearch.

This post first appeared on Medium. This is the maintained version — free to read, no account, and corrected as the product moves.

Try it on your own cluster

SoftClient4ES runs SQL — DDL, DML, queries, cross-index JOINs and materialized views — on Elasticsearch 6 through 9.

Quick start Cross-index JOIN