Delta import query not working

classic Classic list List threaded Threaded
3 messages Options
Reply | Threaded
Open this post in threaded view
|

Delta import query not working

willbrindle
Hi,

I am very new to Solr but I have been playing around with it a bit and my imports are all working fine. However, now I wish to perform a delta import on my query and I'm just getting nothing.

I have the entity:

 <entity name="stock"
          query="SELECT CONCAT('stk_',id) AS id,part_no,name,description FROM stock_items"
          deltaQuery="SELECT CONCAT('stk_',id) AS id,part_no,name,description,updated_at FROM stock_items WHERE updated_at > '${dih.delta.last_index_time}'"
          deltaImportQuery="SELECT CONCAT('stk_',id) AS id,id AS id2,part_no,name,description FROM stock_items WHERE id2='${dih.delta.id}'">
   

I am not too sure if ${dih.delta.id} is supposed to be id or id2 but I have tried both and neither work. My output is something along the lines of:

{
  "responseHeader": {
    "status": 0,
    "QTime": 0
  },
  "initArgs": [
    "defaults",
    [
      "config",
      "data-config.xml"
    ]
  ],
  "command": "status",
  "status": "idle",
  "importResponse": "",
  "statusMessages": {
    "Time Elapsed": "0:0:16.778",
    "Total Requests made to DataSource": "2",
    "Total Rows Fetched": "0",
    "Total Documents Skipped": "0",
    "Delta Dump started": "2015-02-05 16:17:54",
    "Identifying Delta": "2015-02-05 16:17:54",
    "Deltas Obtained": "2015-02-05 16:17:54",
    "Building documents": "2015-02-05 16:17:54",
    "Total Changed Documents": "0",
    "Delta Import Failed": "2015-02-05 16:17:54"
  },
  "WARNING": "This response format is experimental.  It is likely to change in the future."
}

My full import query is working fine.

Thanks.
Reply | Threaded
Open this post in threaded view
|

Re: Delta import query not working

Dan Davis-2
It looks like you are returning the transformed ID, along with some other
fields, in the deltaQuery command.    deltaQuery should only return the ID,
without the "stk_" prefix, and then deltaImportQuery should retrieve the
transformed ID.   I'd suggest:

<entity ...
 deltaQuery="SELECT id WHERE updated_at > '${dih.delta.last_index_time}'"
 deltaImportQuery="SELECT CONCAT('stk_',id) AS id, part_no, name,
description FROM stock_items WHERE id='${dih.delta.id}'">

I'm not sure which RDBMS you are using, but you probably don't need to work
around the column names at all.


On Thu, Feb 5, 2015 at 5:18 PM, willbrindle <[hidden email]> wrote:

> Hi,
>
> I am very new to Solr but I have been playing around with it a bit and my
> imports are all working fine. However, now I wish to perform a delta import
> on my query and I'm just getting nothing.
>
> I have the entity:
>
>  <entity name="stock"
>           query="SELECT CONCAT('stk_',id) AS id,part_no,name,description
> FROM
> stock_items"
>           deltaQuery="SELECT CONCAT('stk_',id) AS
> id,part_no,name,description,updated_at FROM stock_items WHERE updated_at >
> '${dih.delta.last_index_time}'"
>           deltaImportQuery="SELECT CONCAT('stk_',id) AS id,id AS
> id2,part_no,name,description FROM stock_items WHERE id2='${dih.delta.id
> }'">
>
>
> I am not too sure if ${dih.delta.id} is supposed to be id or id2 but I
> have
> tried both and neither work. My output is something along the lines of:
>
> {
>   "responseHeader": {
>     "status": 0,
>     "QTime": 0
>   },
>   "initArgs": [
>     "defaults",
>     [
>       "config",
>       "data-config.xml"
>     ]
>   ],
>   "command": "status",
>   "status": "idle",
>   "importResponse": "",
>   "statusMessages": {
>     "Time Elapsed": "0:0:16.778",
>     "Total Requests made to DataSource": "2",
>     "Total Rows Fetched": "0",
>     "Total Documents Skipped": "0",
>     "Delta Dump started": "2015-02-05 16:17:54",
>     "Identifying Delta": "2015-02-05 16:17:54",
>     "Deltas Obtained": "2015-02-05 16:17:54",
>     "Building documents": "2015-02-05 16:17:54",
>     "Total Changed Documents": "0",
>     "Delta Import Failed": "2015-02-05 16:17:54"
>   },
>   "WARNING": "This response format is experimental.  It is likely to change
> in the future."
> }
>
> My full import query is working fine.
>
> Thanks.
>
>
>
> --
> View this message in context:
> http://lucene.472066.n3.nabble.com/Delta-import-query-not-working-tp4184280.html
> Sent from the Solr - User mailing list archive at Nabble.com.
>
Reply | Threaded
Open this post in threaded view
|

Re: Delta import query not working

Dan Davis-2
It also should be ${dataimporter.last_index_time}

Also, that's two queries - an outer query to get the IDs that are modified,
and another query (done repeatedly) to get the data.   You can go faster
using a parameterized data import as described in the wiki:

http://wiki.apache.org/solr/DataImportHandlerDeltaQueryViaFullImport

Hope this helps,

Dan

On Thu, Feb 5, 2015 at 9:30 PM, Dan Davis <[hidden email]> wrote:

> It looks like you are returning the transformed ID, along with some other
> fields, in the deltaQuery command.    deltaQuery should only return the ID,
> without the "stk_" prefix, and then deltaImportQuery should retrieve the
> transformed ID.   I'd suggest:
>
> <entity ...
>  deltaQuery="SELECT id WHERE updated_at > '${dih.delta.last_index_time}'"
>  deltaImportQuery="SELECT CONCAT('stk_',id) AS id, part_no, name,
> description FROM stock_items WHERE id='${dih.delta.id}'">
>
> I'm not sure which RDBMS you are using, but you probably don't need to
> work around the column names at all.
>
>
> On Thu, Feb 5, 2015 at 5:18 PM, willbrindle <[hidden email]> wrote:
>
>> Hi,
>>
>> I am very new to Solr but I have been playing around with it a bit and my
>> imports are all working fine. However, now I wish to perform a delta
>> import
>> on my query and I'm just getting nothing.
>>
>> I have the entity:
>>
>>  <entity name="stock"
>>           query="SELECT CONCAT('stk_',id) AS id,part_no,name,description
>> FROM
>> stock_items"
>>           deltaQuery="SELECT CONCAT('stk_',id) AS
>> id,part_no,name,description,updated_at FROM stock_items WHERE updated_at >
>> '${dih.delta.last_index_time}'"
>>           deltaImportQuery="SELECT CONCAT('stk_',id) AS id,id AS
>> id2,part_no,name,description FROM stock_items WHERE id2='${dih.delta.id
>> }'">
>>
>>
>> I am not too sure if ${dih.delta.id} is supposed to be id or id2 but I
>> have
>> tried both and neither work. My output is something along the lines of:
>>
>> {
>>   "responseHeader": {
>>     "status": 0,
>>     "QTime": 0
>>   },
>>   "initArgs": [
>>     "defaults",
>>     [
>>       "config",
>>       "data-config.xml"
>>     ]
>>   ],
>>   "command": "status",
>>   "status": "idle",
>>   "importResponse": "",
>>   "statusMessages": {
>>     "Time Elapsed": "0:0:16.778",
>>     "Total Requests made to DataSource": "2",
>>     "Total Rows Fetched": "0",
>>     "Total Documents Skipped": "0",
>>     "Delta Dump started": "2015-02-05 16:17:54",
>>     "Identifying Delta": "2015-02-05 16:17:54",
>>     "Deltas Obtained": "2015-02-05 16:17:54",
>>     "Building documents": "2015-02-05 16:17:54",
>>     "Total Changed Documents": "0",
>>     "Delta Import Failed": "2015-02-05 16:17:54"
>>   },
>>   "WARNING": "This response format is experimental.  It is likely to
>> change
>> in the future."
>> }
>>
>> My full import query is working fine.
>>
>> Thanks.
>>
>>
>>
>> --
>> View this message in context:
>> http://lucene.472066.n3.nabble.com/Delta-import-query-not-working-tp4184280.html
>> Sent from the Solr - User mailing list archive at Nabble.com.
>>
>
>