Bering Note – formerly 流沙河鎮

情報技術系のこと書きます。

OpenSearchをSQLで検索する: OpenSearch SQL pluginの紹介


OpenSearchは、基本的にJSONベースのDSL(query domain-specific language)によってデータを検索します。
一方で、SQLやPPLによる検索も可能で、DSLに不慣れな分析者やアプリ開発者が既存のSQL知識でデータを集計・探索・可視化する際に利用できます。また、SQLやPPLを通じた検索では、複数のindexのjoinも可能になります。
本エントリでは、OpenSearchをSQLで操作する機能やポイントをまとめます。 今回はSQLの解説のみに集中して、PPLについては別エントリに譲ります。

SQL plugin

OpenSearchにおけるSQLはSQL pluginによって実現されています。SQL pluginはOpenSearchの標準的なビルドに同梱されているため、通常は特別な導入手順を必要とせず使用できます。

SQLの実行

/_plugins/_sql API

SQLによるクエリは/_plugins/_sql APIを通じて受け付けます。
基本的な仕様は以下の通りです。

クエリパラメータ

パラメータ データ型 説明
format String レスポンスの形式。_sqlエンドポイントはjdbc、csv、raw、json形式をサポートします。_pplエンドポイントはjdbc、csv、raw形式をサポートします。デフォルトはjdbcです。
sanitize Boolean 結果内の特殊文字をエスケープするかどうかを指定します。デフォルトはtrueです。

リクエストボディフィールド

フィールド データ型 説明
query String 実行するクエリ。必須。
filter JSON object 結果のフィルタ。オプション。
fetch_size integer 1つのレスポンスで返す結果の数。結果のページネーションに使用されます。デフォルトは1,000。オプション。fetch_sizeはSQLでサポートされており、jdbcレスポンス形式の使用が必要です。

レスポンスボディフィールド

フィールド データ型 説明
schema Array すべてのフィールドのフィールド名と型を指定します。
data_rows 2D array 結果の配列。各結果は一致する1つの行(ドキュメント)を表します。
total Integer インデックス内の行(ドキュメント)の総数。
size Integer 1つのレスポンスで返す結果の数。
status String クエリ実行後にOpenSearchが返すHTTPレスポンスステータス。
テスト用indexの準備

実際の挙動を観察しましょう。テスト用のIndexを作成します。

POST /accounts/_bulk
{"index":{"_id":"1"}}
{"account_number":1,"firstname":"Amber","address":"880 Holmes Lane","balance":39225,"gender":"M","city":"Brogan","employer":"Pyrami","state":"IL","age":32,"email":"amberduke@pyrami.com","lastname":"Duke"}
{"index":{"_id":"6"}}
{"account_number":6,"firstname":"Hattie","address":"671 Bristol Street","balance":5686,"gender":"M","city":"Dante","employer":"Netagy","state":"TN","age":36,"email":"hattiebond@netagy.com","lastname":"Bond"}
{"index":{"_id":"13"}}
{"account_number":13,"firstname":"Nanette","address":"789 Madison Street","balance":32838,"gender":"F","city":"Nogal","employer":"Quility","state":"VA","age":28,"email":"nanettebates@quility.com","lastname":"Bates"}
{"index":{"_id":"18"}}
{"account_number":18,"firstname":"Dale","address":"467 Hutchinson Court","balance":4180,"gender":"M","city":"Orick","employer":null,"state":"MD","age":33,"email":"daleadams@boink.com","lastname":"Adams"}
基本的なSELECT

index名をテーブル名に使用してSELECT句を実行してみましょう。

POST /_plugins/_sql 
{
  "query" : "SELECT * FROM accounts"
}

これにより、スキーマと検索結果を含むレスポンスが得られます。

クエリ実行結果 

{
  "schema": [
    {
      "name": "account_number",
      "type": "long"
    },
    {
      "name": "firstname",
      "type": "text"
    },
    {
      "name": "address",
      "type": "text"
    },
    {
      "name": "balance",
      "type": "long"
    },
    {
      "name": "gender",
      "type": "text"
    },
    {
      "name": "city",
      "type": "text"
    },
    {
      "name": "employer",
      "type": "text"
    },
    {
      "name": "state",
      "type": "text"
    },
    {
      "name": "age",
      "type": "long"
    },
    {
      "name": "email",
      "type": "text"
    },
    {
      "name": "lastname",
      "type": "text"
    }
  ],
  "datarows": [
    [
      1,
      "Amber",
      "880 Holmes Lane",
      39225,
      "M",
      "Brogan",
      "Pyrami",
      "IL",
      32,
      "amberduke@pyrami.com",
      "Duke"
    ],
    [
      6,
      "Hattie",
      "671 Bristol Street",
      5686,
      "M",
      "Dante",
      "Netagy",
      "TN",
      36,
      "hattiebond@netagy.com",
      "Bond"
    ],
    [
      13,
      "Nanette",
      "789 Madison Street",
      32838,
      "F",
      "Nogal",
      "Quility",
      "VA",
      28,
      "nanettebates@quility.com",
      "Bates"
    ],
    [
      18,
      "Dale",
      "467 Hutchinson Court",
      4180,
      "M",
      "Orick",
      null,
      "MD",
      33,
      "daleadams@boink.com",
      "Adams"
    ]
  ],
  "total": 4,
  "size": 4,
  "status": 200
}

レスポンスフォーマットの変更

SQLクエリはデフォルトでスキーマ情報とクエリ結果を含むjdbcフォーマットでクエリ結果を返します。
クエリパラメータでレスポンスフォーマットを指定することで、jdbc, csv, raw,rawフォーマットでレスポンスを得ることも可能です。

POST _plugins/_sql?format=csv
{
  "query" : "SELECT * FROM accounts"
}

クエリ実行結果 

account_number,firstname,address,balance,gender,city,employer,state,age,email,lastname
1,Amber,880 Holmes Lane,39225,M,Brogan,Pyrami,IL,32,amberduke@pyrami.com,Duke
6,Hattie,671 Bristol Street,5686,M,Dante,Netagy,TN,36,hattiebond@netagy.com,Bond
13,Nanette,789 Madison Street,32838,F,Nogal,Quility,VA,28,nanettebates@quility.com,Bates
18,Dale,467 Hutchinson Court,4180,M,Orick,,MD,33,daleadams@boink.com,Adams

クライアント

OpenSearchへのSQLクライアントとして、OpenSearchダッシュボードや直接のAPI呼び出しに加えて、いくつかの選択肢があります。

CLI

SQLとPPLを実行するのに使えるPythonベースのCLIクライアントがあります。

pip3 install opensearchsql

CLIを起動する際はopensearchsql <https://host:port> --username <user> --password <password>とします。

.venv ❯ opensearchsql https://localhost:9200 --username admin --password $PASSWORD
   ____                  _____                      __  
  / __ \____  ___  ____ / ___/___  ____ ___________/ /_ 
 / / / / __ \/ _ \/ __ \\__ \/ _ \/ __ `/ ___/ ___/ __ \
/ /_/ / /_/ /  __/ / / /__/ /  __/ /_/ / /  / /__/ / / /
\____/ .___/\___/_/ /_/____/\___/\__,_/_/   \___/_/ /_/ 
    /_/                                                 

Server: OpenSearch 3.2.0
CLI Version: 1.0.0
Endpoint: https://localhost:9200
Query Language: sql

CLIを初めて起動すると、設定ファイルが ~/.config/opensearchsql-cli/configに自動的に作成され、その後は設定が自動的に読み込まれます。 設定ファイルには、エンドポイントや認証方式などを設定できます。

以下のようにSQLを実行できます。

opensearchsql> SELECT firstname, age,address FROM accounts;                                                                             
fetched rows / total rows = 4/4
+-------------+-------+----------------------+
| firstname   | age   | address              |
|-------------+-------+----------------------|
| Amber       | 32    | 880 Holmes Lane      |
| Hattie      | 36    | 671 Bristol Street   |
| Nanette     | 28    | 789 Madison Street   |
| Dale        | 33    | 467 Hutchinson Court |
+-------------+-------+----------------------+

CLIの詳細はSQL and PPL CLIを参照してください。

JDBC driverとODBC driver

JDBC, ODBCドライバが提供されているため、TableauやExcelなどの様々なクライアントからの接続が可能です。
github.com
docs.opensearch.org
https://docs.opensearch.org/latest/search-plugins/sql/sql/odbc/

OpenSearch SQLの基本

OpenSearchにおけるSQLでは、IndexがTable、Documentが行、FieldがColumnに該当します。

識別子（Identifier）

index名(テーブル名)やフィールド名の記述方法を識別子（Identifier）と呼び、通常の識別子と区切り識別子の2種類があります。

通常の識別子

通常の識別子は、ASCII文字（大文字または小文字）で始まる文字列です。2文字目以降は文字、数字、アンダースコア（_）を使用できます。予約語や空白、特殊文字は使用できません。

OpenSearchでは、以下のような特殊なパターンの通常識別子もサポートしています。ドット（.）で始まる識別子は隠しindexに使用され、アットマーク（@）で始まる識別子はLogstashが生成するメタフィールドに使用されます。また、ハイフン（-）を含む識別子は日付情報を持つindex名に、アスタリスク（*）はindexパターンのワイルドカード検索に使用されます。

通常の識別子は、バッククォートやエスケープ文字なしでそのまま使用できます。

POST _plugins/_sql?format=csv
{
  "query" : "SELECT account_number, firstname, lastname FROM accounts"
}

この例では、account_number、firstname、lastname、accountsが全て識別子です。

区切り識別子

区切り識別子は、通常の識別子で許可されていない特殊文字を含むことができます。バッククォート（`）で囲む必要があります。
index名にドット（.）が含まれる場合、例えばlog-2021.01.11のような名前は、バッククォートで囲んで`log-2021.01.11`のように記述します。区切り識別子が必要になるのは、予約語を識別子として使用する場合、ドット（.）やハイフン（-）などの特殊文字を含む場合、Unicode文字を含む場合などです。

POST _plugins/_sql?format=csv
{
  "query" : "SELECT `account_number` FROM `accounts`"
}
大文字小文字の区別

識別子は大文字小文字を区別します。OpenSearchに保存されている通りに正確に記述する必要があります。
例えば、実際のindex名がaccounts（小文字）の場合、Accountsと記述するとindexが見つからないというエラーが発生します。

クエリ構文と構成要素

OpenSearchのSQLクエリは、以下の構文で記述します。

SELECT [DISTINCT] (* | 取得する値) [[AS] 別名] [, ...]
FROM index_name
[WHERE 絞り込み条件]
[GROUP BY グループ化する項目 [, ...]
 [HAVING グループ化後の絞り込み条件]]
[ORDER BY 並び替える項目 [IS [NOT] NULL] [ASC | DESC] [, ...]]
[LIMIT [開始位置, ] 件数]

必須: SELECT句とFROM句
任意: WHERE、GROUP BY、HAVING、ORDER BY、LIMIT

クエリで使える値の種類

1. 固定値（リテラル）

SELECT 100, 'Tokyo', true, '2025-01-01'

数値、文字列、真偽値、日付などの定数を直接指定できます。

2. フィールド名

SELECT account_number, firstname, age FROM accounts

indexに格納されているフィールドを指定します。

3. 計算式（Expression）

SELECT age * 2, UPPER(firstname), balance / 1000 AS balance_k

フィールドや固定値を、演算子や関数を使って組み合わせた式です。SELECT、GROUP BY、ORDER BYで使用できます。

4. 条件式（Predicate）

WHERE age >= 30 AND balance > 10000
HAVING AVG(balance) < 20000

データを絞り込むための条件式です。WHERE句やHAVING句で使用します。

条件式で使える演算子
演算子 意味 例
=, <>, >, <, >=, <= 比較 age >= 30
IS NULL / IS NOT NULL NULL判定 employer IS NOT NULL
BETWEEN 範囲指定 age BETWEEN 20 AND 40
IN 複数値の一致 state IN ('CA', 'NY', 'TX')
LIKE 文字列の部分一致 city LIKE 'San%'
AND, OR, NOT 論理演算 age > 30 AND balance < 5000
実行順序

SQLクエリは以下の順序で実行されます。

  1. FROM - 検索対象のindexを特定
  2. WHERE - 条件に基づいてデータを絞り込み
  3. GROUP BY - 指定した項目でデータをグループ化
  4. HAVING - グループ化後のデータをさらに絞り込み
  5. SELECT - 取得する項目を選択
  6. ORDER BY - 結果を並び替え
  7. LIMIT - 件数を制限
データ型

OpenSearchのSQLでは、様々なデータ型がサポートされています。各データ型はOpenSearchの型とSQLの型にマッピングされます。

サポートされているデータ型
OpenSearch SQL Type OpenSearch Type SQL Type
boolean boolean BOOLEAN
byte byte TINYINT
short byte SMALLINT
integer integer INTEGER
long long BIGINT
float float REAL
half_float float FLOAT
scaled_float float DOUBLE
double double DOUBLE
keyword string VARCHAR
text text VARCHAR
date timestamp TIMESTAMP
date_nanos timestamp TIMESTAMP
ip ip VARCHAR
binary binary VARBINARY
object struct STRUCT
nested array STRUCT
日付と時刻型

日付と時刻を扱う型として、DATE、TIME、DATETIME、TIMESTAMP、INTERVALがあります。OpenSearchのDSLではdate型が唯一の日時関連型ですが、SQLでは用途に応じて複数の型を使い分けます。

DATE型
DATE型は、タイムゾーンに関係なく暦上の日付を表します。時刻情報は含まれず、1000-01-01から9999-12-31の範囲をサポートします。
形式: yyyy-MM-dd（例: 2023-01-15）

TIME型
TIME型は、タイムゾーンに関係なく時刻を表します。日付情報は含まれません。
形式: hh:mm:ss[.fraction]（例: 14:30:00）
範囲: 00:00:00.0000000000から23:59:59.9999999999

DATETIME型
DATETIME型は、日付と時刻を組み合わせたものです。タイムゾーン情報は含まれません。
形式: yyyy-MM-dd hh:mm:ss[.fraction]（例: 2023-01-15 14:30:00）
範囲: 0001-01-01 00:00:00.0000000000から9999-12-31 23:59:59.9999999999

TIMESTAMP型
TIMESTAMP型は、タイムゾーンを含む絶対的な時刻を表します。保存時には現在のタイムゾーンからUTCに変換され、取得時にはUTCから設定されたタイムゾーンに変換されます。
形式: yyyy-MM-dd hh:mm:ss[.fraction]（例: 2023-01-15 14:30:00 UTC）
範囲: 0001-01-01 00:00:01.9999999999 UTCから9999-12-31 23:59:59.9999999999 UTC

INTERVAL型
INTERVAL型は、期間や時間の長さを表します。
形式: INTERVAL expr unit（例: INTERVAL 5 DAY）
unitには、MICROSECOND、SECOND、MINUTE、HOUR、DAY、WEEK、MONTH、QUARTER、YEARが指定できます。INTERVAL型には年週間隔（年、四半期、月、週）と日時間隔（日、時、分、秒、マイクロ秒）の2つのクラスがあります。

型変換

INTERVAL型を除く全ての日付と時刻型は、相互に変換できます。ただし、変換により値が変わったり情報が失われる場合があります。
DATEからの変換では、時刻情報がないため、DATETIMEに変換すると00:00:00が付加されます。例えば2020-08-17は2020-08-17 00:00:00になります。TIMESTAMPに変換すると、時刻に加えてセッションのタイムゾーン（デフォルトはUTC）も付加されます。
TIMEからの変換では、日付情報がないため、他の日時型には変換できません。
DATETIMEからの変換では、DATEへの変換は日付部分のみを抽出し、TIMEへの変換は時刻部分のみを抽出します。TIMESTAMPへの変換では、セッションのタイムゾーン情報が付加されます。
TIMESTAMPからの変換では、DATEへの変換は日付部分を、TIMEへの変換は時刻部分を抽出します。DATETIMEへの変換では、タイムゾーン情報が除外されます。

複雑なクエリ

OpenSearchのSQLでは、複数のindexを組み合わせた複雑なクエリも実行できます。

テスト用indexの準備

複雑なクエリを試すために、新しいindexを作成します。すでに作成済みのaccountsインデックスに加えて、employeesインデックスを作成しましょう。

POST /employees/_bulk
{"index":{"_id":"3"}}
{"id":3,"name":"Bob Smith"}
{"index":{"_id":"4"}}
{"id":4,"name":"Susan Smith"}
{"index":{"_id":"6"}}
{"id":6,"name":"Jane Smith"}

以下の2つのindexを使用していきます。
accounts: アカウント情報

POST _plugins/_sql?format=csv
{
  "query" : "SELECT * FROM accounts"
}

{
  "schema": [
    {
      "name": "account_number",
      "type": "long"
    },
    {
      "name": "firstname",
      "type": "text"
    },
    {
      "name": "address",
      "type": "text"
    },
    {
      "name": "balance",
      "type": "long"
    },
    {
      "name": "gender",
      "type": "text"
    },
    {
      "name": "city",
      "type": "text"
    },
    {
      "name": "employer",
      "type": "text"
    },
    {
      "name": "state",
      "type": "text"
    },
    {
      "name": "age",
      "type": "long"
    },
    {
      "name": "email",
      "type": "text"
    },
    {
      "name": "lastname",
      "type": "text"
    }
  ],
  "datarows": [
    [
      1,
      "Amber",
      "880 Holmes Lane",
      39225,
      "M",
      "Brogan",
      "Pyrami",
      "IL",
      32,
      "amberduke@pyrami.com",
      "Duke"
    ],
    [
      6,
      "Hattie",
      "671 Bristol Street",
      5686,
      "M",
      "Dante",
      "Netagy",
      "TN",
      36,
      "hattiebond@netagy.com",
      "Bond"
    ],
    [
      13,
      "Nanette",
      "789 Madison Street",
      32838,
      "F",
      "Nogal",
      "Quility",
      "VA",
      28,
      "nanettebates@quility.com",
      "Bates"
    ],
    [
      18,
      "Dale",
      "467 Hutchinson Court",
      4180,
      "M",
      "Orick",
      null,
      "MD",
      33,
      "daleadams@boink.com",
      "Adams"
    ]
  ],
  "total": 4,
  "size": 4,
  "status": 200
}

employees: 従業員情報

POST _plugins/_sql?format=csv
{
  "query" : "SELECT * FROM employees"
}

{
  "schema": [
    {
      "name": "name",
      "type": "text"
    },
    {
      "name": "id",
      "type": "long"
    }
  ],
  "datarows": [
    [
      "Bob Smith",
      3
    ],
    [
      "Susan Smith",
      4
    ],
    [
      "Jane Smith",
      6
    ]
  ],
  "total": 3,
  "size": 3,
  "status": 200
}
JOIN（結合）

JOINを使うことで、複数のindexのデータを結合して取得できます。OpenSearchのSQLでは、INNER JOIN、CROSS JOIN、LEFT OUTER JOINがサポートされています。
注意点として、OpenSeachはJOINのハッシュ結合をコーディネーターノードで実施するため、データセットが大きい場合、リソースや性能に問題が起きる可能性があります。その点をよく留意してご利用ください。
未来の話にはなりますが、OSSコミュニティでは、より効率の良いJOINを実現するための開発が進んでいて、この辺りの話はOpenSearchConで語られています。
bering.hatenadiary.com

INNER JOIN（内部結合）

2つのindexから、結合条件に一致するデータのみを取得します。INNERキーワードは省略可能です。

構文:

SELECT カラムリスト
FROM index1 エイリアス1
[INNER] JOIN index2 エイリアス2
  ON 結合条件

例:

POST _plugins/_sql?format=csv
{
  "query" : """SELECT
    a.account_number, a.firstname, a.lastname,
    e.id, e.name
    FROM accounts a
    JOIN employees e
    ON a.account_number = e.id
"""

この例では、accountsのaccount_numberとemployeesのidが一致するデータのみが取得されます。

a.lastname,e.name,a.account_number,e.id,a.firstname
Bond,Jane Smith,6,6,Hattie
CROSS JOIN（クロス結合）

2つのindexの全ての組み合わせ（デカルト積）を生成します。ON句を指定しないINNER JOINと同じ動作です。

例:

POST _plugins/_sql?format=csv
{
  "query" : """
  SELECT
  a.account_number, a.firstname, a.lastname,
  e.id, e.name
  FROM accounts a
  JOIN employees e
"""

a.lastname,e.name,a.account_number,e.id,a.firstname
Bates,Bob Smith,13,3,Nanette
Adams,Bob Smith,18,3,Dale
Duke,Bob Smith,1,3,Amber
Bond,Bob Smith,6,3,Hattie
Bates,Susan Smith,13,4,Nanette
Adams,Susan Smith,18,4,Dale
Duke,Susan Smith,1,4,Amber
Bond,Susan Smith,6,4,Hattie
Bates,Jane Smith,13,6,Nanette
Adams,Jane Smith,18,6,Dale
Duke,Jane Smith,1,6,Amber
Bond,Jane Smith,6,6,Hattie

注意点として、大量のデータを持つindexへのCROSS JOINはメモリ消費が激しいため、メモリ不足によりクエリが強制終了される可能性があります。

LEFT OUTER JOIN（左外部結合）

左側のindex（FROM句で指定したindex）の全てのデータを取得し、右側のindexに一致するデータがあれば結合します。一致するデータがない場合、右側のカラムはnullになります。OUTERキーワードは省略可能です。

例:

POST _plugins/_sql?format=csv
{
  "query" : """
  SELECT
    a.account_number, a.firstname, a.lastname,
    e.id, e.name
  FROM accounts a
  LEFT JOIN employees e
    ON a.account_number = e.id
"""
}

a.lastname,e.name,a.account_number,e.id,a.firstname
Bond,Jane Smith,6,6,Hattie
Adams,null,18,null,Dale
Bates,null,13,null,Nanette
Duke,null,1,null,Amber

account_number 6のみがemployeesに存在するため、他のレコードの右側カラムはnullになっています。

制約事項

JOINを使用する際は、以下の制約に注意してください。

  • 結合できるのは2つのindexのみ
  • indexには必ずエイリアスを付ける（例：accounts a）
  • ON句ではAND条件のみ使用可能
  • WHERE句では複数indexにまたがる条件を混在させない
    • OK: WHERE (a.type1 > 3 OR a.type1 < 0) AND (b.type2 > 4 OR b.type2 < -1)
    • NG: WHERE (a.type1 > 3 OR b.type2 < 0) AND (a.type1 > 4 OR b.type2 < -1)
  • 結果に対するGROUP BYやORDER BYは使用不可
  • LIMIT with OFFSET（例：LIMIT 25 OFFSET 25）は未サポート
集計関数

集計関数を使うことで、データをグループ化して合計、平均、最大値などを計算できます。集計関数はGROUP BY句、SELECT句、HAVING句で使用できます。

テスト用indexの準備

集計関数を試すために、商品の注文データを含むindexを作成します。

PUT orders/_bulk?refresh
{"index":{"_id":"1"}}
{"order_id": 1, "category":"Electronics", "product":"Laptop", "price":1200, "quantity":1, "order_date":"2023-01-15"}
{"index":{"_id":"2"}}
{"order_id": 2, "category":"Electronics", "product":"Mouse", "price":25, "quantity":2, "order_date":"2023-01-20"}
{"index":{"_id":"3"}}
{"order_id": 3, "category":"Books", "product":"SQL Guide", "price":45, "quantity":3, "order_date":"2023-02-10"}
{"index":{"_id":"4"}}
{"order_id": 4, "category":"Electronics", "product":"Keyboard", "price":75, "quantity":1, "order_date":"2023-02-15"}
{"index":{"_id":"5"}}
{"order_id": 5, "category":"Books", "product":"Data Analysis", "price":60, "quantity":2, "order_date":"2023-03-05"}
{"index":{"_id":"6"}}
{"order_id": 6, "category":"Books", "product":"Database Design", "price":50, "quantity":1, "order_date":"2023-03-20"}
GROUP BY句

GROUP BY句を使うと、指定したフィールドの値ごとにデータをグループ化できます。集計関数はこのグループごとに計算され、各グループにつき1行の結果を返します。

フィールド名でグループ化

POST _plugins/_sql?format=csv
{
  "query" : """
  SELECT category, sum(price * quantity) as total_amount
  FROM orders 
  GROUP BY category
"""
}

カテゴリごとの売上合計が計算されます。

category,total_amount
Books,305
Electronics,1325

カラム番号でグループ化
SELECT句で指定したカラムの順番（1から始まる）を使ってグループ化することもできます。

POST _plugins/_sql?format=csv
{
  "query" : """
  SELECT category, sum(price * quantity) as total_amount
  FROM orders 
  GROUP BY 1
"""
}

最初のカラム（category）でグループ化されます。

category,total_amount
Books,305
Electronics,1325

式を使ったグループ化

関数を使った式でもグループ化できます。次の例では、月ごとの平均注文金額を計算します。

POST _plugins/_sql?format=csv
{
  "query" : """
  SELECT month(order_date) as order_month, avg(price * quantity) as avg_amount
  FROM orders 
  GROUP BY month(order_date)
"""
}

order_month,avg_amount
1,625.0
2,105.0
3,85.0
SELECT句での集計

集計関数はSELECT句で直接使用できるほか、より大きな式の一部として使うこともできます。

集計結果を計算に利用
次の例では、各カテゴリの平均価格に10%の税金を加算します。

POST _plugins/_sql?format=csv
{
  "query" : """
  SELECT category, avg(price) * 1.1 as avg_price_with_tax
  FROM orders 
  GROUP BY category
"""
}

category,avg_price_with_tax
Books,56.833333333333336
Electronics,476.6666666666667

集計関数の引数に式を使用
集計関数の引数に式を渡すこともできます。次の例では、各注文の金額（価格×数量）を計算してから、カテゴリごとの平均を求めます。

POST _plugins/_sql?format=csv
{
  "query" : """
  SELECT category, avg(price * quantity) as avg_order_amount
  FROM orders 
  GROUP BY category
"""
}

category,avg_order_amount
Books,101.66666666666667
Electronics,441.6666666666667
COUNT関数

COUNT関数は行数をカウントします。COUNT(*)は全ての行をカウントし、COUNT(field)は指定したフィールドがnullでない行のみをカウントします。

POST _plugins/_sql?format=csv
{
  "query" : """
  SELECT category, count(*) as order_count
  FROM orders 
  GROUP BY category
"""
}

category,order_count
Electronics,3
Books,3
HAVING句

WHERE句は集計前にデータを絞り込みますが、HAVING句は集計後のグループを絞り込みます。そのため、HAVING句では集計関数を使用できます。

GROUP BYと組み合わせたHAVING
次の例では、合計売上が300を超えるカテゴリのみを表示します。

POST _plugins/_sql?format=csv
{
  "query" : """
  SELECT category, sum(price * quantity) as total_amount
  FROM orders
  GROUP BY category
  HAVING sum(price * quantity) > 300
"""
}

category,total_amount
Electronics,1325
Books,305

HAVING句とSELECT句で異なる集計関数を使うこともできます。次の例では、HAVING句でcount関数を使って絞り込み、SELECT句ではsum関数で合計を表示します。

POST _plugins/_sql?format=csv
{
  "query" : """
  SELECT category, sum(price * quantity) as total_amount
  FROM orders
  GROUP BY category
  HAVING count(*) >= 3
"""
}

category,total_amount
Electronics,1325
Books,305

エイリアスの使用
HAVING句では、SELECT句で定義したエイリアスを使用できます。

POST _plugins/_sql?format=csv
{
  "query" : """
  SELECT category, sum(price * quantity) as total
  FROM orders
  GROUP BY category
  HAVING total > 1000
"""
}

category,total
Electronics,1325

GROUP BYなしのHAVING
GROUP BYを使わずにHAVING句を使用すると、全データを1つのグループとして扱います。次の例では、複数のカテゴリが存在するかをチェックします。

POST _plugins/_sql?format=csv
{
  "query" : """
  SELECT 'True' as multiple_categories
  FROM orders 
  HAVING count(DISTINCT category) > 1
"""
}

multiple_categories
True
サブクエリ

サブクエリは、別のSQL文の中に埋め込まれたSELECT文です。括弧で囲んで記述します。

IN句でのサブクエリ

WHERE句のIN条件でサブクエリを使用できます。

例:

POST _plugins/_sql?format=csv
{
  "query" : """
  SELECT a1.firstname, a1.lastname, a1.balance
  FROM accounts a1
  WHERE a1.account_number IN (
    SELECT a2.account_number
    FROM accounts a2
    WHERE a2.balance > 10000
  )
"""
}

この例では、balance > 10000のアカウント番号を取得し、そのアカウント番号に一致するレコードを返します。
内部的には、このサブクエリはJOINに変換されて実行されます。

a1.lastname,a1.balance,a1.firstname
Duke,39225,Amber
Bates,32838,Nanette
FROM句でのサブクエリ

FROM句にサブクエリを指定することで、サブクエリの結果を仮想的なテーブルとして扱えます。

例:

POST _plugins/_sql?format=csv
{
  "query" : """
  SELECT firstname, lastname, age
  FROM (
    SELECT firstname, lastname, age
    FROM accounts
    WHERE age > 30
  ) AS a
"""

サブクエリでage > 30のデータを絞り込み、さらにカラム名をエイリアスで変更した結果を取得しています。

firstname,lastname,age
Amber,Duke,32
Hattie,Bond,36
Dale,Adams,33
関数

OpenSearchのSQLでは、全文検索など様々な関数が提供されています。ここでは主な関数を紹介します。

利用できる関数の一覧はFunctionsを参照してください。

Match query（一致検索）

MATCHQUERYまたはMATCH_QUERY関数を使って、指定したフィールドに対して全文検索を実行できます。

構文:

MATCHQUERY(field, query)
field = MATCH_QUERY(query)

例:

POST _plugins/_sql?format=csv
{
  "query" : """
  SELECT account_number, address
  FROM accounts
  WHERE MATCHQUERY(address, 'Holmes')
"""
}

これによってaddressフィールドに「Holmes」が含まれるドキュメントが検索されます。

account_number,address
1,880 Holmes Lane

SQLによる全文検索クエリでは、様々なオプションを指定できます。詳細はFull-text searchを参照してください。

Multi-match（複数フィールド検索）

MULTI_MATCH関数を使って、複数のフィールドに対して同時に検索できます。各フィールドには重み付け（^で指定）も可能です。

構文:

multi_match('query'=query_expression[, 'fields'=field_expression][, option=<option_value>]*)

例:

POST _plugins/_sql?format=csv
{
  "query" : """
  SELECT account_number, address, city
  FROM accounts
  WHERE multi_match('query'='Lane', 'fields'='address^2,city')
"""
}

addressとcityの両方のフィールドから「Lane」を検索します。addressフィールドには2倍の重み付けがされています。

account_number,address,city
1,880 Holmes Lane,Brogan
Query string（クエリ文字列検索）

QUERY関数を使って、Luceneのクエリ文字列構文で検索できます。

構文:

query('query'=query_expression[, 'fields'=field_expression][, option=<option_value>]*)

例:

POST _plugins/_sql?format=csv
{
  "query" : """
  SELECT account_number, address
  FROM accounts
  WHERE query('address:Lane OR address:Street')
"""
}

addressフィールドに「Lane」または「Street」が含まれるドキュメントが検索されます。

account_number,address
1,880 Holmes Lane
6,671 Bristol Street
13,789 Madison Street
Match phrase（フレーズ一致検索）

MATCHPHRASEQUERY関数を使って、フィールド内の完全なフレーズを検索できます。

構文:

matchphrasequery(field_expression, query_expression[, option=<option_value>]*)

例:

POST _plugins/_sql?format=csv
{
  "query" : """
  SELECT account_number, address
  FROM accounts
  WHERE matchphrasequery(address, 'Holmes Lane')
"""
}

「Holmes Lane」というフレーズ全体に一致するドキュメントが検索されます。

account_number,address
1,880 Holmes Lane
Score query（スコア付き検索）

SCORE関数を使って、検索結果に関連度スコアを付けて取得できます。スコアによるブースト（重み付け）も可能です。

構文:

SCORE(match_query_expression, boost_value)

例:

POST _plugins/_sql?format=csv
{
  "query" : """
  SELECT account_number, address, _score
  FROM accounts
  WHERE SCORE(MATCH_QUERY(address, 'Lane'), 0.5) OR
        SCORE(MATCH_QUERY(address, 'Street'), 100)
  ORDER BY _score
"""
}

「Lane」に一致した場合はスコア0.5、「Street」に一致した場合はスコア100が付与されます。

account_number,address,_score
1,880 Holmes Lane,0.5
6,671 Bristol Street,100.0
13,789 Madison Street,100.0
Wildcard query（ワイルドカード検索）

WILDCARD_QUERY関数を使って、ワイルドカード（*や?）を使った検索ができます。

構文:

wildcard_query(field_expression, wildcard_pattern[, boost=<value>])

例:

POST _plugins/_sql?format=csv
{
  "query" : """
  SELECT account_number, address
  FROM accounts
  WHERE wildcard_query(address, '*olm*')
"""
}

「olm」を含む任意の文字列にマッチするドキュメントが検索されます。

account_number,address
1,880 Holmes Lane
ネストされたデータのクエリ

これまでのクエリ例では、各フィールドが単一の値を持つフラットな構造を扱ってきました。しかし、実際のドキュメントは、配列やオブジェクトがネストされた複雑な構造を持つことがあります。
例えば、従業員データで「1人の従業員が複数のプロジェクトを担当している」ような場合、プロジェクトは配列として格納されます。

{
  "name": "Bob Smith",
  "projects": [
    {"name": "SQL security", "started_year": 1999},
    {"name": "OpenSearch security", "started_year": 2015}
  ]
}

このようなネストされた配列の中身を検索・取得するには、PartiQLという仕様に基づいた特別な構文を使用します。

テスト用indexの準備

プロジェクト情報をネストした配列として持つindexを作成します。

PUT employees_projects
{
  "mappings": {
    "properties": {
      "id": {"type": "long"},
      "name": {"type": "text"},
      "title": {"type": "text"},
      "projects": {
        "type": "nested",
        "properties": {
          "name": {"type": "text"},
          "started_year": {"type": "long"}
        }
      }
    }
  }
}

POST employees_projects/_bulk?refresh
{"index":{"_id":"1"}}
{"id":3,"name":"Bob Smith","title":null,"projects":[{"name":"SQL Spectrum querying","started_year":1990},{"name":"SQL security","started_year":1999},{"name":"OpenSearch security","started_year":2015}]}
{"index":{"_id":"2"}}
{"id":4,"name":"Susan Smith","title":"Dev Mgr","projects":[]}
{"index":{"_id":"3"}}
{"id":6,"name":"Jane Smith","title":"Software Eng 2","projects":[{"name":"SQL security","started_year":1998},{"name":"Hello security","started_year":2015}]}

POST /_plugins/_sql?format=csv
{
  "query": "SELECT * FROM employees_projects"
}

name,id,projects,title
Bob Smith,3,"[{name=SQL Spectrum querying, started_year=1990}, {name=SQL security, started_year=1999}, {name=OpenSearch security, started_year=2015}]",
Susan Smith,4,,Dev Mgr
Jane Smith,6,"[{name=SQL security, started_year=1998}, {name=Hello security, started_year=2015}]",Software Eng 2
ネストされた配列の展開（UNNEST）

ネストされた配列を展開して、各要素を個別の行として扱うことができます。

構文:

FROM parent_table AS alias1,
     alias1.nested_field AS alias2

例:

POST _plugins/_sql
{
  "query" : """
  SELECT e.name AS employeeName,
         p.name AS projectName
  FROM employees_projects AS e,
       e.projects AS p
  WHERE p.name LIKE '%security%'
"""
}

e.projects AS pにより、projects配列の各要素が展開されます。Bob Smithは3つのプロジェクトを持っていますが、そのうち「security」を含むのは2つなので、Bob Smithの行が2行出力されます。

{
  "schema": [
    {
      "name": "name",
      "alias": "employeeName",
      "type": "text"
    },
    {
      "name": "projects.name",
      "alias": "projectName",
      "type": "text"
    }
  ],
  "total": 4,
  "datarows": [
    [
      "Bob Smith",
      "SQL security"
    ],
    [
      "Bob Smith",
      "OpenSearch security"
    ],
    [
      "Jane Smith",
      "SQL security"
    ],
    [
      "Jane Smith",
      "Hello security"
    ]
  ],
  "size": 4,
  "status": 200
}
EXISTS句での使用

配列内に条件を満たす要素が「存在するかどうか」だけを確認したい場合は、EXISTS句を使用します。

例:

POST _plugins/_sql?format=csv
{
  "query" : """
  SELECT e.name AS employeeName
  FROM employees_projects AS e
  WHERE EXISTS (
      SELECT *
      FROM e.projects AS p
      WHERE p.name LIKE '%security%'
  )
"""
}

基本的なUNNESTと異なり、従業員ごとに1行のみ返されます。「securityを含むプロジェクトを担当している従業員」のリストが取得できます。

employeeName
Bob Smith
Jane Smith
データの削除

DELETE文を使って、条件に一致するドキュメントを削除できます。

設定の有効化

DELETE文はデフォルトで無効になっています。使用するには、まず以下の設定で有効化する必要があります。

PUT _plugins/_query/settings
{
  "transient": {
    "plugins.sql.delete.enabled": "true"
  }
}

OpenSearchはデータ構造上、行レベルの削除を頻繁に受け付ける運用がパフォーマンス面で得意ではない点に留意して使用してください。

条件を指定した削除

WHERE句で条件を指定して、一致するドキュメントのみを削除できます。

POST _plugins/_sql
{
  "query" : """
  DELETE FROM accounts
  WHERE age > 30
"""
}

削除されたドキュメント数が結果として返されます。
datarowsの値は削除されたドキュメント数を示します。この例では3件のドキュメントが削除されました。

{
  "schema" : [
    {
      "name" : "deleted_rows",
      "type" : "long"
    }
  ],
  "total" : 1,
  "datarows" : [
    [
      3
    ]
  ],
  "size" : 1,
  "status" : 200
}

SQLプラグイン関連の設定

OpenSearchのSQLプラグインには、いくつかの設定項目があります。これらの設定はほとんどが動的設定なので、クラスタを再起動することなく変更できます。

設定の変更方法

設定は通常のクラスタ設定と同様に変更できます。

PUT _cluster/settings
{
  "transient" : {
    "plugins.sql.enabled" : false
  }
}

または、_plugins/_query/settingsエンドポイントを使用することもできます。

PUT _plugins/_query/settings
{
  "transient" : {
    "plugins.sql.enabled" : false
  }
}
利用可能な設定項目
設定項目 デフォルト値 説明
plugins.sql.enabled true SQLのサポートを無効にする場合はfalseに設定します。
plugins.sql.slowlog 2秒 スロークエリの時間制限を秒単位で設定します。この時間を超えたクエリはopensearch.logに記録されます。
plugins.sql.cursor.keep_alive 1分 カーソルコンテキストを保持する時間を設定します。カーソルコンテキストはリソースを消費するため、低い値を推奨します。
plugins.query.memory_limit 85% クエリエンジンのサーキットブレーカーが使用するヒープメモリの上限を設定します。
plugins.query.size_limit 200 クエリエンジンがOpenSearchから取得するindexのデフォルトサイズを設定します。
plugins.query.field_type_tolerance true falseの場合、配列は任意のネストレベルで最初の非配列値に縮小されます。例えば[[1, 2], [3, 4]]は1になります。trueの場合は配列が保持されます。

便利なテクニック

クエリ結果のページネーション

fetch_size(デフォルト 1,000)を指定することで、ページ分割されたレスポンスが得られます。
レスポンスには、後続ページの結果を取得するために使用されるcursorフィールドが含まれます。

POST _plugins/_sql
{
  "query" : "SELECT * FROM accounts",
  "fetch_size" : 2
}

クエリ実行結果 

{
  "schema": [
    {
      "name": "account_number",
      "type": "long"
    },
    {
      "name": "firstname",
      "type": "text"
    },
    {
      "name": "address",
      "type": "text"
    },
    {
      "name": "balance",
      "type": "long"
    },
    {
      "name": "gender",
      "type": "text"
    },
    {
      "name": "city",
      "type": "text"
    },
    {
      "name": "employer",
      "type": "text"
    },
    {
      "name": "state",
      "type": "text"
    },
    {
      "name": "age",
      "type": "long"
    },
    {
      "name": "email",
      "type": "text"
    },
    {
      "name": "lastname",
      "type": "text"
    }
  ],
  "datarows": [
    [
      1,
      "Amber",
      "880 Holmes Lane",
      39225,
      "M",
      "Brogan",
      "Pyrami",
      "IL",
      32,
      "amberduke@pyrami.com",
      "Duke"
    ],
    [
      6,
      "Hattie",
      "671 Bristol Street",
      5686,
      "M",
      "Dante",
      "Netagy",
      "TN",
      36,
      "hattiebond@netagy.com",
      "Bond"
    ]
  ],
  "total": 2,
  "size": 2,
  "status": 200,
  "cursor": "n:1f8b08000000000000ffcd564b6c1b45181e3ff228a9dad43c848a0a6209a09674ed9092aa4e5a290f3b8f3a76633b4e70a8acf1eec4d9645f991dd7eb44c905817a424848881b4870402ab921e0803870424820d40312074e1c2a0e882b4208f1cff8b1aeb32dc8272cf99fd999f9ffef9bffb5fbc96fa8cfa168dca215d9b289e9104c952dd9d9d5655bc7a649a86c6fd51d4dc1ba7c835adb4461199b50cc2c7a2f39f5c5e5673f4d9c44c8b5c1c655670bab449515cb902b9655d1099f1a960983ae83a2bc68185586cb3a49690e1bc911aa615ddb236ad2a2066afe8228b08106894e0c623287a1473752dbf8168e02994a3453e604265dbb4a5b1b32df909b1b6f7fbffefeb0735e0f724a606c08685df4b91a716d4a1c47036e696c1035d17efee3cdc8bb7f1fbd371d44a114ea037a1838443a28e418d5ccca640a9d5081640533a23274210520510f240a20510f24ead907c5b00990bbe8100dba36f7dbd8c30966c926a1c4548867e4cedcefaf4506def83188c2600e33468539206c63b60584871b84ab4cd3a3dcd7803a4071ed06ec364f8659dd260cbde8475cc50c47f9bee09d8709789ca1535851acaac94a66d528130acccf8a007014799a525c7746c4c0117ffa68eaabef6ef7df15e10c608e1a3a1eb6a6338fee16eefd7a6e7fbe15b6003f7e5a88438acefbf8875394394599539cb528e1345b3914013b149df2701240b973136e134e65d2f302c2e172c016c31043273635ea301e23b1325ce532d262755608b87bdc8755c7a34710cac5cc89c53c7119a779f4fa87ef847f199b03dfa450ffa64674156276ba2366cbd89e747bc29883358ef1b5f2e5b7c13ffb5e11797c92743889a197fe3de8adc390384306b66d88524377c14fb7e3d133739cd3c8b267090c3f625350a44c6b38fa69d73ee4e3630c0daca6afa7336b69087eb2771774c275069f833c0e09c0c301819cfaef6d0b0c3eb06b05a1a67648bdab5b34fb1284f916d6ab8d8b9e873ae0631f57f46640e58c574fb3800d506b6fddf92cfed4c7674228b088420c57447d846a618e098e02c49a455561f79996fbfa73f9ec627a5e3c3e0f87ae370ef1c70b6e237fef4ff901acaabcb5f825fc6521c4ce392e9ee3e205614b2c5ef4f8f0c7281731d753ea402963f089e25b56d7dae20931eb52edaf10538596e3a3392b442ffc66bb40c28ac6ea7e104b42f402b1d40531480c5bb7eafe375911a21798952e983e87c17bc90f635d885e30d6bb3042b8e28b506a0b11c952373562604df75314f9a9f6424dedf6b28e1fdcc07784e80566072af4dac3db91039f45e017d951b0d9d191162179dd1cac2dee27fffaf9f69311f1c9b407103fc0ff9bcf43fbd226b50c291e1b951c682d52fce551896906b1aa4c8a4b6386342a951cab4a15d8da973453d1ab2a71a4f88674ff3b19ceb5df5e306f9635cc9aa507b34625c184673b0cad8c84a9c81aae56216203020563cb99d24d5873dbc8370f40c1a2c06f635f2aa996c2994197014b71093b8a7470300a1b9a7a7c1d0c35fc55c29b8caf6f8c8d4a13dcbead31714150926a13bb975656128bafaea5b7cbe385aabab0bc5634b76a64ae58534c3b5b4c5c195b8db9f5fc7631969fcf265756a6d7527935abae27278aebc95c3676a54676ec44a630b397d593f3c5da74fb97ca1693e57c7102eb4b99b29e5dc8c50a86924cae9663594dd5d3e3d3d9a24116662ea5cd993ad1933bf9f1423ebfb094499bd9e562a2300326aec24582e10f02ff779a435d9f6ddee74dabebb7fa72b3c98a36d86e548d56c28bbd51b9edda0a0c36ed3ac100af2d1498e035e3baff0082705e16520c0000"
}

後続のページを取得するには、前のレスポンスのcursorを使用します。

POST /_plugins/_sql 
{
   "cursor": "<cursorの値>"
}

クエリ実行結果 

{
  "schema": [
    {
      "name": "account_number",
      "type": "long"
    },
    {
      "name": "firstname",
      "type": "text"
    },
    {
      "name": "address",
      "type": "text"
    },
    {
      "name": "balance",
      "type": "long"
    },
    {
      "name": "gender",
      "type": "text"
    },
    {
      "name": "city",
      "type": "text"
    },
    {
      "name": "employer",
      "type": "text"
    },
    {
      "name": "state",
      "type": "text"
    },
    {
      "name": "age",
      "type": "long"
    },
    {
      "name": "email",
      "type": "text"
    },
    {
      "name": "lastname",
      "type": "text"
    }
  ],
  "datarows": [
    [
      13,
      "Nanette",
      "789 Madison Street",
      32838,
      "F",
      "Nogal",
      "Quility",
      "VA",
      28,
      "nanettebates@quility.com",
      "Bates"
    ],
    [
      18,
      "Dale",
      "467 Hutchinson Court",
      4180,
      "M",
      "Orick",
      null,
      "MD",
      33,
      "daleadams@boink.com",
      "Adams"
    ]
  ],
  "total": 2,
  "size": 2,
  "status": 200,
  "cursor": "n:1f8b08000000000000ffcd564b6c1b45181e3ff228a9dad43c848a0a6209a09474ed9092b64e5a290f3b8f3a76633b0f1c2a6bbc3b7136d95766c7f53a517a41a09e101212e20652392095dc10ea0171e058090e392071e0c4a1e280b8228410ff8c1feb3adb827cc292ff999d99ffffbef95fbb5ffe867a1c8ac62c5a962d9b980ec154d9929d5d5db6756c9a84caf656cdd114accb37a8b54d1496b109c5cca20f9393f72fbdfc55e22442ae0d36ae3a5b5825aaac58865cb6acb24ef8d4b04c18741d14e505c3a8305cd2494a73d8508e500debda1e5193163550e31744810dd44f74621093390c3dbd91dac6b77014c894a399122730e1da15dadc90f986dcd8f8f087f54f079d613dc82981b101a075c1e76ac4b529711c0db8a5b141d444ebf98ff7231fff7df8c954108552a807e861e01069a390635433cb13297442059265cc88cad0f91480443d902880443d90a8671f14c32640eea2dba8dfb5b9df469f4c304b360925a6423c23f7667f7f27d2f7de8f4114067398312acc01611bb32d203c58275c619a1ee5be06d43e8aab3760b77132cc6a3661e8753fe22a6638caf705ef3c4cc0e30c9dc28a62554c56342b468950607e560480a3c85394e29a3324068ef8d3e793df7e7fa7f748843380396ae878d81ace3c3c5a7df8ebb9fdb966d802fcf869216e5334ece31f4e51e614654e71c6a284d36ce65004ec5074cac34900e5f64db84d389549cf090887cb3e5b0c030c9dd8d4a8c3788cc4ca6085cb4893d55921e0ee711f566d8f1e412817332716f3c4659ce6e1bb773f0aff323a0bbe49a1de4d8de82ac4ec745bcc96b03de17685310b6b1ce33be59b07c13f7bde12797c92b43989a137fe3de8cdc390380306b66d88525d77de4fb7edd133739cd3d09267090c3f655350a44cab3bfa45d7becdc76718ea5b495f4f67d6d210fc64f72e68876b0f3e077916128087030239f9dfdb16187c6cd70a424ded905a47b768f42508f32dac57ea171d863ae0630f57f46640e58c574f33800d506b1fdcfb3afec217674228b080420c97457d84aa618e098e02c4aa455561f7a5a6fb7a73f9ec427a4e3cbe0a87aed70ff1c7f36e3d7f1f4df93eacaabcb5f825fc2521c4ce392e5ee1e235614b2c5ef0f8f0c7281731d7536a432961f089e25b56d75ae23931eb50ed2d13538596e3a339234437fc663a40c28ac66a7e108b427403b1d801d14f0c5bb76afe375916a21b98e50e981e87c17bc90f635d886e30d63b3042b8ec8b506c0911c962273562604df75314f9a976434dedf4b28e1fdfc07784e80666072af4da93db91039f45e017d951b0d9d691162079dd1cac2dec27fffaf9cef311f1c9b4071047f07f703fb42f6d52cb90e2b111c981d622c5df1c91986610abc2a4b8346a482352d1b12a5481ad7d493315bda212478a6f488fbe93e15cebed05f34659c3ac517a30ab57124c78b6c3d0cc48988aace16a652236205030369d29dd8435b7857cf300142c0afc36f6a5a26a299c197419b01497b0a348070723b0a1a9c7d7c150dd5f45bcc9f8fac6d888347a9903d81a1337042da93abe7b717939b1f0f65a7abb34b65a51e797d60ae65695cc16aa8a69670b892ba32b31b796df2ec4f273d9e4f2f2d45a2aaf66d5f5e478613d99cbc6ae54c98e9dc8ac4eef65f5e45ca13ad5faa5b60ac952be308ef5c54c49cfcee762ab86924cae9462594dd5d36353d98241e6a72fa6cde91ad1933bf9b1d57c7e7e319336b34b85c4ea3498b80a3709863f0bfcdf690e747cb779df37cdb6df6ccc8d2e2bfa60ab53d57b09aff67ae9b68a2bd0dfb0eb0403bc665070f432af1ad7fd077f2d9b57540c0000"
}

結果の最後のページにはcursorは含まれません。
cursor contextは最後のページを取得した際に自動的にクリアされます。

カーソルコンテキストを明示的にクリアするには、_plugins/_sql/closeを使用します。
fetch_sizeパラメータはjdbcレスポンス形式でのみサポートされています。

DSLを併用したクエリ結果のフィルタリング

filterパラメータによって、SQLのクエリ結果をDSLによってフィルタリングできます。

POST /_plugins/_sql?format=csv
{
  "query" : "SELECT firstname, age FROM accounts",
  "filter" : {
    "range" : {
      "age" : {
        "lt" : 40
      }
    }
  }
}

クエリ結果

firstname,age
Amber,32
Hattie,36
Nanette,28
Dale,33
クエリパラメーターの使用

parametersフィールドを使用することで、SQLに変数を埋め込むことができます。

POST /_plugins/_sql?format=csv
{
  "query": "SELECT * FROM accounts WHERE age < ?",
  "parameters": [{
    "type": "integer",
    "value": 40
  }]
}

クエリ結果

account_number,firstname,address,balance,gender,city,employer,state,age,email,lastname
1,Amber,880 Holmes Lane,39225,M,Brogan,Pyrami,IL,32,amberduke@pyrami.com,Duke
6,Hattie,671 Bristol Street,5686,M,Dante,Netagy,TN,36,hattiebond@netagy.com,Bond
13,Nanette,789 Madison Street,32838,F,Nogal,Quility,VA,28,nanettebates@quility.com,Bates
18,Dale,467 Hutchinson Court,4180,M,Orick,,MD,33,daleadams@boink.com,Adams
Explainによるデバッグ

_plugins/_sql/_explainはクエリがどのように解釈され、OpenSearchで実行されたかを示す、OpenSearchドメイン固有言語(DSL)をJSON形式で返します。
これらの情報はパフォーマンスチューニングなどに役立ちます。

POST _plugins/_sql/_explain
{
  "query" : "SELECT * FROM accounts"
}

クエリ実行結果 

{
  "root": {
    "name": "ProjectOperator",
    "description": {
      "fields": "[account_number, firstname, address, balance, gender, city, employer, state, age, email, lastname]"
    },
    "children": [
      {
        "name": "OpenSearchIndexScan",
        "description": {
          "request": """OpenSearchQueryRequest(indexName=accounts, sourceBuilder={"from":0,"size":10000,"timeout":"1m","_source":{"includes":["account_number","firstname","address","balance","gender","city","employer","state","age","email","lastname"],"excludes":[]}}, needClean=true, searchDone=false, pitId=w6q4QQEIYWNjb3VudHMWZnhweDZwcnpRZE91U0xyTjZ0TGRFQQAWLTdRdXF6ZXFSR09wekpEOVBzRlFGZwAAAAAAAAAAKRZFbTZ6alJOblRHS0VmcFFUb0RidlN3ARZmeHB4NnByelFkT3VTTHJONnRMZEVBAAA=, cursorKeepAlive=1m, searchAfter=null, searchResponse=null)"""
        },
        "children": []
      }
    ]
  }
}

メタデータの確認

indexやフィールドの情報を確認するには、SHOWとDESCRIBEコマンドを使用します。

indexのメタデータ確認

SHOW TABLESコマンドで、存在するindexの一覧を確認できます。

例1: 全てのindexを確認

POST _plugins/_sql?format=raw
{
  "query" : "SHOW TABLES LIKE %"
}

ワイルドカード%を使用することで、全てのindexが表示されます。

opensearch-cluster|NULL|.kibana_1|BASE TABLE|NULL|NULL|NULL|NULL|NULL|NULL
opensearch-cluster|NULL|.kibana_92668751_admin_1|BASE TABLE|NULL|NULL|NULL|NULL|NULL|NULL
opensearch-cluster|NULL|.opendistro-reports-definitions|BASE TABLE|NULL|NULL|NULL|NULL|NULL|NULL
opensearch-cluster|NULL|.opendistro-reports-instances|BASE TABLE|NULL|NULL|NULL|NULL|NULL|NULL
opensearch-cluster|NULL|.opendistro_security|BASE TABLE|NULL|NULL|NULL|NULL|NULL|NULL
opensearch-cluster|NULL|.plugins-ml-config|BASE TABLE|NULL|NULL|NULL|NULL|NULL|NULL
opensearch-cluster|NULL|.ql-datasources|BASE TABLE|NULL|NULL|NULL|NULL|NULL|NULL
opensearch-cluster|NULL|accounts|BASE TABLE|NULL|NULL|NULL|NULL|NULL|NULL
opensearch-cluster|NULL|employees_nested|BASE TABLE|NULL|NULL|NULL|NULL|NULL|NULL
opensearch-cluster|NULL|employees_projects|BASE TABLE|NULL|NULL|NULL|NULL|NULL|NULL
opensearch-cluster|NULL|security-auditlog-2025.10.04|BASE TABLE|NULL|NULL|NULL|NULL|NULL|NULL
opensearch-cluster|NULL|top_queries-2025.10.04-55782|BASE TABLE|NULL|NULL|NULL|NULL|NULL|NULL

例2: パターンマッチでindexを絞り込み

POST _plugins/_sql?format=raw
{
  "query" : "SHOW TABLES LIKE acc%"
}

accで始まるindexのみが表示されます。

opensearch-cluster|NULL|accounts|BASE TABLE|NULL|NULL|NULL|NULL|NULL|NULL
フィールドのメタデータ確認

DESCRIBE TABLESコマンドで、特定のindexが持つフィールドの情報を確認できます。

例3: indexのフィールド一覧を確認

POST _plugins/_sql?format=csv
{
  "query" : "DESCRIBE TABLES LIKE accounts"
}

accountsindexが持つ全てのフィールドとそのデータ型が表示されます。

TABLE_CAT,TABLE_SCHEM,TABLE_NAME,COLUMN_NAME,DATA_TYPE,TYPE_NAME,...
docker-cluster,null,accounts,account_number,null,long,...
docker-cluster,null,accounts,firstname,null,text,...
docker-cluster,null,accounts,address,null,text,...
docker-cluster,null,accounts,balance,null,long,...
docker-cluster,null,accounts,gender,null,text,...
docker-cluster,null,accounts,city,null,text,...
docker-cluster,null,accounts,employer,null,text,...
docker-cluster,null,accounts,state,null,text,...
docker-cluster,null,accounts,age,null,long,...
docker-cluster,null,accounts,email,null,text,...
docker-cluster,null,accounts,lastname,null,text,...

制約事項

OpenSearchのSQLプラグインには、いくつかの制約があります。クエリを作成する際には以下の点に注意してください。

集計関数の制約

集計関数は、フィールドに対してのみ適用できます。式を引数として渡すことはできません。例えば、avg(log(age))のような記述はサポートされていません。集計を行う場合は、まずフィールドの値を取得してから計算を行う必要があります。

JOINの制約

OpenSearchはリレーショナルデータベースではないため、JOIN操作にはいくつかの制約があります。結合できるのは2つのindexのみで、結合結果に対して集計関数を使用することはできません。例えば、SELECT depo.name, avg(empo.age) FROM empo JOIN depo WHERE empo.id = depo.id GROUP BY depo.nameのようなクエリはサポートされていません。

JOINクエリは、500万件を超える結果セットを扱う場合にパフォーマンスの問題が発生する可能性があります。パフォーマンスを改善するには、WHERE句で事前にデータを絞り込むことを推奨します。また、JOINクエリはデフォルトで60秒後に自動的に終了します。タイムアウト時間を変更するには、クエリにヒントを使用できます。

SELECT /*! JOIN_TIME_OUT(300) */ left.a, right.b 
FROM left JOIN right ON left.id = right.id
サブクエリの制約

FROM句でのサブクエリは、1つのクエリにマージできる場合のみサポートされます。例えば、以下のようなクエリは実行できます。

SELECT t.f, t.d
FROM (
    SELECT FlightNum as f, DestCountry as d
    FROM opensearch_dashboards_sample_data_flights
    WHERE OriginCountry = 'US'
) t

ただし、外側のクエリにGROUP BY句やORDER BY句が含まれる場合はサポートされません。

ページネーションの制約

ページネーション機能は基本的なクエリのみでサポートされています。集計やJOINを含むクエリではページネーションを使用できません。

POST _plugins/_sql/
{
  "fetch_size" : 5,
  "query" : "SELECT OriginCountry, DestCountry FROM opensearch_dashboards_sample_data_flights ORDER BY OriginCountry ASC"
}
その他の制約

SELECT句でリテラル式のみを使用する場合（FROM句なし）は、サポートされていません。例えば、SELECT 1のようなクエリは実行できません。