Elasticsearch-js: client6.sql.query is not work

[milu2003]

elastic version: 6.6.2

my code:

const { Client: Client7 } = require('@elastic/elasticsearch')
const client6 = new Client7({ node: 'http://localhost:9200' })


let aa = await client6.sql.query({
            body: {
                query: `SELECT * FROM "${this.elastic_name}"`,
            }
 });

Correct interface request：

POST _xpack/sql?format=json
{
  "query":"SELECT * FROM \"f271444afc2479560efc51b68a0cc996\" limit 10"
}

He works normally.

but api/sql.query.js

line 82:

path = '/' + '_sql'

The request is:

{ method: 'POST',
  path: '/_sql',
  body: { query: 'SELECT * FROM "f271444afc2479560efc51b68a0cc996"' },
  querystring: {} }

error:

{ body: 
   { error: 'Incorrect HTTP method for uri [/_sql] and method [POST], allowed: [PUT, DELETE, GET, HEAD]',
     status: 405 },

Change the code to :

path = '/' + '_xpack/sql'

Can operate normally

[delvedor]

Hello!
If you are using version 6 of the client, the sql api lives in the xpack namespace :)

Try with

client.xpack.sql({ ... })

[milu2003]

Ok

[milu2003]

@delvedor
I saw the 7.0 API before. 6.0 API I see this interface
api：https://www.elastic.co/guide/en/elasticsearch/client/javascript-api/6.x/api-reference.html#_xpack_sql_query

[delvedor]

Yes, in 7 we got rid of the xpack prefix.

[milu2003]

@delvedor
I just tested that 6.0 does not have this interface.
Although this api exists in documents, it is not available in the project.

[milu2003]

@delvedor

https://github.com/elastic/elasticsearch-js/search?q=xpack.sql&unscoped_q=xpack.sql

[delvedor]

@milu2003 I'm sorry, but I'm kind of lost.
Can you write a full example of what you are trying to achieve and add also which version of the client and elasticsearch are you using?

[milu2003]

@delvedor yes ,yes

code:

async sql_query(sql){

        const { Client: Client6 } = require('@elastic/elasticsearch')
        const client6 = new Client6({ node: 'http://localhost:9200' })

        console.log(client6.xpack);

        return await client6.xpack.sql({ body: {
            query: sql,
        }});

   }

elastic version:

{
  "name" : "R0T-_NE",
  "cluster_name" : "elasticsearch",
  "cluster_uuid" : "4m7ZjdaDShOA-G1cZ5OoIg",
  "version" : {
    "number" : "6.6.2",
    "build_flavor" : "default",
    "build_type" : "tar",
    "build_hash" : "3bd3e59",
    "build_date" : "2019-03-06T15:16:26.864148Z",
    "build_snapshot" : false,
    "lucene_version" : "7.6.0",
    "minimum_wire_compatibility_version" : "5.6.0",
    "minimum_index_compatibility_version" : "5.0.0"
  },
  "tagline" : "You Know, for Search"
}

package.json:

    "@elastic/elasticsearch": "^7.1.0",

[delvedor]

Sorry, my bad, the API that you should use is:

client.xpack.sql.query({ ... })

As is stated in the README, you should use the same major version of the client as your Elasticsearch instance. You are using Elasticsearch v6, but the client is v7.
Try run:

npm install @elastic/elasticsearch@6

[milu2003]

@delvedor

OK Actually, I went straight to the API and didn't need the library. It's simple

return new Promise(function(resolve, reject) {
            request.post(`http://${G.config.elastic.host}/_xpack/sql?format=json`, {
                json:{
                    query: sql
                }
            }, (err, res, body) => {
                resolve({
                    err: err,
                    res: body,
                })
            });
        });;

[delvedor]

Even tho you can avoid using the client since the connection happens over http, it is not recommended.
The client is doing way more things under the hood, such as retries, sniffing, error handling and it will help you migrate to a new version of Elasticsearch since it hides from you most of the complexity.

[milu2003]

@delvedor Okay, listen to you.

[mitjafelicijan]

@delvedor Totally agree with you on this point. But the option to override the path would be nice. I am using AWS Elasticsearch Service and the path that they use is _opendistro/_sql.

Maybe I haven't found this but as far as I know, this is impossible currently.

[delvedor]

@mitjafelicijan you can't change the path via an option, as this client is generated from the Elasticsearch's spec.
You can either use the transport.request or use the extend API and override the default implementation.

[mitjafelicijan]

For whoever is looking for a direct solution, here it is.

const client = new Elastic({
  node: 'http://localhost:9200'
});

let results = await client.transport.request({
  method: 'POST',
  path: '/_opendistro/_sql',
  body: { query: 'select * from some-index limit 10' }
}) ;

console.log(results);

[kskishore]

@mitjafelicijan Thanks a lot. You saved me. But I'm confused.

I'm using AWS ElasticSearch v7.9. And my client is @elastic/elasticsearch v7.9. (I tried @elastic/elasticsearch v7.10 too)

Below code did not work for me. It returned StatusCode 401 with message "Your request: '/_sql' is not allowed."

const awsCredentials = await awsGetCredentials();
const AWSConnection = createAWSConnection(awsCredentials);
const es = new Client({
        node: ES_NODE,
        ...AWSConnection,
      });
const { body } = await es.sql.query({
        body: {
          query: query,
        },
      });

I had spent around two days scratching my head, until I saw your code above. And it worked perfectly 👍

While I understand that /_opendistro is the reason here, I would assume @elastic/elasticsearch to handle it. Your thoughts on this would help me understand it better.

Thanks again,
Sai Koya

[delvedor]

The Elasticsearch clients go through rigorous testing and validation against the official Elasticsearch distribution coming from Elastic. Since the AWS Elasticsearch Service has incompatible APIs with Elasticsearch itself, either by missing APIs or has incompatible format, we do not support it. Please consider using Elastic Cloud for an official and formally supported hosted Elasticsearch distribution.