# Can not create update index pipeline without unique identifier in database

**URL:** <https://discuss.elastic.co/t/can-not-create-update-index-pipeline-without-unique-identifier-in-database/337494>\
**Category:** Elasticsearch\
**Created:** [July 3, 2023, 8:10pm UTC](https://discuss.elastic.co/t/can-not-create-update-index-pipeline-without-unique-identifier-in-database/337494 "2023-07-03T20:10:46Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![PodarcisMuralis](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/podarcismuralis/32/122606_2.png) [@PodarcisMuralis](https://discuss.elastic.co/u/PodarcisMuralis)\
**Post date:** [July 3, 2023, 8:10pm UTC](https://discuss.elastic.co/t/can-not-create-update-index-pipeline-without-unique-identifier-in-database/337494/1 "2023-07-03T20:10:46Z")

</div>

Hi all.

I want to create an update index using jdbc input and elasticsearch output.  
I have a left joined table consists of 4 tables in database.  
But I do not have a unique identifier in db. Therefore I can not create unique id in document\_id.  
This means if a record is updated, I can not update my document in index.

I tried to generate uuid in filter section but it created new document id even the database record is updated.

I also tried to combine more than one field to create custom document\_id but it was not unique.

Is there any solution for this?  
Thanks.

---

<div class="post-metadata">

**Author:** ![leandrojmp](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/leandrojmp/32/107231_2.png) [@leandrojmp](https://discuss.elastic.co/u/leandrojmp)\
**Post date:** [July 3, 2023, 8:18pm UTC](https://discuss.elastic.co/t/can-not-create-update-index-pipeline-without-unique-identifier-in-database/337494/2 "2023-07-03T20:18:50Z")

</div>

> [@PodarcisMuralis](#):
>
> I tried to generate uuid in filter section but it created new document id even the database record is updated.

Are you using Logstash? If so this is expected, it is how the `uuid` filter works, it is generate a unique ID for every document, it is explained in the documentation.

> This is useful if you need to generate a string that’s unique for every event, even if the same input is processed multiple times. If you want to generate strings that are identical each time a event with a given content is processed (i.e. a hash) you should use the [fingerprint filter](https://www.elastic.co/guide/en/logstash/current/plugins-filters-fingerprint.html) instead.

> [@PodarcisMuralis](#):
>
> I also tried to combine more than one field to create custom document\_id but it was not unique.

How you combined that? Using the `fingerprint` filter?

> [@PodarcisMuralis](#):
>
> Is there any solution for this?

It depends entirely on your data, you need to have a field or a combination of fields that is unique to be able to have a custom unique \_id in elasticsearch.

---

<div class="post-metadata">

**Author:** ![PodarcisMuralis](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/podarcismuralis/32/122606_2.png) [@PodarcisMuralis](https://discuss.elastic.co/u/PodarcisMuralis)\
**Post date:** [July 5, 2023, 9:25am UTC](https://discuss.elastic.co/t/can-not-create-update-index-pipeline-without-unique-identifier-in-database/337494/3 "2023-07-05T09:25:27Z")

</div>

Thanks for the reply.

Since I generate uuid in filter section and it creates distinct events, if someone changes database record, uuid plugin creates new id for the updated version also. Therefore I can not update it. So uuid does not work.

I do not have a unique identifier in database, it means I can not update my documents in index.

Initially I thought creating my composite on database query by combining more than one columns. But it is also not unique and one of them changes, we do not have unique identifier anymore.

```auto
SELECT COL1|| '_' || COL2 || '_' || COL13 AS **COMPOSITE_KEY** ,
       COL4,
      COL5, etc.

output {
  elasticsearch {
    id => "a_name"
    action => "update"
    index => "my_idx"
    document_id => "%{ **COMPOSITE_KEY** }"
    doc_as_upsert => true
    hosts => ["array of hosts"]
    cacert => 'cert'
    user => "user"
    password => "password"
  }
}

```

There is not solution. So I will reindex the whole database each time by using 2 indices. Removing one and filling the other one. Does someone have any idea, how I can do that?

---

<div class="post-metadata">

**Author:** ![Christian\_Dahlqvist](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/christian_dahlqvist/32/4617_2.png) [@Christian\_Dahlqvist](https://discuss.elastic.co/u/Christian_Dahlqvist)\
**Post date:** [July 5, 2023, 9:31am UTC](https://discuss.elastic.co/t/can-not-create-update-index-pipeline-without-unique-identifier-in-database/337494/4 "2023-07-05T09:31:05Z")

</div>

> [@PodarcisMuralis](#):
>
> Since I generate uuid in filter section and it creates distinct events, if someone changes database record, uuid plugin creates new id for the updated version also. Therefore I can not update it. So uuid does not work.

No, UUID will not work. If you are not able to figure out which document to update there is no automatic way to do so. Reindexing the full dataset each time may therefore be your best option.

One way to do this is to create an alias that you query the data through. You can then create the new index in the background and switch the alias to point to the new version before you remove the old version. There is no automatic way to do this so you may need to create a script to handle the reindexing.

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\
**Post date:** [August 2, 2023, 9:31am UTC](https://discuss.elastic.co/t/can-not-create-update-index-pipeline-without-unique-identifier-in-database/337494/5 "2023-08-02T09:31:44Z")

</div>

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