# \[ANN\] JDBC river for Elasticsearch 2.0.0 **URL:** \ **Category:** Elasticsearch\ **Created:** [January 17, 2013, 11:30pm UTC](https://discuss.elastic.co/t/ann-jdbc-river-for-elasticsearch-2-0-0/10402 "2013-01-17T23:30:16Z")\ **Posts on this page:** 10\ **Page:** 1
**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:** [January 17, 2013, 11:30pm UTC](https://discuss.elastic.co/t/ann-jdbc-river-for-elasticsearch-2-0-0/10402/1 "2013-01-17T23:30:16Z")
Hey, JDBC river plugin for Elasticsearch 2.0.0 is out. > **[jprante/elasticsearch-jdbc](https://github.com/jprante/elasticsearch-jdbc)** > > elasticsearch-jdbc - JDBC importer for Elasticsearch Glimpsing the features: - for Elasticsearch 0.20+ - more stable, tested against Derby, H2, HSQLDB, MySQL, PostgreSQL - example code and SQL in the testng tests - new conceptual model: "simple" strategy, "oneshot" strategy, "table" strategy - strategy consists of three modules for better reuse and extensibility (also in other rivers): RiverSource, RiverMouth, RiverFlow - new bulk indexing, showcase of org.elasticsearch.action.bulk.BulkProcessor - many bug fixes - binaries moved to bintray [https://bintray.com/pkg/show/general/jprante/elasticsearch-plugins/elasticsearch-river-jdbc](https://bintray.com/pkg/show/general/jprante/elasticsearch-plugins/elasticsearch-river-jdbc) Documentation is being reorganized on the github wiki, more to come! Cheers, Jörg -- ---
**Author:** ![Gabriel\_Mirea](https://avatars.discourse-cdn.com/v4/letter/g/ee59a6/32.png) [@Gabriel\_Mirea](https://discuss.elastic.co/u/Gabriel_Mirea)\ **Post date:** [January 25, 2013, 12:45pm UTC](https://discuss.elastic.co/t/ann-jdbc-river-for-elasticsearch-2-0-0/10402/2 "2013-01-25T12:45:25Z")
Hi Jörg, 1)I' m using a Oracle XE server on a win7 x64, where I've done: 1.1)unzip elasticsearch-0.20.2.zip 1.2)copy ojdbc14.jar to %ES\_HOME%/lib/ojdbc14.jar 1.3)./bin/plugin -url [http://bit.ly/U75w1N](http://bit.ly/U75w1N) -install river-jdbc 1. Within oracle squirrel client: 2.1)Create Table ORDERS( ORDER\_ID Number(7) Primary Key, description VARCHAR2(100)); Create Table OPTIONS( OPTION\_ID Number(7) Primary Key, order\_id Number(7), option\_name VARCHAR2(50), CONSTRAINT fk\_order FOREIGN KEY (order\_id) REFERENCES ORDERS(order\_id)) INSERT INTO ORDERS(ORDER\_ID, description) values(1, '1st order'); INSERT INTO OPTIONS(OPTION\_ID, ORDER\_ID, option\_name) values(1, 1, '1st order option1'); INSERT INTO OPTIONS(OPTION\_ID, ORDER\_ID, option\_name) values(2, 1, '1st order option2'); 2.2)sqplus-\> SQL\> select \* from orders; ## ORDER\_ID DESCRIPTION * * * ``` 1 1st order ``` SQL\> select \* from options; OPTION\_ID ORDER\_ID OPTION\_NAME * * * ``` 1 1 1st order option1 2 1 1st order option2 ``` SQL\> select 'order' as "\_index", ord.order\_id as "\_id",ord.order\_id as "order.oId",option\_name as "order.options" from orders ord inner join options opt on ord.order\_id = opt.order\_id; \_index \_id order.oId order.options * * * * * * order 1 1 1st order option1 order 1 1 1st order option2 1. On a ubuntu VM box, hosted on the same win7 x64, I executed: ubuntu@ubuntu-VirtualBox:~$ uname -a Linux ubuntu-VirtualBox 3.2.0-24-generic-pae #37-Ubuntu SMP Wed Apr 25 10:47:59 UTC 2012 i686 i686 i386 GNU/Linux ubuntu@ubuntu-VirtualBox:~$ curl -XPUT '192.168.56.1:9200/\_river/my\_jdbc\_river/\_meta' -d '{ "type" : "jdbc", "jdbc" : { "driver" : "oracle.jdbc.OracleDriver", "url" : "jdbc:oracle:thin:@localhost:1521/XE", "user" : "", "password" : "", "poll" : "10s", "sql" : "select \u0027order\u0027 as "\_index", ord.order\_id as "\_id",ord.order\_id as "order.oId",option\_name as "order.option" from orders ord inner join options opt on ord.order\_id = opt.order\_id" } }' 4)I'm searching for: ubuntu@ubuntu-VirtualBox:~$ curl -XGET '192.168.56.1:9200/order/jdbc/\_search?pretty' { "took" : 2, "timed\_out" : false, "\_shards" : { "total" : 5, "successful" : 5, "failed" : 0 }, "hits" : { "total" : 1, "max\_score" : 1.0, "hits" : [ { "\_index" : "order", "\_type" : "jdbc", "\_id" : "1", "\_score" : 1.0, "\_source" : {"order":{"oId":1,"option":"1st order option2"},"\_id":1,"\_index":"order"} } ] } Obviously is fetching only the 2nd record from the one-to-many relation, the "1st order option1" is missing...where I've done wrong? 5)But I would like to have the following result: "\_score" : 1.0, "\_source" : {"order":{"oId":1,"options":["1st order option1","1st order option2"]},"\_id":1,"\_index":"order"} that should be an equivalent result from your example: index=relations id=Good {"contact":{"employee":["Müller","Meier","Schulze"],"customer":"Good"}} Can you please help me?with any hint? Thanks gabriel -- ---
**Author:** ![dadoonet](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/dadoonet/32/137187_2.png) [@dadoonet](https://discuss.elastic.co/u/dadoonet)\ **Post date:** [January 25, 2013, 4:05pm UTC](https://discuss.elastic.co/t/ann-jdbc-river-for-elasticsearch-2-0-0/10402/3 "2013-01-25T16:05:26Z")
If \_id is the ID of your doc in Elasticsearch, your second record is erasing the first one as they have the same \_id. Le 25 janv. 2013 à 13:45, Gabriel Mirea [gmirea@sqli.com](mailto:gmirea@sqli.com) a écrit : > Hi Jörg, > > 1)I' m using a Oracle XE server on a win7 x64, where I've done: > 1.1)unzip elasticsearch-0.20.2.zip > 1.2)copy ojdbc14.jar to %ES\_HOME%/lib/ojdbc14.jar > 1.3)./bin/plugin -url [http://bit.ly/U75w1N](http://bit.ly/U75w1N) -install river-jdbc > > 1. Within oracle squirrel client: > 2.1)Create Table ORDERS( > ORDER\_ID Number(7) Primary Key, > description VARCHAR2(100)); > > Create Table OPTIONS( > OPTION\_ID Number(7) Primary Key, > order\_id Number(7), > option\_name VARCHAR2(50), > CONSTRAINT fk\_order > FOREIGN KEY (order\_id) > REFERENCES ORDERS(order\_id)) > > INSERT INTO ORDERS(ORDER\_ID, description) values(1, '1st order'); > > INSERT INTO OPTIONS(OPTION\_ID, ORDER\_ID, option\_name) values(1, 1, '1st order option1'); > INSERT INTO OPTIONS(OPTION\_ID, ORDER\_ID, option\_name) values(2, 1, '1st order option2'); > > 2.2)sqplus-\> > > SQL\> select \* from orders; > > ORDER\_ID DESCRIPTION > > * * * > > ``` > 1 1st order > > ``` > > SQL\> select \* from options; > > OPTION\_ID ORDER\_ID OPTION\_NAME > > * * * > > ``` > 1 1 1st order option1 > 2 1 1st order option2 > > ``` > > SQL\> select 'order' as "\_index", ord.order\_id as "\_id",ord.order\_id as "order.oId",option\_name as "order.options" from orders ord inner join > options opt on ord.order\_id = opt.order\_id; > > \_index \_id order.oId order.options > > * * * > > order 1 1 1st order option1 > order 1 1 1st order option2 > > 1. On a ubuntu VM box, hosted on the same win7 x64, I executed: > ubuntu@ubuntu-VirtualBox:~$ uname -a > Linux ubuntu-VirtualBox 3.2.0-24-generic-pae #37-Ubuntu SMP Wed Apr 25 10:47:59 UTC 2012 i686 i686 i386 GNU/Linux > > ubuntu@ubuntu-VirtualBox:~$ curl -XPUT '192.168.56.1:9200/\_river/my\_jdbc\_river/\_meta' -d '{ > "type" : "jdbc", > "jdbc" : { > "driver" : "oracle.jdbc.OracleDriver", > "url" : "jdbc:oracle:thin:@localhost:1521/XE", > "user" : "", > "password" : "", > "poll" : "10s", > "sql" : "select \u0027order\u0027 as "\_index", ord.order\_id as "\_id",ord.order\_id as "order.oId",option\_name as "order.option" from orders ord inner join > > options opt on ord.order\_id = opt.order\_id" > } > }' > > 4)I'm searching for: > ubuntu@ubuntu-VirtualBox:~$ curl -XGET '192.168.56.1:9200/order/jdbc/\_search?pretty' > { > "took" : 2, > "timed\_out" : false, > "\_shards" : { > "total" : 5, > "successful" : 5, > "failed" : 0 > }, > "hits" : { > "total" : 1, > "max\_score" : 1.0, > "hits" : [ { > "\_index" : "order", > "\_type" : "jdbc", > "\_id" : "1", > "\_score" : 1.0, "\_source" : {"order":{"oId":1,"option":"1st order option2"},"\_id":1,"\_index":"order"} > } ] > } > > Obviously is fetching only the 2nd record from the one-to-many relation, the "1st order option1" is missing...where I've done wrong? > > 5)But I would like to have the following result: > > "\_score" : 1.0, "\_source" : {"order":{"oId":1,"options":["1st order option1","1st order option2"]},"\_id":1,"\_index":"order"} > > that should be an equivalent result from your example: > > index=relations id=Good {"contact":{"employee":["Müller","Meier","Schulze"],"customer":"Good"}} > > Can you please help me?with any hint? > Thanks > gabriel > > -- -- ---
**Author:** ![Umit\_Seren](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/umit_seren/32/44933_2.png) [@Umit\_Seren](https://discuss.elastic.co/u/Umit_Seren)\ **Post date:** [January 26, 2013, 10:50am UTC](https://discuss.elastic.co/t/ann-jdbc-river-for-elasticsearch-2-0-0/10402/4 "2013-01-26T10:50:45Z")
Great thanks for the plugin. Just a small question. Is there a way how to craete parent-child entries with the JDBC River plugin? At least I wasn't able to come up with a solution with the previous version (haven't really looked into the new version tough). On Friday, January 18, 2013 12:30:16 AM UTC+1, Jörg Prante wrote: > Hey, > > JDBC river plugin for Elasticsearch 2.0.0 is out. > > [GitHub - jprante/elasticsearch-jdbc: JDBC importer for Elasticsearch](https://github.com/jprante/elasticsearch-river-jdbc) > > Glimpsing the features: > > - for Elasticsearch 0.20+ > - more stable, tested against Derby, H2, HSQLDB, MySQL, PostgreSQL > - example code and SQL in the testng tests > - new conceptual model: "simple" strategy, "oneshot" strategy, "table" > strategy > - strategy consists of three modules for better reuse and extensibility > (also in other rivers): RiverSource, RiverMouth, RiverFlow > - new bulk indexing, showcase of > org.elasticsearch.action.bulk.BulkProcessor > - many bug fixes > - binaries moved to bintray > > [Service End for Bintray, JCenter, GoCenter, and ChartCenter | JFrog](https://bintray.com/pkg/show/general/jprante/elasticsearch-plugins/elasticsearch-river-jdbc) > > Documentation is being reorganized on the github wiki, more to come! > > Cheers, > > Jörg -- ---
**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:** [January 26, 2013, 12:02pm UTC](https://discuss.elastic.co/t/ann-jdbc-river-for-elasticsearch-2-0-0/10402/5 "2013-01-26T12:02:36Z")
Yes, you can specify a special column label beginning with a prefix underscore '\_parent' for creating children documents. > **[Labeled columns](https://github.com/jprante/elasticsearch-jdbc/wiki/Labeled-columns)** > > JDBC importer for Elasticsearch. Contribute to jprante/elasticsearch-jdbc development by creating an account on GitHub. The \_parent column value corresponds to the bulk document parameter \_parent > **[Elasticsearch Platform — Find real-time answers at scale](https://www.elastic.co)** > > Power insights and outcomes with the Elasticsearch Platform and AI. See into your data and find answers that matter with enterprise solutions designed to help you build, observe, and protect. Try Elasticsearch free today. I just pushed a fix for missing \_timestamp handling in the JDBC river. Best regards, Jörg Am 26.01.13 11:50, schrieb Ümit Seren: > Great thanks for the plugin. > Just a small question. Is there a way how to craete parent-child > entries with the JDBC River plugin? At least I wasn't able to come up > with a solution with the previous version (haven't really looked into > the new version tough). -- ---
**Author:** ![Gabriel\_Mirea](https://avatars.discourse-cdn.com/v4/letter/g/ee59a6/32.png) [@Gabriel\_Mirea](https://discuss.elastic.co/u/Gabriel_Mirea)\ **Post date:** [January 27, 2013, 9:53pm UTC](https://discuss.elastic.co/t/ann-jdbc-river-for-elasticsearch-2-0-0/10402/6 "2013-01-27T21:53:57Z")
hmm...conceptually, I understand that records erasing is happening due to \_id overwriting... can you please provide an hint/example how to avoid it? many thanks, gabriel Le vendredi 25 janvier 2013 18:05:26 UTC+2, David Pilato a écrit : > If \_id is the ID of your doc in Elasticsearch, your second record is > erasing the first one as they have the same \_id. > > Le 25 janv. 2013 à 13:45, Gabriel Mirea \<[gmi...@sqli.com](mailto:gmi...@sqli.com) \\> a > écrit : > > > Hi Jörg, > > > > 1)I' m using a Oracle XE server on a win7 x64, where I've done: > > 1.1)unzip elasticsearch-0.20.2.zip > > 1.2)copy ojdbc14.jar to %ES\_HOME%/lib/ojdbc14.jar > > 1.3)./bin/plugin -url [http://bit.ly/U75w1N](http://bit.ly/U75w1N) -install river-jdbc > > > > 1. Within oracle squirrel client: > > 2.1)Create Table ORDERS( > > ORDER\_ID Number(7) Primary Key, > > description VARCHAR2(100)); > > > > Create Table OPTIONS( > > OPTION\_ID Number(7) Primary Key, > > order\_id Number(7), > > option\_name VARCHAR2(50), > > CONSTRAINT fk\_order > > FOREIGN KEY (order\_id) > > REFERENCES ORDERS(order\_id)) > > > > INSERT INTO ORDERS(ORDER\_ID, description) values(1, '1st order'); > > > > INSERT INTO OPTIONS(OPTION\_ID, ORDER\_ID, option\_name) values(1, 1, '1st > > order option1'); > > INSERT INTO OPTIONS(OPTION\_ID, ORDER\_ID, option\_name) values(2, 1, '1st > > order option2'); > > > > 2.2)sqplus-\> > > > > SQL\> select \* from orders; > > > > ## ORDER\_ID DESCRIPTION > > * * * > > > ``` > > 1 1st order > > > > ``` > > > > SQL\> select \* from options; > > > > OPTION\_ID ORDER\_ID OPTION\_NAME > > > > * * * > > > > ``` > > 1 1 1st order option1 > > 2 1 1st order option2 > > > > ``` > > > > SQL\> select 'order' as "\_index", ord.order\_id as "\_id",ord.order\_id as > > "order.oId",option\_name as "order.options" from orders ord inner join > > options opt on ord.order\_id = opt.order\_id; > > > > \_index \_id order.oId order.options > > > > * * * > > * * * > > > order 1 1 1st order option1 > > order 1 1 1st order option2 > > > > 1. On a ubuntu VM box, hosted on the same win7 x64, I executed: > > ubuntu@ubuntu-VirtualBox:~$ uname -a > > Linux ubuntu-VirtualBox 3.2.0-24-generic-pae #37-Ubuntu SMP Wed Apr 25 > > 10:47:59 UTC 2012 i686 i686 i386 GNU/Linux > > > > ubuntu@ubuntu-VirtualBox:~$ curl -XPUT ' > > 192.168.56.1:9200/\_river/my\_jdbc\_river/\_meta' -d '{ > > "type" : "jdbc", > > "jdbc" : { > > "driver" : "oracle.jdbc.OracleDriver", > > "url" : "jdbc:oracle:thin:@localhost:1521/XE", > > "user" : "", > > "password" : "", > > "poll" : "10s", > > "sql" : "select \u0027order\u0027 as "\_index", ord.order\_id as > > "\_id",ord.order\_id as "order.oId",option\_name as "order.option" from > > orders ord inner join > > > > options opt on ord.order\_id = opt.order\_id" > > } > > }' > > > > 4)I'm searching for: > > ubuntu@ubuntu-VirtualBox:~$ curl -XGET ' > > 192.168.56.1:9200/order/jdbc/\_search?pretty' > > { > > "took" : 2, > > "timed\_out" : false, > > "\_shards" : { > > "total" : 5, > > "successful" : 5, > > "failed" : 0 > > }, > > "hits" : { > > "total" : 1, > > "max\_score" : 1.0, > > "hits" : [ { > > "\_index" : "order", > > "\_type" : "jdbc", > > "\_id" : "1", > > "\_score" : 1.0, "\_source" : {"order":{"oId":1,"option":"1st order > > option2"},"\_id":1,"\_index":"order"} > > } ] > > } > > > > Obviously is fetching only the 2nd record from the one-to-many relation, > > the "1st order option1" is missing...where I've done wrong? > > > > 5)But I would like to have the following result: > > > > "\_score" : 1.0, "\_source" : {"order":{"oId":1,"options":["1st order > > option1","1st order option2"]},"\_id":1,"\_index":"order"} > > > > that should be an equivalent result from your example: > > > > index=relations id=Good > > {"contact":{"employee":["Müller","Meier","Schulze"],"customer":"Good"}} > > > > Can you please help me?with any hint? > > Thanks > > gabriel > > > > -- -- ---
**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:** [January 28, 2013, 8:41am UTC](https://discuss.elastic.co/t/ann-jdbc-river-for-elasticsearch-2-0-0/10402/7 "2013-01-28T08:41:30Z")
Hi Gabriel, Thanks for your example. It should work. I have to check what is going wrong and opened an issue for it. > > > 1)I' m using a Oracle XE server on a win7 x64, where I've done: > 1.1)unzip elasti…csearch-0.20.2.zip > 1.2)copy ojdbc14.jar to %ES\_HOME%/lib/ojdbc14.jar > 1.3)./bin/plugin -url http://bit.ly/U75w1N -install river-jdbc > > 2) Within oracle squirrel client: > 2.1)Create Table ORDERS( > ORDER\_ID Number(7) Primary Key, > description VARCHAR2(100)); > > Create Table OPTIONS( > OPTION\_ID Number(7) Primary Key, > order\_id Number(7), > option\_name VARCHAR2(50), > CONSTRAINT fk\_order > FOREIGN KEY (order\_id) > REFERENCES ORDERS(order\_id)) > > INSERT INTO ORDERS(ORDER\_ID, description) values(1, '1st order'); > > INSERT INTO OPTIONS(OPTION\_ID, ORDER\_ID, option\_name) values(1, 1, '1st order option1'); > INSERT INTO OPTIONS(OPTION\_ID, ORDER\_ID, option\_name) values(2, 1, '1st order option2'); > > 2.2)sqplus-\> > > SQL\> select \\\* from orders; > > ORDER\_ID DESCRIPTION > > \--- > > \`\`\` > 1 1st order > \`\`\` > > SQL\> select \\\* from options; > > OPTION\_ID ORDER\_ID OPTION\_NAME > > \--- > > \`\`\` > 1 1 1st order option1 > 2 1 1st order option2 > \`\`\` > > SQL\> select 'order' as "\_index", ord.order\_id as "\_id",ord.order\_id as "order.oId",option\_name as "order.options" from orders ord inner join > options opt on ord.order\_id = opt.order\_id; > > \_index \_id order.oId order.options > > \--- > > order 1 1 1st order option1 > order 1 1 1st order option2 > > 3) On a ubuntu VM box, hosted on the same win7 x64, I executed: > ubuntu@ubuntu-VirtualBox:~$ uname -a > Linux ubuntu-VirtualBox 3.2.0-24-generic-pae #37-Ubuntu SMP Wed Apr 25 10:47:59 UTC 2012 i686 i686 i386 GNU/Linux > > ubuntu@ubuntu-VirtualBox:~$ curl -XPUT '192.168.56.1:9200/\_river/my\_jdbc\_river/\_meta' -d '{ > "type" : "jdbc", > "jdbc" : { > "driver" : "oracle.jdbc.OracleDriver", > "url" : "jdbc:oracle:thin:@localhost:1521/XE", > "user" : "", > "password" : "", > "poll" : "10s", > "sql" : "select \\u0027order\\u0027 as \\"\_index\\", ord.order\_id as \\"\_id\\",ord.order\_id as \\"order.oId\\",option\_name as \\"order.option\\" from orders ord inner join > > options opt on ord.order\_id = opt.order\_id" > } > }' > > 4)I'm searching for: > ubuntu@ubuntu-VirtualBox:~$ curl -XGET '192.168.56.1:9200/order/jdbc/\_search?pretty' > { > "took" : 2, > "timed\_out" : false, > "\_shards" : { > "total" : 5, > "successful" : 5, > "failed" : 0 > }, > "hits" : { > "total" : 1, > "max\_score" : 1.0, > "hits" : \[ { > "\_index" : "order", > "\_type" : "jdbc", > "\_id" : "1", > "\_score" : 1.0, "\_source" : {"order":{"oId":1,"option":"1st order option2"},"\_id":1,"\_index":"order"} > } \] > } > > Obviously is fetching only the 2nd record from the one-to-many relation, the "1st order option1" is missing...where I've done wrong? > > 5)But I would like to have the following result: > > "\_score" : 1.0, "\_source" : {"order":{"oId":1,"options":\["1st order option1","1st order option2"\]},"\_id":1,"\_index":"order"} > > that should be an equivalent result from your example: > > index=relations id=Good {"contact":{"employee":\["Müller","Meier","Schulze"\],"customer":"Good"}} > > Can you please help me?with any hint? > Thanks > gabriel Best reagrds, Jörg Am 27.01.13 22:53, schrieb Gabriel Mirea: > hmm...conceptually, I understand that records erasing is happening > due to \_id overwriting... can you please provide an hint/example how > to avoid it? > many thanks, > gabriel > > Le vendredi 25 janvier 2013 18:05:26 UTC+2, David Pilato a écrit : > > ``` > If _id is the ID of your doc in Elasticsearch, your second record > is erasing the first one as they have the same _id. > > Le 25 janv. 2013 à 13:45, Gabriel Mirea > a écrit : > > > Hi Jörg, > > > > 1)I' m using a Oracle XE server on a win7 x64, where I've done: > > 1.1)unzip elasticsearch-0.20.2.zip > > 1.2)copy ojdbc14.jar to %ES_HOME%/lib/ojdbc14.jar > > 1.3)./bin/plugin -url http://bit.ly/U75w1N -install river-jdbc > > > > 2) Within oracle squirrel client: > > 2.1)Create Table ORDERS( > > ORDER_ID Number(7) Primary Key, > > description VARCHAR2(100)); > > > > Create Table OPTIONS( > > OPTION_ID Number(7) Primary Key, > > order_id Number(7), > > option_name VARCHAR2(50), > > CONSTRAINT fk_order > > FOREIGN KEY (order_id) > > REFERENCES ORDERS(order_id)) > > > > INSERT INTO ORDERS(ORDER_ID, description) values(1, '1st order'); > > > > INSERT INTO OPTIONS(OPTION_ID, ORDER_ID, option_name) values(1, > 1, '1st order option1'); > > INSERT INTO OPTIONS(OPTION_ID, ORDER_ID, option_name) values(2, > 1, '1st order option2'); > > > > 2.2)sqplus-> > > > > > > SQL> select * from orders; > > > > ORDER_ID DESCRIPTION > > ---------- > -------------------------------------------------------------------------------- > > > 1 1st order > > > > SQL> select * from options; > > > > OPTION_ID ORDER_ID OPTION_NAME > > ---------- ---------- > -------------------------------------------------- > > 1 1 1st order option1 > > 2 1 1st order option2 > > > > SQL> select 'order' as "_index", ord.order_id as > "_id",ord.order_id as "order.oId",option_name as "order.options" > from orders ord inner join > > options opt on ord.order_id = opt.order_id; > > > > _index _id order.oId order.options > > ------ ---------- ---------- > -------------------------------------------------- > > order 1 1 1st order option1 > > order 1 1 1st order option2 > > > > > > 3) On a ubuntu VM box, hosted on the same win7 x64, I executed: > > ubuntu@ubuntu-VirtualBox:~$ uname -a > > Linux ubuntu-VirtualBox 3.2.0-24-generic-pae #37-Ubuntu SMP Wed > Apr 25 10:47:59 UTC 2012 i686 i686 i386 GNU/Linux > > > > ubuntu@ubuntu-VirtualBox:~$ curl -XPUT > '192.168.56.1:9200/_river/my_jdbc_river/_meta > ' -d '{ > > "type" : "jdbc", > > "jdbc" : { > > "driver" : "oracle.jdbc.OracleDriver", > > "url" : "jdbc:oracle:thin:@localhost:1521/XE", > > "user" : "", > > "password" : "", > > "poll" : "10s", > > "sql" : "select \u0027order\u0027 as \"_index\", > ord.order_id as \"_id\",ord.order_id as \"order.oId\",option_name > as \"order.option\" from orders ord inner join > > > > options opt on ord.order_id = opt.order_id" > > } > > }' > > > > 4)I'm searching for: > > ubuntu@ubuntu-VirtualBox:~$ curl -XGET > '192.168.56.1:9200/order/jdbc/_search?pretty > ' > > { > > "took" : 2, > > "timed_out" : false, > > "_shards" : { > > "total" : 5, > > "successful" : 5, > > "failed" : 0 > > }, > > "hits" : { > > "total" : 1, > > "max_score" : 1.0, > > "hits" : [ { > > "_index" : "order", > > "_type" : "jdbc", > > "_id" : "1", > > "_score" : 1.0, "_source" : > {"order":{"oId":1,"option":"1st order > option2"},"_id":1,"_index":"order"} > > } ] > > } > > > > Obviously is fetching only the 2nd record from the one-to-many > relation, the "1st order option1" is missing...where I've done wrong? > > > > 5)But I would like to have the following result: > > > > "_score" : 1.0, "_source" : {"order":{"oId":1,"options":["1st > order option1","1st order option2"]},"_id":1,"_index":"order"} > > > > that should be an equivalent result from your example: > > > > > > index=relations id=Good > {"contact":{"employee":["Müller","Meier","Schulze"],"customer":"Good"}} > > > > > Can you please help me?with any hint? > > Thanks > > gabriel > > > > -- > > > > > > ``` > > -- ---
**Author:** ![carlozlopes](https://avatars.discourse-cdn.com/v4/letter/c/eada6e/32.png) [@carlozlopes](https://discuss.elastic.co/u/carlozlopes)\ **Post date:** [March 6, 2013, 3:38pm UTC](https://discuss.elastic.co/t/ann-jdbc-river-for-elasticsearch-2-0-0/10402/8 "2013-03-06T15:38:46Z")
Hi everybody, I'm a newbie in elasticsearch and I'm trying to make a mapping between some mysql tables and elasticsearch. For this purpuse I use river-jdbc but I am having some issues to represent (foreign keys). After some search over the internet I found that there's an option for elasticsearch to represent a parent-child link which could be the best aproach to define a "foreign key" in elasticsearch. The problem that I am facing is that even if I have read some explanations of how to use the \_parent prefix I don't know how to implement it in a river-jdbc query in order to preserve mysql's structure represented in a parent-child structure in elasticsearch. The below JSON representation is what I intend to do using a river-jdbc query. document = { "docid": 123456789, "text": "This is an ingredient description", "category": [{ "catid": '124122151', "name": "avocato", "product": [ {"prodid": "12452361"}, {"name": "Bio avacato"} ] }, { "catid": '124122153', "name": "carrot", "product": [ {"prodid": "12452362"}, {"name": "Bio carrot"} ] }], } On Saturday, January 26, 2013 1:02:36 PM UTC+1, Jörg Prante wrote: > Yes, you can specify a special column label beginning with a prefix > underscore '\_parent' for creating children documents. > > [Labeled columns · jprante/elasticsearch-jdbc Wiki · GitHub](https://github.com/jprante/elasticsearch-river-jdbc/wiki/Labeled-columns) > > The \_parent column value corresponds to the bulk document parameter > \_parent > > [Elasticsearch Platform — Find real-time answers at scale | Elastic](http://www.elasticsearch.org/guide/reference/api/bulk.html) > > I just pushed a fix for missing \_timestamp handling in the JDBC river. > > Best regards, > > Jörg > > Am 26.01.13 11:50, schrieb Ümit Seren: > > > Great thanks for the plugin. > > Just a small question. Is there a way how to craete parent-child > > entries with the JDBC River plugin? At least I wasn't able to come up > > with a solution with the previous version (haven't really looked into > > the new version tough). -- 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). For more options, visit [https://groups.google.com/groups/opt\_out](https://groups.google.com/groups/opt_out). ---
**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:** [March 6, 2013, 11:34pm UTC](https://discuss.elastic.co/t/ann-jdbc-river-for-elasticsearch-2-0-0/10402/9 "2013-03-06T23:34:17Z")
The idea is to run the river twice, once for the parents, and once for the children docs. It does not matter if parents exist, you can add children nevertheless. So you can run the children river first and parent second or vice versa. I think you are aware of it, parent/child in ES is not really a "foreign key" surrogate. It helps to enhance performance because related documents are stored nearby and there is a strict 1:n relationship. But, unlike foreign keys in the relational DB context, there is no contraint management and no rejection of deletes if your data in the index gets inconstent. Does this help? Jörg Am 06.03.13 16:38, schrieb Carlos Alberto López Servín: > Hi everybody, > > I'm a newbie in elasticsearch and I'm trying to make a mapping between > some mysql tables and elasticsearch. For this purpuse I use > river-jdbc but I am having some issues to represent (foreign keys). > After some search over the internet I found that there's an option for > elasticsearch to represent a parent-child link which could be the best > aproach to define a "foreign key" in elasticsearch. > > The problem that I am facing is that even if I have read some > explanations of how to use the \_parent prefix I don't know how to > implement it in a river-jdbc query in order to preserve mysql's > structure represented in a parent-child structure in elasticsearch. > > The below JSON representation is what I intend to do using a > river-jdbc query. > > document = { > "docid": 123456789, > "text": "This is an ingredient description", > "category": [{ > "catid": '124122151', > "name": "avocato", > "product": [ > {"prodid": "12452361"}, > {"name": "Bio avacato"} > ] > }, > { > "catid": '124122153', > "name": "carrot", > "product": [ > {"prodid": "12452362"}, > {"name": "Bio carrot"} > ] > }], > } > > On Saturday, January 26, 2013 1:02:36 PM UTC+1, Jörg Prante wrote: > > ``` > Yes, you can specify a special column label beginning with a prefix > underscore '_parent' for creating children documents. > > https://github.com/jprante/elasticsearch-river-jdbc/wiki/Labeled-columns > > > The _parent column value corresponds to the bulk document > parameter _parent > > http://www.elasticsearch.org/guide/reference/api/bulk.html > > > I just pushed a fix for missing _timestamp handling in the JDBC > river. > > Best regards, > > Jörg > > Am 26.01.13 11:50, schrieb Ümit Seren: > > Great thanks for the plugin. > > Just a small question. Is there a way how to craete parent-child > > entries with the JDBC River plugin? At least I wasn't able to > come up > > with a solution with the previous version (haven't really looked > into > > the new version tough). > > > > > > ``` > > -- > 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). > For more options, visit [https://groups.google.com/groups/opt\_out](https://groups.google.com/groups/opt_out). -- 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). For more options, visit [https://groups.google.com/groups/opt\_out](https://groups.google.com/groups/opt_out). ---
**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, 2:47am UTC](https://discuss.elastic.co/t/ann-jdbc-river-for-elasticsearch-2-0-0/10402/10 "2017-07-06T02:47:54Z")