# Updating Elasticsearch Indices conditionally when referring to 2 database table **URL:** \ **Category:** Logstash\ **Created:** [January 9, 2024, 3:04pm UTC](https://discuss.elastic.co/t/updating-elasticsearch-indices-conditionally-when-referring-to-2-database-table/350663 "2024-01-09T15:04:33Z")\ **Posts on this page:** 3\ **Page:** 1
**Author:** ![jainesh\_singh](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/jainesh_singh/32/119545_2.png) [@jainesh\_singh](https://discuss.elastic.co/u/jainesh_singh)\ **Post date:** [January 9, 2024, 3:04pm UTC](https://discuss.elastic.co/t/updating-elasticsearch-indices-conditionally-when-referring-to-2-database-table/350663/1 "2024-01-09T15:04:33Z")
**Description:** We have two SQL tables: `FileDetail` for storing file details and `FileUserActivity` for file activities. Using Logstash, we're indexing data into Elasticsearch with a flat index approach, combining file details and activities in a single document. However, when file details change in the `FileDetail` table (e.g., file owner modification), we need a solution to update all previous Elasticsearch indices related to that file. **Database Tables:** 1. `FileDetail` Table: - Columns: fileid, ..., owner, classificationId 1. `FileUserActivity` Table: - Columns: fileId, activityId, activityType, ... **Elasticsearch Index Structure:** Each Elasticsearch document combines fields from both tables, creating a flat structure. **Logstash Approach:** Logstash is employed to fetch data from both tables and merge it into a single document before indexing into Elasticsearch. **Requirement:** How can we efficiently update all previous Elasticsearch indices when file details change in the `FileDetail` table? Considering a dataset of around 10 lakhs (1 million) records generated every month. **Query:** If the owner of a file (identified by fileid) changes in the `FileDetail` table, what approach should be taken to ensure all historical Elasticsearch indices related to that file are updated accordingly? **Conclusion:** Seeking guidance on an effective solution to handle updates in Elasticsearch indices via logstash. How should i design logstash so that it could handle this? ---
**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:** [January 10, 2024, 6:45am UTC](https://discuss.elastic.co/t/updating-elasticsearch-indices-conditionally-when-referring-to-2-database-table/350663/2 "2024-01-10T06:45:37Z")
OK, so you have a denormalised view of these two tables in Elasticsearch. If you want to efficiently update all related documents when the parent or child document changes through Logstsh, I believe each document to be updated will need a unique document ID in Elasticsearch. Let's assume each `FileDetail` record has a PK called `FileId` and that each `FileUserActivity` record is identified through a `UUID` field, which is a unique identifier. This means that each denormalised document can be uniquely identified through an ID that is the concatenation of these 2 fields. In order to capture changes we assume you have (or will add) a timestamp field (`Timestamp`) to both tables that is updated when the record is created or updated through a trigger. You can now use a JDBC input to capture any additions and changes to these 2 tables in Logstash. This will run a join query which merges these two tables. It will concatenate the two ID fields into the unique identifier for the denormalised record and Logstash will set this as the document ID. The query will select only records where the MAX of the two Timestamp fields is greater that the [sql\_last\_value parameter](https://www.elastic.co/guide/en/logstash/8.11/plugins-inputs-jdbc.html#_predefined_parameters), and this will ensure only added or altered records are processed. If you want to combine this with a traditional time-based index, you can set `@timestamp` in Logstash using a `date` filter based on the created timestamp of the `FileDetail` table (does not change and ensures updates will go to the correct index). Does that make sense? ---
**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\ **Post date:** [February 7, 2024, 6:45am UTC](https://discuss.elastic.co/t/updating-elasticsearch-indices-conditionally-when-referring-to-2-database-table/350663/3 "2024-02-07T06:45:53Z")
This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.