Elasticsearch SQL 包括的技術ガイド

目次

1. はじめに
2. アーキテクチャ -- SQL から Elasticsearch クエリへの変換
3. アクセス方法
4. SQL 構文の詳細
5. 全文検索との統合
6. SQL 関数リファレンス
7. HISTOGRAM による日付集約
8. _sql/translate API
9. レスポンスフォーマット
10. 実践的なユースケース
11. 制限事項
12. パフォーマンスとベストプラクティス
13. まとめ -- 他のクエリ言語との比較

---

1. はじめに

1.1 Elasticsearch SQL とは

Elasticsearch SQL は、Elasticsearch に対して標準的な SQL 構文でクエリを実行するための機能である。Elasticsearch 6.3 で導入され、X-Pack（現在は Basic ライセンス）の一部として提供されている。リレーショナルデータベースに慣れ親しんだ開発者やアナリストが、Query DSL を習得することなく Elasticsearch のデータにアクセスできるようにすることを目的としている。

1.2 Elasticsearch のクエリ言語体系

Elasticsearch には現在、以下の主要なクエリ言語が存在する。

クエリ言語	導入バージョン	主な用途	対象ユーザー
Query DSL	初期	汎用検索・集約	開発者
Elasticsearch SQL	6.3	SQL ベースの分析クエリ	SQL 経験者・アナリスト
EQL (Event Query Language)	7.9	セキュリティイベント分析	セキュリティアナリスト
**ES	QL**	8.11	パイプベースの分析クエリ

Elasticsearch SQL は、SQL の知識を活かしてデータ探索や集約分析を行うための中間層として位置づけられている。内部的にはすべて Query DSL に変換されて実行されるため、パフォーマンス特性は Query DSL と本質的に同一である。

1.3 なぜ Elasticsearch SQL を使うのか

• 学習コストの低減: SQL は最も広く知られたクエリ言語であり、新規メンバーのオンボーディングが容易になる
• BI ツールとの統合: JDBC/ODBC ドライバを通じて Tableau や Power BI などのツールと連携可能
• アドホック分析: 複雑な Query DSL を記述せずに迅速にデータ分析を実行可能
• 段階的な移行: 既存の SQL スキルを活用しながら Elasticsearch の機能を学ぶことができる

---

2. アーキテクチャ -- SQL から Elasticsearch クエリへの変換

2.1 変換パイプライン

Elasticsearch SQL の内部処理は以下の段階で実行される。

SQL クエリ
    |
    v
[1. パーサー (SQL Parser)]
    |  SQL 文を抽象構文木 (AST) に変換
    v
[2. アナライザー (Analyzer)]
    |  スキーマ検証、型チェック
    v
[3. オプティマイザー (Optimizer)]
    |  クエリの最適化（定数畳み込み、述語プッシュダウン等）
    v
[4. プランナー (Planner)]
    |  論理プランから物理プランへの変換
    v
[5. 実行エンジン (Execution Engine)]
    |  Query DSL の生成と実行
    v
Elasticsearch クエリ結果

2.2 SQL と Query DSL の対応関係

以下に SQL 句と対応する Query DSL の構造を示す。

SQL 句	Query DSL 要素
SELECT	_source, fields, docvalue_fields
WHERE	query (bool, term, range など)
GROUP BY	aggs (terms, date_histogram など)
HAVING	aggs 内の bucket_selector
ORDER BY	sort
LIMIT	size

2.3 変換の具体例

以下の SQL クエリが内部でどのように変換されるかを確認する。

SQL クエリ:

SELECT department, COUNT(*) AS cnt, AVG(salary) AS avg_salary
FROM employees
WHERE status = 'active' AND hire_date >= '2024-01-01'
GROUP BY department
HAVING COUNT(*) > 5
ORDER BY avg_salary DESC
LIMIT 10

変換後の Query DSL:

{
  "size": 0,
  "query": {
    "bool": {
      "filter": [
        {
          "term": {
            "status": "active"
          }
        },
        {
          "range": {
            "hire_date": {
              "gte": "2024-01-01",
              "format": "yyyy-MM-dd"
            }
          }
        }
      ]
    }
  },
  "aggs": {
    "groupby": {
      "composite": {
        "size": 10,
        "sources": [
          {
            "department": {
              "terms": {
                "field": "department.keyword"
              }
            }
          }
        ]
      },
      "aggs": {
        "cnt": {
          "value_count": {
            "field": "_index"
          }
        },
        "avg_salary": {
          "avg": {
            "field": "salary"
          }
        },
        "having": {
          "bucket_selector": {
            "buckets_path": {
              "cnt_val": "cnt"
            },
            "script": {
              "source": "params.cnt_val > 5"
            }
          }
        }
      }
    }
  }
}

---

3. アクセス方法

3.1 REST API

最も基本的なアクセス方法であり、_sql エンドポイントを使用する。

基本リクエスト:

POST /_sql?format=txt
{
  "query": "SELECT * FROM employees WHERE department = 'Engineering' LIMIT 5"
}

パラメータ付きリクエスト:

POST /_sql?format=json
{
  "query": "SELECT * FROM employees WHERE department = ? AND salary > ?",
  "params": ["Engineering", 80000],
  "fetch_size": 100,
  "time_zone": "Asia/Tokyo",
  "field_multi_value_leniency": true
}

利用可能なリクエストパラメータ:

パラメータ	説明	デフォルト値
query	実行する SQL 文	(必須)
params	プリペアドステートメントのパラメータ	[]
fetch_size	1回のレスポンスで返す行数	1000
time_zone	タイムゾーン	UTC
field_multi_value_leniency	複数値フィールドの寛容モード	false
request_timeout	タイムアウト	90s
page_timeout	ページネーションのタイムアウト	45s
columnar	カラムナーレスポンス形式	false

3.2 SQL CLI

Elasticsearch に付属するコマンドラインインターフェースを使用する。

# CLI の起動
./bin/elasticsearch-sql-cli https://localhost:9200

# 認証付き
./bin/elasticsearch-sql-cli https://elastic:password@localhost:9200

# SSL/TLS 証明書指定
./bin/elasticsearch-sql-cli --keystore_location /path/to/keystore.p12 \
  https://localhost:9200

CLI 内での操作例:

sql> SELECT department, COUNT(*) FROM employees GROUP BY department;

  department   | COUNT(*)
---------------+----------
Engineering    | 142
Marketing      | 87
Sales          | 65
HR             | 32

sql> DESCRIBE employees;

     column      |    type    | mapping
-----------------+-----------+---------
department       | VARCHAR   | keyword
employee_id      | INTEGER   | integer
hire_date        | TIMESTAMP | date
name             | VARCHAR   | text
salary           | DOUBLE    | double
status           | VARCHAR   | keyword

3.3 JDBC ドライバ

Java アプリケーションや JDBC 対応ツールから接続する場合に使用する。

Maven 依存関係:

<dependency>
  <groupId>org.elasticsearch.plugin</groupId>
  <artifactId>x-pack-sql-jdbc</artifactId>
  <version>8.13.0</version>
</dependency>

Java コード例:

import java.sql.*;
import java.util.Properties;

public class ElasticsearchSQLExample {
    public static void main(String[] args) throws Exception {
        String url = "jdbc:es://https://localhost:9200";
        
        Properties props = new Properties();
        props.put("user", "elastic");
        props.put("password", "changeme");
        props.put("ssl", "true");
        props.put("ssl.truststore.location", "/path/to/truststore.p12");
        props.put("ssl.truststore.pass", "truststore_password");
        
        try (Connection conn = DriverManager.getConnection(url, props)) {
            // プリペアドステートメント
            String sql = "SELECT department, COUNT(*) AS cnt, AVG(salary) AS avg_sal " +
                         "FROM employees WHERE status = ? GROUP BY department";
            
            try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
                pstmt.setString(1, "active");
                
                try (ResultSet rs = pstmt.executeQuery()) {
                    ResultSetMetaData meta = rs.getMetaData();
                    int colCount = meta.getColumnCount();
                    
                    // カラム名の出力
                    for (int i = 1; i <= colCount; i++) {
                        System.out.printf("%-20s", meta.getColumnName(i));
                    }
                    System.out.println();
                    
                    // データの出力
                    while (rs.next()) {
                        for (int i = 1; i <= colCount; i++) {
                            System.out.printf("%-20s", rs.getObject(i));
                        }
                        System.out.println();
                    }
                }
            }
        }
    }
}

JDBC URL パラメータ:

パラメータ	説明
timezone	タイムゾーン設定
connect.timeout	接続タイムアウト(ms)
network.timeout	ネットワークタイムアウト(ms)
page.timeout	ページネーションタイムアウト
page.size	フェッチサイズ
query.timeout	クエリタイムアウト
ssl	SSL 有効化
ssl.truststore.location	トラストストアのパス

3.4 ODBC ドライバ

Windows 環境において、ODBC 対応アプリケーション（Excel、Power BI Desktop など）から接続する場合に使用する。

# DSN 設定例 (odbc.ini)
[ElasticsearchDSN]
Driver      = Elasticsearch ODBC
Description = Elasticsearch SQL ODBC
Host        = localhost
Port        = 9200
Secure      = 1
User        = elastic
Password    = changeme

---

4. SQL 構文の詳細

4.1 SELECT

基本的な SELECT 文とそのバリエーションを示す。

-- 全カラム取得
SELECT * FROM employees LIMIT 10;

-- 特定カラムの選択
SELECT employee_id, name, department, salary FROM employees;

-- エイリアス
SELECT 
  employee_id AS id,
  name AS employee_name,
  salary * 12 AS annual_salary
FROM employees;

-- DISTINCT
SELECT DISTINCT department FROM employees;

-- スカラー関数の使用
SELECT 
  name,
  UPPER(department) AS dept_upper,
  YEAR(hire_date) AS hire_year,
  ROUND(salary, -3) AS salary_rounded
FROM employees;

4.2 WHERE

WHERE 句では様々な条件式を使用できる。

-- 比較演算子
SELECT * FROM employees WHERE salary > 80000;
SELECT * FROM employees WHERE department = 'Engineering';
SELECT * FROM employees WHERE hire_date >= '2024-01-01';

-- 論理演算子
SELECT * FROM employees 
WHERE department = 'Engineering' 
  AND salary > 80000 
  AND status = 'active';

SELECT * FROM employees 
WHERE department = 'Engineering' 
  OR department = 'Marketing';

-- IN
SELECT * FROM employees 
WHERE department IN ('Engineering', 'Marketing', 'Sales');

-- BETWEEN
SELECT * FROM employees 
WHERE salary BETWEEN 60000 AND 100000;

SELECT * FROM employees 
WHERE hire_date BETWEEN '2024-01-01' AND '2024-12-31';

-- LIKE (パターンマッチング)
SELECT * FROM employees WHERE name LIKE 'John%';
SELECT * FROM employees WHERE name LIKE '%smith';
SELECT * FROM employees WHERE name LIKE '_ohn%';

-- IS NULL / IS NOT NULL
SELECT * FROM employees WHERE manager_id IS NULL;
SELECT * FROM employees WHERE department IS NOT NULL;

-- NOT
SELECT * FROM employees 
WHERE NOT department = 'Engineering';

4.3 GROUP BY

集約クエリには GROUP BY を使用する。

-- 基本的な集約
SELECT department, COUNT(*) AS employee_count
FROM employees
GROUP BY department;

-- 複数の集約関数
SELECT 
  department,
  COUNT(*) AS cnt,
  MIN(salary) AS min_salary,
  MAX(salary) AS max_salary,
  AVG(salary) AS avg_salary,
  SUM(salary) AS total_salary
FROM employees
GROUP BY department;

-- 複数カラムによるグループ化
SELECT 
  department,
  status,
  COUNT(*) AS cnt,
  AVG(salary) AS avg_salary
FROM employees
GROUP BY department, status;

-- 式によるグループ化
SELECT 
  YEAR(hire_date) AS hire_year,
  COUNT(*) AS cnt
FROM employees
GROUP BY YEAR(hire_date)
ORDER BY hire_year;

4.4 HAVING

GROUP BY の結果をフィルタリングする場合に HAVING を使用する。

-- 基本的な HAVING
SELECT department, COUNT(*) AS cnt
FROM employees
GROUP BY department
HAVING COUNT(*) > 10;

-- 複数条件の HAVING
SELECT 
  department,
  COUNT(*) AS cnt,
  AVG(salary) AS avg_salary
FROM employees
GROUP BY department
HAVING COUNT(*) > 5 AND AVG(salary) > 70000;

-- WHERE と HAVING の組み合わせ
SELECT 
  department,
  COUNT(*) AS cnt,
  AVG(salary) AS avg_salary
FROM employees
WHERE status = 'active'
GROUP BY department
HAVING COUNT(*) >= 3
ORDER BY avg_salary DESC;

4.5 ORDER BY

-- 昇順（デフォルト）
SELECT * FROM employees ORDER BY salary ASC;

-- 降順
SELECT * FROM employees ORDER BY salary DESC;

-- 複数カラムによるソート
SELECT * FROM employees 
ORDER BY department ASC, salary DESC;

-- NULL の扱い
SELECT * FROM employees 
ORDER BY manager_id ASC NULLS LAST;

SELECT * FROM employees 
ORDER BY manager_id DESC NULLS FIRST;

4.6 LIMIT

-- 上位 N 件の取得
SELECT * FROM employees ORDER BY salary DESC LIMIT 10;

-- 集約結果の制限
SELECT department, AVG(salary) AS avg_salary
FROM employees
GROUP BY department
ORDER BY avg_salary DESC
LIMIT 5;

4.7 SHOW と DESCRIBE

-- テーブル（インデックス）一覧
SHOW TABLES;

-- パターンマッチによるテーブル一覧
SHOW TABLES LIKE 'log%';
SHOW TABLES 'employee*';

-- テーブル構造の確認
DESCRIBE employees;
DESC employees;

-- SQL 関数一覧
SHOW FUNCTIONS;

-- パターンによる関数検索
SHOW FUNCTIONS LIKE 'AVG';
SHOW FUNCTIONS LIKE '%DATE%';

---

5. 全文検索との統合

Elasticsearch SQL の大きな特徴の一つは、標準 SQL では利用できない全文検索機能を SQL 構文内から利用できることである。

5.1 MATCH 関数

MATCH 関数は Elasticsearch の match クエリに対応し、全文検索を実行する。

-- 基本的な MATCH
SELECT employee_id, name, department
FROM employees
WHERE MATCH(name, 'John Smith');

-- 複数フィールドに対する MATCH
SELECT title, description, category
FROM articles
WHERE MATCH('title,description', 'elasticsearch performance tuning');

-- フィールドブースト
SELECT title, description
FROM articles
WHERE MATCH('title^3,description', 'elasticsearch sql');

-- analyzer 指定
SELECT title, content
FROM articles
WHERE MATCH(title, 'データベース', 'analyzer=kuromoji');

-- MATCH と他の条件の組み合わせ
SELECT title, author, published_date, SCORE()
FROM articles
WHERE MATCH(title, 'elasticsearch') 
  AND published_date >= '2024-01-01'
  AND category = 'technology'
ORDER BY SCORE() DESC
LIMIT 20;

5.2 QUERY 関数

QUERY 関数は Elasticsearch の query_string クエリに対応し、Lucene クエリ構文を使用できる。

-- 基本的な QUERY
SELECT * FROM articles 
WHERE QUERY('title:elasticsearch AND content:performance');

-- ワイルドカード
SELECT * FROM articles 
WHERE QUERY('title:elast*');

-- ファジー検索
SELECT * FROM articles 
WHERE QUERY('title:elastisearch~2');

-- フレーズ検索
SELECT * FROM articles 
WHERE QUERY('title:"elasticsearch sql"');

-- 範囲検索との組み合わせ
SELECT * FROM articles 
WHERE QUERY('title:elasticsearch AND date:[2024-01-01 TO 2024-12-31]');

-- ブール演算
SELECT * FROM articles 
WHERE QUERY('(title:elasticsearch OR title:opensearch) AND NOT status:draft');

5.3 SCORE 関数

SCORE() 関数は、全文検索の関連性スコア（_score）を取得する。

-- スコア付きの全文検索
SELECT 
  title,
  author,
  SCORE() AS relevance_score
FROM articles
WHERE MATCH(title, 'elasticsearch sql tutorial')
ORDER BY SCORE() DESC;

-- スコア閾値によるフィルタリング
SELECT 
  title,
  SCORE() AS score
FROM articles
WHERE MATCH(title, 'elasticsearch') AND SCORE() > 2.0
ORDER BY SCORE() DESC;

-- スコアを使った複合ソート
SELECT 
  title,
  published_date,
  SCORE() AS score
FROM articles
WHERE MATCH(title, 'elasticsearch')
ORDER BY SCORE() DESC, published_date DESC
LIMIT 50;

---

6. SQL 関数リファレンス

6.1 集約関数

関数	説明	使用例
AVG(expr)	平均値	AVG(salary)
COUNT(expr)	件数	COUNT(*), COUNT(DISTINCT dept)
SUM(expr)	合計	SUM(salary)
MIN(expr)	最小値	MIN(hire_date)
MAX(expr)	最大値	MAX(salary)
PERCENTILE(expr, pct)	パーセンタイル	PERCENTILE(salary, 95)
PERCENTILE_RANK(expr, val)	パーセンタイルランク	PERCENTILE_RANK(salary, 80000)
STDDEV_POP(expr)	母標準偏差	STDDEV_POP(salary)
STDDEV_SAMP(expr)	標本標準偏差	STDDEV_SAMP(salary)
VAR_POP(expr)	母分散	VAR_POP(salary)
VAR_SAMP(expr)	標本分散	VAR_SAMP(salary)
KURTOSIS(expr)	尖度	KURTOSIS(salary)
SKEWNESS(expr)	歪度	SKEWNESS(salary)
SUM_OF_SQUARES(expr)	平方和	SUM_OF_SQUARES(salary)
MAD(expr)	中央絶対偏差	MAD(salary)

-- 統計サマリーの例
SELECT 
  department,
  COUNT(*) AS cnt,
  ROUND(AVG(salary), 2) AS avg_salary,
  MIN(salary) AS min_salary,
  MAX(salary) AS max_salary,
  ROUND(STDDEV_POP(salary), 2) AS stddev_salary,
  ROUND(PERCENTILE(salary, 50), 2) AS median_salary,
  ROUND(PERCENTILE(salary, 90), 2) AS p90_salary
FROM employees
WHERE status = 'active'
GROUP BY department
ORDER BY avg_salary DESC;

6.2 文字列関数

関数	説明	使用例
ASCII(str)	ASCII コード値	ASCII('A') => 65
BIT_LENGTH(str)	ビット長	BIT_LENGTH('hello') => 40
CHAR(n)	ASCII コードから文字	CHAR(65) => 'A'
CHAR_LENGTH(str)	文字長	CHAR_LENGTH('hello') => 5
CONCAT(str1, str2)	文字列結合	CONCAT('a', 'b') => 'ab'
INSERT(str, pos, len, newstr)	文字列挿入	INSERT('abc', 2, 1, 'XY')
LCASE(str) / LOWER(str)	小文字変換	LOWER('ABC') => 'abc'
UCASE(str) / UPPER(str)	大文字変換	UPPER('abc') => 'ABC'
LEFT(str, n)	左から n 文字	LEFT('hello', 3) => 'hel'
RIGHT(str, n)	右から n 文字	RIGHT('hello', 3) => 'llo'
LENGTH(str)	バイト長	LENGTH('hello') => 5
LOCATE(substr, str)	部分文字列位置	LOCATE('ll', 'hello') => 3
LTRIM(str)	左トリム	LTRIM(' hello')
RTRIM(str)	右トリム	RTRIM('hello ')
TRIM(str)	両端トリム	TRIM(' hello ')
REPEAT(str, n)	文字列繰り返し	REPEAT('ab', 3) => 'ababab'
REPLACE(str, from, to)	文字列置換	REPLACE('abc', 'b', 'X')
REVERSE(str)	文字列反転	REVERSE('hello') => 'olleh'
SPACE(n)	スペース文字列	SPACE(5) => ' '
SUBSTRING(str, pos, len)	部分文字列	SUBSTRING('hello', 2, 3) => 'ell'
POSITION(substr, str)	位置	POSITION('ll', 'hello') => 3
STARTS_WITH(str, prefix)	プレフィックス判定	STARTS_WITH('hello', 'he')

-- 文字列関数の実用例
SELECT 
  employee_id,
  CONCAT(UPPER(LEFT(name, 1)), LOWER(SUBSTRING(name, 2))) AS formatted_name,
  CHAR_LENGTH(name) AS name_length,
  REPLACE(department, 'Engineering', 'Eng') AS dept_short
FROM employees
WHERE STARTS_WITH(name, 'J')
LIMIT 20;

6.3 日付・時刻関数

関数	説明	使用例
CURRENT_DATE / CURDATE()	現在の日付	CURRENT_DATE
CURRENT_TIME / CURTIME()	現在の時刻	CURRENT_TIME
CURRENT_TIMESTAMP / NOW()	現在のタイムスタンプ	NOW()
YEAR(date)	年	YEAR(hire_date)
MONTH(date)	月	MONTH(hire_date)
DAY(date) / DOM(date)	日	DAY(hire_date)
HOUR(date)	時	HOUR(timestamp_field)
MINUTE(date)	分	MINUTE(timestamp_field)
SECOND(date)	秒	SECOND(timestamp_field)
DAY_OF_WEEK(date) / DOW(date)	曜日(1-7)	DOW(hire_date)
DAY_OF_YEAR(date) / DOY(date)	年内日数	DOY(hire_date)
WEEK(date) / ISO_WEEK(date)	週番号	WEEK(hire_date)
QUARTER(date)	四半期	QUARTER(hire_date)
DAY_NAME(date)	曜日名	DAY_NAME(hire_date)
MONTH_NAME(date)	月名	MONTH_NAME(hire_date)
DATE_ADD(date, interval)	日付加算	DATE_ADD('date', INTERVAL 1 DAY)
DATE_DIFF(unit, d1, d2)	日付差分	DATE_DIFF('day', d1, d2)
DATE_TRUNC(unit, date)	日付切り捨て	DATE_TRUNC('month', hire_date)
DATE_PARSE(str, fmt)	文字列パース	DATE_PARSE('2024-01-15', 'yyyy-MM-dd')
DATE_FORMAT(date, fmt)	フォーマット	DATE_FORMAT(hire_date, 'yyyy/MM')

-- 日付関数の実用例
SELECT 
  employee_id,
  name,
  hire_date,
  YEAR(hire_date) AS hire_year,
  MONTH_NAME(hire_date) AS hire_month,
  DATE_DIFF('day', hire_date, NOW()) AS days_employed,
  DATE_DIFF('year', hire_date, NOW()) AS years_employed,
  DATE_TRUNC('month', hire_date) AS hire_month_start,
  QUARTER(hire_date) AS hire_quarter
FROM employees
WHERE hire_date >= DATE_ADD(NOW(), INTERVAL -5 YEAR)
ORDER BY hire_date DESC
LIMIT 20;

6.4 数学関数

関数	説明	使用例
ABS(n)	絶対値	ABS(-5) => 5
CEIL(n) / CEILING(n)	切り上げ	CEIL(4.3) => 5
FLOOR(n)	切り捨て	FLOOR(4.7) => 4
ROUND(n, d)	四捨五入	ROUND(3.14159, 2) => 3.14
TRUNCATE(n, d)	切り捨て(精度指定)	TRUNCATE(3.14159, 2) => 3.14
POWER(base, exp)	累乗	POWER(2, 10) => 1024
SQRT(n)	平方根	SQRT(144) => 12
CBRT(n)	立方根	CBRT(27) => 3
E()	ネイピア数	E() => 2.718...
PI()	円周率	PI() => 3.14159...
EXP(n)	e の n 乗	EXP(1) => 2.718...
LOG(n)	自然対数	LOG(E()) => 1
LOG10(n)	常用対数	LOG10(100) => 2
MOD(a, b)	剰余	MOD(10, 3) => 1
SIGN(n)	符号	SIGN(-5) => -1
RANDOM(seed)	乱数	RANDOM(42)

-- 数学関数の実用例
SELECT 
  department,
  ROUND(AVG(salary), 2) AS avg_salary,
  ROUND(STDDEV_POP(salary), 2) AS stddev,
  ROUND(AVG(salary) - 2 * STDDEV_POP(salary), 2) AS lower_bound,
  ROUND(AVG(salary) + 2 * STDDEV_POP(salary), 2) AS upper_bound,
  ROUND(LOG10(SUM(salary)), 4) AS log_total
FROM employees
GROUP BY department;

6.5 型変換関数

-- CAST による型変換
SELECT CAST(salary AS VARCHAR) AS salary_text FROM employees;
SELECT CAST('2024-01-15' AS DATE) AS date_val;
SELECT CAST(employee_id AS VARCHAR) AS id_text FROM employees;
SELECT CAST('12345' AS INTEGER) AS num_val;

-- CONVERT
SELECT CONVERT(salary, VARCHAR) AS salary_text FROM employees;

-- 暗黙の型変換の例
SELECT * FROM employees WHERE salary > '80000';  -- 文字列が自動的に数値に変換

6.6 条件式

-- CASE WHEN
SELECT 
  name,
  salary,
  CASE
    WHEN salary >= 120000 THEN 'Executive'
    WHEN salary >= 90000 THEN 'Senior'
    WHEN salary >= 60000 THEN 'Mid-level'
    ELSE 'Junior'
  END AS salary_band
FROM employees;

-- CASE WHEN を集約と組み合わせ
SELECT 
  department,
  COUNT(*) AS total,
  SUM(CASE WHEN salary >= 90000 THEN 1 ELSE 0 END) AS high_earners,
  SUM(CASE WHEN salary < 60000 THEN 1 ELSE 0 END) AS low_earners
FROM employees
GROUP BY department;

-- IIF (Inline If)
SELECT 
  name,
  salary,
  IIF(salary > 80000, 'High', 'Normal') AS salary_level
FROM employees;

-- COALESCE
SELECT 
  name,
  COALESCE(manager_id, 0) AS manager_id,
  COALESCE(department, 'Unassigned') AS department
FROM employees;

-- NULLIF
SELECT 
  name,
  NULLIF(department, 'Unknown') AS department
FROM employees;

-- IFNULL
SELECT 
  name,
  IFNULL(manager_id, -1) AS manager_id
FROM employees;

-- GREATEST / LEAST
SELECT 
  name,
  GREATEST(base_salary, bonus_salary) AS higher_pay,
  LEAST(base_salary, bonus_salary) AS lower_pay
FROM compensation;

---

7. HISTOGRAM による日付集約

7.1 HISTOGRAM 関数の概要

Elasticsearch SQL の HISTOGRAM 関数は、Elasticsearch の date_histogram 集約に対応し、GROUP BY 句で使用することで時系列データの集約を実現する。

-- 月次集約
SELECT 
  HISTOGRAM(order_date, INTERVAL 1 MONTH) AS month,
  COUNT(*) AS order_count,
  SUM(amount) AS total_amount,
  AVG(amount) AS avg_amount
FROM orders
GROUP BY HISTOGRAM(order_date, INTERVAL 1 MONTH)
ORDER BY month;

7.2 様々な間隔での集約

-- 日次集約
SELECT 
  HISTOGRAM(timestamp, INTERVAL 1 DAY) AS day,
  COUNT(*) AS event_count
FROM access_logs
WHERE timestamp >= '2024-01-01' AND timestamp < '2024-02-01'
GROUP BY HISTOGRAM(timestamp, INTERVAL 1 DAY)
ORDER BY day;

-- 時間単位の集約
SELECT 
  HISTOGRAM(timestamp, INTERVAL 1 HOUR) AS hour,
  COUNT(*) AS request_count,
  AVG(response_time) AS avg_response_time
FROM access_logs
WHERE timestamp >= '2024-01-15' AND timestamp < '2024-01-16'
GROUP BY HISTOGRAM(timestamp, INTERVAL 1 HOUR)
ORDER BY hour;

-- 週次集約
SELECT 
  HISTOGRAM(created_at, INTERVAL 1 WEEK) AS week,
  COUNT(*) AS ticket_count,
  SUM(CASE WHEN status = 'resolved' THEN 1 ELSE 0 END) AS resolved_count
FROM support_tickets
WHERE created_at >= '2024-01-01'
GROUP BY HISTOGRAM(created_at, INTERVAL 1 WEEK)
ORDER BY week;

-- 四半期集約
SELECT 
  HISTOGRAM(transaction_date, INTERVAL 3 MONTH) AS quarter,
  COUNT(*) AS transaction_count,
  SUM(amount) AS total_revenue,
  COUNT(DISTINCT customer_id) AS unique_customers
FROM transactions
WHERE transaction_date >= '2023-01-01'
GROUP BY HISTOGRAM(transaction_date, INTERVAL 3 MONTH)
ORDER BY quarter;

7.3 数値の HISTOGRAM

日付以外にも、数値フィールドに対して HISTOGRAM を使用できる。

-- 給与帯別の分布
SELECT 
  HISTOGRAM(salary, 10000) AS salary_band,
  COUNT(*) AS employee_count
FROM employees
GROUP BY HISTOGRAM(salary, 10000)
ORDER BY salary_band;

-- レスポンスタイム分布
SELECT 
  HISTOGRAM(response_time_ms, 100) AS response_bucket,
  COUNT(*) AS request_count,
  AVG(response_time_ms) AS avg_time
FROM access_logs
GROUP BY HISTOGRAM(response_time_ms, 100)
ORDER BY response_bucket;

7.4 HISTOGRAM と他の GROUP BY の組み合わせ

-- 日別・ステータス別のアクセスログ集約
SELECT 
  HISTOGRAM(timestamp, INTERVAL 1 DAY) AS day,
  status_code,
  COUNT(*) AS request_count
FROM access_logs
WHERE timestamp >= '2024-01-01' AND timestamp < '2024-01-08'
GROUP BY HISTOGRAM(timestamp, INTERVAL 1 DAY), status_code
ORDER BY day, status_code;

-- 月別・部門別の売上集約
SELECT 
  HISTOGRAM(order_date, INTERVAL 1 MONTH) AS month,
  department,
  SUM(amount) AS total_sales,
  COUNT(*) AS order_count
FROM orders
WHERE order_date >= '2024-01-01'
GROUP BY HISTOGRAM(order_date, INTERVAL 1 MONTH), department
ORDER BY month, department;

---

8. _sql/translate API

8.1 概要

_sql/translate API は、SQL クエリを対応する Query DSL に変換して表示する機能である。実際にクエリを実行することなく、生成される Query DSL を確認できる。デバッグ、最適化、学習目的で非常に有用である。

8.2 基本的な使い方

POST /_sql/translate
{
  "query": "SELECT department, COUNT(*) AS cnt FROM employees GROUP BY department"
}

レスポンス例:

{
  "size": 0,
  "query": {
    "match_all": {}
  },
  "aggs": {
    "groupby": {
      "composite": {
        "size": 1000,
        "sources": [
          {
            "department": {
              "terms": {
                "field": "department.keyword",
                "missing_bucket": true,
                "order": "asc"
              }
            }
          }
        ]
      },
      "aggs": {
        "cnt": {
          "value_count": {
            "field": "_index"
          }
        }
      }
    }
  }
}

8.3 WHERE 句の変換

POST /_sql/translate
{
  "query": "SELECT * FROM employees WHERE department = 'Engineering' AND salary > 80000 AND status IN ('active', 'probation')"
}

レスポンス:

{
  "size": 1000,
  "query": {
    "bool": {
      "filter": [
        {
          "term": {
            "department.keyword": {
              "value": "Engineering"
            }
          }
        },
        {
          "range": {
            "salary": {
              "gt": 80000
            }
          }
        },
        {
          "terms": {
            "status.keyword": [
              "active",
              "probation"
            ]
          }
        }
      ]
    }
  },
  "_source": {
    "includes": [
      "department",
      "employee_id",
      "hire_date",
      "name",
      "salary",
      "status"
    ]
  },
  "sort": [
    {
      "_doc": {
        "order": "asc"
      }
    }
  ]
}

8.4 全文検索の変換

POST /_sql/translate
{
  "query": "SELECT title, SCORE() FROM articles WHERE MATCH(title, 'elasticsearch performance') ORDER BY SCORE() DESC"
}

レスポンス:

{
  "size": 1000,
  "query": {
    "match": {
      "title": {
        "query": "elasticsearch performance"
      }
    }
  },
  "_source": false,
  "fields": [
    {
      "field": "title"
    }
  ],
  "sort": [
    {
      "_score": {
        "order": "desc"
      }
    }
  ]
}

8.5 translate API の活用方法

# 1. SQL クエリを書く
# 2. translate API で Query DSL を確認
# 3. 必要に応じて Query DSL を直接カスタマイズ
# 4. 最適化されたクエリを本番で使用

# 例: translate の結果を jq で整形
curl -s -X POST "localhost:9200/_sql/translate" \
  -H "Content-Type: application/json" \
  -d '{"query": "SELECT department, COUNT(*) FROM employees GROUP BY department HAVING COUNT(*) > 10"}' \
  | jq .

---

9. レスポンスフォーマット

9.1 利用可能なフォーマット

フォーマット	Content-Type	説明
txt	text/plain	人間が読みやすいテーブル形式
json	application/json	構造化された JSON
csv	text/csv	カンマ区切り
tsv	text/tab-separated-values	タブ区切り
yaml	application/yaml	YAML 形式
cbor	application/cbor	バイナリ CBOR
smile	application/smile	バイナリ Smile

9.2 各フォーマットの出力例

クエリ:

SELECT department, COUNT(*) AS cnt, AVG(salary) AS avg_salary
FROM employees
GROUP BY department
ORDER BY cnt DESC
LIMIT 3

txt フォーマット (format=txt):

  department   |     cnt     |  avg_salary
---------------+-------------+-------------
Engineering    |142          |95234.56
Marketing      |87           |78456.12
Sales          |65           |72345.89

json フォーマット (format=json):

{
  "columns": [
    {"name": "department", "type": "keyword"},
    {"name": "cnt", "type": "long"},
    {"name": "avg_salary", "type": "double"}
  ],
  "rows": [
    ["Engineering", 142, 95234.56],
    ["Marketing", 87, 78456.12],
    ["Sales", 65, 72345.89]
  ]
}

csv フォーマット (format=csv):

department,cnt,avg_salary
Engineering,142,95234.56
Marketing,87,78456.12
Sales,65,72345.89

tsv フォーマット (format=tsv):

department	cnt	avg_salary
Engineering	142	95234.56
Marketing	87	78456.12
Sales	65	72345.89

yaml フォーマット (format=yaml):

columns:
- name: "department"
  type: "keyword"
- name: "cnt"
  type: "long"
- name: "avg_salary"
  type: "double"
rows:
- - "Engineering"
  - 142
  - 95234.56
- - "Marketing"
  - 87
  - 78456.12
- - "Sales"
  - 65
  - 72345.89

9.3 カラムナー（列指向）レスポンス

POST /_sql?format=json
{
  "query": "SELECT department, COUNT(*) AS cnt FROM employees GROUP BY department LIMIT 3",
  "columnar": true
}

レスポンス:

{
  "columns": [
    {"name": "department", "type": "keyword"},
    {"name": "cnt", "type": "long"}
  ],
  "values": [
    ["Engineering", "Marketing", "Sales"],
    [142, 87, 65]
  ]
}

9.4 ページネーション

大量のデータを取得する場合は、カーソルベースのページネーションを使用する。

# 最初のリクエスト
POST /_sql?format=json
{
  "query": "SELECT * FROM employees ORDER BY employee_id",
  "fetch_size": 100
}

# レスポンスにカーソルが含まれる
{
  "columns": [...],
  "rows": [...],
  "cursor": "sDXF1ZXJ5QW5kRmV0Y2gBAAAAAAAAAAEWYUpOYklR..."
}

# 次のページの取得
POST /_sql?format=json
{
  "cursor": "sDXF1ZXJ5QW5kRmV0Y2gBAAAAAAAAAAEWYUpOYklR..."
}

# カーソルのクリア
POST /_sql/close
{
  "cursor": "sDXF1ZXJ5QW5kRmV0Y2gBAAAAAAAAAAEWYUpOYklR..."
}

---

10. 実践的なユースケース

10.1 BI ツール統合

Tableau との連携: JDBC ドライバを使用して Elasticsearch に接続し、ダッシュボードを作成する。

-- Tableau カスタム SQL の例: 月次売上レポート
SELECT 
  HISTOGRAM(order_date, INTERVAL 1 MONTH) AS month,
  product_category,
  COUNT(*) AS order_count,
  SUM(amount) AS total_revenue,
  AVG(amount) AS avg_order_value,
  COUNT(DISTINCT customer_id) AS unique_customers
FROM orders
WHERE order_date >= '2023-01-01'
GROUP BY HISTOGRAM(order_date, INTERVAL 1 MONTH), product_category
ORDER BY month, product_category

Grafana との連携: Elasticsearch データソースで SQL クエリを使用する。

-- Grafana の時系列パネル用クエリ
SELECT 
  HISTOGRAM(timestamp, INTERVAL 5 MINUTE) AS time,
  AVG(cpu_usage) AS avg_cpu,
  MAX(cpu_usage) AS max_cpu,
  AVG(memory_usage) AS avg_memory
FROM server_metrics
WHERE timestamp >= '2024-01-15T00:00:00Z' 
  AND timestamp < '2024-01-16T00:00:00Z'
  AND host = 'web-server-01'
GROUP BY HISTOGRAM(timestamp, INTERVAL 5 MINUTE)
ORDER BY time

10.2 アドホック分析

-- ログ分析: エラー率の推移
SELECT 
  HISTOGRAM(timestamp, INTERVAL 1 HOUR) AS hour,
  COUNT(*) AS total_requests,
  SUM(CASE WHEN status_code >= 500 THEN 1 ELSE 0 END) AS error_count,
  ROUND(
    CAST(SUM(CASE WHEN status_code >= 500 THEN 1 ELSE 0 END) AS DOUBLE) 
    / COUNT(*) * 100, 2
  ) AS error_rate_pct
FROM access_logs
WHERE timestamp >= '2024-01-15' AND timestamp < '2024-01-16'
GROUP BY HISTOGRAM(timestamp, INTERVAL 1 HOUR)
ORDER BY hour;

-- ユーザー行動分析
SELECT 
  user_agent_family,
  COUNT(*) AS session_count,
  AVG(page_views) AS avg_page_views,
  AVG(session_duration_sec) AS avg_duration,
  ROUND(
    CAST(SUM(CASE WHEN converted = true THEN 1 ELSE 0 END) AS DOUBLE) 
    / COUNT(*) * 100, 2
  ) AS conversion_rate
FROM user_sessions
WHERE session_start >= '2024-01-01'
GROUP BY user_agent_family
HAVING COUNT(*) > 100
ORDER BY session_count DESC;

10.3 レポート生成

-- 日次レポート: API パフォーマンスサマリー
SELECT 
  api_endpoint,
  COUNT(*) AS total_calls,
  ROUND(AVG(response_time_ms), 2) AS avg_response_ms,
  ROUND(PERCENTILE(response_time_ms, 50), 2) AS p50_ms,
  ROUND(PERCENTILE(response_time_ms, 95), 2) AS p95_ms,
  ROUND(PERCENTILE(response_time_ms, 99), 2) AS p99_ms,
  MIN(response_time_ms) AS min_ms,
  MAX(response_time_ms) AS max_ms,
  SUM(CASE WHEN status_code >= 400 THEN 1 ELSE 0 END) AS error_count
FROM api_logs
WHERE timestamp >= CURRENT_DATE - INTERVAL 1 DAY
  AND timestamp < CURRENT_DATE
GROUP BY api_endpoint
ORDER BY total_calls DESC;

-- 月次レポート: 部門別コスト分析
SELECT 
  department,
  HISTOGRAM(transaction_date, INTERVAL 1 MONTH) AS month,
  COUNT(*) AS transaction_count,
  SUM(amount) AS total_cost,
  AVG(amount) AS avg_transaction,
  MAX(amount) AS largest_transaction
FROM expense_reports
WHERE transaction_date >= '2024-01-01'
  AND status = 'approved'
GROUP BY department, HISTOGRAM(transaction_date, INTERVAL 1 MONTH)
ORDER BY department, month;

10.4 セキュリティ監視

-- ログイン失敗の監視
SELECT 
  source_ip,
  COUNT(*) AS failed_attempts,
  MIN(timestamp) AS first_attempt,
  MAX(timestamp) AS last_attempt,
  COUNT(DISTINCT target_user) AS targeted_users
FROM auth_logs
WHERE event_type = 'login_failed'
  AND timestamp >= NOW() - INTERVAL 1 HOUR
GROUP BY source_ip
HAVING COUNT(*) > 10
ORDER BY failed_attempts DESC;

-- 異常なデータアクセスパターンの検出
SELECT 
  user_id,
  HISTOGRAM(timestamp, INTERVAL 1 HOUR) AS hour,
  COUNT(*) AS access_count,
  COUNT(DISTINCT resource_id) AS unique_resources
FROM data_access_logs
WHERE timestamp >= CURRENT_DATE
GROUP BY user_id, HISTOGRAM(timestamp, INTERVAL 1 HOUR)
HAVING COUNT(*) > 1000
ORDER BY access_count DESC;

10.5 SRE / インフラ監視

-- ディスク使用率の傾向分析
SELECT 
  host,
  HISTOGRAM(timestamp, INTERVAL 1 DAY) AS day,
  ROUND(AVG(disk_usage_pct), 2) AS avg_disk_usage,
  ROUND(MAX(disk_usage_pct), 2) AS max_disk_usage
FROM system_metrics
WHERE timestamp >= NOW() - INTERVAL 30 DAY
  AND metric_type = 'disk'
GROUP BY host, HISTOGRAM(timestamp, INTERVAL 1 DAY)
HAVING MAX(disk_usage_pct) > 80
ORDER BY host, day;

-- サービスレベルの計算
SELECT 
  service_name,
  COUNT(*) AS total_requests,
  SUM(CASE WHEN response_time_ms <= 200 THEN 1 ELSE 0 END) AS fast_requests,
  ROUND(
    CAST(SUM(CASE WHEN response_time_ms <= 200 THEN 1 ELSE 0 END) AS DOUBLE)
    / COUNT(*) * 100, 4
  ) AS sli_pct
FROM request_logs
WHERE timestamp >= NOW() - INTERVAL 7 DAY
GROUP BY service_name
ORDER BY sli_pct ASC;

---

11. 制限事項

11.1 サポートされていない機能

Elasticsearch SQL には以下の制限がある。これらは Elasticsearch の基盤アーキテクチャに起因するものである。

機能	サポート状況	代替手段
JOIN	非サポート	アプリケーション側での結合、非正規化
サブクエリ	限定的	複数クエリに分割
INSERT / UPDATE / DELETE	非サポート	REST API (_doc, _bulk, _update_by_query)
CREATE TABLE / DROP TABLE	非サポート	Index Management API
CREATE INDEX	非サポート	Index API
ALTER TABLE	非サポート	Mapping API
トランザクション	非サポート	Elasticsearch はトランザクション非対応
ストアドプロシージャ	非サポート	Painless スクリプト
ビュー	非サポート	Alias, Data View
UNION / INTERSECT / EXCEPT	非サポート	Multi-search API
ウィンドウ関数	非サポート	Query DSL + スクリプト
再帰 CTE	非サポート	アプリケーション側で実装

11.2 型マッピングの制限

Elasticsearch 型	SQL 型	注意事項
text	VARCHAR	集約・ソート不可（keyword サブフィールドが必要）
keyword	VARCHAR	全文検索不可
long	BIGINT	-
integer	INTEGER	-
double	DOUBLE	-
float	REAL	-
date	TIMESTAMP	-
boolean	BOOLEAN	-
nested	サポート外	flatten 化が必要
object	ドット記法	address.city のようにアクセス
geo_point	GEO_POINT	限定的なサポート

11.3 集約の制限

-- NG: ネストされた集約
-- 以下のようなクエリは直接サポートされない
-- SELECT department, (SELECT MAX(salary) FROM ...) FROM employees GROUP BY department;

-- OK: 代替方法
SELECT 
  department,
  MAX(salary) AS max_salary
FROM employees
GROUP BY department;

-- NG: GROUP BY で ALIAS を使用
-- SELECT YEAR(hire_date) AS yr, COUNT(*) FROM employees GROUP BY yr;

-- OK: 式を繰り返す
SELECT YEAR(hire_date) AS yr, COUNT(*) FROM employees GROUP BY YEAR(hire_date);

11.4 text フィールドの制限

-- text フィールドに対する操作の制限

-- NG: text フィールドでの GROUP BY（集約不可）
-- SELECT description, COUNT(*) FROM articles GROUP BY description;

-- OK: keyword サブフィールドを使用
SELECT description.keyword, COUNT(*) FROM articles GROUP BY description.keyword;

-- OK: text フィールドへの全文検索
SELECT * FROM articles WHERE MATCH(description, 'elasticsearch tutorial');

---

12. パフォーマンスとベストプラクティス

12.1 クエリ最適化

1. 必要なフィールドのみを SELECT する

-- 非推奨: 全フィールド取得
SELECT * FROM large_index LIMIT 10000;

-- 推奨: 必要なフィールドのみ
SELECT employee_id, name, department FROM employees LIMIT 10000;

2. WHERE 句で早期にフィルタリングする

-- 非推奨: 全データをスキャン後にフィルタ
SELECT department, AVG(salary)
FROM employees
GROUP BY department
HAVING department = 'Engineering';

-- 推奨: WHERE で先にフィルタ
SELECT department, AVG(salary)
FROM employees
WHERE department = 'Engineering'
GROUP BY department;

3. 適切な fetch_size を設定する

POST /_sql?format=json
{
  "query": "SELECT * FROM large_index",
  "fetch_size": 500
}

4. 時間範囲を明示的に指定する

-- 非推奨: 時間範囲なし
SELECT * FROM access_logs WHERE status_code = 500;

-- 推奨: 時間範囲を指定
SELECT * FROM access_logs 
WHERE status_code = 500 
  AND timestamp >= '2024-01-15' 
  AND timestamp < '2024-01-16';

12.2 インデックス設計のベストプラクティス

{
  "mappings": {
    "properties": {
      "name": {
        "type": "text",
        "fields": {
          "keyword": {
            "type": "keyword",
            "ignore_above": 256
          }
        }
      },
      "department": {
        "type": "keyword"
      },
      "salary": {
        "type": "double"
      },
      "hire_date": {
        "type": "date",
        "format": "yyyy-MM-dd||epoch_millis"
      },
      "description": {
        "type": "text",
        "analyzer": "standard",
        "fields": {
          "keyword": {
            "type": "keyword",
            "ignore_above": 512
          }
        }
      }
    }
  }
}

12.3 translate API によるクエリ検証

本番環境でクエリを実行する前に、translate API で生成される Query DSL を確認する。

# ステップ 1: SQL の作成と translate
POST /_sql/translate
{
  "query": "SELECT department, AVG(salary) FROM employees WHERE status = 'active' GROUP BY department"
}

# ステップ 2: 生成された Query DSL を確認し、最適化の余地がないか検討
# ステップ 3: 問題がなければ実際に実行
POST /_sql?format=json
{
  "query": "SELECT department, AVG(salary) FROM employees WHERE status = 'active' GROUP BY department"
}

12.4 パフォーマンス監視

-- 重いクエリの特定 (Slow Log と組み合わせて使用)
-- elasticsearch.yml での設定:
-- index.search.slowlog.threshold.query.warn: 10s
-- index.search.slowlog.threshold.query.info: 5s
-- index.search.slowlog.threshold.query.debug: 2s

-- インデックスサイズの確認
SHOW TABLES LIKE 'access_logs*';

12.5 セキュリティのベストプラクティス

# ロールベースのアクセス制御
# elasticsearch.yml でのセキュリティ設定例

# SQL 専用ロールの作成
POST /_security/role/sql_reader
{
  "indices": [
    {
      "names": ["employees", "departments"],
      "privileges": ["read"],
      "field_security": {
        "grant": ["employee_id", "name", "department", "hire_date"]
      }
    }
  ]
}

# パラメータ化クエリを使用して SQL インジェクションを防止
POST /_sql?format=json
{
  "query": "SELECT * FROM employees WHERE department = ? AND salary > ?",
  "params": ["Engineering", 80000]
}

---

13. まとめ -- 他のクエリ言語との比較

13.1 クエリ言語の比較表

機能	Query DSL	Elasticsearch SQL	EQL	ES|QL
構文スタイル	JSON	SQL	イベントクエリ	パイプベース
学習曲線	高	低	中	低
全文検索	完全	MATCH/QUERY 経由	限定的	限定的
集約	完全	主要機能サポート	非サポート	STATS コマンド
JOIN	nested/has_child	非サポート	sequence	LOOKUP
時系列分析	date_histogram	HISTOGRAM	sequence	BUCKET
セキュリティ分析	可能だが複雑	基本的	最適	可能
パイプ処理	非サポート	非サポート	pipe 一部	ネイティブ
DDL/DML	REST API	非サポート	非サポート	非サポート
BI ツール連携	不可	JDBC/ODBC	不可	JDBC/ODBC(開発中)
プログラマティック	最適	良好	良好	良好
リアルタイム	最適	良好	最適	良好

13.2 ユースケース別の推奨

ユースケース	推奨クエリ言語	理由
複雑な全文検索	Query DSL	最も柔軟で強力
BI ダッシュボード	Elasticsearch SQL	JDBC 経由で BI ツールと直結
アドホック分析	Elasticsearch SQL / ES|QL	SQL の知識で即座に分析可能
セキュリティ脅威検出	EQL	イベントのシーケンス分析に最適
ログ分析パイプライン	ES|QL	パイプ処理で段階的に絞り込み
アプリケーション検索	Query DSL	プログラマティック制御が容易
レポート生成	Elasticsearch SQL	CSV/TSV 出力で自動化が容易
リアルタイム監視	Query DSL / EQL	低レイテンシ要件に対応

13.3 各クエリ言語の同一タスクの比較

以下は「部門別の平均給与を求め、平均給与が 80000 以上の部門を降順で表示する」というタスクを各言語で記述した例である。

Elasticsearch SQL:

SELECT department, AVG(salary) AS avg_salary
FROM employees
WHERE status = 'active'
GROUP BY department
HAVING AVG(salary) >= 80000
ORDER BY avg_salary DESC

Query DSL:

{
  "size": 0,
  "query": {
    "term": { "status": "active" }
  },
  "aggs": {
    "by_department": {
      "terms": {
        "field": "department.keyword",
        "order": { "avg_salary": "desc" }
      },
      "aggs": {
        "avg_salary": {
          "avg": { "field": "salary" }
        },
        "salary_filter": {
          "bucket_selector": {
            "buckets_path": { "avg": "avg_salary" },
            "script": "params.avg >= 80000"
          }
        }
      }
    }
  }
}

ES|QL:

FROM employees
| WHERE status == "active"
| STATS avg_salary = AVG(salary) BY department
| WHERE avg_salary >= 80000
| SORT avg_salary DESC

13.4 Elasticsearch SQL の今後

Elasticsearch SQL は成熟した機能であり、以下の方向で継続的に改善が行われている。

• ES|QL との共存: ES|QL は Elasticsearch SQL を置き換えるものではなく、補完する位置づけ
• 関数の拡充: 新しい SQL 関数やデータ型のサポートが順次追加
• パフォーマンスの改善: クエリオプティマイザの強化
• BI ツール互換性の向上: JDBC/ODBC ドライバの継続的な改善

13.5 結論

Elasticsearch SQL は、SQL の知識を Elasticsearch エコシステムで活用するための強力なブリッジである。以下のケースで特に価値を発揮する。

1. SQL に精通したチームメンバーが Query DSL を学ぶことなく即座にデータ分析を開始できる
2. BI ツールとの統合により、Elasticsearch のデータを視覚的に探索できる
3. アドホッククエリの実行速度を向上させ、データ探索のイテレーションを加速する
4. translate APIを活用することで、SQL から Query DSL への学習パスを提供する

ただし、複雑な検索要件、JOIN が必要なケース、DDL/DML 操作が必要な場合には、Query DSL や他の API を併用する必要がある。Elasticsearch SQL を他のクエリ言語と適切に使い分けることが、Elasticsearch を最大限に活用するための鍵となる。

---

参考リンク:

• Elasticsearch SQL 公式ドキュメント
• SQL Functions Reference
• JDBC Driver
• ODBC Driver