Re: The issue of import data from database using Solr DIH
Hi Kevin, I think you made OS06Y the uniqueKey, right? So, in entity 1 you specify values for it, but in entity 2 you do so as well. I am not absolutely sure about this, but: It seems like your two entities create two documents and the second will overwrite the first. Have a look at this page: http://wiki.apache.org/solr/DIHQuickStart#Index_data_from_multiple_tables_into_Solr I think it will help you in rewriting your queries to fit your usecase. Regards, Em -- View this message in context: http://lucene.472066.n3.nabble.com/The-issue-of-import-data-from-database-using-Solr-DIH-tp2845318p2846296.html Sent from the Solr - User mailing list archive at Nabble.com.
RE: The issue of import data from database using Solr DIH
Thanks Em. Yes, OS06Y is the uniqueKey. Table1 and Table2 is parallel in my example. In the Url: http://wiki.apache.org/solr/DIHQuickStart#Index_data_from_multiple_table s_into_Solr The tables don't have parallel relations in the above URL example I want to know that can solr implement the case? 1.Get data from database table1; 2.Get data from database table2; 3.merge the fields of table1 and table2; The configuration of db-data-config.xml is the following: document name=allperf entity name=PerformanceData1 dataSource=getTrailingTotalReturnForMonthEnd1 query=SELECT Perfo rmanceId,Trailing1MonthReturn,Trailing2MonthReturn,Trailing3MonthReturn, FROM Table1 field column=PerformanceId name=OS06Y / field column=Trailing1MonthReturn name=PM004 / field column=Trailing2MonthReturn name=PM133 / field column=Trailing3MonthReturn name=PM006 / /entity entity name=PerformanceData2 dataSource=getTrailingTotalReturnForMonthEnd2 query=SELECT Performan ceId,Trailing10YearReturn,Trailing15YearReturn,TrailingYearToDateReturn, SinceInceptionReturn FROM Table2 field column=PerformanceId name=OS06Y / field column=Trailing10YearReturn name=PM00I / field column=Trailing15YearReturn name=PM00K / field column=TrailingYearToDateReturn name=PM00A / field column=SinceInceptionReturn name=PM00M / /entity /document Because I don't want to get one id and data from table1 and then get the data by id from table2,it may met performance issue. -Original Message- From: Em [mailto:mailformailingli...@yahoo.de] Sent: Thursday, April 21, 2011 4:38 PM To: solr-user@lucene.apache.org Subject: Re: The issue of import data from database using Solr DIH Hi Kevin, I think you made OS06Y the uniqueKey, right? So, in entity 1 you specify values for it, but in entity 2 you do so as well. I am not absolutely sure about this, but: It seems like your two entities create two documents and the second will overwrite the first. Have a look at this page: http://wiki.apache.org/solr/DIHQuickStart#Index_data_from_multiple_table s_into_Solr I think it will help you in rewriting your queries to fit your usecase. Regards, Em -- View this message in context: http://lucene.472066.n3.nabble.com/The-issue-of-import-data-from-databas e-using-Solr-DIH-tp2845318p2846296.html Sent from the Solr - User mailing list archive at Nabble.com.
RE: The issue of import data from database using Solr DIH
Not sure I understood you correct: You expect that OS06Y stores *two* different performanceIds? One from table1 and the other from table2? I think this may be a problem. If both OS06Y-keys are equal, than you can use the syntax as mentioned in the wiki without any problems. You just have to rewrite your config to make the second entity a sub-entity and to add a WHERE-clause. If this is really not possible for you, just a guess, what happens if you remove the OS06Y-field from your second entity? Regards, Em -- View this message in context: http://lucene.472066.n3.nabble.com/The-issue-of-import-data-from-database-using-Solr-DIH-tp2845318p2846347.html Sent from the Solr - User mailing list archive at Nabble.com.
RE: The issue of import data from database using Solr DIH
I try remove the OS06Y-field from your second entity ,import the second entity failed. Give a example: Table1: OS06Y=123,f1=100,f2=200,f3=300; OS06Y=456,f1=100,f2=200,f3=300; Table2: OS06Y=123,f4=100,f5=200; OS06Y=456,f4=100; OS06Y=789,f4=100; I want the result: OS06Y=123,f1=100,f2=200,f3=300,f4=100,f5=200; OS06Y=456,f1=100,f2=200,f3=300,f4=100; OS06Y=789,f4=100; Can solr implement it? if yes,how to configure dataconfig.xml in solr? -Original Message- From: Em [mailto:mailformailingli...@yahoo.de] Sent: Thursday, April 21, 2011 4:59 PM To: solr-user@lucene.apache.org Subject: RE: The issue of import data from database using Solr DIH Not sure I understood you correct: You expect that OS06Y stores *two* different performanceIds? One from table1 and the other from table2? I think this may be a problem. If both OS06Y-keys are equal, than you can use the syntax as mentioned in the wiki without any problems. You just have to rewrite your config to make the second entity a sub-entity and to add a WHERE-clause. If this is really not possible for you, just a guess, what happens if you remove the OS06Y-field from your second entity? Regards, Em -- View this message in context: http://lucene.472066.n3.nabble.com/The-issue-of-import-data-from-databas e-using-Solr-DIH-tp2845318p2846347.html Sent from the Solr - User mailing list archive at Nabble.com.
RE: The issue of import data from database using Solr DIH
What you want to do is something like a left outer join, isn't it ? something like : select table2.OS06Y, f1,f2,f3,f4,f5 from table2 left outer join table1 on table2.OS06Y = table1.OS06Y where ... could you prepare a view in your RDBMS ? That could be another solution ? Ludovic. - Jouve France. -- View this message in context: http://lucene.472066.n3.nabble.com/The-issue-of-import-data-from-database-using-Solr-DIH-tp2845318p2846403.html Sent from the Solr - User mailing list archive at Nabble.com.
RE: The issue of import data from database using Solr DIH
As Iboutrus mentioned, if you can summarize it in a query, than yes, Solr can handle it. Make a step backward: Do not think of Solr. Write a query (one! query) that shows exactly the output you exepct. Afterwards, implement this query as a source for DIH. Regards, Em -- View this message in context: http://lucene.472066.n3.nabble.com/The-issue-of-import-data-from-database-using-Solr-DIH-tp2845318p2846414.html Sent from the Solr - User mailing list archive at Nabble.com.
RE: The issue of import data from database using Solr DIH
Yes, it is like the left outer join. In my example.the table may be table or view or stored procedure,I can not change it in database. If for every id in table1,we need search the fields by id from table2 in database,it will met performance issue,especially the size of tables are very big. -Original Message- From: lboutros [mailto:boutr...@gmail.com] Sent: Thursday, April 21, 2011 5:25 PM To: solr-user@lucene.apache.org Subject: RE: The issue of import data from database using Solr DIH What you want to do is something like a left outer join, isn't it ? something like : select table2.OS06Y, f1,f2,f3,f4,f5 from table2 left outer join table1 on table2.OS06Y = table1.OS06Y where ... could you prepare a view in your RDBMS ? That could be another solution ? Ludovic. - Jouve France. -- View this message in context: http://lucene.472066.n3.nabble.com/The-issue-of-import-data-from-databas e-using-Solr-DIH-tp2845318p2846403.html Sent from the Solr - User mailing list archive at Nabble.com.