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 Elasticsearch as
[
{
"_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 Elasticsearch 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 so there is just one document with _id as %{projectsRowId}". If I exclude document_id there are duplicates which get created.
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]}"
}
}