0

I am using logstash to create a pipeline from elasticsearch to crate.io. Below is the config.

input{
 elasticsearch {
   hosts => "<host_name>:9200"
   index => "index1"
   query => '{ "size":10,"query": {"match_all": {} } }'
   }
}
filter{
    if!([bench_pose][M_Body_t]) {
            mutate{
                    add_field => {"[bench_pose][M_Body_t]" => null}
            }
    }
    if!([bench_pose][M_Jaw_t]) {
            mutate{
                    add_field => {"[bench_pose][M_Jaw_t]" => null}
            }
    }
}
output{
 jdbc {
       driver_class => "io.crate.client.jdbc.CrateDriver"
       driver_auto_commit => false
       driver_jar_path => "/etc/crate/crate-jdbc-standalone-1.12.3.jar"
       connection_string => "crate://<host_ip>:4300"
       statement => ["INSERT INTO table_name(path,bench_pose_m_Body_t,bench_pose_m_jaw_t) VALUES(?,?,?)",'path','%{[bench_pose][M_Body_t]}','%{[bench_pose][M_Jaw_t]}']
   }
}

The source has a field 'bench_pose' which is of OBJECT datatype, and the fields M_Body_t and M_Jaw_t under bench_pose are of type DOUBLE. These fields are NULLable in source and destination. This field has null values in few documents in the source. Even though the destination table has NULLable fields, when the insert statement encounters a document with null value, throws the below error.

JDBC - Exception. Not retrying. {:exception=>java.sql.SQLException: Validation failed for bench_pose_m_body_t: 'null' cannot be cast to type double  

If I remove the filter plugin, it throws an error as below.

JDBC - Exception. Not retrying. {:exception=>java.sql.SQLException: Validation failed for bench_pose_m_body_t: '%{[bench_pose][M_Body_t]}' cannot be cast to type double

If I hard code null value in an insert and run the query on crate sql, it executes correctly. How do I parse null values in these fields in logstash?

ashivan
  • 23
  • 8
  • Is your DB field `bench_pose_m_body_t` NULLable? If not, you could initialize it to 0 instead when creating it in the mutate filter – Val Aug 16 '16 at 18:16
  • Yes, bench_pose_m_body_t‌​ field is NULLable. – ashivan Aug 16 '16 at 18:31
  • How can I retain the null values coming in from source as is and parse it to destination? – ashivan Aug 16 '16 at 18:54
  • Can you change your INSERT query into this: `INSERT INTO table_name(path,bench_pose_m_Body_t,bench_pose_m_jaw_t) VALUES(?,TRY_CAST(? as double),TRY_CAST(? as double))` – Val Aug 16 '16 at 19:50
  • Yes, it works fine now. Thank you! – ashivan Aug 16 '16 at 20:33

1 Answers1

2

You need to change the INSERT statement to this:

   statement => ["INSERT INTO table_name(path,benc‌​h_pose_m_Body_t,bench‌​_pose_m_jaw_t) VALUES(?,TRY_CAST(? as double),TRY_CAST(? as double))",'path','%{[bench_pose][M_Body_t]}','%{[bench_pose][M_Jaw_t]}']

TRY_CAST will return null instead of throwing an error in case of incompatible type cast.

Val
  • 207,596
  • 13
  • 358
  • 360