Die Volltextsuche ist eine optionale Komponente des SQL Server-Datenbankmoduls und hilft bei der Volltextabfrage für zeichenbasierte Daten in SQL Server-Tabellen.
Sie dient daher dem Auffinden und Extrahieren wichtiger Informationen aus großen Mengen von unstrukturierten Texten oder Dateien und ist somit auch ein wichtiger Teilbereich des Text Mining.
Bevor wir aber Volltextabfragen für eine bestimmte Tabelle ausführen können, müssen noch einige Vorbereitungen getroffen werden.
“FILESTREAM” einrichten
FILESTREAM ermöglicht in SQL Server-basierten Anwendungen, nicht-strukturierte Daten wie Dokumente und Bilder im Dateisystem zu speichern, abzufragen, zu aktualisieren und zu durchsuchen. Somit integriert FILESTREAM das Datenbankmodul in ein NTFS-Dateisystem, indem BLOB-Daten (Binary Large Object) vom Typ “varbinary(max)” im Dateisystem gespeichert werden.
Zuerst müssen wir jedoch FILESTREAM mit dem “SQL Server Configuration Manager” aktivieren, weil SQL Server dies bei der Installation nicht automatisch macht.
Abb. 1: Enable Streaming
Dateigruppe für FILESTREAM in der Datenbank definieren
Um FILESTREAM verwenden zu können, müssen wir für die Datenbank eine besondere Art von Dateigruppe anlegen bzw. gleich eine neue Datenbank mit dieser Dateigruppe erstellen.
Das geschieht über folgenden Code für die Datenbank “Volltextsuche”, die wir zuvor angelegt haben:
Alter Database Volltextsuche Add Filegroup FileStreamPDF_FG CONTAINS Filestream
Abb. 2: Dateigruppe definieren
Datenbankordner für FILESTREAM-Dateigruppe erstellen
Im Gegensatz zu einer normalen Dateigruppe benötigt eine FILESTREAM-Dateigruppe keine neue Datenbankdatei, sondern einen neuen Ordner. Dabei muss der Pfad bis zum letzten Ordner vorhanden sein. Der Unterordner “FS_Volltextsuche_Documents” wird jedoch automatisch erstellt. Mit Hilfe des folgenden Codes legen wir den Ordner an:
Alter Database Volltextsuche ADD File ( Name = 'FileStreamPDF_FG' ,FILENAME = 'C:\Data\FS_Volltextsuche_Documents' ) To FILEGROUP FileStreamPDF_FG
Nach der Ausführung des obigen Codes werfen wir einen Blick auf das Dateisystem:
Abb. 3: Dateigruppe Ordnerstruktur
Die Datei “filestream.hdr” ist eine Systemdatei, die FILESTREAM-Headerinformationen enthält. Sie darf nicht entfernt oder geändert werden.
Nun sind datenbankseitig alle Vorbereitungen abgeschlossen, so dass wir uns den Tabellen und ihrer Struktur zuwenden können.
Tabelle mit Filestream-Spalte anlegen
Es wird eine Tabelle zum Speichern von FILESTREAM-Daten erstellt. Diese enthält eine Spalte vom Typ “varbinary(max)”. Hier werden z. B. PDF-Dokumente im FILESTREAM-Ordner abgelegt. Außerdem muss die ID-Spalte “DocumentID” als ROWGUIDCOL-Spalte definiert werden, die zur Verwendung von FILESTREAM-Daten erforderlich ist. Die Spalte “Document_Type” ist genauso wichtig, damit bei der Volltextsuche der richtige Filter verwendet wird, in unserem Beispiel also “.pdf”.
Create Table T_Documents ( DocumentID uniqueidentifier NOT NULL ROWGUIDCOL CONSTRAINT DF_T_Documents_ID DEFAULT NEWSEQUENTIALID() CONSTRAINT UQ_T_Documents_ID UNIQUE , Document varbinary(max) FILESTREAM DEFAULT (0x) , Document_No int Not NULL Identity(1,1) --Primary Key CONSTRAINT PK_T_Documents Primary KEY , Document_Type nvarchar(100) , Document_Name nvarchar(200) , Document_Page int )
Volltextkatalog anlegen
Zum Erstellen von Volltextindizes in Tabellen einer Datenbank benötigen wir den sogenannten Volltextkatalog, den wir wie folgt anlegen:
CREATE FULLTEXT CATALOG ft_Catalog_pdf as DEFAULT
Der Volltextindex umfasst eine oder mehrere zeichenbasierte Spalten der Tabelle. Diese Spalten können jeden der folgenden Datentypen aufweisen: char, varchar, nchar, nvarchar, text, ntext, image, xml oder varbinary(max) und FILESTREAM. Jeder Volltextindex indiziert mindestens eine Spalte aus der Basistabelle. Und man kann sogar für jede Spalte eine eigene Sprache verwenden.
Datenbank für Volltext einrichten
In SQL Server 2008 und höheren Versionen sind standardmäßig alle von Benutzern erstellten Datenbanken immer volltextfähig. Der folgende SQL-Befehl wird nur aus Gründen der Abwärtskompatibilität unterstützt:
exec sp_fulltext_database 'enable'
Zusätzliche Komponente für PDF-Dateien installieren
Standardmäßig werden PDF-Dateien in SQL Server nicht unterstützt. Um zu prüfen ob die Komponente bereits installiert ist, führen wir folgenden Code im Abfragefenster des SQL Server Management Studios einfach aus:
exec sp_help_fulltext_system_components 'filter' go SELECT document_type, path FROM sys.fulltext_document_types WHERE document_type = '.pdf'
Die erste Zeile liefert uns alle registrierten Komponenten vom Typ “filter”.
Wird nach der Ausführung vom “SELECT-Statement” kein Datensatz geliefert, muss der sogenannte “PDF iFilter” installiert werden. Die Installationsdatei kann von hier heruntergeladen werden. Die Dokumentation für die Konfiguration des “iFilter” befindet sich hier. “iFilter” funktioniert auch mit SQL Server 2012.
Der “iFilter” dient dazu, große Mengen von PDF-Dokumenten zu indizieren, so dass bestimmte Inhalte schnell gefunden werden können.
Neuladen von Volltext-Filter nach der Installation des “iFilter”
Nach der Installation des “iFilter” müssen wir die Änderungen der Serverinstanz mitteilen, weil diese nicht automatisch wirksam werden.
Zum Laden neu installierter Filter in die Serverinstanz benutzen wir folgenden Code:
exec sp_fulltext_service 'load_os_resources', 1
Standardmäßig werden nur vertrauenswürdige signierte Binärdateien geladen. Um dieses Verhalten zu deaktivieren, verwenden wir folgenden Befehl:
exec sp_fulltext_service 'verify_signature', 0
Volltext-Index anlegen, mit Dokumententyp verbinden, aktivieren und Index starten
Jetzt ist es an der Zeit den Volltext-Index zu erstellen:
DECLARE @indexName nvarchar(255) = (SELECT Top 1 i.Name from sys.indexes i Join sys.tables t on i.object_id = t.object_id WHERE t.Name = 'T_Documents' AND i.type_desc = 'CLUSTERED') PRINT @indexName --Primary Key!
Mit dem Dokumententyp zu verbinden:
EXEC sp_fulltext_table 'T_Documents', 'create', 'ft_Catalog_pdf', @indexName EXEC sp_fulltext_column 'T_Documents', 'Document', 'add', 0, 'Document_Type'
Den Index zu aktivieren:
EXEC sp_fulltext_table 'T_Documents', 'activate'
Und den Index zu starten:
EXEC sp_fulltext_catalog 'ft_Catalog_pdf', 'start_full' ALTER FULLTEXT INDEX ON [dbo].[T_Documents] ENABLE ALTER FULLTEXT INDEX ON [dbo].[T_Documents] SET CHANGE_TRACKING = AUTO
Füllen der Tabelle mit PDF-Dokumenten
In einem Test haben wir sieben Dokumente aus unseren DeltaMaster clicks! in einem Ordner gespeichert und mit Hilfe von folgendem Code in die Tabelle “T_Documents” importiert:
Declare @i int Declare @sql varchar(max) Set @i = 1 While @i < 8 begin Set @sql = 'INSERT INTO T_Documents(Document_Name, Document_Type, Document) SELECT ''DeltaMaster_clicks!_2010-0' + Cast(@i as varchar(1)) + ''' AS DocumentName ,''.pdf'' AS DocumentExtension , * FROM OPENROWSET(BULK ''C:\_Ablage\DeltaMaster_Clicks\DeltaMaster_clicks!_2010-0' + Cast(@i as varchar(1)) + '.pdf'', SINGLE_BLOB) AS Document;' exec (@sql) Set @i = @i + 1 end
Erklärungsbedürftig ist der Befehl OPENROWSET, der BULK-Rowsetanbieter verwendet, um Daten aus einer Datei zu lesen und mit dem Parameter “SINGLE_BLOB” den Inhalt der Datei als einzeiliges, einspaltiges “Rowset” vom Typ “varbinary(max)” zurückgibt.
Zur Kontrolle führen wir folgende Select-Anweisung aus:
SELECT * FROM dbo.T_Documents
Das Ergebnis:
Abb. 4: T_Documents
In diesem ersten Teil der Volltextsuche schauen wir uns einen einfachen Suchvorgang an:
SELECT d.* FROM dbo.T_Documents d WHERE Contains(d.Document, '%spannweiten%')
Bei Spalten mit zeichenbasierten Datentypen wird ein sogenanntes “Prädikat”, hier “CONTAINS”, in der WHERE-Klausel verwendet. “CONTAINS” durchsucht solchen Spalten nach genauen oder ungefähren Treffern. In unserem Fall suchen wir in den sieben PDF-Dateien nach dem Begriff “Spannweiten”. Als Resultat liefert uns die Datenbank:
Abb. 5: T_Documents
Im nächsten Blogbeitrag schauen wir uns weitere Volltextprädikate und Suchoptionen an. Lassen Sie sich überraschen.
