[ANN] elseql - you know, for query

Elastic Stack Elasticsearch
1

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 :slight_smile:
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

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

--

2

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

Kindly guide me on how to install it.

Thanks
Vineeth

On Wed, Sep 12, 2012 at 6:32 AM, Raffaele Sena 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 :slight_smile:
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

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

--

--

3

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 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

Kindly guide me on how to install it.

Thanks
Vineeth

On Wed, Sep 12, 2012 at 6:32 AM, Raffaele Sena 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 :slight_smile:
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

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

--

--

--

4

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 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 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

Kindly guide me on how to install it.

Thanks
Vineeth

On Wed, Sep 12, 2012 at 6:32 AM, Raffaele Sena 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 :slight_smile:
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

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

--

--

--

5

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 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 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 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

Kindly guide me on how to install it.

Thanks
Vineeth

On Wed, Sep 12, 2012 at 6:32 AM, Raffaele Sena 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 :slight_smile:
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

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

--

--

--

--

6

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

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 :wink:

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 :slight_smile:
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

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

--