Elasticsearch:Kueri data menggunakan plug-in aliyun-sql (onboarding pelanggan baru dihentikan)

Aktifkan dan nonaktifkan plug-in

Sebelum menggunakan plug-in aliyun-sql, Anda harus mengaktifkannya di Dev Tools pada konsol Kibana. Untuk login ke konsol Kibana, lihat Menghubungkan ke kluster menggunakan Kibana.

• Aktifkan plug-in

  Di Dev Tools Kibana, jalankan perintah berikut:

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

• Nonaktifkan dan uninstal plug-in

  Anda harus menonaktifkan konfigurasi plug-in aliyun-sql sebelum meng-uninstalnya. Jika tidak, restart kluster yang dipicu oleh proses uninstall akan hang.

  Nonaktifkan konfigurasi plug-in.

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

  Jika Anda meng-uninstal plug-in sebelum menonaktifkan konfigurasinya dan restart menjadi hang, jalankan perintah berikut untuk membersihkan konfigurasi yang diarsipkan dan melanjutkan restart:

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

Panduan Cepat

Contoh berikut menunjukkan cara menggunakan plug-in aliyun-sql untuk menulis data uji dan menjalankan kueri JOIN.

Plug-in aliyun-sql hanya mendukung permintaan kueri, bukan permintaan tulis. Anda dapat menggunakan bulk API untuk menulis data.

1. Login ke konsol Kibana instans Alibaba Cloud Elasticsearch Anda.

   Untuk informasi selengkapnya tentang cara login ke konsol Kibana, lihat Menghubungkan ke kluster menggunakan Kibana.

2. Di Kibana, buka Dev Tools > Console, lalu masukkan data informasi mahasiswa dan data peringkat.

   Data informasi mahasiswa:

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

   Data peringkat mahasiswa:

   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. Gunakan kueri JOIN untuk mengambil nama dan peringkat mahasiswa.

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

   Dalam respons, bidang columns berisi nama dan tipe kolom, sedangkan bidang rows berisi data baris:

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

Kueri dasar

Anda dapat menjalankan semua kueri SQL menggunakan endpoint POST /_alisql.

• Kueri sederhana

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

• Tetapkan jumlah hasil yang dikembalikan

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

• Kueri berparameter

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

Kueri scroll

Gunakan cursor dari kueri sebelumnya untuk mengambil halaman data berikutnya.

• Permintaan kueri

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

  Jenis parameter	Parameter	Apakah wajib?	Deskripsi
  Parameter URL	pretty	Tidak	Format respons agar mudah dibaca.
  Parameter body permintaan	cursor	Ya	Nilai cursor untuk mengambil data yang sesuai.

• Respons

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

  Respons ini mirip dengan kueri dasar, tetapi bidang columns dihilangkan untuk mengurangi lalu lintas jaringan.

Kueri format JSON

Anda dapat menggunakan parameter format=org untuk mengembalikan hasil dalam format JSON Elasticsearch asli. Mode ini tidak mendukung kueri JOIN.

• Permintaan kueri

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

  Parameter kueri lainnya sama seperti pada kueri dasar.

• Respons

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

  Format respons sesuai dengan kueri DSL asli. Anda dapat menggunakan parameter _scroll_id untuk paginasi.

Terjemahkan kueri

Anda dapat mengonversi pernyataan SQL menjadi pernyataan Domain-Specific Language (DSL) Elasticsearch. Fitur ini tidak mendukung kueri JOIN.

• Permintaan kueri

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

• Respons

  {
    "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": [ ]
  
    }
  }

Kueri JOIN

Plug-in aliyun-sql hanya mendukung inner join, yang diimplementasikan menggunakan merge join. Perhatikan batasan berikut:

• Bidang JOIN harus bersifat strictly increasing atau decreasing terhadap ID dokumen Elasticsearch.

• Bidang JOIN harus numerik. Bidang string tidak didukung.

• Secara default, jumlah maksimum baris yang dapat dikembalikan per tabel adalah 10.000.

Sintaksis:

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

Anda dapat mengubah jumlah maksimum baris per tabel menggunakan parameter kluster dinamis max.join.size. Misalnya, untuk menetapkan jumlah maksimum baris menjadi 20.000, jalankan perintah berikut:

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

Kueri untuk bidang nested dan text

Plug-in aliyun-sql mendukung kueri untuk bidang nested dan text.

1. Buat indeks dengan bidang nested dan text.

   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. Lakukan penyisipan bulk.

   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. Kueri bidang second_name bertipe nested.

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

   Respons:

   {
     "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. Kueri bidang addr bertipe text.

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

   Respons:

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

Fungsi UDF kustom

Anda hanya dapat mendaftarkan User-Defined Functions (UDF) selama inisialisasi plug-in. Anda tidak dapat menambahkan UDF secara dinamis. Contoh berikut menunjukkan cara memperluas metode date_format.

1. Definisikan kelas DateFormat berdasarkan UDF.

   /**
    * 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. Tambahkan kelas DateFormat ke metode inisialisasi plug-in.

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

3. Gunakan UDF dalam kueri.

   select date_format(date_f,'yyyy') from date_test

Ikhtisar sintaksis 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]

Fungsi dan ekspresi