Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Solr DIH delta-import with compound primary keys?

Tags:

solr

dih

My Solr data source is a SQL database where the primary key is compound (i.e. it's two fields).

This is fine for my main DIH query, I just concatenate the fields and that becomes my Solr primary key. However it's unclear from the documentation how I'd write a delta-import query to support this.

The documentation suggests I need two queries - one to find the primary key of the changed rows, and another to then actually retrieve the individual documents corresponding to each of those keys. There's no example showing this for compound keys though.

Ideally I don't want those two separate queries at all, it would put less load on the database if those two queries were simply combined such that the only difference between query and deltaQuery is the WHERE clause that filters based on last_changed.

So, if my main query is:

SELECT key1 || key2 as pk FROM table

What would the relevant deltaQuery (and/or deltaImportQuery) look like?

I tried just adding the WHERE clause but after the query ran I got a warning about the missing deltaImportQuery and then a null-pointer exception.

like image 211
Alnitak Avatar asked Dec 22 '09 11:12

Alnitak


People also ask

What is Delta-import in SOLR?

delta-import. For incremental imports and change detection. Only the SqlEntityProcessor supports delta imports. For example: http://localhost:8983/solr/dih/dataimport?command=delta-import . This command supports the same clean , commit , optimize and debug parameters as full-import command described below.

What is full-import and Delta-import in SOLR?

In other words, a full-import will execute exactly 1 query for each defined entity + N queries for each sub-entity, while a delta-import will execute 1 query to get given entity's changed elements list + N queries for each changed element + another N queries for each defined sub-entity.

What is dih in SOLR?

The Data Import Handler (DIH) provides a mechanism for importing content from a data store and indexing it. In addition to relational databases, DIH can index content from HTTP based data sources such as RSS and ATOM feeds, e-mail repositories, and structured XML where an XPath processor is used to generate fields.


1 Answers

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 text. The single quotes around ${dataimporter.delta.key2} wouldn't be needed if key2 is numeric for example.

like image 150
Rye Avatar answered Sep 18 '22 03:09

Rye