Use jdbc river to index data from two different tables in two different database to the same index


(Srinivasan Ramaswamy) #1

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.
To view this discussion on the web visit https://groups.google.com/d/msgid/elasticsearch/4ec15a84-2a90-4d3d-8914-d7267d642e84%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.


(Jörg Prante) #2

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.comwrote:

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.
To view this discussion on the web visit
https://groups.google.com/d/msgid/elasticsearch/4ec15a84-2a90-4d3d-8914-d7267d642e84%40googlegroups.comhttps://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.

--
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.
To view this discussion on the web visit https://groups.google.com/d/msgid/elasticsearch/CAKdsXoGMOkfZ6rSEk%3D2FphPa8FNDcgBZRtrwjipTSgs1ZAy3EA%40mail.gmail.com.
For more options, visit https://groups.google.com/d/optout.


(Srinivasan Ramaswamy) #3

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 <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.comwrote:

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.

To view this discussion on the web visit
https://groups.google.com/d/msgid/elasticsearch/4ec15a84-2a90-4d3d-8914-d7267d642e84%40googlegroups.comhttps://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.

--
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.
To unsubscribe from this group and all its topics, send an email to
elasticsearch+unsubscribe@googlegroups.com.
To view this discussion on the web visit
https://groups.google.com/d/msgid/elasticsearch/CAKdsXoGMOkfZ6rSEk%3D2FphPa8FNDcgBZRtrwjipTSgs1ZAy3EA%40mail.gmail.comhttps://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.

--
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.
To view this discussion on the web visit 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.


(Jörg Prante) #4

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.comwrote:

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 <
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.comwrote:

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.

To view this discussion on the web visit
https://groups.google.com/d/msgid/elasticsearch/4ec15a84-2a90-4d3d-8914-d7267d642e84%40googlegroups.comhttps://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.

--
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.
To unsubscribe from this group and all its topics, send an email to
elasticsearch+unsubscribe@googlegroups.com.
To view this discussion on the web visit
https://groups.google.com/d/msgid/elasticsearch/CAKdsXoGMOkfZ6rSEk%3D2FphPa8FNDcgBZRtrwjipTSgs1ZAy3EA%40mail.gmail.comhttps://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.

--
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.
To view this discussion on the web visit
https://groups.google.com/d/msgid/elasticsearch/CAL1MvVzgaJPkRfMf%2BXCEYGuv%3DGP_4%3DpX4c3hi8CNpjvRDSYxpA%40mail.gmail.comhttps://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.

--
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.
To view this discussion on the web visit 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.


(system) #5