📘 DDL Statements — SQL Gateway for Elasticsearch

Introduction

The SQL Gateway provides a full Data Definition Language (DDL) layer on top of Elasticsearch.
It allows defining tables, schemas, pipelines, mappings, settings, watchers, enrich policies using a relational syntax while generating the appropriate Elasticsearch structures:

indices (for non-partitioned tables)
index templates (for partitioned tables)
ingest pipelines (default or user-defined)
mappings and settings
metadata (primary key, generated columns, comments, options)
watchers (scheduled monitoring and alerting)
enrich policies (data enrichment from lookup indices)

The DDL engine is:

version-aware (ES6 → ES9)
client-agnostic (Jest, RHLC, Java Client)
schema-driven
round-trip safe (SHOW CREATE returns normalized SQL)

Table Model
Column Types & Mapping
Constraints & Column Options
Partitioning
CREATE TABLE
CREATE TABLE AS SELECT
ALTER TABLE
DROP TABLE
TRUNCATE TABLE
CREATE PIPELINE
ALTER PIPELINE
DROP PIPELINE
WATCHERS
CREATE WATCHER
DROP WATCHER
ENRICH POLICIES
CREATE ENRICH POLICY
EXECUTE ENRICH POLICY
DROP ENRICH POLICY
USING ENRICH POLICIES IN PIPELINES
Index Migration Workflow
VERSION COMPATIBILITY
QUICK REFERENCE

Table Model

A SQL table corresponds to:

SQL Definition	Elasticsearch Structure
`CREATE TABLE` without `PARTITION BY`	Concrete index
`CREATE TABLE` with `PARTITION BY`	Index template (legacy ES6 or composable ES7+)

Index-backed table (no partitioning)

CREATE TABLE users (
  id INT,
  name VARCHAR,
  PRIMARY KEY (id)
);

Creates:

index users
default pipeline users_ddl_default_pipeline
mapping + settings

Template-backed table (with partitioning)

The clause is PARTITION BY <column> (<granularity>) — the column first, the granularity in parentheses after it. The parentheses are required when a granularity is given; omit the granularity entirely and it defaults to DAY.

CREATE TABLE users (
  id INT,
  birthdate DATE,
  PRIMARY KEY (id)
)
PARTITION BY birthdate (MONTH);

Creates:

template users
default pipeline with date_index_name
indices generated dynamically:
- users-2025-01
- users-2025-02

Column Types & Mapping

The SQL Gateway supports the following type system:

SQL Type	Elasticsearch Mapping
`NULL`	`null`
`TINYINT`	`byte`
`SMALLINT`	`short`
`INT`	`integer`
`BIGINT`	`long`
`DOUBLE`	`double`
`REAL`	`float`
`BOOLEAN`	`boolean`
`VARCHAR` \| `TEXT`	`text` + optional `keyword` subfield
`CHAR`	`text`
`DECIMAL` \| `NUMERIC` \| `DEC`	`double` (approximate — no exact decimal type)
`SIGNED` \| `UNSIGNED`	`long` (both are 64-bit signed; aliases of `BIGINT`)
`KEYWORD`	`keyword`
`DATE`	`date`
`TIMESTAMP`	`date`
`STRUCT`	`object` with nested properties
`ARRAY<STRUCT>`	`nested`
`GEO_POINT`	`geo_point`

A length, precision or scale may be written on any type SQL parameterises — c VARCHAR(255), c CHAR(1), c DECIMAL(10,2), c BIGINT(20) — and is accepted and ignored, exactly as in a cast: Elasticsearch has no length- or scale-constrained scalar type. c VARCHAR(255) creates the same field as c VARCHAR, and the column re-renders without the parameter so SHOW CREATE TABLE shows what was actually created.

🧩 Nested and Structured Data

FIELDS for Multi-fields

FIELDS (...) can be used to define multi-fields for text columns.
This allows indexing the same column in multiple ways (e.g., with different analyzers).

Example:

CREATE TABLE docs (
  content VARCHAR FIELDS (
    keyword VARCHAR OPTIONS (analyzer = 'keyword'),
    english VARCHAR OPTIONS (analyzer = 'english')
  )
)

content is indexed as text.
content.keyword is a keyword sub-field.
content.english is a text sub-field with the English analyzer.

A column may declare its sub-fields and a computed value together, sub-fields first, as SHOW TABLE renders it (since 0.24.0):

CREATE TABLE docs (
  k KEYWORD,
  c VARCHAR FIELDS (raw KEYWORD) SCRIPT AS (UPPER(k))
)

The opposite order, SCRIPT AS (…) FIELDS (…), is a syntax error.

FIELDS for STRUCT or NESTED OBJECTS

FIELDS (...) also enables the definition of STRUCT types, representing hierarchical data.

Example:

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 VARCHAR
    ),
    join_date DATE,
    seniority INT SCRIPT AS (DATEDIFF(CURRENT_DATE, profile.join_date, DAY))
  )
)

profile is a STRUCT column containing multiple fields.
address is a nested STRUCT inside profile.

FIELDS for ARRAY

Example:

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

products is an ARRAY<STRUCT> column.
Maps naturally to Elasticsearch nested.

Notes

On VARCHAR → defines multi-fields
On STRUCT → defines object fields
On ARRAY<STRUCT> → defines nested fields
Sub-fields support:
- nested FIELDS
- DEFAULT
- NOT NULL
- COMMENT
- OPTIONS
- SCRIPT AS — optionally STORED (except inside ARRAY<STRUCT>)
Multi-level nesting is supported.

Constraints & Column Options

Primary Key

id INT,
PRIMARY KEY (id)

Used for:

document ID generation
upsert semantics
COPY INTO conflict resolution

🔑 Composite Primary Keys

SoftClient4ES supports composite primary keys in SQL.

Syntax

CREATE TABLE users (
  id INT NOT NULL,
  birthdate DATE NOT NULL,
  name VARCHAR,
  PRIMARY KEY (id, birthdate)
);

Elasticsearch Translation

{
  "processors": [
    {
      "set": {
        "field": "_id",
        "value": "{{id}}||{{birthdate}}"
      }
    }
  ]
}

Notes

Composite PK fields must be immutable.
Avoid long _id values.
Mapping rules:
- PRIMARY KEY (id) → _id = id
- PRIMARY KEY (id, birthdate) → _id = "{{id}}||{{birthdate}}"

Partitioning

Partitioning routes documents to time-based indices using date_index_name.

Supported Granularities

SQL Granularity	ES `date_rounding`	Example Index Name
YEAR	"y"	users-2025
MONTH	"M"	users-2025-12
DAY (default)	"d"	users-2025-12-10
HOUR	"h"	users-2025-12-10-09
MINUTE	"m"	users-2025-12-10-09-46
SECOND	"s"	users-2025-12-10-09-46-30

CREATE TABLE

Basic Example

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

Partitioned Example

CREATE TABLE users (
  id INT,
  birthdate DATE,
  PRIMARY KEY (id)
)
PARTITION BY birthdate (MONTH);

Table Options (index settings)

Index settings are declared with a table-level OPTIONS (…) clause after the column list:

CREATE TABLE users (
  id INT,
  name VARCHAR,
  PRIMARY KEY (id)
) OPTIONS (number_of_shards = 1, number_of_replicas = 0);

Any index setting can be passed — for example default_pipeline to route inserts through a custom ingest pipeline (see Using Enrich Policies in Pipelines):

CREATE TABLE events (
  id INT,
  user_id KEYWORD,
  PRIMARY KEY (id)
) OPTIONS (default_pipeline = 'events_enriched');

⚠️ OPTIONS, not WITH — WITH (…) belongs to CREATE MATERIALIZED VIEW. A CREATE TABLE … WITH (…) is rejected by the parser (the whole statement must parse).

CREATE TABLE AS SELECT

CREATE TABLE new_users AS
SELECT id, name FROM users;

The gateway:

infers the schema
generates mappings
creates index or template
populates data using the Bulk API

A computed column keeps its script only if the projection carries every column it reads. CREATE TABLE monthly AS SELECT year FROM orders, where orders.year is SCRIPT AS (YEAR(order_date)), creates monthly.year as an ordinary stored column: its values come from the query that populates the table, and no pipeline is created for it. Select order_date as well and the script travels with the column. The dropped script is reported at WARN, naming the column and the operands the projection does not select.

The same applies to a pipeline processor the source table declares: one that writes — or, where the processor's operands are determinable, reads — a column the projection does not carry is not copied either. A processor the source's pipeline holds without the source table declaring it (one added with ALTER PIPELINE … ADD PROCESSOR, say) is not copied.

If the copied script would otherwise be invalid in the new table — an operand whose declared type the function cannot take, say — the statement is rejected, and the reason is worded exactly as the equivalent CREATE TABLE / ALTER TABLE rejection words it (the surrounding sentence differs: each statement names itself, and the ALTER form adds a remedy that does not apply here). The check runs before anything is created, and before CREATE OR REPLACE deletes the existing index, so a refused statement leaves the cluster exactly as it was.

A query may not select from the table it is creating: CREATE TABLE t AS SELECT … FROM t is refused ("CREATE TABLE t cannot select from itself."), and so is a query that reads it as a JOIN leg or in a WHERE subquery at any depth. A CREATE OR REPLACE is also refused when an index pattern or a list it reads matches the table it replaces and does not exclude it (CREATE OR REPLACE TABLE logs_1 AS SELECT … FROM logs_*); FROM logs_*,-logs_1, and a new table name the pattern matches, run.

A table created this way is an ordinary table, even when the query selects from a materialized view: it is not itself a view, and nothing refreshes it.

Computed columns: `SCRIPT AS` and `STORED`

A column declared SCRIPT AS (expression) is computed on ingest: the expression becomes a script processor in the table's default pipeline, and every document written to the index has the value derived for it.

CREATE TABLE users (
  id INT,
  birthdate DATE,
  age INT SCRIPT AS (TIMESTAMPDIFF(YEAR, birthdate, CURRENT_DATE))
);

Adding STORED keeps the expression as documentation of how the column was derived, without computing it here:

CREATE TABLE users_view (
  id INT,
  birthdate DATE,
  age INT SCRIPT AS (TIMESTAMPDIFF(YEAR, birthdate, CURRENT_DATE)) STORED
);

	ingest pipeline	`DESCRIBE` `Script` column	`SHOW CREATE`
`SCRIPT AS (...)`	runs the script	shows the expression	`SCRIPT AS (...)`
`SCRIPT AS (...) STORED`	no processor	shows the expression	`SCRIPT AS (...) STORED`

Use STORED when the value already arrives materialized and only its lineage needs recording — the value is written by whatever produced it upstream, not recomputed on arrival.

The primary consumer is the materialized-view pipeline. A view's computed column is calculated once, in the stage that owns it; every index downstream of that stage carries the column with STORED, so the value flows through unchanged. Recomputing it downstream would be redundant, and wrong whenever the expression's source columns did not survive the transform — the projection would then overwrite a good value with NULL.

Note — STORED describes this index only. The same column can be an executing SCRIPT AS in the index that computes it and a STORED one in every index it flows into.

Conversions inside a computed column

The expression is resolved against the columns of the same table, so a CAST over a sibling column converts on the way in:

CREATE TABLE orders (
  id INT,
  zip_code KEYWORD,
  zip_n BIGINT SCRIPT AS (CAST(zip_code AS BIGINT))    -- stores the NUMBER 75001
);

ALTER TABLE ... ALTER COLUMN ... SET SCRIPT AS (...) resolves the same way, against the table as it currently exists.

TRY_CAST / SAFE_CAST in a computed column. A safe cast converts the values it can and yields NULL for the ones it cannot, instead of failing the document:

CREATE TABLE orders (
  id INT,
  zip_code KEYWORD,
  zip_n BIGINT SCRIPT AS (TRY_CAST(zip_code AS BIGINT))
);

A row whose zip_code is '75001' stores zip_n = 75001. A row whose zip_code is 'N/A' is indexed with zip_n set to NULL: the stored _source carries "zip_n": null, no doc value is written, so WHERE zip_n IS NOT NULL and any aggregation over zip_n skip that row, while SELECT zip_n returns the row with an empty value rather than omitting it. Use a plain CAST when a value that cannot be converted should be a loud failure instead.

A failed safe cast is a NULL operand for whatever wraps it, exactly like a column that is null: CONCAT(TRY_CAST(zip_code AS BIGINT), '-X') is '-X' for that row — CONCAT skips a NULL argument, as DuckDB does, never the text null-X — and UPPER(...), ABS(...) or ROUND(...) over it is NULL.

What "never rejected" rests on. The generated script processor carries ignore_failure: true, so a document survives even a conversion the engine cannot express as a null. Turn ignore_failure off on that processor — through ALTER PIPELINE — and a failure rejects the document at index time like any other pipeline failure.

Before 0.24.0 this never worked, in any shape: the generated script was malformed, so the CREATE TABLE itself failed. If you worked around it with a plain CAST plus a CASE, the safe cast now expresses it directly.

Arithmetic over a cast. From 0.24.0 the cast decides the arithmetic, in a computed column, in a WHERE and in a projection alike. Two consequences, both of them changes to statements that ran without error before:

A cast of a string column now COMPUTES. SCRIPT AS (CAST(amount_str AS BIGINT) + fee) sums the two. Before 0.24.0 it concatenated the two renderings — '125' and 7 stored 1257 rather than 132, with no error.
A cast of a NUMERIC column to a different numeric type now decides the result type. CAST(price AS INTEGER) / 2 over price = 5.0 is 2, because CAST(price AS INTEGER) really is an integer; before 0.24.0 the column's own type won and it was 2.5. The widening direction moves the same way: CAST(qty AS DOUBLE) / 2 over an INTEGER column is now 2.5 where it was 2. WHERE clauses over such an expression therefore match a different set of rows.
A numeric column cast to a string now CONCATENATES. CAST(qty AS KEYWORD) + 2 yields '42'-style text; before 0.24.0 the string was silently parsed back to a number and the expression computed. Under -, * and / the same shape is now a runtime failure rather than a silently un-done cast.

Arithmetic with no cast, and a cast to the column's own type, are unchanged.

NULLIF in a computed column. From 0.24.0 NULLIF follows what each argument renders, the same rule as the arithmetic above, and a function argument is evaluated once:

CREATE TABLE orders (
  id INT,
  zip_code KEYWORD,
  zip_n BIGINT SCRIPT AS (NULLIF(CAST(zip_code AS BIGINT), 0)),   -- 0 becomes NULL
  label KEYWORD SCRIPT AS (NULLIF(UPPER(zip_code), 'N/A'))
);

Before 0.24.0 both columns were rejected at CREATE TABLE: the first because the comparison was decided by zip_code's declared KEYWORD type rather than by the cast, the second because the function argument was spliced into the comparison unparenthesised. A CASE as the first argument compiled and could return the wrong value. See NULLIF.

Re-run CREATE TABLE / ALTER TABLE ... SET SCRIPT AS and reindex for any table whose computed column uses one of these shapes; a stored computed column of a moved shape is also reported as changed by the next ALTER TABLE.

An operand must name a column the table declares, and must have a type the function can take. CREATE TABLE t (c INTEGER SCRIPT AS (YEAR(nosuch))) and CREATE TABLE t (k KEYWORD, c INTEGER SCRIPT AS (YEAR(k))) are both rejected with a message naming the column. Declaration ORDER does not matter — a computed column may reference one declared after it, and may reference another computed column.

Two cases are deliberately not checked, because the generated script coerces the value and works: a string function's argument (CONCAT(name, ' ', id) over a numeric id), and a STORED column, whose source columns need not exist in the table that carries it.

The rule applies to a live table, and to EVERY ALTER on it. A table is re-validated as a whole before an ALTER is applied — it is read back from Elasticsearch, where a computed column's SQL expression is stored — so ALTER TABLE t DROP COLUMN n is rejected while a computed column of t reads n, and so is any other alteration of t (ADD COLUMN, a setting, an alias) while t carries a computed column that does not satisfy the rule. In that case the message names the offending column rather than the one you altered, and says so. The remedy is to drop or redefine that column: ALTER TABLE t DROP COLUMN <the computed column> removes the reference along with the column, and is accepted once no other computed column of t is still invalid — validation reports every offending column, so a table with two of them has to lose (or fix) both before any other alteration is accepted.

A stored expression this engine can no longer parse is reported at WARN and its references go unchecked, so an unparseable one never makes a table un-alterable.

Date and time functions in a computed column. In an ingest script the operand is the raw JSON value of the incoming document, not the temporal object a query sees, so YEAR(created), DATE_TRUNC(...), DATE_ADD(...), DATE_FORMAT(...) and DATE_DIFF(...) parse it first. Both shapes Elasticsearch accepts into a date field work — an ISO string ("2025-01-10", "2025-01-10 14:30:00", "2025-01-10T14:30:00Z") and epoch milliseconds (1736467200000) — and the column's DECLARED type chooses how a string is read: a DATE column reads a date-only spelling as that day, and a date-time spelling (which Elasticsearch accepts into it) as the UTC day of the instant it spells. CURRENT_DATE, CURRENT_TIMESTAMP, NOW and TODAY are the ingest time of the document.

A document that does not carry the source field leaves the computed column absent, as before.

Before 0.23.0 these functions failed at ingest and the column was silently missing from the stored document. If a table was created on an earlier version, re-run its CREATE TABLE (or ALTER TABLE ... ALTER COLUMN ... SET SCRIPT AS) so the pipeline is re-derived, and reindex any documents whose computed column is absent.

ALTER TABLE

Supported statements:

ADD COLUMN [IF NOT EXISTS] column_definition
DROP COLUMN [IF EXISTS] column_name
RENAME COLUMN old_name TO new_name
ALTER COLUMN column_name SET SCRIPT AS (sql) [STORED]
ALTER COLUMN column_name DROP SCRIPT
ALTER COLUMN column_name SET|ADD OPTION (key = value)
ALTER COLUMN column_name DROP OPTION key
ALTER COLUMN column_name SET COMMENT 'comment'
ALTER COLUMN column_name DROP COMMENT
ALTER COLUMN column_name SET DEFAULT value
ALTER COLUMN column_name DROP DEFAULT
ALTER COLUMN column_name SET NOT NULL
ALTER COLUMN column_name DROP NOT NULL
ALTER COLUMN column_name SET DATA TYPE new_type
ALTER COLUMN column_name SET|ADD FIELD field_definition
ALTER COLUMN column_name DROP FIELD field_name
ALTER COLUMN column_name SET FIELDS (...)
SET|ADD MAPPING key = value
DROP MAPPING key
SET|ADD SETTING key = value
DROP SETTING key
SET|ADD ALIAS alias_name = value
DROP ALIAS alias_name
SET SCHEMA CACHE TTL [=] 'duration'
DROP SCHEMA CACHE TTL

ALTER TABLE changes only the ingest processors the table declares: those of its computed columns, defaults, primary key and partitioning. A processor its pipeline holds otherwise (added with ALTER PIPELINE … ADD PROCESSOR, through the Elasticsearch API, or in a pipeline named by default_pipeline / final_pipeline) — a script processor that runs a stored script by its id included — is not changed or removed by ALTER TABLE (one exception closes this note):

When no declared processor changes, the pipeline is not written.

When one does, the pipeline is written again, with each such processor as Elasticsearch returned it (the same JSON values; a number or a string escape may be spelled differently, 1.50 as 1.5, \/ as /). One placed before every declared processor stays first; every other one, including one placed between two declared processors, follows the declared ones. The pipeline's own on_failure, version and _meta are kept as Elasticsearch returned them, and so is its description, unless it is one elasticsql wrote for the table with this Elasticsearch major's rendering, whatever the pipeline is named: that one is written again for the new declaration. A description written under another major, or by an older release, is kept.

When the statement reindexes, the documents are migrated through a pipeline holding only the declared processors, or with no pipeline (_none) when the table declares none; the index then gets its pipeline back. When the table declares no processor for a pipeline and that pipeline holds nothing else, the index is left with _none.

If a step after the migration fails — it throws, or Elasticsearch answers it with an error, such as a pipeline it refuses with a 400 — the index's pipeline settings are restored first, then the temporary pipeline is dropped, and the statement answers that failure. If the restore itself fails, the temporary pipeline is kept, since the index still names it.

SHOW CREATE TABLE shows the declaration; SHOW CREATE PIPELINE shows every processor.

elasticsql treats a change to default_pipeline or final_pipeline as requiring a reindex (Elasticsearch documents both settings as dynamic), and that reindex keeps the table's current pipelines: ALTER TABLE … SET SETTING default_pipeline = '…' does not rename the table's pipeline. In such a statement the current pipeline is compared against the one named, so the processors it holds without the table declaring them are not protected: a declared change rewrites it with the declared processors only.

Table-level clauses take no parentheses

The table-level MAPPING, SETTING and ALIAS clauses are written as a bare key = value, without parentheses — unlike the column-level ALTER COLUMN … SET|ADD OPTION (key = value):

ALTER TABLE orders (
  SET MAPPING _meta.owner = 'analytics',
  SET SETTING index.refresh_interval = '1s',
  SET ALIAS recent_orders = (filter = (range = (order_date = (gte = 'now-7d'))))
);

The parentheses around the statement list are optional and independent of any single clause — ALTER TABLE orders SET SETTING index.refresh_interval = '1s' is equally valid.

A value may be a scalar ('1s', true, 2), an array (['a', 'b']), or a nested object written with parentheses: (key = value, key = (nested = value)). The {…} brace form is for STRUCT column values in INSERT, and is not accepted here.

SET SCHEMA CACHE TTL

How long a client may cache this table's schema before reading it from Elasticsearch again. The volatility of a mapping is a property of the index, so the value lives with the index and overrides the client's elastic.schema-cache.ttl default for this table alone:

ALTER TABLE orders SET SCHEMA CACHE TTL = '1h';
ALTER TABLE orders DROP SCHEMA CACHE TTL;

The = is optional (SET SCHEMA CACHE TTL '1h'). Durations use the HOCON spellings — 30s, 10m, 1h, or a bare number of milliseconds; anything else is rejected when the statement is parsed, before it reaches the cluster.

This is sugar: it writes _meta.schema_cache_ttl, so it is exactly equivalent to

ALTER TABLE orders SET MAPPING _meta.schema_cache_ttl = '1h';

and a table can be created with one:

CREATE TABLE IF NOT EXISTS orders (
  id INT NOT NULL
) OPTIONS (mappings = (_meta = (schema_cache_ttl = '1h')));

Since the schema now reaches every executed statement, a stale entry means Painless emitted for the previous mapping, not merely a stale column list — so shorten this for a table whose mapping changes under a running client. Note that changing it is self-referential: another client notices the new value only when its current entry expires, i.e. after at most one old period. The primary shard-count cache used by sliced paging follows the same TTL. See client/search.

Type Changes and Safety

When applying ALTER COLUMN column_name SET DATA TYPE new_type, the SQL Gateway computes a structural diff between the current schema and the target schema.

Type changes fall into two categories:

Convertible types (SQLTypeUtils.canConvert(from, to) = true)
The change is allowed but requires a full reindex of the underlying data.
The Gateway automatically performs the reindex operation and swaps aliases when complete.
These changes are classified as UnsafeReindex.
Incompatible types (SQLTypeUtils.canConvert(from, to) = false)
The change is not allowed and the ALTER TABLE statement fails.
These changes are classified as Impossible.

This is the only case where an ALTER TABLE operation can be rejected for safety reasons.
All other ALTER operations (adding/dropping columns, renaming, modifying options, modifying nested fields, etc.) are allowed.

DROP TABLE

DROP TABLE IF EXISTS users;

Deletes:

index (non-partitioned)
template (partitioned)

TRUNCATE TABLE

TRUNCATE TABLE users;

Deletes all documents while keeping:

mapping
settings
pipeline
template (if any)

CREATE PIPELINE

CREATE OR REPLACE PIPELINE user_pipeline
WITH PROCESSORS (
    SET (
        field = "name",
        if = "ctx.name == null",
        description = "DEFAULT 'anonymous'",
        ignore_failure = true,
        value = "anonymous"
    ),
    SCRIPT (
        description = "age INT SCRIPT AS (TIMESTAMPDIFF(YEAR, birthdate, CURRENT_DATE))",
        lang = "painless",
        source = "...",
        ignore_failure = true
    ),
    DATE_INDEX_NAME (
        field = "birthdate",
        index_name_prefix = "users-",
        date_formats = ["yyyy-MM"],
        date_rounding = "M",
        separator = "-",
        ignore_failure = true
    )
);

DROP PIPELINE

DROP PIPELINE IF EXISTS user_pipeline;

ALTER PIPELINE

ALTER PIPELINE IF EXISTS user_pipeline (
    ADD PROCESSOR SET (
        field = "status",
        if = "ctx.status == null",
        description = "status DEFAULT 'active'",
        ignore_failure = true,
        value = "active"
    ),
    DROP PROCESSOR SET (_id)
);

Watchers

Watchers provide scheduled monitoring and alerting on Elasticsearch data.
A watcher consists of:

Trigger — scheduling (cron or interval)
Input — data source (search, simple, HTTP, chain, or none)
Condition — evaluation logic (always, never, compare, script)
Actions — what to do when condition is met (logging, webhook)

⚠️ Limitations

The following Elasticsearch Watcher features are not supported in this DSL:

Feature	Status
Email action	❌ Not supported
Slack action	❌ Not supported
PagerDuty action	❌ Not supported
Jira action	❌ Not supported
Index action	❌ Not supported
Transform (root level)	❌ Not supported
Transform (action level)	❌ Not supported

Only logging and webhook actions are currently implemented.

CREATE WATCHER

Syntax

CREATE [OR REPLACE] WATCHER watcher_name AS
  trigger_clause
  input_clause
  condition_clause
  DO
  action_name [AS] action_definition [, ...]
  END

Note: The AS keyword before action definitions is optional.

Triggers

Triggers define when the watcher executes.

Trigger Type	Syntax	Example
Interval	`EVERY n unit`	`EVERY 5 MINUTES`
Cron	`AT SCHEDULE 'expression'`	`AT SCHEDULE '0 /5 * * ?'`

Supported time units:

Unit	Singular	Plural
Milliseconds	`MILLISECOND`	`MILLISECONDS`
Seconds	`SECOND`	`SECONDS`
Minutes	`MINUTE`	`MINUTES`
Hours	`HOUR`	`HOURS`
Days	`DAY`	`DAYS`
Weeks	`WEEK`	`WEEKS`
Months	`MONTH`	`MONTHS`
Years	`YEAR`	`YEARS`

Examples:

-- Execute every 30 seconds
EVERY 30 SECONDS

-- Execute every 5 minutes
EVERY 5 MINUTES

-- Execute daily
EVERY 1 DAY

-- Execute with cron expression (every day at 9 AM)
AT SCHEDULE '0 0 9 * * ?'

Inputs

Inputs define the data source for the watcher.

No Input

WITH NO INPUT

No data is loaded. The watcher executes with an empty payload.

Simple Input

WITH INPUT (key1 = value1, key2 = value2, ...)

Provides a static payload.

Example:

WITH INPUT (keys = ["value1", "value2"], threshold = 10)

Generates:

{
  "input": {
    "simple": {
      "keys": ["value1", "value2"],
      "threshold": 10
    }
  }
}

Search Input

FROM index_name [, index_name2, ...] [WHERE criteria] [WITHIN n unit]

Queries Elasticsearch indices.

Example:

FROM my_index WITHIN 2 MINUTES

FROM logs-*, metrics-* WHERE level = 'ERROR' WITHIN 5 MINUTES

JOIN is not supported in a search input. A watcher input maps to a single Elasticsearch search request over a list of indices — there is no join engine behind it. A FROM clause containing JOIN (or JOIN UNNEST(...)) is rejected at parse time:
JOIN is not supported in a watcher input (JOIN customers AS c ON o.customer_id = c.id): a watcher
input can only search one or more indices (FROM index1, index2). Pre-join the sources with a
MATERIALIZED VIEW and have the watcher search the view.
Pre-join the sources with a MATERIALIZED VIEW and point the watcher at the view instead.

A table alias may be used and is resolved in the WHERE, so FROM orders o WHERE o.status = 'FAILED' searches index orders for documents whose status field is FAILED.

Qualifiers are single-index only. FROM a, b is a legitimate multi-index search, but one Elasticsearch search applies one query to every index it names — it can neither join them nor scope a predicate to one of them. A table-qualified column over a multi-index FROM is therefore rejected, whether it correlates the indices (a join in ANSI-89 clothing) or only looks like scoping:
FROM orders o, customers c WHERE o.customer_id = c.id  -- rejected: a join
FROM orders, refunds WHERE orders.status = 'FAILED'    -- rejected: would filter refunds too
FROM logs-2025, logs-2024 WHERE level = 'ERROR'        -- fine: no qualifier

Generates:

{
  "input": {
    "search": {
      "request": {
        "indices": ["my_index"],
        "body": {
          "query": {"match_all": {}}
        }
      },
      "timeout": "2m"
    }
  }
}

HTTP Input

HTTP requests can be defined in two ways:

Option 1: Full URL String

WITH INPUT method "full_url" [HEADERS (...)] [BODY "body"] [TIMEOUT (...)]

Example:

WITH INPUT GET "https://api.example.com:443/data?param=value" 
  HEADERS ("Authorization" = "Bearer token") 
  TIMEOUT (connection = "5s", read = "30s")

Option 2: Decomposed URL Components

WITH INPUT method PROTOCOL protocol HOST "hostname" [PORT port] [PATH "path"] [PARAMS (...)] [HEADERS (...)] [BODY "body"] [TIMEOUT (...)]

Example:

WITH INPUT GET PROTOCOL https HOST "api.example.com" PORT 443 PATH "/data" 
  PARAMS (param = "value")
  HEADERS ("Authorization" = "Bearer token") 
  TIMEOUT (connection = "5s", read = "30s")

Components:

Component	Required	Description
`method`	✔	HTTP method: `GET`, `POST`, `PUT`, `DELETE`, `HEAD`
URL or `PROTOCOL`	✔	Either full URL string or protocol (`http`/`https`)
`HOST`	✔*	Hostname (required if using decomposed format)
`PORT`	✖	Port number (default: 80 for http, 443 for https)
`PATH`	✖	URL path
`PARAMS`	✖	Query parameters as key-value pairs
`HEADERS`	✖	HTTP headers as key-value pairs
`BODY`	✖	Request body (quoted string)
`TIMEOUT`	✖	Connection and read timeouts

⚠️ Note: Only HEAD, GET, POST, PUT, and DELETE methods are supported by Elasticsearch.

Generates:

{
  "input": {
    "http": {
      "request": {
        "scheme": "https",
        "host": "api.example.com",
        "port": 443,
        "method": "get",
        "path": "/data",
        "params": {"param": "value"},
        "headers": {"Authorization": "Bearer token"},
        "connection_timeout": "5s",
        "read_timeout": "30s"
      }
    }
  }
}

Chain Input

WITH INPUTS name1 [AS] input1, name2 [AS] input2, ...

Combines multiple inputs. Each input is named and can reference previous inputs in the chain.

Note: The AS keyword is optional.

Example:

WITH INPUTS 
  search_data FROM my_index WITHIN 2 MINUTES, 
  http_data GET "https://api.example.com/enrich" 
    HEADERS ("Authorization" = "Bearer token") 
    TIMEOUT (connection = "5s")

Generates:

{
  "input": {
    "chain": {
      "inputs": [
        {
          "search_data": {
            "search": {
              "request": {
                "indices": ["my_index"],
                "body": {"query": {"match_all": {}}}
              },
              "timeout": "2m"
            }
          }
        },
        {
          "http_data": {
            "http": {
              "request": {
                "scheme": "https",
                "host": "api.example.com",
                "port": 443,
                "method": "get",
                "path": "/enrich",
                "headers": {"Authorization": "Bearer token"},
                "connection_timeout": "5s"
              }
            }
          }
        }
      ]
    }
  }
}

Conditions

Conditions determine whether actions should execute.

Condition Type	Syntax	Description
`ALWAYS`	`ALWAYS DO`	Always triggers actions
`NEVER`	`NEVER DO`	Never triggers (useful for testing)
Compare	`WHEN path operator value DO`	Compare field to value
Compare (date math)	`WHEN path operator date_function DO`	Compare field to date math expression
Script	`WHEN SCRIPT '...' ... RETURNS TRUE DO`	Custom Painless condition

Always / Never Conditions

ALWAYS DO
-- actions
END

NEVER DO
-- actions
END

Compare Condition

WHEN ctx.payload.hits.total > 100 DO
-- actions
END

Supported operators: >, >=, <, <=, =, !=

With date math:

WHEN ctx.execution_time > DATE_SUB(CURRENT_DATE, INTERVAL 5 DAY) DO
-- actions
END

Generates:

{
  "condition": {
    "compare": {
      "ctx.execution_time": {
        "gt": "now-5d/d"
      }
    }
  }
}

Script Condition

WHEN SCRIPT 'ctx.payload.hits.total > params.threshold' 
  USING LANG 'painless' 
  WITH PARAMS (threshold = 10) 
  RETURNS TRUE 
DO
-- actions
END

Generates:

{
  "condition": {
    "script": {
      "source": "ctx.payload.hits.total > params.threshold",
      "lang": "painless",
      "params": {
        "threshold": 10
      }
    }
  }
}

Actions

Actions define what happens when the condition is met.

Note: The AS keyword before action definitions is optional.

Logging Action

action_name [AS] LOG "message" [AT level] [FOREACH "path"] [LIMIT n]

Component	Required	Description
Message	✔	Log message (supports Mustache templates)
`AT level`	✖	Log level: `DEBUG`, `INFO`, `WARN`, `ERROR` (default: `INFO`)
`FOREACH`	✖	Path to iterate over
`LIMIT`	✖	Maximum iterations (default: 100)

Example:

log_action LOG "Alert: {{ctx.payload.hits.total}} errors detected" AT ERROR FOREACH "ctx.payload.hits.hits" LIMIT 500

Generates:

{
  "actions": {
    "log_action": {
      "foreach": "ctx.payload.hits.hits",
      "max_iterations": 500,
      "logging": {
        "text": "Alert: {{ctx.payload.hits.total}} errors detected",
        "level": "error"
      }
    }
  }
}

Webhook Action

Webhook requests can be defined in two ways, similar to HTTP inputs:

Option 1: Full URL String

action_name [AS] WEBHOOK method "full_url" [HEADERS (...)] [BODY "body"] [TIMEOUT (...)] [FOREACH "path"] [LIMIT n]

Example:

webhook_action WEBHOOK POST "https://api.example.com:443/alert?source={{ctx.watch_id}}" 
  HEADERS ("Content-Type" = "application/json") 
  BODY "{\"message\": \"Alert triggered\"}" 
  TIMEOUT (connection = "10s", read = "30s")

Option 2: Decomposed URL Components

action_name [AS] WEBHOOK method PROTOCOL protocol HOST "hostname" [PORT port] [PATH "path"] [PARAMS (...)] [HEADERS (...)] [BODY "body"] [TIMEOUT (...)] [FOREACH "path"] [LIMIT n]

Example:

webhook_action WEBHOOK POST PROTOCOL https HOST "api.example.com" PORT 443 PATH "/alert"
  PARAMS (source = "{{ctx.watch_id}}")
  HEADERS ("Content-Type" = "application/json") 
  BODY "{\"message\": \"Alert triggered\"}" 
  TIMEOUT (connection = "10s", read = "30s")

Component	Required	Description
Method	✔	HTTP method: `GET`, `POST`, `PUT`, `DELETE`, `HEAD`
URL or `PROTOCOL`	✔	Either full URL string or protocol
`HOST`	✔*	Hostname (required if using decomposed format)
`PORT`	✖	Port number
`PATH`	✖	URL path
`PARAMS`	✖	Query parameters
`HEADERS`	✖	HTTP headers as key-value pairs
`BODY`	✖	Request body (quoted string, supports Mustache)
`TIMEOUT`	✖	Connection and read timeouts
`FOREACH`	✖	Path to iterate over
`LIMIT`	✖	Maximum iterations (default: 100)

⚠️ Note: Only HEAD, GET, POST, PUT, and DELETE methods are supported by Elasticsearch.

Generates:

{
  "actions": {
    "webhook_action": {
      "webhook": {
        "scheme": "https",
        "host": "api.example.com",
        "port": 443,
        "method": "post",
        "path": "/alert",
        "params": {"source": "{{ctx.watch_id}}"},
        "headers": {"Content-Type": "application/json"},
        "body": "{\"message\": \"Alert triggered\"}",
        "connection_timeout": "10s",
        "read_timeout": "30s"
      }
    }
  }
}

Complete Examples

Monitor with search input and logging

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

Webhook with simple input and script condition

CREATE OR REPLACE WATCHER threshold_alert AS
  EVERY 1 HOUR
  WITH INPUT (keys = ["server1", "server2", "server3"])
  WHEN SCRIPT 'ctx.payload.keys.size() > params.threshold' 
    USING LANG 'painless' 
    WITH PARAMS (threshold = 2) 
    RETURNS TRUE 
  DO
  alert WEBHOOK POST "https://hooks.example.com/webhook" 
    HEADERS ("Content-Type" = "application/json") 
    BODY "{\"text\": \"Threshold exceeded for {{ctx.payload._value}}\"}" 
    FOREACH "ctx.payload.keys" 
    LIMIT 10
END

Daily report with cron trigger

CREATE OR REPLACE WATCHER daily_report AS
  AT SCHEDULE '0 0 9 * * ?'
  FROM orders WHERE status = 'completed' WITHIN 1 DAY
  ALWAYS DO
  send_report WEBHOOK POST "https://api.company.com/reports" 
    HEADERS ("Authorization" = "Bearer {{ctx.metadata.api_key}}") 
    BODY "{\"date\": \"{{ctx.execution_time}}\", \"total_orders\": {{ctx.payload.hits.total}}}" 
    TIMEOUT (connection = "10s", read = "60s")
END

Chain input with multiple actions

CREATE OR REPLACE WATCHER enriched_alert AS
  AT SCHEDULE '0 */15 * * * ?'
  WITH INPUTS 
    alerts FROM alerts-* WHERE severity = 'critical' WITHIN 15 MINUTES,
    context GET PROTOCOL https HOST "api.internal.com" PATH "/context" 
      HEADERS ("X-API-Key" = "secret123")
  WHEN ctx.payload.alerts.hits.total > 0 DO
  log_alert LOG "Critical alerts: {{ctx.payload.alerts.hits.total}}" AT ERROR,
  notify_ops WEBHOOK POST "https://alerting.example.com/alert" 
    HEADERS ("Content-Type" = "application/json") 
    BODY "{\"alerts\": {{ctx.payload.alerts.hits.total}}, \"context\": \"{{ctx.payload.context.environment}}\"}"
END

DROP WATCHER

DROP WATCHER [IF EXISTS] watcher_name;

Deletes the watcher and stops its execution.

Example:

DROP WATCHER IF EXISTS high_error_rate;

Enrich Policies

Enrich policies allow you to add data from existing indices to incoming documents during ingest.
This is useful for:

Adding user profile information to events
Enriching logs with geo-location data
Adding product details to order documents

⚠️ Requirements

Enrich policies require Elasticsearch 7.5+
The source index must exist before creating the policy
The policy must be executed before it can be used in a pipeline

CREATE ENRICH POLICY

Syntax

CREATE [OR REPLACE] ENRICH POLICY policy_name
  [TYPE { MATCH | GEO_MATCH | RANGE }]
  FROM source_index [, source_index2, ...]
  ON match_field
  ENRICH field1, field2, ...
  [WHERE criteria]

Component	Required	Description
`policy_name`	✔	Unique name for the policy
`TYPE`	✖	Policy type (default: `MATCH`)
`FROM`	✔	Source index(es) containing enrichment data
`ON`	✔	Field used to match documents
`ENRICH`	✔	Fields to add from the source index
`WHERE`	✖	Filter criteria for source documents

Policy Types

Type	Description	Use Case
`MATCH`	Exact value matching (default)	User IDs, product codes
`GEO_MATCH`	Geo-shape matching	Location-based enrichment
`RANGE`	Range-based matching	IP ranges, numeric ranges

Examples

Basic match policy (default type)

CREATE ENRICH POLICY user_enrichment
  FROM users
  ON user_id
  ENRICH name, email, department

Generates:

{
  "match": {
    "indices": "users",
    "match_field": "user_id",
    "enrich_fields": ["name", "email", "department"]
  }
}

Match policy with WHERE clause

CREATE OR REPLACE ENRICH POLICY active_user_enrichment
  FROM users
  ON user_id
  ENRICH name, email, department
  WHERE account_status = 'active' AND email_verified = true

Generates:

{
  "match": {
    "indices": "users",
    "match_field": "user_id",
    "enrich_fields": ["name", "email", "department"],
    "query": {
      "bool": {
        "must": [
          {"term": {"account_status": "active"}},
          {"term": {"email_verified": true}}
        ]
      }
    }
  }
}

Policy with multiple source indices

CREATE ENRICH POLICY contact_enrichment
  FROM users, customers, partners
  ON contact_id
  ENRICH name, email, company

Geo-match policy

CREATE ENRICH POLICY geo_enrichment
  TYPE GEO_MATCH
  FROM postal_codes
  ON location
  ENRICH city, region, country, timezone

Generates:

{
  "geo_match": {
    "indices": "postal_codes",
    "match_field": "location",
    "enrich_fields": ["city", "region", "country", "timezone"]
  }
}

Range policy

CREATE ENRICH POLICY ip_enrichment
  TYPE RANGE
  FROM ip_ranges
  ON ip_range
  ENRICH network_name, datacenter, owner

Generates:

{
  "range": {
    "indices": "ip_ranges",
    "match_field": "ip_range",
    "enrich_fields": ["network_name", "datacenter", "owner"]
  }
}

Complex WHERE clause

CREATE ENRICH POLICY premium_users
  FROM users
  ON user_id
  ENRICH name, email, tier, subscription_end_date
  WHERE status = 'active'
    AND tier IN ('premium', 'enterprise')
    AND created_at > '2023-01-01'
    AND (country = 'US' OR country = 'CA')

EXECUTE ENRICH POLICY

After creating an enrich policy, you must execute it to create the enrich index.

EXECUTE ENRICH POLICY policy_name;

Example:

EXECUTE ENRICH POLICY user_enrichment;

⚠️ Note: The policy must be re-executed whenever the source index data changes.

DROP ENRICH POLICY

DROP ENRICH POLICY [IF EXISTS] policy_name;

Deletes the enrich policy and its associated enrich index.

Example:

DROP ENRICH POLICY IF EXISTS user_enrichment;

Using Enrich Policies in Pipelines

Once an enrich policy is created and executed, use it in an ingest pipeline with the ENRICH processor.

Syntax

CREATE PIPELINE my_pipeline
WITH PROCESSORS (
    ENRICH (
        policy_name = "user_enrichment",
        field = "user_id",
        target_field = "user_info",
        max_matches = 1,
        ignore_missing = true
    )
)

Option	Required	Description
`policy_name`	✔	Name of the enrich policy
`field`	✔	Field in the incoming document to match
`target_field`	✔	Field to store enrichment data
`max_matches`	✖	Maximum matching documents (default: 1)
`ignore_missing`	✖	Ignore if match field is missing (default: false)
`override`	✖	Override existing target field (default: true)
`shape_relation`	✖	For geo_match: `INTERSECTS`, `DISJOINT`, `WITHIN`, `CONTAINS`

Complete Example

1. Create source index

CREATE TABLE users (
  user_id KEYWORD,
  name VARCHAR,
  email VARCHAR,
  department KEYWORD,
  PRIMARY KEY (user_id)
);

INSERT INTO users VALUES 
  ('U001', 'Alice Smith', 'alice@example.com', 'Engineering'),
  ('U002', 'Bob Jones', 'bob@example.com', 'Marketing');

2. Create enrich policy

CREATE ENRICH POLICY user_enrichment
  FROM users
  ON user_id
  ENRICH name, email, department;

3. Execute the policy

EXECUTE ENRICH POLICY user_enrichment;

4. Create pipeline with enrich processor

CREATE PIPELINE events_enriched
WITH PROCESSORS (
    ENRICH (
        policy_name = "user_enrichment",
        field = "user_id",
        target_field = "user",
        max_matches = 1,
        ignore_missing = true
    )
);

5. Create table with pipeline

CREATE TABLE events (
  id INT,
  user_id KEYWORD,
  event_type KEYWORD,
  timestamp TIMESTAMP,
  PRIMARY KEY (id)
) OPTIONS (default_pipeline = 'events_enriched');

6. Insert data

INSERT INTO events VALUES (1, 'U001', 'login', '2025-02-05T10:00:00Z');

Result

The document is enriched with user data:

{
  "id": 1,
  "user_id": "U001",
  "event_type": "login",
  "timestamp": "2025-02-05T10:00:00Z",
  "user": {
    "user_id": "U001",
    "name": "Alice Smith",
    "email": "alice@example.com",
    "department": "Engineering"
  }
}

Index Migration Workflow

Initial Creation

CREATE TABLE users (...);

Creates:

index or template
default pipeline
mapping + settings
metadata (PK, defaults, scripts)

Schema Evolution

Add a column

ALTER TABLE users ADD COLUMN last_login TIMESTAMP;

Modify a column

ALTER TABLE users ALTER COLUMN name SET OPTIONS (analyzer = 'french');

Add a STRUCT field

ALTER TABLE users ALTER COLUMN profile ADD FIELD followers INT;

Drop a column

ALTER TABLE users DROP COLUMN old_field;

Migration Safety

The Gateway ensures:

non-destructive updates
mapping compatibility checks
pipeline regeneration when needed
template updates for partitioned tables
index updates for non-partitioned tables

Full Replacement (CTAS)

CREATE OR REPLACE TABLE users AS
SELECT id, name FROM old_users;

Steps:

infer schema
create new index/template
bulk-copy data
atomically replace

Version Compatibility

Feature	ES6	ES7	ES8	ES9
Legacy templates	✔	✔	✖	✖
Composable templates	✖	✔	✔	✔
date_index_name	✔	✔	✔	✔
Generated scripts	✔	✔	✔	✔
STRUCT	✔	✔	✔	✔
ARRAY<STRUCT>	✔	✔	✔	✔
Watchers	✔	✔	✔	✔
Enrich Policies	✖	✔*	✔	✔
Materialized Views	✖	✔*	✔	✔

* Enrich policies and materialized views require ES 7.5+

See Materialized Views for full documentation on materialized views.

Quick Reference

Watcher Syntax Summary

CREATE [OR REPLACE] WATCHER name AS
  -- Trigger (required)
  EVERY n {MILLISECONDS|SECONDS|MINUTES|HOURS|DAYS|WEEKS|MONTHS|YEARS}
  | AT SCHEDULE 'cron'
  
  -- Input (required)
  WITH NO INPUT
  | WITH INPUT (key = value, ...)
  | FROM index [WHERE criteria] [WITHIN n unit]
  | WITH INPUT method "url" [HEADERS (...)] [BODY "..."] [TIMEOUT (...)]
  | WITH INPUT method PROTOCOL scheme HOST "host" [PORT n] [PATH "path"] [PARAMS (...)] [HEADERS (...)] [BODY "..."] [TIMEOUT (...)]
  | WITH INPUTS name [AS] input, name [AS] input, ...
  
  -- Condition (required)
  NEVER DO | ALWAYS DO
  | WHEN path op value DO
  | WHEN SCRIPT '...' USING LANG '...' WITH PARAMS (...) RETURNS TRUE DO
  
  -- Actions (at least one, AS is optional)
  name [AS] LOG "message" [AT level] [FOREACH "path"] [LIMIT n]
  | name [AS] WEBHOOK method "url" [HEADERS (...)] [BODY "..."] [TIMEOUT (...)] [FOREACH "path"] [LIMIT n]
  | name [AS] WEBHOOK method PROTOCOL scheme HOST "host" [PORT n] [PATH "path"] [PARAMS (...)] [HEADERS (...)] [BODY "..."] [TIMEOUT (...)] [FOREACH "path"] [LIMIT n]
  [, ...]
END

Supported HTTP methods: HEAD, GET, POST, PUT, DELETE

Enrich Policy Syntax Summary

-- Create policy
CREATE [OR REPLACE] ENRICH POLICY name
  [TYPE {MATCH|GEO_MATCH|RANGE}]
  FROM source_index [, source_index2, ...]
  ON match_field
  ENRICH field1, field2, ...
  [WHERE criteria]

-- Execute policy (required before use)
EXECUTE ENRICH POLICY name;

-- Drop policy
DROP ENRICH POLICY [IF EXISTS] name;

-- Show policy
SHOW ENRICH POLICY name;

Back to index

FilesExpand file tree

ddl_statements.md

Latest commit

History

ddl_statements.md

File metadata and controls

📘 DDL Statements — SQL Gateway for Elasticsearch

Introduction

Table of Contents

Table Model

Index-backed table (no partitioning)

Template-backed table (with partitioning)

Column Types & Mapping

🧩 Nested and Structured Data

FIELDS for Multi-fields

FIELDS for STRUCT or NESTED OBJECTS

FIELDS for ARRAY

Notes

Constraints & Column Options

Primary Key

🔑 Composite Primary Keys

Syntax

Elasticsearch Translation

Notes

Partitioning

Supported Granularities

CREATE TABLE

Basic Example

Partitioned Example

Table Options (index settings)

CREATE TABLE AS SELECT

Computed columns: SCRIPT AS and STORED

Conversions inside a computed column

ALTER TABLE

Table-level clauses take no parentheses

SET SCHEMA CACHE TTL

Type Changes and Safety

DROP TABLE

TRUNCATE TABLE

CREATE PIPELINE

DROP PIPELINE

ALTER PIPELINE

Watchers

⚠️ Limitations

CREATE WATCHER

Syntax

Triggers

Inputs

No Input

Simple Input

Search Input

HTTP Input

Option 1: Full URL String

Option 2: Decomposed URL Components

Chain Input

Conditions

Always / Never Conditions

Compare Condition

Script Condition

Actions

Logging Action

Webhook Action

Option 1: Full URL String

Option 2: Decomposed URL Components

Complete Examples

Monitor with search input and logging

Webhook with simple input and script condition

Daily report with cron trigger

Chain input with multiple actions

DROP WATCHER

Enrich Policies

⚠️ Requirements

CREATE ENRICH POLICY

Syntax

Policy Types

Examples

Basic match policy (default type)

Match policy with WHERE clause

Policy with multiple source indices

Geo-match policy

Computed columns: `SCRIPT AS` and `STORED`