Elasticsearch : set up parent/child using jdbc-rivers

Question

I am reading data from Sql Server database/table using jdbc-river currently. As of now I have created a individual type for each of the table in my database. As next step in my implementation I would like to use parent/child types so that I can translate the relationship between my sql tables and store them. ``` Table1 Col_id| name| prop1|prop2|prop3 child_table1 col_id| table_id| child_prop1|child_prop2|child_prop3 curl -XPUT 'localhost:9200/_river/parent/_meta' -d '{ "type" : "jdbc", "jdbc" : { "driver" : "com.mysql.jdbc.Driver", "url" : "jdbc:mysql://localhost:3306/test", "user" : "", "password" : "", "sql" : "select * from table1", "index" : "index1", "type" : "parent" } }' curl -XPUT 'localhost:9200/_river/child/_meta' -d '{ "type" : "jdbc", "jdbc" : { "driver" : "com.mysql.jdbc.Driver", "url" : "jdbc:mysql://localhost:3306/test", "user" : "", "password" : "", "sql" : "select * from child_table1", "index" : "index1", "type" : "child" } }' curl -XPOST 'localhost:9200/_river/child/…

Accepted Answer

Assumed that your tables look like:

```
table1
table_id| name| prop1|prop2|prop3

child_table1
child_id| table_id| child_prop1|child_prop2|child_prop3
```

You will need to select your primary row id and named it as "_id", your parent id and named it as "_parent"

```
curl -XPUT 'localhost:9200/_river/parent/_meta' -d '{
    "type" : "jdbc",
    "jdbc" : {
        "driver" : "com.mysql.jdbc.Driver",
        "url" : "jdbc:mysql://localhost:3306/test",
        "user" : "",
        "password" : "",
        "sql" : "select table_id as _id, name, prop1, prop2, prop3 from table1",
        "index" : "index1",
        "type" : "parent"
    }
}'

curl -XPUT 'localhost:9200/_river/child/_meta' -d '{
    "type" : "jdbc",
    "jdbc" : {
        "driver" : "com.mysql.jdbc.Driver",
        "url" : "jdbc:mysql://localhost:3306/test",
        "user" : "",
        "password" : "",
        "sql" : "select child_id as _id, table_id as _parent, child_prop1, child_prop2, child_prop3 from child_table1",
        "index" : "index1",
        "type" : "child"
    }
}'
```

And define the mapping parent/child as you did, then it's done. You can use parent/child queries to query the parent/child data now.

`UPDATE:` I already use your newest mapping and create a sample database to import data. Everything work fine, I can index parent/child without any errors. I'm using ES 0.9.5, jdbc-river 2.2.2.

Elasticsearch : set up parent/child using jdbc-rivers

Solution

Problem