文档章节

Elasticsearch通关教程(五):如何通过SQL查询Elasticsearch

SEOwhywhy
 SEOwhywhy
发布于 2018/12/10 08:54
字数 3717
阅读 22
收藏 3

  这篇博文本来是想放在全系列的大概第五、六篇的时候再讲的,毕竟查询是在索引创建、索引文档数据生成和一些基本概念介绍完之后才需要的。当前面的一些知识概念全都讲解完之后再讲解查询是最好的,但是最近公司项目忙经常加班,毕竟年底了。但是不写的话我怕会越拖越久,最后会不了了之了,所以刚好上海周末下雪,天冷无法出门,就坐在电脑前敲下了这篇博文。因为公司的查询这块是我负责的所以我研究了比较多点,写起来也顺手些。那么进入正文。
  
  2|0为什么用SQL查询
  
  前面的文章介绍过,Elasticsearch 的官方查询语言是 Query DSL,既然是官方指定的,说明最吻合 ES 的强大功能,为ES做支撑。那么我们为什么还用 SQL 查询?这是否是多此一举了呢?
  
  其实,存在毕竟有存在的道理,存在即合理。SQL 作为一个数据库查询语言,它语法简洁,书写方便而且大部分服务端程序员都清楚了解和熟知它的写法。但是作为一个 ES 萌新来说,就算他已经是一位编程界的老江湖,但是如果他不熟悉 ES ,那么他如果要使用公司已经搭好的 ES 服务,他必须要先学习 Query DSL,学习成本也是一项影响技术开发进度的因素而且不稳定性高。但是如果 ES 查询支持 SQL的话,那么也许就算他是工作一两年的同学,他虽然不懂 ES的复杂概念,他也能很好的使用 ES 而且顺利的参加到开发的队伍中,毕竟SQL 谁不会写呢?
  
  3|0Elasticsearch-SQL
  
  我们正式介绍下我们的主角 - Elasticsearch-SQL,Elasticsearch-SQL不属于 Elasticsearch 官方的,它是 NLPChina(中国自然语言处理开源组织)开源的一个 ES 插件,主要功能是通过 SQL 来查询 ES,其实它的底层是通过解释 SQL,将SQL 转换为 DSL 语法,再通过DSL 查询。
  
  Elasticsearch-SQL目前已经支持大概所有版本的 ES,而且最近的6.5.x的也在支持的范围了,所以可以看得出来维护的还是蛮频繁的。
  
  4|0安装插件
  
  由于 ES 2.x 和 5.x 的版本区别(详细参考:版本选择),我们安装 ES 插件是有点区别的,
  
  在 5.0之前的安装方式为:plugin install
  
  ./bin/plugin install https://github.com/NLPchina/elasticsearch-sql/releases/download/2.4.6.0/elasticsearch-sql-2.4.6.0.zip
  
  在5.0之后(包括6.x)的安装方式为:elasticsearch-plugin install
  
  ./bin/elasticsearch-plugin install https://github.com/NLPchina/elasticsearch-sql/releases/download/5.0.1/elasticsearch-sql-5.0.1.0.zip
  
  如果我们安装不成功,我们可以直接下载 Elasticsearch-SQL 插件的压缩包,然后解压,完成之后重命名文件夹为 sql ,放到 ES 的安装路径的 plugins目录中,例如:..\elasticsearch-6.4.0\plugins\sql。
  
  完成此操作后,需要重新启动Elasticsearch服务器,否则会报错:Invalid index name [sql], must not start with '']; ","status":400}。
  
  5|0前端可视化界面
  
  Elasticsearch-SQL 插件提供了可视化的界面,方便你执行SQL查询,界面如下:
  
  在 elasticsearch 1.x / 2.x,你可以直接访问如下地址:
  
  http://localhost:9200/_plugin/sql/
  
  而在 elasticsearch 5.x/6.x,这需要安装 node.js 和下载及解压site,然后像这样启动web前端:
  
  cd site-server
  
  npm install express --save
  
  node node-server.js
  
  6|0查询语法
  
  经过以上的操作之后,如果没出问题,现在就可以使用 SQL 查询 ES 了,其中有些是正常的 SQL 语法,还有些是超越SQL 语法的,相当于是对 SQL 语法的增强,ES 的查询格式是:
  
  http://localhost:9200/_sql?sql=select * from indexName limit 10
  
  6|1简单查询
  
  先上个简单的查询语法:
  
  SELECT fields from indexName WHERE conditions
  
  可以看到,我们以前的查询语句中,表名 tableName 的地方现在改为了索引名 indexName,如果有索引Type ,还可以这样写:
  
  SELECT fields from indexName/type WHERE conditions
  
  也可以同时查询索引的多个类型,语法如下:
  
  SELECT fields from indexName/type1,indexName/type2 WHERE conditions
  
  如果想知道当前SQL是如何将SQL解释为Elasticsearch 的Query DSL,可以这样通过关键字explain。
  
  http://localhost:9200/_sql/_explain?sql=select * from indexName limit 10
  
  聚合类函数查询
  
  select COUNT(*),SUM(age),MIN(age) as m, MAX(age),AVG(age)
  
  FROM bank GROUP BY gender ORDER BY SUM(age), m DESC
  
  6|2额外增强查询
  
  Search
  
  SELECT address FROM bank WHERE address = matchQuery('880 Holmes Lane') ORDER BY _score DESC LIMIT 3
  
  Aggregations
  
  range age group 20-25,25-30,30-35,35-40
  
  SELECT COUNT(age) FROM bank GROUP BY range(age, 20,25,30,35,40)
  
  range date group by day
  
  SELECT online FROM online GROUP BY date_histogram(field='insert_time','interval'='1d')
  
  range date group by your config
  
  SELECT online FROM online GROUP BY date_range(field='insert_time','format'='yyyy-MM-dd' ,'2014-08-18','2014-08-17','now-8d','now-7d','now-6d','now')
  
  6|3地理查询
  
  Elasticsearch 可以把地理位置、全文搜索、结构化搜索和分析结合到一起。而Elasticsearch-sql 也基本支持所有地理位置相关的查询,对应 Elasticsearch的章节内容为Geolocation。
  
  1、地理坐标盒模型过滤器
  
  地理坐标盒模型过滤器(Geo Bounding Box Filter),指定一个矩形的顶部,底部,左边界和右边界,然后过滤器只需判断坐标的经度是否在左右边界之间,纬度是否在上下边界之间。
  
  语法:
  
  GEO_BOUNDING_BOX(fieldName,topLeftLongitude,topLeftLatitude,bottomRightLongitude,bottomRightLatitude)
  
  示例:
  
  SELECT * FROM location WHERE GEO_BOUNDING_BOX(center,100.0,1.0,101,0.0)
  
  2、地理距离过滤器
  
  地理距离过滤器( geo_distance ),以给定位置为圆心画一个圆,来找出那些地理坐标落在指定距离范围的文档。
  
  语法:
  
  GEO_DISTANCE(fieldName,distance,fromLongitude,fromLatitude)
  
  示例:
  
  SELECT * FROM location WHERE GEO_DISTANCE(center,'1km',100.5,0.5)
  
  3、地理距离区间过滤器
  
  范围距离过滤器(Range Distance filter),以给定位置为圆心,分别以两个给定的距离画圆,找出与指定点距离在给定最小距离和最大距离之间的点,和geo_distance filter的唯一差别在于Range Distance filter是一个环状的,它会排除掉落在内圈中的那部分文档。
  
  语法:
  
  GEO_DISTANCE_RANGE(fieldName,distanceFrom,distanceTo,fromLongitude,fromLatitude)
  
  示例:
  
  SELECT * FROM location WHERE GEO_DISTANCE_RANGE(center,'1m','1km',100.5,0.50001)
  
  4、Polygon filter (works on points)
  
  找出落在多边形中的点。 这个过滤器使用代价很大 。当你觉得自己需要使用它,最好先看看 geo-shapes 。
  
  语法:
  
  GEO_POLYGON(fieldName,lon1,lat1,lon2,lat2,lon3,lat3,...)
  
  示例:
  
  SELECT * FROM location WHERE GEO_POLYGON(center,100,0,100.5,2,101.0,0)
  
  5、GeoShape Intersects filter (works on geoshapes)
  
  这里需要使用WKT表示查询时的形状。
  
  语法:
  
  GEO_INTERSECTS(fieldName,'WKT')
  
  示例:
  
  SELECT * FROM location WHERE GEO_INTERSECTS(place,'POLYGON ((102 2, 103 2, 103 3, 102 3, 102 2))
  
  更多关于地理的查询可以参考这里。
  
  7|0实战用法
  
  我们以本系列的第一篇教程中我们创建的索引 nba来作示例,如下:
  
  1、查询 nba 所有球队信息
  
  http://localhost:9200/_sql?sql=select * from nba limit 10
  
  查询结果:
  
  2、查询当家球星是詹姆斯的球队信息
  
  http://localhost:9200/_sql?sql=select * from nba where topStar  = "勒布朗·詹姆斯"
  
  查询结果:
  
  3、根据建队时间降序排列
  
  http://localhost:9200/_sql?sql=select * from nba order by date desc
  
  查询结果:
  
  4、查询拥有总冠军超过5个的球队信息
  
  http://localhost:9200/_sql?sql=select * from nba where championship  >= 5
  
  查询结果:
  
  5、查询总冠军数量分别在1-5,5-10,10-15,15-20范围之间球队的数量
  
  http://localhost:9200/_sql?sql=SELECT COUNT(championship) FROM nba GROUP BY range(championship, 1,5,10,15,20)
  
  查询结果:
  
  当然还有更多的写法,具体实现在这里就不多诉了,感兴趣的读者可以自己搭建个项目然后尝试下,更多特色SQL写法可以参考这里:
  
  基本条件查询
  
  地理查询
  
  聚合查询
  
  额外SQL功能
  
  Scan and scroll
  
  功能有限的连接查询
  
  Show Commands
  
  Script Fields
  
  NestedTypes support
  
  Union & Minus support
  
  8|0Java实现
  
  上面已经介绍了 Elasticsearch-SQL的安装和使用,那么我们如何在项目中使用它,Elasticsearch-SQL底层是使用Java语言开发的,通过解析SQL 转换为 DSL 语言,然后得出查询结果,解析结果成key-value的固定格式返回。
  
  8|1引入依赖
  
  使用前我们需要先引入maven依赖
  
  <dependency>
  
  <groupId>org.nlpcn</groupId>
  
  <artifactId>elasticsearch-sql</artifactId>
  
  <version>x.x.x.0</version>
  
  </dependency>
  
  版本号(x.x.x)需要和 Elasticsearch的版本对应上,具体的对应关系大致可以参考下图:
  
  但是不是所有的版本,我们都可以从Maven Repository里获取到,我们如果直接从Maven 仓库里面只能获取如下几个版本的依赖,其中缺少很多版本:
  
  那如果我们使用的是其他版本的 ES 如何解决依赖 jar包问题呢?还记得我们开始下载插件解压后的sql文件夹吗?例如6.5.0版本的插件的解压后文件夹内容如下:
  
  这里面就有我们需要的 jar包,有了 jar包就好办了,我们可以直接加入到项目中,当然最好的方式是上传到公司的私有仓库里面,然后通过pom文件依赖进来。
  
  8|2搭建项目
  
  jar包问题解决之后就可以正式进入开发阶段了,新建一个springboot项目,引入各项依赖,一切准备就寻后,如何连接ES呢?
  
  这里有两种方式可以实现我们的功能,一个是通过JDBC的方式,连接数据库一样连接ES。还有一种就是通过 tansport client 方式。
  
  JDBC的方式
  
  代码示例
  
  public void testJDBC() throws Exception {
  
  Properties properties = new Properties();
  
  properties.put("url", "jdbc:elasticsearch://192.168.3.31:9300,192.168.3.32:9300/" + TestsConstants.TEST_INDEX);
  
  DruidDataSource dds = (DruidDataSource) ElasticSearchDruidDataSourceFactory.createDataSource(properties);
  
  Connection connection = dds.getConnection();
  
  PreparedStatement ps = connection.prepareStatement("SELECT  gender,lastname,age from  " + TestsConstants.TEST_INDEX + " where lastname='Heath'");
  
  ResultSet resultSet = ps.executeQuery();
  
  List<String> result = new ArrayList<String>();
  
  while (resultSet.next()) {
  
  System.out.println(resultSet.getString("lastname") + "," + resultSet.getInt("age") + "," + resultSet.getString("gender"))
  
  }
  
  ps.close();
  
  connection.close();
  
  dds.close();
  
  }
  
  这种方式是最直观的,用到了Druid连接池,所以我们还需要在项目中引入druid依赖,而且需要注意依赖的版本,否则会报错。
  
  <dependency>
  
  <groupId>com.alibaba</groupId>
  
  <artifactId>druid</artifactId>
  
  <version>1.0.15</version>
  
  </dependency>
  
  这种方式很好理解,而且开发也方便,但是我在项目中应用了发现它有很多不足,所以我最后还是自己看了下源码,通过API的方式重新封装调用。
  
  API方式
  
  其实 elasticsearch-sql 没有提供开发的 文档,并没有介绍如何通过调用 Java API方式开发,我们需要阅读 elasticsearch-sql 的源代码来发现它的service,然后包装成我们需要的,通过阅读源码我们发现了如下一个功能明显的Service类。
  
  public class SearchDao {
  
  private static final Set<String> END_TABLE_MAP = new HashSet<>();
  
  static {
  
  END_TABLE_MAP.add(www.thd540.com"limit");
  
  END_TABLE_MAP.add(www.furggw.com"order");
  
  END_TABLE_MAP.add("where");
  
  END_TABLE_MAP.add("group");
  
  }
  
  private Client client = null;
  
  public SearchDao(Client client) {
  
  this.client = client;
  
  }
  
  public Client getClient(www.mhylpt.com/ ) {
  
  return client;
  
  }
  
  /**
  
  * Prepare action And transform sql
  
  * into ES ActionRequest
  
  * @param sql SQL query to execute.
  
  * @return ES request
  
  * @throws SqlParseException
  
  */
  
  public QueryAction explain(String sql) throws SqlParseException, SQLFeatureNotSupportedException {
  
  return ESActionFactory.create(www.dasheng178.com/   client, sql);
  
  }
  
  }
  
  SearchDao 类中有一个explain方法,接收的参数就是一个字符串sql ,返回结果是 QueryAction ,QueryAction 是一个抽象类,它又有如下子类
  
  可以看出,每个子类对应的就是一个查询的功能,聚合查询,默认查询,删除,哈希连接查询,连接查询,嵌套查询等等。
  
  获得的 QueryAction 我们可以通过 QueryActionElasticExecutor类的executeAnyAction方法来接受,并内部处理,然后就能获得相应的执行结果。
  
  public static Object executeAnyAction(Client client , QueryAction queryAction) throws SqlParseException, IOException {
  
  if(queryAction instanceof DefaultQueryAction)
  
  return executeSearchAction(www.mcyllpt.com/ (DefaultQueryAction) queryAction);
  
  if(queryAction instanceof AggregationQueryAction)
  
  return executeAggregationAction((AggregationQueryAction) queryAction);
  
  if(queryAction instanceof ESJoinQueryAction)
  
  return executeJoinSearchAction(client, www.yigouyule2.cn  (ESJoinQueryAction) queryAction);
  
  if(queryAction instanceof MultiQueryAction)
  
  return executeMultiQueryAction(client, (MultiQueryAction) queryAction);
  
  if(queryAction instanceof DeleteQueryAction )
  
  return executeDeleteAction((DeleteQueryAction) queryAction);
  
  return null;
  
  }
  
  虽然得到了查询结果,但是它是一个Object类型,我们还需要定制化一下,注意到了一个类:ObjectResultsExtractor,它的构造函数如下,构造函数包含三个布尔类型的参数。它们的作用是在结果集中是否包含score,是否包含type,是否包含ID,我们可以都设置为 false。
  
  public ObjectResultsExtractor(boolean includeScore, boolean includeType, boolean includeId) {
  
  this.includeScore = includeScore;
  
  this.includeType = includeType;
  
  this.includeId = includeId;
  
  this.currentLineIndex = 0;
  
  }
  
  ObjectResultsExtractor它仅有一个对外的 pulic 修饰的方法extractResults。
  
  public ObjectResult extractResults(Object queryResult, boolean flat) throws ObjectResultsExtractException {
  
  if (queryResult instanceof SearchHits) {
  
  SearchHit[] hits = ((SearchHits) queryResult).getHits();
  
  List<Map<String, Object>www.michenggw.com/> docsAsMap = new ArrayList<>();
  
  List<String> headers = createHeadersAndFillDocsMap(flat, hits, docsAsMap);
  
  List<List<Object>> lines = createLinesFromDocs(flat, docsAsMap, headers);
  
  return new ObjectResult(headers, lines);
  
  }
  
  if (queryResult instanceof Aggregations) {
  
  List<String> headers = new ArrayList<>();
  
  List<List<Object>> lines = new ArrayList<>();
  
  lines.add(new ArrayList<Object>());
  
  handleAggregations((Aggregations) queryResult, headers, lines);
  
  // remove empty line。
  
  if(lines.get(0).size(www.gcyL157.com) == 0) {
  
  lines.remove(0);
  
  }
  
  //todo: need to handle more options for aggregations:
  
  //Aggregations that inhrit from base
  
  //ScriptedMetric
  
  return new ObjectResult(headers, lines);
  
  }
  
  return null;
  
  }
  
  至此我们就大致了解了它的查询API ,然后我们只需要在我们项目中做如下的代码调用就可以完成我们的查询功能了,最后得到的ObjectResult就是我们的最终查询结果集了。
  
  //1.解释SQL
  
  SearchDao searchDao = new SearchDao(transportClient);
  
  QueryAction queryAction = searchDao.explain(sql);
  
  //2.执行
  
  Object execution = QueryActionElasticExecutor.executeAnyAction(searchDao.getClient(), queryAction);
  
  //3.格式化查询结果
  
  ObjectResult result =www.yongshiyule178.com  (new ObjectResultsExtractor(true, false, false)).extractResults(execution, true);
  
  至此,代码开发完成,我们来测试下运行结果,我对外提供了三个接口,一个是 API方式查询,一个是JDBC方式查询,还有一个解释SQL。
  
  @RestController
  
  @RequestMapping("/es/data")
  
  public class ElasticSearchController {
  
  @Autowired
  
  private ElasticSearchSqlService elasticSearchSqlService;
  
  @PostMapping(value = "/search")
  
  public CommonResult search(@RequestBody QueryDto queryDto) {
  
  SearchResultDTO resultDTO = elasticSearchSqlService.search(queryDto.getSql());
  
  return CommonResult.success(resultDTO.getResult());
  
  }
  
  @PostMapping(value = "/query")
  
  public CommonResult query(@RequestBody QueryDto queryDto) {
  
  SearchResultDTO resultDTO = elasticSearchSqlService.query(queryDto.getSql(), queryDto.getIndex());
  
  return CommonResult.success(resultDTO.getResult());
  
  }
  
  @PostMapping(value = "/explain")
  
  public CommonResult explain(@RequestBody QueryDto queryDto) {
  
  return CommonResult.success(elasticSearchSqlService.explain(queryDto.getSql()));
  
  }
  
  }
  
  请求示例:
  
  查询结果示例:
  
  9|0总结
  
  SQL 虽然不是 ES 官方推荐的查询语言,但是由于他的便捷性,ES 官方也开始意识到这块。ES 在 6.3.0版本后也开始支持 SQL了,但是他是通过引入 x-pack 的方式,如归我们可以通过 REST 方式使用,但是我们引入到开发中还是有点问题,需要铂金会员才行,不知道以后会不会放开。
  
  另外,SQL 虽然使用起来比较方便,但是毕竟不是官方指定的,所以难免在功能上有缺陷,没有 DSL 功能强大,而且里面的坑比较多,但是基本的查询都支持。所以如果不是迫不得已,我还是建议使用 DSL,而一些简单的操作可以用SQL来辅助,本篇文章源码都已上传到本人的 Github ,如果感兴趣的读者可以关注我的 Github。

© 著作权归作者所有

共有 人打赏支持
SEOwhywhy
粉丝 3
博文 84
码字总数 174846
作品 0
私信 提问
Elastic 在年度用户大会 Elastic{ON} 2018 上发布众多新功能和技术预览

下载超过 2.25 亿次,Elastic 公开 X-Pack 源代码 旧金山 (Elastic{ON} 2018) – 2018 年 2 月 27 日 – Elastic,Elasticsearch 和 Elastic Stack背后的公司,今天宣布其产品累计下载次数达...

Medcl
2018/03/01
0
0
Elasticsearch如何实现 SQL语句中 Group By 和 Limit 的功能

有 SQL 背景的同学在学习 Elasticsearch 时,面对一个查询需求,不由自主地会先思考如何用 SQL 来实现,然后再去想 Elasticsearch 的 Query DSL 如何实现。那么本篇就给大家讲一条常见的 SQ...

rockybean
2018/05/21
0
0
小白都会超详细--ELK日志管理平台搭建教程

目录 一、介绍 二、安装JDK 三、安装Elasticsearch 四、安装Logstash 五、安装Kibana 六、Kibana简单使用 系统环境:CentOS Linux release 7.4.1708 (Core) 当前问题状况 开发人员不能登录线...

渣渣辉
2018/07/15
0
0
Elastic 南京 Meetup

1. 主办方 Elastic中文社区 趋势科技 2. 时间地点 活动时间:2018年6月30日 13:00 - 18:00 活动地点:雨花区软件大道48号苏豪国际广场B座 趋势科技中国研发中心(靠花神庙地铁站) 3. 报名地...

Medcl
2018/06/04
69
0
centos 7( linux )下安装elasticsearch教程

目录 概述 环境准备 elaticsearch简介 安装elasticsearch 彩蛋 概述 很久没有写博客了,最近在做全文检索的项目,发现elasticsearch踩了不少坑,百度点进去又是坑,在此记录一下自己的踩坑历程。...

java_龙
2018/10/15
0
0

没有更多内容

加载失败,请刷新页面

加载更多

开始看《JSP&Servlet学习笔记》

1:WEB应用简介。其中1.2.1对Web容器的工作流程写得不错 2:编写Servlet。搞清楚了Java的Web目录结构,以及Web.xml的一些配置作用。特别是讲了@WebServlet标签 3:请求与响应。更细致的讲了从...

max佩恩
43分钟前
1
0
mysql分区功能详细介绍,以及实例

一,什么是数据库分区 前段时间写过一篇关于mysql分表的的文章,下面来说一下什么是数据库分区,以mysql为例。mysql数据库中的数据是以文件的形势存在磁盘上的,默认放在/mysql/data下面(可...

吴伟祥
44分钟前
2
0
SQL语句查询

1.1 排序 通过order by语句,可以将查询出的结果进行排序。放置在select语句的最后。 格式: SELECT * FROM 表名 ORDER BY 排序字段ASC|DESC; ASC 升序 (默认) DESC 降序 1.查询所有商品信息,...

stars永恒
今天
2
0
IntelliJ IDEA 第一个 Scala 程序

IntelliJ 安装完成 Scala 插件后,你需要尝试使用 IntelliJ 来创建并且运行第一个程序。 通常这个程序只是简单的输出 Hello World。 创建一个新工程 在文件下面选择新建,然后选择创建工程。...

honeymose
今天
2
0
csapp 习题 - 如何实现异或 exclusive-or

阅读 csapp v3 时,练习题 2.13 很有意思。练习题描述如下。 位设置是对于参数 mask 中每一个为 1 的位,那么参数 x 中相应位则被设置为 1 ;位清除是对于参数 mask 中每一个为 1 的位,那么...

ylme
昨天
6
0

没有更多内容

加载失败,请刷新页面

加载更多

返回顶部
顶部