Elasticsearch 5.x 版本中, SQL 功能还没有集成到 Elasticsearch 源码中, 需要下载第三方插件后才能使用, 配置过程如下:
1. 安装 ES-SQL 依赖 node NPM
ES-SQL 5.x 版本以后, 安装需要依赖 node 和 NPM, 先安装 node 和 NPM, 安装后在检查 node 及 NPM 的安装, 命令如下:
- yum -y install Node.JS 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.3.0/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;"
上面两个命令都是 "SHOW COLUMNS [ FROM | IN ] ? table" 命令的别名
* 查看函数: "SHOW FUNCTIONS [ LIKE? pattern? ]?"
精准查询某个函数:
通配符查询某些函数:
查看所有函数:
常用的聚合函数:
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";
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;
*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;
根据多个字段分组查询:
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;
对于比较长的字段, 也可以对该字段声明别名, 并对别名进行分组查询, 声明字段别名的 "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;
还可以对某字段进行计算, 然后按照计算结果分组查询:
select city c,value_1 + 1 vp from "micloud_es_sink_zhouyongbo_test-2018.10.19" group by c,vp;
*HAVING 过滤分组结果 (ES-SQL 引擎同样会在分组之后计算 HAVING 语句):
Select city c,count(*) count from "micloud_es_sink_zhouyongbo_test-2018.10.19" group by c having count> 53834;
* 查询嵌套类型:
select * from zhouyongbo_test04 where love.kaishu='鲁公';
* 用通配符查询多个索引:
注意被查询索引必须有相同的 mapping, 否则会有如下报错:
常用的方法和操作汇总:
* 比较操作:
- 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(<datetime_function> FROM <expression>):
- 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 语法对查询结果进行过滤, 进而返回最终结果.
参考文档:
6.4.0 Elasticsearch SQL 新特性简介:
https://www.elastic.co/cn/products/stack/elasticsearch-sql
6.4.0 Elasticsearch SQL 使用文档:
https://www.elastic.co/guide/en/elasticsearch/reference/current/xpack-sql.html
来源: http://www.tuicool.com/articles/BbyiyeQ