Logstash doesn't apply my ES Template when I upload data

[romainrom]

Hello,

I'm trying to use Logstash to load Oracle data to ES applying a specific template.
logstash version : 6.4.2
Here are the steps followed:

Step 1: retrieve data from Oracle and send them to ES without any template

Step 2 : retrieve existing mapping in one file with cURL

curl –XGET http://localhost:9200/index9/_mapping?pretty > template9.json

Step 3 : adapt the mapping to do my template (red circled = what I’ve changed)

Step 4: load the template with cURL

Curl –XPUT –H “Content-type: application/json” http://localhost:9200/_template/index9?pretty –d @template9.json

Acknowledged = true

Step 5: drop existing ES index with cURL

curl –XDELETE http://localhost:9200/index9?pretty

ack = true

Step 6: Modify my logstash-config.conf file

Step 7 : launch logstash to load ES data

Logstash –f logstash.config.conf

No error detected within Logstash logs.

Step 8: results

Template looks good:

However I don’t get my “new object”,
all data are on the same level:

And the mapping seems different:

Anyone know what I’m missing ?

[balumurari1]

Hello,
can you provide your input code, which helps to give you solution

[romainrom]

Hello !
Thank you for your help!

Here's the input part of my logstash-config.conf:

[romainrom]

file: logstash-config.conf

input {
jdbc {
# Postgres jdbc connection string to our database, mydb
jdbc_connection_string => "jdbc:oracle:thin:@localhost:1521:DATABASE"

    # The user we wish to execute our statement as
    jdbc_user => "ROMAINROM"
	jdbc_password => "ROMAINROM"
	
    # The path to our jdbc driver for Oracle
    jdbc_driver_library => "xxxx\oracle-10g\ojdbc14.jar"
	
    # The name of the driver class for Oracle
    jdbc_driver_class => "Java::oracle.jdbc.driver.OracleDriver"
	
	
    # our query
    statement_filepath => "query.sql"

}

}
#filter {

json {

source => "message"

}

#}

[romainrom]

Sorry about the presentation ,
no filter applied.

[balumurari1]

The input code is fine, in the output did you mention elasticsearch to get the result,

Try this,

input {

jdbc {
jdbc_driver_library => "xxxx\oracle-10g\ojdbc14.jar"
jdbc_driver_class => "oracle.jdbc.driver.OracleDriver"
jdbc_connection_string => "jdbc:oracle:thin:@localhost:1521:DATABASE"
jdbc_user => "ROMAINROM"
jdbc_password => "ROMAINROM"
statement => "SELECT TOP 10 * FROM TABLE"
jdbc_paging_enabled => "true"
jdbc_page_size => "50000"
}

}

output{
elasticsearch { codec => json hosts => ["localhost:9200"] index => "index9" }
stdout { codec => rubydebug }
}

[romainrom]

Well actually I got the output in ES
but ES data are not mapped according to my template...

I add like you said:
jdbc_paging_enabled => "true"
jdbc_page_size => "50000"

I haven't modified my query as it's an inner join that worked so far.

And tried both output (I deleted index9 before each test):

I have still the same issue:

Template is not applied...

[balumurari1]

You need to check with the query whether it is retrieving correct data from database or not. Please verify once.

[romainrom]

My query looks like:
SELECT
TABLE1.CSAGUID,TABLE1.TYPE_FATHER,TABLE1.NBFILS,
TABLE2.PRIORITY,TABLE2.DISPLAY
FROM TABLE1
INNER JOIN TABLE2
ON TABLE1.FATHER_GUID=TABLE2.CSAGUID

This query gives 96 lines in Oracle DB and I also retrieve these 96 hits in ES DB,
all values seem correct so far.

So I think this query is correct.

[balumurari1]

can you display the output of this query,

http://localhost:9200/index9/_search

[romainrom]

yes, I get:

[balumurari1]

ok now can you please tell me what is the output you were expecting to be displayed

[romainrom]

yes, like described in step 3 and step 8 above, I want to get the following template applied :

Unfortunately I do not get the "newobject" level...

[romainrom]

Any ideas?

[romainrom]

Ok, I finally found a collleague of mine who told me that he had the same issue 2 years ago, so I give up templates with a logstash loading in ES...
I'm going to use the mutate option then.

[romainrom]

And a script to read the data.