Cannot get any results from ElasticSearch with JDBC River

Question

I cannot figure out how to use this plugin at all.
I am running this curl:

curl -XPUT 'localhost:9200/_river/faycare_kids/_meta' -d '{
  "jdbc":{
      "driver" : "org.postgresql.Driver",
      "url" : "jdbc:postgresql://localhost:5432/faycare",
      "user" : "faycare",
      "password" : "password",
      "strategy" : "simple",
      "poll" : "5s",
      "scale" : 0,
      "autocommit" : true,
      "fetchsize" : 10,
      "index" : "faycare",
      "type" : "kid",
      "max_rows" : 0,
      "max_retries" : 3,
      "max_retries_wait" : "10s",
      "sql"   : "SELECT kid.id as _id,kid.first_name,kid.last_name FROM kid;"
  }
}'

It returns:

{"ok":true,"_index":"_river","_type":"faycare_kids","_id":"_meta","_version":1}

How do I search/fetch/see my data?

How do I know if anything is indexed?

I tried so many things:

curl -XGET 'localhost:9200/_river/faycare_kids/_search?pretty&q=*'

This gives me info about the _river

curl -XGET 'localhost:9200/faycare/kid/_search?pretty&q=*'

This tells me: "error" : "IndexMissingException[[faycare] missing]"

I am running sudo service elasticsearch start to run it in the background.

Answer 1

The problem is in the way you setting up your river. You specifying and index and type where the river should bulk indexing records in the wrong place.

The proper way of doing this would be this:

curl -XPUT 'localhost:9200/_river/faycare_kids/_meta' -d '{
  "type" : "jdbc",
  "jdbc":{
      "driver" : "org.postgresql.Driver",
      "url" : "jdbc:postgresql://localhost:5432/faycare",
      "user" : "faycare",
      "password" : "password",
      "strategy" : "simple",
      "poll" : "5s",
      "scale" : 0,
      "autocommit" : true,
      "fetchsize" : 10,
      "max_rows" : 0,
      "max_retries" : 3,
      "max_retries_wait" : "10s",
      "sql"   : "SELECT kid.id as _id,kid.first_name,kid.last_name FROM kid;"
  },
  "index":{
      "index" : "faycare",
      "type" : "kid"
  }
}'

Answer 2

For one, I would install elasticsearch head it can be super useful for checking on your cluster.

You can get stats for all indices:

curl -XGET 'http://localhost:9200/_all/_status'

You can check if an index exists:

curl -XHEAD 'http://localhost:9200/myindex'

You should be able to search all indices like this:

  curl -XGET 'localhost:9200/_all/_search?q=*'

If nothing shows up, your rivers are probably not working, I would check your logs to see if any errors appear.

Answer 3

I appreciate all of your help. The elastic-head did give me some insight. Apparently I just had something wrong with my JSON For some reason when I changed my JSON to this it worked:

curl -XPUT 'localhost:9200/_river/my_jdbc_river/_meta' -d '{
      "type" : "jdbc",
      "jdbc" : {
          "driver" : "org.postgresql.Driver",
          "url" : "jdbc:postgresql://localhost:5432/faycare",
          "user" : "faycare",
          "password" : "hatpants",
          "index" : "jdbc",
          "type" : "jdbc"
          "sql"   : "SELECT kid.id as _id,kid.first_name,kid.last_name FROM kid;"
      }
 }'

I am not sure what specifically needed to be changed to make this work, but it does now work. I am guessing that it is the outer jdbc needed to be added. I am guessing I can change the inner index and type.

Answer 4

I wrote a quick post on using this plugin, hopefully in can give you a little more insight - the post is located here.