Elasticsearch logstash JDBC giving exception for data type casting float to long and long to float


(ajit) #1

Hi Team,
I have to create index dynamically using logstash jdbc. I have used sql query in logstash. And my few column contains some decimal and some long values mixed. After starting logstash I am getting exception
cannot be changed from type [long] to [float]
Please help us to resolve the issue [long] to [float] and [float] to [long].
Note: I have used mutate filter to convert data type but its not working. And I don't want to make mapping manually for index.
Please provide solution on this issue.
Below is my configuration logstash.

input {
    jdbc {
        jdbc_validate_connection => true
        jdbc_connection_string => "jdbc:oracle:thin:@DEV:8080/SID"
        jdbc_user => "AJIT"
        jdbc_password => "pass#1234"
        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,
  cast(bm.period_code as decimal(15,2)),
cast(Column1 as decimal(15,2))  TOTAL_INCOME  ,
cast(Column10 as decimal(15,2))  TOTAL_OPERATING_EXPENDITURE ,
cast(Column11 as decimal(15,2))  Total_Provisions_Cont ,
cast(Column12 as decimal(15,2))  Adjusted_PROFIT_AFTER_TAX ,
cast(Column13 as decimal(15,2))  Net_Intrst_Incm_Avg_Ttl_Assts ,
cast(Column14 as decimal(15,2))  Non_Int_Income_Avg_Ttl_Assts  ,
cast(Column15 as decimal(15,2))  Non_Int_expenses_Avg_Ttl_Assts  ,
cast(Column16 as decimal(15,2))  PAT_Adjusted_Avg_Total_Assets ,
cast(Column17 as decimal(15,2))  Intrst_Paid_on_Dep_Avg_Dep  ,
cast(Column18 as decimal(15,2))  Tier_I_Capital_Percentage ,
cast(Column19 as decimal(15,2))  Capital_Adequacy_Ratio  ,
cast(Column2 as decimal(15,2)) TOTAL_ASSETS  ,
cast(Column20 as decimal(15,2))  Gross_NPA_Loans_Advances  ,
cast(Column21 as decimal(15,2))  Net_NPA_Loans_Advances  ,
cast(Column22 as decimal(15,2))  Networth_Net_NPA  ,
cast(Column23 as decimal(15,2))  CASA  ,
cast(Column24 as decimal(15,2))  Operating_Expenses  ,
cast(Column25 as decimal(15,2))  Equity_Share_Capital  ,
cast(Column3 as decimal(15,2)) TOTAL_LOANS_ADVANCES  ,
cast(Column4 as decimal(15,2)) TANGIBLE_NETWORTH ,
cast(Column5 as decimal(15,2)) TOTAL_DEPOSITS  ,
cast(Column6 as decimal(15,2)) TOTAL_INTEREST_INCOME ,
cast(Column7 as decimal(15,2)) TOTAL_INTEREST_PAID ,
cast(Column8 as decimal(15,2)) NET_INTEREST_INCOME ,
cast(Column9 as decimal(15,2)) TOTAL_OTHER_INCOME  
From Banknbfc_Periodmaster_Synm Bm,
  TEMP 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'
--and column1 in (1322394.26,1299694)
ORDER BY Periodendson "       
 }
}

filter{

  mutate {convert => ["TOTAL_INCOME","float"] }
  mutate {convert => ["Capital_Adequacy_Ratio","float"] }
  mutate {convert => ["Networth_Net_NPA","float"] }
  mutate {convert => ["TANGIBLE_NETWORTH","float"] }
  mutate {convert => ["Non_Int_expenses_Avg_Ttl_Assts","float"] }
  mutate {convert => ["Total_Provisions_Cont","float"] }
  mutate {convert => ["TOTAL_INTEREST_INCOME","float"] }
  mutate {convert => ["TOTAL_LOANS_ADVANCES","float"] }
  mutate {convert => ["Net_NPA_Loans_Advances","float"] }
  mutate {convert => ["Tier_I_Capital_Percentage","float"] }
}

output   {
   
    elasticsearch {
    	hosts => "localhost:9200"
        index => "ajit_test"    
        user => "c-ajitb"
        password => "pass#1234"
        ssl => true
        ssl_certificate_verification => false
        truststore => "/opt/READONLYREST/elasticsearch-6.3.0/config/keystore.jks"
        truststore_password => "readonlyrest"

    
    }
}

Please give us solution on this issue. Not getting any reply from logstash team.


(Christian Dahlqvist) #2

Please stop opening duplicate issues. This seems to be exactly the same as this one, so I will close this.

This forum is manned by volunteers, so there is no guarantee of getting answer nor any SLA.

If you need guarantees that someone takes on your issue within an SLA, Elastic offers commercial subscriptions.


(Christian Dahlqvist) #3