Elasticsearch SQL_elasticsearch sql查询

一般使用 Elasticsearch 的时候，会使用 Query DSL 来查询数据，从 Elasticsearch6.3 版本以后，Elasticsearch 已经支持SQL查询了。

Elasticsearch SQL 是一个 X-Pack 组件，它允许针对 Elasticsearch 实时执行类似SQL的 查询。无论使用REST接口，命令行还是JDBC，任何客户端都可以使用SQL对 Elasticsearch中的数据进行原生搜索和聚合数据。可以将 Elasticsearch SQL 看作是一种翻译器，它可以将 SQL翻译成 Query DSL。

# Elasticsearch_SQL具有如下特性

• 原生集成 Elasticsearch SQL 是为 Elasticsearch 从头开始​​构建的。每个查询都根据底层存储有效地针对相关节点执行。

• 没有外部零件 无需额外的硬件、进程、运行时或库来查询 Elasticsearch；Elasticsearch SQL 通过在Elasticsearch 内部运行消除了额外的移动部件。

• 轻巧高效 Elasticsearch SQL并未抽象化其搜索功能，相反的它拥抱并接受了SQL来 实现全文搜索，以简洁的方式实时运行全文搜索

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

PUT my-sql-index/_bulk?refresh 
{"index":{"_id": "JAVA"}} 
{"name":  "JAVA",  "author":  "zhangsan",  "release_date":  "2022-08-10","page_count": 561} 
{"index":{"_id": "BIGDATA"}} 
{"name": "BIGDATA", "author": "lisi", "release_date": "2022-08-11", "page_count": 482} 
{"index":{"_id": "SCALA"}}
{"name": "SCALA", "author": "wangwu", "release_date": "2022-08-12", "page_count": 604}

# 第一个SQL查询

# SQL 
# 这里的表就是索引 
# 可以通过 format参数控制返回结果的格式，默认为  json格式 
#       txt:表示文本格式，看起来更直观点. 
#       csv:使用逗号隔开的数据 
#       json:JSON格式数据 
#       tsv: 使用  tab键隔开数据 
#       yaml:属性配置格式 
POST _sql?format=txt 
{ 
    "query": """ 
      SELECT * FROM "my-sql-index" 
    """ 
}

# 条件查询

# 条件查询 
POST _sql?format=txt 
{ 
    "query": """ 
      SELECT * FROM "my-sql-index" where page_count > 500
    """ 
}

# SQL转换为DSL使用

当我们需要使用Query DSL时，也可以先使用SQL来查询，然后通过Translate API转换即可，查询的结果为DSL方式的结果

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

# SQL和DSL混合使用

我们如果在优化SQL语句之后还不满足查询需求，可以拿SQL和 DSL 混用，ES会先根据SQL 进行查询，然后根据DSL语句对SQL的执行结果进行二次查询

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

# 常用SQL操作

# 查询所有索引

GET _sql?format=txt
{ 
    "query": """ 
      show tables 
    """ 
}

# 查询指定索引

GET _sql?format=txt
{ 
    "query": """ 
      show tables like 'my-sql-index'
    """ 
}

# 模糊查询索引

GET _sql?format=txt
{ 
    "query": """ 
      show tables like 'my-%'
    """ 
}

# 查看索引结构

# 先创建一个索引
put myindex
{
  "mappings":{
    "properties":{
      "sku_id":{
        "type":"long"
      },
      "sku_name":{
        "type":"text"
      },
      "sku_url":{
        "type":"keyword"
      }
    }
  }
}

GET _sql?format=txt
{ 
    "query": """ 
      describe myindex
    """ 
}

# 基本查询操作

• 在ES中使用SQL查询的语法与在数据库中使用基本一致

# where

# 条件过滤 
POST _sql?format=txt 
{ 
    "query": """ SELECT * FROM "my-sql-index" where name = 'JAVA' """ 
}

# group-by

# 按照日期进行分组 
GET _sql?format=txt 
{ 
    "query": """ 
      SELECT release_date FROM "my-sql-index" group by release_date 
    """ 
}

# having

# 对分组后的数据进行过滤 
GET _sql?format=txt 
{ 
  "query": """ 
    SELECT sum(page_count), release_date as datacnt FROM "my-sql-index" group by release_date having sum(page_count) > 1000 
  """ 
}

# order-by

# 对页面数量进行排序（降序） 
GET _sql?format=txt 
{ 
    "query": """ 
      select * from "my-sql-index" order by page_count desc
    """ 
}

# limit

# 限定查询数量 
GET _sql?format=txt 
{ 
    "query": """ 
      select * from "my-sql-index" limit 3
    """ 
}

# cursor

游标（cursor）是系统为用户开设的一个数据缓冲区，存储sql语句的执行结果，每个游标区都有一个名字，用户可以用 sql 语句逐一从游标中获取记录，并赋给主变量，交由主语言进一步处理。就本质而言，游标实际上是一种能从包括多条数据记录的结果集中每次提取一条或多条记录的机制

# 查询数据 
# 因为查询结果较多，但是获取的数据较少，所以为了提高效果，会将数据存储到临时缓冲区中 
# 此处数据展示格式为   json 
POST _sql?format=json 
{ 
    "query": """ SELECT * FROM "my-sql-index" order by page_count desc """, 
    "fetch_size": 2 
}

返回结果中的cursor就是缓冲区的标识，这就意味着可以从缓冲区中直接获取后续数据，操作上有点类似于迭代器，可多次执行。

# 此处游标cursor值就是上图中的结果
POST /_sql?format=json 
{ 
    "cursor": "v5HqA0RGTACEkd9OwjAUxnvmQgwx8RF8BVG44IKLDew0YRCkUF1MljI6NigtrOVPeCIfwPfTbUDEK7+Lnu80PV+T34EAQYIsQIW+c92WDuVXUI1TLqahVpmprtiMh5HaSIMqYZxm2gAgsIWSs+N7+IIrZFm5KY4y4eNkUFHBrrCNSVSGLFuyJUfWZaZ1k3HBmebhlBkOd9pkaWTKJlQrkyrJRGjSJQ8lk0pb8AnbxvPa2T35k7eFiR6x7lKxCbxxHS/EfEh7a9pJ1NhrdklnqiORKCKSve9FDhaiPhnt7vsjHI/mbi2Yu+3goUEGFMdDGjlntZs+DQ4v+76HiX94JUwK/E5XPb/mpl0SkMHAcf/7y3FaLVQ9crUhPhEA2/C9yZHEJYnf9oLIGS3Ef8lcF0gKEPl4vqofAAAA//8DAA==" 
}

• 如果执行后，无任何结果返回，说明数据已经读取完毕

• 此时再次执行，会返回错误信息

• 如果关闭缓冲区，执行下面指令即可

POST _sql/close
{ 
  "cursor": "v5HqA0RGTACEkUtuwjAQhj1phCpUqUfoFUoLCxYsEmjSSgREMbjNJjLBJgFjQ2we4kQ9QE/Ui7RJAJWu+i9G/4zmIX0DIYIEWYAKfee6LR3KS1DlKRPTSKvMVFd0xqJYbaRBlYinmTYACGyh5OzYD59whSwrN0UoK18ng4rtYFfoxiQqQ5Yt6ZIh63KndZMxwahm0ZQaBnfaZGlsyiRSK5MqSUVk0iWLJJVKW/AB28bz2tk9BZO3hYkfPd0lYhP647q3EPMh6a1JJ1Fjv9nFnamORaKwSPaBHzueEPXJaHffH3l8NHdr4dxthw8NPCAeH5LYOavdDEh4eNn3fQ8Hh1dMpfDeyaoX1Ny0i0M8GDjuf7ccp9VC1SNXG/iJANiG7U2OhJckftMLIme0wP+SuS6QFCDy8fxVPwAAAP//AwA="
}

# 聚合操作

• Min、Max、Avg、Sum、Count(*) 、Distinct

GET _sql?format=txt 
{ 
    "query": """ 
        SELECT 
        MIN(page_count) min, 
        MAX(page_count) max, 
        AVG(page_count) avg, 
        SUM(page_count) sum, 
        COUNT(*) count, 
        COUNT(DISTINCT name) dictinct_count 
        FROM "my-sql-index"
    """
}

# 支持的函数和运算

# 比较运算符

-- Equality 
SELECT * FROM "my-sql-index" WHERE name = 'JAVA' 
 
-- Null Safe Equality 
SELECT 'elastic' <=> null AS "equals" 
SELECT null <=> null AS "equals" 
 
-- Inequality 
SELECT * FROM "my-sql-index" WHERE name <> 'JAVA' 
SELECT * FROM "my-sql-index" WHERE name != 'JAVA' 
 
-- Comparison 
SELECT * FROM "my-sql-index" WHERE page_count > 500 
SELECT * FROM "my-sql-index" WHERE page_count >= 500 
SELECT * FROM "my-sql-index" WHERE page_count < 500 
SELECT * FROM "my-sql-index" WHERE page_count <= 500 
 
-- BETWEEN 
SELECT * FROM "my-sql-index" WHERE page_count between 100 and 500 
 
-- Is Null / Is Not Null 
SELECT * FROM "my-sql-index" WHERE name is not null 
SELECT * FROM "my-sql-index" WHERE name is null 
 
-- IN 
SELECT * FROM "my-sql-index" WHERE name in ('JAVA', 'SCALA')

# 逻辑运算符

-- AND 
SELECT * FROM "my-sql-index" WHERE name = 'JAVA' AND page_count > 100
 
-- OR 
SELECT * FROM "my-sql-index" WHERE name = 'JAVA' OR name = 'SCALA' 
 
-- NOT 
SELECT * FROM "my-sql-index" WHERE NOT name = 'JAVA'

# 数学运算符

# 加减乘除 
select  1 + 1 as x 
select  1 - 1 as x 
select  - 1 as x 
select  6 * 6 as x 
select  30 / 5 as x 
select  30 % 7 as x

# 类型转换

SELECT '123'::long AS long

复制代码

# 模糊查询

-- LIKE 通配符 
SELECT * FROM "my-sql-index" WHERE name like 'JAVA%' 
SELECT * FROM "my-sql-index" WHERE name like 'JAVA_' 
 
-- 如果需要匹配通配符本身,使用转义字符 
SELECT * FROM "my-sql-index" WHERE name like 'JAVA/%' ESCAPE '/' 
 
-- RLIKE 不要误会，这里的  R表示的不是方向，而是正则表示式  Regex 
SELECT * FROM "my-sql-index" WHERE name like 'JAV*A' 
SELECT * FROM "my-sql-index" WHERE name rlike 'JAV*A' 
 
-- 尽管 LIKE在 Elasticsearch SQL 中搜索或过滤时是一个有效的选项，但全文搜索    MATCH和 QUERY 速度更快、功能更强大，并且是首选替代方案。

# 聚合分析函数

-- FIRST / FIRST_VALUE : FIRST(第一个字段，排序字段) 
SELECT  first(name, release_date) FROM "my-sql-index" 
SELECT first_value(substring(name,2,1)) FROM "my-sql-index" 
 
-- LAST / LAST_VALUE : LAST (第一个字段，排序字段) 
SELECT  last(name, release_date) FROM "my-sql-index" 
SELECT  last_value(substring(name,2,1)) FROM "my-sql-index" 
 
-- KURTOSIS 量化字段的峰值分布 
SELECT  KURTOSIS(page_count) FROM "my-sql-index" 
 
-- MAD 
SELECT  MAD(page_count) FROM "my-sql-index"

# 分组函数

-- HISTOGRAM : 直方矩阵 
SELECT HISTOGRAM(page_count, 100) as c，count(*) FROM "my-sql-index" group by c

# 数学通用函数

-- ABS：求数字的绝对值 
select ABS(page_count) from "myindex" limit 5 
 
-- CBRT：求数字的立方根，返回  double 
select page_count v,CBRT(page_count) cbrt from "myindex" limit 5 
 
-- CEIL：返回大于或者等于指定表达式最小整数（double） 
select page_count v,CEIL(page_count) from "myindex" limit 5 
 
-- CEILING：等同于  CEIL 
select page_count v,CEILING(page_count) from "myindex" limit 5 
 
-- E：返回自然常数  e(2.718281828459045) 
select  page_count,E(page_count)  from  "myindex"  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)