ES的SQL功能 2020-08-05

声明

es版本号6.8

概念对应

SQL	Elasticsearch	对比说明
column	field	ES尽可能的把字段和SQL中的列做对应，并不是完全对应的上，比如ES中字段可能是一个list
row	document	document 结构更灵活和松散，row更严格
table	index	查询语句执行的对象，理论上table 和index 更加具有可比性，而不是type
schema	无对应关系	SQL中schema是一个table、index 等对象的命名空间，能起到一定的访问控制的作用，ES中没有合适的对应，或者对应上具有安全特性的ES的role概念
database或catalog	cluster 实例	SQL中database和catalog大多数情况指的是一个概念，代表schema的集合。ES中cluster是index的集合
cluster	cluster、集群联盟	典型的SQL场景中，cluster指的是包含多个database或catalog的单个实例，ES的cluster是真正的运行在多个机器上的分布式节点集群

SQL查询方式

使用POST _xpack/sql?format=txt 或者_sql?format=txt(7.0后)API
- format 支持 txt ，以表格方式返回数据
- format 支持josn ，以json格式返回数据
- format支持其他格式，如csv、yaml、cbor、smile
使用es命令行工具 /bin/elasticsearch-sql-cli
使用jdbc Driver 或者odbc Driver (铂金级产品才能用)

SQL查询的本质

_sql API 本质上是一个翻译者的角色，把SQL翻译成了DSL语句给ES去执行，可以通过 /_xpack/sql/translate 查看翻译后的DSL

POST /_xpack/sql/translate
{
    "query": "select \"@timestamp\",application.keyword,cs_uri_query,elapsed_time,requested_uri,server_ip from \"accesslog-car-api-2020-08-05\" where application.keyword = 'car-web-pc-api' and requested_uri.keyword = '/pc/web/api/v1/getrecommendserialbyserialid' and elapsed_time >= 100 order by elapsed_time desc",
    "fetch_size": 10
}

{
  "size" : 10,
  "query" : {
    "bool" : {
      "must" : [
        {
          "bool" : {
            "must" : [
              {
                "term" : {
                  "application.keyword" : {
                    "value" : "car-web-pc-api",
                    "boost" : 1.0
                  }
                }
              },
              {
                "term" : {
                  "requested_uri.keyword" : {
                    "value" : "/pc/web/api/v1/getrecommendserialbyserialid",
                    "boost" : 1.0
                  }
                }
              }
            ],
            "adjust_pure_negative" : true,
            "boost" : 1.0
          }
        },
        {
          "range" : {
            "elapsed_time" : {
              "from" : 100,
              "to" : null,
              "include_lower" : true,
              "include_upper" : false,
              "boost" : 1.0
            }
          }
        }
      ],
      "adjust_pure_negative" : true,
      "boost" : 1.0
    }
  },
  "_source" : {
    "includes" : [
      "cs_uri_query",
      "requested_uri",
      "server_ip"
    ],
    "excludes" : [ ]
  },
  "docvalue_fields" : [
    {
      "field" : "@timestamp",
      "format" : "epoch_millis"
    },
    {
      "field" : "application.keyword",
      "format" : "use_field_mapping"
    },
    {
      "field" : "elapsed_time",
      "format" : "use_field_mapping"
    }
  ],
  "sort" : [
    {
      "elapsed_time" : {
        "order" : "desc",
        "missing" : "_first",
        "unmapped_type" : "long"
      }
    }
  ]
}

支持的SQL命令

SELECT
只支持SELECT 查询语句，且不是所有的SELECT 语法结构都支持，不支持INSERT、UPDATE、DELETE、CREATE等语句

查询方式一：

POST /_xpack/sql?format=txt
{
    "query": "select \"@timestamp\",application.keyword,cs_uri_query,elapsed_time,requested_uri,server_ip from \"accesslog-car-api-2020-08-05\" where application.keyword = 'car-web-pc-api' and requested_uri.keyword = '/pc/web/api/v1/getrecommendserialbyserialid' and elapsed_time >= 100 order by elapsed_time desc",
    "fetch_size": 10
}

返回数据

       @timestamp       |application.keyword| cs_uri_query  | elapsed_time  |               requested_uri               |   server_ip   
------------------------+-------------------+---------------+---------------+-------------------------------------------+---------------
2020-08-05T02:18:36.114Z|car-web-pc-api     |serialId=1987  |176            |/pc/web/api/v1/getrecommendserialbyserialid|10.244.42.194  
2020-08-05T07:03:32.249Z|car-web-pc-api     |serialId=6044  |164            |/pc/web/api/v1/getrecommendserialbyserialid|10.244.42.194  
2020-08-05T00:17:41.124Z|car-web-pc-api     |serialId=4452  |162            |/pc/web/api/v1/getrecommendserialbyserialid|10.244.42.194  
2020-08-05T01:30:00.670Z|car-web-pc-api     |serialId=5642  |161            |/pc/web/api/v1/getrecommendserialbyserialid|10.244.42.194  
2020-08-05T09:22:55.332Z|car-web-pc-api     |serialId=6188  |160            |/pc/web/api/v1/getrecommendserialbyserialid|10.244.41.88   
2020-08-05T07:34:42.856Z|car-web-pc-api     |serialId=4854  |158            |/pc/web/api/v1/getrecommendserialbyserialid|10.244.42.194  
2020-08-05T07:12:41.605Z|car-web-pc-api     |serialId=5772  |156            |/pc/web/api/v1/getrecommendserialbyserialid|10.244.41.88   
2020-08-05T03:47:10.815Z|car-web-pc-api     |serialId=5217  |154            |/pc/web/api/v1/getrecommendserialbyserialid|10.244.42.194  
2020-08-05T07:56:34.936Z|car-web-pc-api     |serialId=3901  |154            |/pc/web/api/v1/getrecommendserialbyserialid|10.244.41.88   
2020-08-05T07:20:34.482Z|car-web-pc-api     |serialId=2120  |153            |/pc/web/api/v1/getrecommendserialbyserialid|10.244.42.194

查询方式二：

curl -X POST -H 'Content-Type: application/json' -i 'http://192.168.87.203:9200/_xpack/sql?format=txt' --data '{
  "query":"select * from test2"
}'

返回数据：

HTTP/1.1 200 OK
Took-nanos: 20477000
Warning: 299 Elasticsearch-7.3.1-4749ba6 "[POST /_xpack/sql] is deprecated! Use [POST /_sql] instead."
content-type: text/plain
content-length: 144

   info.age    |   info.sex    |     name
---------------+---------------+---------------
28             |man            |wangzhen2

那如果要进行模糊搜索呢，Elasticsearch 的搜索能力大家都知道，强！在 SQL 里面，可以用 match 关键字来写，如下：

//不好使
POST /_xpack/sql?format=txt
{
    "query": "SELECT * FROM test2 WHERE match(info.sex, 'ma')"
}

DESCRIBE/DESC/SHOW COLUMNS
描述一个索引的定义，类似GET /{index}/{type}/_mappings ，查看该索引有多少字段，字段的类型。

//方式一
POST /_xpack/sql?format=txt
{
    "query": "desc test2"
}

//方式二
POST /_xpack/sql?format=txt
{
    "query": "SHOW COLUMNS IN test2"
}

#! Deprecation: [POST /_xpack/sql] is deprecated! Use [POST /_sql] instead.
     column     |     type      |    mapping    
----------------+---------------+---------------
info            |STRUCT         |object         
info.age        |BIGINT         |long           
info.sex        |VARCHAR        |text           
info.sex.keyword|VARCHAR        |keyword        
name            |VARCHAR        |text           
name.keyword    |VARCHAR        |keyword

SHOW FUNCTIONS
展示支持的SQL函数

POST /_xpack/sql?format=txt
{
    "query": "SHOW FUNCTIONS"
}

//支持通配符
POST /_xpack/sql?format=txt
{
    "query": "SHOW FUNCTIONS 'S__'"
}

#! Deprecation: [POST /_xpack/sql] is deprecated! Use [POST /_sql] instead.
      name       |     type      
-----------------+---------------
AVG              |AGGREGATE      
COUNT            |AGGREGATE      
FIRST            |AGGREGATE      
FIRST_VALUE      |AGGREGATE      
LAST             |AGGREGATE      
LAST_VALUE       |AGGREGATE      
MAX              |AGGREGATE      
MIN              |AGGREGATE      
SUM              |AGGREGATE      
KURTOSIS         |AGGREGATE      
MAD              |AGGREGATE      
PERCENTILE       |AGGREGATE      
PERCENTILE_RANK  |AGGREGATE      
SKEWNESS         |AGGREGATE      
STDDEV_POP       |AGGREGATE      
SUM_OF_SQUARES   |AGGREGATE      
VAR_POP          |AGGREGATE      
HISTOGRAM        |GROUPING 
...

SHOW TABLES
展示集群中的index 信息

POST /_xpack/sql?format=txt
{
    "query": "SHOW tables"
}

//支持通配符
POST /_xpack/sql?format=txt
{
    "query": "SHOW TABLES 'twit%'"
}

POST /_xpack/sql?format=txt
{
    "query": "SHOW TABLES 'twitte_'"
}

#! Deprecation: [POST /_xpack/sql] is deprecated! Use [POST /_sql] instead.
            name            |     type      |     kind      
----------------------------+---------------+---------------
.kibana                     |VIEW           |ALIAS          
.kibana_1                   |BASE TABLE     |INDEX          
.kibana_task_manager        |BASE TABLE     |INDEX          
ecommerce                   |VIEW           |ALIAS          
flights                     |VIEW           |ALIAS          
kibana_sample_data_ecommerce|BASE TABLE     |INDEX          
kibana_sample_data_flights  |BASE TABLE     |INDEX          
kibana_sample_data_logs     |BASE TABLE     |INDEX          
lead                        |BASE TABLE     |INDEX          
logstash                    |VIEW           |ALIAS          
logstash-2019.10.30-000001  |BASE TABLE     |INDEX          
logstash-2019.11.29-000002  |BASE TABLE     |INDEX          
logstash-2019.12.29-000003  |BASE TABLE     |INDEX          
logstash-2020.01.28-000004  |BASE TABLE     |INDEX          
logstash-2020.02.27-000005  |BASE TABLE     |INDEX          
logstash-2020.03.28-000006  |BASE TABLE     |INDEX          
logstash-2020.05.08-000007  |BASE TABLE     |INDEX          
logstash-2020.06.07-000008  |BASE TABLE     |INDEX          
logstash-2020.07.07-000009  |BASE TABLE     |INDEX          
monitor_1                   |BASE TABLE     |INDEX          
my_index                    |BASE TABLE     |INDEX          
test2                       |BASE TABLE     |INDEX

最后一个小贴士，如果你的索引名称包含横线，如 logstash-201811，只需要做一个用双引号包含，对双引号进行转义即可，如下：

POST /_xpack/sql?format=txt
{
"query":"SELECT COUNT(*) FROM \"logstash-*\""
}

ES的SQL功能 2020-08-05

声明

概念对应

SQL查询方式

SQL查询的本质

支持的SQL命令

推荐阅读更多精彩内容