geoPoints: field name (AS) on sql statement cannot contain dot sign

[lhenry2k]

I'm working well with the new version of jdbc, but cannot find how to let the plugin map geoPoints using the dot notation in the sql statement unlike the old version of jdbc was able to do.

select ..... t.lat as location.lat, t.lon as location.lt

from table t

I tried to put "type_mapping" on my .conf file but with no luck .. here is my test.conf file I run with "logstash -f test.conf"

input { jdbc {

jdbc_driver_library => "mysql-connector-java-5.1.33.jar"
    jdbc_driver_class => "com.mysql.jdbc.Driver"
    jdbc_connection_string => "jdbc:mysql://127.0.0.1:3306/db1"
    jdbc_user => "me"
    jdbc_password => "pass"
    statement_filepath => "file.sql"

} }

output { elasticsearch { index => "logstash-offers" document_type => "offers" hosts => "127.0.0.1" document_id => "%{IDoffer}" } }

[jprante]

For geo point support, see the demo

bin/mysql-geo-points.sh

in the distribution.

https://github.com/jprante/elasticsearch-jdbc/blob/master/bin/mysql-geo-points.sh

[lhenry2k]

@jprante: i ran the script and got what is below. I was wondering if I could make it work with the .conf file to be passed to logstash, wich approach I found very clear and easy to read ( I'm a total newby) tnx

~/Documents/kibana/elasticsearch-jdbc-master# ./bin/mysql-geo-points.sh

./bin/mysql-geo-points.sh: 18: ./bin/mysql-geo-points.sh: Bad substitution {"error":{"root_cause":[{"type":"index_not_found_exception","reason":"no such index","resource.type":"index_or_alias","resource.id":"myjdbc","index":"myjdbc"}],"type":"index_not_found_exception","reason":"no such index","resource.type":"index_or_alias","resource.id":"myjdbc","index":"myjdbc"},"status":404}Error: Could not find or load main class org.xbib.tools.Runner {"error":{"root_cause":[{"type":"index_not_found_exception","reason":"no such index","resource.type":"index_or_alias","resource.id":"myjdbc","index":"myjdbc"}],"type":"index_not_found_exception","reason":"no such index","resource.type":"index_or_alias","resource.id":"myjdbc","index":"myjdbc"},"status":404}{ "error" : { "root_cause" : [ { "type" : "index_not_found_exception", "reason" : "no such index", "resource.type" : "index_or_alias", "resource.id" : "myjdbc", "index" : "myjdbc" } ], "type" : "index_not_found_exception", "reason" : "no such index", "resource.type" : "index_or_alias", "resource.id" : "myjdbc", "index" : "myjdbc" }, "status" : 404 }

[jprante]

Bad substitution is a shell error and not related to JDBC importer.

What OS do you use?

[jprante]

Also note, you are on directory elasticsearch-jdbc-master, from which I conclude you run the example from the source tree. This does not work. It only works in a release directory which is elasticsearch-jdbc-2.1.1.2 where binaries are in place.

[lhenry2k]

Ok, I ran from release and fixed the "Bad substitution" error executing on ubuntu with

bash bin/mysql-geo.sh 

In mysql-geo-points.sh I also changed the command on the ES call, from XDELETE to XPUT and jdbc correctly connects to mysql but I the index jdbc creates has no rows and contains only ES special fields: _source _id _type _index _score

[jprante]

This is because you changed DELETE to PUT so you prevented ES to create a geo_point field in a new index myjdbc with type mytype.

The demo runs perfectly on Red Hat Linux and Mac OS X.

[lhenry2k]

.. and even on Ubuntu! Meanwhile I updated ES so jdbc was not version matching.. tnx for your help!

[jprante]

It was a version mismatch? OK, I have to think about a clear error message then.

[lhenry2k]

yes, I was using jdbc 2.1.0.0 on ES 2.1.1