ElasticsearchSQL是一个X-Pack组件，允许用户使用类似SQL的语法在ES中进行查询。用户可以在REST、JDBC、命令行中使用SQL对ES进行数据检索和数据聚合操作。ESSQL具有以下特点：本地集成，SQL模块由ES自己构建，直接集成到发布版本中。不需要外部组件，使用SQL模块不需要额外的硬件、运行时库等依赖。轻量高效，SQL模块没有抽象ES及其搜索能力，而是暴露了SQL接口，允许适当的全-以同样的声明式、简洁的方式进行文本搜索。在接下来的内容中，我们将学习基于ES7.13的ElasticsearchSQL模块所提供的功能。如果你对ES感兴趣，欢迎订阅我的Elasticsearch小册子从入门到实践，一起学习进步！1、使用ElasticsearchSQL在开始使用SQL模块提供的功能之前，在kibana中执行如下命令创建数据：PUT/library/_bulk?refresh{"index":{"_id":"LeviathanWakes"}}{"name":"LeviathanWakes","author":"JamesS.A.Corey","release_date":"2011-06-02","page_count":561}{"index":{"_id":"Hyperion"}}{"name":"Hyperion","author":"DanSimmons","release_date":"1989-05-26","page_count":482}{"index":{"_id":"Dune"}}{"name":"Dune","author":"FrankHerbert","release_date":"1965-06-01","page_count":604}导入数据后，可以执行如下SQL用于数据搜索A:POST/_sql?format=txt{"query":"SELECT*FROMlibraryWHERErelease_date<'2000-01-01'"}如上例，使用_sql表示使用SQL模块，在查询字段指定要执行的SQL语句，使用format指定返回数据的格式。http头描述csvtext/csv逗号分隔的jsonapplication/jsonJson格式tsvtext/tab-separated-valuestab分隔的txttext/纯文本格式yamlapplication/yamlyamlcborapplication/cbor简洁的二进制对象表示格式smileapplication/smile类似于cborSQL上面的另一种二进制格式结果执行过程如下：更多返回格式大家可以自行尝试。除了直接执行SQL，还可以对结果进行过滤。使用过滤字段在参数中指定过滤条件。您可以使用标准的ESDSL查询语句来过滤SQL操作的结果。实际例子如下：POST/_sql?format=txt{"query":"SELECT*FROMlibraryORDERBYpage_countDESC","filter":{"range":{"page_count":{"gte":500,"lte":600}}},"fetch_size":5}如上例，结果为：author|姓名|页数|发布日期--------------+------------+-------------+----------------------JamesS.A.Corey|LeviathanWakes|561|2011-06-02T00:00:00.000Z此外，您可以使用“？”占位符传递参数，然后将参数和语句组装成一条完整的SQL语句：POST/_sql?format=txt{"query":"SELECTYEAR(release_date)ASyearFROMlibraryWHEREpage_count>?ANDauthor=?GROUPBYyearHAVINGCOUNT(*)>?","params":[300,"FrankHerbert",0]}如上例所示，使用'?'传递参数的占位符。2.传统SQL和ElasticsearchSQL概念映射尽管SQL和Elasticsearch在数据组织方面有不同的术语（和不同的语义），但它们在目的上本质上是相同的。下面是它们的映射关系表：SQLElasticsearch说明当columnfield在Elasticsearch字段中时，SQL将这样的条目称为列。注意，在Elasticsearch中，一个字段可以包含多个相同类型的值（本质上是一个列表），而在SQL中，一个列只能包含一个代表该类型的值。ElasticsearchSQL将尽最大努力保留SQL语义，并根据查询拒绝返回多个值的字段。rowdocument列和字段本身不存在；它们是行或文档的一部分。两者的语义略有不同：行往往是严格的（并且有更多的强制执行），而文档往往更灵活或更宽松（同时仍然具有结构）。SQL或Elasticsearch中tableindex查询的目标schema是隐式的在关系数据库中，schema主要是表的命名空间，通常用作安全边界。Elasticsearch没有为它提供等效的概念。尽管这些概念之间的映射在语义上有些不同，但它们之间的共同点多于差异点。3.SQLTranslateAPISQLTranslateAPI接收JSON格式的SQL语句，将其转换为ES的DSL查询语句，但不会执行该语句。我们可以使用此API将SQL转换为DSL语句。实际例子如下：POST/_sql/translate{"query":"SELECT*FROMlibraryORDERBYpage_countDESC","fetch_size":10}如上例，翻译后的DSL如下：{"size":10,"_source":false,"fields":[{"field":"author"},{"field":"name"},{"field":"page_count"},{"field":"release_date","format":"strict_date_optional_time_nanos"}],"sort":[{"page_count":{"order":"desc","missing":"_first","unmapped_type":"short"}}]}四、SQL语法介绍学习看看ES提供的SQL语法和语义。1、词法结构ESSQL的词法结构在很大程度上类似于ANSISQL本身。ESSQL目前一次只接受一个命令，其中命令是一系列由输入流结尾终止的标记。这些标记可以是关键字、标识符（带引号或不带引号）、文本（或常量）、特殊字符符号（通常是定界符）。Keywords这其实和我们写SQL语句定义关键字是一样的。比如SELECT、FROM等都是关键字。需要注意的是，关键字不区分大小写。SELECT*FROMmy_table如上例，一共有4个token：SELECT,,FROM,my_table，其中SELECT,,FROM是关键字，代表SQL中固定含义的词。而my_table是一个标识符，表示SQL中的实体，比如表，列等。可以看出关键字和标识符的词法结构是一样的，在SQL中几乎是一样的长度，有时很难区分.ESSQL支持的关键字有很多，这里就不一一列举了。可以参考官方文档。标识符有两种类型的标识符：带引号的和不带引号的。示例如下：SELECTip_addressFROM"hosts-*"如上例，查询中有两个标识符：不带引号的ip_address和带引号的hosts-*（通配符模式）。因为ip_address不和任何关键字冲突，所以可以不加引号。而hosts-*与-（减号运算）和*冲突，所以需要加引号。对于标识符，应尽量避免使用复杂的名称和与关键字冲突的名称，在键入时应使用引号作为标识符，这样可以消除歧义。直接常量ESSQL支持两种隐式类型常量：字符串和数字。字符串，字符串可以用单引号分隔，例如：'mysql'。如果字符串中包含单引号，则需要使用另一个单引号对其进行转义，例如：'CaptainEO''sVoyage'。数值常数，数值常数可以用十进制和科学记数法表示，例子如下：1969--整数记数法3.14--十进制记数法.1234--小数点开始的十进制记数法4E5--科学记数法（带指数标记）1.2e-3--带小数点的科学计数法包含小数点的数字常量将被解析为Double类型。如果适合解析为整数，则解析为Integer，否则解析为Long。单引号、双引号在SQL中，单引号和双引号的含义不同，不能互换使用。单引号用于声明字符串，而双引号用于表示标识符。示例如下：SELECT"first_name"FROM"musicians"WHERE"last_name"='Carroll'如上例，first_name、musicians、last_name都是标识符，用双引号括起来。而Carroll是一个字符串，使用单引号。特殊字符某些非数字和字母字符具有不同于运算符的特殊含义。特殊字符有：字符描述*在某些上下文中，表示数据表的所有字段，也可以表示某些聚合函数的参数。,用于枚举列表的元素。用于数字常量或定界标识符限定符（表、列等）()用于特定SQL命令、函数声明或强制执行优先级。运算符ESSQL中的大多数运算符具有相同的优先级并且是左关联的。如果您需要修改优先级，请使用括号强制更改其优先级。下表显示了ESSQL支持的运算符及其优先级：运算符关联性说明。Leftassociativequalifierorseparator::LeftassociativePostgreSQL-styletypeconversionoperator+-Rightassociativeunaryplusandminus*/%Left-associativemultiplication,division,modules+-left-associativeaddition,subtractionBETWEENINLIKErangeincludes,字符匹配<><=>==<=><>!=比较运算NOT右结合逻辑NOTAND左结合逻辑OR左组合逻辑或注释ESSQL支持两种注释：单行注释和多行注释,例子如下：--单行注释,单行注释/*支持嵌套注释的多行注释/*嵌套注释*/多行注释*/2,SQL命令下面将介绍SQL命令。DESCRIBETABLE使用该命令查看索引的结构，其语法如下：DESCRIBE[表标识符|[LIKEpattern]]在第1行中，关键字DESCRIBE可以缩写为DESC。第2行，单表标识或双引号ES多索引模式。第3行，SQLLike匹配模式。DESCRIBE命令的使用示例如下：DESCRIBEtable;SELECT其实我们很熟悉。使用SELECT返回要显示的列。语法如下：SELECT[TOP[count]]select_expr[,...][FROMtable_name][WHEREcondition][GROUPBYgrouping_element[,...]][HAVINGcondition][ORDERBYexpression[ASC|DESC][,...]][LIMIT[count]][PIVOT(aggregation_exprFORcolumnIN(value[[AS]alias][,...]))]在ES中使用SELECT查询的语法基本上是与数据库中使用的相同，这里不再赘述。SHOWCOLUMNS使用SHOWCOLUMNS命令列出表的所有列、它们的类型和其他属性。语法如下：SHOWCOLUMNS[FROM|在]？[表标识符|[LIKEpattern]]使用示例如下：SHOWCOLUMNSINemp;SHOWCOLUMNSINempLIKE'birth_da%';//匹配birth_da开头的列SHOWFUNCTIONS使用SHOWFUNCTIONS列出SQL支持的所有函数及其类型，LIKE子句匹配对应的结果。语法如下：SHOWFUNCTIONS[LIKEpattern?]?示例如下：SHOWFUNCTIONS;SHOWFUNCTIONSLIKE'ABS';//精确匹配SHOWFUNCTIONSLIKE'A__';//一个'_'表示一个字符，所以精确匹配A+两个字符，比如AVG，ABS。显示像“%DAY%”这样的函数；//与DAYSHOWTABLES匹配的函数我们可以使用SHOWTABLES查看所有表（ES中的索引），语法如下：SHOWTABLES[INCLUDEFROZEN]?[表标识符|[喜欢图案]]？一个简单的例子如下：SHOWTABLES;SHOWTABLES"*,-l*";//使用ES多目标语法匹配SHOWTABLESLIKE'emp';//精确匹配SHOWTABLESLIKE'emp%';//表匹配emp+多个字符SHOWTABLESLIKE'em_';//表匹配em+单个字符SHOWTABLESLIKE'%em_';//表索引匹配多字符+em+单字符模式ESSQL支持两种模式匹配方式来匹配多索引或表：多索引模式和LIKE模式。多索引模式支持通配符*或排他匹配，例如：SHOWTABLES"*,-l*";通过ES多目标语法支持多索引模式。LIKE模式大家都很熟悉，上面的例子也很多，这里就不赘述了。5、ESSQL的使用实践在使用SQL之前，我们首先要准备数据。这里我们将使用Kibana提供的航班数据：如上图，在Kibana中点击左侧栏Analytics下的Overview，在弹出的页面Tab中选择Sampledata，然后点击adddata按钮添加航班数据。可以使用如下语句查看航班数据的数据结构：POST/kibana_sample_data_flights/_search{"query":{"match_all":{}}}ok，我们来看普通SQL的写法。WHERE我们过滤掉目的地为美国的数据：POST/_sql?format=txt{"query":"SELECTFlightNum,OriginWeather,OriginCountry,CarrierFROMkibana_sample_data_flightsWHEREDestCountry='US'"}如上例，对于SQL数据查询你肯定不陌生，最后的结果是：GROUPBY你可以使用GROUPBY语句对数据进行分组聚合和统计操作，比如查询航班组的平均飞行距离。示例如下：POST/_sql?format=txt{"query":"SELECTcount(*),max(DistanceMiles),avg(DistanceMiles)FROMkibana_sample_data_flightsGROUPBYDestCountry"}如上例，我们分组依据目的地国家，然后统计每组人数，最大飞行距离，平均飞行距离。结果如下：HAVING可以使用HAVING对分组后的数据进行二次过滤，比如过滤分组中记录超过100条的数据，示例如下：POST/_sql?format=txt{"query":"SELECTcount(*),max(DistanceMiles),avg(DistanceMiles)FROMkibana_sample_data_flightsGROUPBYDestCountryHAVINGCOUNT(*)>100"}如上例，我们过滤掉组中记录大于100的数据，结果如下：ORDERBY我们可以使用ORDERBY排序，比如对平均飞行距离进行降序排序，示例如下：POST/_sql?format=txt{"query":"SELECTcount(*),max(DistanceMiles),avg(DistanceMiles)asavgDistanceFROMkibana_sample_data_flightsGROUPBYDestCountryHAVINGCOUNT(*)>100ORDERBYavgDistancedesc"}如上例，我们按照平均距离对数据进行排序，结果为:实现分页的方式有很多种，可以使用limit、top、fetch_size来进行分页。1.限制分页操作POST/_sql?format=txt{"query":"SELECTFlightNum,OriginWeather,OriginCountry,CarrierFROMkibana_sample_data_flightsWHEREDestCountry='US'limit10"}2.使用top进行分页POST/_sql?format=txt{“查询”：“从kibana_sample_data_flights中选择前10个FlightNum、OriginWeather、OriginCountry、CarrierWHEREDestCountry='US'”}3.使用fetch_size进行分页POST/_sql?format=txt{"query":"SELECTFlightNum,OriginWeather,OriginCountry,CarrierFROMkibana_sample_data_flightsWHEREDestCountry='US'","fetch_size":10}子查询ESSQL可以支持像SELECT这样的简单子查询X来自（从Y选择*）。示例如下：POST/_sql?format=txt{"query":"SELECTavg(data.DistanceMiles)from(SELECTFlightNum,OriginWeather,OriginCountry,Carrier,DistanceMilesFROMkibana_sample_data_flightsWHEREDestCountry='US')asdata"不支持更复杂的子查询。更多限制请参考官方文档。6.小结本文详细介绍了ESSQL的相关知识。一般来说，大部分都是根据官方文档直译过来的。更多ESSQL的使用方法，请参考官方文档。
