Elasticsearch SQL 深度体验

最新推荐文章于 2022-09-27 10:33:29 发布

vkingnew

最新推荐文章于 2022-09-27 10:33:29 发布

阅读量958

点赞数

分类专栏： Elasticsearch

本文链接：https://blog.csdn.net/vkingnew/article/details/91964465

版权

Elasticsearch 专栏收录该内容

38 篇文章 3 订阅

订阅专栏

1.简介

Elasticsearch SQL 自6.3版本集成到X_PACK_CORE包中，允许Elasticsearch执行实时的SQL查询。可以在在Elasticsearch中本地搜索和聚合数据，通过REST、JDBC、ODBC、CLI等多种方式。可以将Elasticsearch SQL理解为一个翻译器，它既能理解SQL又能理解Elasticsearch，并且通过利用Elasticsearch功能，可以实现大规模实时读取和处理数据。

2.特点：

原生整合(Native integration)
Elasticsearch SQL是为Elasticsearch构建的。基于底层存储，针对相关节点有效地执行每个查询。
没有外部部分
无需额外的硬件，进程，runtime或库来查询Elasticsearch; Elasticsearch SQL基于Elasticsearch集群内运行SQL,消除额外的数据移动。
轻巧高效(Lightweight and efficient)
Elasticsearch SQL不会抽象Elasticsearch及其搜索功能 - 相反，它拥抱并扩展SQL以支持相同的声明性，简洁的方式，实时查询和全文索引(full-text search).

3.SQL和ElasticSearh的概念隐射：

SQL	Elasticsearch	说明
column	field
row	document
table	index
database	cluster instance

cluster

cluster（federated）

4.Elasticsearch 安全支持：

支持SSL/TLS 配置

支持的认证方式：1.Username/Password 2.PKI/X.509

5.REST查询支持的数据格式：

查询返回的格式：

REST支持的参数：

6.Elasticsearch ODBC驱动的支持：

Windows 10 64 bit or Windows Server 2016 64 bit operating system
.NET Framework 4.0 full
Microsoft Visual C++ Redistributable for Visual Studio 2017

7.支持的SQL语句：

DESCRIBE TABLE  -- 查询表的定义
SELECT          -- 查询表的数据
SHOW COLUMNS    -- 列出表的列
SHOW FUNCTIONS  -- 列出支持的函数
SHOW TABLES     -- 列出可用的表

SQL 查询语句：
SELECT select_expr [, ...]
[ FROM table_name ]
[ WHERE condition ]
[ GROUP BY grouping_element [, ...] ]
[ HAVING condition]
[ ORDER BY expression [ ASC | DESC ] [, ...] ]
[ LIMIT [ count ] ]

8.支持的数据类型：

9.Elasticsearch SQL的限制：

1.大型查询可能会抛出ParsingExpection
极大的查询在解析阶段会消耗太多内存，在这种情况下，Elasticsearch SQL引擎将中止解析并抛出错误。在这种情况下，
请考虑通过可能简化查询或将查询拆分为较小的查询来将查询缩小到较小的大小

2.SYS COLUMNS和DESCRIBE TABLEedit中的嵌套字段
Elasticsearch有一种特殊类型的关系字段，称为嵌套字段。在Elasticsearch SQL中，可以通过引用其内部子字段来使用它们。
即使SYS COLUMNS处于非驱动程序模式（在CLI和REST调用中）和DESCRIBE TABLE仍将它们显示为具有NESTED类型，它们也不能在查询中使用。
示例：
SELECT dep.dep_name.keyword FROM test_emp GROUP BY languages;

3.多嵌套的字段（Multi-nested fields）：
Elasticsearch SQL不支持多嵌套文档（ multi-nested documents），因此查询不能在索引中引用多个嵌套字段。这适用于多级嵌套字段，但也适用于在同一级别定义的多个嵌套字段

4.规范化关键字字段(Normalized keyword fields)
Elasticsearch中的关键字字段可以通过定义规范化器来规范化。 Elasticsearch SQL不支持此类字段。

5.Paginating nested inner hitsedit
When SELECTing a nested field, pagination will not work as expected, Elasticsearch SQL will return 
at least the page size records. This is because of the way nested queries work in Elasticsearch: 
the root nested field will be returned and it’s matching inner nested fields as well, pagination 
taking place on the root nested document and not on its inner hits.

6.数组类型的字段(Array type of fields)
由于Elasticsearch处理值数组的“不可见”方式，不支持数组字段：映射不指示字段是否是数组（具有多个值），因此
无需读取所有数据，Elasticsearch SQL无法知道某个字段是单个值还是多个值。为字段返回多个值时，默认情况下，
Elasticsearch SQL将引发异常。但是，可以通过REST中的field_multi_value_leniency参数（默认情况下禁用）
或驱动程序中的field.multi.value.leniency（默认情况下启用）来更改此行为。

7.不支持复杂子查询
当子查询中有group by和having 子句时候则不支持子查询

8.聚合排序的限制：
在进行聚合（GROUP BY）时，Elasticsearch SQL依赖于Elasticsearch的复合聚合来支持分页结果。但是，这种类型的聚合
确实存在一个限制：排序只能应用于聚合桶的key。
Elasticsearch SQL通过进行客户端排序克服了这一限制，但作为安全措施，最多只允许512行。

示例：
SELECT * FROM test GROUP BY age ORDER BY COUNT(*) LIMIT 100;
 可以不用limit操作但是默认只能限制512行，对于更多的排序Elasticsearch无法跟踪，超出限制则报告异常。

9.聚合函数的限制：
聚合函数只能直接在字段上使用，因此SELECT max(abs(age)) FROM test之类的查询是不支持的。

10.在having子句中使用聚合函数（FIRST/LAST/min/max）不被支持

11.不支持多表关联查询

12.不支持DDL

5.示例：

添加示例数据：
#curl -X PUT "localhost:9200/library/book/_bulk?refresh" -H 'Content-Type: application/json' -d'
{"index":{"_id": "Leviathan Wakes"}}
{"name": "Leviathan Wakes", "author": "James S.A. Corey", "release_date": "2011-06-02", "page_count": 561}
{"index":{"_id": "Hyperion"}}
{"name": "Hyperion", "author": "Dan Simmons", "release_date": "1989-05-26", "page_count": 482}
{"index":{"_id": "Dune"}}
{"name": "Dune", "author": "Frank Herbert", "release_date": "1965-06-01", "page_count": 604}
'

REST API查询：
#curl -X POST "localhost:9200/_xpack/sql?format=txt" -H 'Content-Type: application/json' -d'
{
    "query": "SELECT * FROM library WHERE release_date < \u00272000-01-01\u0027"
}
'
    author     |     name      |  page_count   |      release_date      
---------------+---------------+---------------+------------------------
Dan Simmons    |Hyperion       |482            |1989-05-26T00:00:00.000Z
Frank Herbert  |Dune           |604            |1965-06-01T00:00:00.000Z

CLI访问：
# /usr/share/elasticsearch/bin/elasticsearch-sql-cli

登录的方式：
1. #./bin/elasticsearch-sql-cli https://some.server:9200
2. #./bin/elasticsearch-sql-cli https://sql_user:strongpassword@some.server:9200

sql> show tables;
      name       |     type      
-----------------+---------------
datastream       |VIEW           
datastream-000001|BASE TABLE     
library          |BASE TABLE     
test-index       |BASE TABLE     

sql> select * from library;
     author     |     name      |  page_count   |      release_date      
----------------+---------------+---------------+------------------------
Dan Simmons     |Hyperion       |482            |1989-05-26T00:00:00.000Z
James S.A. Corey|Leviathan Wakes|561            |2011-06-02T00:00:00.000Z
Frank Herbert   |Dune           |604            |1965-06-01T00:00:00.000Z

sql> exit;
Bye!


--不同格式的查询：
--CSV
curl -X POST "localhost:9200/_xpack/sql?format=csv" -H 'Content-Type: application/json' -d'
{
    "query": "SELECT * FROM library ORDER BY page_count DESC",
    "fetch_size": 5
}
'
--JSON
curl -X POST "localhost:9200/_xpack/sql?format=json" -H 'Content-Type: application/json' -d'
{
    "query": "SELECT * FROM library ORDER BY page_count DESC",
    "fetch_size": 5
}
'

--TSV：
curl -X POST "localhost:9200/_xpack/sql?format=tsv" -H 'Content-Type: application/json' -d'
{
    "query": "SELECT * FROM library ORDER BY page_count DESC",
    "fetch_size": 5
}
'

--TXT：
curl -X POST "localhost:9200/_xpack/sql?format=txt" -H 'Content-Type: application/json' -d'
{
    "query": "SELECT * FROM library ORDER BY page_count DESC",
    "fetch_size": 5
}
'
--YAML：
curl -X POST "localhost:9200/_xpack/sql?format=yaml" -H 'Content-Type: application/json' -d'
{
    "query": "SELECT * FROM library ORDER BY page_count DESC",
    "fetch_size": 5
}
'
--Elasticsearch DSL：
curl -X POST "localhost:9200/_xpack/sql?format=txt" -H 'Content-Type: application/json' -d'
{
    "query": "SELECT * FROM library ORDER BY page_count DESC",
    "filter": {
        "range": {
            "page_count": {
                "gte" : 100,
                "lte" : 200
            }
        }
    },
    "fetch_size": 5
}
'
--translate API：
curl -X POST "localhost:9200/_xpack/sql/translate" -H 'Content-Type: application/json' -d'
{
    "query": "SELECT * FROM library ORDER BY page_count DESC",
    "fetch_size": 10
}
'

参考：

https://www.elastic.co/guide/en/elasticsearch/reference/6.8/sql-jdbc.html

https://www.elastic.co/guide/en/elasticsearch/reference/6.8/_api_usage.html

https://www.elastic.co/guide/en/elasticsearch/reference/6.8/sql-functions.html

https://www.elastic.co/guide/en/elasticsearch/reference/6.8/sql-syntax-reserved.html

vkingnew

关注

0
点赞
踩
2

收藏

觉得还不错? 一键收藏
0
评论
Elasticsearch SQL 深度体验

1.简介Elasticsearch SQL 自6.3版本集成到X_PACK_CORE包中，允许Elasticsearch执行实时的SQL查询。可以在在Elasticsearch中本地搜索和聚合数据，通过REST、JDBC、ODBC、CLI等多种方式。可以将Elasticsearch SQL理解为一个翻译器，它既能理解SQL又能理解Elasticsearch，并且通过利用Elasticsear...
复制链接

扫一扫