sql

sql

玩转 Elasticsearch 的 SQL 功能

Elasticsearchmedcl 发表了文章 • 9 个评论 • 2337 次浏览 • 2018-06-27 21:54 • 来自相关话题

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

首先看看接口的支持情况

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

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

命令 说明
DESC table 用来描述索引的字段属性
SHOW COLUMNS 功能同上,只是别名
SHOW FUNCTIONS 列出支持的函数列表,支持通配符过滤
SHOW TABLES 返回索引列表
SELECT .. FROM table_name WHERE .. GROUP BY .. HAVING .. ORDER BY .. LIMIT .. 用来执行查询的命令

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

首先,我们创建一条数据:

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 命令行已经准备就绪了,查看一下索引列表,不,数据表的列表:

15301043943573.jpg

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

如果要连接远程的 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

15301048139518.jpg

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

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

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

15301049453527.jpg

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

15301054144234.jpg

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

15301054340439.jpg

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

15301055143574.jpg

添加完成之后,如下图:

15301055446598.jpg

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

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

15301057385898.jpg

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

15301057824336.jpg

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

15301064989466.jpg

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

15301065711818.jpg

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

15301066251658.jpg

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

15301068015599.jpg

同理,各种 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!

有老铁测试了es6.3.0的sql功能吗?

Elasticsearchfeloxx 发表了文章 • 15 个评论 • 678 次浏览 • 2018-06-19 16:26 • 来自相关话题

我单机装了个6.3.0 执行的时候老出现如下错误,有老铁遇到过吗?   我是这么来启动的 ./elasticsearch-sql-cli http://127.0.0.1:9200
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)
      这是测试数据的mapping
{
  "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
  }
}
    ---   初步怀疑是不是不支持嵌套,数组啥的呀   然后我就翻了翻源码,发现了这个   我的错误就是在最后一个else里出现的   仔细一看,发现这个地方循环只要走了两次,或者前面的条件不成立就肯定会抛这个异常,这怎么看上去像是有点问题呢  
    @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);
    }

elastic 5.x 有支持的 sql 查询的jar包吗

Elasticsearchstrglee 回复了问题 • 2 人关注 • 1 个回复 • 296 次浏览 • 2018-04-12 11:31 • 来自相关话题

Elasticsearch sql 怎么实现高亮

Elasticsearchxinfanwang 回复了问题 • 2 人关注 • 1 个回复 • 1012 次浏览 • 2017-08-18 15:57 • 来自相关话题

es sql话查询插件

回复

Elasticsearchlinyongzhi 回复了问题 • 1 人关注 • 1 个回复 • 2422 次浏览 • 2017-08-10 14:57 • 来自相关话题

Sql on Elasticsearch

Elasticsearchhill 发表了文章 • 9 个评论 • 4497 次浏览 • 2017-04-28 11:25 • 来自相关话题

esql Git地址 https://github.com/unimassystem/esql5 
elsh.png
 
create table my_index.my_table (
     id keyword,
     name text,
     age long,
     birthday date
);

select * from my_index.my_type;

select count(*) from my_index.my_table group by age;
#Create table

字段参数,ES中分词规则、索引类型、字段格式等高级参数的支持

create table my_table (
	name text (analyzer = ik_max_word),
	dd text (index=no),
	age long (include_in_all=false)
);


对象、嵌套字段支持 as

create table my_index (
	id long,
	name text,
     obj object as (
         first_name text,
         second_name text (analyzer=pinyin)
     )
);


create table my_index (
	id long,
	name text,
   obj nested as (
         first_name text,
         second_name text (analyzer=pinyin)
   )
);


ES索引高级参数支持 with option

create table my_index (
	id long,
	name text
) with option (
	index.number_of_shards=10,
   index.number_of_replicas = 1
);
#Insert/Bulk

单条数据插入
insert into my_index.index (name,age) values ('zhangsan',24);

多条插入
bulk into my_index.index (name,age) values ('zhangsan',24),('lisi',24);


对象数据插入,list,{}Map

insert into my_index.index (ds) values (['zhejiang','hangzhou']);
		
insert into my_index.index (dd) values ({address='zhejiang',postCode='330010'});
#select/Aggregations

select * from my_table.my_index where name like 'john *' and age between 20 and 30 and (hotel = 'hanting' or flight = 'MH4510');

地理位置中心点查询
select * from hz_point where geo_distance({distance='1km',location='30.306378,120.247427'});

地理坐标区域查询
select * from hz_point where geo_bounding_box({location={top_left='31.306378,119.247427',bottom_right='29.285797,122.172329'}});

pipeline统计 move_avg
select count(*) as total, moving_avg({buckets_path=total}) from my_index group by date_histogram({field=timestamp,interval='1h'});
Getting Started

环境要求python >= 2.7

export PYTHONHOME=(%python_path)
export PATH=$PYTHONHOME/bin:$PATH


安装第三方依赖包
pip install -r esql5.egg-info/requires.txt
或python setup.py install

运行esql5服务 
(standalone):
cd esql5
python -m App.app

(with uwsgi)
cd esql5
uwsgi --ini conf/uwsgi.ini


shell终端:
python -m elsh.Command

elasticsearch-query-tookit一款基于SQL查询elasticsearch编程工具包,支持SQL解析生成DSL,支持JDBC驱动,支持和Spring、MyBatis集成

Elasticsearchchennanlcy 发表了文章 • 1 个评论 • 3643 次浏览 • 2017-03-24 23:09 • 来自相关话题

`elasticsearch-query-tookit`是一款基于SQL查询elasticsearch编程工具包,支持SQL解析生成DSL,支持JDBC驱动,支持和Spring、MyBatis集成,提供Java编程接口可基于此工具包二次开发   只是重新造了个轮子,有兴趣的同学可以相互交流,QQ: 465360798   项目地址:https://github.com/gitchennan/ ... olkit   一、SQL解析生成DSL使用示例 SQL语法帮助手册戳这里: https://github.com/gitchennan/ ... p-doc  
String sql = "select * from index.order where status='SUCCESS' and price > 100 order by nvl(pride, 0) asc routing by 'JD' limit 0, 20";

ElasticSql2DslParser sql2DslParser = new ElasticSql2DslParser();
//解析SQL
ElasticSqlParseResult parseResult = sql2DslParser.parse(sql);
//生成DSL(可用于rest api调用)
String dsl = parseResult.toDsl();

//toRequest方法接收一个clinet对象参数
SearchRequestBuilder searchReq = parseResult.toRequest(esClient);
//执行查询
SearchResponse response = searchReq.execute().actionGet();
生成的DSL如下:
{
  "from" : 0,
  "size" : 20,
  "query" : {
    "bool" : {
      "filter" : {
        "bool" : {
          "must" : [ {
            "term" : {
              "status" : "SUCCESS"
            }
          }, {
            "range" : {
              "price" : {
                "from" : 100,
                "to" : null,
                "include_lower" : false,
                "include_upper" : true
              }
            }
          } ]
        }
      }
    }
  },
  "sort" : [ {
    "pride" : {
      "order" : "asc",
      "missing" : 0
    }
  } ]
}
二、集成MyBatis、Spring   首先在Spring配置文件中增加如下代码 1. 指定driverClassName:org.elasticsearch.jdbc.api.ElasticDriver 2. 指定连接ES的连接串:jdbc:elastic:192.168.0.109:9300/product_cluster 3. 创建一个SqlMapClient对象,并指定sqlMapConfig.xml路径  
<bean id="elasticDataSource" class="org.elasticsearch.jdbc.api.ElasticSingleConnectionDataSource" destroy-method="destroy">
    <property name="driverClassName" value="org.elasticsearch.jdbc.api.ElasticDriver" />
    <property name="url" value="jdbc:elastic:192.168.0.109:9300/product_cluster" />
</bean>

<bean id="sqlMapClient" class="org.springframework.orm.ibatis.SqlMapClientFactoryBean">
    <property name="dataSource" ref="elasticDataSource" />
    <property name="configLocation" value="classpath:sqlMapConfig.xml"/>
</bean>
sqlMapConfig.xml文件内容如下:
<sqlMapConfig>
    <settings
            cacheModelsEnabled="true"
            lazyLoadingEnabled="true"
            enhancementEnabled="true"
            maxSessions="64"
            maxTransactions="20"
            maxRequests="128"
            useStatementNamespaces="true"/>

    <sqlMap resource="sqlmap/PRODUCT.xml"/>

</sqlMapConfig>
PRODUCT.xml文件中声明select sql语句
<sqlMap namespace="PRODUCT">
    <select id="getProductByCodeAndMatchWord" parameterClass="java.util.Map" resultClass="java.lang.String">
        SELECT *
        FROM index.product
        QUERY match(productName, #matchWord#) or prefix(productName, #prefixWord#, 'boost:2.0f')
        WHERE productCode = #productCode#
        AND advicePrice > #advicePrice#
        AND $$buyers.buyerName IN ('china', 'usa')
        ROUTING BY #routingVal#
    </select>
</sqlMap>
编写对应DAO代码:
@Repository
public class ProductDao {
    @Autowired
    @Qualifier("sqlMapClient")
    private SqlMapClient sqlMapClient;


    public List<Product> getProductByCodeAndMatchWord(String matchWord, String productCode) throws SQLException {
        Map<String, Object> paramMap = Maps.newHashMap();
        paramMap.put("productCode", productCode);
        paramMap.put("advicePrice", 1000);
        paramMap.put("routingVal", "A");
        paramMap.put("matchWord", matchWord);
        paramMap.put("prefixWord", matchWord);
        String responseGson = (String) sqlMapClient.queryForObject("PRODUCT.getProductByCodeAndMatchWord", paramMap);
        
        //反序列化查询结果
        JdbcSearchResponseResolver responseResolver = new JdbcSearchResponseResolver(responseGson);
        JdbcSearchResponse<Product> searchResponse = responseResolver.resolveSearchResponse(Product.class);

        return searchResponse.getDocList();

    }
}
编写测试方法
@Test
public void testProductQuery() throws Exception {
    BeanFactory factory = new ClassPathXmlApplicationContext("application-context.xml");
    ProductDao productDao = factory.getBean(ProductDao.class);
    
    List<Product> productList = productDao.getProductByCodeAndMatchWord("iphone 6s", "IP_6S");
    for (Product product : productList) {
        System.out.println(product.getProductName());
    }
}
   

有木有人用elasticsearch-sql?

Elasticsearchansj 回复了问题 • 6 人关注 • 5 个回复 • 3903 次浏览 • 2016-12-02 22:02 • 来自相关话题

Sql 语法转换es node版本

Elasticsearchwwfalcon 回复了问题 • 5 人关注 • 3 个回复 • 2636 次浏览 • 2016-03-17 15:31 • 来自相关话题

有没有可能搞一个综合的Kafka/Elasticsearch集群

回复

Elasticsearchtaowen 发起了问题 • 1 人关注 • 0 个回复 • 3186 次浏览 • 2016-03-06 16:20 • 来自相关话题

使用 SQL 查询 Elasticsearch

Elasticsearchtaowen 发表了文章 • 4 个评论 • 11069 次浏览 • 2016-02-21 16:19 • 来自相关话题

我新写了一个用 SQL 查询 Elasticsearch 的工具 https://github.com/taowen/es-monitor,欢迎大家使用。详细的文档参见:https://segmentfault.com/a/1190000003502849   在此之前,有这么三个SQL查询Elasticsearch的工具:   Crate.io 的问题是它不是Elasticsearch,它的聚合是自己实现的版本,和Elasticsearch的Aggregation是两套东西。 http://sqltoelasticsearch.fr/ 语法支持很不晚上,同时 WHERE 和 GROUP BY 就翻译错了。 https://github.com/NLPchina/elasticsearch-sql 的问题在于其用Java来翻译SQL太笨拙了,如果要达到同样的SQL语法支持程度还要增加大量的Java代码。 如果只是支持SQL,很多Elasticsearch的功能是无法被充分释放的。比如Elasticsearch支持sub aggregation,每个sub aggregation就是OLAP里的下钻一次的概念。而且每下钻一次都可以有自己的指标计算。简单的SQL是无法表达这样的特性的。所以我扩充了一下SQL的语义,使得其更贴近Elasticsearch聚合的工作方式:  
$ cat << EOF | ./es_query.py http://127.0.0.1:9200 
    WITH SELECT MAX(market_cap) AS max_all_times FROM symbol AS all_symbols; 
    WITH SELECT MAX(market_cap) AS max_at_2000 FROM all_symbols WHERE ipo_year=2000 AS year_2000; 
    WITH SELECT MAX(market_cap) AS max_at_2001 FROM all_symbols WHERE ipo_year=2001 AS year_2001;
EOF
希望我的小工具可以帮到你  

Elasticsearch 整合 SQL 嵌套group by

ElasticsearchDengShk 回复了问题 • 2 人关注 • 2 个回复 • 3643 次浏览 • 2015-12-10 09:44 • 来自相关话题

elastic 5.x 有支持的 sql 查询的jar包吗

回复

Elasticsearchstrglee 回复了问题 • 2 人关注 • 1 个回复 • 296 次浏览 • 2018-04-12 11:31 • 来自相关话题

Elasticsearch sql 怎么实现高亮

回复

Elasticsearchxinfanwang 回复了问题 • 2 人关注 • 1 个回复 • 1012 次浏览 • 2017-08-18 15:57 • 来自相关话题

es sql话查询插件

回复

Elasticsearchlinyongzhi 回复了问题 • 1 人关注 • 1 个回复 • 2422 次浏览 • 2017-08-10 14:57 • 来自相关话题

有木有人用elasticsearch-sql?

回复

Elasticsearchansj 回复了问题 • 6 人关注 • 5 个回复 • 3903 次浏览 • 2016-12-02 22:02 • 来自相关话题

Sql 语法转换es node版本

回复

Elasticsearchwwfalcon 回复了问题 • 5 人关注 • 3 个回复 • 2636 次浏览 • 2016-03-17 15:31 • 来自相关话题

有没有可能搞一个综合的Kafka/Elasticsearch集群

回复

Elasticsearchtaowen 发起了问题 • 1 人关注 • 0 个回复 • 3186 次浏览 • 2016-03-06 16:20 • 来自相关话题

Elasticsearch 整合 SQL 嵌套group by

回复

ElasticsearchDengShk 回复了问题 • 2 人关注 • 2 个回复 • 3643 次浏览 • 2015-12-10 09:44 • 来自相关话题

玩转 Elasticsearch 的 SQL 功能

Elasticsearchmedcl 发表了文章 • 9 个评论 • 2337 次浏览 • 2018-06-27 21:54 • 来自相关话题

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

首先看看接口的支持情况

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

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

命令 说明
DESC table 用来描述索引的字段属性
SHOW COLUMNS 功能同上,只是别名
SHOW FUNCTIONS 列出支持的函数列表,支持通配符过滤
SHOW TABLES 返回索引列表
SELECT .. FROM table_name WHERE .. GROUP BY .. HAVING .. ORDER BY .. LIMIT .. 用来执行查询的命令

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

首先,我们创建一条数据:

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 命令行已经准备就绪了,查看一下索引列表,不,数据表的列表:

15301043943573.jpg

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

如果要连接远程的 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

15301048139518.jpg

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

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

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

15301049453527.jpg

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

15301054144234.jpg

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

15301054340439.jpg

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

15301055143574.jpg

添加完成之后,如下图:

15301055446598.jpg

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

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

15301057385898.jpg

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

15301057824336.jpg

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

15301064989466.jpg

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

15301065711818.jpg

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

15301066251658.jpg

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

15301068015599.jpg

同理,各种 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!

有老铁测试了es6.3.0的sql功能吗?

Elasticsearchfeloxx 发表了文章 • 15 个评论 • 678 次浏览 • 2018-06-19 16:26 • 来自相关话题

我单机装了个6.3.0 执行的时候老出现如下错误,有老铁遇到过吗?   我是这么来启动的 ./elasticsearch-sql-cli http://127.0.0.1:9200
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)
      这是测试数据的mapping
{
  "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
  }
}
    ---   初步怀疑是不是不支持嵌套,数组啥的呀   然后我就翻了翻源码,发现了这个   我的错误就是在最后一个else里出现的   仔细一看,发现这个地方循环只要走了两次,或者前面的条件不成立就肯定会抛这个异常,这怎么看上去像是有点问题呢  
    @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);
    }

Sql on Elasticsearch

Elasticsearchhill 发表了文章 • 9 个评论 • 4497 次浏览 • 2017-04-28 11:25 • 来自相关话题

esql Git地址 https://github.com/unimassystem/esql5 
elsh.png
 
create table my_index.my_table (
     id keyword,
     name text,
     age long,
     birthday date
);

select * from my_index.my_type;

select count(*) from my_index.my_table group by age;
#Create table

字段参数,ES中分词规则、索引类型、字段格式等高级参数的支持

create table my_table (
	name text (analyzer = ik_max_word),
	dd text (index=no),
	age long (include_in_all=false)
);


对象、嵌套字段支持 as

create table my_index (
	id long,
	name text,
     obj object as (
         first_name text,
         second_name text (analyzer=pinyin)
     )
);


create table my_index (
	id long,
	name text,
   obj nested as (
         first_name text,
         second_name text (analyzer=pinyin)
   )
);


ES索引高级参数支持 with option

create table my_index (
	id long,
	name text
) with option (
	index.number_of_shards=10,
   index.number_of_replicas = 1
);
#Insert/Bulk

单条数据插入
insert into my_index.index (name,age) values ('zhangsan',24);

多条插入
bulk into my_index.index (name,age) values ('zhangsan',24),('lisi',24);


对象数据插入,list,{}Map

insert into my_index.index (ds) values (['zhejiang','hangzhou']);
		
insert into my_index.index (dd) values ({address='zhejiang',postCode='330010'});
#select/Aggregations

select * from my_table.my_index where name like 'john *' and age between 20 and 30 and (hotel = 'hanting' or flight = 'MH4510');

地理位置中心点查询
select * from hz_point where geo_distance({distance='1km',location='30.306378,120.247427'});

地理坐标区域查询
select * from hz_point where geo_bounding_box({location={top_left='31.306378,119.247427',bottom_right='29.285797,122.172329'}});

pipeline统计 move_avg
select count(*) as total, moving_avg({buckets_path=total}) from my_index group by date_histogram({field=timestamp,interval='1h'});
Getting Started

环境要求python >= 2.7

export PYTHONHOME=(%python_path)
export PATH=$PYTHONHOME/bin:$PATH


安装第三方依赖包
pip install -r esql5.egg-info/requires.txt
或python setup.py install

运行esql5服务 
(standalone):
cd esql5
python -m App.app

(with uwsgi)
cd esql5
uwsgi --ini conf/uwsgi.ini


shell终端:
python -m elsh.Command

elasticsearch-query-tookit一款基于SQL查询elasticsearch编程工具包,支持SQL解析生成DSL,支持JDBC驱动,支持和Spring、MyBatis集成

Elasticsearchchennanlcy 发表了文章 • 1 个评论 • 3643 次浏览 • 2017-03-24 23:09 • 来自相关话题

`elasticsearch-query-tookit`是一款基于SQL查询elasticsearch编程工具包,支持SQL解析生成DSL,支持JDBC驱动,支持和Spring、MyBatis集成,提供Java编程接口可基于此工具包二次开发   只是重新造了个轮子,有兴趣的同学可以相互交流,QQ: 465360798   项目地址:https://github.com/gitchennan/ ... olkit   一、SQL解析生成DSL使用示例 SQL语法帮助手册戳这里: https://github.com/gitchennan/ ... p-doc  
String sql = "select * from index.order where status='SUCCESS' and price > 100 order by nvl(pride, 0) asc routing by 'JD' limit 0, 20";

ElasticSql2DslParser sql2DslParser = new ElasticSql2DslParser();
//解析SQL
ElasticSqlParseResult parseResult = sql2DslParser.parse(sql);
//生成DSL(可用于rest api调用)
String dsl = parseResult.toDsl();

//toRequest方法接收一个clinet对象参数
SearchRequestBuilder searchReq = parseResult.toRequest(esClient);
//执行查询
SearchResponse response = searchReq.execute().actionGet();
生成的DSL如下:
{
  "from" : 0,
  "size" : 20,
  "query" : {
    "bool" : {
      "filter" : {
        "bool" : {
          "must" : [ {
            "term" : {
              "status" : "SUCCESS"
            }
          }, {
            "range" : {
              "price" : {
                "from" : 100,
                "to" : null,
                "include_lower" : false,
                "include_upper" : true
              }
            }
          } ]
        }
      }
    }
  },
  "sort" : [ {
    "pride" : {
      "order" : "asc",
      "missing" : 0
    }
  } ]
}
二、集成MyBatis、Spring   首先在Spring配置文件中增加如下代码 1. 指定driverClassName:org.elasticsearch.jdbc.api.ElasticDriver 2. 指定连接ES的连接串:jdbc:elastic:192.168.0.109:9300/product_cluster 3. 创建一个SqlMapClient对象,并指定sqlMapConfig.xml路径  
<bean id="elasticDataSource" class="org.elasticsearch.jdbc.api.ElasticSingleConnectionDataSource" destroy-method="destroy">
    <property name="driverClassName" value="org.elasticsearch.jdbc.api.ElasticDriver" />
    <property name="url" value="jdbc:elastic:192.168.0.109:9300/product_cluster" />
</bean>

<bean id="sqlMapClient" class="org.springframework.orm.ibatis.SqlMapClientFactoryBean">
    <property name="dataSource" ref="elasticDataSource" />
    <property name="configLocation" value="classpath:sqlMapConfig.xml"/>
</bean>
sqlMapConfig.xml文件内容如下:
<sqlMapConfig>
    <settings
            cacheModelsEnabled="true"
            lazyLoadingEnabled="true"
            enhancementEnabled="true"
            maxSessions="64"
            maxTransactions="20"
            maxRequests="128"
            useStatementNamespaces="true"/>

    <sqlMap resource="sqlmap/PRODUCT.xml"/>

</sqlMapConfig>
PRODUCT.xml文件中声明select sql语句
<sqlMap namespace="PRODUCT">
    <select id="getProductByCodeAndMatchWord" parameterClass="java.util.Map" resultClass="java.lang.String">
        SELECT *
        FROM index.product
        QUERY match(productName, #matchWord#) or prefix(productName, #prefixWord#, 'boost:2.0f')
        WHERE productCode = #productCode#
        AND advicePrice > #advicePrice#
        AND $$buyers.buyerName IN ('china', 'usa')
        ROUTING BY #routingVal#
    </select>
</sqlMap>
编写对应DAO代码:
@Repository
public class ProductDao {
    @Autowired
    @Qualifier("sqlMapClient")
    private SqlMapClient sqlMapClient;


    public List<Product> getProductByCodeAndMatchWord(String matchWord, String productCode) throws SQLException {
        Map<String, Object> paramMap = Maps.newHashMap();
        paramMap.put("productCode", productCode);
        paramMap.put("advicePrice", 1000);
        paramMap.put("routingVal", "A");
        paramMap.put("matchWord", matchWord);
        paramMap.put("prefixWord", matchWord);
        String responseGson = (String) sqlMapClient.queryForObject("PRODUCT.getProductByCodeAndMatchWord", paramMap);
        
        //反序列化查询结果
        JdbcSearchResponseResolver responseResolver = new JdbcSearchResponseResolver(responseGson);
        JdbcSearchResponse<Product> searchResponse = responseResolver.resolveSearchResponse(Product.class);

        return searchResponse.getDocList();

    }
}
编写测试方法
@Test
public void testProductQuery() throws Exception {
    BeanFactory factory = new ClassPathXmlApplicationContext("application-context.xml");
    ProductDao productDao = factory.getBean(ProductDao.class);
    
    List<Product> productList = productDao.getProductByCodeAndMatchWord("iphone 6s", "IP_6S");
    for (Product product : productList) {
        System.out.println(product.getProductName());
    }
}
   

使用 SQL 查询 Elasticsearch

Elasticsearchtaowen 发表了文章 • 4 个评论 • 11069 次浏览 • 2016-02-21 16:19 • 来自相关话题

我新写了一个用 SQL 查询 Elasticsearch 的工具 https://github.com/taowen/es-monitor,欢迎大家使用。详细的文档参见:https://segmentfault.com/a/1190000003502849   在此之前,有这么三个SQL查询Elasticsearch的工具:   Crate.io 的问题是它不是Elasticsearch,它的聚合是自己实现的版本,和Elasticsearch的Aggregation是两套东西。 http://sqltoelasticsearch.fr/ 语法支持很不晚上,同时 WHERE 和 GROUP BY 就翻译错了。 https://github.com/NLPchina/elasticsearch-sql 的问题在于其用Java来翻译SQL太笨拙了,如果要达到同样的SQL语法支持程度还要增加大量的Java代码。 如果只是支持SQL,很多Elasticsearch的功能是无法被充分释放的。比如Elasticsearch支持sub aggregation,每个sub aggregation就是OLAP里的下钻一次的概念。而且每下钻一次都可以有自己的指标计算。简单的SQL是无法表达这样的特性的。所以我扩充了一下SQL的语义,使得其更贴近Elasticsearch聚合的工作方式:  
$ cat << EOF | ./es_query.py http://127.0.0.1:9200 
    WITH SELECT MAX(market_cap) AS max_all_times FROM symbol AS all_symbols; 
    WITH SELECT MAX(market_cap) AS max_at_2000 FROM all_symbols WHERE ipo_year=2000 AS year_2000; 
    WITH SELECT MAX(market_cap) AS max_at_2001 FROM all_symbols WHERE ipo_year=2001 AS year_2001;
EOF
希望我的小工具可以帮到你