how to perform a delta-import when related table is updated
My delta-import
(http://localhost:8983/solr/freemedia/dataimport?command=delta-import) does
not correctly update my solr fields.
Please see my data-config here:
entity name=freemedia query=select * from freemedia WHERE
categoryid0
deltaImportQuery=select * from freemedia WHERE updatedate lt;
getdate()
AND id='${dataimporter.delta.id}' AND categoryid0
deltaQuery=select id from freemedia where updatedate gt;
'${dataimporter.last_index_time}' AND categoryid0
entity name=lovecount query=select COUNT(id) as likes FROM
freemedialikes WHERE freemediaid=${freemedia.id}/entity
Now when a new item is inserted into [freemedialikes]
and I perform a delta-import, the Solr index does not show the total new
amount of likes. Only after I perform a full-import
(http://localhost:8983/solr/freemedia/dataimport?command=full-import) the
correct number is shown.
So the SQL is returning the correct results, I just don't know how to get
the updated likes count via the delta-import.
I have reloaded the data-config everytime I made a change.
--
View this message in context:
http://lucene.472066.n3.nabble.com/how-to-perform-a-delta-import-when-related-table-is-updated-tp4032587.html
Sent from the Solr - User mailing list archive at Nabble.com.
RE: how to perform a delta-import when related table is updated
Peter,
See http://wiki.apache.org/solr/DataImportHandler#Using_delta-import_command ,
then scroll down to where it says The deltaQuery in the above example only
detects changes in item but not in other tables... It shows you two ways to
do it.
Option 1: add a reference to the last_modified_date (or whatever) from the
child table in a where-in clause in the parent entity's deltaQuery.
Option 2: add a parentDeltaQuery on the child entity. This is a query that
tells DIH which parent-table keys need to update because of child table
updates. In other words, say your child's Delta Query says that child_id=1
changed. You might have for parentDeltaQuery something like: SELECT ID FROM
PARENT P WHERE P.CHILD_ID=${Child.ID} . While this can simplify things for you
and prevent you from not needing giant where-in clauses on the parent query,
it will double the number of queries that get issued to determine which
documents to update.
James Dyer
E-Commerce Systems
Ingram Content Group
(615) 213-4311
-Original Message-
From: PeterKerk [mailto:vettepa...@hotmail.com]
Sent: Friday, January 11, 2013 12:02 PM
To: solr-user@lucene.apache.org
Subject: how to perform a delta-import when related table is updated
My delta-import
(http://localhost:8983/solr/freemedia/dataimport?command=delta-import) does
not correctly update my solr fields.
Please see my data-config here:
entity name=freemedia query=select * from freemedia WHERE
categoryid0
deltaImportQuery=select * from freemedia WHERE updatedate lt;
getdate()
AND id='${dataimporter.delta.id}' AND categoryid0
deltaQuery=select id from freemedia where updatedate gt;
'${dataimporter.last_index_time}' AND categoryid0
entity name=lovecount query=select COUNT(id) as likes FROM
freemedialikes WHERE freemediaid=${freemedia.id}/entity
Now when a new item is inserted into [freemedialikes]
and I perform a delta-import, the Solr index does not show the total new
amount of likes. Only after I perform a full-import
(http://localhost:8983/solr/freemedia/dataimport?command=full-import) the
correct number is shown.
So the SQL is returning the correct results, I just don't know how to get
the updated likes count via the delta-import.
I have reloaded the data-config everytime I made a change.
--
View this message in context:
http://lucene.472066.n3.nabble.com/how-to-perform-a-delta-import-when-related-table-is-updated-tp4032587.html
Sent from the Solr - User mailing list archive at Nabble.com.
RE: how to perform a delta-import when related table is updated
Hi James,
Ok, so I did this:
entity name=freemedia query=select * from freemedia WHERE
categoryid0
deltaImportQuery=select * from freemedia WHERE updatedate lt;
getdate()
AND id='${dataimporter.delta.id}' AND categoryid0
deltaQuery=select id from freemedia where id in
(select freemediaid as
id from freemedialikes where createdate
'${dih.last_index_time}') or updatedate gt;
'${dataimporter.last_index_time}' AND categoryid0
I now get this error in the logfile:
SEVERE: Delta Import Failed
java.lang.IllegalArgumentException: deltaQuery has no column to resolve to
declared primary key pk='ID'
Now, my table looks like this:
CREATE TABLE [dbo].[freemedialikes](
[id] [int] IDENTITY(1,1) NOT NULL,
[userid] [nvarchar](50) NOT NULL,
[freemediaid] [int] NOT NULL,
[createdate] [datetime] NOT NULL,
CONSTRAINT [PK_freemedialikes] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY =
OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[freemedialikes] WITH CHECK ADD CONSTRAINT
[FK_freemedialikes_freemedia] FOREIGN KEY([freemediaid])
REFERENCES [dbo].[freemedia] ([id])
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[freemedialikes] CHECK CONSTRAINT
[FK_freemedialikes_freemedia]
GO
ALTER TABLE [dbo].[freemedialikes] ADD CONSTRAINT
[DF_freemedialikes_createdate] DEFAULT (getdate()) FOR [createdate]
GO
So in the deltaquery I thought I had to reference the freemediaid, like so:
select freemediaid as id from freemedialikes
Got the same error as above.
So then I thought since there was mention of a PK in the error I just
reference the PK of the childtable, didn't make sense, but hey :)
select id from freemedialikes w
But I got the same error again.
Any suggestions?
Thanks!
--
View this message in context:
http://lucene.472066.n3.nabble.com/how-to-perform-a-delta-import-when-related-table-is-updated-tp4032587p4032608.html
Sent from the Solr - User mailing list archive at Nabble.com.
RE: how to perform a delta-import when related table is updated
Awesome! This one line did the trick: entity name=freemedia pk=id query=select * from freemedia WHERE categoryid0 Thanks! -- View this message in context: http://lucene.472066.n3.nabble.com/how-to-perform-a-delta-import-when-related-table-is-updated-tp4032587p4032671.html Sent from the Solr - User mailing list archive at Nabble.com.
