Sql on Elasticsearch

作者: hill   发布时间:2017-04-28
sql
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

9 个评论

不是在V站发过了?
没有吧,刚上git没有多久,哈哈。
欢迎评鉴。
https://github.com/unimassystem/esql5
赞一个,支持分享
这玩意的实现是先将 sql 转为 dsl 再请求 rest api 吗?
当前版本是的
能不能写个像splunk SPL这样的语法?
hill

hill 回复 chym

理论上可以
在windows上安装,到倒数第二步的时候报错了,哪位大侠帮忙看看什么原因,谢谢。

D:\Software\esql5-master>python -m App.app
Traceback (most recent call last):
File "C:\Python27\lib\runpy.py", line 162, in _run_module_as_main
"__main__", fname, loader, pkg_name)
File "C:\Python27\lib\runpy.py", line 72, in _run_code
exec code in run_globals
File "D:\Software\esql5-master\App\app.py", line 10, in <module>
from App.esql import Esql
File "App\esql.py", line 11, in <module>
from ql.parse import lexer
File "ql\__init__.py", line 5, in <module>
from ql.dsl.Query import Query
File "ql\dsl\__init__.py", line 7
def parse_tok_table_name(tree : Node):
^
SyntaxError: invalid syntax
当sql过长的时候,会报错,这个怎么解决

要回复文章请先登录注册