elasticsearch-query-toolkit

SQL to DSL for Elasticsearch

elasticsearch-sql2dsl版本

sql2dsl version	ES version
master	1.4.5

sql2dsl介绍

elasticsearch-sql2dsl是一款能将SQL转换为ES的查询语言DSL的工具包，目前只支持比较简单的查询。

使用注意

• 在SQL的where条件中，原子条件的左边必须为变量名，右边必须为查询参数值
• 针对Inner Doc，直接引用即可：a.b.c.d
• 针对Nested Doc，需要在内嵌文档加上$符号： $b.c

Api使用示例

常规sql查询，不带参数

String sql = "select * from index.order where status='SUCCESS' order by nvl(pride, 0) asc limit 0, 20";

ElasticSql2DslParser sql2DslParser = new ElasticSql2DslParser();
//解析SQL
ElasticSqlParseResult parseResult = sql2DslParser.parse(sql);

//生成DSL,可用于rest api调用
String dsl = parseResult.toDsl();

//toRequest方法接收一个clinet对象参数，用于生成SearchRequestBuilder
SearchRequestBuilder searchReq = parseResult.toRequest(esClient);

//执行查询
SearchResponse response = searchReq.execute().actionGet();

指定可变参数查询

String sql = "select * from index.order where status=? order by nvl(pride, 0) asc limit ?, ?";

ElasticSql2DslParser sql2DslParser = new ElasticSql2DslParser();
//指定参数，解析SQL
ElasticSqlParseResult parseResult = sql2DslParser.parse(sql, new Object[]{"SUCCESS", 0, 20});

//生成DSL,可用于rest api调用
String dsl = parseResult.toDsl();

//toRequest方法接收一个clinet对象参数，用于生成SearchRequestBuilder
SearchRequestBuilder searchReq = parseResult.toRequest(esClient);

//执行查询
SearchResponse response = searchReq.execute().actionGet();

向WHERE条件解析时注册监听

String sql = "select * from index.order where status='SUCCESS' and lastUpdateTime > '2017-01-02' order by nvl(pride, 0) asc limit 0, 20";

ElasticSql2DslParser sql2DslParser = new ElasticSql2DslParser();

//指定SQL解析监听器,解析SQL
ElasticSqlParseResult parseResult = sql2DslParser.parse(sql, new ParseActionListenerAdapter() {
    @Override
    public void onAtomConditionParse(ElasticSqlQueryField paramName, Object[] paramValues, SQLConditionOperator operator) {
        if(paramName.getQueryFieldType() == QueryFieldType.RootDocField && "lastUpdateTime".equals(paramName.getQueryFieldFullName())) {
            //这里是解析SQL中原子条件时的回调方法
            //某些按时间划分的索引,可在此解析出SQL中指定的时间范围,并重新设置需要查询的索引
        }
    }
});

//生成DSL,可用于rest api调用
String dsl = parseResult.toDsl();

//toRequest方法接收一个clinet对象参数，用于生成SearchRequestBuilder
SearchRequestBuilder searchReq = parseResult.toRequest(esClient);

//执行查询
SearchResponse response = searchReq.execute().actionGet();

使用routing by指定routing参数

String sql = "select * from index.order where status='SUCCESS' order by nvl(pride, 0) asc routing by 'A','B' limit 0, 20";

ElasticSql2DslParser sql2DslParser = new ElasticSql2DslParser();
//解析SQL，
ElasticSqlParseResult parseResult = sql2DslParser.parse(sql);

//toRequest方法会把指定的routing参数放到SearchRequest中
SearchRequestBuilder searchReq = parseResult.toRequest(esClient);

SQL使用示例

下面index表示索引名，order表示文档类型名

select * from index.order

# 1. 默认从第0条数据开始，取15条
# 2. 因为没有带where条件所以查询是match_all
{
  "from" : 0,
  "size" : 15,
  "query" : {
    "match_all" : { }
  }
}

用关键字limit指定分页参数

select * from index.order limit 0,100

# 分页参数从0开始取100条
{
  "from" : 0,
  "size" : 100,
  "query" : {
    "match_all" : { }
  }
}

SQL中指定查询条件 status = 'SUCCESS'

select * from index.order where status='SUCCESS' limit 0,100

{
  "from" : 0,
  "size" : 100,
  "query" : {
    "filtered" : {
      "filter" : {
        "bool" : {
          "must" : {
            "term" : {
              "status" : "SUCCESS"
            }
          }
        }
      }
    }
  }
}

范围查询 totalPrice > 1000

select * from index.order where status='SUCCESS' and totalPrice > 1000 limit 0,100

{
  "from" : 0,
  "size" : 100,
  "query" : {
    "filtered" : {
      "filter" : {
        "bool" : {
          "must" : [ {
            "term" : {
              "status" : "SUCCESS"
            }
          }, {
            "range" : {
              "totalPrice" : {
                "from" : 1000,
                "to" : null,
                "include_lower" : false,
                "include_upper" : true
              }
            }
          } ]
        }
      }
    }
  }
}

使用 between...and... 指定条件范围（上下限都取）

select * from index.order where status='SUCCESS' and totalPrice between 1000 and 2000 limit 0,100

{
  "from" : 0,
  "size" : 100,
  "query" : {
    "filtered" : {
      "filter" : {
        "bool" : {
          "must" : [ {
            "term" : {
              "status" : "SUCCESS"
            }
          }, {
            "range" : {
              "totalPrice" : {
                "from" : 1000,
                "to" : 2000,
                "include_lower" : true,
                "include_upper" : true
              }
            }
          } ]
        }
      }
    }
  }
}

日期类型范围查询，下面3条SQL等效

select * from index.order where status='SUCCESS' and lastUpdateTime > '2017-01-01 00:00:00' limit 0,100
select * from index.order where status='SUCCESS' and lastUpdateTime > '2017-01-01' limit 0,100

#日期函数可以自定义日期格式
select * from index.order where status='SUCCESS' and lastUpdateTime > date('yyyy-MM-dd', '2017-01-01') limit 0,100

{
  "from" : 0,
  "size" : 100,
  "query" : {
    "filtered" : {
      "filter" : {
        "bool" : {
          "must" : [ {
            "term" : {
              "status" : "SUCCESS"
            }
          }, {
            "range" : {
              "lastUpdateTime" : {
                "from" : "2017-01-01T00:00:00.000+0800",
                "to" : null,
                "include_lower" : false,
                "include_upper" : true
              }
            }
          } ]
        }
      }
    }
  }
}

排序条件，price升序，publishDate降序

select * from index.order where status='SUCCESS' order by price asc, publishDate desc

{
  "from" : 0,
  "size" : 15,
  "query" : {
    "filtered" : {
      "filter" : {
        "bool" : {
          "must" : {
            "term" : {
              "status" : "SUCCESS"
            }
          }
        }
      }
    }
  },
  "sort" : [ {
    "price" : {
      "order" : "asc"
    }
  }, {
    "publishDate" : {
      "order" : "desc"
    }
  } ]
}

使用nvl函数指定默认值

select * from index.order where status='SUCCESS' order by nvl(price, 0) asc, publishDate desc

{
  "from" : 0,
  "size" : 15,
  "query" : {
    "filtered" : {
      "filter" : {
        "bool" : {
          "must" : {
            "term" : {
              "status" : "SUCCESS"
            }
          }
        }
      }
    }
  },
  "sort" : [ {
    "price" : {
      "order" : "asc",
      "missing" : 0
    }
  }, {
    "publishDate" : {
      "order" : "desc"
    }
  } ]
}

内嵌文档排序，指定sort_mode（其中$providers 表示内嵌文档），按照 min 方式排序

select * from index.order where status='SUCCESS' order by nvl(product.$providers.sortNo, 0, 'min') asc, publishDate desc

{
  "from" : 0,
  "size" : 15,
  "query" : {
    "filtered" : {
      "filter" : {
        "bool" : {
          "must" : {
            "term" : {
              "status" : "SUCCESS"
            }
          }
        }
      }
    }
  },
  "sort" : [ {
    "product.providers.sortNo" : {
      "order" : "asc",
      "missing" : 0,
      "mode" : "min",
      "nested_path" : "product.providers"
    }
  }, {
    "publishDate" : {
      "order" : "desc"
    }
  } ]
}

Inner Doc 查询

select * from index.order where seller.name='JD' order by id desc

{
  "from" : 0,
  "size" : 15,
  "query" : {
    "filtered" : {
      "filter" : {
        "bool" : {
          "must" : {
            "term" : {
              "seller.name" : "JD"
            }
          }
        }
      }
    }
  },
  "sort" : [ {
    "id" : {
      "order" : "desc"
    }
  } ]
}

Nested Doc查询（$providers 表示内嵌文档）

select * from index.order where product.$providers.name in ('JD', 'TB') and product.price < 1000 order by id desc

{
  "from" : 0,
  "size" : 15,
  "query" : {
    "filtered" : {
      "filter" : {
        "bool" : {
          "must" : [ {
            "range" : {
              "product.price" : {
                "from" : null,
                "to" : 1000,
                "include_lower" : true,
                "include_upper" : false
              }
            }
          }, {
            "nested" : {
              "filter" : {
                "terms" : {
                  "product.providers.name" : [ "JD", "TB" ]
                }
              },
              "path" : "product.providers"
            }
          } ]
        }
      }
    }
  },
  "sort" : [ {
    "id" : {
      "order" : "desc"
    }
  } ]
}

组合条件查询

select * from index.order
where
(product.$providers.name in ('JD', 'TB') or product.$providers.channel='ONLINE')
and
(product.status='OPEN' or product.price < 1000)
order by id desc

{
  "from" : 0,
  "size" : 15,
  "query" : {
    "filtered" : {
      "filter" : {
        "bool" : {
          "must" : [ {
            "bool" : {
              "should" : {
                "nested" : {
                  "filter" : {
                    "bool" : {
                      "should" : [ {
                        "terms" : {
                          "product.providers.name" : [ "JD", "TB" ]
                        }
                      }, {
                        "term" : {
                          "product.providers.channel" : "ONLINE"
                        }
                      } ]
                    }
                  },
                  "path" : "product.providers"
                }
              }
            }
          }, {
            "bool" : {
              "should" : [ {
                "term" : {
                  "product.status" : "OPEN"
                }
              }, {
                "range" : {
                  "product.price" : {
                    "from" : null,
                    "to" : 1000,
                    "include_lower" : true,
                    "include_upper" : false
                  }
                }
              } ]
            }
          } ]
        }
      }
    }
  },
  "sort" : [ {
    "id" : {
      "order" : "desc"
    }
  } ]
}

查询字段指定

select totalPrice, product.*, product.$seller.* from index.order

{
  "from" : 0,
  "size" : 15,
  "query" : {
    "match_all" : { }
  },
  "_source" : {
    "includes" : [ "totalPrice", "product.*", "product.seller.*" ],
    "excludes" : [ ]
  }
}

聚合统计，目前只支持 terms，range 聚合

select min(price),avg(price) from index.product group by terms(category),terms(color),range(price, segment(0,100), segment(100,200), segment(200,300))


{
  "from" : 0,
  "size" : 15,
  "query" : {
    "match_all" : { }
  },
  "aggregations" : {
    "agg_category" : {
      "terms" : {
        "field" : "category",
        "size" : 500,
        "shard_size" : 1000,
        "min_doc_count" : 1,
        "shard_min_doc_count" : 1,
        "order" : {
          "_count" : "desc"
        }
      },
      "aggregations" : {
        "agg_color" : {
          "terms" : {
            "field" : "color",
            "size" : 500,
            "shard_size" : 1000,
            "min_doc_count" : 1,
            "shard_min_doc_count" : 1,
            "order" : {
              "_count" : "desc"
            }
          },
          "aggregations" : {
            "agg_price" : {
              "range" : {
                "field" : "price",
                "ranges" : [ {
                  "key" : "0-100",
                  "from" : 0.0,
                  "to" : 100.0
                }, {
                  "key" : "100-200",
                  "from" : 100.0,
                  "to" : 200.0
                }, {
                  "key" : "200-300",
                  "from" : 200.0,
                  "to" : 300.0
                } ]
              },
              "aggregations" : {
                "min_price" : {
                  "min" : {
                    "field" : "price"
                  }
                },
                "avg_price" : {
                  "avg" : {
                    "field" : "price"
                  }
                }
              }
            }
          }
        }
      }
    }
  }
}

作者: [@陈楠][1] Email: 465360798@qq.com

<完>