2025-06-13

Leichtgewichtige Text-Analytics-Workflows mit DuckDB

Petrica Leuca

Einleitung

Text Analytics ist ein zentraler Bestandteil vieler moderner Daten-Workflows und umfasst Aufgaben wie Keyword Matching, Full-Text Search und semantischen Vergleich. Herkömmliche Tools erfordern oft komplexe Pipelines und erhebliche Infrastruktur, was eine echte Hürde sein kann. DuckDB bietet eine performante SQL-Engine, die Text Analytics vereinfacht und strafft. In diesem Beitrag zeigen wir, wie man DuckDB nutzt, um fortgeschrittene Text Analytics in Python effizient umzusetzen.

Die folgende Implementierung läuft in einem marimo-Python-Notebook, das auf GitHub in unserem Examples-Repository verfügbar ist.

Datenvorbereitung

Wir arbeiten mit einem öffentlichen Datensatz auf Hugging Face, der englische Twitter-Nachrichten und ihre Klassifikation zu einer der folgenden Emotionen enthält: anger, fear, joy, love, sadness und surprise.

Mit DuckDB können wir Hugging-Face-Datensätze über das Präfix hf:// erreichen:

from_hf_rel = conn.read_parquet(
        "hf://datasets/dair-ai/emotion/unsplit/train-00000-of-00001.parquet",
        file_row_number=True
    )
from_hf_rel = from_hf_rel.select("""
    text,
    label as emotion_id,
    file_row_number as text_id
""")
from_hf_rel.to_table("text_emotions")

Wie man Hugging-Face-Datensätze mit DuckDB erreicht, steht im Beitrag „Access 150k+ Datasets from Hugging Face with DuckDB“.

In den obigen Daten haben wir nur die Kennung einer Emotion (emotion_id), ohne ihre beschreibende Information. Deshalb erzeugen wir aus der in der Datensatzbeschreibung angegebenen Liste eine Referenztabelle, indem wir die Python-Liste unnesten und den Index jedes Werts mit der Funktion generate_subscripts holen:

emotion_labels = ["sadness", "joy", "love", "anger", "fear", "surprise"]

from_labels_rel = conn.values([emotion_labels])
from_labels_rel = from_labels_rel.select("""
    unnest(col0) as emotion,
    generate_subscripts(col0, 1) - 1 as emotion_id
""")
from_labels_rel.to_table("emotion_ref")

Zuletzt definieren wir eine Relation, indem wir die beiden Tabellen joinen:

text_emotions_rel = conn.table("text_emotions").join(
    conn.table("emotion_ref"), condition="emotion_id"
)

Mit text_emotions_rel.to_view("text_emotions_v", replace=True) wird eine View namens text_emotions_v erzeugt, die in SQL-Zellen genutzt werden kann.

Wir plotten die Emotionsverteilung als Balkendiagramm, um ein erstes Verständnis unserer Daten zu bekommen:

Keyword Search

Keyword Search ist die grundlegendste Form der Textsuche: exakte Wörter oder Phrasen in Textfeldern mit SQL-Bedingungen wie CONTAINS, ILIKE oder anderen DuckDB-Textfunktionen. Sie ist schnell, braucht kein Preprocessing und eignet sich gut für strukturierte Queries wie das Filtern von Logs, das Matchen von Tags oder das Finden von Produktnamen.

Texte und ihr Emotionslabel zu holen, die die Phrase excited to learn enthalten, ist zum Beispiel eine Sache von filter auf der oben definierten Relation:

text_emotions_rel.filter("text ilike '%excited to learn%'").select("""
    emotion,
    substring(
        text,
        position('excited to learn' in text),
        len('excited to learn')
    ) as substring_text
""")

┌─────────┬──────────────────┐
│ emotion │  substring_text  │
│ varchar │     varchar      │
├─────────┼──────────────────┤
│ sadness │ excited to learn │
│ joy     │ excited to learn │
│ joy     │ excited to learn │
│ fear    │ excited to learn │
│ fear    │ excited to learn │
│ joy     │ excited to learn │
│ joy     │ excited to learn │
│ sadness │ excited to learn │
└─────────┴──────────────────┘

Ein üblicher Schritt in der Textverarbeitung ist, Text in Tokens (Keywords) zu zerlegen: Rohtext wird in kleinere Einheiten (typischerweise Wörter) gebrochen, die analysiert oder indexiert werden können. Dieser Prozess, Tokenization, hilft, unstrukturierten Text in eine strukturierte Form für Keyword Search zu bringen. In DuckDB lässt sich das mit der Funktion regexp_split_to_table umsetzen, die den Text anhand der angegebenen Regex splittet und jedes Keyword in einer Zeile zurückgibt.

Dieser Schritt ist case-sensitive, deshalb ist es wichtig, den gesamten Text vor der Verarbeitung in eine einheitliche Groß-/Kleinschreibung zu bringen (mit lcase oder ucase).

Im folgenden Snippet selektieren wir alle Keywords, indem wir den Text an einem oder mehreren Nicht-Wort-Zeichen splitten (alles außer [a-zA-Z0-9_]):

text_emotions_tokenized_rel = text_emotions_rel.select("""
    text_id,
    emotion,
    regexp_split_to_table(text, '\\W+') as token
""")

Beim Tokenisieren schließen wir üblicherweise häufige Wörter (wie and, the) aus, sogenannte Stopwords. In DuckDB setzen wir den Ausschluss mit einem ANTI JOIN auf eine kuratierte CSV-Datei auf GitHub um:

english_stopwords_rel = duckdb_conn.read_csv(
    "https://raw.githubusercontent.com/stopwords-iso/stopwords-en/refs/heads/master/stopwords-en.txt",
    header=False,
).select("column0 as token")

text_emotions_tokenized_rel.join(
    english_stopwords_rel,
    condition="token",
    how="anti",
).to_table("text_emotion_tokens")

Nachdem wir den Text tokenisiert und bereinigt haben, können wir Keyword Search umsetzen, indem wir den Match mit Ähnlichkeitsfunktionen wie Jaccard ranken:

text_token_rel = conn.table(
    "text_emotion_tokens"
).select("token, emotion, jaccard(token, 'learn') as jaccard_score")

text_token_rel = text_token_rel.max(
    "jaccard_score",
    groups="emotion, token",
    projected_columns="emotion, token"
)

text_token_rel.order("3 desc").limit(10)

┌──────────┬─────────┬────────────────────┐
│ emotion  │  token  │ max(jaccard_score) │
│ varchar  │ varchar │       double       │
├──────────┼─────────┼────────────────────┤
│ fear     │ learn   │                1.0 │
│ surprise │ learn   │                1.0 │
│ love     │ learn   │                1.0 │
│ joy      │ lerna   │                1.0 │
│ sadness  │ learn   │                1.0 │
│ fear     │ learner │                1.0 │
│ anger    │ learn   │                1.0 │
│ joy      │ leaner  │                1.0 │
│ fear     │ allaner │                1.0 │
│ anger    │ learner │                1.0 │
├──────────┴─────────┴────────────────────┤
│ 10 rows                       3 columns │
└─────────────────────────────────────────┘

Wir können die Daten auch visualisieren, um Insights zu gewinnen. Ein einfacher und wirksamer Ansatz ist, die häufigsten Wörter zu plotten. Indem wir Token-Vorkommen über den Datensatz zählen und sie in Bubble Plots darstellen, erkennen wir schnell dominante Themen, wiederholte Keywords oder ungewöhnliche Muster im Text. Zum Beispiel plotten wir die Daten mit einem Scatter-Facet-Plot pro Emotion:

Im obigen Plot sehen wir wiederholte Keywords wie feel - feeling, love - loved - loving. Um solche Daten zu deduplizieren, müssen wir uns den Wortstamm anschauen statt das Wort selbst. Das führt uns zur Full-Text Search.

Full-Text Search

Die Full-Text-Search-(FTS-)DuckDB-Extension ist eine experimentelle Extension, die zwei zentrale Full-Text-Search-Funktionen umsetzt:

die Funktion stem, um den Wortstamm zu holen;
die Funktion match_bm25, um den Best-Match-Score zu berechnen.

Wenden wir stem auf die Token-Spalte an, können wir den häufigsten Wortstamm in unseren Daten visualisieren:

Wir sehen, dass feel und love nur einmal erscheinen und neue Wortstämme geplottet werden, etwa support, surpris.

Während stem allein genutzt werden kann, braucht match_bm25 den Bau eines FTS-Index, eines speziellen Index, der schnelles und effizientes Suchen von Text ermöglicht, indem er die Wörter (Tokens) in einer Spalte indexiert:

conn.sql("""
    PRAGMA create_fts_index(
        "text_emotions",
        text_id,
        "text",
        stemmer = 'english',
        stopwords = 'english_stopwords',
        ignore = '(\\.|[^a-z])+',
        strip_accents = 1,
        lower = 1,
        overwrite = 1
    )
""")

Bei der FTS-Index-Erzeugung nutzen wir dieselbe Liste englischer Stopwords wie beim Tokenisieren, indem wir sie in einer Tabelle namens english_stopwords speichern. Der Index ist case-insensitive dank des Parameters lower, der den Text automatisch kleinschreibt.

Warnung Der Index kann nur auf Tabellen erzeugt werden und braucht eine eindeutige Kennung des Texts. Er muss außerdem neu gebaut werden, wenn die zugrunde liegenden Daten geändert wurden.

Sobald der Index erzeugt ist, können wir den Match zwischen der Spalte text und der Phrase excited to learn ranken:

text_emotions_rel
.select("""
    emotion,
    text,
    emotion_color,
    fts_main_text_emotions.match_bm25(
        text_id,
        'excited to learn'
    )::decimal(3, 2) as bm25_score
""")
.order("bm25_score desc")
.limit(10)

Von den 10 zurückgegebenen Texten, oben in einem Tabellenplot dargestellt, sind 2 schlechte Matches zu unserer Sucheingabe; wahrscheinlich, weil das BM25-Scoring durch häufige Terms oder Unterschiede in der Dokumentlänge verzerrt wird.

Semantische Suche

Im Vergleich zu Keyword- und Full-Text-Search berücksichtigt semantische Suche Bedeutung und Kontext des Texts. Statt nur nach exakten Wörtern zu schauen, nutzt sie Techniken wie Vector Embeddings, um die zugrunde liegenden Konzepte einzufangen. Semantische Suche, die case-insensitive ist, lässt sich in DuckDB mit der (ebenfalls experimentellen) Vector-Similarity-Search-Extension umsetzen.

Die Vector Embeddings einer (Liste von) Texten lassen sich mit der Bibliothek sentence-transformers und dem vortrainierten Modell all-MiniLM-L6-v2 berechnen:

from sentence_transformers import SentenceTransformer

model = SentenceTransformer('all-MiniLM-L6-v2')

def get_text_embedding_list(list_text: list[str]):
    """
    Return the list of normalized vector embeddings for list_text.
    """
    return model.encode(list_text, normalize_embeddings=True)

Zum Beispiel gibt get_text_embedding_list(['excited to learn']) zurück:

array([[ 3.14795598e-02, -6.66208193e-02,  1.05058309e-02,
         4.12571728e-02, -8.67664907e-03, -1.79746319e-02,
        ...
        -2.50727013e-02, -3.00881546e-03,  1.55055271e-02]], dtype=float32)

Wir registrieren die Modell-Inferenzfunktion als Python User Defined Function und erzeugen eine Tabelle mit einer Spalte vom Typ FLOAT[384], um die Embeddings zu laden:

conn.create_function(
    "get_text_embedding_list",
    get_text_embedding_list,
    return_type='FLOAT[384][]'
)

conn.sql("""
    create table text_emotion_embeddings (
        text_id integer,
        text_embedding FLOAT[384]
    )
""")

Mit der Python-UDF speichern wir die Modellausgabe in Batches in text_emotion_embeddings:

for i in range(num_batches):
    selection_query = (
        duckdb_conn.table("text_emotions")
        .order("text_id")
        .limit(batch_size, offset=batch_size*i)
        .select("*")
    )

    (
        selection_query.aggregate("""
            array_agg(text) as text_list,
            array_agg(text_id) as id_list,
            get_text_embedding_list(text_list) as text_emb_list
        """).select("""
            unnest(id_list) as text_id,
            unnest(text_emb_list) as text_embedding
        """)
    ).insert_into("text_emotion_embeddings")

Über Modell-Inferenz in DuckDB haben wir im Beitrag Machine Learning Prototyping with DuckDB and scikit-learn geschrieben.

Jetzt können wir semantische Suche durchführen, indem wir die Cosine Distance zwischen dem Vector Embedding unseres Suchtexts excited to learn und dem Embedding des Felds text nutzen:

input_text_emb_rel = conn.sql("""
    select get_text_embedding_list(['excited to learn'])[1] as input_text_embedding
""")

text_emotions_rel
.join(conn.table("text_emotion_embeddings"), condition="text_id")
.join(input_text_emb_rel, condition="1=1")
.select("""
        text,
        emotion,
        emotion_color,
        array_cosine_distance(
            text_embedding,
            input_text_embedding
        )::decimal(3, 2) as cosine_distance_score
    """)
.order("cosine_distance_score asc")
.limit(10)

Interessant: Die Phrase i am excited to learn and feel privileged to be here hat es in unserer semantischen Suche nicht in die Top 10 geschafft!

Similarity Joins

Vector Embeddings sind vor allem für Suchmaschinen bekannt, lassen sich aber in vielen Text-Analytics-Use-Cases nutzen, etwa Topic Grouping, Klassifikation oder semantisches Matching zwischen Dokumenten. Die VSS-Extension bietet Vector Similarity Joins, mit denen sich solche Analysen durchführen lassen.

Zum Beispiel zeigen wir im folgenden Heatmap-Chart die Zahl der Texte für jede Kombination von Emotionslabels, wobei die x-Achse dem semantischen Matching zwischen Text und Emotion entspricht, die y-Achse der klassifizierten Emotion und die Farbe die Zahl der Texte je Paar angibt:

Beste gematchte Emotion versus klassifizierte Emotion

Besonders auffällig: Von den 6 Emotionen hat nur sadness einen starken semantischen Match mit dem Text, der mit demselben Label klassifiziert ist. Wie bei Full-Text Search wird semantische Suche von Unterschieden in der Dokumentlänge beeinflusst (hier das Emotions-Keyword versus ein Text).

Hybrid Search

Jede Suchart hat ihre eigene Anwendbarkeit, aber wir haben gesehen, dass manche Ergebnisse nicht wie erwartet sind:

Keyword Search und Full-Text Search berücksichtigen die Wortbedeutung nicht;
semantische Suche hat Synonyme höher bewertet als den Suchtext.

In der Praxis werden die drei Suchmethoden kombiniert und als „Hybrid Search“ genutzt, um Relevanz und Genauigkeit zu verbessern. Wir beginnen damit, den Score für jede Suchart zu berechnen, mit eigener Logik, etwa einer Prüfung auf die Emotion:

if(
    emotion = 'joy' and contains(text, 'excited to learn'),
    1,
    0
) exact_match_score,

fts_main_text_emotions.match_bm25(
        text_id,
        'excited to learn'
)::decimal(3, 2) as bm25_score,

array_cosine_similarity(
    text_embedding,
    input_text_embedding
)::decimal(3, 2) as cosine_similarity_score

Der BM25-Score wird absteigend gerankt und die Cosine Distance aufsteigend. In Hybrid Search nutzen wir den Score array_cosine_similarity, um dieselbe Sortierreihenfolge sicherzustellen (hier absteigend).

Cosine Similarity = 1 − Cosine Distance

Weil der BM25-Score theoretisch unbeschränkt sein kann, müssen wir den Score skalieren auf das Intervall [0, 1], indem wir Min-Max-Normalisierung umsetzen:

max(bm25_score) over () as max_bm25_score,
min(bm25_score) over () as min_bm25_score,
(bm25_score - min_bm25_score) / nullif((max_bm25_score - min_bm25_score), 0) as norm_bm25_score

Der Hybrid-Search-Score wird berechnet, indem BM25- und Cosine-Similarity-Scores gewichtet werden:

if(
    exact_match_score = 1,
    exact_match_score,
    cast(
        0.3 * coalesce(norm_bm25_score, 0) +
        0.7 * coalesce(cosine_similarity_score, 0)
        as
        decimal(3, 2)
    )
) as hybrid_score

Und hier sind die Ergebnisse! Viel besser, finden Sie nicht?

Fazit

In diesem Beitrag haben wir gezeigt, wie DuckDB für Text Analytics genutzt werden kann, indem Keyword-, Full-Text- und semantische Suchtechniken kombiniert werden. Mit den experimentellen Extensions fts und vss und der Bibliothek sentence-transformers haben wir gezeigt, wie DuckDB sowohl traditionelle als auch moderne Text-Analytics-Workflows unterstützen kann.