Full-Text-Search-Erweiterung

Full-Text Search ist eine Erweiterung für DuckDB, mit der sich Strings durchsuchen lassen, ähnlich der FTS5-Erweiterung von SQLite.

Installation und Laden

Die Erweiterung fts wird beim ersten Einsatz transparent aus dem offiziellen Erweiterungs-Repository automatisch geladen. Wenn Sie sie manuell installieren und laden möchten, führen Sie aus:

INSTALL fts;
LOAD fts;

Verwendung

Die Erweiterung fügt DuckDB zwei PRAGMA-Anweisungen hinzu: eine zum Anlegen und eine zum Löschen eines Index. Zusätzlich kommt das Skalarmakro stem hinzu, das intern von der Erweiterung verwendet wird.

PRAGMA create_fts_index

create_fts_index(input_table, input_id, *input_values, stemmer = 'porter',
                 stopwords = 'english', ignore = '(\\.|[^a-z])+',
                 strip_accents = 1, lower = 1, overwrite = 0)

PRAGMA, der einen FTS-Index für die angegebene Tabelle anlegt.

Name	Type	Description
input_table	VARCHAR	Qualifizierter Name der angegebenen Tabelle, z. B. 'table_name' oder 'main.table_name'
input_id	VARCHAR	Spaltenname der Dokumentkennung, z. B. 'document_identifier'
input_values...	VARCHAR	Spaltennamen der zu indizierenden Textfelder (vararg), z. B. 'text_field_1', 'text_field_2', …, 'text_field_N', oder '\*' für alle Spalten vom Typ VARCHAR in input_table
stemmer	VARCHAR	Der zu verwendende Stemmer-Typ. Einer von 'arabic', 'basque', 'catalan', 'danish', 'dutch', 'english', 'finnish', 'french', 'german', 'greek', 'hindi', 'hungarian', 'indonesian', 'irish', 'italian', 'lithuanian', 'nepali', 'norwegian', 'porter', 'portuguese', 'romanian', 'russian', 'serbian', 'spanish', 'swedish', 'tamil', 'turkish' oder 'none', wenn kein Stemming verwendet werden soll. Standard ist 'porter'
stopwords	VARCHAR	Qualifizierter Name einer Tabelle mit einer einzelnen VARCHAR-Spalte der gewünschten Stoppwörter, oder 'none', wenn keine Stoppwörter verwendet werden sollen. Standard ist 'english' für eine vordefinierte Liste von 571 englischen Stoppwörtern
ignore	VARCHAR	Regulärer Ausdruck der zu ignorierenden Muster. Standard ist `’(\.
strip_accents	BOOLEAN	Ob Akzente entfernt werden sollen (z. B. á zu a). Standard ist 1
lower	BOOLEAN	Ob der gesamte Text in Kleinbuchstaben umgewandelt werden soll. Standard ist 1
overwrite	BOOLEAN	Ob ein bestehender Index auf einer Tabelle überschrieben werden soll. Standard ist 0

Dieser PRAGMA legt den Index unter einem neu erstellten Schema an. Das Schema wird nach der Eingabetabelle benannt: Wird ein Index auf der Tabelle 'main.table_name' erstellt, heißt das Schema 'fts_main_table_name'.

PRAGMA drop_fts_index

drop_fts_index(input_table)

Löscht einen FTS-Index für die angegebene Tabelle.

Name	Type	Description
input_table	VARCHAR	Qualifizierter Name der Eingabetabelle, z. B. 'table_name' oder 'main.table_name'

Funktion match_bm25

match_bm25(input_id, query_string, fields := NULL, k := 1.2, b := 0.75, conjunctive := 0)

Wenn ein Index gebaut ist, wird dieses Retrieval-Makro angelegt, mit dem der Index durchsucht werden kann.

Name	Type	Description
input_id	VARCHAR	Spaltenname der Dokumentkennung, z. B. 'document_identifier'
query_string	VARCHAR	Der String, nach dem im Index gesucht werden soll
fields	VARCHAR	Kommagetrennte Liste der zu durchsuchenden Felder, z. B. 'text_field_2, text_field_N'. Standard ist NULL, um alle indizierten Felder zu durchsuchen
k	DOUBLE	Parameter k1 im Okapi-BM25-Retrievalmodell. Standard ist 1.2
b	DOUBLE	Parameter b im Okapi-BM25-Retrievalmodell. Standard ist 0.75
conjunctive	BOOLEAN	Ob die Abfrage konjunktiv sein soll, d. h. alle Terme im Query-String müssen vorkommen, damit ein Dokument gefunden wird

Funktion stem

stem(input_string, stemmer)

Reduziert Wörter auf ihren Stamm. Wird intern von der Erweiterung verwendet.

Name	Type	Description
input_string	VARCHAR	Die zu stemmende Spalte oder Konstante.
stemmer	VARCHAR	Der zu verwendende Stemmer-Typ. Einer von 'arabic', 'basque', 'catalan', 'danish', 'dutch', 'english', 'finnish', 'french', 'german', 'greek', 'hindi', 'hungarian', 'indonesian', 'irish', 'italian', 'lithuanian', 'nepali', 'norwegian', 'porter', 'portuguese', 'romanian', 'russian', 'serbian', 'spanish', 'swedish', 'tamil', 'turkish' oder 'none', wenn kein Stemming verwendet werden soll.

Beispielverwendung

Legen Sie eine Tabelle an und füllen Sie sie mit Textdaten:

CREATE TABLE documents (
    document_identifier VARCHAR,
    text_content VARCHAR,
    author VARCHAR,
    doc_version INTEGER
);
INSERT INTO documents
    VALUES ('doc1',
            'The mallard is a dabbling duck that breeds throughout the temperate.',
            'Hannes Mühleisen',
            3),
           ('doc2',
            'The cat is a domestic species of small carnivorous mammal.',
            'Laurens Kuiper',
            2
           );

Bauen Sie den Index und machen Sie sowohl die Spalte text_content als auch author durchsuchbar.

PRAGMA create_fts_index(
    'documents', 'document_identifier', 'text_content', 'author'
);

Durchsuchen Sie den Index des Felds author nach Dokumenten, die von Muhleisen verfasst wurden. Das findet doc1:

SELECT document_identifier, text_content, score
FROM (
    SELECT *, fts_main_documents.match_bm25(
        document_identifier,
        'Muhleisen',
        fields := 'author'
    ) AS score
    FROM documents
) sq
WHERE score IS NOT NULL
  AND doc_version > 2
ORDER BY score DESC;

document_identifier	text_content	score
doc1	The mallard is a dabbling duck that breeds throughout the temperate.	0.0

Suchen Sie nach Dokumenten über small cats. Das findet doc2:

SELECT document_identifier, text_content, score
FROM (
    SELECT *, fts_main_documents.match_bm25(
        document_identifier,
        'small cats'
    ) AS score
    FROM documents
) sq
WHERE score IS NOT NULL
ORDER BY score DESC;

document_identifier	text_content	score
doc2	The cat is a domestic species of small carnivorous mammal.	0.0

Warnung Der FTS-Index wird nicht automatisch aktualisiert, wenn sich die Eingabetabelle ändert. Ein Workaround für diese Einschränkung ist, den Index neu zu erstellen, um ihn zu aktualisieren.