The Only Full SQL for Elasticsearch

CREATE TABLE users (
  id KEYWORD,
  name TEXT,
  email KEYWORD,
  age INTEGER,
  PRIMARY KEY (id)
);

ALTER TABLE users
  ADD COLUMN status KEYWORD;

INSERT INTO users (id, name, email, age)
VALUES ('1', 'Alice', 'alice@ex.com', 30);

UPDATE users
SET status = 'active'
WHERE age > 25;

-- Load from S3
COPY INTO users
FROM 's3://bucket/users.parquet'
FORMAT PARQUET;

-- Load from local JSON
COPY INTO logs
FROM '/data/logs.json'
FORMAT JSON;

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

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;

softclient4es> SHOW TABLES;
+-----------+
| table     |
+-----------+
| users     |
| orders    |
| products  |
+-----------+
3 rows in set