Полнотекстовый поиск

DuckDB поддерживает полнотекстовый поиск через fts расширение. Полнотекстовый индекс позволяет быстро искать все вхождения отдельных слов в более длинных текстовых строках.

Пример: Корпус Шекспира

Вот пример создания полнотекстового индекса пьес Шекспира.

CREATE TABLE corpus AS
    SELECT * FROM 'https://blobs.duckdb.org/data/shakespeare.parquet';

DESCRIBE corpus;

column_name	column_type	null	key	default	extra
line_id	VARCHAR	YES	NULL	NULL	NULL
play_name	VARCHAR	YES	NULL	NULL	NULL
line_number	VARCHAR	YES	NULL	NULL	NULL
speaker	VARCHAR	YES	NULL	NULL	NULL
text_entry	VARCHAR	YES	NULL	NULL	NULL

Текст каждой строки находится в text_entry, а уникальный ключ для каждой строки — в line_id.

Создание индекса полнотекстового поиска

Сначала мы создаем индекс, указав имя таблицы, столбец уникального идентификатора и столбец(ы) для индексации. Мы будем индексировать только столбец text_entry, который содержит текст строк в пьесе.

PRAGMA create_fts_index('corpus', 'line_id', 'text_entry');

Теперь таблица готова к запросу с использованием функции ранжирования Okapi BM25. Строки без совпадений возвращают нулевой рейтинг.

Что Шекспир говорит о масле?

SELECT
    fts_main_corpus.match_bm25(line_id, 'butter') AS score,
    line_id, play_name, speaker, text_entry
FROM corpus
WHERE score IS NOT NULL
ORDER BY score DESC;

score	line_id	play_name	speaker	text_entry
4.427313429798464	H4/2.4.494	Henry IV	Carrier	As fat as butter.
3.836270302568675	H4/1.2.21	Henry IV	FALSTAFF	prologue to an egg and butter.
3.836270302568675	H4/2.1.55	Henry IV	Chamberlain	They are up already, and call for eggs and butter;
3.3844488405497115	H4/4.2.21	Henry IV	FALSTAFF	toasts-and-butter, with hearts in their bellies no
3.3844488405497115	H4/4.2.62	Henry IV	PRINCE HENRY	already made thee butter. But tell me, Jack, whose
3.3844488405497115	AWW/4.1.40	Alls well that ends well	PAROLLES	butter-womans mouth and buy myself another of
3.3844488405497115	AYLI/3.2.93	As you like it	TOUCHSTONE	right butter-womens rank to market.
3.3844488405497115	KL/2.4.132	King Lear	Fool	kindness to his horse, buttered his hay.
3.0278411214953107	AWW/5.2.9	Alls well that ends well	Clown	henceforth eat no fish of fortunes buttering.
3.0278411214953107	MWW/2.2.260	Merry Wives of Windsor	FALSTAFF	Hang him, mechanical salt-butter rogue! I will
3.0278411214953107	MWW/2.2.284	Merry Wives of Windsor	FORD	rather trust a Fleming with my butter, Parson Hugh
3.0278411214953107	MWW/3.5.7	Merry Wives of Windsor	FALSTAFF	Ill have my brains taen out and buttered, and give
3.0278411214953107	MWW/3.5.102	Merry Wives of Windsor	FALSTAFF	to heat as butter; a man of continual dissolution
2.739219044070792	H4/2.4.115	Henry IV	PRINCE HENRY	Didst thou never see Titan kiss a dish of butter?

В отличие от стандартных индексов, полнотекстовые индексы не обновляются автоматически при изменении исходных данных, поэтому вам необходимо PRAGMA drop_fts_index(my_fts_index) и пересоздать его при необходимости.

Примечание о генерации таблицы корпуса

Для получения дополнительной информации см. статью в блоге “Генерация корпуса Шекспира для полнотекстового поиска из JSON”

Столбцы: line_id, play_name, line_number, speaker, text_entry.
Для работы полнотекстового поиска нам нужен уникальный ключ для каждой строки.
line_id KL/2.4.132 означает Король Лир, акт 2, сцена 4, строка 132.