京东自研SQL4ES使用说明-京东云开发者社区

### 1 简介

该工具最初是bdp平台开发的，后来由于功能没人维护了，所以我们在此基础上做了一些函数支持和功能拓展。完全京东自主实现的，没有依赖其他sql引擎的工具包。我们也提供了sql验证的工具。可以使用http://dbcp.jdl.cn/ 直接访问验证。

### 2 接入方式

#### 2.1 Step1: pom引入包

目前最新的版本是

```java
<sql4es.version>6.1.0-SNAPSHOT</sql4es.version>
```

```java
<dependency>
    <groupId>com.jd.visual</groupId>
    <artifactId>sql4es-jdbc</artifactId>
    <version>${sql4es.version}</version>
</dependency>
<dependency>
    <groupId>com.jd.visual</groupId>
    <artifactId>sql4es-core</artifactId>
    <version>${sql4es.version}</version>
</dependency>
<dependency>
    <groupId>com.jd.visual</groupId>
    <artifactId>sql4es-common</artifactId>
    <version>${sql4es.version}</version>
</dependency>
```

#### 2.2 Step2:通过jdbcp连接：

和mysql基本一样连接，注意的是ip和端口需要tcp的

```java
Class.forName("com.jd.visual.sql4es.jdbc.Driver");
Connection con = DriverManager.getConnection("jdbc:elasticsearch://ip:port,ip1:port1/index名称?" +
                "cluster.name=集群名","用户名","密码");
Statement st = con.createStatement();
ResultSet rs = st.executeQuery("sql语句");
if(rs.next()){
    System.out.println(rs);
}
con.close();
```

### 3 支持语法

Tip：目前只支持Select查询操作，（insert、update、delete）暂未支持

#### 3.1 Documentation

##### 3.1.1 Query SQL

```sql
SELECT {derived column list}|{es internal column}
    FROM [index1[,index2 ...].]type
	[WHERE {where condition}]
	[ORDER BY order-specifications] 
	[LIMIT [offset], count]
```

note :
```sql
{derived column list}: '*' or comma-separated list of field names or constant and they can have aliases
{es internal column}: elasticsearch internal field, _id、_version、_timestamp
{where condition}: 
	{field name} [>, >=, <, <=, =, <>] value
	{field name} like {pattern}
	{field name} not like {pattern}
	{field name} match {pattern}
	{field name} in(value1, value2, ...)
	{field name} not in(value1, value2, ...)
	{field name} between min-value and max-value
	{field name} not between min-value and max-value
	{where condition} and {where condition}
	{where condition} or {where condition}
{order-specifications}:
    comma-separated list of {derived column name ASC|DESC [NULLS FIRST|NULLS LAST]}
{offset}:
    start index of query result to returned, similar to "from" of elasticsearch
{count}:
    max count of rows to returned, similar to "size" of elasticsearch
{pattern}:
	consist of '%' and string value.
```

##### 3.1.2 Aggregation SQL

```sql
SELECT {aggregation expression list}|{group by column list}|{value}
	FROM [index1[,index2 ...].]type
	[WHERE {where condition}]
	[GROUP BY {group column list}|{bucket functions}]
```

note :

```sql
{aggregation expression list}: 
	comma-separated list of aggregate functions and they can have aliases
{group by column list}: 
	comma-separated list of field names, and the field must be exist in group column list.
{where condition}: 
	similar to "where condition" of Query SQL
{group column list}: 
	comma-separated list of field names
{aggregate functions}: 
	sum(field_name), count('*'|field_name), avg(field_name), max(field_name), min(field_name)
{bucket functions}:
    terms(field_name, terms_size), range(field_name, value1, value2, ...), date_range(field_name, value1, value2, ...), histogram(field_name, interval), date_histogram(field_name, interval[, format])
```

##### 3.1.3 DataType

```java
double, float, long, integer, boolean, string, date
```

##### 3.1.4 JDBC URL

```java
jdbc:elasticsearch://ip:port[,ip:port ...]/database?cluster.name=clusterName[&propertyName=propertyValue...]
```

properties

```java
debugMode: Set sql4es execute with debug mode on, default false.

clientScanSize: Set default query count of rows, default 1000.

clientScanMaxSize: Set max query count of rows, default 100000.

optimizeSingleTable: Optimize query when index has only one type, then search without type, default false.

statementCacheSize: Set statement cache size, default 1000.

termsAggregationDefaultSize: Set terms aggregation default size, default 10000.

fetchFieldSource: Set fetch result of column value from source, options: _source,fields,fielddata_fields, default _source.

preferencePrimaryShards: Set query only be executed on the primary shards, default false.

metadataRefreshInterval: Set metadata cache refresh interval, defualt 10m.

metadataAutoRefresh: Set metadata cache auto refresh, defualt 
```

#### 3.2 Spring DataSource

```java
<bean id="dataSource" class="com.jd.visual.sql4es.jdbc.DataSource" destroy-method="close">
    <property name="url" value="${jdbc.url}"/>
</bean>
```

### 4 个性函数支持

#### 4.1 date_histogram：时间桶函数

```sql
SELECT timeField FROM table WHERE condition GROUP BY date_histogram(timeField, '1h')
```

#### 4.2 count_distinct：去重统计函数

```sql
SELECT count_distinct(字段名) FROM table WHERE condition；
```

##### 4.3 nested：内部列查询

样例：

```json
SELECT
NESTED(field, nestField),
count(field)
FROM
table
WHERE
codition
GROUP BY
NESTED(exceptionDetailList, exceptionCode)
```

### 5  应用案例

目前冷链工作台、lv数字仓储等系统都有在使用。例如我们查询一个【生鲜仓单量明细】进行比对。

#### 5.1 原生的es查询

```
GET starflow_order_demension/_search
{
"query": {
"bool": {
"filter": [{
"term": {
"order_type": 4
}
},{
"range": {
"storage_package_time" :{
"gte" : 1632758400000
}
}
},{
"term": {
"storage_type_code": 20
}
},{
"terms": {
"service_type": [1,2,3,4,5,6,7,100,101]
}
},{
"exists": {
"field" : "dms_org_name"
}
}],"must_not": [
{
"term": {
"valid_flag": 0
}
},{
"term": {
"large_flag": 1
}
},{
"term": {
"reverse_flag": 1
}
},{
"terms": {
"transport_type": [1,2]
}
},{
"term": {
"dms_org_name": "-"
}
}

]
}
}
}
```

#### 5.2 sql查询

```sql
SELECT
  *
FROM
  starflow_order_demension
WHERE
  valid_flag ! = '0'
  and large_flag ! = 1
  and reverse_flag ! = 1
  and order_type = 4
  and service_type in (1, 2, 3, 4, 5, 6, 7, 100, 101)
  and dms_org_name is not null
  and dms_org_name ! = '-'
  and transport_type not in (1, 2)
  and storage_package_time >= date_add(date(now()), INTERVAL - 0 DAY)
```

### 6 总结

sql4es组件实现了很多Sql函数和Es语法之间的映射，而且我们也增加了对ES常见的时区问题的兼容。可以通过参数的形式去选择时区。还有就是一些Es的专用函数也有在持续支持中。

通过使用该sql4es组件大大降低了使用es查询的使用门槛。将es的api和使用者进行了隔离，使用者只要懂jdbc和sql语法即可使用ES，而且我们提供了sql查询小工具，可以很方便的验证查询结果。代码和工作效率都可以得到较大提升。

------------

###### 自猿其说Tech-京东物流技术发展部
###### 作者：王秋晓