Doesn't The elasticsearch-jdbc support the sql statement like "select * from a join b where a.id = b.id where a.id = 1"?


(Miracle24) #1

I try to import some data with some costraint from the mysql to ES.Here is my shell code:
echo ' { "type" : "jdbc", "jdbc" : { "url" : "jdbc:mysql://localhost:3306/test", "user" : "root", "password" : "miracle", "locale" : "en_US", "sql" : "select a.aid as _id,a.aid as \"a.aid\",a.anum as \"a.anum\",b.bid as \"b.bid\",b.bnum as \"b.bnum\" from a left join b on a.aid = b.bid where a.id <= 100", "elasticsearch" : { "cluster" : "fycluster", "host" : "localhost", "port" : 9300 }, "index" : "testa", "type" : "testb", "index_settings" : { "index" : { "number_of_replicas" : 1 } }, "type_mapping": { "testb":{ "properties":{ "a":{ "properties":{ "aid":{"type":"integer"}, "anum":{"type":"string"} } }, "b":{ "properties":{ "bid":{"type":"integer"}, "bnum":{"type":"string"} } } } } } } } ' | java \ -cp "${lib}/*" \ -Dlog4j.configurationFile=${bin}/log4j2.xml \ org.xbib.tools.Runner \ org.xbib.tools.JDBCImporter curl -XGET 'localhost:9200/testa/_refresh'
It is curious that when I use sql statement 'select a.aid as _id,a.aid as \"a.aid\",a.anum as \"a.anum\",b.bid as \"b.bid\",b.bnum as \"b.bnum\" from a left join b on a.aid = b.bid limit 1,100'it works fine.
Thank you very much!


(Jörg Prante) #2

This is a specific MySQL question related to JDBC import, you can of course always ask questions at https://github.com/jprante/elasticsearch-jdbc/issues or a MySQL forum.

Both variants are fine as they are passed unmodified to MySQL. The result depends on your DB data.


(system) #3