Elasticsearch SQLの活用事例 - パート2
Elasticsearch SQLの活用事例シリーズのパート1では、Elasticsearch SQLの新機能と_translate APIについて簡単に説明しました。今回は、より複雑な機能を取り上げます。さらにElasticsearch SQLの最初のバージョンで、ユーザーに制約となる可能性がある挙動と、今後のリリースでアップデートが予定される点についても説明します。
記事中の事例はdemo.elastic.coで見ることができるほか、Kibanaで使えるデータセットも配信する予定です。この連載で使用されるデータセットはすべてインデックスされており、SQLはKibanaコンソールで扱えるようになっています。各事例に個別のdemo.elastic.coリンクがついていますが、デモ用のKibanaコンソールですべてのクエリを1つのスレッドとして表示させることもできます。
複雑な例とElasticsearchの強み
グルーピング
Elasticsearchのアグリゲーションフレームワークは数十億のデータポイントを要約することもでき、Elastic Stackで最もパワフルな、よく使われる機能の1つです。機能性という観点で、ElasticsearchのアグリゲーションはSQLのGROUP BY演算子に似ています。ここではGROUP BY機能の具体例を参照しつつ、translate APIが行うGROUP BYからアグリゲーションへの"翻訳"も確認してみましょう。
「離陸国別に、ロンドン行きのフライトの平均飛行時間を求めよ。国名は、アルファベット順で表示する」
sql> SELECT AVG(FlightTimeHour) Avg_Flight_Time, OriginCountry FROM flights GROUP BY OriginCountry ORDER BY OriginCountry LIMIT 5; Avg_Flight_Time | OriginCountry ------------------+--------------- 9.342180244924574 |AE 13.49582274385201 |AR 4.704097126921018 |AT 15.081367354940724|AU 7.998943401875511 |CA
このクエリをDSLで記述すると、コンポジットアグリゲーションを使用することがわかります。
{
"size":0,
"_source": false,
"stored_fields": "_none_",
"aggregations": {
"groupby": {
"composite": {
"size":1000,
"sources": [
{
"3471": {
"terms": {
"field":"OriginCountry.keyword",
"order": "asc"
}
}
}
]
},
"aggregations": {
"3485": {
"avg": {
"field":"FlightTimeHour"
}
}
}
}
}
}
また、関数を使用してSELECTにあるGROUP BYエイリアスフィールドを定義することもできます。
「月別に、フライトの回数と平均飛行時間を求めよ」
POST _xpack/sql
{
"query":"SELECT COUNT(*), MONTH_OF_YEAR(timestamp) AS month_of_year, AVG(FlightTimeHour) AS Avg_Flight_Time FROM flights GROUP BY month_of_year"
}
COUNT(1) | month_of_year | Avg_Flight_Time ---------------+---------------+----------------- 1303 |5 |8.628949653846158 8893 |6 |8.520481551839334 2863 |7 |8.463433805045094
コンポジットアグリゲーションにはメリットがあります。ドキュメントをスクロールするように、特定のアグリゲーションのすべてのバケットにストリームするメカニズムによって、カーディナリティの高いフィールドに対してもGROUP BYを確実に、スケーラブルに実装できます。さらに、タームスアグリゲーションを使った実装のようにメモリ容量が不足することもありません。一方でこれは、現在のところ、メトリックをGROUP BYで並べ替えることはできないということでもあります。たとえば次のように記述すると、エラーとなります。
POST _xpack/sql/translate
{
"query":"SELECT AVG(FlightTimeHour) Avg_Flight_Time, OriginCountry FROM flights GROUP BY OriginCountry ORDER BY Avg_Flight_Time"
}
グループをフィルタリングする
グループをフィルタリングする際にHAVING演算子を使いますが、SELECT句で指定されたエイリアスを使うこともできます。SQLに習熟した人にはよくわかると思いますが、RDBMSベースの実装でこれはできません。HAVINGの後に実行されたSELECTがあるためです。ここで、HAVING句は実行段階で宣言されたエイリアスを使用しています。Elasticsearch SQLのアナライザーは優れており、先まで見てHAVINGで使用する宣言を選ぶことができます。
「都市間の平均飛行距離が3,000マイル超、4,000マイル未満に位置するフライトについて、離陸都市からのフライト数と、平均飛行距離、95パーセンタイルの飛行距離を求めよ」
sql> SELECT OriginCityName, ROUND(AVG(DistanceKilometers)) avg_distance, COUNT(*) c, ROUND(PERCENTILE(DistanceKilometers,95)) AS percentile_distance FROM flights GROUP BY OriginCityName HAVING avg_distance BETWEEN 3000 AND 4000; OriginCityName | avg_distance | c |percentile_distance ---------------+---------------+---------------+------------------- Verona |3078 |120 |7927 Vienna |3596 |120 |7436 Xi'an |3842 |114 |7964
Elasticsearch SQLはバケットセレクターパイプラインアグリゲーションを活用してHAVING機能を実装し、パラメータ化されたPainlessスクリプトで値をフィルタリングしています。以下の実例で、OriginCityNameフィールドのkeyword変数が標準的なテキスト変数ではなく、アグリゲーション用に自動的に選択されている点に注目してください。標準的なテキスト変数の場合、フィールドデータが有効化されていないため、うまくいきません。avgメトリックアグリゲーションとpercentileメトリックアグリゲーションはSQL変数と同等の関数です。
{
"size":0,
"_source": false,
"stored_fields": "_none_",
"aggregations": {
"groupby": {
"composite": {
"size":1000,
"sources": [
{
"4992": {
"terms": {
"field":"OriginCityName.keyword",
"order": "asc"
}
}
}
]
},
"aggregations": {
"4947": {
"bucket_selector": {
"buckets_path": {
"a0":"5010",
"a1":"5010"
},
"script": {
"source": "params.v0 <= params.a0 && params.a1 <= params.v1",
"lang": "painless",
"params": {
"v0":3000,
"v1":4000
}
},
"gap_policy": "skip"
}
},
"5010": {
"avg": {
"field":"DistanceKilometers"
}
},
"5019": {
"percentiles": {
"field":"DistanceKilometers",
"percents": [
95
],
"keyed": true,
"tdigest": {
"compression":100
}
}
}
}
}
}
}
文字列演算子と関連性
検索エンジンとしてElasticsearchが独自に備える機能に、マッチングのスコア機能があります。従来型のRDBMSがシンプルなyes/no型のマッチであるのに対し、Elasticsearchは文字列データのプロパティから関連性を計算してスコアを付けます。SQL構文を拡張してこの機能を活用すると、従来のRDBMSに比べて優れた結果を取得することができます。
そこで今回2つの演算子、QUERYとMATCHが新たに導入されました。Elasticsearchに精通している方は、multi_match演算子とquery_string演算子に相当する、という説明で理解できると思います。Kibanaユーザーも、デフォルトの検索バーにquery_string演算子が使われているので、すぐ慣れるはずです。この演算子にはインテリジェントなパース機能があり、自然言語スタイルのクエリを実行できます。本記事では2つの演算子について詳細な説明は省きますが、各コンセプトについてはこちらのエントリにわかりやすい説明があります。
ここで、下の設問を考えてみましょう。
「2018年6月6日から2018年6月17日の間にコペンハーゲン空港を発着したフライトのうち、遅延のあったすべてのフライトを日付順に表示せよ」
コペンハーゲン空港は実在の空港で、正式名称は"Copenhagen Kastrup Airport"です。ここではQUERY演算子を使い、シンプルに"Kastrup"で検索します。
sql> SELECT timestamp, FlightNum, OriginCityName, DestCityName FROM flights WHERE QUERY('Kastrup') AND FlightDelay=true AND timestamp > '2018-06-20' AND timestamp < '2018-06-27' ORDER BY timestamp;
timestamp | FlightNum |OriginCityName | DestCityName
---------------+---------------+---------------+-------------------
2018-06-21T01:46:28.000Z|57SWSLT |Copenhagen |Orlando
2018-06-21T07:28:07.000Z|X43J6GE |Abu Dhabi |Copenhagen
2018-06-21T13:36:31.000Z|7T04SK7 |Copenhagen |Milan
2018-06-22T19:52:15.000Z|NXMN87D |Mumbai |Copenhagen
2018-06-23T08:05:02.000Z|YXHMDKV |Copenhagen |Oslo
2018-06-25T18:21:04.000Z|2R86JEZ |Copenhagen |Shanghai
2018-06-26T22:16:10.000Z|TCE99LO |Copenhagen |Edmonton
フィールドの指定要件がないことがポイントです。QUERY演算子を使い、シンプルに"Kastrup"で検索するだけでOKです。さらに、コペンハーゲン空港に着陸した便と離陸した便の両方が返されていることにも注目しましょう。Elasticsearchでは、次のようなクエリになります。
{
"size":1000,
"query": {
"bool": {
"filter": [
{
"bool": {
"filter": [
{
"bool": {
"filter": [
{
"query_string": {
"query":"Kastrup",
"fields": [],
"type": "best_fields",
"default_operator": "or",
"max_determinized_states":10000,
"enable_position_increments": true,
"fuzziness":"AUTO",
"fuzzy_prefix_length":0,
"fuzzy_max_expansions":50,
"phrase_slop":0,
"escape": false,
"auto_generate_synonyms_phrase_query": true,
"fuzzy_transpositions": true,
"boost":1
}
},
{
"term": {
"FlightDelay": {
"value": true,
"boost":1
}
}
}
],
"adjust_pure_negative": true,
"boost":1
}
},
{
"range": {
"timestamp": {
"from":"2018-06-20",
"to": null,
"include_lower": false,
"include_upper": false,
"boost":1
}
}
}
],
"adjust_pure_negative": true,
"boost":1
}
},
{
"range": {
"timestamp": {
"from": null,
"to":"2018-06-27",
"include_lower": false,
"include_upper": false,
"boost":1
}
}
}
],
"adjust_pure_negative": true,
"boost":1
}
},
"_source": {
"includes": [
"FlightNum",
"OriginCityName",
"DestCityName"
],
"excludes": []
},
"docvalue_fields": [
"timestamp"
],
"sort": [
{
"timestamp": {
"order": "asc"
}
}
]
}
Elasticsearchの初心者にとっては、なかなか複雑なクエリです。Bool Queryが、Nested Range、Term制限、クエリストリング演算子と共に使われています。SQLからアプリケーションを移行するユーザーにしてみれば、このクエリが正しく記述できているか、あるいは最適な記述か、とかいう以前にもうくじけそう...という雰囲気のタスクになっています。設問は日付順に並べるというもので、関連性が問われないため、query_string演算子は1つのフィルター内にnestされています。これによりフィルタキャッシュを利用でき、スコアリングを回避してレスポンスタイムを短縮しています。
これらの演算子のパラメーターは、SQLでもエクスポーズすることができます。最後の事例では、複数のフィールドで複数の語句を検索する場合に、MATCHクエリを使って結果の範囲を指定する方法を説明してみたいと思います。
「バルセロナを発着したフライトのうち、天候に雷が含まれたものを見つける」
また方法を紹介する目的で、Score()関数を使って関連性スコアの表示と並べ替えを行いました。
sql> SELECT Score(), timestamp, FlightNum, OriginCityName, DestCityName, DestWeather, OriginWeather FROM flights WHERE MATCH('*Weather,*City*', 'Lightning Barcelona', 'type=cross_fields;operator=AND') ORDER BY Score() DESC LIMIT 5;
SCORE() | timestamp | FlightNum |OriginCityName | DestCityName | DestWeather | OriginWeather
---------------+------------------------+---------------+---------------+---------------+-------------------+-------------------
6.990964 |2018-05-31T06:00:41.000Z|L637ISB |Barcelona |Santiago |Rain |Thunder & Lightning
6.990964 |2018-06-13T12:23:44.000Z|0GIHB62 |Barcelona |Buenos Aires |Clear |Thunder & Lightning
6.9796515 |2018-06-14T21:04:51.000Z|7GEMEDR |Barcelona |Hyderabad |Thunder & Lightning|Rain
6.9133706 |2018-05-31T01:58:51.000Z|ZTOD7RQ |Barcelona |Dubai |Sunny |Thunder & Lightning
6.9095163 |2018-06-06T14:02:34.000Z|QSQA5CT |Barcelona |Naples |Rain |Thunder & Lightning
ワイルドカードパターンを使って一致させるフィールドを指定し、ブール型AND条件で一致するようにしています。クロスフィールドパラメーターは、検索語句の全てが1つのフィールドに存在することを求めておらず、異なるフィールドにあっても結果に含めることができます。このデータの構造を考えると、この設定が不可欠です。
上の事例では、行vsグループで結果が返されました。QUERY演算子とMATCH演算子は、GROUP BYと共に使用することも可能です。ElasticsearchでGROUP BYはアグリゲーションのフィルタリングに効果的です。
クロスインデックス検索とエイリアス
ここまで、1つのテーブルまたはインデックスを対象にしたクエリについて見てきました。仮にドキュメントをコピーし、再インデックスして新しい名前を付けるというやり方でインデックスを複製するとします。双方のインデックスのマッピングが同じであれば、2つのインデックスを同じ時間でクエリできるはずです。マッピングに相違があれば、クエリは解析の時点でエラーになります。複数のインデックスを同時にクエリするためには、Elasticsearchエイリアスを追加するか、WHERE句の中でワイルドカードを使用する必要があります。たとえば、“flights”と“flights-2”という2つのインデックスがあり、“f_alias”エイリアスを使うとします。
POST /_aliases
{
"actions" : [
{ "add" : { "index" : "flights-2", "alias" : "f_alias" } },
{ "add" : { "index" : "flights", "alias" : "f_alias" } }
]
}
次のクエリは、論理的には同一です。
sql> SELECT FlightNum, OriginCityName, DestCityName, DestWeather, OriginWeather FROM flights* ORDER BY timestamp DESC LIMIT 1; FlightNum |OriginCityName | DestCityName | DestWeather | OriginWeather ---------------+---------------+---------------+---------------+--------------- 6UPDDGK |Zurich |Zurich |Rain |Hail sql> SELECT FlightNum, OriginCityName, DestCityName, DestWeather, OriginWeather FROM f_alias ORDER BY timestamp DESC LIMIT 1; FlightNum |OriginCityName | DestCityName | DestWeather | OriginWeather ---------------+---------------+---------------+---------------+--------------- 6UPDDGK |Zurich |Zurich |Rain |Hail
demo.elastic.coで見る デモ環境には、テスト用に2つのインデックスと設定済みのエイリアスが用意されています。
上記の要件は将来的に緩和される可能性がありますが、初回のリリースでは、この仕様によってロジックをシンプルにしています。
JOIN
従来のRDBMS SQLでJOINを使うと、関連する列に基づいて別のテーブルの行を連結させ、1つのテーブルのように見せることができます。この機能を使うと、データのリレーショナルモデリングができます。Elasticsearchでネイティブに使えるオプションと比べ、大きく違う点でもあります。現在Elasticsearch SQLではJOIN演算子を提供していませんが、nestされたドキュメントを使用して1対多数のシンプルなリレーショナルモデリングを行うことはできます。nestされたドキュメントのクエリは、ユーザーから見える形で処理されます。この機能をデモンストレーションするには、そうしたデータのインデックスが必要です。事例用に、"orders"というインデックスをdemo.elastic.coにあげています。インデックス元のドキュメントはeコマースサイトの注文に関するもので、order_date、billing_city、customer_last_nameなどのフィールドがあります。また、"products"フィールドには注文に含まれる製品ごとのサブドキュメントがnestされています。たとえば次の通りです。
{
"billing_last_name":"Green",
"billing_first_name":"Jason",
"order_id":519894,
"products": [
{
"tax_amount":0,
"taxful_price":22.99,
"quantity":1,
"taxless_price":22.99,
"discount_amount":0,
"base_unit_price":22.99,
"discount_percentage":0,
"product_name":"Briefcase - black",
"manufacturer":"Pier One",
"min_price":11.27,
"created_on":"2016-11-08T04:16:19+00:00",
"unit_discount_amount":0,
"price":22.99,
"product_id":12733,
"base_price":22.99,
"_id": "sold_product_519894_12733",
"category":"Men's Accessories",
"sku":"PI952HA0M-Q11"
},
{
"tax_amount":0,
"taxful_price":16.99,
"quantity":1,
"taxless_price":16.99,
"discount_amount":0,
"base_unit_price":16.99,
"discount_percentage":0,
"product_name":"3 PACK - Boxer shorts - white/navy",
"manufacturer":"Pier One",
"min_price":8.33,
"created_on":"2016-11-08T04:16:19+00:00",
"unit_discount_amount":0,
"price":16.99,
"product_id":18370,
"base_price":16.99,
"_id": "sold_product_519894_18370",
"category":"Men's Clothing",
"sku":"PI982AA0Y-A11"
}
],
"has_error": false,
"customer_last_name":"Green",
"currency":"EUR",
"billing_first_name":"Jason",
"shipping_country_code":"US",
"email": "swagelastic@gmail.com",
"day_of_week":"Tuesday",
"geoip": {
"continent_name":"North America",
"city_name":"New York",
"country_iso_code":"US",
"location": {
"lon": -73.9862,
"lat":40.7662
},
"region_name":"New York"
},
"payment_status":"FULLY_PAID",
...
}
通常、このようなドキュメントをクエリするには、ユーザーが"products"フィールドでnestされたデータタイプが使われる理由を理解していること、さらにnestされたクエリ構文の知識も必要です。ところが、Elasticsearch SQLの場合はnestされたドキュメントの1つ1つがペアレントフィールドの個別の行であるかのように扱うことができます。構造をフラットに表現することができる、と言ってもいいでしょう。上の注文で、2つの製品が含まれている例を考えます。製品のサブドキュメントからフィールドをリクエストする場合、クエリすると2行で表示されます。リクエストすれば、各行にさらにペアレントオーダーのフィールドを含めることもできます。たとえば次の通りです。
「注文番号518894の請求者の名前と、購入製品を見つける」
sql> SELECT billing_last_name, billing_first_name, products.price, products.product_id FROM orders WHERE order_id=519894; billing_last_name|billing_first_name|products.price |products.product_id -----------------+------------------+---------------+------------------- Green |Jason |16.984375 |18370 Green |Jason |22.984375 |12733
_translate APIを使うと、nestされたクエリで表現されています。
{
"size":1000,
"query": {
"bool": {
"filter": [
{
"term": {
"order_id": {
"value":519894,
"boost":1
}
}
},
{
"nested": {
"query": {
"match_all": {
"boost":1
}
},
"path": "products",
"ignore_unmapped": false,
"score_mode": "none",
"boost":1,
"inner_hits": {
"ignore_unmapped": false,
"from":0,
"size":99,
"version": false,
"explain": false,
"track_scores": false,
"_source": false,
"stored_fields": "_none_",
"docvalue_fields": [
"products.product_id",
"products.price"
]
}
}
}
],
"adjust_pure_negative": true,
"boost":1
}
},
"_source": {
"includes": [
"billing_last_name":
"billing_first_name"
],
"excludes": []
},
"sort": [
{
"_doc": {
"order": "asc"
}
}
]
}
ペアレントフィールドでクエリすると、1行しか表示されません。
「注文番号518894の請求者の名前を見つける」
sql> SELECT billing_last_name, billing_first_name FROM orders WHERE order_id=519894; billing_last_name|billing_first_name -----------------+------------------ Green |Jason
現行バージョンでの制約と、今後のアップデート
現在の制約をまとめると、次のようになります。
- クロステーブル/クロスインデックスでクエリする - インデックスのマッピングが同一の場合に限り実行できます。インデックスのマッピングに相違があると、クエリ時にエラーが生じます。
- JOIN - nestされたドキュメントを使用するJOINのみサポートされます。
- GROUP BYのグループ順序 - Elasticsearchのコンポジットアグリゲーションによる制限があります。
- nestされたSELECT句 - BIツールで詳細をサポートする一般的なメカニズムです。複数のインデックスを使用するnestされたSELECT宣言文はJOINに類似していますが、同じインデックスを使用する宣言文は書き換えられる可能性があります。今後詳しく検討される予定です。
今後リリースされるバージョンでは、次のような改善が予定されています。
- マルチレベルのGROUP BY実行機能（例：「月別に、平均2時間以上の遅延があるフライトについて、発着の両都市を見つける」）
- Elasticsearch地理空間演算子の提供
- INTERVALタイプのサポート（例：2つの日時の差異を演算する機能）
- Data/Time Mathの拡張とString機能
- グルーピングにおけるヒストグラムのサポート
- マッピングが同一でない場合のクロスインデックス検索とクロスクラスター検索
Elasticsearch SQLの活用事例シリーズは次回に続きます。どうぞお楽しみに。