声明
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-*\""
}
