java.sql.SQLException: Validation failed for <field_name>: 'null' cannot be cast to type double

Question

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?

Answer 1

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.