How to add multiple object types to elasticsearch using jdbc river?

Question

I'm using the jdbc river to successfully add one object type, "contacts", to elasticsearch. How can I add another contact type with different fields? I'd like to add "companies" as well.

What I have is below. Do I need to do a separate PUT statement? If I do, no new data appears to be added to elasticsearch.

PUT /_river/projects_river/_meta
{
    "type" : "jdbc",
    "index" : {
        "index" : "ALL",
        "type" : "project",
        "bulk_size" : 500,
        "max_bulk_requests" : 1,
        "autocommit": true
        },
    "jdbc" : {
        "driver" : "com.microsoft.sqlserver.jdbc.SQLServerDriver",
        "poll" : "30s",
        "strategy" : "poll",
        "url" : "jdbc:sqlserver://connectionstring",
        "user":"username","password":"password",
        "sql" : "select ContactID as _id, * from Contact"
        }
}

Also, when search returns results, how can I tell if they are of type contact or company? Right now they all have a type of "jdbc", and changing that in the code above throws an error.

Answer 1

You can achieve what you want with inserting several columns to your sql query.

Like ContactID AS _id you can also define indexName AS _index and indexType AS _type in your sql query.

Also, if you need another river, add rivers with different _river types.

In your case such as,

PUT /_river/projects_river2/_meta + Query ....
PUT /_river/projects_river3/_meta + Query ....

Answer 2

Anyone else who stumbles across this, please see official documentation for syntax first: https://github.com/jprante/elasticsearch-river-jdbc/wiki/How-bulk-indexing-isused-by-the-JDBC-river

Here's the final put statement I used:

PUT /_river/contact/_meta
{
    "type":"jdbc",
    "jdbc": {
        "driver":"com.microsoft.sqlserver.jdbc.SQLServerDriver",
        "url":"connectionstring",
        "user":"username",
        "password":"password",
        "sql":"select ContactID as _id,* from Contact",
        "poll": "5m",
        "strategy": "simple",
        "index": "contact", 
        "type": "contact" 
    }       
}