Elasticsearch：Elasticsearch SQL介绍及实例

SQL 实操

检索Elasticsearch schema 信息：DSL vs SQL

首先，我们确定表/索引的schema以及可供我们使用的字段。 我们将通过REST界面执行此操作：

POST /_sql
{
  "query":"desc sports"
}

上面的命令结果：

{
  "columns" : [
    {
      "name" : "column",
      "type" : "keyword"
    },
    {
      "name" : "type",
      "type" : "keyword"
    },
    {
      "name" : "mapping",
      "type" : "keyword"
    }
  ],
  "rows" : [
    [
      "age",
      "INTEGER",
      "integer"
    ],
    [
      "birthdate",
      "TIMESTAMP",
      "datetime"
    ],
    [
      "goals",
      "INTEGER",
      "integer"
    ],
    [
      "location",
      "GEOMETRY",
      "geo_point"
    ],
    [
      "name",
      "VARCHAR",
      "keyword"
    ],
    [
      "rating",
      "INTEGER",
      "integer"
    ],
    [
      "role",
      "VARCHAR",
      "keyword"
    ],
    [
      "score_weight",
      "REAL",
      "float"
    ],
    [
      "sport",
      "VARCHAR",
      "keyword"
    ]
  ]
}

也可以通过url参数format = txt以表格形式格式化以上响应。 例如：

POST /_sql?format=txt 
{
  "query": "DESCRIB sports"
}

上面查询结果

|   column     |     type      |    mapping    
---------------+---------------+---------------
age            |INTEGER        |integer        
birthdate      |TIMESTAMP      |datetime       
goals          |INTEGER        |integer        
location       |GEOMETRY       |geo_point      
name           |VARCHAR        |keyword        
rating         |INTEGER        |integer        
role           |VARCHAR        |keyword        
score_weight   |REAL           |float          
sport          |VARCHAR        |keyword        

也可以通过控制台实现相同的查询，需要使用一下命令登陆：

cd /home/elastic/elasticsearch-7.5.1
./bin/elasticsearch-sql-cli http://192.168.1.234:9200

可以看到SQL的命令提示符了，可以直接输入sql