Aufbau eines Wörterbuches

4. April 2009 , Geschrieben von Olaf Helper Veröffentlicht in #T-SQL

Ursprüngliche Intension war es eine Art Wörterbuch aufzubauen, um Texte „taggen“ zu können bzw. eine Art „Volltextsuche“ der minimalistischen Art Marke „Eigenbau“ zu realisieren und das möglichst performant. Dazu kam dann noch eine Forum-Anfrage, die in eine ähnliche Richtung geht und eine sportliche Herausforderung für mich darstellte.

Vereinfacht gesagt war es Ziel: Man hat Texte, die es in einzelne Wörter zu zergliedern gilt, welche man dann in einer Tabelle als Referenz ablegt.

Das Script ist wie bei mir üblich so aufgebaut, das man es beliebig oft ausführen kann, ohne das es zu Problemen führt.

Als Quelle dienen dabei Plain Text Dateien; sorry, aber ich hatte einfach keine sonderliche Lust nun auch noch HTML-Tag und ähnliches Gedöns raus zu parsen; man (ich) ist halt doch etwas bequem.

Womit sich das nächste Problem stellt: Woher brauchbare Texte finden, die man als Quelle nutzen kann? Das ist das eigentliche Problem an der Geschichte, hier muss jeder selbst zusehen, was er nutzen kann und will. Für mein Beispiel hier habe ich mir im Netz ein paar Dateien dazu rausgesucht; wie gesagt, ist nicht einfach heutzutage noch „Plain Text“ zu finden.

Zudem vereinfache ich mir einiges, was nun wirklich nicht gerade zur Qualität des Ergebnisses bei trägt. Wohl oder übel muss man die Wörterliste selbst sichten und unbrauchbares entfernen.

Lange Rede, wenig Sinn, fangen wir einfachen Mal an; Optimierungspotenzial gibt es allemal, was jeder für sich umsetzen mag.

Zunächst brauchen wir Tabellen zum Speichern; logo. Eine temporäre Staging Tabelle für den Import und natürlich eine finale Tabelle für die „Wörter“. Sinnig wäre es die Tabelle für Wörter um ein Feld für die Sprache zu ergänzen; aktuell ist es für mich aber eher ein „Proof of Concept“ und überhaupt; automatisiert die Sprache zu ermitteln ist ein noch größeres Problem. Aber Probleme löst man Step-by-Step; fangen wir erst mal an:

 SET NOCOUNT ON;

 -- Word-Tabelle anlegen, wenn nicht vorhanden

 IF OBJECT_ID('dbo.Words') IS NULL

 BEGIN

  CREATE TABLE dbo.Words

  (ID int NOT NULL IDENTITY(1, 1),

  Word varchar(100) NOT NULL);

  ALTER TABLE dbo.Words ADD CONSTRAINT

  PK_Words PRIMARY KEY CLUSTERED (ID);

  CREATE UNIQUE NONCLUSTERED INDEX IX_Words_Word

  ON dbo.Words (Word);

END

GO

 -- Temporäre Staging Tabelle für BLOB Import

 CREATE TABLE #Staging (Doc varchar(max));

GO

Nun ist die Staging-Tabelle recht leer, die gilt es nun mit Texten zu füllen. Für das Beispiel habe ich mir im Netz ein paar Plain Text Dateien rausgesucht und an den Link angeben, damit Ihr es auch analog nachvollziehen könnt. Es sind gemischt (im wahrsten Worte) DE/EN Texte, das Ergebnis ist qualitative nicht so übertrieben toll; was soll‘s. Der Import erfolgt per BULK INSERT, je Zeile wird ein DataSet in die Staging-Tabelle weg geschrieben. BULK ist sehr effizient:

 -- Text-Datei Zeilenweise ins Staging per BULK importieren

 -- http://www.gnu.org/licenses/lgpl.txt

 BULK INSERT #Staging FROM 'D:\Projekte\lgpl.txt';

 -- http://www.davros.org/misc/iso3166.txt

 BULK INSERT #Staging FROM 'D:\Projekte\iso3166.txt';

 -- http://werbach.com/barebones/barebones.txt

 BULK INSERT #Staging FROM 'D:\Projekte\barebones.txt';

 -- http://www.w3.org/TR/2000/WD-smil-boston-dom-20000225/SYMM.txt

 BULK INSERT #Staging FROM 'D:\Projekte\SYMM.txt';

 -- http://www.astro.caltech.edu/~george/qqq/qqq_pr.txt

 BULK INSERT #Staging FROM 'D:\Projekte\qqq_pr.txt';

 -- http://www.valeriodistefano.com/gutenberg/00_multilingual_dvd/etext93/badge10.txt

 BULK INSERT #Staging FROM 'D:\Projekte\badge10.txt';

 -- http://www.ojp.usdoj.gov/bjs/pub/ascii/ecp.txt

 BULK INSERT #Staging FROM 'D:\Projekte\ecp.txt';

 -- http://opensource.linux-mirror.org/licenses/cuaoffice.txt

 BULK INSERT #Staging FROM 'D:\Projekte\cuaoffice.txt';

 -- Fast DE nur ohne Umlaute

 -- http://www.classicistranieri.com/deutsch/etext05/7kndr10.txt

 BULK INSERT #Staging FROM 'D:\Projekte\7kndr10.txt';

 -- http://www.ibiblio.org/pub/docs/books/gutenberg/etext90/const11.txt

 BULK INSERT #Staging FROM 'D:\Projekte\const11.txt'

 -- Schon eher richtig DE

 -- http://www.stefanbion.de/cueltool/readme.txt

 BULK INSERT #Staging FROM 'D:\Projekte\readme.txt'

 -- http://www.dcoul.de/faq/text/A.txt

 BULK INSERT #Staging FROM 'D:\Projekte\A.txt'

GO

Nun haben wir temporär Daten = Texte vorhanden.

(!! Bitte bei der Aktion die tempdb kontrollieren, bevor sie „platzt“; sind zwar keine wirklichen Datenmenge, aber Vorsicht ist besser als die Nachsicht zu haben!!).

Die sollen nun in einzelne Worte aufgesplittet werden, mit der Annahme, das ein Wort mit dem nächsten Leerzeichen endet (eine vereinfache, aber nicht korrekte Annahme). Dazu verwende ich eine ebenso temporäre Stored Procedure:

 -- Temporäre SPs für die Verarbeitung anlegen

 IF NOT OBJECT_ID('tempdb..#spGetWordsFromStaging') IS NULL

  DROP PROCEDURE #spGetWordsFromStaging;

GO

 CREATE PROCEDURE #spGetWordsFromStaging

AS

 BEGIN

  DECLARE @Text varchar(max);

  DECLARE @Word varchar(100);

  DECLARE @NextSpace int;

  DECLARE Line CURSOR LOCAL FORWARD_ONLY FOR

  SELECT Doc FROM #Staging

  OPEN LINE

  FETCH NEXT FROM Line INTO @Text

  WHILE @@FETCH_STATUS = 0

  BEGIN

  -- Überflüssige SPACEs entfernen und trimmen

  SET @Text = RTRIM(LTRIM(REPLACE(ISNULL(@Text, ''), ' ', ' ')));

  SET @NextSpace = CHARINDEX(' ', @Text);

  WHILE @NextSpace > 0

  BEGIN

  SET @Word = LEFT(@Text, @NextSpace);

  -- Unerwünschte Sonderzeichen entfernen

  -- Nums

  SET @Word = REPLACE(@Word, '0', '');

  SET @Word = REPLACE(@Word, '1', '');

  SET @Word = REPLACE(@Word, '2', '');

  SET @Word = REPLACE(@Word, '3', '');

  SET @Word = REPLACE(@Word, '4', '');

  SET @Word = REPLACE(@Word, '5', '');

  SET @Word = REPLACE(@Word, '6', '');

  SET @Word = REPLACE(@Word, '7', '');

  SET @Word = REPLACE(@Word, '8', '');

  SET @Word = REPLACE(@Word, '9', '');

  -- Interpunktion

  SET @Word = REPLACE(@Word, '.', '');

  SET @Word = REPLACE(@Word, ',', '');

  SET @Word = REPLACE(@Word, ';', '');

  SET @Word = REPLACE(@Word, ':', '');

  SET @Word = REPLACE(@Word, '!', '');

  SET @Word = REPLACE(@Word, '?', '');

  -- Und sonstige Sonderzeichen

  SET @Word = REPLACE(@Word, '-', '');

  SET @Word = REPLACE(@Word, '+', '');

  SET @Word = REPLACE(@Word, '=', '');

  SET @Word = REPLACE(@Word, '"', '');

  SET @Word = REPLACE(@Word, '´', '');

  SET @Word = REPLACE(@Word, '`', '');

  SET @Word = REPLACE(@Word, '#', '');

  SET @Word = REPLACE(@Word, '$', '');

  SET @Word = REPLACE(@Word, '€', '');

  SET @Word = REPLACE(@Word, '*', '');

  SET @Word = REPLACE(@Word, '_', '');

  SET @Word = REPLACE(@Word, '$', '');

  SET @Word = REPLACE(@Word, '&', '');

  -- Html, XML und Wiki kommen auch vor

  SET @Word = REPLACE(@Word, '(', '');

  SET @Word = REPLACE(@Word, ')', '');

  SET @Word = REPLACE(@Word, '[', '');

  SET @Word = REPLACE(@Word, ']', '');

  SET @Word = REPLACE(@Word, '<', '');

  SET @Word = REPLACE(@Word, '>', '');

  -- Na Hochkommas sowieso

  SET @Word = REPLACE(@Word, '''', '');

  -- Noch mal trimmen

  SET @Word = LTRIM(RTRIM(@Word));

  -- Nur "nützliche" Wörter mit >= 4 Zeichen

  -- und keine Nums/Dates, kein (Back)-Slash

  -- Emails machen acuh keinen Sinn

  IF LEN(@Word) >= 4

  AND ISNUMERIC(@Word) = 0

  AND ISDATE(@Word) = 0

  AND NOT @Word LIKE 'WWW%'

  AND NOT @Word LIKE '%/%'

  AND NOT @Word LIKE '%\%'

  AND NOT @Word LIKE '%@%'

  AND NOT @Word LIKE '%|%'

  BEGIN

  IF NOT EXISTS(SELECT Word FROM Words

  WHERE Word = @Word)

  INSERT INTO Words (Word)

  VALUES (@Word);

  END;

  -- Verwendeten Text abschnippeln

  SET @Text = SUBSTRING(@Text, @NextSpace + 1, LEN(@Text));

  SET @NextSpace = CHARINDEX(' ', @Text);

  END;

  FETCH NEXT FROM Line INTO @Text

END

  CLOSE Line;

  DEALLOCATE Line;

 END;

GO

Das Aufsplitten selbst ist wirklich recht Simple.

Aber wie man sehen auch kann: Ich mache es mir sehr einfach und ignoriere diverses Sonderfälle; es ist suboptimal und wie bereits erwähnt gibt es reichlich Optimierungspotenzial; ein jeder möge sich bitte nach Lust & Laune hier austoben und sich kreativ betätigen; über Feedback hierzu würde ich mich freuen.

Nun muss das ganze auch ausgeführt werden; also ein EXEC auf die #SP, anschließend die #Temps abräumen und das Ergebnis kontrollieren: