SV: configuring schema to match database
When thinkting some more, Perhaps I could have coursename and such as multivalue? Or should I have separate indeces for users, courses and languages? I get the feeling both would work, but now sure which way is the best to go. When a user is updating/removing/adding a course it would be nice to to have to query the database for users courses and languages and update everything but just update a course document But perhaps I'm thinking to much in database terms? But still I'm unsure how the schema should look like Thanks /Niklas -Ursprungligt meddelande- Från: Niklas Langvig [mailto:niklas.lang...@globesoft.com] Skickat: den 11 januari 2013 14:19 Till: solr-user@lucene.apache.org Ämne: configuring schema to match database Hi! I'm quite new to solr and trying to understand how to create a schema from how our postgres database and then search for the content in solr instead of querying the db. My question should be really easy, it has most likely been asked many times but still I'm not able to google any answer to it. To make it easy, I have 3 columns: users, courses and languages Users has columns , userid, firstname, lastname Courses has column coursename, startdate, enddate Languages has column language, writingskill, verbalskill UserA has taken courseA, courseB and courseC and has writingskill good verbalskill good for english and writingskill excellent verbalskill excellent for spanish UserB has taken courseA, courseF, courseG and courseH and has writingskill fluent verbalskill fluent for english and writingskill good verbalskill good for italian I would like to put this data into solr so I can search for all users how have taken courseA and are fluent in english. Can I do that? The problem is I'm not sure how to flatten this database into a schema It's easy to understand the users column, for example field name=userid type=string indexed=true / field name=firstname type=string indexed=true / field name=lastname type=string indexed=true / But then I'm not so sure how the schema should look like for courses and languages field name=userid type=string indexed=true / field name=coursename type=string indexed=true / field name=startdate type=string indexed=true / field name=enddate type=string indexed=true / Thanks for any help /Niklas
SV: configuring schema to match database
Hmm noticed I wrote I have 3 columns: users, courses and languages I ofcourse mean I have 3 tables: users, courses and languages /Niklas -Ursprungligt meddelande- Från: Niklas Langvig [mailto:niklas.lang...@globesoft.com] Skickat: den 11 januari 2013 14:19 Till: solr-user@lucene.apache.org Ämne: configuring schema to match database Hi! I'm quite new to solr and trying to understand how to create a schema from how our postgres database and then search for the content in solr instead of querying the db. My question should be really easy, it has most likely been asked many times but still I'm not able to google any answer to it. To make it easy, I have 3 columns: users, courses and languages Users has columns , userid, firstname, lastname Courses has column coursename, startdate, enddate Languages has column language, writingskill, verbalskill UserA has taken courseA, courseB and courseC and has writingskill good verbalskill good for english and writingskill excellent verbalskill excellent for spanish UserB has taken courseA, courseF, courseG and courseH and has writingskill fluent verbalskill fluent for english and writingskill good verbalskill good for italian I would like to put this data into solr so I can search for all users how have taken courseA and are fluent in english. Can I do that? The problem is I'm not sure how to flatten this database into a schema It's easy to understand the users column, for example field name=userid type=string indexed=true / field name=firstname type=string indexed=true / field name=lastname type=string indexed=true / But then I'm not so sure how the schema should look like for courses and languages field name=userid type=string indexed=true / field name=coursename type=string indexed=true / field name=startdate type=string indexed=true / field name=enddate type=string indexed=true / Thanks for any help /Niklas
SV: configuring schema to match database
Hi Dariusz, To me this example has one table user and I have many tables that connects to one user and that is what I'm unsure how how to do. /Niklas -Ursprungligt meddelande- Från: Dariusz Borowski [mailto:darius...@gmail.com] Skickat: den 11 januari 2013 14:56 Till: solr-user@lucene.apache.org Ämne: Re: configuring schema to match database Hi Niklas, Maybe this link helps: http://www.coderthing.com/solr-with-multicore-and-database-hook-part-1/ D. On Fri, Jan 11, 2013 at 2:19 PM, Niklas Langvig niklas.lang...@globesoft.com wrote: Hi! I'm quite new to solr and trying to understand how to create a schema from how our postgres database and then search for the content in solr instead of querying the db. My question should be really easy, it has most likely been asked many times but still I'm not able to google any answer to it. To make it easy, I have 3 columns: users, courses and languages Users has columns , userid, firstname, lastname Courses has column coursename, startdate, enddate Languages has column language, writingskill, verbalskill UserA has taken courseA, courseB and courseC and has writingskill good verbalskill good for english and writingskill excellent verbalskill excellent for spanish UserB has taken courseA, courseF, courseG and courseH and has writingskill fluent verbalskill fluent for english and writingskill good verbalskill good for italian I would like to put this data into solr so I can search for all users how have taken courseA and are fluent in english. Can I do that? The problem is I'm not sure how to flatten this database into a schema It's easy to understand the users column, for example field name=userid type=string indexed=true / field name=firstname type=string indexed=true / field name=lastname type=string indexed=true / But then I'm not so sure how the schema should look like for courses and languages field name=userid type=string indexed=true / field name=coursename type=string indexed=true / field name=startdate type=string indexed=true / field name=enddate type=string indexed=true / Thanks for any help /Niklas
SV: configuring schema to match database
Ahh sorry, Now I understand, Ok seems like a good solution, I just know need to understand how to query multiple cores now :) -Ursprungligt meddelande- Från: Dariusz Borowski [mailto:darius...@gmail.com] Skickat: den 11 januari 2013 15:15 Till: solr-user@lucene.apache.org Ämne: Re: configuring schema to match database Hi, No, it has actually two tables. User and Item. The example shown on the blog is for one table, because you repeat the same thing for the other table. Only your data-import.xml file changes. For the rest, just copy and paste it in the conf directory. If you are running your solr in Linux, then you can work with symlinks. D. On Fri, Jan 11, 2013 at 3:12 PM, Niklas Langvig niklas.lang...@globesoft.com wrote: Hi Dariusz, To me this example has one table user and I have many tables that connects to one user and that is what I'm unsure how how to do. /Niklas -Ursprungligt meddelande- Från: Dariusz Borowski [mailto:darius...@gmail.com] Skickat: den 11 januari 2013 14:56 Till: solr-user@lucene.apache.org Ämne: Re: configuring schema to match database Hi Niklas, Maybe this link helps: http://www.coderthing.com/solr-with-multicore-and-database-hook-part-1 / D. On Fri, Jan 11, 2013 at 2:19 PM, Niklas Langvig niklas.lang...@globesoft.com wrote: Hi! I'm quite new to solr and trying to understand how to create a schema from how our postgres database and then search for the content in solr instead of querying the db. My question should be really easy, it has most likely been asked many times but still I'm not able to google any answer to it. To make it easy, I have 3 columns: users, courses and languages Users has columns , userid, firstname, lastname Courses has column coursename, startdate, enddate Languages has column language, writingskill, verbalskill UserA has taken courseA, courseB and courseC and has writingskill good verbalskill good for english and writingskill excellent verbalskill excellent for spanish UserB has taken courseA, courseF, courseG and courseH and has writingskill fluent verbalskill fluent for english and writingskill good verbalskill good for italian I would like to put this data into solr so I can search for all users how have taken courseA and are fluent in english. Can I do that? The problem is I'm not sure how to flatten this database into a schema It's easy to understand the users column, for example field name=userid type=string indexed=true / field name=firstname type=string indexed=true / field name=lastname type=string indexed=true / But then I'm not so sure how the schema should look like for courses and languages field name=userid type=string indexed=true / field name=coursename type=string indexed=true / field name=startdate type=string indexed=true / field name=enddate type=string indexed=true / Thanks for any help /Niklas
SV: configuring schema to match database
It sounds good not to use more than one core, for sure I do not want to over complicate this. Yes I meant tables. It's pretty simple. Both table courses and languages has it's own primary key courseseqno and languagesseqno Both also have a foreign key userid that references the users table with column userid The relationship from users to courses and languages are one-to-many. but I guess I'm thinking wrong because my idead whould be to have a block of fields connected with one id field name=coursename type=string indexed=true / field name=startdate type=date indexed=true / field name=enddate type= indexed=true / These three are connected with a field name=courseseqno type=int indexed=true / But also have a field name=userid type=int indexed=true / To connect to a specific user? Thanks /Niklas -Ursprungligt meddelande- Från: Gora Mohanty [mailto:g...@mimirtech.com] Skickat: den 11 januari 2013 15:55 Till: solr-user@lucene.apache.org Ämne: Re: configuring schema to match database On 11 January 2013 19:57, Niklas Langvig niklas.lang...@globesoft.com wrote: Ahh sorry, Now I understand, Ok seems like a good solution, I just know need to understand how to query multiple cores now :) There is no need to use multiple cores in your setup. Going back to your original problem statement, it can easily be handled with a single core, and it actually makes more sense to do it that way. You will need to give us more details. My question should be really easy, it has most likely been asked many times but still I'm not able to google any answer to it. To make it easy, I have 3 columns: users, courses and languages Presumably, you mean three tables, as you describe each as having columns. How are the tables connected? Is there a foreign key relationship between them? Is the relationship one-to-one, one-to-many, or what? Users has columns , userid, firstname, lastname Courses has column coursename, startdate, enddate Languages has column language, writingskill, verbalskill [...] I would like to put this data into solr so I can search for all users how have taken courseA and are fluent in english. Can I do that? 1. Your schema for the single core is quite straightforward, and along the lines of what you had described (one field for each database column in each table), e.g., field name=userid type=string indexed=true / field name=firstname type=string indexed=true / field name=lastname type=string indexed=true / field name=coursename type=string indexed=true / field name=startdate type=date indexed=true / field name=enddate type= indexed=true / field name=language type=string indexed=true / field name=writingskill type=string indexed=true / field name=verbalskill type=string indexed=true / Pay attention to the type. Dates should typically be solr.DateField. The others can be strings, but if they are integers in the database, you might benefit from making these integers in Solr also. 2. One has to stop thinking of Solr as a RDBMS. Instead, one flattens out data from a typical RDBMS structure. It is difficult to give you complete instructions unless you describe the database relationships, but, e.g., if one has userA with course1, course2, and course3, and userB with course2, course4, the Solr documents would be : userA course1 details for course1... userA course2 details for course2... userA course3 details for course3... userB course2 details for course2... userB course4 details for course4... This scheme could also be extended to languages, depending on how the tables are related. 3. While indexing into Solr, one has to select from the database, and flatten out the data as above. The two main ways of doing this are using a library like SolrJ for Java (other languages have other libraries, e.g., django-haystack is easy to get started with if one is using Python/Django), or the Solr DataImportHandler (please see http://wiki.apache.org/solr/DataImportHandler ) with nested entities. 4. With such a structure, querying Solr should be simple. Regards, Gora