To prevent data duplication while injesting data from logstash I am adding a document_id string for logstash conf with a peopleRowId column. However it does not get evaluated. So in my case I am trying to set document id as document_id => "%{[document][projectsRowId]}", However this does not get evaluated for some reason and id in elastic search as I added ROW_NUMBER() OVER ( ORDER BY a.created_at ) as projectsRowId to create unique id

[
      {
        "_index" : "projectsv3",
        "_type" : "_doc",
        "_id" : "%{[document][projectsRowId]}",
        "_score" : 1.0,
        "_source" : {...single record}

]

I am not sure why document id is not getting enabled. Using elastic search 7 and ECS is also disabled. I have tried out other methods too like filter with fingerprint I have also tried to set document id as document_id => "%{projectsRowId}" although in all cases it does not get evaluated

input {
    jdbc {
        jdbc_driver_library => "C:\\ElasticStack\\mysql-connector-java-8.0.24\\mysql-connector-java-8.0.24.jar"
        jdbc_driver_class => "com.mysql.jdbc.Driver"
        # mysql jdbc connection string to our database, mydb
         jdbc_connection_string =>  "jdbc:mysql://127.0.0.1:3306/corrabla_sercweb"
        # The user we wish to execute our statement as
        jdbc_user => "root"
        jdbc_password => "root"
        schedule => "* * * * *"
        clean_run => true
        # use_column_value => true
        # tracking_column => "%{[@metadata][fingerprint]}"
        # tracking_column_type => "numeric"
        # our query to fetch people details
        statement => "select ROW_NUMBER() OVER (
        ORDER BY a.created_at
    ) as projectsRowId , (a.created_at), tr.report_number as 'tech_report_number', tr.file_s3 as 'tech_report_file_name', tr.abstract as 'tech_report_abstract' , c.prefix as 'piPrefix' , c.first_name as 'piFirstName', c.middle_name as 'piMiddleName' ,c.last_name as 'piLastName', b.person_id, d.prefix as 'coPiPrefix' "
        # use_column_value => true
        # tracking_column => id
        # tracking_column_type => "numeric"
    }
}
output {
   elasticsearch {
       action => "create"
       hosts => "http://127.0.0.1:9200"
       index => "projectsv3"
       doc_as_upsert => true
       document_id => "%{[document][projectsRowId]}"
        }
}

CodePudding user response：

By default a jdbc input will fold field names to lower case, so your event will have a field called projectsrowid, not projectsRowId. If you set lowercase_column_names => false on the input then `document_id => "%{[projectsRowId]}" will work.