1. 利用metricbeat和jolokia监控java应用。 http://t.cn/Rr9N1fw

2. es2.3.5修改源代码获取dismax每个字段的得分。 http://t.cn/Rr9NdSb

3. 你真的会打 Log 吗？ http://t.cn/Rr9NFh8

活动预告：

1. 6月30日南京meetup参会报名中 https://elasticsearch.cn/m/article/647

2. 7月21日上海meetup演讲申请中 https://elasticsearch.cn/m/article/655

• 编辑：金桥

• 归档：https://elasticsearch.cn/article/689

• 订阅：https://tinyletter.com/elastic-daily

• SoundCloud：https://soundcloud.com/elastic ... eleme
• 喜马拉雅：http://www.ximalaya.com/keji/14965410/94803743
• 蜻蜓 FM：http://share.qingting.fm/vchan ... 12932

最近发布的 Elasticsearch 6.3 包含了大家期待已久的 SQL 特性，今天给大家介绍一下具体的使用方法。

首先看看接口的支持情况

目前支持的 SQL 只能进行数据的查询只读操作，不能进行数据的修改，所以我们的数据插入还是要走之前的常规索引接口。

目前 Elasticsearch 的支持 SQL 命令只有以下几个：

我们分别来看一下各自怎么用，以及有什么效果吧，自己也可以动手试一下，看看。

首先，我们创建一条数据：

POST twitter/doc/
{
  "name":"medcl",
  "twitter":"sql is awesome",
  "date":"2018-07-27",
  "id":123
}

RESTful下调用SQL

在 ES 里面执行 SQL 语句，有三种方式，第一种是 RESTful 方式，第二种是 SQL-CLI 命令行工具，第三种是通过 JDBC 来连接 ES，执行的 SQL 语句其实都一样，我们先以 RESTful 方式来说明用法。

RESTful 的语法如下：

POST /_xpack/sql?format=txt
{
    "query": "SELECT * FROM twitter"
}

因为 SQL 特性是 xpack 的免费功能，所以是在 _xpack 这个路径下面，我们只需要把 SQL 语句传给 query 字段就行了，注意最后面不要加上 ; 结尾，注意是不要！

我们执行上面的语句，查询返回的结果如下：

          date          |      id       |     name      |    twitter    
------------------------+---------------+---------------+---------------
2018-07-27T00:00:00.000Z|123            |medcl          |sql is awesome 

ES 俨然已经变成 SQL 数据库了，我们再看看如何获取所有的索引列表：

POST /_xpack/sql?format=txt
{
    "query": "SHOW tables"
}

返回如下：

              name               |     type      
---------------------------------+---------------
.kibana                          |BASE TABLE     
.monitoring-alerts-6             |BASE TABLE     
.monitoring-es-6-2018.06.21      |BASE TABLE     
.monitoring-es-6-2018.06.26      |BASE TABLE     
.monitoring-es-6-2018.06.27      |BASE TABLE     
.monitoring-kibana-6-2018.06.21  |BASE TABLE     
.monitoring-kibana-6-2018.06.26  |BASE TABLE     
.monitoring-kibana-6-2018.06.27  |BASE TABLE     
.monitoring-logstash-6-2018.06.20|BASE TABLE     
.reporting-2018.06.24            |BASE TABLE     
.triggered_watches               |BASE TABLE     
.watcher-history-7-2018.06.20    |BASE TABLE     
.watcher-history-7-2018.06.21    |BASE TABLE     
.watcher-history-7-2018.06.26    |BASE TABLE     
.watcher-history-7-2018.06.27    |BASE TABLE     
.watches                         |BASE TABLE     
apache_elastic_example           |BASE TABLE     
forum-mysql                      |BASE TABLE     
twitter      

有点多，我们可以按名称过滤，如 twitt 开头的索引，注意通配符只支持 %和 _，分别表示多个和单个字符（什么，不记得了，回去翻数据库的书去！）：

POST /_xpack/sql?format=txt
{
    "query": "SHOW TABLES 'twit%'"
}

POST /_xpack/sql?format=txt
{
    "query": "SHOW TABLES 'twitte_'"
}

上面返回的结果都是：

     name      |     type      
---------------+---------------
twitter        |BASE TABLE     

如果要查看该索引的字段和元数据，如下：

POST /_xpack/sql?format=txt
{
    "query": "DESC twitter"
}

返回：

    column     |     type      
---------------+---------------
date           |TIMESTAMP      
id             |BIGINT         
name           |VARCHAR        
name.keyword   |VARCHAR        
twitter        |VARCHAR        
twitter.keyword|VARCHAR        

都是动态生成的字段，包含了 .keyword 字段。 还能使用下面的命令来查看，主要是兼容 SQL 语法。

POST /_xpack/sql?format=txt
{
    "query": "SHOW COLUMNS IN twitter"
}

另外，如果不记得 ES 支持哪些函数，只需要执行下面的命令，即可得到完整列表：

SHOW FUNCTIONS

返回结果如下，也就是当前6.3版本支持的所有函数，如下：

      name      |     type      
----------------+---------------
AVG             |AGGREGATE      
COUNT           |AGGREGATE      
MAX             |AGGREGATE      
MIN             |AGGREGATE      
SUM             |AGGREGATE      
STDDEV_POP      |AGGREGATE      
VAR_POP         |AGGREGATE      
PERCENTILE      |AGGREGATE      
PERCENTILE_RANK |AGGREGATE      
SUM_OF_SQUARES  |AGGREGATE      
SKEWNESS        |AGGREGATE      
KURTOSIS        |AGGREGATE      
DAY_OF_MONTH    |SCALAR         
DAY             |SCALAR         
DOM             |SCALAR         
DAY_OF_WEEK     |SCALAR         
DOW             |SCALAR         
DAY_OF_YEAR     |SCALAR         
DOY             |SCALAR         
HOUR_OF_DAY     |SCALAR         
HOUR            |SCALAR         
MINUTE_OF_DAY   |SCALAR         
MINUTE_OF_HOUR  |SCALAR         
MINUTE          |SCALAR         
SECOND_OF_MINUTE|SCALAR         
SECOND          |SCALAR         
MONTH_OF_YEAR   |SCALAR         
MONTH           |SCALAR         
YEAR            |SCALAR         
WEEK_OF_YEAR    |SCALAR         
WEEK            |SCALAR         
ABS             |SCALAR         
ACOS            |SCALAR         
ASIN            |SCALAR         
ATAN            |SCALAR         
ATAN2           |SCALAR         
CBRT            |SCALAR         
CEIL            |SCALAR         
CEILING         |SCALAR         
COS             |SCALAR         
COSH            |SCALAR         
COT             |SCALAR         
DEGREES         |SCALAR         
E               |SCALAR         
EXP             |SCALAR         
EXPM1           |SCALAR         
FLOOR           |SCALAR         
LOG             |SCALAR         
LOG10           |SCALAR         
MOD             |SCALAR         
PI              |SCALAR         
POWER           |SCALAR         
RADIANS         |SCALAR         
RANDOM          |SCALAR         
RAND            |SCALAR         
ROUND           |SCALAR         
SIGN            |SCALAR         
SIGNUM          |SCALAR         
SIN             |SCALAR         
SINH            |SCALAR         
SQRT            |SCALAR         
TAN             |SCALAR         
SCORE           |SCORE          

同样支持通配符进行过滤：

POST /_xpack/sql?format=txt
{
    "query": "SHOW FUNCTIONS 'S__'"
}

结果：

     name      |     type      
---------------+---------------
SUM            |AGGREGATE      
SIN            |SCALAR         

那如果要进行模糊搜索呢，Elasticsearch 的搜索能力大家都知道，强！在 SQL 里面，可以用 match 关键字来写，如下：

POST /_xpack/sql?format=txt
{
    "query": "SELECT SCORE(), * FROM twitter WHERE match(twitter, 'sql is') ORDER BY id DESC"
}

最后，还能试试 SELECT 里面的一些其他操作，如过滤，别名，如下：

POST /_xpack/sql?format=txt
{
    "query": "SELECT SCORE() as score,name as myname FROM twitter as mytable where name = 'medcl' OR name ='elastic' limit 5"
}

结果如下：

     score     |    myname     
---------------+---------------
0.2876821      |medcl          

或是分组和函数计算：

POST /_xpack/sql?format=txt
{
    "query": "SELECT name,max(id) as max_id FROM twitter as mytable group by name limit 5"
}

结果如下：

     name      |    max_id     
---------------+---------------
medcl          |123.0          

SQL-CLI下的使用

上面的例子基本上把 SQL 的基本命令都介绍了一遍，很多情况下，用 RESTful 可能不是很方便，那么可以试试用 CLI 命令行工具来执行 SQL 语句，妥妥的 SQL 操作体验。

切换到命令行下，启动 cli 程序即可进入命令行交互提示界面，如下：

➜  elasticsearch-6.3.0 ./bin/elasticsearch-sql-cli

     .sssssss.`                     .sssssss.
  .:sXXXXXXXXXXo`                `ohXXXXXXXXXho.
 .yXXXXXXXXXXXXXXo`            `oXXXXXXXXXXXXXXX-
.XXXXXXXXXXXXXXXXXXo`        `oXXXXXXXXXXXXXXXXXX.
.XXXXXXXXXXXXXXXXXXXXo.    .oXXXXXXXXXXXXXXXXXXXXh
.XXXXXXXXXXXXXXXXXXXXXXo``oXXXXXXXXXXXXXXXXXXXXXXy
`yXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX.
 `oXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXo`
   `oXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXo`
     `oXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXo`
       `oXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXo`
         `oXXXXXXXXXXXXXXXXXXXXXXXXXXXXo`
           .XXXXXXXXXXXXXXXXXXXXXXXXXo`
         .oXXXXXXXXXXXXXXXXXXXXXXXXo`
       `oXXXXXXXXXXXXXXXXXXXXXXXXo`   `odo`
     `oXXXXXXXXXXXXXXXXXXXXXXXXo`   `oXXXXXo`
   `oXXXXXXXXXXXXXXXXXXXXXXXXo`   `oXXXXXXXXXo`
 `oXXXXXXXXXXXXXXXXXXXXXXXXo`   `oXXXXXXXXXXXXXo`
`yXXXXXXXXXXXXXXXXXXXXXXXo`    oXXXXXXXXXXXXXXXXX.
.XXXXXXXXXXXXXXXXXXXXXXo`   `oXXXXXXXXXXXXXXXXXXXy
.XXXXXXXXXXXXXXXXXXXXo`     /XXXXXXXXXXXXXXXXXXXXX
.XXXXXXXXXXXXXXXXXXo`        `oXXXXXXXXXXXXXXXXXX-
 -XXXXXXXXXXXXXXXo`            `oXXXXXXXXXXXXXXXo`
  .oXXXXXXXXXXXo`                `oXXXXXXXXXXXo.
    `.sshXXyso`        SQL         `.sshXhss.`

sql> 

当你看到一个硕大的创口贴，表示 SQL 命令行已经准备就绪了，查看一下索引列表，不，数据表的列表：

各种操作妥妥的，上面已经测试过的命令就不在这里重复了，只是体验不一样罢了。

如果要连接远程的 ES 服务器，只需要启动命令行工具的时候，指定服务器地址，如果有加密，指定 keystone 文件，完整的帮助如下:

➜  elasticsearch-6.3.0 ./bin/elasticsearch-sql-cli --help
Elasticsearch SQL CLI

Non-option arguments:
uri                  

Option                   Description                                           
------                   -----------                                           
-c, --check <Boolean>    Enable initial connection check on startup (default:  
                           true)                                               
-d, --debug              Enable debug logging                                  
-h, --help               show help                                             
-k, --keystore_location  Location of a keystore to use when setting up SSL. If 
                           specified then the CLI will prompt for a keystore   
                           password. If specified when the uri isn't https then
                           an error is thrown.                                 
-s, --silent             show minimal output                                   
-v, --verbose            show verbose output  

JDBC 对接

JDBC 对接的能力，让我们可以与各个 SQL 生态系统打通，利用众多现成的基于 SQL 之上的工具来使用 Elasticsearch，我们以一个工具来举例。

和其他数据库一样，要使用 JDBC，要下载该数据库的 JDBC 的驱动，我们打开： https://www.elastic.co/downloads/jdbc-client

只有一个 zip 包下载链接，下载即可。

然后，我们这里使用 DbVisualizer 来连接 ES 进行操作，这是一个数据库的操作和分析工具，DbVisualizer 下载地址是：https://www.dbvis.com/。

下载安装启动之后的程序主界面如下图：

我们如果要使用 ES 作为数据源，我们第一件事需要把 ES 的 JDBC 驱动添加到 DbVisualizer 的已知驱动里面。我们打开 DbVisualizer 的菜单【Tools】-> 【Driver Manager】，打开如下设置窗口：

点击绿色的加号按钮，新增一个名为 Elasticsearch-SQL 的驱动，url format 设置成 jdbc:es:，如下图：

然后点击上图黄色的文件夹按钮，添加我们刚刚下载好且解压之后的所有 jar 文件，如下：

添加完成之后，如下图：

就可以关闭这个 JDBC 驱动的管理窗口了。下面我们来连接到 ES 数据库。

选择主程序左侧的新建连接图标，打开向导，如下:

选择刚刚加入的 Elasticsearch-SQL 驱动:

设置连接字符串，此处没有登录信息，如果有可以对应的填上：

点击 Connect，即可连接到 ES，左侧导航可以展开看到对应的 ES 索引信息：

同样可以查看相应的库表结果和具体的数据：

用他自带的工具执行 SQL 也是不在话下：

同理，各种 ETL 工具和基于 SQL 的 BI 和可视化分析工具都能把 Elasticsearch 当做 SQL 数据库来连接获取数据了。

最后一个小贴士，如果你的索引名称包含横线，如 logstash-201811，只需要做一个用双引号包含，对双引号进行转义即可，如下：

POST /_xpack/sql?format=txt
{
"query":"SELECT COUNT(*) FROM \"logstash-*\""
}

关于 SQL 操作的文档在这里：

https://www.elastic.co/guide/en/elasticsearch/reference/current/sql-jdbc.html

Enjoy!

1. 基于hanlp的ES分词插件(可以作为IK的补充）。 http://t.cn/RrAUlTZ

2. Berlin Buzzword 2018中ES开发人员的演讲(需翻墙）。 http://t.cn/RrAcrKY

3. 一周热点：一个正则表达式引发的惨案。 http://t.cn/RrAVq9K

活动预告

1. 6月30日南京meetup参会报名中 https://elasticsearch.cn/m/article/647

2. 7月21日上海meetup演讲申请中 https://elasticsearch.cn/m/article/655

• 编辑：bsll

• 归档：https://elasticsearch.cn/article/680

• 订阅：https://tinyletter.com/elastic-daily

sql> show tables;

      name      |     type

----------------+---------------

hello           |BASE TABLE



sql> select * from hello;

Server error [Server encountered an error [Cannot extract value [deliveraddress.address] from source]. [SqlIllegalArgumentException[Cannot extract value [deliveraddress.address] from source]

	at org.elasticsearch.xpack.sql.execution.search.extractor.FieldHitExtractor.extractFromSource(FieldHitExtractor.java:139)

	at org.elasticsearch.xpack.sql.execution.search.extractor.FieldHitExtractor.extract(FieldHitExtractor.java:95)

	at org.elasticsearch.xpack.sql.execution.search.SearchHitRowSet.getColumn(SearchHitRowSet.java:114)

	at org.elasticsearch.xpack.sql.session.AbstractRowSet.column(AbstractRowSet.java:18)

{

  "test2": {

    "properties": {

      "deliveraddress": {

        "properties": {

          "phone_no": {

            "type": "text",

            "fields": {

              "keyword": {

                "ignore_above": 256,

                "type": "keyword"

              }

            }

          },

          "default": {

            "type": "boolean"

          },

          "address": {

            "type": "text",

            "fields": {

              "keyword": {

                "ignore_above": 256,

                "type": "keyword"

              }

            }

          },

          "province": {

            "type": "text",

            "fields": {

              "keyword": {

                "ignore_above": 256,

                "type": "keyword"

              }

            }

          },

          "city": {

            "type": "text",

            "fields": {

              "keyword": {

                "ignore_above": 256,

                "type": "keyword"

              }

            }

          },

          "mapping_id": {

            "type": "text",

            "fields": {

              "keyword": {

                "ignore_above": 256,

                "type": "keyword"

              }

            }

          },

          "name": {

            "type": "text",

            "fields": {

              "keyword": {

                "ignore_above": 256,

                "type": "keyword"

              }

            }

          },

          "full_address": {

            "type": "text",

            "fields": {

              "keyword": {

                "ignore_above": 256,

                "type": "keyword"

              }

            }

          },

          "zip_code": {

            "type": "text",

            "fields": {

              "keyword": {

                "ignore_above": 256,

                "type": "keyword"

              }

            }

          }

        }

      },

      "alipaywealth": {

        "properties": {

          "balance": {

            "type": "long"

          },

          "total_quotient": {

            "type": "long"

          },

          "huabei_creditamount": {

            "type": "long"

          },

          "mapping_id": {

            "type": "text",

            "fields": {

              "keyword": {

                "ignore_above": 256,

                "type": "keyword"

              }

            }

          },

          "huabei_totalcreditamount": {

            "type": "long"

          },

          "total_profit": {

            "type": "long"

          }

        }

      },

      "id": {

        "type": "text",

        "fields": {

          "keyword": {

            "ignore_above": 256,

            "type": "keyword"

          }

        }

      }

    }

  }

}

{

  "_id": "5b1cbc7935eb6e0007a154bb",

  "deliveraddress": [

    {

      "phone_no": "13*******98",

      "default": true,

      "address": "江苏省无asdads市徐***镇",

      "province": "江苏",

      "city": "无锡",

      "mapping_id": "3561511087asdasd341",

      "name": "b***",

      "full_address": "湖asd***上7号",

      "zip_code": "214400"

    },

    {

      "phone_no": "15*******70",

      "default": false,

      "address": "江苏省苏州asdasdasd张家港经济技术开发区",

      "province": "江苏",

      "city": "苏州",

      "mapping_id": "3561511asdasd505341",

      "name": "a**",

      "full_address": "新asd路***德***",

      "zip_code": "215600"

    }

  ],

  "alipaywealth": {

    "balance": 0,

    "total_quotient": 0,

    "huabei_creditamount": 500,

    "mapping_id": "3561511asdsa63505341",

    "huabei_totalcreditamount": 500,

    "total_profit": 0

  }

}

    @SuppressWarnings("unchecked")

    Object extractFromSource(Map<String, Object> map) {

        Object value = map;

        boolean first = true;

        // each node is a key inside the map

        for (String node : path) {

            if (value == null) {

                return null;

            } else if (first || value instanceof Map) {

                first = false;

                value = ((Map<String, Object>) value).get(node);

            } else {

                throw new SqlIllegalArgumentException("Cannot extract value [{}] from source", fieldName);

            }

        }

        return unwrapMultiValue(value);

    }