Elasticsearch SQLの活用事例 - パート1

By
Dale McDiarmid

Elastic Stackバージョン6.3のリリースでは、さまざまな機能が登場しました。X-Packのコードの公開やロールアップと共に、より実験的に加わったのがSQLのサポート機能です。Elasticsearchに関して最もリクエストが多い機能の1つでもありました。

このブログではシリーズ記事形式で、現在Elasticsearch SQLがサポートする機能を紹介します。また、機能的な制限と、今後アップデートが予定されている内容についてもお伝えします。

誰にとって便利な機能か

これまでElasticではいくつかの点について方向性が決まらず、プロダクトにSQLに対応する機能を追加していませんでした。たとえば次のような点です。

  • SQLのサポート機能に何を含めるべき？
  • どの機能をサポートするべき（JOINをサポートするべき）？
  • Expression、関数、グルーピングのサポートは？
  • JDBC/ODBC接続のサポートは？
  • そもそもSQLをサポートすべきなの？単にElasticが使用するドメイン固有言語について新規ユーザー向けの説明が不足しているのでは？
    •

試行錯誤の末、Elasticはユーザーに役立つ機能にするための要件を次のように特定しました。ユーザーへのヒアリングから、SQLのサポートを求める2つのユーザー像が見えてきたのです。

  • 新規ユーザー： Elastic Stackの新規ユーザーで、いきなりElasticsearchのDSL（ドメイン固有言語）を使用するのはハードルが高いと感じている、または完全な構文を習得する必要性がない新規ユーザーです。たとえば、「パフォーマンスやスケールの観点でSQLベースのアプリケーションからの移行を考えているが、構文を学習する手間は避けたい、なおかつ同等のクエリ機能を使いたい」といったケース。こうしたユーザーの場合、既存の知識をベースに、共通項を手掛かりとして新たな言語を学ぶメカニズムが備わっていればなお良いと考えられます。
  • データコンシューマー：ElasticsearchのDSLを学びたいというニーズはまったくないユーザーです。たとえば、データを抽出し、外部処理に使用できれば十分と考えるデータサイエンティストなどが該当します。初級レベルのBIユーザーで、SQLの知識がある、あるいは日常的なSQLユーザーと協働してしているケースが少なくありません。

またこのブログシリーズでも何度か取り上げているように、SQL自体、宣言型言語として非常に魅力ある言語です。SQLがこれほど普及している理由として、処理のロジックや目的を、制御フローを定義することなく表現できる機能性を挙げることができます。またこの記事でも説明しますが、Elasticsearch DSLでは冗長な表現になりやすいあるパターンのクエリを、より美しく定義することができます。Elasticsearch DSLが全文検索をスムーズに記述できるのに対し、SQLは構造化された解析ベースのクエリを効率的に記述することができます。

Elasticsearch SQLでできること、できないこと

初めてとなる今回のリリースで、Elasticsearch SQLに含まれる機能の1つがANSI SQL仕様のサブセットに対応した読み取り専用インターフェースです。また、Elasticsearchをテーブルソースとして提供できます。この仕様の他に追加の演算子もあり、RDBMSベースの実装と比べ、Elasticsearch固有の機能を提供できるようになっています。軽量かつ高速な実装を提供できるように、外部依存性と可動部を最少に留めています。機能としてはElasticsearchを（関連するプロパティと共に）完全なリレーショナルデータベースに変換するとか、データモデリングのニーズに応えるものではありません。SQLプラグインは、多少のデータ操作機能とデータ操作の言語表現を実装しますが、結果のカウントや順序に影響する場合、グループ化のリクエストがある場合はプッシュダウン（後入れ先出し）の原則に従います。したがって現在のところ、Elasticsearch SQLプラグインでのデータ処理は結果の操作（例：フィールドの関数）と、クライアント（JDBCドライバー/CLIまたはブラウザー）のレンダリングに制限されます。このアプローチはElasticsearchのスケーラビリティとパフォーマンスを利用して、重い処理をさせるというものです。

elasticsearch-sql.png

概念マッピング：インデックスとドキュメントvsテーブルと行

初期のElasticsearchでは、インデックスと各種typeをRDBMSデータベースとテーブルに例えて説明することがありました。新しいコンセプトをわかりやすく伝えるためです。しかし、Elasticsearch 6.3ドキュメントにもあるように、この例えはどちらも不正確で、誤解を招く可能性があります。現在typeは1種類（将来的にはtypeはなくなる予定）になりましたが、そのこととは別に、Elasticsearchのスキーマレスなドキュメント指向モデルと、SQLの厳密な型指定のコンセプトとの間で何が等価なのか、正確に、ロジカルに理解する必要があります。

まずElasticsearchのインデックスはRDBMSのテーブルと同様、物理的に切り離され、広く同じやり方で使用される必要があります（例：関連データの格納）。行とドキュメントも、フィールドや列をグループ化するメカニズムを持つ点で似ています。ただし、行は厳格な傾向がある（強制をより多く含む）のに対し、ドキュメントは構造を維持しつつも、やや柔軟です。Elasticsearchのフィールドは名前エントリを表し、さまざまなデータタイプをサポートする上、複数の値を含む可能性（例：リスト）もあります。この複数値のフィールドという例外を除けば、フィールドという概念はSQLの列と直接対比（マッピング）させることができます。メモ：複数値フィールドでSELECTを実行すると、クエリ時にエラーが生じます。

その他に直接的に対比できる概念はなく、SQLデータベースとElasticsearchクラスターの間には緩やかな相関性しか存在しません。しかし一般的に、相関性の乏しさがElasticsearch SQLユーザーに及ぼす影響はありません。概念マッピングや相関性について詳しくは、「SQLとElasticsearchの概念マッピング」ドキュメントをご覧ください。

簡単にまとめると、WHERE句でインデックス名を使用して、特定のインデックス/テーブルをターゲットとすることができます。ユニークなドキュメントが行として返され、フィールドは列として出力されます。このマッピングはわかりやすいので、以降、ドキュメント/行、フィールド/列の用語は互換性のあるものとして使用します。

実装の詳細

Elasticsearch SQLの実装は4つの実行フェーズから構成されます。

elasticsearch-sql-implementation.png

最初にSQLクエリをAbstract Syntax Tree（AST、抽象構文木）に変換するフェーズがパースです。ここであらゆる構文検証を行った後、次にアナライザーがASTを検証し、テーブルや列、関数、エイリアス、名前空間を解決して論理プランを作成します。このプランは冗長な表現の削除を含めて最適化されています。3つ目のフェーズが物理プラン（例：Elasticsearch DSL）の変換、実行です。最後にQuery Executorが実際のクエリを実行し、要求されたtypeやテーブルを変換（例：アグリゲーションツリーからテーブル）してクライアントに結果をストリーミングします。

接続サポート

SQL向けのソリューションを考える上で重要となるのが接続に関するサポートです。RESTのみのインターフェースで問題ないと考えるユーザーも多少はいますが、多くのユーザーが望むのは標準的なインターフェースであるJDBCとODBCへの接続です。現在、ODBCへのサポートを計画中で、開発が進んでいます。JDBCへのサポートは今回のリリースで提供されており、ダウンロードして使用できます。

ポイントは、これらのドライバーによる通信が、RESTインターフェースを介してHTTPを経由することです。これにはいくつかの大きなメリットがあります。

  1. ユーザーへのSQLアクセスの付与が、ネイティブに統合し、セキュリティを備えたElasticsearchポートを開き、エクスポーズすることと何ら変わりません。Elastic Cloudで提供されるマネージドのElasticsearch ServiceでもすばやくSQLをサポートでき、既存のユーザーはアクセス制御許可を簡単に組み合わせることができます。
  2. これによりRESTインターフェースで直接SQLを活用できるほか、追加で便利なCLIクライアントをリリースすることも可能です。CLIクライアントは特に管理者による使用が期待され、RDBMSで一般的なコマンドラインに慣れることができます。

このJDBCドライバーはクエリの実行と、レスポンスのパースをするための新規に作成されたXContentライブラリを活用します（このコードは歴史的にElasticsearchと組み合わせて使われています）。そうすることでElasticsearchのすべてのライブラリに依存する必要が無くなり、軽量でポータブルなドライバーとなります。この分離の仕組みは今後のリリースでさらに改良され、より小さく、高速なドライバーへアップデートされる予定です。

事例

CLIとREST APIを組み合わせて活用する例を考えてみましょう。この事例で使うサンプルの飛行データセットはまもなくKibanaと一緒に配布予定です。リリース前に入手したいという方はdemo.elastic.coから、同じデータセットをKibanaのコンソールで実行できます。demo.elastic.coのリンクでご覧いただくと、このブログで扱ったクエリが自動的に入力されます。また、実行したクエリの完全なリストもKibanaのデモコンソールで提供されています。関連性がない、または並び替え順が適用されていない場合にElasticsearchの結果は自然順となるため、サンプルのクエリに明確な順序付けや制約がない場合は、結果が異なることがあります。

Elasticsearchスキーマ情報を取得する：DSL vs SQL

はじめに、テーブル/インデックスのスキーマと、使用できるフィールドを特定しましょう。この手順はRESTインターフェース経由で行います。

リクエスト

POST _xpack/sql
{
  "query":"DESCRIBE flights"
}

demo.elastic.coで見る

レスポンス

{
  "columns": [
    {
      "name": "column",
      "type": "keyword"
    },
    {
      "name": "type",
      "type": "keyword"
    }
  ],
  "rows": [
    [ "AvgTicketPrice", "REAL" ],
    [ "Cancelled", "BOOLEAN" ],
    [ "Carrier", "VARCHAR" ],
    [ "Dest", "VARCHAR" ],
    [ "DestAirportID", "VARCHAR" ],
    [ "DestCityName", "VARCHAR" ],
    [ "DestCountry", "VARCHAR" ],
    [ "DestLocation", "OTHER" ],
    [ "DestRegion", "VARCHAR" ],
    [ "DestWeather", "VARCHAR" ],
    [ "DistanceKilometers", "REAL" ],
    [ "DistanceMiles", "REAL" ],
    [ "FlightDelay", "BOOLEAN" ],
    [ "FlightDelayMin", "INTEGER" ],
    [ "FlightDelayType", "VARCHAR" ],
    [ "FlightNum", "VARCHAR" ],
    [ "FlightTimeHour", "VARCHAR" ],
    [ "FlightTimeMin", "REAL" ],
    [ "Origin", "VARCHAR" ],
    [ "OriginAirportID", "VARCHAR" ],
    [ "OriginCityName", "VARCHAR" ],
    [ "OriginCountry", "VARCHAR" ],
    [ "OriginLocation", "OTHER" ],
    [ "OriginRegion", "VARCHAR" ],
    [ "OriginWeather", "VARCHAR" ],
    [ "dayOfWeek", "INTEGER" ],
    [ "timestamp", "TIMESTAMP" ]
  ]
}

上のレスポンスは、URLパラメーターの?format=txtでテーブル形式にすることができます。たとえば次のようになります。 

POST _xpack/sql?format=txt 
{
  "query":"DESCRIBE flights"
}

demo.elastic.coで見る 

column          |     type      
--------------------------+---------------
AvgTicketPrice            |REAL           
Cancelled                 |BOOLEAN        
Carrier                   |VARCHAR        
Carrier.keyword           |VARCHAR        
Dest                      |VARCHAR        
Dest.keyword              |VARCHAR        
DestAirportID             |VARCHAR        
DestAirportID.keyword     |VARCHAR        
DestCityName              |VARCHAR        
DestCityName.keyword      |VARCHAR        
DestCountry               |VARCHAR        
DestCountry.keyword       |VARCHAR        
DestLocation              |STRUCT         
DestLocation.lat          |VARCHAR        
DestLocation.lat.keyword  |VARCHAR        
DestLocation.lon          |VARCHAR        
DestLocation.lon.keyword  |VARCHAR        
DestRegion                |VARCHAR        
DestRegion.keyword        |VARCHAR        
DestWeather               |VARCHAR        
DestWeather.keyword       |VARCHAR        
DistanceKilometers        |REAL           
DistanceMiles             |REAL           
FlightDelay               |BOOLEAN        
FlightDelayMin            |BIGINT         
FlightDelayType           |VARCHAR        
FlightDelayType.keyword   |VARCHAR        
FlightNum                 |VARCHAR        
FlightNum.keyword         |VARCHAR        
FlightTimeHour            |REAL           
FlightTimeMin             |REAL           
Origin                    |VARCHAR        
Origin.keyword            |VARCHAR        
OriginAirportID           |VARCHAR        
OriginAirportID.keyword   |VARCHAR        
OriginCityName            |VARCHAR        
OriginCityName.keyword    |VARCHAR        
OriginCountry             |VARCHAR        
OriginCountry.keyword     |VARCHAR        
OriginLocation            |STRUCT         
OriginLocation.lat        |VARCHAR        
OriginLocation.lat.keyword|VARCHAR        
OriginLocation.lon        |VARCHAR        
OriginLocation.lon.keyword|VARCHAR        
OriginRegion              |VARCHAR        
OriginRegion.keyword      |VARCHAR        
OriginWeather             |VARCHAR        
OriginWeather.keyword     |VARCHAR        
dayOfWeek                 |BIGINT         
timestamp                 |TIMESTAMP

これ以降、REST APIのレスポンス例を示す場合に上のようなテーブルレスポンスストラクチャーを使用します。コンソールで同じクエリを実行するには、次の情報を使用してログオンしてください。 

./elasticsearch-sql-cli http://elastic@localhost:9200

パスワードを入力すると、こうなります。 

sql> DESCRIBE flights;
column      |     type
------------------+---------------
AvgTicketPrice    |REAL
Cancelled         |BOOLEAN
Carrier           |VARCHAR
Dest              |VARCHAR
DestAirportID     |VARCHAR
DestCityName      |VARCHAR
DestCountry       |VARCHAR
DestLocation      |OTHER
DestRegion        |VARCHAR
DestWeather       |VARCHAR
DistanceKilometers|REAL
DistanceMiles     |REAL
FlightDelay       |BOOLEAN
FlightDelayMin    |INTEGER
FlightDelayType   |VARCHAR
FlightNum         |VARCHAR
FlightTimeHour    |VARCHAR
FlightTimeMin     |REAL
Origin            |VARCHAR
OriginAirportID   |VARCHAR
OriginCityName    |VARCHAR
OriginCountry     |VARCHAR
OriginLocation    |OTHER
OriginRegion      |VARCHAR
OriginWeather     |VARCHAR
dayOfWeek         |INTEGER
timestamp         |TIMESTAMP
sql>

上のスキーマはSELECT句で表示されたフィールドに対するすべてのクエリを返しており、潜在的なドライバーに対して結果のフォーマットや演算に必要なtype情報を提供しています。たとえばレスポンスを短くするため、LIMIT句を含む簡単なSELECTを考えてみましょう。デフォルトでは1,000行を返します。

簡単なSELECT

POST _xpack/sql?format=txt
{
  "query":"SELECT FlightNum FROM flights LIMIT 1"
}

demo.elastic.coで見る（結果は異なる場合があります） 

   FlightNum   
---------------
1Y0TZOE

JDBCドライバーやコンソールを使用した場合、同じRESTリクエスト/レスポンスを処理していますが、ユーザーは意識する必要はありません。 

sql> SELECT OriginCountry, OriginCityName FROM flights LIMIT 1;
OriginCountry |OriginCityName
---------------+---------------
US      |San Diego

demo.elastic.coで見る（結果は異なる場合があります）

存在しないフィールドをリクエストした場合（ケースセンシティブです）、エラーを意味するテーブルのセマンティックと強く型付けされたストアが返されます。これは、ただフィールドが返ってこないだけというElasticsearchの挙動とは異なります、例えば上の例で“OriginCityName”に代えて“OrigincityName”というフィールドを使用すると、次のように親切なエラーメッセージが返されます。 

{
  "error": {
    "root_cause": [
      {
        "type": "verification_exception",
        "reason": "Found 1 problem(s)\nline 1:8: Unknown column [OrigincityName], did you mean any of [OriginCityName, DestCityName]?"
      }
    ],
    "type": "verification_exception",
    "reason": "Found 1 problem(s)\nline 1:8: Unknown column [OrigincityName], did you mean any of [OriginCityName, DestCityName]?"
  },
  "status": 400
}

demo.elastic.coで見る

同様に、互換性のない関数や表現をフィールドに使用した場合もエラーが表示されます。一般的に、アナライザーがASTを検証するプロセスの初期段階で失敗となります。これを実現するには、Elasticsearchがインデックスマッピングと各フィールドの機能を把握している必要があります。このため、セキュリティを備えたSQLインターフェースにアクセスするクライアントには適切なアクセス権が必要です。

記事の長さの関係で、すべてのリクエストパターンとレスポンス事例を記載することはできませんが、もう少し複雑な、興味深いクエリをいくつかご紹介します。

WHEREとORDER BYを使ったSELECT

「飛行時間が5時間以上で、米国を離陸した、飛行時間が長い上位10フライトを見つける」 

POST _xpack/sql?format=txt
{
  "query":"SELECT OriginCityName, DestCityName FROM flights WHERE FlightTimeHour > 5 AND OriginCountry='US' ORDER BY FlightTimeHour DESC LIMIT 10"
}

demo.elastic.coで見る 

OriginCityName | DestCityName  
---------------+---------------
Atlanta        |Durban         
Louisville     |Melbourne      
Peoria         |Melbourne      
Albuquerque    |Durban         
Birmingham     |Durban         
Bangor         |Brisbane       
Seattle        |Durban         
Huntsville     |Sydney         
Savannah       |Shanghai       
Philadelphia   |Xi'an

行数を制限する演算子は実装するSQLによって異なります。Elasticsearch SQLのLIMIT演算子の実装は、Postgresql/Mysqlと同じ実装です。

数学処理

ランダムな数学処理を行ってみます。 

sql> SELECT ((1 + 3) * 1.5 / (7 - 6)) * 2 AS random;
    random
---------------
12.0

demo.elastic.coで見る

これはサーバーが関数のポストプロセスをどこで実行しているかの例を示します。これに相当する機能はElasticsearch DSLクエリにはありません。

関数と表現

「6月以降の、飛行時間が5時間以上のフライトを飛行時間が長い順にすべて見つける」 

POST _xpack/sql?format=txt
{
  "query":"SELECT MONTH_OF_YEAR(timestamp), OriginCityName, DestCityName FROM flights WHERE FlightTimeHour > 5 AND MONTH_OF_YEAR(timestamp) > 6 ORDER BY FlightTimeHour DESC LIMIT 10"
}

demo.elastic.coで見る 

MONTH_OF_YEAR(timestamp [UTC])|OriginCityName |   DestCityName    
------------------------------+---------------+-------------------
7                             |Buenos Aires   |Shanghai           
7                             |Stockholm      |Sydney             
7                             |Chengdu        |Bogota             
7                             |Adelaide       |Cagliari           
7                             |Osaka          |Buenos Aires       
7                             |Buenos Aires   |Chitose / Tomakomai
7                             |Buenos Aires   |Shanghai           
7                             |Adelaide       |Washington         
7                             |Osaka          |Quito              
7                             |Buenos Aires   |Xi'an

従来、このような結果をElasticsearchで得るには、Painless言語で記述する必要がありました。しかしSQLの宣言機能を使えば、スクリプトを書く必要がありません。WHERE句とSELECT句でこの関数を使用する方法にも注目してください。WHERE句のコンポーネントは結果の行数に影響するため、Elasticsearchにプッシュダウンされます。一方SELECT関数は、提示され次第サーバーサイドプラグインで処理されます。

使用できる関数の一覧は、“SHOW FUNCTIONS”で取得することができます。

demo.elastic.coで見る

この処理を先ほどの数学処理と組み合わせて、DSLでは表現が複雑になりがちなクエリを作成することができます。

「飛行距離が500km以上で、月曜、火曜、水曜の午前9時から11時の間に離陸した最も速度の速い上位2フライトの、飛行距離と平均速度を求めよ。速度が等しい場合、飛行距離の長いものを先に表示する。」

demo.elastic.coで見る 

sql> SELECT timestamp, FlightNum, OriginCityName, DestCityName, ROUND(DistanceMiles) AS distance, ROUND(DistanceMiles/FlightTimeHour) AS speed, DAY_OF_WEEK(timestamp) AS day_of_week FROM flights WHERE DAY_OF_WEEK(timestamp) >= 0 AND DAY_OF_WEEK(timestamp) <= 2 AND HOUR_OF_DAY(timestamp) >=9 AND HOUR_OF_DAY(timestamp) <= 10 ORDER BY speed DESC, distance DESC LIMIT 2;
       timestamp        |   FlightNum   |OriginCityName | DestCityName  |   distance    |     speed     |  day_of_week
------------------------+---------------+---------------+---------------+---------------+---------------+---------------
2018-07-03T10:03:11.000Z|REPKGRT        |Melbourne      |Norfolk        |10199          |783            |2
2018-06-05T09:18:29.000Z|J72Y2HS        |Dubai          |Lima           |9219           |783            |2

設問がややこしくなってしまいましたが、そこがポイントです。フィールドエイリアスを作成し、ORDER BY句で参照する仕組みにも注目してください。

WHERE句とORDER BY句で使用されたSELECT句ですべてのフィールドを指定する必要がないことにも注目しましょう。他のSQL実装とは異なるのではないかと思います。たとえば、次のようなクエリもまったく問題なく機能します。 

POST _xpack/sql
{
  "query":"SELECT timestamp, FlightNum FROM flights WHERE AvgTicketPrice > 500 ORDER BY AvgTicketPrice"
}

demo.elastic.coで見る

SQLクエリをDSLに翻訳する

ここまでElasticsearch DSLでは表現しにくいSQLクエリについて、またDSLによる記述が可能な限り最適化されているかについて考えてきました。今回新たに登場したSQLインターフェースの魅力の1つが、Elasticsearchをはじめて導入するユーザー向けに、こうした問題をアシストする機能の搭載です。このRESTインターフェースは“sql”エンドポイントに/translate機能を追加し、Elasticsearchクエリを取得します。

先ほどのクエリで実行してみます。 

POST _xpack/sql/translate
{
  "query":"SELECT OriginCityName, DestCityName FROM flights WHERE FlightTimeHour > 5 AND OriginCountry='US' ORDER BY FlightTimeHour DESC LIMIT 10"
}

demo.elastic.coで見る

経験豊富なElasticsearchユーザーならわかると思いますが、同じクエリをDSLで記述するとこうなります。 

{
  "size": 10,
  "query": {
    "bool": {
      "filter": [
        {
          "range": {
            "FlightTimeHour": {
              "from": 5,
              "to": null,
              "include_lower": false,
              "include_upper": false,
              "boost": 1
            }
          }
        },
        {
          "term": {
            "OriginCountry.keyword": {
              "value": "US",
              "boost": 1
            }
          }
        }
      ],
      "adjust_pure_negative": true,
      "boost": 1
    }
  },
  "_source": {
    "includes": [
      "OriginCityName",
      "DestCityName"
    ],
    "excludes": []
  },
  "sort": [
    {
      "FlightTimeHour": {
        "order": "desc"
      }
    }
  ]
}

WHERE句は"range"クエリと"term"クエリに変換されます。サブフィールドの"OriginCountry.keyword"変数が、親であるOriginCountry（テキストtype）の別の形としてexact term matchのために使用されていることがわかります。ユーザーは、マッピングによる挙動の差異を理解する必要はありません。自動で正しいフィールドタイプが選択されます。興味深いのは、doc valuesが有効なexact type（数値、日付、イーワード）などが利用できる場合に、インターフェースが_sourceに対してdocvalue_fieldを使用して取得パフォーマンスの最適化を図っている点です。このようにElasticsearch SQLを使用して、指定するクエリで最適なDSLによる記述を生成することができます。

次に、先ほどの最も複雑なクエリを取り上げましょう。 

POST _xpack/sql/translate
{
  "query":"SELECT timestamp, FlightNum, OriginCityName, DestCityName, ROUND(DistanceMiles) AS distance, ROUND(DistanceMiles/FlightTimeHour) AS speed, DAY_OF_WEEK(timestamp) AS day_of_week FROM flights WHERE DAY_OF_WEEK(timestamp) >= 0 AND DAY_OF_WEEK(timestamp) <= 2 AND HOUR_OF_DAY(timestamp) >=9 AND HOUR_OF_DAY(timestamp) <= 10 ORDER BY speed DESC, distance DESC LIMIT 2"
}

demo.elastic.coで見る

レスポンスはこうなりました。 

{
  "size": 2,
  "query": {
    "bool": {
      "filter": [
        {
          "bool": {
            "filter": [
              {
                "script": {
                  "script": {
                    "source": "(params.v0 <= doc[params.v1].value.getDayOfWeek()) && (doc[params.v2].value.getDayOfWeek() <= params.v3)",
                    "lang": "painless",
                    "params": {
                      "v0": 0,
                      "v1": "timestamp",
                      "v2": "timestamp",
                      "v3": 2
                    }
                  },
                  "boost": 1
                }
              },
              {
                "script": {
                  "script": {
                    "source": "doc[params.v0].value.getHourOfDay() >= params.v1",
                    "lang": "painless",
                    "params": {
                      "v0": "timestamp",
                      "v1": 9
                    }
                  },
                  "boost": 1
                }
              }
            ],
            "adjust_pure_negative": true,
            "boost": 1
          }
        },
        {
          "script": {
            "script": {
              "source": "doc[params.v0].value.getHourOfDay() <= params.v1",
              "lang": "painless",
              "params": {
                "v0": "timestamp",
                "v1": 10
              }
            },
            "boost": 1
          }
        }
      ],
      "adjust_pure_negative": true,
      "boost": 1
    }
  },
  "_source": false,
  "stored_fields": "_none_",
  "docvalue_fields": [
    "timestamp",
    "FlightNum",
    "OriginCityName",
    "DestCityName",
    "DistanceMiles",
    "FlightTimeHour"
  ],
  "sort": [
    {
      "_script": {
        "script": {
          "source": "Math.round((doc[params.v0].value) / (doc[params.v1].value))",
          "lang": "painless",
          "params": {
            "v0": "DistanceMiles",
            "v1": "FlightTimeHour"
          }
        },
        "type": "number",
        "order": "desc"
      }
    },
    {
      "_script": {
        "script": {
          "source": "Math.round(doc[params.v0].value)",
          "lang": "painless",
          "params": {
            "v0": "DistanceMiles"
          }
        },
        "type": "number",
        "order": "desc"
      }
    }
  ]
}

WHERE句とORDER BY句はPainlessスクリプトに変換され、Elasticsearchが提供するsortとscript queryが活用されています。さらにスクリプトは複雑化を避け、またスクリプトキャッシングを活用するためパラメーター表記化されています。

ちなみに、上の例ではSQLステートメントに対して最適な翻訳が行われていますが、より広範な問題では最適なソリューションが提示されません。今後さらに、1週間の中の曜日や、1日の中の時間、ドキュメントのインデックス速度をエンコードする必要があります。従って現在はシンプルな範囲のクエリを使用するしかありません。Painlessスクリプトを使用して解決する特定の問題ではよりパフォーマンスに優れる結果となるでしょう。この理由から、ドキュメントに一部のこうしたフィールドが存在しています。一般的に、ユーザーの注意が必要な点は「Elasticsearch SQLの実装で最適な翻訳が行われることについては信頼できるが、クエリに指定されたフィールドでしか活用することはできない。したがって、大規模な問題に最適なソリューションをかならず提供するとは限らない」ということです。最適なアプローチの実施に必要となる基盤プラットフォームの長所を考慮すると、_translate APIはこのプロセスの第一歩として位置づけることができます。

次回記事

次の記事、「Elasticsearch SQLの活用事例パートII」では、引き続き_translate APIを使用してElasticsearch SQLのさらに高度な機能をご紹介します。また、初回のリリースでユーザーにとって制約となる可能性のある部分についてもご説明します。さらに、今後リリースが予定されている機能についてにも触れます。