# \[ANN\] elseql - you know, for query **URL:** \ **Category:** Elasticsearch\ **Created:** [September 12, 2012, 1:02am UTC](https://discuss.elastic.co/t/ann-elseql-you-know-for-query/8999 "2012-09-12T01:02:58Z")\ **Posts on this page:** 7\ **Page:** 1
**Author:** ![Raffaele\_Sena](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/raffaele_sena/32/1759_2.png) [@Raffaele\_Sena](https://discuss.elastic.co/u/Raffaele_Sena)\ **Post date:** [September 12, 2012, 1:02am UTC](https://discuss.elastic.co/t/ann-elseql-you-know-for-query/8999/1 "2012-09-12T01:02:58Z")
I have been a big fan of ElasticSearch for quite some time and I have a couple of projects where ElasticSearch is "the database" (the data gets updated infrequently but it needs to be sliced and diced in many different ways). Lately for one of these project I am getting requests that are more "I need this report" or "I need this query" and since there is "no SQL database" I have to write code that queries ElasticSearch (I could use curl, but typing json by hand is slightly annoying and I always forget the correct syntax anyway). So, this weekend I got tired of editing one more time my "search" script to change the query and decided to write a tool, you know, for query πŸ™‚ Didn't want to invent a new query language, and wanting to enable my "client" to do his own queries, I decided to start from a SQL parser and see how far I could take it. So, here is "elseql", available on github at [https://github.com/raff/elseql](https://github.com/raff/elseql) It's written in python, so you'll need a reasonably recent version of python (2.7.x) and a couple of extra packages (it uses rawes for the ElasticSearch client). If you have the setuptools installed (easy\_install and stuff) you should be able to run "python setup.py install" and be ready to go. Otherwise check the README for other options. Right now the main effort has been to implement the "SELECT" statements, since I need it, you know, for query, but I could potentially add things like "CREATE TABLE" (put mapping) and "INSERT" (put document). But there is a "DESCRIBE" command (get mapping) and the mapping is used to enable command completion (like the mysql client). The current supported syntax is: SELECT field1,field2... FROM index WHERE field=value AND|OR field=value... ORDER BY field1 ASC, field2 DESC LIMIT start,stop plus a couple of extras: SELECT field1,field2 FACETS field1, field2 FROM... and: SELECT field1,field2,value SCRIPT value='inline script' FROM... You can check the generated ElasticSearch query and the response by passing the command line option "--debug" and point to your ElasticSearch cluster/host with --host=hostname:port (default is localhost:9200) This is pretty much it for now, but it seems to work and I am adding more features when I need them. Try it out and let me know. Feel free to email, post issues on GitHub or add pull requests. Enjoy! -- Raffaele -- ---
**Author:** ![vineeth\_mohan](https://avatars.discourse-cdn.com/v4/letter/v/bc79bd/32.png) [@vineeth\_mohan](https://discuss.elastic.co/u/vineeth_mohan)\ **Post date:** [September 12, 2012, 8:32am UTC](https://discuss.elastic.co/t/ann-elseql-you-know-for-query/8999/2 "2012-09-12T08:32:54Z")
Hi , I liked the idea of the utility and i would love to see this in action. Unfortunately am new to python and i am not able to install this application. LOG - [gist:3705241 Β· GitHub](https://gist.github.com/3705241) Kindly guide me on how to install it. Thanks Vineeth On Wed, Sep 12, 2012 at 6:32 AM, Raffaele Sena [raff367@gmail.com](mailto:raff367@gmail.com) wrote: > I have been a big fan of Elasticsearch for quite some time and I have a > couple of projects where Elasticsearch is "the database" (the data gets > updated infrequently but it needs to be sliced and diced in many different > ways). > > Lately for one of these project I am getting requests that are more "I > need this report" or "I need this query" and since there is "no SQL > database" I have to write code that queries Elasticsearch (I could use > curl, but typing json by hand is slightly annoying and I always forget the > correct syntax anyway). > > So, this weekend I got tired of editing one more time my "search" script > to change the query and decided to write a tool, you know, for query πŸ™‚ > Didn't want to invent a new query language, and wanting to enable my > "client" to do his own queries, I decided to start from a SQL parser and > see how far I could take it. > > So, here is "elseql", available on github at > [GitHub - raff/elseql: a SQL-like command line client for elasticsearch](https://github.com/raff/elseql) > > It's written in python, so you'll need a reasonably recent version of > python (2.7.x) and a couple of extra packages (it uses rawes for the > Elasticsearch client). > If you have the setuptools installed (easy\_install and stuff) you should > be able to run "python setup.py install" and be ready to go. Otherwise > check the README for other options. > > Right now the main effort has been to implement the "SELECT" statements, > since I need it, you know, for query, but I could potentially add things > like "CREATE TABLE" (put mapping) and "INSERT" (put document). > > But there is a "DESCRIBE" command (get mapping) and the mapping is used to > enable command completion (like the mysql client). > > The current supported syntax is: > > SELECT field1,field2... FROM index WHERE field=value AND|OR > field=value... ORDER BY field1 ASC, field2 DESC LIMIT start,stop > > plus a couple of extras: > > SELECT field1,field2 FACETS field1, field2 FROM... > > and: > > SELECT field1,field2,value SCRIPT value='inline script' FROM... > > You can check the generated Elasticsearch query and the response by > passing the command line option "--debug" and point to your Elasticsearch > cluster/host with --host=hostname:port (default is localhost:9200) > > This is pretty much it for now, but it seems to work and I am adding more > features when I need them. Try it out and let me know. Feel free to email, > post issues on GitHub or add pull requests. > > Enjoy! > > -- Raffaele > > -- -- ---
**Author:** ![Raffaele\_Sena](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/raffaele_sena/32/1759_2.png) [@Raffaele\_Sena](https://discuss.elastic.co/u/Raffaele_Sena)\ **Post date:** [September 12, 2012, 2:29pm UTC](https://discuss.elastic.co/t/ann-elseql-you-know-for-query/8999/3 "2012-09-12T14:29:23Z")
I see an error "compiling" (line 34) that sounds strange. I didn't try running on python 2.6. I'll see if I can test it. But after that everything was installed in the right place (the last error is actually more of a warning and I'll fix it right away) The script was installed in /usr/local/bin. > Installing elseql script to /usr/local/bin Did you try running it ? Do you get any error ? Try: /usr/local/bin/elseql or from the elseql folder: python elseql/elseql.py -- Raffaele On Wed, Sep 12, 2012 at 1:32 AM, Vineeth Mohan [vineethmohan@algotree.com](mailto:vineethmohan@algotree.com) wrote: > Hi , > > I liked the idea of the utility and i would love to see this in action. > Unfortunately am new to python and i am not able to install this > application. > > LOG - [gist:3705241 Β· GitHub](https://gist.github.com/3705241) > > Kindly guide me on how to install it. > > Thanks > Vineeth > > On Wed, Sep 12, 2012 at 6:32 AM, Raffaele Sena [raff367@gmail.com](mailto:raff367@gmail.com) wrote: > > > I have been a big fan of Elasticsearch for quite some time and I have a > > couple of projects where Elasticsearch is "the database" (the data gets > > updated infrequently but it needs to be sliced and diced in many different > > ways). > > > > Lately for one of these project I am getting requests that are more "I > > need this report" or "I need this query" and since there is "no SQL > > database" I have to write code that queries Elasticsearch (I could use curl, > > but typing json by hand is slightly annoying and I always forget the correct > > syntax anyway). > > > > So, this weekend I got tired of editing one more time my "search" script > > to change the query and decided to write a tool, you know, for query πŸ™‚ > > Didn't want to invent a new query language, and wanting to enable my > > "client" to do his own queries, I decided to start from a SQL parser and see > > how far I could take it. > > > > So, here is "elseql", available on github at > > [GitHub - raff/elseql: a SQL-like command line client for elasticsearch](https://github.com/raff/elseql) > > > > It's written in python, so you'll need a reasonably recent version of > > python (2.7.x) and a couple of extra packages (it uses rawes for the > > Elasticsearch client). > > If you have the setuptools installed (easy\_install and stuff) you should > > be able to run "python setup.py install" and be ready to go. Otherwise check > > the README for other options. > > > > Right now the main effort has been to implement the "SELECT" statements, > > since I need it, you know, for query, but I could potentially add things > > like "CREATE TABLE" (put mapping) and "INSERT" (put document). > > > > But there is a "DESCRIBE" command (get mapping) and the mapping is used to > > enable command completion (like the mysql client). > > > > The current supported syntax is: > > > > SELECT field1,field2... FROM index WHERE field=value AND|OR > > field=value... ORDER BY field1 ASC, field2 DESC LIMIT start,stop > > > > plus a couple of extras: > > > > SELECT field1,field2 FACETS field1, field2 FROM... > > > > and: > > > > SELECT field1,field2,value SCRIPT value='inline script' FROM... > > > > You can check the generated Elasticsearch query and the response by > > passing the command line option "--debug" and point to your Elasticsearch > > cluster/host with --host=hostname:port (default is localhost:9200) > > > > This is pretty much it for now, but it seems to work and I am adding more > > features when I need them. Try it out and let me know. Feel free to email, > > post issues on GitHub or add pull requests. > > > > Enjoy! > > > > -- Raffaele > > > > -- > > -- -- ---
**Author:** ![Raffaele\_Sena](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/raffaele_sena/32/1759_2.png) [@Raffaele\_Sena](https://discuss.elastic.co/u/Raffaele_Sena)\ **Post date:** [September 12, 2012, 2:45pm UTC](https://discuss.elastic.co/t/ann-elseql-you-know-for-query/8999/4 "2012-09-12T14:45:37Z")
Ok, I found the problem with Python 2.6 and fixed it. You can pull the new version and try to install again. Thanks! -- Raffaele On Wed, Sep 12, 2012 at 7:29 AM, Raffaele Sena [raff367@gmail.com](mailto:raff367@gmail.com) wrote: > I see an error "compiling" (line 34) that sounds strange. I didn't try > running on python 2.6. I'll see if I can test it. > > But after that everything was installed in the right place (the last > error is actually more of a warning and I'll fix it right away) > > The script was installed in /usr/local/bin. > > > Installing elseql script to /usr/local/bin > > Did you try running it ? Do you get any error ? > > Try: > /usr/local/bin/elseql > > or from the elseql folder: > python elseql/elseql.py > > -- Raffaele > > On Wed, Sep 12, 2012 at 1:32 AM, Vineeth Mohan > [vineethmohan@algotree.com](mailto:vineethmohan@algotree.com) wrote: > > > Hi , > > > > I liked the idea of the utility and i would love to see this in action. > > Unfortunately am new to python and i am not able to install this > > application. > > > > LOG - [gist:3705241 Β· GitHub](https://gist.github.com/3705241) > > > > Kindly guide me on how to install it. > > > > Thanks > > Vineeth > > > > On Wed, Sep 12, 2012 at 6:32 AM, Raffaele Sena [raff367@gmail.com](mailto:raff367@gmail.com) wrote: > > > > > I have been a big fan of Elasticsearch for quite some time and I have a > > > couple of projects where Elasticsearch is "the database" (the data gets > > > updated infrequently but it needs to be sliced and diced in many different > > > ways). > > > > > > Lately for one of these project I am getting requests that are more "I > > > need this report" or "I need this query" and since there is "no SQL > > > database" I have to write code that queries Elasticsearch (I could use curl, > > > but typing json by hand is slightly annoying and I always forget the correct > > > syntax anyway). > > > > > > So, this weekend I got tired of editing one more time my "search" script > > > to change the query and decided to write a tool, you know, for query πŸ™‚ > > > Didn't want to invent a new query language, and wanting to enable my > > > "client" to do his own queries, I decided to start from a SQL parser and see > > > how far I could take it. > > > > > > So, here is "elseql", available on github at > > > [GitHub - raff/elseql: a SQL-like command line client for elasticsearch](https://github.com/raff/elseql) > > > > > > It's written in python, so you'll need a reasonably recent version of > > > python (2.7.x) and a couple of extra packages (it uses rawes for the > > > Elasticsearch client). > > > If you have the setuptools installed (easy\_install and stuff) you should > > > be able to run "python setup.py install" and be ready to go. Otherwise check > > > the README for other options. > > > > > > Right now the main effort has been to implement the "SELECT" statements, > > > since I need it, you know, for query, but I could potentially add things > > > like "CREATE TABLE" (put mapping) and "INSERT" (put document). > > > > > > But there is a "DESCRIBE" command (get mapping) and the mapping is used to > > > enable command completion (like the mysql client). > > > > > > The current supported syntax is: > > > > > > SELECT field1,field2... FROM index WHERE field=value AND|OR > > > field=value... ORDER BY field1 ASC, field2 DESC LIMIT start,stop > > > > > > plus a couple of extras: > > > > > > SELECT field1,field2 FACETS field1, field2 FROM... > > > > > > and: > > > > > > SELECT field1,field2,value SCRIPT value='inline script' FROM... > > > > > > You can check the generated Elasticsearch query and the response by > > > passing the command line option "--debug" and point to your Elasticsearch > > > cluster/host with --host=hostname:port (default is localhost:9200) > > > > > > This is pretty much it for now, but it seems to work and I am adding more > > > features when I need them. Try it out and let me know. Feel free to email, > > > post issues on GitHub or add pull requests. > > > > > > Enjoy! > > > > > > -- Raffaele > > > > > > -- > > > > -- -- ---
**Author:** ![vineeth\_mohan](https://avatars.discourse-cdn.com/v4/letter/v/bc79bd/32.png) [@vineeth\_mohan](https://discuss.elastic.co/u/vineeth_mohan)\ **Post date:** [September 13, 2012, 2:50am UTC](https://discuss.elastic.co/t/ann-elseql-you-know-for-query/8999/5 "2012-09-13T02:50:24Z")
Hello Raffaele , Its working great. This was kind the tool i have been looking for a long time. Now the non technical people can also see what is going on. Thanks a ton. Thanks Vineeth On Wed, Sep 12, 2012 at 8:15 PM, Raffaele Sena [raff367@gmail.com](mailto:raff367@gmail.com) wrote: > Ok, I found the problem with Python 2.6 and fixed it. You can pull the > new version and try to install again. > > Thanks! > > -- Raffaele > > On Wed, Sep 12, 2012 at 7:29 AM, Raffaele Sena [raff367@gmail.com](mailto:raff367@gmail.com) wrote: > > > I see an error "compiling" (line 34) that sounds strange. I didn't try > > running on python 2.6. I'll see if I can test it. > > > > But after that everything was installed in the right place (the last > > error is actually more of a warning and I'll fix it right away) > > > > The script was installed in /usr/local/bin. > > > > > Installing elseql script to /usr/local/bin > > > > Did you try running it ? Do you get any error ? > > > > Try: > > /usr/local/bin/elseql > > > > or from the elseql folder: > > python elseql/elseql.py > > > > -- Raffaele > > > > On Wed, Sep 12, 2012 at 1:32 AM, Vineeth Mohan > > [vineethmohan@algotree.com](mailto:vineethmohan@algotree.com) wrote: > > > > > Hi , > > > > > > I liked the idea of the utility and i would love to see this in action. > > > Unfortunately am new to python and i am not able to install this > > > application. > > > > > > LOG - [gist:3705241 Β· GitHub](https://gist.github.com/3705241) > > > > > > Kindly guide me on how to install it. > > > > > > Thanks > > > Vineeth > > > > > > On Wed, Sep 12, 2012 at 6:32 AM, Raffaele Sena [raff367@gmail.com](mailto:raff367@gmail.com) > > > wrote: > > > > > > > I have been a big fan of Elasticsearch for quite some time and I have a > > > > couple of projects where Elasticsearch is "the database" (the data gets > > > > updated infrequently but it needs to be sliced and diced in many > > > > different > > > > ways). > > > > > > > > Lately for one of these project I am getting requests that are more "I > > > > need this report" or "I need this query" and since there is "no SQL > > > > database" I have to write code that queries Elasticsearch (I could use > > > > curl, > > > > but typing json by hand is slightly annoying and I always forget the > > > > correct > > > > syntax anyway). > > > > > > > > So, this weekend I got tired of editing one more time my "search" > > > > script > > > > to change the query and decided to write a tool, you know, for query πŸ™‚ > > > > Didn't want to invent a new query language, and wanting to enable my > > > > "client" to do his own queries, I decided to start from a SQL parser > > > > and see > > > > how far I could take it. > > > > > > > > So, here is "elseql", available on github at > > > > [GitHub - raff/elseql: a SQL-like command line client for elasticsearch](https://github.com/raff/elseql) > > > > > > > > It's written in python, so you'll need a reasonably recent version of > > > > python (2.7.x) and a couple of extra packages (it uses rawes for the > > > > Elasticsearch client). > > > > If you have the setuptools installed (easy\_install and stuff) you > > > > should > > > > be able to run "python setup.py install" and be ready to go. Otherwise > > > > check > > > > the README for other options. > > > > > > > > Right now the main effort has been to implement the "SELECT" > > > > statements, > > > > since I need it, you know, for query, but I could potentially add > > > > things > > > > like "CREATE TABLE" (put mapping) and "INSERT" (put document). > > > > > > > > But there is a "DESCRIBE" command (get mapping) and the mapping is > > > > used to > > > > enable command completion (like the mysql client). > > > > > > > > The current supported syntax is: > > > > > > > > SELECT field1,field2... FROM index WHERE field=value AND|OR > > > > field=value... ORDER BY field1 ASC, field2 DESC LIMIT start,stop > > > > > > > > plus a couple of extras: > > > > > > > > SELECT field1,field2 FACETS field1, field2 FROM... > > > > > > > > and: > > > > > > > > SELECT field1,field2,value SCRIPT value='inline script' FROM... > > > > > > > > You can check the generated Elasticsearch query and the response by > > > > passing the command line option "--debug" and point to your > > > > Elasticsearch > > > > cluster/host with --host=hostname:port (default is localhost:9200) > > > > > > > > This is pretty much it for now, but it seems to work and I am adding > > > > more > > > > features when I need them. Try it out and let me know. Feel free to > > > > email, > > > > post issues on GitHub or add pull requests. > > > > > > > > Enjoy! > > > > > > > > -- Raffaele > > > > > > > > -- > > > > > > -- > > -- -- ---
**Author:** ![jprante](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/jprante/32/44941_2.png) [@jprante](https://discuss.elastic.co/u/jprante)\ **Post date:** [September 13, 2012, 9:10pm UTC](https://discuss.elastic.co/t/ann-elseql-you-know-for-query/8999/6 "2012-09-13T21:10:08Z")
Hi Raffaele, FYI, the subset of SQL you have implemented is close to "Common Command Language" (CCL), or ISO 8777 (equivalent to the now inactive Z39.58 NISO standard) [http://www.niso.org/apps/group\_public/download.php/6567/Common%20Command%20Language%20for%20Online%20Interactive%20Information%20Retrieval.pdf](http://www.niso.org/apps/group_public/download.php/6567/Common%20Command%20Language%20for%20Online%20Interactive%20Information%20Retrieval.pdf) CCL lacks any relational queries, joins etc. It dates back to the 1970s and was invented and used by one of the first online network of databases (Euronet DIANE), long before commercial SQL DBs or the Web arrived. Funny to see those kind of a "language for interactive information retrieval" again πŸ˜‰ Best regards, JΓΆrg On Wednesday, September 12, 2012 3:02:58 AM UTC+2, Raffaele Sena wrote: > I have been a big fan of Elasticsearch for quite some time and I have a > couple of projects where Elasticsearch is "the database" (the data gets > updated infrequently but it needs to be sliced and diced in many different > ways). > > Lately for one of these project I am getting requests that are more "I > need this report" or "I need this query" and since there is "no SQL > database" I have to write code that queries Elasticsearch (I could use > curl, but typing json by hand is slightly annoying and I always forget the > correct syntax anyway). > > So, this weekend I got tired of editing one more time my "search" script > to change the query and decided to write a tool, you know, for query πŸ™‚ > Didn't want to invent a new query language, and wanting to enable my > "client" to do his own queries, I decided to start from a SQL parser and > see how far I could take it. > > So, here is "elseql", available on github at > [GitHub - raff/elseql: a SQL-like command line client for elasticsearch](https://github.com/raff/elseql) > > It's written in python, so you'll need a reasonably recent version of > python (2.7.x) and a couple of extra packages (it uses rawes for the > Elasticsearch client). > If you have the setuptools installed (easy\_install and stuff) you should > be able to run "python setup.py install" and be ready to go. Otherwise > check the README for other options. > > Right now the main effort has been to implement the "SELECT" statements, > since I need it, you know, for query, but I could potentially add things > like "CREATE TABLE" (put mapping) and "INSERT" (put document). > > But there is a "DESCRIBE" command (get mapping) and the mapping is used to > enable command completion (like the mysql client). > > The current supported syntax is: > > SELECT field1,field2... FROM index WHERE field=value AND|OR > field=value... ORDER BY field1 ASC, field2 DESC LIMIT start,stop > > plus a couple of extras: > > SELECT field1,field2 FACETS field1, field2 FROM... > > and: > > SELECT field1,field2,value SCRIPT value='inline script' FROM... > > You can check the generated Elasticsearch query and the response by > passing the command line option "--debug" and point to your Elasticsearch > cluster/host with --host=hostname:port (default is localhost:9200) > > This is pretty much it for now, but it seems to work and I am adding more > features when I need them. Try it out and let me know. Feel free to email, > post issues on GitHub or add pull requests. > > Enjoy! > > -- Raffaele -- ---
**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\ **Post date:** [July 6, 2017, 3:12am UTC](https://discuss.elastic.co/t/ann-elseql-you-know-for-query/8999/7 "2017-07-06T03:12:50Z")