# Use jdbc river to index data from two different tables in two different database to the same index **URL:** \ **Category:** Elasticsearch\ **Created:** [April 8, 2014, 11:46pm UTC](https://discuss.elastic.co/t/use-jdbc-river-to-index-data-from-two-different-tables-in-two-different-database-to-the-same-index/16879 "2014-04-08T23:46:35Z")\ **Posts on this page:** 5\ **Page:** 1
**Author:** ![Srinivasan\_Ramaswamy](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/srinivasan_ramaswamy/32/74106_2.png) [@Srinivasan\_Ramaswamy](https://discuss.elastic.co/u/Srinivasan_Ramaswamy)\ **Post date:** [April 8, 2014, 11:46pm UTC](https://discuss.elastic.co/t/use-jdbc-river-to-index-data-from-two-different-tables-in-two-different-database-to-the-same-index/16879/1 "2014-04-08T23:46:35Z")
I am using elasticsearch to index documents. I have a few tables in one database (dbA) and few other tables in another database (dbB). I have a join query that joins all the relevant tables in dbA curl -XPUT 'localhost:9200/\_river/riverA/\_meta' -d '{ "type" : "jdbc", "jdbc" : { "url" : "jdbc:sqlserver://dbServer:1433;DatabaseName=dbA", "index":"idx", "type":"typA", "user" : "username", "password" : "mypasswd", "sql" : "SELECT t1.key as \_id, t1.val, t2.val from table1 t1 INNER JOIN table2 t2 on t1.key=t2.key" } }' curl -XPUT 'localhost:9200/\_river/riverB/\_meta' -d '{ "type" : "jdbc", "jdbc" : { "url" : "jdbc:sqlserver://dbServer:1433;DatabaseName=dbB", "index":"idx", "type":"typB", "user" : "username", "password" : "mypasswd", "sql" : "SELECT t3.key as \_id, t3.val, t4.val from table3 t3 INNER JOIN table4 t4 on t3.key=t4.key" } }' If I create two different rivers and write to the same index the index content is rewritten with the 2nd river query. Can i have two different queries the output of which is added to the same index and to the same document ? Thanks Srini -- You received this message because you are subscribed to the Google Groups "elasticsearch" group. To unsubscribe from this group and stop receiving emails from it, send an email to [elasticsearch+unsubscribe@googlegroups.com](mailto:elasticsearch+unsubscribe@googlegroups.com). To view this discussion on the web visit [https://groups.google.com/d/msgid/elasticsearch/4ec15a84-2a90-4d3d-8914-d7267d642e84%40googlegroups.com](https://groups.google.com/d/msgid/elasticsearch/4ec15a84-2a90-4d3d-8914-d7267d642e84%40googlegroups.com). For more options, visit [https://groups.google.com/d/optout](https://groups.google.com/d/optout). ---
**Author:** ![jprante](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/jprante/32/44941_2.png) [@jprante](https://discuss.elastic.co/u/jprante)\ **Post date:** [April 9, 2014, 3:14pm UTC](https://discuss.elastic.co/t/use-jdbc-river-to-index-data-from-two-different-tables-in-two-different-database-to-the-same-index/16879/2 "2014-04-09T15:14:55Z")
Can you clarify what you mean by "added to the same index and to the same document"? Maybe you can give an example of what you want to achieve. Jörg On Wed, Apr 9, 2014 at 1:46 AM, Srinivasan Ramaswamy [ursvasan@gmail.com](mailto:ursvasan@gmail.com)wrote: > I am using elasticsearch to index documents. I have a few tables in one > database (dbA) and few other tables in another database (dbB). I have a > join query that joins all the relevant tables in dbA > > curl -XPUT 'localhost:9200/\_river/riverA/\_meta' -d '{ > "type" : "jdbc", > "jdbc" : { > "url" : "jdbc:sqlserver://dbServer:1433;DatabaseName=dbA", > "index":"idx", > "type":"typA", > "user" : "username", > "password" : "mypasswd", > "sql" : "SELECT t1.key as \_id, t1.val, t2.val from table1 t1 INNER > JOIN table2 t2 on t1.key=t2.key" > } > }' > > curl -XPUT 'localhost:9200/\_river/riverB/\_meta' -d '{ > "type" : "jdbc", > "jdbc" : { > "url" : "jdbc:sqlserver://dbServer:1433;DatabaseName=dbB", > "index":"idx", > "type":"typB", > "user" : "username", > "password" : "mypasswd", > "sql" : "SELECT t3.key as \_id, t3.val, t4.val from table3 t3 INNER > JOIN table4 t4 on t3.key=t4.key" > } > }' > > If I create two different rivers and write to the same index the index > content is rewritten with the 2nd river query. Can i have two different > queries the output of which is added to the same index and to the same > document ? > > Thanks > Srini > > -- > You received this message because you are subscribed to the Google Groups > "elasticsearch" group. > To unsubscribe from this group and stop receiving emails from it, send an > email to [elasticsearch+unsubscribe@googlegroups.com](mailto:elasticsearch+unsubscribe@googlegroups.com). > To view this discussion on the web visit > [https://groups.google.com/d/msgid/elasticsearch/4ec15a84-2a90-4d3d-8914-d7267d642e84%40googlegroups.com](https://groups.google.com/d/msgid/elasticsearch/4ec15a84-2a90-4d3d-8914-d7267d642e84%40googlegroups.com)[https://groups.google.com/d/msgid/elasticsearch/4ec15a84-2a90-4d3d-8914-d7267d642e84%40googlegroups.com?utm\_medium=email&utm\_source=footer](https://groups.google.com/d/msgid/elasticsearch/4ec15a84-2a90-4d3d-8914-d7267d642e84%40googlegroups.com?utm_medium=email&utm_source=footer) > . > For more options, visit [https://groups.google.com/d/optout](https://groups.google.com/d/optout). -- You received this message because you are subscribed to the Google Groups "elasticsearch" group. To unsubscribe from this group and stop receiving emails from it, send an email to [elasticsearch+unsubscribe@googlegroups.com](mailto:elasticsearch+unsubscribe@googlegroups.com). To view this discussion on the web visit [https://groups.google.com/d/msgid/elasticsearch/CAKdsXoGMOkfZ6rSEk%3D2FphPa8FNDcgBZRtrwjipTSgs1ZAy3EA%40mail.gmail.com](https://groups.google.com/d/msgid/elasticsearch/CAKdsXoGMOkfZ6rSEk%3D2FphPa8FNDcgBZRtrwjipTSgs1ZAy3EA%40mail.gmail.com). For more options, visit [https://groups.google.com/d/optout](https://groups.google.com/d/optout). ---
**Author:** ![Srinivasan\_Ramaswamy](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/srinivasan_ramaswamy/32/74106_2.png) [@Srinivasan\_Ramaswamy](https://discuss.elastic.co/u/Srinivasan_Ramaswamy)\ **Post date:** [April 9, 2014, 5:56pm UTC](https://discuss.elastic.co/t/use-jdbc-river-to-index-data-from-two-different-tables-in-two-different-database-to-the-same-index/16879/3 "2014-04-09T17:56:26Z")
Sure. I am trying to index a bunch of products (unique product) and each product can have multiple tags (product sold my a merchant). I am planning to add tags a nested document in the index. The product and tags information are stored in productdb database. And there are search\_tags (in the sense of keywords) associated with every product (merchants can tag the products with keywords) which is stored in a different database called tagdb. each document in the index is going to look like product\_id, product\_title, product\_desc, tags: [(tag\_id1, tag\_name, tag\_desc, tag\_price, tag\_seller), (tag\_id\_n, tag\_name\_n, tag\_desc, tag\_price, tag\_seller),.. ], search\_tags There are few more fields which i have omitted here for the sake of clarity and simplicity. Those fields will come from other tables in the database as a result of joins in the sql statement. productdb: product, tag, few other related tables tagdb: search\_tags, few other related tables Please let me know if I can clarify further. Thanks Srini On Wed, Apr 9, 2014 at 8:14 AM, [joergprante@gmail.com](mailto:joergprante@gmail.com) \<[joergprante@gmail.com](mailto:joergprante@gmail.com) > wrote: > Can you clarify what you mean by "added to the same index and to the same > document"? Maybe you can give an example of what you want to achieve. > > Jörg > > On Wed, Apr 9, 2014 at 1:46 AM, Srinivasan Ramaswamy [ursvasan@gmail.com](mailto:ursvasan@gmail.com)wrote: > > > I am using elasticsearch to index documents. I have a few tables in one > > database (dbA) and few other tables in another database (dbB). I have a > > join query that joins all the relevant tables in dbA > > > > curl -XPUT 'localhost:9200/\_river/riverA/\_meta' -d '{ > > "type" : "jdbc", > > "jdbc" : { > > "url" : "jdbc:sqlserver://dbServer:1433;DatabaseName=dbA", > > "index":"idx", > > "type":"typA", > > "user" : "username", > > "password" : "mypasswd", > > "sql" : "SELECT t1.key as \_id, t1.val, t2.val from table1 t1 > > INNER JOIN table2 t2 on t1.key=t2.key" > > } > > }' > > > > curl -XPUT 'localhost:9200/\_river/riverB/\_meta' -d '{ > > "type" : "jdbc", > > "jdbc" : { > > "url" : "jdbc:sqlserver://dbServer:1433;DatabaseName=dbB", > > "index":"idx", > > "type":"typB", > > "user" : "username", > > "password" : "mypasswd", > > "sql" : "SELECT t3.key as \_id, t3.val, t4.val from table3 t3 > > INNER JOIN table4 t4 on t3.key=t4.key" > > } > > }' > > > > If I create two different rivers and write to the same index the index > > content is rewritten with the 2nd river query. Can i have two different > > queries the output of which is added to the same index and to the same > > document ? > > > > Thanks > > Srini > > > > -- > > You received this message because you are subscribed to the Google Groups > > "elasticsearch" group. > > To unsubscribe from this group and stop receiving emails from it, send an > > email to [elasticsearch+unsubscribe@googlegroups.com](mailto:elasticsearch+unsubscribe@googlegroups.com). > > > > To view this discussion on the web visit > > [https://groups.google.com/d/msgid/elasticsearch/4ec15a84-2a90-4d3d-8914-d7267d642e84%40googlegroups.com](https://groups.google.com/d/msgid/elasticsearch/4ec15a84-2a90-4d3d-8914-d7267d642e84%40googlegroups.com)[https://groups.google.com/d/msgid/elasticsearch/4ec15a84-2a90-4d3d-8914-d7267d642e84%40googlegroups.com?utm\_medium=email&utm\_source=footer](https://groups.google.com/d/msgid/elasticsearch/4ec15a84-2a90-4d3d-8914-d7267d642e84%40googlegroups.com?utm_medium=email&utm_source=footer) > > . > > For more options, visit [https://groups.google.com/d/optout](https://groups.google.com/d/optout). > > -- > You received this message because you are subscribed to a topic in the > Google Groups "elasticsearch" group. > To unsubscribe from this topic, visit > [https://groups.google.com/d/topic/elasticsearch/lCM5buPAQAc/unsubscribe](https://groups.google.com/d/topic/elasticsearch/lCM5buPAQAc/unsubscribe). > To unsubscribe from this group and all its topics, send an email to > [elasticsearch+unsubscribe@googlegroups.com](mailto:elasticsearch+unsubscribe@googlegroups.com). > To view this discussion on the web visit > [https://groups.google.com/d/msgid/elasticsearch/CAKdsXoGMOkfZ6rSEk%3D2FphPa8FNDcgBZRtrwjipTSgs1ZAy3EA%40mail.gmail.com](https://groups.google.com/d/msgid/elasticsearch/CAKdsXoGMOkfZ6rSEk%3D2FphPa8FNDcgBZRtrwjipTSgs1ZAy3EA%40mail.gmail.com)[https://groups.google.com/d/msgid/elasticsearch/CAKdsXoGMOkfZ6rSEk%3D2FphPa8FNDcgBZRtrwjipTSgs1ZAy3EA%40mail.gmail.com?utm\_medium=email&utm\_source=footer](https://groups.google.com/d/msgid/elasticsearch/CAKdsXoGMOkfZ6rSEk%3D2FphPa8FNDcgBZRtrwjipTSgs1ZAy3EA%40mail.gmail.com?utm_medium=email&utm_source=footer) > . > > For more options, visit [https://groups.google.com/d/optout](https://groups.google.com/d/optout). -- You received this message because you are subscribed to the Google Groups "elasticsearch" group. To unsubscribe from this group and stop receiving emails from it, send an email to [elasticsearch+unsubscribe@googlegroups.com](mailto:elasticsearch+unsubscribe@googlegroups.com). To view this discussion on the web visit [https://groups.google.com/d/msgid/elasticsearch/CAL1MvVzgaJPkRfMf%2BXCEYGuv%3DGP\_4%3DpX4c3hi8CNpjvRDSYxpA%40mail.gmail.com](https://groups.google.com/d/msgid/elasticsearch/CAL1MvVzgaJPkRfMf%2BXCEYGuv%3DGP_4%3DpX4c3hi8CNpjvRDSYxpA%40mail.gmail.com). For more options, visit [https://groups.google.com/d/optout](https://groups.google.com/d/optout). ---
**Author:** ![jprante](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/jprante/32/44941_2.png) [@jprante](https://discuss.elastic.co/u/jprante)\ **Post date:** [April 11, 2014, 11:11am UTC](https://discuss.elastic.co/t/use-jdbc-river-to-index-data-from-two-different-tables-in-two-different-database-to-the-same-index/16879/4 "2014-04-11T11:11:45Z")
Yes, you have to specify a single SQL join on the tables productdb and tagdb and then you can index the result by JDBC river. Jörg On Wed, Apr 9, 2014 at 7:56 PM, Srinivasan Ramaswamy [ursvasan@gmail.com](mailto:ursvasan@gmail.com)wrote: > Sure. I am trying to index a bunch of products (unique product) and each > product can have multiple tags (product sold my a merchant). I am planning > to add tags a nested document in the index. The product and tags > information are stored in productdb database. And there are search\_tags (in > the sense of keywords) associated with every product (merchants can tag the > products with keywords) which is stored in a different database called > tagdb. > > each document in the index is going to look like > > product\_id, product\_title, product\_desc, tags: [(tag\_id1, tag\_name, > tag\_desc, tag\_price, tag\_seller), (tag\_id\_n, tag\_name\_n, tag\_desc, > tag\_price, tag\_seller),.. ], search\_tags > > There are few more fields which i have omitted here for the sake of > clarity and simplicity. Those fields will come from other tables in the > database as a result of joins in the sql statement. > > productdb: > product, tag, few other related tables > tagdb: > search\_tags, few other related tables > > Please let me know if I can clarify further. > > Thanks > Srini > > On Wed, Apr 9, 2014 at 8:14 AM, [joergprante@gmail.com](mailto:joergprante@gmail.com) \< > [joergprante@gmail.com](mailto:joergprante@gmail.com)\> wrote: > > > Can you clarify what you mean by "added to the same index and to the same > > document"? Maybe you can give an example of what you want to achieve. > > > > Jörg > > > > On Wed, Apr 9, 2014 at 1:46 AM, Srinivasan Ramaswamy [ursvasan@gmail.com](mailto:ursvasan@gmail.com)wrote: > > > > > I am using elasticsearch to index documents. I have a few tables in one > > > database (dbA) and few other tables in another database (dbB). I have a > > > join query that joins all the relevant tables in dbA > > > > > > curl -XPUT 'localhost:9200/\_river/riverA/\_meta' -d '{ > > > "type" : "jdbc", > > > "jdbc" : { > > > "url" : "jdbc:sqlserver://dbServer:1433;DatabaseName=dbA", > > > "index":"idx", > > > "type":"typA", > > > "user" : "username", > > > "password" : "mypasswd", > > > "sql" : "SELECT t1.key as \_id, t1.val, t2.val from table1 t1 > > > INNER JOIN table2 t2 on t1.key=t2.key" > > > } > > > }' > > > > > > curl -XPUT 'localhost:9200/\_river/riverB/\_meta' -d '{ > > > "type" : "jdbc", > > > "jdbc" : { > > > "url" : "jdbc:sqlserver://dbServer:1433;DatabaseName=dbB", > > > "index":"idx", > > > "type":"typB", > > > "user" : "username", > > > "password" : "mypasswd", > > > "sql" : "SELECT t3.key as \_id, t3.val, t4.val from table3 t3 > > > INNER JOIN table4 t4 on t3.key=t4.key" > > > } > > > }' > > > > > > If I create two different rivers and write to the same index the index > > > content is rewritten with the 2nd river query. Can i have two different > > > queries the output of which is added to the same index and to the same > > > document ? > > > > > > Thanks > > > Srini > > > > > > -- > > > You received this message because you are subscribed to the Google > > > Groups "elasticsearch" group. > > > To unsubscribe from this group and stop receiving emails from it, send > > > an email to [elasticsearch+unsubscribe@googlegroups.com](mailto:elasticsearch+unsubscribe@googlegroups.com). > > > > > > To view this discussion on the web visit > > > [https://groups.google.com/d/msgid/elasticsearch/4ec15a84-2a90-4d3d-8914-d7267d642e84%40googlegroups.com](https://groups.google.com/d/msgid/elasticsearch/4ec15a84-2a90-4d3d-8914-d7267d642e84%40googlegroups.com)[https://groups.google.com/d/msgid/elasticsearch/4ec15a84-2a90-4d3d-8914-d7267d642e84%40googlegroups.com?utm\_medium=email&utm\_source=footer](https://groups.google.com/d/msgid/elasticsearch/4ec15a84-2a90-4d3d-8914-d7267d642e84%40googlegroups.com?utm_medium=email&utm_source=footer) > > > . > > > For more options, visit [https://groups.google.com/d/optout](https://groups.google.com/d/optout). > > > > -- > > You received this message because you are subscribed to a topic in the > > Google Groups "elasticsearch" group. > > To unsubscribe from this topic, visit > > [https://groups.google.com/d/topic/elasticsearch/lCM5buPAQAc/unsubscribe](https://groups.google.com/d/topic/elasticsearch/lCM5buPAQAc/unsubscribe). > > To unsubscribe from this group and all its topics, send an email to > > [elasticsearch+unsubscribe@googlegroups.com](mailto:elasticsearch+unsubscribe@googlegroups.com). > > To view this discussion on the web visit > > [https://groups.google.com/d/msgid/elasticsearch/CAKdsXoGMOkfZ6rSEk%3D2FphPa8FNDcgBZRtrwjipTSgs1ZAy3EA%40mail.gmail.com](https://groups.google.com/d/msgid/elasticsearch/CAKdsXoGMOkfZ6rSEk%3D2FphPa8FNDcgBZRtrwjipTSgs1ZAy3EA%40mail.gmail.com)[https://groups.google.com/d/msgid/elasticsearch/CAKdsXoGMOkfZ6rSEk%3D2FphPa8FNDcgBZRtrwjipTSgs1ZAy3EA%40mail.gmail.com?utm\_medium=email&utm\_source=footer](https://groups.google.com/d/msgid/elasticsearch/CAKdsXoGMOkfZ6rSEk%3D2FphPa8FNDcgBZRtrwjipTSgs1ZAy3EA%40mail.gmail.com?utm_medium=email&utm_source=footer) > > . > > > > For more options, visit [https://groups.google.com/d/optout](https://groups.google.com/d/optout). > > -- > You received this message because you are subscribed to the Google Groups > "elasticsearch" group. > To unsubscribe from this group and stop receiving emails from it, send an > email to [elasticsearch+unsubscribe@googlegroups.com](mailto:elasticsearch+unsubscribe@googlegroups.com). > To view this discussion on the web visit > [https://groups.google.com/d/msgid/elasticsearch/CAL1MvVzgaJPkRfMf%2BXCEYGuv%3DGP\_4%3DpX4c3hi8CNpjvRDSYxpA%40mail.gmail.com](https://groups.google.com/d/msgid/elasticsearch/CAL1MvVzgaJPkRfMf%2BXCEYGuv%3DGP_4%3DpX4c3hi8CNpjvRDSYxpA%40mail.gmail.com)[https://groups.google.com/d/msgid/elasticsearch/CAL1MvVzgaJPkRfMf%2BXCEYGuv%3DGP\_4%3DpX4c3hi8CNpjvRDSYxpA%40mail.gmail.com?utm\_medium=email&utm\_source=footer](https://groups.google.com/d/msgid/elasticsearch/CAL1MvVzgaJPkRfMf%2BXCEYGuv%3DGP_4%3DpX4c3hi8CNpjvRDSYxpA%40mail.gmail.com?utm_medium=email&utm_source=footer) > . > > For more options, visit [https://groups.google.com/d/optout](https://groups.google.com/d/optout). -- You received this message because you are subscribed to the Google Groups "elasticsearch" group. To unsubscribe from this group and stop receiving emails from it, send an email to [elasticsearch+unsubscribe@googlegroups.com](mailto:elasticsearch+unsubscribe@googlegroups.com). To view this discussion on the web visit [https://groups.google.com/d/msgid/elasticsearch/CAKdsXoEE%2Bh6u2jtQDe\_6-TWjOjF%3DOVj1\_JfgTjVX1gpoPMEW3Q%40mail.gmail.com](https://groups.google.com/d/msgid/elasticsearch/CAKdsXoEE%2Bh6u2jtQDe_6-TWjOjF%3DOVj1_JfgTjVX1gpoPMEW3Q%40mail.gmail.com). For more options, visit [https://groups.google.com/d/optout](https://groups.google.com/d/optout). ---
**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\ **Post date:** [July 6, 2017, 1:36am UTC](https://discuss.elastic.co/t/use-jdbc-river-to-index-data-from-two-different-tables-in-two-different-database-to-the-same-index/16879/5 "2017-07-06T01:36:33Z")