DDL — Schema Management

The SQL Gateway provides a full Data Definition Language (DDL) layer on top of Elasticsearch, allowing you to define tables, schemas, pipelines, watchers, and enrich policies using relational syntax.

Table Model

A SQL table maps to Elasticsearch structures:

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

Column Types

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
KEYWORD	keyword
DATE	date
TIMESTAMP	date
STRUCT	object with nested properties
ARRAY<STRUCT>	nested
GEO_POINT	geo_point

CREATE TABLE

Basic Example

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

With STRUCT and Multi-fields

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(profile.join_date, CURRENT_DATE, DAY))
  ),
  content VARCHAR FIELDS (
    keyword VARCHAR OPTIONS (analyzer = 'keyword'),
    english VARCHAR OPTIONS (analyzer = 'english')
  )
);

With ARRAY<STRUCT> (Nested)

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

FIELDS Behavior

• On VARCHAR — defines multi-fields (different analyzers)
• On STRUCT — defines object fields
• On ARRAY<STRUCT> — defines nested fields
• Sub-fields support: nested FIELDS, DEFAULT, NOT NULL, COMMENT, OPTIONS, SCRIPT AS
• A column may declare its sub-fields and a computed value together, sub-fields first, as SHOW TABLE renders it (since 0.24.0): c VARCHAR FIELDS (raw KEYWORD) SCRIPT AS (UPPER(k)). The opposite order, SCRIPT AS (…) FIELDS (…), is a syntax error.

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 is accepted — for example default_pipeline, to route inserts through a custom ingest pipeline instead of the generated <table>_ddl_default_pipeline:

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; on a CREATE TABLE it is a parse error, because the whole statement must parse. Use single quotes for values — double quotes denote a quoted identifier, not a string.

Constraints

Primary Key

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

Used for document ID generation, upsert semantics, and COPY INTO conflict resolution.

Composite Primary Keys

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

Generates _id = "{{id}}||{{birthdate}}" via an ingest pipeline processor.

Partitioning

Partitioning routes documents to time-based indices using date_index_name.

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);

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 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 operations:

-- Column operations
ALTER TABLE users ADD COLUMN [IF NOT EXISTS] last_login TIMESTAMP;
ALTER TABLE users DROP COLUMN [IF EXISTS] old_field;
ALTER TABLE users RENAME COLUMN old_name TO new_name;
ALTER TABLE users ALTER COLUMN name SET OPTIONS (analyzer = 'french');
ALTER TABLE users ALTER COLUMN age SET SCRIPT AS (YEAR(CURRENT_DATE) - YEAR(birthdate));
ALTER TABLE users ALTER COLUMN age DROP SCRIPT;
ALTER TABLE users ALTER COLUMN name SET DEFAULT 'unknown';
ALTER TABLE users ALTER COLUMN name DROP DEFAULT;
ALTER TABLE users ALTER COLUMN name SET NOT NULL;
ALTER TABLE users ALTER COLUMN name DROP NOT NULL;
ALTER TABLE users ALTER COLUMN name SET DATA TYPE KEYWORD;
ALTER TABLE users ALTER COLUMN name SET COMMENT 'Full name';
ALTER TABLE users ALTER COLUMN name DROP COMMENT;
ALTER TABLE users ALTER COLUMN profile ADD FIELD followers INT;
ALTER TABLE users ALTER COLUMN profile DROP FIELD old_field;

-- Index-level operations
ALTER TABLE users SET MAPPING dynamic = false;
ALTER TABLE users DROP MAPPING dynamic;
ALTER TABLE users SET SETTING number_of_replicas = 2;
ALTER TABLE users DROP SETTING number_of_replicas;
ALTER TABLE users SET SCHEMA CACHE TTL = '1h';
ALTER TABLE users 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.

Schema cache TTL

A client caches a table’s schema so it does not read the mapping on every statement. How long it may do so is elastic.schema-cache.ttl (5 minutes by default), and a table can override it for itself — the volatility of a mapping is a property of the index, so the value lives with the index:

ALTER TABLE orders SET SCHEMA CACHE TTL = '1h';       -- a mapping that never moves
ALTER TABLE feature_flags SET SCHEMA CACHE TTL = '30s';
ALTER TABLE orders DROP SCHEMA CACHE TTL;             -- back to the client default

The = is optional. 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.

The value is stored in the index’s own mapping metadata, so every client against that cluster picks it up, and these three write exactly the same thing:

ALTER TABLE orders SET SCHEMA CACHE TTL = '1h';
ALTER TABLE orders SET MAPPING _meta.schema_cache_ttl = '1h';
CREATE TABLE orders (id INT NOT NULL) OPTIONS (mappings = (_meta = (schema_cache_ttl = '1h')));

Precedence is index metadata > elastic.schema-cache.ttl > the built-in 5 minutes, and the primary shard-count cache used by paged extraction follows the same clock.

The schema reaches every executed statement, so a stale entry is not merely a stale column list — it is a script compiled for the previous mapping. Shorten the TTL 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, so it takes effect within one old period.

Type Change Safety

• Convertible types — allowed but requires automatic reindex
• Incompatible types — rejected with error

DROP TABLE

DROP TABLE IF EXISTS users;

TRUNCATE TABLE

TRUNCATE TABLE users;

Deletes all documents while keeping mapping, settings, pipeline, and template.

---

Pipelines

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 (...)",
        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
    )
);

ALTER PIPELINE

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

DROP PIPELINE

DROP PIPELINE IF EXISTS user_pipeline;

---

Watchers

Watchers provide scheduled monitoring and alerting. A watcher consists of a trigger, input, condition, and actions.

Supported Actions

Only logging and webhook actions are currently supported. Email, Slack, PagerDuty, Jira, and Index actions are not available.

CREATE WATCHER

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

Triggers

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

Inputs

Input Type	Syntax
No input	WITH NO INPUT
Simple	WITH INPUT (key = value, ...)
Search	FROM index1 [, index2, ...] [WHERE criteria] [WITHIN n unit]
HTTP	WITH INPUT GET "url" [HEADERS (...)] [BODY "..."] [TIMEOUT (...)]
Chain	WITH INPUTS name1 input1, name2 input2, ...

Search input restrictions

A search input maps to a single Elasticsearch search request over a list of indices — there is no join engine behind it, so both of these are rejected at parse time:

• JOIN in the FROM (including JOIN UNNEST(...)). Pre-join the sources with a materialized view and have the watcher search the view.
• Table-qualified columns over a multi-index FROM — one search applies one query to every index it names, so it can neither join them (FROM orders o, customers c WHERE o.id = c.order_id) nor scope a predicate to one of them (FROM orders, refunds WHERE orders.status = 'FAILED'). FROM logs-2025, logs-2024 WHERE level = 'ERROR' is fine: no qualifier.

A table alias is allowed and is resolved in the WHERE: FROM orders o WHERE o.status = 'FAILED' searches index orders on the field status.

Conditions

Condition	Syntax
Always	ALWAYS DO
Never	NEVER DO
Compare	WHEN path operator value DO
Script	WHEN SCRIPT '...' USING LANG '...' WITH PARAMS (...) RETURNS TRUE DO

Actions

Logging:

action_name LOG "message" AT ERROR FOREACH "ctx.payload.hits.hits" LIMIT 500

Webhook:

action_name WEBHOOK POST "https://hooks.example.com/webhook"
  HEADERS ("Content-Type" = "application/json")
  BODY "{\"text\": \"Alert triggered\"}"

Complete Example

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}}}"
END

DROP WATCHER

DROP WATCHER IF EXISTS high_error_rate;

---

Enrich Policies

Enrich policies add data from existing indices to incoming documents during ingest.

Requirements

Enrich policies require Elasticsearch 7.5+. The source index must exist before creating the policy, and the policy must be executed before use.

CREATE ENRICH POLICY

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

Policy Types

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

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;

EXECUTE ENRICH POLICY

EXECUTE ENRICH POLICY user_enrichment;

Caution

The policy must be re-executed whenever the source index data changes.

Using in Pipelines

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

DROP ENRICH POLICY

DROP ENRICH POLICY IF EXISTS user_enrichment;

---

Version Compatibility

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

* Requires ES 7.5+