Bug 35345 - Pass custom SQL to rebuild_elasticsearch.pl
Summary: Pass custom SQL to rebuild_elasticsearch.pl
Status: Signed Off
Alias: None
Product: Koha
Classification: Unclassified
Component: Searching - Elasticsearch (show other bugs)
Version: master
Hardware: All All
: P5 - low new feature (vote)
Assignee: Bugs List
QA Contact:
URL:
Keywords:
Depends on:
Blocks:
 
Reported: 2023-11-16 09:45 UTC by Thomas Klausner
Modified: 2024-02-12 02:03 UTC (History)
0 users

See Also:
Change sponsored?: Sponsored
Patch complexity: Trivial patch
Documentation contact:
Documentation submission:
Text to go in the release notes:
Version(s) released in:


Attachments
Bug 35345: Add --where option to rebuild_elasticsearch.pl (21.35 KB, patch)
2023-11-16 13:00 UTC, Thomas Klausner
Details | Diff | Splinter Review
Bug 35345: Add --where option to rebuild_elasticsearch.pl (21.40 KB, patch)
2024-02-12 02:03 UTC, David Nind
Details | Diff | Splinter Review

Note You need to log in before you can comment on or make changes to this bug.
Description Thomas Klausner 2023-11-16 09:45:33 UTC
Sometimes we need to only re-index a subset of our bibliographic data or authorities. Currently this is only possible by enumerating all id (-bn or -ai), which does not work well when indexing eg 100.000 items of a 2.000.000 DB. Re-indexing everything is also overkill.

I propose adding an `--sql` Flag to misc/search_tools/rebuild_elasticsearch.pl which can take arbitrary SQL (that of course has to match the respective tables) and adds it as an additional param to the resultset to index:

Example:

* Only index auths of type Geograpy: misc/search_tools/rebuild_elasticsearch.pl -a -v --sql "authtypecode = 'GEOGR_NAME'"

* Only index books with id 100-150: misc/search_tools/rebuild_elasticsearch.pl -b -v --sql "biblionumber between 100 and 150"

In this first version only columns available in the primary tables (biblio, auth_header) are available. Later we could add command line options for joining other tables (eg biblio_metadata).
Comment 1 Katrin Fischer 2023-11-16 09:48:25 UTC
Hi Thomas, I like this idea. Just a note: we have similar in other jobs and I believe it's usually named --where. So my suggestion would be to use that as a parameter name for some consistency :)
Comment 2 Thomas Klausner 2023-11-16 10:32:54 UTC
I don't care about --sql or --where, and -w is also still not used, so I'll use that (and modify my patch, which I haven't commited yet)

What other tools are using `--where`? Or are you talking about non-Koha tools?
Comment 3 Katrin Fischer 2023-11-16 10:36:21 UTC
git grep "\-\-where" 

touch_all_items.pl
update_patrons_category.pl
delete_items.pl
... :)
Comment 4 Katrin Fischer 2023-11-16 10:37:01 UTC
rebuild_zebra.pl as well!
Comment 5 Thomas Klausner 2023-11-16 11:17:19 UTC
(In reply to Katrin Fischer from comment #4)
> rebuild_zebra.pl as well!

rebuild_zebra.pl already has a --where flag :-)

(but it's not using DBIx::Class/Koha::BiblioUtils etc, but directly building a query and executing it via DBI)
Comment 6 Thomas Klausner 2023-11-16 13:00:18 UTC
Created attachment 159044 [details] [review]
Bug 35345: Add --where option to rebuild_elasticsearch.pl

Sometimes we need to only re-index a subset of our bibliographic data or authorities. Currently this is only possible by enumerating all id (-bn or -ai), which does not work well when indexing eg 100.000 items of a 2.000.000 DB. Re-indexing everything is also overkill.

This patch adds an `--where` flag to misc/search_tools/rebuild_elasticsearch.pl which can take arbitrary SQL (that of course has to match the respective tables) and adds it as an additional param to the resultset to index

To test, start koha-testing-docker with ElasticSearch enabled, for example via `ktd --es7 up

Before applying the patch, rebuild_elasticsearch will index all data:

Biblios:
$ misc/search_tools/rebuild_elasticsearch.pl -b -v
[12387] Checking state of biblios index
[12387] Indexing biblios
[12387] Committing final records...
[12387] Total 435 records indexed
(there might be a waring regarding a broken biblio, which can be ignored)

Auth:
$ misc/search_tools/rebuild_elasticsearch.pl -a -v
[12546] Checking state of authorities index
[12546] Indexing authorities
[12546] 1000 records processed
[12546] Committing final records...
[12546] Total 1706 records indexed

Now apply the patch

Biblio, limit by range of biblioid:
$ misc/search_tools/rebuild_elasticsearch.pl -b -v --where "biblionumber between 100 and 150"
[12765] Checking state of biblios index
[12765] Indexing biblios
[12765] Committing final records...
[12765] Total 50 records indexed

Note that only 50 records where indexed (instead of the whole set of 435 records)

Auth, limit by authtypecode:
$ misc/search_tools/rebuild_elasticsearch.pl -a -v --where "authtypecode = 'GEOGR_NAME'"
[12848] Checking state of authorities index
[12848] Indexing authorities
[12848] Committing final records...
[12848] Total 142 records indexed

Again, only 142 have been indexed.

Note that this patch also includes perltidied versions of the affected files (because the qa script complained)

Sponsored-by: Steiermärkische Landesbibliothek
Comment 7 Katrin Fischer 2023-11-16 19:03:33 UTC
(In reply to Thomas Klausner from comment #5)
> (In reply to Katrin Fischer from comment #4)
> > rebuild_zebra.pl as well!
> 
> rebuild_zebra.pl already has a --where flag :-)
> 
> (but it's not using DBIx::Class/Koha::BiblioUtils etc, but directly building
> a query and executing it via DBI)

Yes, you asked about examples and when checking I realized that the Zebra equivalent already had such a parameter as well. So having it for Elastic too makes even more sense.
Comment 8 David Nind 2024-02-12 02:03:43 UTC
Created attachment 162015 [details] [review]
Bug 35345: Add --where option to rebuild_elasticsearch.pl

Sometimes we need to only re-index a subset of our bibliographic data or authorities. Currently this is only possible by enumerating all id (-bn or -ai), which does not work well when indexing eg 100.000 items of a 2.000.000 DB. Re-indexing everything is also overkill.

This patch adds an `--where` flag to misc/search_tools/rebuild_elasticsearch.pl which can take arbitrary SQL (that of course has to match the respective tables) and adds it as an additional param to the resultset to index

To test, start koha-testing-docker with ElasticSearch enabled, for example via `ktd --es7 up

Before applying the patch, rebuild_elasticsearch will index all data:

Biblios:
$ misc/search_tools/rebuild_elasticsearch.pl -b -v
[12387] Checking state of biblios index
[12387] Indexing biblios
[12387] Committing final records...
[12387] Total 435 records indexed
(there might be a waring regarding a broken biblio, which can be ignored)

Auth:
$ misc/search_tools/rebuild_elasticsearch.pl -a -v
[12546] Checking state of authorities index
[12546] Indexing authorities
[12546] 1000 records processed
[12546] Committing final records...
[12546] Total 1706 records indexed

Now apply the patch

Biblio, limit by range of biblioid:
$ misc/search_tools/rebuild_elasticsearch.pl -b -v --where "biblionumber between 100 and 150"
[12765] Checking state of biblios index
[12765] Indexing biblios
[12765] Committing final records...
[12765] Total 50 records indexed

Note that only 50 records where indexed (instead of the whole set of 435 records)

Auth, limit by authtypecode:
$ misc/search_tools/rebuild_elasticsearch.pl -a -v --where "authtypecode = 'GEOGR_NAME'"
[12848] Checking state of authorities index
[12848] Indexing authorities
[12848] Committing final records...
[12848] Total 142 records indexed

Again, only 142 have been indexed.

Note that this patch also includes perltidied versions of the affected files (because the qa script complained)

Sponsored-by: Steiermärkische Landesbibliothek
Signed-off-by: David Nind <david@davidnind.com>