Bulk Creating & Bulk Updating ElasticSearch Index Document using Logstash

Question

Documents are not getting updated but are getting newly created instead of updating the document.

COMPANY_ID is a unique column..

SAMPLE DATA

COMPANY_NAME,LOGO_EXT,COMPANY_ID

ABC LIMITED,JPG,ABC000001

XYZ LIMITED,PNG,ABC000002

AAA LLC,,ABC000003

I am able to create the index and the documents.

The problem is when I update the index the document is getting created instead of getting updated. E.g. BEFORE ABC LIMITED,JPG,ABC000001

AFTER ABCD LIMITED,JPG,ABC000001

So therefor only COMPANY_NAME should be updated.

1. Successfully able to create the index using the below code :-

BAT FILE

cd C:\logstash-7.3.1\bin logstash -f C:\logstash.conf

C:\logstash.conf FILE

input {  
    jdbc {  
        jdbc_driver_library => "C:\sqljdbc_7.4\enu\mssql-jdbc-7.4.1.jre8.jar"  
        jdbc_driver_class => "com.microsoft.sqlserver.jdbc.SQLServerDriver"  
        jdbc_connection_string => "jdbc:sqlserver://;user=;password=;"  
        jdbc_user => ""  
        jdbc_password => ""  
        statement => "SELECT COMPANY_NAME,LOGO_EXT,COMPANY_ID from dbo.CompanyMaster WITH(NOLOCK) ORDER BY COMPANY_NAME"  
    }  
}  
filter {}  
output {  
    stdout {  
        codec => json_lines  
    }  
    elasticsearch {  
        hosts => "http://localhost:9200"  
        index => "companylistindex"  
        document_id => "%{COMPANY_ID}"
        action => index
    }  
}

2. Update code

input {  
    jdbc {  
        jdbc_driver_library => "C:\sqljdbc_7.4\enu\mssql-jdbc-7.4.1.jre8.jar"  
        jdbc_driver_class => "com.microsoft.sqlserver.jdbc.SQLServerDriver"  
        jdbc_connection_string => "jdbc:sqlserver://;user=;password=;"  
        jdbc_user => ""  
        jdbc_password => ""  
        statement => "SELECT COMPANY_NAME,LOGO_EXT,COMPANY_ID from dbo.CompanyMaster WITH(NOLOCK) WHERE ModifiedOn>'2019-11-01'"  
    }  
}  
filter {}  
output {  
    stdout {  
        codec => json_lines  
    }  
    elasticsearch {  
        hosts => "http://localhost:9200"  
        index => "companylistindex"  
        document_id => "%{COMPANY_ID}"
    }  
}

Please help me update the document.

Note : Only COMPANY_NAME or LOGO_EXT needs to be updated if different. COMPANY_ID is unique column.

You need to use action => update. Also, read about doc_as_upsert — Polynomial Proton
– Polynomial Proton, Commented Nov 1, 2019 at 20:20
I have added action => "update" doc_as_upsert => "true" but still COMPANY_ID is getting duplicated. It should update the record. Do I have to mark {COMPANY_ID} as a primary key when newly creating the index ? Am I missing something...? — Shirish Patel
– Shirish Patel, Commented Nov 4, 2019 at 7:35
It looks like you have multiple events for same id. You probably need to use aggregate filter plugin. Also, order by id on sql side and change worker count to 1. — Polynomial Proton
– Polynomial Proton, Commented Nov 4, 2019 at 16:05

Sandeep Kanabar · Accepted Answer · 2019-11-02 07:42:51Z

1

In addition to the comment suggested by @Polynomial Proton, you no longer need 2 output sections. Just 1 section like below would do:

output {  
    stdout {  
        codec => json_lines  
    }  
    elasticsearch {  
        hosts => "http://localhost:9200"  
        index => "companylistindex"  
        document_id => "%{COMPANY_ID}"
        action => "update"
        doc_as_upsert => "true"
    }  
}

This will take care of both indexing and updating.

answered Nov 2, 2019 at 7:42

Sandeep Kanabar

1,31220 silver badges36 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Shirish Patel Over a year ago

I have added action => "update" doc_as_upsert => "true" but still COMPANY_ID is getting duplicated. It should update the record. Do I have to mark {COMPANY_ID} as a primary key when newly creating the index ? Am I missing something...?

Collectives™ on Stack Overflow

Bulk Creating & Bulk Updating ElasticSearch Index Document using Logstash

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related