【Elasticsearch】SQL操作相关

SQL操作

通常使用es的时候，都会使用query dsl来查询数据，从es6.3之后，es就支持SQL查询啦

本质就是 Elasticsearch SQL 这个 X-Pack 组件，将SQL翻译成SQL DSL

• Elasticsearch SQL 具有如下特性：

  • 原生支持：Elasticsearch SQL 是专门为 Elasticsearch 打造的。
  • 没有额外的零件：无需其他硬件，处理器，运行环境或依赖库即可查询 Elasticsearch，Elasticsearch SQL 直接在 Elasticsearch 内部运行。
  • 轻巧高效：Elasticsearch SQL 并未抽象化其搜索功能，相反的它拥抱并接受了 SQL 来实现全文搜索，以简洁的方式实时运行全文搜索。

• SQL和Elasticsearch的对应关系

  SQL	ES	描述
  Column	field	对比两个，数据都存储在命名条目中，具有多种数据类型，包含个值。SQL 将此类条目称为列，而 Elasticsearch 称为字段。请注意在 Elasticsearch 中，一个字段可以包含多个相同类型的值 (本质上是一个列表)，而在 SQL 中，一个列可以只包含一个所述类型的值。Elasticsearch SQL 将尽最大努力保留 SQL 语义，并根据查询拒绝那些返回具有多个值的字段的查询
  Row	document	Columns 和 fields 本身不存在;它们是 row或 a的一部分 document两者的语义略有不同:row 趋于严格 (并且有更多的强制执行)，而document 趋于更加灵活或松散(同时仍然具有结构)
  Table	Index	执行查询的目标
  Schema	Mapping	在 RDBMS 中，schem 主要是表的命名空间，通常用作安全边界。Elasticsearch 没有为它提供等效的概念。但是，当启用安全性时Elasticsearch 会自动应用安全性强制，以便角色只能看到它被允许访 问的数据
  Database	Cluster 实例	在 SQL 中，catalog 或者 database 从概念上可以互换使用，表示一组模式，即多个表。在 Elasticsearch 中，可用的索引集被分组在一个cluster，语义也有所不同。database 本质上是另一个命名空间 (可能对数据的存储方式有一些影响)，而 Elasticsearch cluster 是一个运行时实例，或者更确切地说是一组至少一个 Elasticsearch 实例(通常是分布式运行)。在实践中，这意味着虽然在 SQL 中，一个实例中可能有多个目录，但在 Elasticsearch 中，一个目录仅限于一个

• _sql?format=xxx

  format 参数控制返回结果的格式，默认为 json 格式

  • txt:表示文本格式，看起来更直观点.
  • csv:使用逗号隔开的数据
  • json:JSON 格式数据
  • tsv: 使用 tab 键隔开数据
  • yaml:属性配置格式

数据准备

创建索引并增加数据，等同于创建表和数据

PUT my-sql-index/_bulk?refresh
{"index":{"_id": "JAVA"}}
{"name": "JAVA", "author": "zhangsan", "release_date": "2022-05-01", "page_count": 561}
{"index":{"_id": "BIGDATA"}}
{"name": "BIGDATA", "author": "lisi", "release_date": "2022-05-02", "page_count": 482}
{"index":{"_id": "SCALA"}}
{"name": "SCALA", "author": "wangwu", "release_date": "2022-05-03", "page_count": 604}

查询索引下的数据

POST _sql?format=txt
{
 "query": """
 SELECT * FROM "my-sql-index"
 """
}

SQL转化为DSL(本质)

# 转换 SQL 为 DSL 进行操作
POST _sql/translate
{
 "query": """
 SELECT * FROM "my-sql-index" where page_count > 500
 """
}

SQL与DSL混合使用

ES 会先根据 SQL 进行查询，然后根据 DSL 语句对 SQL 的执行结果进行二次查询

# 由于索引中含有横线，所以作为表名时需要采用双引号，且外层需要三个引号包含
POST _sql?format=txt
{
    "query": """
    	SELECT * FROM "my-sql-index"
    	""",
    "filter": {
        "range": {
            "page_count": {
                "gte": 400,
                "lte": 600
            }
        }
    },
    "fetch_size": 2
}