Getting internal "_id" field via jdbc

[Kenjiro]

Hello,

I'm new to elasticsearch and as far as I've read, _id field is not accesible via sql endpoint at the moment.

I've read some posts about duplicating it as a regular field in order to reach via sql endpoint. But I cannot find a document about how to actually do it.

Can you please advise?

[dadoonet (David Pilato)]

Welcome!

Easiest thing is to do that from the client which is sending data to Elasticsearch:

PUT /index/_doc/1
{
  "id": "1",
  "foo": "bar"
}

If this does not work for you, there are many other options, like using runtime fields or using an ingest pipeline to set this value automatically at index time... Something like:

PUT _ingest/pipeline/set-id
{
  "processors": [
    {
      "set": {
        "field": "id",
        "value": "{{{_id}}}"
      }
    }
  ]
}

And then call:

PUT /index/_doc/1?pipeline=set-id
{
  "foo": "bar"
}

Or define the pipeline as a default one.

As you can see, many options. So please clarify your exact need and architecture so we can give even better answers

BTW I did not check initially but where dii you see that you can't access the _id field using the SQL layer?

[Kenjiro]

Hello,

@dadoonet

BTW I did not check initially but where dii you see that you can't access the _id field using the SQL layer?

This is stated here, on several posts from elastic team members. One example being --> Is there a way to access internal _id and _type fields in ES SQL query (XPack)?

@dadoonet

As you can see, many options. So please clarify your exact need and architecture so we can give even better answers

I need to extract data from elasticsearch into a relational database. Since the data contains no logical unique keys (even not a composite one), I have to rely elastic _id field to ensure data consistency. But I cannot reach the field via elasticsearch JDBC driver.

Any suggestions?

[dadoonet (David Pilato)]

@Kenjiro

This is stated here, on several posts from elastic team members. One example being --> Is there a way to access internal _id and _type fields in ES SQL query (XPack)?

It's from 2019. 5 years ago. Did you try it? I meant that the post was about filtering by id not getting back the id.

@Kenjiro

Any suggestions?

I think I proposed some options already.

[Kenjiro]

@dadoonet

It's from 2019. 5 years ago. Did you try it? I meant that the post was about filtering by id not getting back the id.

Well, of course I tried

As far as I understand, it's easy for the team to make _id field accesible via sql endpoint. But things get complicated when it comes to other sql functionality like aggregations, grouping etc. Therefore they chose not to include that functionality altogether.

Several requests have been made over the years but all have been dismissed. Some directly by denial, some indirectly via redirecting to other requests and have them closed due to inactivity.

@dadoonet

I think I proposed some options already.

I'll forward those to our elastic admin to see if we can do it.

Thank you