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
- 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
- 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
--