Elasticsearch SQL的用法是什么

65次阅读
没有评论

共计 6864 个字符,预计需要花费 18 分钟才能阅读完成。

Elasticsearch SQL 的用法是什么,针对这个问题,这篇文章详细介绍了相对应的分析和解答,希望可以帮助更多想解决这个问题的小伙伴找到更简单易行的方法。

丸趣 TV 小编将介绍不同版本中 Elasticsearch SQL 的使用方法,总结了实际中常用的方法和操作,并给出了几个具体例子。

一、5.x 中 ES-SQL 用法

Elasticsearch 5.x 版本中,SQL 功能还没有集成到 Elasticsearch 源码中,需要下载第三方插件后才能使用,配置过程如下: 

1. 安装 ES-SQL 依赖 node npm 

ES-SQL 5.x 版本以后,安装需要依赖 node 和 npm,先安装 node 和 npm,安装后在检查 node 及 npm 的安装,命令如下: 

yum -y install nodejs npm 
node -v  
npm -v

2. 下载 ES-SQL 并安装  

然后切换到 ES 的根目录下,执行如下命令,下载并安 ES-SQL 插件: 

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

离线包安装可以执行: 

./bin/elasticsearch-plugin install file:/elasticsearch-sql-5.6.3.0.zip

3. 重启 ES 服务  

执行完上述三步,你就可以使用 SQL 探索数据了,以 kibana 中的使用为例:

二、6.4 Elasticsearch SQL 用法

1   Elasticsearch SQL 支持的数据类型

首先我们看下 Elasticsearch SQL 和标准 SQL 中数据类型的对应关系:

2   Elasticsearch SQL 的使用方式

Elasticsearch SQL 支持三种 client: REST Interface,command-line,JDBC

2.1 REST Interface

建议先在 kibana 中测试 (可以一次执行多个 SQL),查询通过之后把查询 copy 到项目中进行测试。

6.3+ Elasticsearch SQL 有个非常实用的功能,就是可以用 translate api 把 SQL 语句翻译成 ES DSL 语句,对于学习 DSL 感到头痛的同学有福啦。

2.2 command-line

命令行界面的进入方式:

./elasticsearch-sql-cli IP:PORT(本机 ip 和 es 的端口)

进入后的界面如下:

命令行一般作为 SQL 测试时使用。

2.3 JDBC

该组件为 X -Pack 中的收费组件,感兴趣的同学可以参考官方文档:https://www.elastic.co/guide/en/elasticsearch/reference/current/sql-jdbc.html

3    常用 SQL 语句

注意:查询单个索引名一定要用 引上,否则会报错

* 查看当前用户所有的索引:“SHOW TABLES;”

精准查询某个索引:“SHOW TABLES LIKE‘indexname’;”

通配符查询某些索引:“SHOW TABLES LIKE‘’;”

* 查看某个索引结构:“DESCRIBE table;”或者“DESC table;”

Elasticsearch SQL 的用法是什么

上面两个命令都是“SHOW COLUMNS [FROM | IN] ? table”命令的别名

Elasticsearch SQL 的用法是什么

* 查看函数:“SHOW FUNCTIONS [LIKE? pattern?]?” 

精准查询某个函数:

Elasticsearch SQL 的用法是什么

通配符查询某些函数:

Elasticsearch SQL 的用法是什么

Elasticsearch SQL 的用法是什么

查看所有函数:

Elasticsearch SQL 的用法是什么

常用的聚合函数: 

SELECT MIN(value_1) min, MAX(value_1) max, AVG(value_1) avg,SUM(value_1) sum,COUNT(*) count,COUNT(DISTINCT value_1) dictinct_count FROM  micloud_es_sink_zhouyongbo_test-2018.10.19”;

Elasticsearch SQL 的用法是什么

SELECT 语句的语法排序如下:

SELECT select_expr [, ...]
[ FROM table_name ] 
[ WHERE condition ] 
[ GROUP BY grouping_element [, ...] ]
[ HAVING condition]
[ ORDER BY expression [ ASC | DESC ] [, ...] ]
[ LIMIT [ count ] ]

* 限定返回数据的条数:“limit” 

SELECT * FROM  micloud_es_sink_zhouyongbo_test-2018.10.19” limit 10 ;

注意 SQL 中的 limit 比 fetch_size 中的优先级高, 例如下面的例子返回的是 5 条 :

{ 
  query :  SELECT * FROM  micloud_es_sink_zhouyongbo_test-2018.10.19” limit 5 , 
  fetch_size :10 
}

* 排序:“order by + 字段名字 + asc/desc”

SELECT * FROM  micloud_es_sink_zhouyongbo_test-2018.10.19” ORDER BY value_1 asc/desc;

根据多个字段排序: 

select city c,value_1 + 1 vp from  micloud_es_sink_zhouyongbo_test-2018.10.19  group by c,vp order by c desc,vp asc;

Elasticsearch SQL 的用法是什么

*WHERE 根据条件查询:

WHERE 后面跟 ES 复杂数据类型: 

SELECT first_name FROM index WHERE first_name.raw = ‘John’ ;

WHERE 后面跟多个查询条件: 

SELECT * FROM micloud_es_sink_zhouyongbo_test* where city=‘北京  and value_1=8 ORDER BY value_1 desc ;

*group by 分组查询: 

根据单个字段分组查询:

select city,count(city) as count_city,sum(value_1) as count_value_1 from  micloud_es_sink_zhouyongbo_test-2018.10.19  group by city;

Elasticsearch SQL 的用法是什么

根据多个字段分组查询: 

select city,count(city) count_city,sum(value_1) count_value_1 from  micloud_es_sink_zhouyongbo_test-2018.10.19  group by city,value_1;

Elasticsearch SQL 的用法是什么

对于比较长的字段,也可以对该字段声明别名,并对别名进行分组查询,声明字段别名的“as”可省略: 

select city c,count(city) count_city,sum(value_1) count_value_1 from  micloud_es_sink_zhouyongbo_test-2018.10.19  group by c,value_1;

Elasticsearch SQL 的用法是什么

还可以对某字段进行计算,然后按照计算结果分组查询: 

select city c,value_1 + 1 vp from  micloud_es_sink_zhouyongbo_test-2018.10.19  group by c,vp;

Elasticsearch SQL 的用法是什么

*HAVING 过滤分组结果(ES-SQL 引擎同样会在分组之后计算 HAVING 语句):

Select city c,count(*) count from  micloud_es_sink_zhouyongbo_test-2018.10.19  group by c having count   53834;

Elasticsearch SQL 的用法是什么

* 查询嵌套类型:

select * from zhouyongbo_test04 where love.kaishu=‘鲁公’;

Elasticsearch SQL 的用法是什么

* 用通配符查询多个索引:

Elasticsearch SQL 的用法是什么

注意被查询索引必须有相同的 mapping,否则会有如下报错:

Elasticsearch SQL 的用法是什么

常用的方法和操作汇总:

* 比较操作: 

Equality (=) 

select * from  micloud_es_sink_zhouyongbo_test-2018.10.19  where value_1 = 6 limit 5;

Inequality ( or != or 

select * from  micloud_es_sink_zhouyongbo_test-2018.10.19  where value_1   6 limit 5;

Comparison (, 

select * from  micloud_es_sink_zhouyongbo_test-2018.10.19  where value_1  = 6 limit 5;

BETWEEN

select * from  micloud_es_sink_zhouyongbo_test-2018.10.19  where value_1 between 6 and 8 limit 5;

IS NULL/IS NOT NULL

select * from  micloud_es_sink_zhouyongbo_test-2018.10.19  where value_1 is not NULL limit 5;

* 逻辑操作:

AND

select * from  micloud_es_sink_zhouyongbo_test-2018.10.19  where value_1   5 and value_1   7 limit 5;

OR

select * from  micloud_es_sink_zhouyongbo_test-2018.10.19  where value_1 = 5 or value_1 = 7 limit 5;

NOT

select * from  micloud_es_sink_zhouyongbo_test-2018.10.19  where not value_1   5 limit 5;

* 数学运算操作: 

Add (+)

select 1 + 1 as x;

Subtract (infix -) 

select 1 - 1 as x;

Negate (unary -) 

select - 1 as x;

Multiply (*) 

select 6 * 6 as x;

Divide (/) 

select 30 / 5 as x;

Modulo or Reminder(%) 

select 30 % 7 as x;

* 数学函数:(分为通用函数和三角函数两部分):

通用函数: 

ABS:求数字的绝对值  

select ABS(value_1) from  micloud_es_sink_zhouyongbo_test-2018.10.19  limit 5;

CBRT:求数字的立方根,返回 double

select value_1 v,CBRT(value_1) cbrt from  micloud_es_sink_zhouyongbo_test-2018.10.19  limit 5;

CEIL:返回大于或者等于指定表达式最小整数(double)

select value_1 v,CEIL(value_1) from  micloud_es_sink_zhouyongbo_test-2018.10.19  limit 5;

CEILING:等同于 CEIL

select value_1 v,CEILING(value_1) from  micloud_es_sink_zhouyongbo_test-2018.10.19  limit 5;

E:返回自然常数 e(2.718281828459045)

select value_1,E(value_1) from  micloud_es_sink_zhouyongbo_test-2018.10.19  limit 5;

ROUND:四舍五入精确到个位

select ROUND(-3.14);

FLOOR:向下取整

select FLOOR(3.14);

LOG:计算以 2 为底的自然对数

select LOG(4);

LOG10:计算以 10 为底的自然对数

select LOG10(100);

SQRT:求一个非负实数的平方根

select SQRT(9);

EXP:此函数返回 e(自然对数的底) 的 X 次方的值

select EXP(3);

EXPM1:返回 e  x -1

select EXPM1(3);

三角函数:

DEGREES:返回 X 从弧度转换为度值

select DEGREES(x);

RADIANS:返回 X 从度转换成弧度的值

select RADIANS(x);

SIN:返回 X 的正弦

select SIN(x);

COS:返回 X,X 值是以弧度给出的余弦值

select COS(角度);

TAN:返回参数 X,表示以弧度的切线值

select TAN(角度);

ASIN:返回 X 的反正弦,X 的值必须在 - 1 至 1 范围内,返回 NULL

select ASIN(x);

ACOS:返回 X 的反正弦,X 值必须 - 1 到 1 之间范围否则将返回 NULL

select ACOS(x);

ATAN:返回 X 的反正切

select ATAN(x);

SINH:返回 X 的双曲正弦值

select SINH(x);

COSH:返回 X 的双曲余弦值

select COSH(x);

* 日期和时间处理相关方法:

YEAR:

SELECT YEAR(CAST( 2018-10-23T16:59:27Z  AS TIMESTAMP)) AS year;

MONTH_OF_YEAR() or MONTH():

SELECT MONTH(CAST( 2018-10-23T16:59:27Z  AS TIMESTAMP)) AS month;

WEEK_OF_YEAR() or WEEK():

SELECT WEEK(CAST( 2018-10-23T16:59:27Z  AS TIMESTAMP)) AS week;

DAY_OF_YEAR() or DOY(),效果等同于 EXTRACT(

SELECT DOY(CAST( 2018-10-23T16:59:27Z  AS TIMESTAMP)) AS day;

DAY_OF_MONTH(), DOM(), or DAY():

SELECT DAY(CAST( 2018-10-23T16:59:27Z  AS TIMESTAMP)) AS day;

DAY_OF_WEEK() or DOW():

SELECT DOW(CAST( 2018-10-23T16:59:27Z  AS TIMESTAMP)) AS day;

HOUR_OF_DAY() or HOUR():

SELECT HOUR(CAST( 2018-10-23T16:59:27Z  AS TIMESTAMP)) AS hour;

MINUTE_OF_DAY():

SELECT MINUTE_OF_DAY(CAST( 2018-10-23T16:59:27Z  AS TIMESTAMP)) AS minute;

MINUTE_OF_HOUR() or MINUTE():

SELECT MINUTE(CAST( 2018-10-23T16:59:27Z  AS TIMESTAMP)) AS minute;

SECOND_OF_MINUTE() or SECOND():

SELECT SECOND(CAST( 2018-10-23T16:59:27Z  AS TIMESTAMP)) AS second;

如上就是 6.4 Elasticsearch SQL 支持的主要用法了,如果在优化 SQL 语句之后还不满足查询需求,可以拿 SQL 和 DSL 混用,ES 会先根据 SQL 进行查询,然后根据 DSL 语句对 SQL 的执行结果进行二次查询,下面是个小例子:

POST /_xpack/sql?format=txt 
{ 
  query :  SELECT * FROM library ORDER BY page_count DESC , 
  filter : { 
  range : { 
  page_count : { 
  gte  : 100, 
  lte  : 200 
 } 
 } 
 }, 
  fetch_size : 5
}

这个查询就会先根据“query”后面的 SQL 进行查询,然后用执行“filter”和“fetch_size”DSL 语法对查询结果进行过滤,进而返回最终结果。

关于 Elasticsearch SQL 的用法是什么问题的解答就分享到这里了,希望以上内容可以对大家有一定的帮助,如果你还有很多疑惑没有解开,可以关注丸趣 TV 行业资讯频道了解更多相关知识。

正文完
 
丸趣
版权声明:本站原创文章,由 丸趣 2023-07-17发表,共计6864字。
转载说明:除特殊说明外本站除技术相关以外文章皆由网络搜集发布,转载请注明出处。
评论(没有评论)