Hi @logstash.chirag @Logstash-lover @logstash_user @logstash_oz @logstash @logstashisawesome
we have to create index runtime using logstash. we have a table in database,In that we have columns that contains some decimal and some integer values. After run logstash .conf file,index should be created at runtime. We are able to create index but for few records logstash giving exception "can not convert from float to long and long to float". my columns contains both, some decimal and integer values. So we don't know that why It is giving exception for typecasting from float to long this is first thing. Sometime getting long to float exception also.
we Got Exception in Console:
[2018-07-09T19:21:03,284][WARN ][logstash.outputs.elasticsearch] Could not index event to Elasticsearch. {:status=>400, :action=>["index", {:_id=>nil, :_index=>"bank_crisil_rated_test_for_problm", :_type=>"doc", :_routing=>nil}, #<LogStash::Event:0x7dd752b4>], :response=>{"index"=>{"_index"=>"bank_crisil_rated_test_for_problm", "_type"=>"doc", "_id"=>"wMdPf2QBylXhGFh3VX8g", "status"=>400, "error"=>{"type"=>"illegal_argument_exception", "reason"=>"mapper [total_income] cannot be changed from type [float] to [long]"}}}}
[2018-07-09T19:21:03,284][WARN ][logstash.outputs.elasticsearch] Could not index event to Elasticsearch. {:status=>400, :action=>["index", {:_id=>nil, :_index=>"bank_crisil_rated_test_for_problm", :_type=>"doc", :_routing=>nil}, #<LogStash::Event:0x86af032>], :response=>{"index"=>{"_index"=>"bank_crisil_rated_test_for_problm", "_type"=>"doc", "_id"=>"HMdPf2QBylXhGFh3VYAn", "status"=>400, "error"=>{"type"=>"illegal_argument_exception", "reason"=>"mapper [capital_adequacy_ratio] cannot be changed from type [float] to [long]"}}}}
[2018-07-09T19:21:03,287][WARN ][logstash.outputs.elasticsearch] Could not index event to Elasticsearch. {:status=>400, :action=>["index", {:_id=>nil, :_index=>"bank_crisil_rated_test_for_problm", :_type=>"doc", :_routing=>nil}, #<LogStash::Event:0x3401b2af>], :response=>{"index"=>{"_index"=>"bank_crisil_rated_test_for_problm", "_type"=>"doc", "_id"=>"HsdPf2QBylXhGFh3VYAn", "status"=>400, "error"=>{"type"=>"illegal_argument_exception", "reason"=>"mapper [total_income] cannot be changed from type [float] to [long]"}}}}
Note: I have some predefined table's data in my database which i want to push in the ES using logstash JDBC and try to create index.
Here is my logstash_Conf_file:
input {
jdbc {
jdbc_validate_connection => true
jdbc_connection_string => "jdbc:oracle:thin:@AbDV:1931/CUPS"
jdbc_user => "ABCTIX"
jdbc_password => "ABC@2017"
jdbc_driver_library => "/opt/READONLYREST/OSS/logstash-6.3.0/ojdbc7.jar"
jdbc_driver_class => "Java::oracle.jdbc.driver.OracleDriver"
statement => "Select Distinct Bm.Crmcompanyid,Cd.Company_Name,
Bm.Datatype,Bm.Template,
Bm.Isconsolidated,
Bm.Periodendson,
Bm.Crr_Date,
bm.period_code,
Column1 TOTAL_INCOME ,
Column10 TOTAL_OPERATING_EXPENDITURE ,
Column11 Total_Provisions_Cont ,
Column12 Adjusted_PROFIT_AFTER_TAX ,
Column13 Net_Intrst_Incm_Avg_Ttl_Assts ,
Column14 Non_Int_Income_Avg_Ttl_Assts ,
Column15 Non_Int_expenses_Avg_Ttl_Assts ,
Column16 PAT_Adjusted_Avg_Total_Assets ,
Column17 Intrst_Paid_on_Dep_Avg_Dep ,
Column18 Tier_I_Capital_Percentage ,
Column19 Capital_Adequacy_Ratio ,
Column2 TOTAL_ASSETS ,
Column20 Gross_NPA_Loans_Advances ,
Column21 Net_NPA_Loans_Advances ,
Column22 Networth_Net_NPA ,
Column23 CASA ,
Column24 Operating_Expenses ,
Column25 Equity_Share_Capital ,
Column3 TOTAL_LOANS_ADVANCES ,
Column4 TANGIBLE_NETWORTH ,
Column5 TOTAL_DEPOSITS ,
Column6 TOTAL_INTEREST_INCOME ,
Column7 TOTAL_INTEREST_PAID ,
Column8 NET_INTEREST_INCOME ,
Column9 TOTAL_OTHER_INCOME
From Banknbfc_Periodmaster_Synm Bm,
Banknbfc_Perioddata_Synm Bd,
company_details_mv_synm cd
Where Bm.Period_Code = Bd.Period_Code
And Cd.Company_Code = Bm.Crmcompanyid
and bm.template = 'Bank'
and cd.company_status = 'Active'
"
}
}
filter {
mutate {
convert => {"TOTAL_INCOME"=>"float" }
convert => {"Capital_Adequacy_Ratio"=>"float" }
}
}
output {
elasticsearch {
hosts => "XXXXXXXX:123:176:9200"
index => "bank_crisil_rated_test_for_problm"
user => "ajitb"
password => "Abc123"
ssl => true
ssl_certificate_verification => false
truststore => "/opt/READONLYREST/elasticsearch-6.3.0/config/keystore.jks"
truststore_password => "readonlyrest"
}
We have used all datatypes in mutate. like
filter {
mutate {
convert => {"TOTAL_INCOME"=>"float" }
convert => {"Capital_Adequacy_Ratio"=>"integer_eu" }
}
}
filter {
csv {
source => "data"
columns => ['TOTAL_INCOME', 'Capital_Adequacy_Ratio']
separator => ";"
}
mutate {
convert => {
"TOTAL_INCOME" => "float"
"Capital_Adequacy_Ratio" => "float_eu"
}
}
}
mutate
{
convert => [ "[TOTAL_INCOME]", "float" ]
convert => [ "[Capital_Adequacy_Ratio]", "float" ]
}
mutate {
convert => {
"Total_Income" => "integer"
}
}
But we are getting same issue. Also we have tried long datatype but getting same exception. My database column values are integer and decimal both.
Please provide solution on this issue.