Getting internal "_id" field via jdbc

Elastic Stack Elasticsearch
1

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?

2

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

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

3

Hello,

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

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?

4

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.

I think I proposed some options already.

5

Well, of course I tried :slight_smile:

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.

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

Thank you :slight_smile:

6

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.