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 subacini Arunkumar <su...@gmail.com> on 2013/11/30 09:15:12 UTC
Re:
Hi
We are using solr 4.4 . Please let me know the possible solutions to
address my requirement.
We have to fetch data from two tables Product , Order table.
Product Table
id Name
P1 ipad
P2 iphone 4
P3 iphone 5
Order Table
id order date product_id
O1 1-Dec-2012 P1
O2 1-Dec-2012 P2
O3 2-Dec-2012 P2
I want to show the details in UI as below
O1 01-Dec-2012
On Sat, Nov 30, 2013 at 12:13 AM, subacini Arunkumar <su...@gmail.com>wrote:
> Hi
>
> We are using solr 4.4 . Please let me know the possible solutions to
> address my requirement.
>
> We have to fetch data from two tables Product , Order table.
>
> Product Table
>
> id Name
> P1 ipad
> P2 iphone 4
> P3 iphone 5
>
>
> Order Table
>
> id order date product_id
> O1
>
>
Re:
Posted by Gora Mohanty <go...@mimirtech.com>.
On 1 December 2013 11:29, subacini Arunkumar <su...@gmail.com> wrote:
> I have product core, order core ,Is there a way in solr to fetch all
> fields from two cores in a single query?Solr join can fetch fields from
> only 1 core.
> If we cant , how to achieve this??, is the only option is to index
> denormalize data??
Please look at Walter's response. Yes, the standard way to deal with
this would be to denormalise data while they are being indexed into
Solr. http://wiki.apache.org/solr/DataImportHandler should get you
started on this.
It looks like you are trying to force Solr into the role of a relational
database which is not a good idea.
Regards,
Gora
Re:
Posted by subacini Arunkumar <su...@gmail.com>.
I have product core, order core ,Is there a way in solr to fetch all
fields from two cores in a single query?Solr join can fetch fields from
only 1 core.
If we cant , how to achieve this??, is the only option is to index
denormalize data??
On Sat, Nov 30, 2013 at 8:08 PM, Walter Underwood <wu...@wunderwood.org>wrote:
> Several million rows is not a big deal for Solr, especially if you don't
> need really fast response. Lots of columns are also not a big problem.
>
> Join the tables, like you would to make a view, and store that in Solr.
>
> But again, do not use Solr for transactional data.
>
> wunder
>
> On Nov 30, 2013, at 5:04 PM, subacini Arunkumar <su...@gmail.com>
> wrote:
>
> > Thanks Walter for the reply. Here is my complete requirement.
> >
> >
> > Please let me know the possible solutions to address my requirement.
> >
> > * Two tables might have millions of records with 50 columns in each table
> >
> > * Expected output is same as what we get in SQL inner join
> >
> > say For Eg, I have two tables Product , Order table.
> >
> > *Product Table *
> >
> > id Name
> >
> > P1 ipad
> >
> > P2 iphone 4
> >
> > P3 iphone 5
> >
> > *Order Table*
> >
> > id order date product_id
> >
> > O1 1-Dec-2012 P1
> >
> > O2 1-Dec-2012 P2
> >
> > O3 2-Dec-2012 P2
> >
> >
> > *Expected Output: *I want to show the details in UI as below [SQL inner
> > join ]
> >
> > O1 01-Dec-2012 ipad
> >
> > O2 1-Dec-2012 iPhone 4
> >
> > O3 2-Dec-2012 iPhone 5
> >
> >
> > I tried setting up two solr cores , Product core & Order Core
> >
> > *Option 1: Using Solr Join*
> >
> > I got expected result but i was able to get columns only from one core
> (ie)
> > total 3 records but only product table columns
> >
> >
> > http://<….>/product/select?q=*&fq={!join from=product_id to=id
> > fromIndex=order}*
> >
> >
> > *Option 2: Using shards*
> >
> > Created third core, but number of records is sum of(Product core + Order
> > Core) as documents are of different types and they are all unique(ie) 6
> > records
> >
> > So how could i generate a single document with all fields from two
> > different document types in different cores.
> >
> >
> > On Sat, Nov 30, 2013 at 8:04 AM, Walter Underwood <wunder@wunderwood.org
> >wrote:
> >
> >> 1. Flatten the data into a single table.
> >>
> >> 2. Solr does not seem like a good solution for order data, especially
> live
> >> orders that need to be transactional. That is a great match to a
> standard
> >> relational DB.
> >>
> >> wunder
> >>
> >> On Nov 30, 2013, at 12:15 AM, subacini Arunkumar <su...@gmail.com>
> >> wrote:
> >>
> >>> Hi
> >>>
> >>> We are using solr 4.4 . Please let me know the possible solutions to
> >>> address my requirement.
> >>>
> >>> We have to fetch data from two tables Product , Order table.
> >>>
> >>> Product Table
> >>>
> >>> id Name
> >>> P1 ipad
> >>> P2 iphone 4
> >>> P3 iphone 5
> >>>
> >>>
> >>> Order Table
> >>>
> >>> id order date product_id
> >>> O1 1-Dec-2012 P1
> >>> O2 1-Dec-2012 P2
> >>> O3 2-Dec-2012 P2
> >>>
> >>> I want to show the details in UI as below
> >>>
> >>> O1 01-Dec-2012
> >>>
> >>>
> >>> On Sat, Nov 30, 2013 at 12:13 AM, subacini Arunkumar <
> subacini@gmail.com
> >>> wrote:
> >>>
> >>>> Hi
> >>>>
> >>>> We are using solr 4.4 . Please let me know the possible solutions to
> >>>> address my requirement.
> >>>>
> >>>> We have to fetch data from two tables Product , Order table.
> >>>>
> >>>> Product Table
> >>>>
> >>>> id Name
> >>>> P1 ipad
> >>>> P2 iphone 4
> >>>> P3 iphone 5
> >>>>
> >>>>
> >>>> Order Table
> >>>>
> >>>> id order date product_id
> >>>> O1
> >>>>
> >>>>
> >>
> >> --
> >> Walter Underwood
> >> wunder@wunderwood.org
> >>
> >>
> >>
> >>
>
> --
> Walter Underwood
> wunder@wunderwood.org
>
>
>
>
Re:
Posted by Walter Underwood <wu...@wunderwood.org>.
Several million rows is not a big deal for Solr, especially if you don't need really fast response. Lots of columns are also not a big problem.
Join the tables, like you would to make a view, and store that in Solr.
But again, do not use Solr for transactional data.
wunder
On Nov 30, 2013, at 5:04 PM, subacini Arunkumar <su...@gmail.com> wrote:
> Thanks Walter for the reply. Here is my complete requirement.
>
>
> Please let me know the possible solutions to address my requirement.
>
> * Two tables might have millions of records with 50 columns in each table
>
> * Expected output is same as what we get in SQL inner join
>
> say For Eg, I have two tables Product , Order table.
>
> *Product Table *
>
> id Name
>
> P1 ipad
>
> P2 iphone 4
>
> P3 iphone 5
>
> *Order Table*
>
> id order date product_id
>
> O1 1-Dec-2012 P1
>
> O2 1-Dec-2012 P2
>
> O3 2-Dec-2012 P2
>
>
> *Expected Output: *I want to show the details in UI as below [SQL inner
> join ]
>
> O1 01-Dec-2012 ipad
>
> O2 1-Dec-2012 iPhone 4
>
> O3 2-Dec-2012 iPhone 5
>
>
> I tried setting up two solr cores , Product core & Order Core
>
> *Option 1: Using Solr Join*
>
> I got expected result but i was able to get columns only from one core (ie)
> total 3 records but only product table columns
>
>
> http://<….>/product/select?q=*&fq={!join from=product_id to=id
> fromIndex=order}*
>
>
> *Option 2: Using shards*
>
> Created third core, but number of records is sum of(Product core + Order
> Core) as documents are of different types and they are all unique(ie) 6
> records
>
> So how could i generate a single document with all fields from two
> different document types in different cores.
>
>
> On Sat, Nov 30, 2013 at 8:04 AM, Walter Underwood <wu...@wunderwood.org>wrote:
>
>> 1. Flatten the data into a single table.
>>
>> 2. Solr does not seem like a good solution for order data, especially live
>> orders that need to be transactional. That is a great match to a standard
>> relational DB.
>>
>> wunder
>>
>> On Nov 30, 2013, at 12:15 AM, subacini Arunkumar <su...@gmail.com>
>> wrote:
>>
>>> Hi
>>>
>>> We are using solr 4.4 . Please let me know the possible solutions to
>>> address my requirement.
>>>
>>> We have to fetch data from two tables Product , Order table.
>>>
>>> Product Table
>>>
>>> id Name
>>> P1 ipad
>>> P2 iphone 4
>>> P3 iphone 5
>>>
>>>
>>> Order Table
>>>
>>> id order date product_id
>>> O1 1-Dec-2012 P1
>>> O2 1-Dec-2012 P2
>>> O3 2-Dec-2012 P2
>>>
>>> I want to show the details in UI as below
>>>
>>> O1 01-Dec-2012
>>>
>>>
>>> On Sat, Nov 30, 2013 at 12:13 AM, subacini Arunkumar <subacini@gmail.com
>>> wrote:
>>>
>>>> Hi
>>>>
>>>> We are using solr 4.4 . Please let me know the possible solutions to
>>>> address my requirement.
>>>>
>>>> We have to fetch data from two tables Product , Order table.
>>>>
>>>> Product Table
>>>>
>>>> id Name
>>>> P1 ipad
>>>> P2 iphone 4
>>>> P3 iphone 5
>>>>
>>>>
>>>> Order Table
>>>>
>>>> id order date product_id
>>>> O1
>>>>
>>>>
>>
>> --
>> Walter Underwood
>> wunder@wunderwood.org
>>
>>
>>
>>
--
Walter Underwood
wunder@wunderwood.org
Re:
Posted by subacini Arunkumar <su...@gmail.com>.
Thanks Walter for the reply. Here is my complete requirement.
Please let me know the possible solutions to address my requirement.
* Two tables might have millions of records with 50 columns in each table
* Expected output is same as what we get in SQL inner join
say For Eg, I have two tables Product , Order table.
*Product Table *
id Name
P1 ipad
P2 iphone 4
P3 iphone 5
*Order Table*
id order date product_id
O1 1-Dec-2012 P1
O2 1-Dec-2012 P2
O3 2-Dec-2012 P2
*Expected Output: *I want to show the details in UI as below [SQL inner
join ]
O1 01-Dec-2012 ipad
O2 1-Dec-2012 iPhone 4
O3 2-Dec-2012 iPhone 5
I tried setting up two solr cores , Product core & Order Core
*Option 1: Using Solr Join*
I got expected result but i was able to get columns only from one core (ie)
total 3 records but only product table columns
http://<….>/product/select?q=*&fq={!join from=product_id to=id
fromIndex=order}*
*Option 2: Using shards*
Created third core, but number of records is sum of(Product core + Order
Core) as documents are of different types and they are all unique(ie) 6
records
So how could i generate a single document with all fields from two
different document types in different cores.
On Sat, Nov 30, 2013 at 8:04 AM, Walter Underwood <wu...@wunderwood.org>wrote:
> 1. Flatten the data into a single table.
>
> 2. Solr does not seem like a good solution for order data, especially live
> orders that need to be transactional. That is a great match to a standard
> relational DB.
>
> wunder
>
> On Nov 30, 2013, at 12:15 AM, subacini Arunkumar <su...@gmail.com>
> wrote:
>
> > Hi
> >
> > We are using solr 4.4 . Please let me know the possible solutions to
> > address my requirement.
> >
> > We have to fetch data from two tables Product , Order table.
> >
> > Product Table
> >
> > id Name
> > P1 ipad
> > P2 iphone 4
> > P3 iphone 5
> >
> >
> > Order Table
> >
> > id order date product_id
> > O1 1-Dec-2012 P1
> > O2 1-Dec-2012 P2
> > O3 2-Dec-2012 P2
> >
> > I want to show the details in UI as below
> >
> > O1 01-Dec-2012
> >
> >
> > On Sat, Nov 30, 2013 at 12:13 AM, subacini Arunkumar <subacini@gmail.com
> >wrote:
> >
> >> Hi
> >>
> >> We are using solr 4.4 . Please let me know the possible solutions to
> >> address my requirement.
> >>
> >> We have to fetch data from two tables Product , Order table.
> >>
> >> Product Table
> >>
> >> id Name
> >> P1 ipad
> >> P2 iphone 4
> >> P3 iphone 5
> >>
> >>
> >> Order Table
> >>
> >> id order date product_id
> >> O1
> >>
> >>
>
> --
> Walter Underwood
> wunder@wunderwood.org
>
>
>
>
Re:
Posted by Henrik Ossipoff Hansen <ho...@entertainment-trading.com>.
To expand a bit on the other replies, yes, your order data should definitely be denormalized into one single order scheme. We store orders this way in Solr, since near real-time search among live orders is a requirement for several of our systems.
Something non-Solr though - consider denormalizing your order data in your relational database as well. Sooner or later, you will get into trouble with keeping orders and associated products separated via normalization - unless you keep a history of all previous versions of a product, or you never change products. Say that a product changes its name one month after an order is placed - if you keep the data normalized, all previous orders will get the new name of the product - not the name it had when the order was placed. This behaviour is usually not sought after in my experience.
This would, of course, also make a direct map to Solr more straightforward.
--
Henrik Ossipoff Hansen
On 1. dec. 2013 at 02.06.54, subacini Arunkumar (subacini@gmail.com<ma...@gmail.com>) wrote:
Thanks Walter for the reply. Here is my complete requirement.
Please let me know the possible solutions to address my requirement.
* Two tables might have millions of records with 50 columns in each table
* Expected output is same as what we get in SQL inner join
say For Eg, I have two tables Product , Order table.
*Product Table *
id Name
P1 ipad
P2 iphone 4
P3 iphone 5
*Order Table*
id order date product_id
O1 1-Dec-2012 P1
O2 1-Dec-2012 P2
O3 2-Dec-2012 P2
*Expected Output: *I want to show the details in UI as below [SQL inner
join ]
O1 01-Dec-2012 ipad
O2 1-Dec-2012 iPhone 4
O3 2-Dec-2012 iPhone 5
I tried setting up two solr cores , Product core & Order Core
*Option 1: Using Solr Join*
I got expected result but i was able to get columns only from one core (ie)
total 3 records but only product table columns
http://<….>/product/select?q=*&fq={!join from=product_id to=id
fromIndex=order}*
*Option 2: Using shards*
Created third core, but number of records is sum of(Product core + Order
Core) as documents are of different types and they are all unique(ie) 6
records
So how could i generate a single document with all fields from two
different document types in different cores.
On Sat, Nov 30, 2013 at 8:04 AM, Walter Underwood <wu...@wunderwood.org>wrote:
> 1. Flatten the data into a single table.
>
> 2. Solr does not seem like a good solution for order data, especially live
> orders that need to be transactional. That is a great match to a standard
> relational DB.
>
> wunder
>
> On Nov 30, 2013, at 12:15 AM, subacini Arunkumar <su...@gmail.com>
> wrote:
>
> > Hi
> >
> > We are using solr 4.4 . Please let me know the possible solutions to
> > address my requirement.
> >
> > We have to fetch data from two tables Product , Order table.
> >
> > Product Table
> >
> > id Name
> > P1 ipad
> > P2 iphone 4
> > P3 iphone 5
> >
> >
> > Order Table
> >
> > id order date product_id
> > O1 1-Dec-2012 P1
> > O2 1-Dec-2012 P2
> > O3 2-Dec-2012 P2
> >
> > I want to show the details in UI as below
> >
> > O1 01-Dec-2012
> >
> >
> > On Sat, Nov 30, 2013 at 12:13 AM, subacini Arunkumar <subacini@gmail.com
> >wrote:
> >
> >> Hi
> >>
> >> We are using solr 4.4 . Please let me know the possible solutions to
> >> address my requirement.
> >>
> >> We have to fetch data from two tables Product , Order table.
> >>
> >> Product Table
> >>
> >> id Name
> >> P1 ipad
> >> P2 iphone 4
> >> P3 iphone 5
> >>
> >>
> >> Order Table
> >>
> >> id order date product_id
> >> O1
> >>
> >>
>
> --
> Walter Underwood
> wunder@wunderwood.org
>
>
>
>
Re:
Posted by Walter Underwood <wu...@wunderwood.org>.
1. Flatten the data into a single table.
2. Solr does not seem like a good solution for order data, especially live orders that need to be transactional. That is a great match to a standard relational DB.
wunder
On Nov 30, 2013, at 12:15 AM, subacini Arunkumar <su...@gmail.com> wrote:
> Hi
>
> We are using solr 4.4 . Please let me know the possible solutions to
> address my requirement.
>
> We have to fetch data from two tables Product , Order table.
>
> Product Table
>
> id Name
> P1 ipad
> P2 iphone 4
> P3 iphone 5
>
>
> Order Table
>
> id order date product_id
> O1 1-Dec-2012 P1
> O2 1-Dec-2012 P2
> O3 2-Dec-2012 P2
>
> I want to show the details in UI as below
>
> O1 01-Dec-2012
>
>
> On Sat, Nov 30, 2013 at 12:13 AM, subacini Arunkumar <su...@gmail.com>wrote:
>
>> Hi
>>
>> We are using solr 4.4 . Please let me know the possible solutions to
>> address my requirement.
>>
>> We have to fetch data from two tables Product , Order table.
>>
>> Product Table
>>
>> id Name
>> P1 ipad
>> P2 iphone 4
>> P3 iphone 5
>>
>>
>> Order Table
>>
>> id order date product_id
>> O1
>>
>>
--
Walter Underwood
wunder@wunderwood.org