You are viewing a plain text version of this content. The canonical link for it is here.
Posted to solr-user@lucene.apache.org by Rohan Thakur <ro...@gmail.com> on 2013/05/15 13:07:51 UTC
indexing unrelated tables in single core
hi all
I want to index 2 separate unrelated tables from database into single solr
core and search in any one of the document separately how can I do it?
please help
thanks in advance
regards
Rohan
Re: indexing unrelated tables in single core
Posted by Rohan Thakur <ro...@gmail.com>.
hi mohanty
I appreciate it but dint get that can you please elaborate?
my dataconfig is like:
<entity name="catalogsearch_query" query="select query_id,query_text from
catalogsearch_query where num_results!= 0">
<field column="query_id" name="value_id"/>
<field column="query_text" name="user_query"/>
</entity>
<entity name="catalog_product_entity_varchar" query="select
value_id,value,entity_id,attribute_id from catalog_product_entity_varchar
where attribute_id=60">
<field column="value_id" name="value_id"/>
<field column="value" name="title"/>
<field column="entity_id" name="product_id"/>
<field column="attribute_id" name="attribute"/>
</entity>
my schema is like:
<fields>
<field name="keyfeatures" type="text_en_splitting" indexed="true"
stored="true" required= "false"/>
<field name="value_id" type="plong" indexed="true" stored="false"/>
<field name="product_id" type="plong" indexed="true" stored="true"/>
<field name="features" type="text_en_splitting_tight" indexed="true"
stored="false" required="false" multiValued="true"/>
<!--<field name="f_product_id" type="plong" indexed="true"
stored="true"/>
<field name="f_value_id" type="plong" indexed="true" stored="true"/> -->
<field name="attribute" type="plong" indexed="false" stored="false"/>
<field name="title" type="text_en_splitting" indexed="true"
stored="true" required= "true"/>
<field name="image" type="text_en_splitting_tight" indexed="false"
stored="false"/>
<field name="url" type="text_en_splitting_tight" indexed="false"
stored="false"/>
<field name="brand" type="text_en" indexed="true" stored="true"/>
<field name="procat" type="text_en" indexed="true" stored="true"/>
<field name="rootcat" type="text_en" indexed="true" stored="true"/>
<field name="color" type="text_en" indexed="true" stored="true"/>
<field name="sku" type="text_en_splitting_tight" indexed="true"
stored="true"/>
<field name="spell" type="tSpell" indexed="true" stored="true" />
<field name="query_id" type="plong" indexed="true" stored="true" />
<field name="user_query" type="text_en_splitting_tight" indexed="true"
stored="true" required="false"/>
</fields>
<uniqueKey>value_id</uniqueKey>
<!-- <field name="solr_value" type="text" indexed="true" stored="true"/>
-->
<!-- field for the QueryParser to use when an explicit fieldname is absent
DEPRECATED: specify "df" in your request handler instead. -->
<defaultSearchField>title</defaultSearchField>
thanks regards
Rohan
On Thu, May 16, 2013 at 5:11 PM, Gora Mohanty <go...@mimirtech.com> wrote:
> On 16 May 2013 16:24, Rohan Thakur <ro...@gmail.com> wrote:
> > hi
> >
> > I got the problem it is with the unique key defined in the schema.xml
> > if i difine it to be query_id then while indexing it says
> > missing mandatory key query_id which is not present in the root
> > entity(data-config.xml) which is indexing the product from the database
> > which has product_id as the unique key and when in schema I set
> product_id
> > as the unique key then it says missing mandatory key product_id which is
> > not present in the root entity(data-config.xml) which is indiexing the
> user
> > query from another table in the database which has user_id as the unique
> > key.
> >
> > how can I fix this thanks I want to index both the tables which are
> > basically unrelated that is does not have any *Common* fields....
> [...]
>
> Fix it in the SELECT statement:
> SELECT product_id as id,... for one entity, and
> SELECT query_id as id,... in the other
> and use id as the uniqueKey for Solr.
>
> Regards,
> Gora
>
Re: indexing unrelated tables in single core
Posted by Rohan Thakur <ro...@gmail.com>.
thanks gora
I got that...have to change in the DB itself...
On Fri, May 17, 2013 at 6:27 PM, Gora Mohanty <go...@mimirtech.com> wrote:
> On 16 May 2013 19:11, Rohan Thakur <ro...@gmail.com> wrote:
> > hi Mohanty
> >
> > I tried what you suggested of using id as common field and changing the
> SQL
> > query to point to id
> > and using id as uniqueKey
> > it is working but now what it is doing is just keeping the id's that are
> > not same in both the tables and discarding the id's that are same in both
> > the tables....but this is not correct as both the product_id and query_id
> > has no relation as such both are representing separate things in each
> > tables.
> [...]
>
> Sorry, was away from email. The last configuration that you posted
> seemed fine, and as you say above things seem to work for you.
>
> What you are facing now is that documents where the product_id
> is the same as the query_id are being overwritten, as they have the
> same uniqueKey as far as Solr is concerned. Thus, Solr will update an
> existing document rather than adding a new one. So, you have to
> come up with a scheme that makes the IDs unique. There are various
> ways of doing this depending on how your product_id/query_id are
> set up. One way might be to make the 'id' field that is used as the
> uniqueKey a string rather than an integer, and prefix 'P' for product_ids
> and 'Q' for query_ids.
>
> Regards,
> Gora
>
Re: indexing unrelated tables in single core
Posted by Gora Mohanty <go...@mimirtech.com>.
On 16 May 2013 19:11, Rohan Thakur <ro...@gmail.com> wrote:
> hi Mohanty
>
> I tried what you suggested of using id as common field and changing the SQL
> query to point to id
> and using id as uniqueKey
> it is working but now what it is doing is just keeping the id's that are
> not same in both the tables and discarding the id's that are same in both
> the tables....but this is not correct as both the product_id and query_id
> has no relation as such both are representing separate things in each
> tables.
[...]
Sorry, was away from email. The last configuration that you posted
seemed fine, and as you say above things seem to work for you.
What you are facing now is that documents where the product_id
is the same as the query_id are being overwritten, as they have the
same uniqueKey as far as Solr is concerned. Thus, Solr will update an
existing document rather than adding a new one. So, you have to
come up with a scheme that makes the IDs unique. There are various
ways of doing this depending on how your product_id/query_id are
set up. One way might be to make the 'id' field that is used as the
uniqueKey a string rather than an integer, and prefix 'P' for product_ids
and 'Q' for query_ids.
Regards,
Gora
Re: indexing unrelated tables in single core
Posted by Rohan Thakur <ro...@gmail.com>.
hi Mohanty
I tried what you suggested of using id as common field and changing the SQL
query to point to id
and using id as uniqueKey
it is working but now what it is doing is just keeping the id's that are
not same in both the tables and discarding the id's that are same in both
the tables....but this is not correct as both the product_id and query_id
has no relation as such both are representing separate things in each
tables.
regards
Rohan
On Thu, May 16, 2013 at 5:11 PM, Gora Mohanty <go...@mimirtech.com> wrote:
> On 16 May 2013 16:24, Rohan Thakur <ro...@gmail.com> wrote:
> > hi
> >
> > I got the problem it is with the unique key defined in the schema.xml
> > if i difine it to be query_id then while indexing it says
> > missing mandatory key query_id which is not present in the root
> > entity(data-config.xml) which is indexing the product from the database
> > which has product_id as the unique key and when in schema I set
> product_id
> > as the unique key then it says missing mandatory key product_id which is
> > not present in the root entity(data-config.xml) which is indiexing the
> user
> > query from another table in the database which has user_id as the unique
> > key.
> >
> > how can I fix this thanks I want to index both the tables which are
> > basically unrelated that is does not have any *Common* fields....
> [...]
>
> Fix it in the SELECT statement:
> SELECT product_id as id,... for one entity, and
> SELECT query_id as id,... in the other
> and use id as the uniqueKey for Solr.
>
> Regards,
> Gora
>
Re: indexing unrelated tables in single core
Posted by Gora Mohanty <go...@mimirtech.com>.
On 16 May 2013 16:24, Rohan Thakur <ro...@gmail.com> wrote:
> hi
>
> I got the problem it is with the unique key defined in the schema.xml
> if i difine it to be query_id then while indexing it says
> missing mandatory key query_id which is not present in the root
> entity(data-config.xml) which is indexing the product from the database
> which has product_id as the unique key and when in schema I set product_id
> as the unique key then it says missing mandatory key product_id which is
> not present in the root entity(data-config.xml) which is indiexing the user
> query from another table in the database which has user_id as the unique
> key.
>
> how can I fix this thanks I want to index both the tables which are
> basically unrelated that is does not have any *Common* fields....
[...]
Fix it in the SELECT statement:
SELECT product_id as id,... for one entity, and
SELECT query_id as id,... in the other
and use id as the uniqueKey for Solr.
Regards,
Gora
Re: indexing unrelated tables in single core
Posted by Rohan Thakur <ro...@gmail.com>.
I mean to say that
I want to index 2 tables that is using 2 root entity in data-config.xml
one is product table and other is user search table these both have
no foreign key and I want to index both of them as document in my solr
index what should I do...its taking either one of them and rejecting other
table as document when I am taking primary key of one table as unique key
in the solr schema...and vice verca.....how to solve this?
On Thu, May 16, 2013 at 4:24 PM, Rohan Thakur <ro...@gmail.com> wrote:
> hi
>
> I got the problem it is with the unique key defined in the schema.xml
> if i difine it to be query_id then while indexing it says
> missing mandatory key query_id which is not present in the root
> entity(data-config.xml) which is indexing the product from the database
> which has product_id as the unique key and when in schema I set product_id
> as the unique key then it says missing mandatory key product_id which is
> not present in the root entity(data-config.xml) which is indiexing the user
> query from another table in the database which has user_id as the unique
> key.
>
> how can I fix this thanks I want to index both the tables which are
> basically unrelated that is does not have any *Common* fields....
>
> thanks
> rohan
>
>
> On Thu, May 16, 2013 at 3:24 PM, Michael Della Bitta <
> michael.della.bitta@appinions.com> wrote:
>
>> True, it's complaining that your Solr schema has a required field 'title'
>> and your query and data import config aren't providing it.
>> On May 16, 2013 5:51 AM, "Rohan Thakur" <ro...@gmail.com> wrote:
>>
>> > its saying in the logs that missing required field title which is no
>> where
>> > in the database...
>> >
>> >
>> > On Thu, May 16, 2013 at 3:08 PM, Rohan Thakur <ro...@gmail.com>
>> > wrote:
>> >
>> > > I am not able to index the fields from data base its getting failed...
>> > >
>> > > data-config.xml
>> > >
>> > > <dataSource type="JdbcDataSource" driver="com.mysql.jdbc.Driver"
>> > > url="jdbc:mysql://localhost/test"
>> > > user="user" password="dfsdf"/>
>> > > <document>
>> > > <entity name="catalogsearch_query" query="select
>> query_id,query_text
>> > > from catalogsearch_query where num_results!= 0">
>> > > <field column="query_id" name="query_id"/>
>> > > <field column="query_text" name="user_query"/>
>> > > </entity>
>> > > </document>
>> > >
>> > > its showing all failed and 0 indexed
>> > >
>> > >
>> > > On Wed, May 15, 2013 at 8:31 PM, Alexandre Rafalovitch <
>> > arafalov@gmail.com
>> > > > wrote:
>> > >
>> > >> 1. Create a schema that accomodates both types of fields either using
>> > >> optional fields or dynamic fields.
>> > >> 2. Create some sort of differentiator key (e.g. schema), separately
>> > >> from id (which needs to be globally unique, so possibly schema+id)
>> > >> 3. Use that schema in filter queries (fq) to look only at subject of
>> > items
>> > >> 4. (Optionally) define separate search request handlers that force
>> > >> that schema parameter (using appends or invariants instead of
>> > >> defaults)
>> > >>
>> > >> That should get you most of the way there.
>> > >>
>> > >> Regards,
>> > >> Alex.
>> > >> Personal blog: http://blog.outerthoughts.com/
>> > >> LinkedIn: http://www.linkedin.com/in/alexandrerafalovitch
>> > >> - Time is the quality of nature that keeps events from happening all
>> > >> at once. Lately, it doesn't seem to be working. (Anonymous - via
>> GTD
>> > >> book)
>> > >>
>> > >>
>> > >> On Wed, May 15, 2013 at 7:07 AM, Rohan Thakur <ro...@gmail.com>
>> > >> wrote:
>> > >> > hi all
>> > >> >
>> > >> >
>> > >> > I want to index 2 separate unrelated tables from database into
>> single
>> > >> solr
>> > >> > core and search in any one of the document separately how can I do
>> it?
>> > >> > please help
>> > >> >
>> > >> > thanks in advance
>> > >> > regards
>> > >> > Rohan
>> > >>
>> > >
>> > >
>> >
>>
>
>
Re: indexing unrelated tables in single core
Posted by Rohan Thakur <ro...@gmail.com>.
hi
I got the problem it is with the unique key defined in the schema.xml
if i difine it to be query_id then while indexing it says
missing mandatory key query_id which is not present in the root
entity(data-config.xml) which is indexing the product from the database
which has product_id as the unique key and when in schema I set product_id
as the unique key then it says missing mandatory key product_id which is
not present in the root entity(data-config.xml) which is indiexing the user
query from another table in the database which has user_id as the unique
key.
how can I fix this thanks I want to index both the tables which are
basically unrelated that is does not have any *Common* fields....
thanks
rohan
On Thu, May 16, 2013 at 3:24 PM, Michael Della Bitta <
michael.della.bitta@appinions.com> wrote:
> True, it's complaining that your Solr schema has a required field 'title'
> and your query and data import config aren't providing it.
> On May 16, 2013 5:51 AM, "Rohan Thakur" <ro...@gmail.com> wrote:
>
> > its saying in the logs that missing required field title which is no
> where
> > in the database...
> >
> >
> > On Thu, May 16, 2013 at 3:08 PM, Rohan Thakur <ro...@gmail.com>
> > wrote:
> >
> > > I am not able to index the fields from data base its getting failed...
> > >
> > > data-config.xml
> > >
> > > <dataSource type="JdbcDataSource" driver="com.mysql.jdbc.Driver"
> > > url="jdbc:mysql://localhost/test"
> > > user="user" password="dfsdf"/>
> > > <document>
> > > <entity name="catalogsearch_query" query="select
> query_id,query_text
> > > from catalogsearch_query where num_results!= 0">
> > > <field column="query_id" name="query_id"/>
> > > <field column="query_text" name="user_query"/>
> > > </entity>
> > > </document>
> > >
> > > its showing all failed and 0 indexed
> > >
> > >
> > > On Wed, May 15, 2013 at 8:31 PM, Alexandre Rafalovitch <
> > arafalov@gmail.com
> > > > wrote:
> > >
> > >> 1. Create a schema that accomodates both types of fields either using
> > >> optional fields or dynamic fields.
> > >> 2. Create some sort of differentiator key (e.g. schema), separately
> > >> from id (which needs to be globally unique, so possibly schema+id)
> > >> 3. Use that schema in filter queries (fq) to look only at subject of
> > items
> > >> 4. (Optionally) define separate search request handlers that force
> > >> that schema parameter (using appends or invariants instead of
> > >> defaults)
> > >>
> > >> That should get you most of the way there.
> > >>
> > >> Regards,
> > >> Alex.
> > >> Personal blog: http://blog.outerthoughts.com/
> > >> LinkedIn: http://www.linkedin.com/in/alexandrerafalovitch
> > >> - Time is the quality of nature that keeps events from happening all
> > >> at once. Lately, it doesn't seem to be working. (Anonymous - via GTD
> > >> book)
> > >>
> > >>
> > >> On Wed, May 15, 2013 at 7:07 AM, Rohan Thakur <ro...@gmail.com>
> > >> wrote:
> > >> > hi all
> > >> >
> > >> >
> > >> > I want to index 2 separate unrelated tables from database into
> single
> > >> solr
> > >> > core and search in any one of the document separately how can I do
> it?
> > >> > please help
> > >> >
> > >> > thanks in advance
> > >> > regards
> > >> > Rohan
> > >>
> > >
> > >
> >
>
Re: indexing unrelated tables in single core
Posted by Michael Della Bitta <mi...@appinions.com>.
True, it's complaining that your Solr schema has a required field 'title'
and your query and data import config aren't providing it.
On May 16, 2013 5:51 AM, "Rohan Thakur" <ro...@gmail.com> wrote:
> its saying in the logs that missing required field title which is no where
> in the database...
>
>
> On Thu, May 16, 2013 at 3:08 PM, Rohan Thakur <ro...@gmail.com>
> wrote:
>
> > I am not able to index the fields from data base its getting failed...
> >
> > data-config.xml
> >
> > <dataSource type="JdbcDataSource" driver="com.mysql.jdbc.Driver"
> > url="jdbc:mysql://localhost/test"
> > user="user" password="dfsdf"/>
> > <document>
> > <entity name="catalogsearch_query" query="select query_id,query_text
> > from catalogsearch_query where num_results!= 0">
> > <field column="query_id" name="query_id"/>
> > <field column="query_text" name="user_query"/>
> > </entity>
> > </document>
> >
> > its showing all failed and 0 indexed
> >
> >
> > On Wed, May 15, 2013 at 8:31 PM, Alexandre Rafalovitch <
> arafalov@gmail.com
> > > wrote:
> >
> >> 1. Create a schema that accomodates both types of fields either using
> >> optional fields or dynamic fields.
> >> 2. Create some sort of differentiator key (e.g. schema), separately
> >> from id (which needs to be globally unique, so possibly schema+id)
> >> 3. Use that schema in filter queries (fq) to look only at subject of
> items
> >> 4. (Optionally) define separate search request handlers that force
> >> that schema parameter (using appends or invariants instead of
> >> defaults)
> >>
> >> That should get you most of the way there.
> >>
> >> Regards,
> >> Alex.
> >> Personal blog: http://blog.outerthoughts.com/
> >> LinkedIn: http://www.linkedin.com/in/alexandrerafalovitch
> >> - Time is the quality of nature that keeps events from happening all
> >> at once. Lately, it doesn't seem to be working. (Anonymous - via GTD
> >> book)
> >>
> >>
> >> On Wed, May 15, 2013 at 7:07 AM, Rohan Thakur <ro...@gmail.com>
> >> wrote:
> >> > hi all
> >> >
> >> >
> >> > I want to index 2 separate unrelated tables from database into single
> >> solr
> >> > core and search in any one of the document separately how can I do it?
> >> > please help
> >> >
> >> > thanks in advance
> >> > regards
> >> > Rohan
> >>
> >
> >
>
Re: indexing unrelated tables in single core
Posted by Rohan Thakur <ro...@gmail.com>.
its saying in the logs that missing required field title which is no where
in the database...
On Thu, May 16, 2013 at 3:08 PM, Rohan Thakur <ro...@gmail.com> wrote:
> I am not able to index the fields from data base its getting failed...
>
> data-config.xml
>
> <dataSource type="JdbcDataSource" driver="com.mysql.jdbc.Driver"
> url="jdbc:mysql://localhost/test"
> user="user" password="dfsdf"/>
> <document>
> <entity name="catalogsearch_query" query="select query_id,query_text
> from catalogsearch_query where num_results!= 0">
> <field column="query_id" name="query_id"/>
> <field column="query_text" name="user_query"/>
> </entity>
> </document>
>
> its showing all failed and 0 indexed
>
>
> On Wed, May 15, 2013 at 8:31 PM, Alexandre Rafalovitch <arafalov@gmail.com
> > wrote:
>
>> 1. Create a schema that accomodates both types of fields either using
>> optional fields or dynamic fields.
>> 2. Create some sort of differentiator key (e.g. schema), separately
>> from id (which needs to be globally unique, so possibly schema+id)
>> 3. Use that schema in filter queries (fq) to look only at subject of items
>> 4. (Optionally) define separate search request handlers that force
>> that schema parameter (using appends or invariants instead of
>> defaults)
>>
>> That should get you most of the way there.
>>
>> Regards,
>> Alex.
>> Personal blog: http://blog.outerthoughts.com/
>> LinkedIn: http://www.linkedin.com/in/alexandrerafalovitch
>> - Time is the quality of nature that keeps events from happening all
>> at once. Lately, it doesn't seem to be working. (Anonymous - via GTD
>> book)
>>
>>
>> On Wed, May 15, 2013 at 7:07 AM, Rohan Thakur <ro...@gmail.com>
>> wrote:
>> > hi all
>> >
>> >
>> > I want to index 2 separate unrelated tables from database into single
>> solr
>> > core and search in any one of the document separately how can I do it?
>> > please help
>> >
>> > thanks in advance
>> > regards
>> > Rohan
>>
>
>
Re: indexing unrelated tables in single core
Posted by Rohan Thakur <ro...@gmail.com>.
I am not able to index the fields from data base its getting failed...
data-config.xml
<dataSource type="JdbcDataSource" driver="com.mysql.jdbc.Driver"
url="jdbc:mysql://localhost/test"
user="user" password="dfsdf"/>
<document>
<entity name="catalogsearch_query" query="select query_id,query_text
from catalogsearch_query where num_results!= 0">
<field column="query_id" name="query_id"/>
<field column="query_text" name="user_query"/>
</entity>
</document>
its showing all failed and 0 indexed
On Wed, May 15, 2013 at 8:31 PM, Alexandre Rafalovitch
<ar...@gmail.com>wrote:
> 1. Create a schema that accomodates both types of fields either using
> optional fields or dynamic fields.
> 2. Create some sort of differentiator key (e.g. schema), separately
> from id (which needs to be globally unique, so possibly schema+id)
> 3. Use that schema in filter queries (fq) to look only at subject of items
> 4. (Optionally) define separate search request handlers that force
> that schema parameter (using appends or invariants instead of
> defaults)
>
> That should get you most of the way there.
>
> Regards,
> Alex.
> Personal blog: http://blog.outerthoughts.com/
> LinkedIn: http://www.linkedin.com/in/alexandrerafalovitch
> - Time is the quality of nature that keeps events from happening all
> at once. Lately, it doesn't seem to be working. (Anonymous - via GTD
> book)
>
>
> On Wed, May 15, 2013 at 7:07 AM, Rohan Thakur <ro...@gmail.com>
> wrote:
> > hi all
> >
> >
> > I want to index 2 separate unrelated tables from database into single
> solr
> > core and search in any one of the document separately how can I do it?
> > please help
> >
> > thanks in advance
> > regards
> > Rohan
>
Re: indexing unrelated tables in single core
Posted by Alexandre Rafalovitch <ar...@gmail.com>.
1. Create a schema that accomodates both types of fields either using
optional fields or dynamic fields.
2. Create some sort of differentiator key (e.g. schema), separately
from id (which needs to be globally unique, so possibly schema+id)
3. Use that schema in filter queries (fq) to look only at subject of items
4. (Optionally) define separate search request handlers that force
that schema parameter (using appends or invariants instead of
defaults)
That should get you most of the way there.
Regards,
Alex.
Personal blog: http://blog.outerthoughts.com/
LinkedIn: http://www.linkedin.com/in/alexandrerafalovitch
- Time is the quality of nature that keeps events from happening all
at once. Lately, it doesn't seem to be working. (Anonymous - via GTD
book)
On Wed, May 15, 2013 at 7:07 AM, Rohan Thakur <ro...@gmail.com> wrote:
> hi all
>
>
> I want to index 2 separate unrelated tables from database into single solr
> core and search in any one of the document separately how can I do it?
> please help
>
> thanks in advance
> regards
> Rohan