JDBC Sqlserver to elastic search : one input to different output?
So I figured out what I was doing wrong, there were 2 different problems.
1. Wrong inserted row count
This was caused by the SQL query I was using. Elastic wants unique document_id values and the left join I was using caused multiple rows to be returned with the same values. What elastic does in such case is it overwrites the existing row with the new values.
2. Assign rows to different indexes and types
I had tried using the same syntax as in the document_id which ended up looking like this:
index =&gt; &quot;%{Language}&quot;
document_type =&gt; &quot;%{MerchantID}&quot;

This would result in rows getting inserted with in an index called %{Language} which was obviously not the desired result.
The problem was simple: ELASTIC DOES NOT LIKE UPPERCASE LETTERS.
So my final configuration file ended up looking like this:
input {
jdbc {
jdbc_driver_library =&gt; &quot;C:\elastic\Microsoft-JDBC-Driver-6.0-for-SQL-Server\sqljdbc_6.0\enu\jre8\sqljdbc42.jar&quot;
jdbc_driver_class =&gt; &quot;com.microsoft.sqlserver.jdbc.SQLServerDriver&quot;
jdbc_connection_string =&gt; &quot;jdbc:sqlserver://10.11.12.13:1433;databaseName=testdb1;integratedSecurity=false;user=ElasticExtractor;password=flyingweisels;&quot;
jdbc_user =&gt; &quot;ElasticExtractor&quot;
jdbc_password =&gt; &quot;flyingweisels&quot;
statement =&gt; &quot;select top 50000 id itemid,item_id itemid,Merchant_ID merchantid,model,case when (ID%2=0) then 'germanindex' else 'englishindex' end language from items order by id desc&quot;
}
}
output {
elasticsearch {
hosts =&gt; &quot;localhost:9200&quot;
index =&gt; &quot;%{language}&quot;
document_type =&gt; &quot;%{merchantid}&quot;
document_id =&gt; &quot;%{itemid}&quot;
}
}

It works great !