Solr DIH Delta Import with Compound Primary Keys?

Question

My Solr data source is an SQL database where the primary key is a composite (i.e. two fields).

This is normal for my main DIH query , I just concatenate the fields and becomes my main Solr key. However, it is not clear from the documentation how I will write a delta import request to support this.

The documentation assumes that I need two queries - one for finding the primary key of the changed rows, and the other for actually receiving the individual documents corresponding to each of these keys. There is no example showing this for compound keys.

Ideally, I don’t want these two separate queries at all, it would reduce the database load if the two queries were simply combined, so the only difference between query and deltaQuery is the WHERE that the filters are based on last_changed .

So, if my main query :

 SELECT key1 || key2 as pk FROM table 

What do the corresponding deltaQuery (and / or deltaImportQuery ) look like?

I tried just adding the WHERE , but after starting the query, I got a warning about missing deltaImportQuery and then deltaImportQuery null pointer.

Answer 1

 query="SELECT key1 || key2 as id, ...other fields FROM table" deltaImportQuery="SELECT key1 || key2 as id, ... other fields FROM table where key1 = '${dataimporter.delta.key1}' and key2 = '${dataimporter.delta.key2}'" deltaQuery="SELECT key1 || key2 as id, key1, key2 FROM table WHERE lastUpdated > '${dataimporter.last_index_time}'" 

Assuming key1 and key2 are textual. Single quotes around $ {dataimporter.delta.key2} are not needed if key2 is numeric, for example.

Answer 2

Set deltaQuery to "select 1", which will call deltaImportQuery then just write deltaImportQuery with '$ {dataimporter.last_index_time}' in the where clause

therefore deltaQuery = "select 1" deltaImportQuery = "select * from a_table where lastUpdated> '$ {dataimporter.last_index_time}'"

Answer 3

There are two requests for deltaImport. The first (deltaQuery) is for determining what to index. For example, in it we can determine which identifier to index. The other is for defining data from these identifiers. Look at my example, I hope it helps you:

 <entity name="address" pk="address_id" query="SELECT * FROM address a" deltaImportQuery="SELECT * FROM address a where a.address_id > ${dataimporter.delta.id}" deltaQuery="select address_id as id from address where address_id=101010"> 

An important part of deltaImportQuery is $ {dataimporter.delta.id}. This is how we set our id from deltaQuery to deltaImportQuery.