Elasticsql package converts SQL to ElasticSearch DSL

This is a creation in Article, where the information may have evolved or changed.

Elasticsql
-----------

Elasticsql package converts SQL to ElasticSearch DSL

SQL Features Support:

-[x] SQL Select
-[x] SQL Where
-[x] SQL Order bysql
-[x] SQL Group by
-[x] SQL and & OR
-[x] SQL like & don't like
-[x] SQL COUNT DISTINCT
-[x] SQL in & not in
-[x] SQL between
-[x] SQL avg (), COUNT (*), Count (field), Min (field), Max (field)

Beyond SQL Features Support:
-[x] ES Tophits
-[x] ES Date_histogram
-[x] ES STATS
-[x] ES RANGE
-[x] ES Date_range

Improvement:now the query DSL is much more flat

SQL Usage

Query

select * from test where a=1 and b="c" and create_time between '2015-01-01T00:00:00+0800' and '2016-01-01T00:00:00+0800' and process_id > 1 order by id desc limit 100,10

Aggregation

select avg(age),min(age),max(age),count(student),count(distinct student) from test group by grade,class limit 10

Beyond SQL

• Range age group 20-25,25-30,30-35,35-40

SELECT COUNT(age) FROM bank GROUP BY range(age, 20,25,30,35,40)

• Range Date GROUP by your Config

SELECT online FROM online GROUP BY date_range(field="insert_time",format="yyyy-MM-dd" ,"2014-08-18","2014-08-17","now-8d","now-7d","now-6d","now")

• Range Date GROUP BY day

select * from test group by date_histogram(field="changeTime",interval="1h",format="yyyy-MM-dd HH:mm:ss")

• Stats

SELECT online FROM online group by stats(field="grade")

• Tophits

select top_hits(field="class", hitssort="age:desc", taglimit = "10", hitslimit = "1", _source="name,age,class,gender") from school

PKG Usage
-------------

Go get Github.com/farmerx/elasticsql

Demo:

package mainimport (    "fmt"    "github.com/farmerx/elasticsql")var sql = `select * from test where a=1 and b="c" and create_time between '2015-01-01T00:00:00+0800' and '2016-01-01T00:00:00+0800' and process_id > 1 order by id desc limit 100,10`var sql2= `  select avg(age),min(age),max(age),count(student) from test group by class limit 10`var sql3= `  select * from test group by class,student limit 10`var sql4 = `  select * from test group by date_histogram(field="changeTime",interval="1h",format="yyyy-MM-dd HH:mm:ss")`func main() {    esql := elasticsql.NewElasticSQL(eslasticsql.InitOptions{})    table, dsl, err := esql.SQLConvert(sql)    fmt.Println(table, dsl, err)}

Project Address

Elasticsql GitHub Address

416 Reads