首页 / 广告发布 / 正文
Elasticsearch SQL介绍及实例
sundas333 发表于:2020-6-9 22:15:57 复制链接 看图 发表新帖
阅读数:6345

下载APP可以快速和圈友联系

您需要 登录 才可以下载或查看,没有帐号?立即注册

x
导语

Elasticsearch 是一个全文搜索引擎,具有您期望的一切优点,例如相关性评分,词干,同义词等。而且,由于它是具有水平可扩大的散布式文档存储,是以它可以处置数十亿行数据,而不会费劲。针对Elasticsearch专业职员来说,大大都人喜好利用DSL来停止搜索,可是对于一些不是那末专业的职员来说,他们更加熟悉的是 SQL 语句。若何让他们对 Elasticsearch 的数据停止查询是一个题目。借助 Elasticsearch SQL,您可以利用熟悉的查询语法拜候全文搜索,超快的速度和轻松的可伸缩性。X-Pack 包括一项 SQL 功用,可对 Elasticsearch 索引履行 SQL 查询并以表格格式返回成果。

在明天的文章里,我们将简单先容一下若何利用 Elasticsearch SQL来对我们的数据停止查询。在之前的一篇文章“Kibana:Canvas入门”里也有 Elasticsearch SQL 的具体用例先容。

安装

对于还没安装好自己的 Elasticsearch 的开辟者来说,你可以参阅我之前的文章“Elastic:菜鸟上手指南”来停止安装自己的 Elasticsearch 及 Kibana。在这里我就不累述了。

预备数据

我们首先翻开 Kibana:

Elasticsearch SQL先容及实例-1.jpg

点击上面的“Load a data set and a Kibana dashboard”:

Elasticsearch SQL先容及实例-2.jpg

点击上面的 Add data,这样我们便可以完成尝试数据的导入了。在 Elasticsearch 中,我们会找到一个叫 kibana_sample_data_flights 的索引。

SQL 实操

检索 Elasticsearch schema 信息:DSL vs SQL

首先,我们肯定表/索引的 schema 以及可供我们利用的字段。我们将经过 REST 界面履行此操纵:
POST /_sql{ "query": """ DESCRIBE kibana_sample_data_flights """}上面号令的成果:

{ "columns" : [ { "name" : "column", "type" : "keyword" }, { "name" : "type", "type" : "keyword" }, { "name" : "mapping", "type" : "keyword" } ], "rows" : [ [ "AvgTicketPrice", "REAL", "float" ], [ "Cancelled", "BOOLEAN", "boolean" ], [ "Carrier", "VARCHAR", "keyword" ], [ "Dest", "VARCHAR", "keyword" ], [ "DestAirportID", "VARCHAR", "keyword" ], [ "DestCityName", "VARCHAR", "keyword" ], [ "DestCountry", "VARCHAR", "keyword" ], [ "DestLocation", "GEOMETRY", "geo_point" ], [ "DestRegion", "VARCHAR", "keyword" ], [ "DestWeather", "VARCHAR", "keyword" ], [ "DistanceKilometers", "REAL", "float" ], [ "DistanceMiles", "REAL", "float" ], [ "FlightDelay", "BOOLEAN", "boolean" ], [ "FlightDelayMin", "INTEGER", "integer" ], [ "FlightDelayType", "VARCHAR", "keyword" ], [ "FlightNum", "VARCHAR", "keyword" ], [ "FlightTimeHour", "VARCHAR", "keyword" ], [ "FlightTimeMin", "REAL", "float" ], [ "Origin", "VARCHAR", "keyword" ], [ "OriginAirportID", "VARCHAR", "keyword" ], [ "OriginCityName", "VARCHAR", "keyword" ], [ "OriginCountry", "VARCHAR", "keyword" ], [ "OriginLocation", "GEOMETRY", "geo_point" ], [ "OriginRegion", "VARCHAR", "keyword" ], [ "OriginWeather", "VARCHAR", "keyword" ], [ "dayOfWeek", "INTEGER", "integer" ], [ "timestamp", "TIMESTAMP", "datetime" ] ]}
也可以经过 url 参数 format = txt 以表格形式格式化以上响应。例如:
POST /_sql?format=txt { "query": "DESCRIBE kibana_sample_data_flights"}
上面号令查询的成果是:
column | type | mapping ------------------+---------------+---------------AvgTicketPrice |REAL |float Cancelled |BOOLEAN |boolean Carrier |VARCHAR |keyword Dest |VARCHAR |keyword DestAirportID |VARCHAR |keyword DestCityName |VARCHAR |keyword DestCountry |VARCHAR |keyword DestLocation |GEOMETRY |geo_point DestRegion |VARCHAR |keyword DestWeather |VARCHAR |keyword DistanceKilometers|REAL |float DistanceMiles |REAL |float FlightDelay |BOOLEAN |boolean FlightDelayMin |INTEGER |integer FlightDelayType |VARCHAR |keyword FlightNum |VARCHAR |keyword FlightTimeHour |VARCHAR |keyword FlightTimeMin |REAL |float Origin |VARCHAR |keyword OriginAirportID |VARCHAR |keyword OriginCityName |VARCHAR |keyword OriginCountry |VARCHAR |keyword OriginLocation |GEOMETRY |geo_point OriginRegion |VARCHAR |keyword OriginWeather |VARCHAR |keyword dayOfWeek |INTEGER |integer timestamp |TIMESTAMP |datetime
能否是感受回到 SQL 时代啊:)

向前迈进,只要供给来自 REST api 的示例响应,我们就会利用上面显现的表格响应结构。要经过控制台实现不异的查询,需要利用以下号令登录:
./bin/elasticsearch-sql-cli http://localhost:9200
我们可在屏幕上看到以下的画面:

Elasticsearch SQL先容及实例-3.jpg

太奇异了。我们间接看到 SQL 的号令提醒符了。在上面的号令行中,我们打入以下的号令:
DESCRIBE kibana_sample_data_flights;
Elasticsearch SQL先容及实例-4.jpg

这个成果和我们在Kibana中获得的成果是一样的。

上面的schema也会随对在 SELECT 子句中显现的字段的任何查询一路返回,从而为任何潜伏的驱动法式供给格式化或对成果停止操纵所需的需要范例信息。例如,斟酌带有 LIMIT 子句的简单 SELECT,以使响应简短。默许情况下,我们返回1000行。

我们发现索引的名字 kibana_sample_data_flights 比力长,为了方便,我们来建立一个alias:
PUT /kibana_sample_data_flights/_alias/flights
这样在今后的操纵中,当我们利用flights的时辰,实在也就是对索引kibana_sample_data_flights 停止操纵。

我们履行以下的号令:
POST /_sql?format=txt { "query": "SELECT FlightNum FROM flights LIMIT 1"}
显现成果:
FlightNum ---------------9HY9SWR
不异的REST请求/响应由JDBC驱动法式和控制台利用:
sql> SELECT OriginCountry, OriginCityName FROM flights LIMIT 1; OriginCountry | OriginCityName ---------------+-----------------DE |Frankfurt am Main
请留意,假如在任何时辰请求的字段都不存在(区分巨细写),则表格式和强范例存储区的语义意味着将返回毛病-这与 Elasticsearch 行为分歧,在该行为中,底子不会返回该字段。例如,将上面的内容点窜成利用字段“OrigincityName”而不是“OriginCityName”会发生有用的毛病消息:
sql> SELECT OriginCountry, OrigincityName FROM flights LIMIT 1;Bad request [Found 1 problem(s)line 1:23: Unknown column [OrigincityName], did you mean any of [OriginCityName, DestCityName]?]
一样,假如我们尝试在不兼容的字段上利用函数或表达式,则会出现响应的毛病。凡是,分析器在考证 AST 时会较早失利。为了实现这一点,Elasticsearch 必须领会每个字段的索引映照和功用。是以,任何具有平安性拜候 SQL 接口的客户端都需要适当的权限。

假如我们继续供给每一个请求和响应的答复,我们将终极获得一篇冗杂的博客文章!为了简洁起见,以下是一些带有感爱好的正文的日益复杂的查询。

利用 WHERE 及 ORDER BY 来 SELECT

“找到飞翔时候跨越5小时的美国最长10班航班。”
POST /_sql?format=txt{ "query": """ SELECT OriginCityName, DestCityName FROM flights WHERE FlightTimeHour > 5 AND OriginCountry='US' ORDER BY FlightTimeHour DESC LIMIT 10 """}
显现成果是:
OriginCityName | DestCityName ---------------+-------------------Chicago |Oslo Cleveland |Seoul Denver |Chitose / TomakomaiNashville |Verona Minneapolis |Tokyo Portland |Treviso Spokane |Vienna Kansas City |Zurich Kansas City |Shanghai Los Angeles |Zurich
限制行数的运算符因 SQL 实现而异。对于 Elasticsearch SQL,我们在实现LIMIT运算符时与 Postgresql/Mysql 连结分歧。

Math

只是一些随机数字...
sql> SELECT ((1 + 3) * 1.5 / (7 - 6)) * 2 AS random; random ---------------12.0
这代表办事器端对功用履行某些后处置的示例。没有等效的Elasticsearch DSL查询。

Functions & Expressions

“在2月份以后查找一切航班,该航班的飞翔时候大于5小时,而且依照时候最长来排序。”
POST /_sql?format=txt{ "query": """ SELECT MONTH_OF_YEAR(timestamp), OriginCityName, DestCityName FROM flights WHERE FlightTimeHour > 1 AND MONTH_OF_YEAR(timestamp) > 2 ORDER BY FlightTimeHour DESC LIMIT 10 """}
显现成果是:
MONTH_OF_YEAR(timestamp)|OriginCityName | DestCityName ------------------------+---------------+---------------4 |Chicago |Oslo 4 |Osaka |Spokane 4 |Quito |Tucson 4 |Shanghai |Stockholm 5 |Tokyo |Venice 5 |Tokyo |Venice 5 |Tokyo |Venice 5 |Buenos Aires |Treviso 5 |Amsterdam |Birmingham 5 |Edmonton |Milan
这些功用凡是需要在 Elasticsearch 中应用 Painless 变形才能到达等效的结果,而 SQL 的功用声明避免任何剧本编写。还要留意我们若何在WHERE和SELECT子句中利用该函数。WHERE 子句组件被下推到 Elasticsearch,由于它影响成果计数。SELECT 函数由演示中的办事器端插件处置。

请留意,可勤奋用列表可经过“SHOW FUNCTIONS”检索
sql> SHOW FUNCTIONS; name | type -----------------+---------------AVG |AGGREGATE COUNT |AGGREGATE FIRST |AGGREGATE FIRST_VALUE |AGGREGATE LAST |AGGREGATE LAST_VALUE |AGGREGATE MAX |AGGREGATE  ...
将其与我们之前的数学才能相连系,我们可以起头制定查询,对于大大都DSL用户来说,查询将很是复杂。

“找出最快的2个航班(速度)的间隔和均匀速度,这些航班在星期一,星期二或星期三上午9点至11点之间分开,而且间隔跨越500千米。将间隔和速度四舍五入到最接近的整数。假如速度相称,请先显现最长的时候。”

首先我们在上面的 DESCRIBE kibana_sample_data_flights 号令的输出中,我们可以看到FlightTimeHour 是一个 keyword。这个明显是差池的,由于它是一个数值。也许在最初的设想时这么想的。我们需要把这个字段改成 float 范例的数据。
PUT flight1{ "mappings": { "properties": { "AvgTicketPrice": { "type": "float" }, "Cancelled": { "type": "boolean" }, "Carrier": { "type": "keyword" }, "Dest": { "type": "keyword" }, "DestAirportID": { "type": "keyword" }, "DestCityName": { "type": "keyword" }, "DestCountry": { "type": "keyword" }, "DestLocation": { "type": "geo_point" }, "DestRegion": { "type": "keyword" }, "DestWeather": { "type": "keyword" }, "DistanceKilometers": { "type": "float" }, "DistanceMiles": { "type": "float" }, "FlightDelay": { "type": "boolean" }, "FlightDelayMin": { "type": "integer" }, "FlightDelayType": { "type": "keyword" }, "FlightNum": { "type": "keyword" }, "FlightTimeHour": { "type": "float" }, "FlightTimeMin": { "type": "float" }, "Origin": { "type": "keyword" }, "OriginAirportID": { "type": "keyword" }, "OriginCityName": { "type": "keyword" }, "OriginCountry": { "type": "keyword" }, "OriginLocation": { "type": "geo_point" }, "OriginRegion": { "type": "keyword" }, "OriginWeather": { "type": "keyword" }, "dayOfWeek": { "type": "integer" }, "timestamp": { "type": "date" } } }}
我们需要 reindex 这个索引。
POST _reindex{ "source": { "index": "flights" }, "dest": { "index": "flight1" }}
那末现在 flight1 的数据中,FlightTimeHour 字段将会是一个 float 的范例。我们再次重新设备 alias 为 flights:
POST _aliases{ "actions": [ { "add": { "index": "flight1", "alias": "flights" } }, { "remove": { "index": "kibana_sample_data_flights", "alias": "flights" } } ]}
那末现在 flights 将是指向 flight1 的一个 alias。

我们利用以下的 SQL 语句来查询:
sql> SELECT timestamp, FlightNum, OriginCityName, DestCityName, ROUND(DistanceMiles) AS distance, ROUND(DistanceMiles/FlightTimeHour) AS speed, DAY_OF_WEEK(timestamp) AS day_of_week FROM flights WHERE DAY_OF_WEEK(timestamp) >= 0 AND DAY_OF_WEEK(timestamp) <= 2 AND HOUR_OF_DAY(timestamp) >=9 AND HOUR_OF_DAY(timestamp) <= 10 ORDER BY speed DESC, distance DESC LIMIT 2; timestamp | FlightNum |OriginCityName | DestCityName | distance | speed | day_of_week ------------------------+---------------+---------------+---------------+---------------+---------------+---------------2020-05-17T10:53:52.000Z|LAJSKLT |Guangzhou |Lima |11398.0 |783.0 |1 2020-04-27T09:30:39.000Z|VLUDO2H |Buenos Aires |Moscow |8377.0 |783.0 |2
一个相当复杂且希奇的题目,但希望您能大白这一点。还要留意我们若何建立字段别名并在ORDER BY 子句中援用它们。

还要留意,不需要在 SELECT 子句中指定 WHERE 和 ORDER BY 中利用的一切字段。这能够与您曩昔利用的 SQL 实现分歧。例如,以下内容完全正确:
POST /_sql{ "query":"SELECT timestamp, FlightNum FROM flights WHERE AvgTicketPrice > 500 ORDER BY AvgTicketPrice"}
它显现:
{ "columns" : [ { "name" : "timestamp", "type" : "datetime" }, { "name" : "FlightNum", "type" : "text" } ], "rows" : [ [ "2020-04-26T09:04:20.000Z", "QG5DXD3" ], [ "2020-05-02T23:18:27.000Z", "NXA71BT" ], [ "2020-04-17T01:55:18.000Z", "VU8K9DM" ], [ "2020-04-24T08:46:45.000Z", "UM8IKF8" ],...]
将SQL查询转换为DSL

我们都曾尝试过要在 Elasticsearch DSL 中表达的 SQL 查询,大概想晓得它能否是最好的。新 SQL 接口的引人注视标功用之一是它可以辅佐 Elasticsearch 的新采用者处理此类题目。利用 REST 接口,我们只需将/translate附加到“sql”端点,即可获得驱动法式将发出的Elasticsearch 查询。

让我们斟酌一下之前的一些查询:
POST /_sql/translate{ "query": "SELECT OriginCityName, DestCityName FROM flights WHERE FlightTimeHour > 5 AND OriginCountry='US' ORDER BY FlightTimeHour DESC LIMIT 10"}
对于任何有经历的 Elasticsearch 用户,等效的 DSL 都应当是不言而喻的:
{ "size" : 10, "query" : { "bool" : { "must" : [ { "range" : { "FlightTimeHour" : { "from" : 5, "to" : , "include_lower" : false, "include_upper" : false, "boost" : 1.0 } } }, { "term" : { "OriginCountry.keyword" : { "value" : "US", "boost" : 1.0 } } } ], "adjust_pure_negative" : true, "boost" : 1.0 } }, "_source" : { "includes" : [ "OriginCityName", "DestCityName" ], "excludes" :  }, "sort" : [ { "FlightTimeHour" : { "order" : "desc", "missing" : "_first", "unmapped_type" : "float" } } ]}
WHERE 子句将按您期望的那样转换为 range 和 term 查询。请留意,子字段的OriginCountry.keyword变体若何用于与父代 OriginCountry(文本范例)的切确婚配。不需要用户晓得根本映照的行为差别-正确的字段范例将会被自动挑选。风趣的是,该接口尝试经过在 _source 上利用 docvalue_fields 来优化检干脆能,例如适用于启用了 doc 值简直切范例(数字,日期,关键字)。我们可以依靠 Elasticsearch SQL 为指定的查询天生最好的 DSL。

现在斟酌我们上次利用的最复杂的查询:
POST /_sql/translate{ "query": """ SELECT timestamp, FlightNum, OriginCityName, DestCityName, ROUND(DistanceMiles) AS distance, ROUND(DistanceMiles/FlightTimeHour) AS speed, DAY_OF_WEEK(timestamp) AS day_of_week FROM flights WHERE DAY_OF_WEEK(timestamp) >= 0 AND DAY_OF_WEEK(timestamp) <= 2 AND HOUR_OF_DAY(timestamp) >=9 AND HOUR_OF_DAY(timestamp) <= 10 ORDER BY speed DESC, distance DESC LIMIT 2 """}
上面的响应为:
{ "size" : 2, "query" : { "bool" : { "must" : [ { "script" : { "script" : { "source" : "InternalSqlScriptUtils.SafeFilter(InternalSqlScriptUtils.and(InternalSqlScriptUtils.gte(InternalSqlScriptUtils.dateTimeChrono(InternalSqlScriptUtils.docValue(doc,params.v0), params.v1, params.v2), params.v3), InternalSqlScriptUtils.lte(InternalSqlScriptUtils.dateTimeChrono(InternalSqlScriptUtils.docValue(doc,params.v4), params.v5, params.v6), params.v7)))", "lang" : "painless", "params" : { "v0" : "timestamp", "v1" : "Z", "v2" : "HOUR_OF_DAY", "v3" : 9, "v4" : "timestamp", "v5" : "Z", "v6" : "HOUR_OF_DAY", "v7" : 10 } }, "boost" : 1.0 } }, { "script" : { "script" : { "source" : "InternalSqlScriptUtils.SafeFilter(InternalSqlScriptUtils.and(InternalSqlScriptUtils.gte(InternalSqlScriptUtils.dayOfWeek(InternalSqlScriptUtils.docValue(doc,params.v0), params.v1), params.v2), InternalSqlScriptUtils.lte(InternalSqlScriptUtils.dayOfWeek(InternalSqlScriptUtils.docValue(doc,params.v3), params.v4), params.v5)))", "lang" : "painless", "params" : { "v0" : "timestamp", "v1" : "Z", "v2" : 0, "v3" : "timestamp", "v4" : "Z", "v5" : 2 } }, "boost" : 1.0 } } ], "adjust_pure_negative" : true, "boost" : 1.0 } }, "_source" : { "includes" : [ "FlightNum", "OriginCityName", "DestCityName", "DistanceMiles", "FlightTimeHour" ], "excludes" :  }, "docvalue_fields" : [ { "field" : "timestamp", "format" : "epoch_millis" } ], "sort" : [ { "_script" : { "script" : { "source" : "InternalSqlScriptUtils.SafeSortNumeric(InternalSqlScriptUtils.round(InternalSqlScriptUtils.div(InternalSqlScriptUtils.docValue(doc,params.v0),InternalSqlScriptUtils.docValue(doc,params.v1)),params.v2))", "lang" : "painless", "params" : { "v0" : "DistanceMiles", "v1" : "FlightTimeHour", "v2" :  } }, "type" : "number", "order" : "desc" } }, { "_script" : { "script" : { "source" : "InternalSqlScriptUtils.SafeSortNumeric(InternalSqlScriptUtils.round(InternalSqlScriptUtils.docValue(doc,params.v0),params.v1))", "lang" : "painless", "params" : { "v0" : "DistanceMiles", "v1" :  } }, "type" : "number", "order" : "desc" } } ]}
能否是感觉很是复杂啊?

我们的 WHERE 和 ORDER BY 子句已转换为 painless 剧本,并在 Elasticsearch 供给的排序和剧本查询中利用。这些剧本甚至被参数化以避免编译并操纵剧本缓存。

附带说明一下,虽然以上内容代表了 SQL 语句的最好翻译,但并不代表处理更普遍题目标最好处理计划。现实上,我们希望在索引时候对文档中的星期几,一天中的小时和速度停止编码,是以可以只利用简单的范围查询。这能够比利用painless 剧本处理此特定题目标性能更高。现实上,由于这些缘由,其中的某些字段现实上甚至已经存在于文档中。这是用户应留意的常见主题:虽然我们可以依靠 Elasticsearch SQL 实现为我们供给最好翻译,但它只能操纵查询中指定的字段,是以纷歧定能为更大的题目查询供给最好处理计划。为了实现最好方式,需要斟酌根本平台的上风,而 _translate API 能够是此进程的第一步。

参考:https://elasticstack.blog.csdn.net/article/details/105199768

正文完

作者:刘晓国

本文编辑:妃尔

原文地址:https://blog.csdn.net/UbuntuTouch/article/details/105658911
Elasticsearch SQL先容及实例-5.jpg

    基于 Apache Flink 的实时监控诉警系统

    日志收集Agent,阴晦湿润的地底天下

    2020 继续踏踏实实的做好自己

Elasticsearch SQL先容及实例-6.jpg

Elasticsearch SQL先容及实例-7.jpg

你点的每个赞,我都认真当做了喜好


上一篇:中尧科技D8品牌转产KN95口罩助外洋抗击疫情
下一篇:日本37岁男星被曝性侵!趁女方醉酒强行得逞,事后却说自己不记得
温馨提示:
下载好向圈客户端可以随时随地交流学习经验,也可以和圈友发起聊天成为好友
好向圈www.kuaixunai.com是一个专业经验分享交流平台,请提供优质的经验内容分享,拒绝任何广告内容出现,低质量广告内容硬广包含手机号码,微信,QQ或者二维码,网址等形式存在可能会审核不通过甚至封号 圈友联系仅限于好向圈APP进行及时沟通咨询 要想被各大搜索引擎尽快收录请做好内容原创工作,才会有更好的推广效果。
返回列表
使用道具 举报
#我们, #数据, #查询, #使用, #进行
条评论
您需要登录后才可以回帖 登录 | 立即注册
王总2017 发表于 2020-6-9 22:18:38 | 阅读全部
转发了
使用道具 举报
回复
星空下的渔芈 发表于 2020-6-9 22:23:30 | 阅读全部
转发了
使用道具 举报
回复
123474210 发表于 2020-6-9 22:28:55 | 阅读全部
转发了
使用道具 举报
回复
小口子截留酆uz 发表于 2020-6-9 22:33:03 | 阅读全部
转发了
使用道具 举报
回复
百度地图侵权投诉可通过好向圈APP举报投诉----社区技术支持:泰帮动力 江苏好向圈信息科技有限公司 网站地图1 网站地图2