Elasticsearch:aliyun-sql プラグインによるデータクエリ (新規顧客向けサービス終了)

プラグインの有効化と無効化

aliyun-sql プラグインを使用する前に、Kibana コンソールの [開発ツール] で有効化する必要があります。Kibana コンソールにログインする方法については、「Connect to a cluster using Kibana」をご参照ください。

• プラグインの有効化

  Kibana の [Dev Tools] で、次のコマンドを実行してプラグインを有効にします:

  PUT _cluster/settings
  {
    "transient": {
      "aliyun.sql.enabled": true
    }
  }

• プラグインの無効化とアンインストール

  アンインストールする前に、aliyun-sql プラグインの設定を無効化する必要があります。無効化しない場合、アンインストールによってクラスターの再起動がトリガーされ、ハングします。

  プラグインの設定を無効化します。

  PUT _cluster/settings
  {
    "persistent": {
      "aliyun.sql.enabled": null
    }
  }

  設定を無効化する前にプラグインをアンインストールし、再起動がハングした場合は、次のコマンドを実行してアーカイブ済みの設定をクリアし、再起動を再開します：

  PUT _cluster/settings
  {
    "persistent": {
      "archived.aliyun.sql.enabled": null
    }
  }

クイックスタート

次の例では、テストデータを書き込み、aliyun-sql プラグイン を使用して JOIN クエリ を実行する方法を示します。

aliyun-sql プラグイン はクエリリクエスト のみをサポートし、書き込みリクエストはサポートしていません。バルク API を使用してデータを書き込むことができます。

1. ご使用の Alibaba Cloud Elasticsearch インスタンス の Kibana コンソール にログインします。

   Kibana コンソール へのログイン方法の詳細については、「Kibana を使用したクラスターへの接続」をご参照ください。

2. Kibana コンソール で、[開発ツール] > [コンソール] に移動し、次のコマンドを実行して学生データとランキングデータを追加します。

   学生情報データ：

   PUT stuinfo/_doc/_bulk?refresh
   {"index":{"_id":"1"}}
   {"id":572553,"name":"xiaoming","age":"22","addr":"addr1"}
   {"index":{"_id":"2"}}
   {"id":572554,"name":"xiaowang","age":"23","addr":"addr2"}
   {"index":{"_id":"3"}}
   {"id":572555,"name":"xiaoliu","age":"21","addr":"addr3"}

   学生ランキングデータ：

   PUT sturank/_doc/_bulk?refresh
   {"index":{"_id":"1"}}
   {"id":572553,"score":"90","sorder":"5"}
   {"index":{"_id":"2"}}
   {"id":572554,"score":"92","sorder":"3"}
   {"index":{"_id":"3"}}
   {"id":572555,"score":"86","sorder":"10"}

3. JOIN クエリ を使用して、学生の名前とランキングを取得します。

   POST /_alisql
   {
     "query":"select stuinfo.name,sturank.sorder from stuinfo join sturank on stuinfo.id=sturank.id"
   }

   レスポンスでは、columns フィールド にはカラム名と型が、rows フィールド には行データが含まれます：

   {
     "columns" : [
       {
         "name" : "name",
         "type" : "text"
       },
       {
         "name" : "sorder",
         "type" : "text"
       }
     ],
     "rows" : [
       [
         "xiaoming",
         "5"
       ],
       [
         "xiaowang",
         "3"
       ],
       [
         "xiaoliu",
         "10"
       ]
     ]
   }

基本的なクエリ

すべての SQL クエリは、POST /_alisql エンドポイントを使用して実行できます。

• 単純なクエリ

  POST /_alisql?pretty
  {
    "query": "select * from monitor where host='100.80.xx.xx' limit 5"
  }

• 返す結果数の設定

  POST /_alisql?pretty
  {
      "query": "select * from monitor",
      "fetch_size": 3
  }

• パラメータ化されたクエリ

  POST /_alisql?pretty
  {
    "query": "select * from monitor where host= ? ",
    "params": [{"type":"STRING","value":"100.80.xx.xx"}],
    "fetch_size": 1
  }

{
  "columns": [
    {
      "name": "times",
      "type": "integer"
    },
    {
      "name": "value2",
      "type": "float"
    },
    {
      "name": "host",
      "type": "keyword"
    },
    {
      "name": "region",
      "type": "keyword"
    },
    {
      "name": "measurement",
      "type": "keyword"
    },
    {
      "name": "timestamp",
      "type": "date"
    }
  ],
  "rows": [
    [
      572575,
      4649800.0,
      "100.80.xx.xx",
      "china-dd",
      "cpu",
      "2018-08-09T08:18:42.000Z"
    ]
  ],
  "cursor": "u5HzAgJzY0BEWEYxWlhKNVFXNWtS****"
}

スクロールクエリ

前のクエリのカーソルを使用して、次のページのデータを取得します。

• クエリリクエスト

  POST /_alisql?pretty
  {
      "cursor": "u5HzAgJzY0BEWEYxWlhKNVFXNWtS****"
  }

  パラメータータイプ	パラメーター	必須	説明
  URL パラメーター	pretty	いいえ	レスポンスを読みやすくフォーマットします。
  リクエストボディ パラメーター	cursor	はい	対応するデータをフェッチするためのカーソル値です。

• レスポンス

  {
    "rows": [
      [
        572547,
        3.327459E7,
        "100.80.xx.xx",
        "china-dd",
        "cpu",
        "2018-08-09T08:19:12.000Z"
      ]
    ],
    "cursor": "u5HzAgJzY0BEWEYxWlhKNVFXNWtS****"
  }

  レスポンスは基本的なクエリの場合と同様ですが、ネットワークトラフィックを削減するためにカラムフィールドは省略されます。

JSON フォーマットクエリ

format=org パラメーターを使用すると、結果を Elasticsearch の生の JSON フォーマットで返すことができます。このモードでは JOIN クエリはサポートされません。

• クエリリクエスト

  POST /_alisql?format=org
  {
    "query": "select * from monitor where host= ? ",
    "params": [{"type":"STRING","value":"100.80.xx.xx"}],
    "fetch_size": 1
  }

  その他のクエリパラメーターは、基本クエリと同じです。

• レスポンス

  {
    "_scroll_id": "DXF1ZXJ5QW5kRmV0Y2gBAAAAAAAAAAsWYXNEdlVJZzJTSXFfOGluOVB4Q3Z****",
    "took": 18,
    "timed_out": false,
    "_shards": {
      "total": 1,
      "successful": 1,
      "skipped": 0,
      "failed": 0
    },
    "hits": {
      "total": 2,
      "max_score": 1.0,
      "hits": [
        {
          "_index": "monitor",
          "_type": "_doc",
          "_id": "2",
          "_score": 1.0,
          "_source": {
            "times": 572575,
            "value2": 4649800,
            "host": "100.80.xx.xx",
            "region": "china-dd",
            "measurement": "cpu",
            "timestamp": "2018-08-09T16:18:42+0800"
          }
        }
      ]
    }
  }

  レスポンスフォーマットは、元の DSL クエリのものと同じです。ページネーションには _scroll_id パラメーターを使用できます。

クエリの変換

SQL ステートメントを Elasticsearch のドメイン固有言語 (DSL) ステートメントに変換できます。この機能は JOIN クエリをサポートしていません。

• クエリリクエスト

  POST _alisql/translate
  {
    "query": "select * from monitor where host= '100.80.xx.xx' "
  }

• レスポンス

  {
    "size": 1000,
    "query": {
      "constant_score": {
        "filter": {
          "term": {
            "host": {
              "value": "100.80.xx.xx",
              "boost": 1.0
            }
          }
        },
        "boost": 1.0
      }
    },
    "_source": {
      "includes": [
        "times",
        "value2",
        "host",
        "region",
        "measurement",
        "timestamp"
      ],
  
      "excludes": [ ]
  
    }
  }

JOIN クエリ

aliyun-sql プラグインは、マージ結合で実装された内部結合のみをサポートします。次の制限事項にご注意ください。

• JOIN フィールドは、Elasticsearch ドキュメント ID に応じて、厳密に増加または減少する必要があります。

• JOIN フィールドは数値である必要があります。文字列フィールドはサポートされていません。

• デフォルトでは、テーブルごとに返される最大行数は 10,000 です。

構文：

SELECT
  expression
FROM table_name
JOIN table_name
 ON expression
[WHERE condition]

動的クラスターパラメーター max.join.size を使用して、テーブルごとの最大行数を 20,000 に設定するには、次のコマンドを実行します。

PUT /_cluster/settings
{
  "transient": {
    "max.join.size": 20000
  }
}

ネストされたフィールドとテキストフィールドのクエリ

aliyun-sql プラグインは、ネストされたフィールドとテキストフィールドのクエリをサポートしています。

1. ネストされたフィールドとテキストフィールドを含むインデックスを作成します。

   PUT user_info/
   {
       "mappings":{
           "_doc":{
               "properties":{
                   "addr":{
                       "type":"text"
                   },
                   "age":{
                       "type":"integer"
                   },
                   "id":{
                       "type":"integer"
                   },
                   "name":{
                        "type":"nested",
                        "properties":{
                           "first_name":{
                               "type":"keyword"
                           },
                           "second_name":{
                               "type":"keyword"
                           }
                       }
                   }
               }
           }
       }
   }

2. 一括挿入を実行します。

   PUT user_info/_doc/_bulk?refresh
   {"index":{"_id":"1"}}
   {"addr":"467 Hutchinson Court","age":80,"id":1,"name":[{"first_name":"lesi","second_name" : "Adams"},{"first_name":"chaochaosi","second_name" : "Aams"}]}
   {"index":{"_id":"2"}}
   {"addr":"671 Bristol Street","age":21,"id":2,"name":{"first_name":"Hattie","second_name" : "Bond"}}
   {"index":{"_id":"3"}}
   {"addr":"554 Bristol Street","age":23,"id":3,"name":{"first_name":"Hattie","second_name" : "Bond"}}

3. ネストされた型の second_name フィールドを照会します。

   POST _alisql
   {
     "query": "select * from user_info where name.second_name='Adams'"
   }

   レスポンス：

   {
     "columns" : [
       {
         "name" : "id",
         "type" : "integer"
       },
       {
         "name" : "addr",
         "type" : "text"
       },
       {
         "name" : "name.first_name",
         "type" : "keyword"
       },
       {
         "name" : "age",
         "type" : "integer"
       },
       {
         "name" : "name.second_name",
         "type" : "keyword"
       }
     ],
     "rows" : [
       [
         1,
         "467 Hutchinson Court",
         "lesi",
         80,
         "Adams"
       ]
     ]
   }

4. テキスト型の addr フィールドをクエリします。

   POST _alisql
   {
     "query": "select * from user_info where addr='Bristol'"
   }

   レスポンス：

   {
     "columns" : [
       {
         "name" : "id",
         "type" : "integer"
       },
       {
         "name" : "addr",
         "type" : "text"
       },
       {
         "name" : "name.first_name",
         "type" : "keyword"
       },
       {
         "name" : "age",
         "type" : "integer"
       },
       {
         "name" : "name.second_name",
         "type" : "keyword"
       }
     ],
     "rows" : [
       [
         2,
         "671 Bristol Street",
         "Hattie",
         21,
         "Bond"
       ],
       [
         3,
         "554 Bristol Street",
         "Hattie",
         23,
         "Bond"
       ]
     ]
   }

カスタムユーザー定義関数 (UDF)

ユーザー定義関数 (UDF) は、プラグインの初期化時にのみ登録できます。UDF を動的に追加することはできません。次の例では、date_format メソッドを拡張する方法を示します。

1. UDF に基づいて DateFormat クラスを定義します。

   /**
    * DateFormat。
    */
   public class DateFormat extends UDF {
   
       public String eval(DateTime time, String toFormat) {
           if (time == null || toFormat == null) {
               return null;
           }
           Date date = time.toDate();
           SimpleDateFormat format =  new SimpleDateFormat(toFormat);
           return format.format(date);
       }
   
   }

2. DateFormat クラスをプラグインの初期化メソッドに追加します。

   udfTable.add(KeplerSqlUserDefinedScalarFunction
                   .create("date_format"
                           , DateFormat.class
                           , (JavaTypeFactoryImpl) typeFactory));

3. クエリで UDF を使用します。

   select date_format(date_f,'yyyy') from date_test

SQL 構文の概要

SELECT [DISTINCT] (* | expression) [[AS] alias] [, ...]
FROM table_name
[WHERE condition]
[GROUP BY expression [, ...]
 [HAVING condition]]
[ORDER BY expression [ ASC | DESC ] [, ...]]
[LIMIT [offset, ] size]

SELECT
  expression
FROM table_name
JOIN table_name
 ON expression
[WHERE condition]

関数とエクスプレッション