3
  • Postgresql : 9.3.2
    • Elasticsearch : 0.90
    • jprante/elasticsearch-river-jdbc : 2.2.2
    • postgresql JDBC : 9.3-1100 JDBC 41

I'm trying to get a postgresql Json data type column into elasticsearch, using the elasticsearch river.

Here the created river

curl -XPUT 'localhost:9200/_river/business_river/_meta' -d '{
          "type" : "jdbc",
          "jdbc" : {
              "driver" : "org.postgresql.Driver",
              "url" : "jdbc:postgresql://localhost:5432/business",
              "user" : "postgres",
              "password" : "",
              "sql" : "select id, active, companies->'sic'->>'national_number' AS sic, companies->'names'->>'name' AS name, companies->'address'->>'country' AS country from businesses.business",
              "index" : "business",
              "type" : "jdbc"
     }
}'

The river complains that the "names" column doesn't exist !!! the query works in Postgresql which leads me to think that the Json data type is not yet supported by JDBC or the river.

Here the full message

[2014-01-13 07:47:27,919][INFO ][org.xbib.elasticsearch.river.jdbc.JDBCRiver] [Brigade] [jdbc][business_river] starting JDBC river: URL [jdbc:postgresql://localhost:5432/business], driver [org.postgresql.Driver], strategy [oneshot], index [jdbc]/[jdbc]
[2014-01-13 07:47:33,281][ERROR][org.xbib.elasticsearch.river.jdbc.strategy.simple.SimpleRiverFlow] ERROR: column "names" does not exist
  Position: 31
org.postgresql.util.PSQLException: ERROR: column "names" does not exist
  Position: 31
        at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2161)
        at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1890)
        at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:255)                                                                                         
        at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:560)                                                                                 
        at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:403)                                                                        
        at org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery(AbstractJdbc2Statement.java:283)                                                                            
        at org.xbib.elasticsearch.river.jdbc.strategy.simple.SimpleRiverSource.executeQuery(SimpleRiverSource.java:417)                                                         
        at org.xbib.elasticsearch.river.jdbc.strategy.simple.SimpleRiverSource.fetch(SimpleRiverSource.java:241)                                                                
        at org.xbib.elasticsearch.river.jdbc.strategy.simple.SimpleRiverFlow.move(SimpleRiverFlow.java:184)                                                                     
        at org.xbib.elasticsearch.river.jdbc.strategy.oneshot.OneShotRiverFlow.run(OneShotRiverFlow.java:38)                                                                    
        at java.lang.Thread.run(Thread.java:744)
Cœur
  • 37,241
  • 25
  • 195
  • 267
Sebastien Deschamps
  • 45
  • 1
  • 9
  • Show: The **EXACT** error message, and the back-end SQL that produced it from the PostgreSQL log files. Also, I don't think it's the cause, but your quoting is wrong. Look at how SO has syntax-highlighted your curl input. See `companies->'sic'`? Well, `sic` is unquoted text. It doesn't matter since there are no shell metacharacters. – Craig Ringer Jan 12 '14 at 23:22
  • Please write the solution in the answer area and check it so it can be considered as answered. – eliasah Jun 04 '14 at 18:08
  • @sdeschamps - Please move your solution edit to an answer and mark it accepted. – Andy Jun 18 '14 at 00:36
  • Note that Rivers have been deprecated since elasticsearch v1.5, and will be removed completely in 2.0 – Damian Oct 05 '15 at 18:49

1 Answers1

1

Thanks to Craig Ringer who pointed me to the real problem, I rewrited my river, replacing the simple quotes in the postgresql json query with "\u0027", and now it works just fine

Sebastien Deschamps
  • 45
  • 1
  • 9