Missing Converter handling for full class name=org.postgresql.util.PGobject, simple name=PGobject

Question

There is a column of geometry type in my postgres table. I checked that the rest of the columns worked fine postgres-> logstash-> elasticsearch. However, I am not sure how to cast the geometry type. Can anyone give advice on how to cast? Please help me.

here is my conf now.

    jdbc {
    jdbc_connection_string => "jdbc:postgresql://localhost:5432/atlasdb?useTimezone=true&useLegacyDatetimeCode=false&serverTimezone=UTC&useSSL=false&useUnicode=true&characterEncoding=utf8"
    jdbc_user => "atlas"
    jdbc_password => "atlas"
    jdbc_validate_connection => true
    jdbc_driver_library => "/lib/postgres-42-test.jar"
    jdbc_driver_class => "org.postgresql.Driver"
    columns_charset => { "region_name_kr" => "UTF-8" }
    schedule => "* * * * *"
    statement => "SELECT region_id, region_name_full, boundaries_nearby from expedia_region_union_copy_jpa_test order by region_id asc limit 100"
}

stdin {
    codec => plain { charset => "UTF-8"}
}

}
filter {
    json { source => "boundaries_nearby" }
}
output {
    elasticsearch {
    hosts => [ "localhost:9200" ]
    index => "2020-05-06-wed"
    doc_as_upsert => true
    action => "update"
    document_id => "%{region_id}"
}
    stdout { codec => rubydebug }
}

https://discuss.elastic.co/t/sql-query-from-logstash-causes-an-error-pgobject/138648 It seems to overlap with this question, but I couldn't find a clue from that.

here is my sample geometry data

POLYGON ((19.969742 52.425483, 19.972195 52.424076, 19.973841 52.422807, 19.976831 52.419417, 19.976941 52.419093, 19.978737 52.414383, something else...))

also I think this https://discuss.elastic.co/t/missing-converter-handling-for-full-class-name-org-postgresql-util-pgobject-simple-name-pgobject/163338/3 might be an answer. but I do not understand well this answer.

"The issue is solved by cast the json result to an string in postgres level and parse the json string in logstash."

If anyone can explain this, please help. Or, please answer the person who had the same problem. thank you in advance.

user_14028578 · Answer

I have received the same error while ingesting data from postgres for a simple query where columns having datatype as citext.

Error: "exception when executing jdbc query {:exception=>#

The error got resolved after casting citext to text. e.g.

select name::text from contact;

user_10194999 · Answer

I had a problem with jsonb and geometry type casting, When loading data into elasticsearch using logstash in postgres.

In conclusion

For geometry type, use st_asgeojson to change it. Then the string is returned. Then you can cast it again with json.

In the case of jsonb, I received it as text and parse it with json using ruby filter. I'm not sure if this is the best way to go. Still, there was nothing short of solving the problem.

here is my sample conf file. Due to circumstances, maybe there is a typo. Use after confirmation.

input {
    jdbc {
        jdbc_connection_string => "jdbc:postgresql://ip:port/dbname"
        jdbc_user => "user"
        jdbc_password => "password"
        jdbc_driver_library => "driver.jar"
        jdbc_validate_connection => true
        jdbc_driver_class => "org.postgresql.Driver"
        columns_charset => {"region_name_kr" => "UTF-8"}
        schedule => "*/10 * * * *"
        statement => "SELECT region_id, region_name, st_asgeojson(boundaries) as boundaries
                             country::text from expedia_region_union"
        jdbc_paging_enabled => "true"
        jdbc_page_size => "100000"
    }    
    stdin {
        codec => plain { charset => "UTF-8" }
    }
}
filter {
    ruby {
        code => "
            require 'json'
            begin
                country_json = JSON.parse(event.get('country').to_s || '{}')
                event.set('country', country_json)
            rescue Exception => e
                event.tag('invalide country json')
            end
            begin
                boundaries_json = JSON.parse(event.get('boundaries').to_s || '{}')
                event.set('boundaries', boundaries_json)
            rescue Exception => e
                event.tag('invalide boundaries json')
            end
        "
    }
}
output {
    elasticsearch {
        hosts => ["https://host"] # if you have many elastic nodes, please pick master node. 
        index => "indexName"
        doc_as_upsert => true
        action => "update"
        document_id => "%{region_id}"
    }
    stdout { codec => rubydebug }
}

Missing Converter handling for full class name=org.postgresql.util.PGobject, simple name=PGobject

2 Answers