I need to get one to many relation from oracle database and create an elasticsaerch index with documents with multi value field (array).
I have two oracle tables:
CarMark (cmk_id integer, cmk_name varchar2(100) );
(1 , "BMW")
CarModeL (cml_id integer, cmk_id integer, cml_name varchar2(100));
(1 , 1 , "X5")
(2 , 1 , "M3")
My statment is:
select cmk_id, cmk_name, cml_name
from CarMark left join
CarModel on CarModel.cmk_id = CarMark.cmk_id;
I should get an ES index with the below document:
"cmk_id" : "1",
"cmk_Name" : "BMW"
"cml_name" : [ "X5" , "M6" ].
I tried to use the below logstash configuration, but is doesn't work
input {
jdbc {
jdbc_driver_library => "C:jdbc\ojdbc6.jar"
jdbc_driver_class => "Java::oracle.jdbc.OracleDriver"
jdbc_connection_string => "jdbc:oracle:thin:@localhost:1521:orcl"
jdbc_user => "testuser"
jdbc_password => "pass"
statement => "select cmk_id, cmk_name, cml_name
from CarMark left join
CarModel on CarModel.cmk_id = CarMark.cmk_id;"
}
}
output {
elasticsearch { host => localhost
index => cars
document_type => item
document_id => [%{cmk_id}]
}
stdout { codec => rubydebug }
}