SQL queries and node js

[blueren (bluren)]

Hello.

I just read the blog about support for SQL queries against ES database. I was wondering how I can get it to work with nodejs. Currently, the elasticsearch npm module caters to regular ES queries. Is it possible to run SQL statements via nodejs and retrieve results? If so, how? Any pointers about this will be missed helpful!

Thanks.

[Igor_Motov]

There is no SQL-specific commands in elasitcsearch-js client at the moment, but sql is exposed via normal REST protocol, so you can use the generic request to send requests and get data back.

[blueren (bluren)]

Thanks Igor. I'm still new to this. Right now, as an example, how do I convert this to a SQL based search query?

client.search({
    index: 'blog',
    type: 'posts',
    body: {
        query: {
            match: {
                "PostName": 'Node.js'
            }
        }
    }
}).then(function(resp) {
    console.log(resp);
}, function(err) {
    console.trace(err.message);
});

I know that the SQL raw query is going to be:
SELECT * from blog WHERE PostName = 'Node.js'

Where do I plug this in?

POST _xpack/sql?format=txt
{
  "query":"SELECT * from blog WHERE PostName = 'Node.js'"
}

Going by your reply, the below wouldn't work?

client.search({
        index: 'blog',
        type: 'posts',
        body: {
            "query":"SELECT * from blog WHERE PostName = 'Node.js'"
        }
    }).then(function(resp) {
        console.log(resp);
    }, function(err) {
        console.trace(err.message);
    });

Whilst on the topic of ES and SQL, I'd also like to know some information on the below:

1. Is there is some way to reactively "know" when an insert has happened in ES.

As an example with mongo, I can subscribe to a collection's observer and there by know when and what document got inserted into the DB. Is there any such mechanism when it comes to ES?

2. Does ES-SQL have a distinct function? I do not find any reference to using distinct in the SQL statements. Right now, how do I query unique documents?

Eg>

 POST _xpack/sql?format=txt
{
  "query":"select distinct(OriginCityName) from flights"
}

returns:

{
  "error": {
    "root_cause": [
      {
        "type": "verification_exception",
        "reason": "Found 1 problem(s)\nline 1:8: SELECT DISTINCT is not yet supported"
      }
    ],
    "type": "verification_exception",
    "reason": "Found 1 problem(s)\nline 1:8: SELECT DISTINCT is not yet supported"
  },
  "status": 400
}

Thanks.

[Igor_Motov]

I am not a node.js expert, but I would try to do something like this:

var elasticsearch = require('elasticsearch');
var client = new elasticsearch.Client({
  host: 'localhost:9200'
});

client.transport.request({
  method: "POST",
  path: "/_xpack/sql",
  body: {
    query: "SELECT 42 AS UltimateAnswer"
  }
}, function(error, response) {
  if (error) {
    console.error('something does not compute');
  } else {
    console.log("The answer is " + response.rows[0][0] + ".");
  }
});

[blueren (bluren)]

Thanks for the response @Igor_Motov. It works perfectly your way. I have raised a few more queries regarding the same here It would be of much help if I could get some response there as well.

Thanks!!