sql

sql

Sql on Elasticsearch

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

esql
Git地址
https://github.com/unimassystem/esql5 





 $(document).ready(function() {$('pre code').each(function(i, block) { hljs.highlightBlock( block); }); });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 查看全部
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 发表了文章 • 0 个评论 • 500 次浏览 • 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-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 个回复 • 1795 次浏览 • 2016-12-02 22:02 • 来自相关话题

Sql 语法转换es node版本

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

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

回复

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

使用 SQL 查询 Elasticsearch

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

我新写了一个用 SQL 查询 Elasticsearch 的工具 https://github.com/taowen/es-monitor,欢迎大家使用。详细的文档参见:https://segmentfault.com/a/1190000003502849
 
在此之前,有这么三个SQL查询Elasticsearch的工具:
Crate.iohttp://sqltoelasticsearch.fr/https://github.com/NLPchina/elasticsearch-sql
 
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希望我的小工具可以帮到你
  查看全部
我新写了一个用 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 个回复 • 1409 次浏览 • 2015-12-10 09:44 • 来自相关话题

有木有人用elasticsearch-sql?

回复

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

Sql 语法转换es node版本

回复

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

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

回复

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

Elasticsearch 整合 SQL 嵌套group by

回复

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

Sql on Elasticsearch

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

esql
Git地址
https://github.com/unimassystem/esql5 





 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 查看全部
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 发表了文章 • 0 个评论 • 500 次浏览 • 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-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 发表了文章 • 3 个评论 • 3291 次浏览 • 2016-02-21 16:19 • 来自相关话题

我新写了一个用 SQL 查询 Elasticsearch 的工具 https://github.com/taowen/es-monitor,欢迎大家使用。详细的文档参见:https://segmentfault.com/a/1190000003502849
 
在此之前,有这么三个SQL查询Elasticsearch的工具:
Crate.iohttp://sqltoelasticsearch.fr/https://github.com/NLPchina/elasticsearch-sql
 
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希望我的小工具可以帮到你
  查看全部
我新写了一个用 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
希望我的小工具可以帮到你