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!
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.
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:
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?
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 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.
Apache, Apache Lucene, Apache Hadoop, Hadoop, HDFS and the yellow elephant
logo are trademarks of the
Apache Software Foundation
in the United States and/or other countries.