Elasticsearch:aliyun-sql プラグインを使用したデータのクエリ (新規顧客の受け入れは終了しました)

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

aliyun-sql プラグインを使用する前に、Kibana コンソールの [Dev Tools] で有効にする必要があります。Kibana コンソールへのログイン方法については、「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 プラグインはクエリリクエストのみをサポートし、書き込みリクエストはサポートしていません。bulk API を使用してデータを書き込むことができます。

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

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

2. Kibana で、[Dev Tools] > [Console] に移動し、学生情報とランキングデータを入力します。

   学生情報データ:

   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"
       ]
     ]
   }

基本的なクエリ

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

• シンプルクエリ

  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****"
  }

  応答は基本的なクエリの応答と似ていますが、ネットワークトラフィックを削減するために `columns` フィールドは省略されています。

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 Domain-Specific Language (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]

関数と式