es 3期 第24节-运用SQL简化DSL查询

发布于：2025-02-10 ⋅ 阅读:(46) ⋅ 点赞:(0)

#### 1.Elasticsearch是数据库，不是普通的Java应用程序，传统数据库需要的硬件资源同样需要，提升性能最有效的就是升级硬件。
#### 2.Elasticsearch是文档型数据库，不是关系型数据库，不具备严格的ACID事务特性，任何企图直接替代严格事务性场景的应用项目都会失败!!!
#### 3.Elasticsearch原则上适合一切非事务性应用场景或能够容许一定的延迟的事务性场景;能最大限度的替代mongodb与传统关系型数据库

##### 索引字段与属性都属于静态设置，若后期变更历史数据需要重建索引才可生效
##### 对历史数据无效！！！！
##### 一定要重建索引！！！！


#### 1、ESSOL概要介绍SOL需求背景
###SOL需求背景
# DSL难于掌握，语法过于复杂
# SOL拥有广大的群众基础，便于推广
# ES-JDBC能力，便于与其它JDBC混搭
# SOL是大数据产品的标配
# 早期基于NLPChina插件实现
# SQL -> DSL -> 执行

### SQL概念映射
## 数据库与ES概念映射Elasticsearch
## sql                Elasticsearch   
# 数据列 column       字段 field
# 数据行 row          数据文档 document
# 数据表 table        索引index
# 表结构 schema       映射 mapping
# 数据库 database     ES集群 cluster

### SQL执行接口
# SQL执行接口执行接口，执行SOL语言表达式
# GET _sql
  

#### 2、SOL常用查询
## 查询参数
# _sql，执行sql查询 API
# format，返回结果的格式化，支持多种，默认ison
# query，执行SOL脚本输入

# dsl查询方式
GET kibana_sample_data_flights/_search
{
  "track_total_hits":true,
  "size":1
}
# sql查询方式
GET _sql
{
  "query":"""
    select * from "kibana_sample_data_flights"
  """
}

## 通过对比结果发现
# dsl每条数据都是json格式，包含了key/value
# sql类似于excel，columns单独返回了表头，rows中只有value，数据包减小了，可以作为性能优化点

# 返回1条数据和指定字段

GET _sql?format=json
{
  "query":"""
    select AvgTicketPrice, Cancelled from "kibana_sample_data_flights" limit 1
  """
}


### format 内容格式
# 支持多种响应格式，默认 Json
# 查询参数
# 参数说明
# json    默认 ，相比原有 dsl-json 大大减少资源
# csv     Excel 表格，分隔符
# tsv
# txt     文本
# yaml
# cbor    二进制编码
# smile   二进制编码

# csv格式

# csv格式
GET _sql?format=csv
{
  "query":"""
    select AvgTicketPrice, Cancelled from "kibana_sample_data_flights" limit 1
  """
}
# yaml格式
GET _sql?format=yaml
{
  "query":"""
    select AvgTicketPrice, Cancelled from "kibana_sample_data_flights" limit 1
  """
}

 

### SQL 分页
# SOL执行查询下，分页采用的是快照方式，基于游标
# 查询参数
# fetch_size，分页大小，默认 1000

# 分页查询，注意返回结果中的cursor，cursor相当于游标，用于翻页

GET _sql
{
  "query":"""
    select * from "kibana_sample_data_flights"
  """,
  "fetch_size": 2
}

 # 使用cursor翻页，每次都要重新获取上一次结果的cursor作为下一页的入参

GET _sql
{"cursor": "i/vrA0RGTACEVMty2jAUlVwohYRkptss+gVdQNPpZGkwhCTFFAKYeOMRlrAVhMXYIgz/1UX/LpVsEyxCp9pY99yr+5DOMXQBDIEBgVqvcl2mOyAhWFtQwrCX8FiUPMz91yxKLfgbfgCGITfp0bLcpassEZUFXl2YL8GY+ksifsXUJ8CotlHkE8YIBkaljeKYkhgYJYskAhh19TFpvJa17ixgnCu7TcXORit59iw1+SYS8S53/uQ+EpRHwKgp54gEqZFGOgSJUGX/bNFEqLIPlPEVESROVK0c7FNGpH3WZTQIhUUYkskvClafyoyXBWC8W6tJMsTerN7Cx3RFenwjS9YzpwLS4x8HMQ3SPNmmMORFhhzGrOfAftA84DDqeRawHzaPfxu3itFusHAIWcomhexADr9awy+JkE8gPIwE8fha3RpinvJ7EYp4YsA/kHovsbntINRk9Mn5Hs6+2eF81krc6c3yaTYKZ003QQ7muNd30G2j50/X4bgRjvD4/sGdjPpO52bQn7nJZGg6w6bbmDt2H3fuH+2OL3BjaqFbvPUtNsBbM19f0Y+Wa82du63btVuD6SgkE/t6OA4f58+ta7Lsjkyz879aJqjltL2CiyPKwfKCcSQkIxcH7sHKnHNGUKTgPQ1hZUl2Wx5jBaaM1BCdnJpL46nmKVL2/ZH9k8JqQLi35jRK+yxQ+X2y/Jk1xwmCF8bWmV5wFCmv3cgR+2FFNkYCKSXZ3LEQtEYOmtDgPN9eHvq1akopdJdLRgs+Vo9eRReSXkXT1Klj/3gLTWunUp56j4MCtasriPGTUqHSHgAl+aOsAPAXAAD//wMA"
}

 

### 结果行转列
# 在部分场景下，我们需要提取数据按照列示的方式，可以减少转换
# ES 默认情况下，类数据全部是按照行式返回的
# 目前限定 format格式必须是json/yaml才可以
# 查询参数
# columnar，取值范围 true/false

# columnar行转列,查询目的地城市名

GET _sql
{
  "query":"""
    select DestCityName from "kibana_sample_data_flights"
  """,
  "fetch_size": 2,
  "columnar":true
}

# 默认的,查询目的地城市名，对比两或者差异

GET _sql
{
  "query":"""
    select DestCityName from "kibana_sample_data_flights"
  """,
  "fetch_size": 2
}

### 查询过滤条件参数化
# ES 提供的 SOL查询近可能的靠近 SOL的表达能力，但与标准的SOL还是有点差距
# 查询参数
# params，提供参数化

# sql中入参

GET _sql
{
  "query":"""
    select * from "kibana_sample_data_flights" where DestCountry='CN'
  """,
  "fetch_size": 2
}

# 占位符参数

GET _sql
{
  "query":"""
    select * from "kibana_sample_data_flights" where DestCountry=?
  """,
  "fetch_size": 2,
  "params":["CN"]
}

### SOL与 DSL混合限制查询
# SOL执行查询时，也可以基于 DSL编写限制过滤条件
# 查询参数
# filter，限制条件查询关键字表达式

# dsl查询方式

GET kibana_sample_data_flights/_search
{
  "track_total_hits":true,
  "size":1,
  "query":{
    "bool": {
      "filter": [
        {
          "term": {
            "DestCountry": "CN"
          }
        }
      ]
    }
  }
}

# 混合查询方式

GET _sql
{
  "query": """
    select * from "kibana_sample_data_flights"
  """,
  "fetch_size": 2,
  "filter": {
    "term": {
      "DestCountry": "CN"
    }
  }
}


### sql 翻译成 dsl

GET _sql/translate
{
  "query":"""
    select DestCityName,DistanceMiles from "kibana_sample_data_flights" where DestCountry='CN'
  """,
  "fetch_size": 2
}

# 使用上面的返回结果查询

GET kibana_sample_data_flights/_search
{
  "size": 2,
  "query": {
    "term": {
      "DestCountry": {
        "value": "CN"
      }
    }
  },
  "_source": false,
  "fields": [
    {
      "field": "DestCityName"
    },
    {
      "field": "DistanceMiles"
    }
  ],
  "sort": [
    {
      "_doc": {
        "order": "asc"
      }
    }
  ],
  "track_total_hits": -1
}


### 常用 SQL
# SQL语法
# SQL查询语法几乎与常用的 SOL标准是一样的
# 查询语法
# SELECT [TOP [ count ]]select_expr [, ...]
# FROM table name.
# [WHERE condition ]
# [ GROUP BY grouping _element [,..]]
# [ HAVING condition]
# # [ORDER BY expression [ASC DESC ][,...]]
# [ LIMIT [count ]]
# [ PIVOT ( aggregation_expr FOR column IN ( value [ [ AS ] alias ][, ..] ))]


### 精确查询
# dsl查询方式

GET kibana_sample_data_flights/_search
{
  "track_total_hits":true,
  "size":1,
  "query":{
    "bool": {
      "filter": [
        {
          "term": {
            "DestCountry": "CN"
          }
        }
      ]
    }
  }
}

# sql 

GET _sql
{
  "query":"""
    select DestCountry,DestCityName,DistanceMiles from "kibana_sample_data_flights" where DestCountry='CN'
  """,
  "fetch_size": 2
}

### 分词查询

GET kibana_sample_data_flights_3share/_mappings

# dsl查询方式, 使用kibana_sample_data_flights_3share索引

GET kibana_sample_data_flights_3share/_search
{
  "track_total_hits": true,
  "size": 1,
  "query": {
    "match": {
      "Dest": "International"
    }
  }
}

 # sql 分词,注意排序与dsl不同，最好加order by

GET _sql
{
  "query":"""
    select DestCountry,DestCityName,DistanceMiles from "kibana_sample_data_flights_3share" where match(Dest,'International')
  """,
  "fetch_size": 2
}

### sql 常用聚合
# 指标聚合、分桶聚合、管道聚合、混合聚合

# avg，dsl查询方式

GET kibana_sample_data_flights/_search
{
  "track_total_hits":true,
  "size":0,
  "aggs":{
    "avg_FlightTimeMin":{
      "avg": {
        "field": "FlightTimeMin"
      }
    }
  }
}

# avg，sql查询方式

GET _sql
{
  "query":"""
    select avg(FlightTimeMin) from "kibana_sample_data_flights"
  """
}

# 分组聚合，dsl方式

GET kibana_sample_data_flights/_search
{
  "track_total_hits": true,
  "size": 0,
  "aggs": {
    "trems_DestCountry": {
      "terms": {
        "field": "DestCountry",
        "size": 100,
        "order": {
          "_key": "asc"
        }
      },
      "aggs": {
        "stats_FlightTimeMin": {
          "stats": {
            "field": "FlightTimeMin"
          }
        }
      }
    }
  }
}

# 分组聚合，sql查询方式

GET _sql
{
  "query":"""
    select avg(FlightTimeMin) as avg, max(FlightTimeMin) as max, min(FlightTimeMin) as min, DestCountry from "kibana_sample_data_flights" group by DestCountry
  """
}


  
#### 3、SOL高级查询

## 查询所有索引

GET _cat/indices
GET _sql
{
  "query":"""
  show tables
  """
}

## 查询支持的函数

GET _sql
{
  "query":"""
  show functions
  """
}

## 查询所有列

GET _sql
{
  "query":"""
  show columns in "kibana_sample_data_flights"
  """
}
GET _sql
{
  "query":"""
  describe "kibana_sample_data_flights"
  """
}

## 时间函数

GET _sql
{
  "query":"""
    select CURRENT_DATE(),CURRENT_DATE,CURRENT_TIME(),CURRENT_TIME,CURRENT_TIMESTAMP
(),CURRENT_TIMESTAMP,DATEADD('year',1,CURRENT_DATE)
 """
}

## case

GET _sql
{
  "query":"""
    SELECT CASE 3
    WHEN 1 THEN 'one'
    WHEN 3 THEN 'three'
    WHEN 5 THEN 'five'
    END AS "case"
 """
}


## SQL 函数
# 全文文本函数
# ESSOL全文检索与标准 SOL有差异，采用定制函数的方式实现
# MATCH，查询函数关键字
# 查询出发地国家包括“CN"的航班信息

GET _sql
{
  "query":"""
    select DestCountry,DestCityName,DistanceMiles from "kibana_sample_data_flights_3share" where match(DestCountry,'CN')
  """,
  "fetch_size": 2
}

## 常用聚合函数
# AVG，SUM ，MIN，MAXCOUNT
# FIRST, LAST, FIRST_VALUE, LAST_VALUE

# 提取航班信息中，第一个出发地国家，最后一个出发地国家

GET _sql
{
  "query":"""
    select FIRST(DestCountry) as first, FIRST_VALUE(DestCountry) FIRST_VALUE,LAST(DestCountry) as last, min(FlightTimeMin) as min from "kibana_sample_data_flights"
  """
}

## 统计类聚合函数
# PERCENTILE, PERCENTILE_RANK
# 其它聚合统计函数，查阅官方网站

# 基于百分位占比，统计航班信息中飞行时间长百分位占比;第1个，统计第95 百分位的飞行时间平均时间;第2个，统计飞行时间为 700的百分位占比

GET _sql
{
  "query":"""
    select PERCENTILE(FlightTimeMin,95), PERCENTILE_RANK(FlightTimeMin,700) from "kibana_sample_data_flights"
  """
}

## 分桶类聚合函数
# HISTOGRAM，直方图统计函数，必须带上group关键字
# 按照飞行时间统计所有航班信息，按照100 间隔;此处对比结果，并没有 DSL表达能力好

GET _sql
{
  "query":"""
    select HISTOGRAM(FlightTimeMin,100) as h,count(0) from "kibana_sample_data_flights" group by h
  """
}

## 数学函数
# ESSOL支持很多数学函数
# ABS
# 绝对值查询，去掉负数;对比 DSL没有此功能，需要通过脚本的机制转换

GET _sql
{
  "query":"""
    select abs(-10)
  """
}

## SQL操作符
# 条件比对、逻辑条件、数学计算、Like模糊查询

## SQL限制性
# 类型限制:数组类型不支持
# ESSQL并非完全支持标准SOL，很多SOL独有的功能不具备;
# DSL表达能力很丰富，SOL无法实现;

#### 4、JDBC与BI报表可视化

### JDBC应用
# JDBC应用配置JDBC驱动，客户端工具可直接连接或者Java代码可直接访问。

# JDBC客户端
# Dbeaver
# Java代码
# ES-SQL-CLI工具

## tips：
# 1、需要授权使用；
# 2、不支持insert；

### BI报表可视化
## Tableau
# BI可视化
# 基于jdbc直接可以集成报表工具，满足BI需求

## Qlik sense
# BI可视化
# 基于idbc直接可以集成报表工具，满足B1需求


#### 5、SOL实战建议与经验分享
## 简单查询场景尽量使用SQL
## DSL依然是最强大的领域搜索语言SOL目前无法完全取代DSL
## SOL查询比DSL查询更适合应用（报头小）
## SQL自身局限性

#### 6、PrestoDB查询引擎
## 架构概要


# 支持连表
## Prestodb数据源
# 支持数据源
# 1.支持同时配置多个不同集群ES数据源
# 2.支持同时配置多个不同版本的ES数据源

## Prestodb查询
# 复杂查询
# 1.基于Prestodb引擎支持关联Join查询、支持多级嵌套查询
# 2.支持跨多个ES数据源中心查询

# es-sql 官方文档
# https://www.elastic.co/guide/en/elasticsearch/reference/8.6/xpack-sql.html
# sql-spec 语言语法
# https://www.elastic.co/guide/en/elasticsearch/reference/8.6/sql-spec.html
# sql-functions 函数操作符
# https://www.elastic.co/guide/en/elasticsearch/reference/8.6/sql-functions.html
# sql-limitations 限制条件
# https://www.elastic.co/guide/en/elasticsearch/reference/current/sgl-limitations.html
# sql-client-apps 客户端工具
# https://www.elastic.co/guide/en/elasticsearch/reference/8.6/sgl-client-apps.html
# elasticsearch-sql 三方开源支持
# https://github.com/NLPchina/elasticsearch-sql
# dbeaver 官方下载
# https://dbeaver.io
# prestodb 官方参考
# https://prestodb.io/docs/current
# prestodb-elasticsearch 连接器配置
# https://prestodb.io/docs/current/connector/elasticsearch.html

网站公告

今日签到

点亮在社区的每一天
去签到

热门文章

最新发布