Elasticsearch SQL

介绍

ES SQL 是x-pack的一个组件，它提供了一个到Elasticsearch的SQL接口，可以针对ES索引执行实时的SQL查询，轻松实时大规模的查询和处理数据，并以表格格式返回结果。它相当于一种翻译器，使用将sql语句转换为DSL语句，再搜索elasticsearch数据

SQL与Elasticsearch的映射关系

（只列常用的）

在 Elasticsearch 中，可用的索引集被分组在一个cluster，一个实例只有一个目录 |

SELECT语法

​ 同sql语法基本一致，基本所有的sql语法都支持

# select
SELECT [TOP [ count ] ] select_expr [, ...]
[ FROM table_name ]
[ WHERE condition ]
[ GROUP BY grouping_element [, ...] ]
[ HAVING condition]
[ ORDER BY expression [ ASC | DESC ] [, ...] ]
[ LIMIT [ count ] ]
[ PIVOT ( aggregation_expr FOR column IN ( value [ [ AS ] alias ] [, ...] ) ) ]

注意：

  1. FROM :目前只支持一张表，不支持连表查询，table_name可以是一个索引，也可以是一个模式（子语句、正则匹配的索引）
  2. WHERE: 从查询中过滤行，如果指定了子句，所有不满足条件的都将从输出中删除
  3. GROUP BY：如果指定了子句或者存在聚合函数调用，则输出将组合成匹配一个或多个值的行组，并计算聚合函数的结果，如果该HAVING子句存在，它将消除不满足给定条件的组
  4. 使用SELECT每个选定行或行组的输出表达式计算实际输出行，可以使用通配符*返回所有的列
  5. 如果ORDER BY指定了子句，则返回的行按指定的顺序排序。如果ORDER BY未给出，则以系统发现最快生成的任何顺序返回行
  6. 如果指定了LIMITor TOP（不能在同一个查询中同时使用两者），则该SELECT语句仅返回结果行的一个子集

缺点：

  1. 不支持复杂的sql： select count(distinct(field1, field2))，可以使用group by替代
  2. 但不支持连表查询，需分表查询

ES SQL 执行方式

  1. 在kabana console 面板中执行sql语句（经常用于调试）
// 方式一：直接搜索sql
GET _sql?format=json
{
"query": """
SELECT *  FROM "mysql-shop_trades-order_statics" where store_id = 165
"""
}

// 方式二：先将sql转换为DSL语句，再通过DSL语句查询结果，这样得到的数据格式比较清晰
// sql转换成DSL语句
GET _sql/translate
{
"query": """
SELECT *  FROM "mysql-shop_trades-order_statics" where store_id = 165
"""
}

// 使用DSL语句查询

GET /mysql-shop_trades-order_statics/_search
{
"size" : 100,
"query" : {
"term" : {
"store_id" : {
"value" : 165,
"boost" : 1.0
}
}
},
"sort" : [
{
"_doc" : {
"order" : "asc"
}
}
]
}

上述是查询一个简单的数据

  1. format：返回数据的格式，支持csv、json、tsv、text、yaml、cbor、smile
  2. sql搜索， 不支持 ， - .等特殊字符，因此需要转移，包裹sql使用""" 会自动转移特殊字符
  3. 在客户端使用SQL CLI执行
$ ./bin/elasticsearch-sql-cli http://192.168.3.53:9200
sql> select id from "mysql-shop_trades-order_statics" order by id asc limit 2;
  1. 使用 elasticsearch-PHP插件执行 （在业务中使用）
// 读取sql语句，不执行
$where = [
'is_deleted' => 0,
'label_type' => $type,
];
$this->dblink_trade_slave->select('bar_code')->where($where);
if ($is_having) {
$this->dblink_trade_slave->having($having);
}
if ($label_ids) {
$this->dblink_trade_slave->where_in('bind_item_label_id', $label_ids);
}
$sql = $this->dblink_trade_slave->limit(1)->get_compiled_select($index);

// 使用es搜索
$index = '"mysql-shop_trades-order_item_label_binds"'; // 索引需要加引号来转义特殊字符
$params = [
'body' => [
'query' => $sql,
'fetch_size' => 20 // 返回数据条数
]
];
$result = EsClient::sql($params);
// todo 处理结果

Elasticsearch6.3+后，开始支持SQL查询语言，但6.7之前SQL都是实验性质的，6.6进入beta特性，6.7后官方正常正式支持，因此elasticsearch-php 7.0+版本才支持查询x-pack sql，低版本不支持sql查询

需服务器手动安装elasticsearch-sql插件

./bin/elasticsearch-plugin install https://github.com/NLPchina/elasticsearch-sql/releases/download/6.7.0.0/elasticsearch-sql-6.7.0.0.zip

发表评论