Bug 35345 - Pass custom SQL to rebuild_elasticsearch.pl to determine which records to index
Summary: Pass custom SQL to rebuild_elasticsearch.pl to determine which records to index
Status: RESOLVED FIXED
Alias: None
Product: Koha
Classification: Unclassified
Component: Searching - Elasticsearch (show other bugs)
Version: Main
Hardware: All All
: P5 - low enhancement (vote)
Assignee: Thomas Klausner
QA Contact:
URL:
Keywords:
Depends on:
Blocks:
 
Reported: 2023-11-16 09:45 UTC by Thomas Klausner
Modified: 2024-05-28 20:15 UTC (History)
4 users (show)

See Also:
Change sponsored?: Sponsored
Patch complexity: Trivial patch
Documentation contact:
Documentation submission:
Text to go in the release notes:
This adds a `--where` parameter to the `rebuild_elasticsearch.pl` script that allows to flexibly select the records for reindexing with SQL. Examples would be the authority type or ranges and lists of biblionumbers and authids.
Version(s) released in:
24.05.00,23.11.06


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
Bug 35345: Add --where option to rebuild_elasticsearch.pl (4.84 KB, patch)
2024-03-29 09:22 UTC, Thomas Klausner
Details | Diff | Splinter Review
Bug 35345: Add --where option to rebuild_elasticsearch.pl (4.95 KB, patch)
2024-03-30 14:43 UTC, Nick Clemens (kidclamp)
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>
Comment 9 Emily Lamancusa 2024-03-27 17:51:57 UTC
This is a great idea! However, the patch contains many Perltidy/formatting changes that are unrelated to the enhancement. Please separate out your code changes into one patch, and the Perltidy/formatting changes into a second patch.
Comment 10 Thomas Klausner 2024-03-29 09:22:49 UTC
Created attachment 164118 [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.

Sponsored-by: Steiermärkische Landesbibliothek
Sponsored-by: HKS3 / koha-support.eu
Comment 11 Thomas Klausner 2024-03-29 09:26:52 UTC
I've now cleaned up the patch to only include the actual changes.

Not sure if I should now also change the status (and if yes, to what..)
Comment 12 Nick Clemens (kidclamp) 2024-03-30 14:43:58 UTC
Created attachment 164157 [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.

Sponsored-by: Steiermärkische Landesbibliothek
Sponsored-by: HKS3 / koha-support.eu

Signed-off-by: David Nind <david@davidnind.com>
Signed-off-by: Nick Clemens <nick@bywatersolutions.com>
Comment 13 Nick Clemens (kidclamp) 2024-03-30 14:46:37 UTC
Restored David's SO line, the changes seemed mostly cosmetic between the patches.

Works, is straightforward. I would have asked for test coverage, but there are currently no tests for  MetadataIterator or the iterator functions, so letting this one move forward, but filing a bug for those tests. RM call on whether they are a blocker for this
Comment 14 Katrin Fischer 2024-04-22 10:28:27 UTC
An SQL example for documentation might be a nice addition :)
Comment 15 Katrin Fischer 2024-04-22 10:40:16 UTC
Pushed for 24.05!

Well done everyone, thank you!
Comment 16 Fridolin Somers 2024-04-22 10:41:35 UTC
A close sibling of Bug 29921 ;)
Comment 17 Fridolin Somers 2024-05-23 14:50:31 UTC
Small useful enhancement,

Pushed to 23.11.x for 23.11.06
Comment 18 Lucas Gass 2024-05-28 20:15:11 UTC
Enhancement will not be backported to 23.05.x