Introduzione alle funzionalità di Full-Text Search con SQL Server 2005

Di Andrea Benedetti - Microsoft MVP

SQL Server, a partire dalla versione 7, offre, nativamente, le funzionalità necessarie alla creazione di query più o meno complesse su tipi di dati carattere che possono includere parole o intere frasi: SQL Full-Text Search (SQL FTS).

Si tratta di un componente in grado di effettuare interrogazioni veloci ed efficienti su indicizzazioni di testi, ovvero query che si basano su termini chiave, parti di testo o semplici parole.

A differenza della clausola LIKE, applicabile in qualsiasi clausola WHERE, questa indicizzazione consente il recupero dei termini cercati anche tramite ricerche linguistiche.

Possiamo ottenere i massimi benefici da questa tecnica soprattutto in presenza di grandi moli di informazioni di tipo non strutturato, come possono essere testi o interi documenti.

Vediamo allora di capire come SQL Server implementa queste funzionalità, quali sono gli attori che entrano in scena e come configurarli correttamente.

In questa pagina

L’architettura L’architettura
Abilitare ed utilizzare la funzionalità Abilitare ed utilizzare la funzionalità

L’architettura

SQL Server, all’atto del setup, consente l’installazione del servizio Full Text (il motore di indicizzazione) che quindi, per essere utilizzato, dovrà essere avviato (possiamo verificare lo stato del servizio tramite l’applicazione SQL Server Configuration Manager presente all’interno dei “Configuration Tools”).

*

L'architettura responsabile di questa funzionalità è composta dal motore di indicizzazione (MSFTESQL, che si connette alla nostra istanza tramite un provider OLE-DB) che si occupa del popolamento e della gestione degli indici e da un "filter daemon" (MSFTEFD) costituito da componenti responsabili dell'accesso ai dati delle tabelle e della relativa applicazione di filtri, un word breaking (meccanismo in grado di suddividere testo in token basandosi sulle regole lessicali pertinenti) ed uno stemming (ricerca di radici di parole).

È utile sottolineare come il componente di word breaking lavori utilizzando il LCID (LoCale IDentifier) specificato all’atto della creazione dell’indice full-text, ovvero possiamo fare in modo che il componente lavori su specifiche lingue.

Possiamo visualizzare tutti i linguaggi supportati da SQL Server 2005 interrogando la vista di sistema sys.fulltext_languages come:

select * from sys.fulltext_languages

Internamente il motore di indicizzazione costruisce uno o più indici attraverso un processo chiamato “popolamento” in grado di associare le parole ed i termini con le loro posizioni all’interno dei dati e, tramite un componenti COM chiamati iFilters, è in grado di comprendere ed estrarre il testo dalle colonne indicizzate.

A seconda del tipo di colonna indicizzata verrà utilizzato l’oggetto iFilter appropriato:

  • Per colonne testuali di tipo char, nchar, varchar, nvarchar e blob come text ed ntext verrà utilizzato il componente iFilter text

  • Per colonne XML, nuovo primary data type introdotto con SQL Server 2005, verrà utilizzato il componente iFilter XML

  • Per colonne blob di tipo image o varbinary verrà utlizzato il componente specifico per l’estensione del file memorizzato al loro interno

Per conoscere i tipi di file supportati e disponibili per operazioni di indicizzazione possiamo interrogare la vista di sistema sys.fulltext_document_types, ovvero tramite:

select document_type, path from sys.fulltext_document_types

Con questa interrogazione siamo in grado di conoscere l’estensione del file, ovvero del tipo supportato, ed il percorso della libreria che contiene il componente iFilter appropriato.

L’indice viene memorizzato in cataloghi che sono specifici per ogni database, ovvero è possibile definire più cataloghi per ciascun database presente sulle nostre istanze, ma non è possibile far sì che il catalogo contenga dati appartenenti a database differenti.

L'amministrazione della ricerca full-text si compone di quattro differenti passaggi:

  • creazione di indici e cataloghi

  • modifica di indici e cataloghi esistenti

  • eliminazione di indici e cataloghi

  • pianificazione e gestione degli indici

Per quanto riguarda gli indici fulltext, invece:

  • vengono archiviati nel file system, ma gestiti tramite il db

  • viene consentita la costruzione di un solo indice per tabella

  • l'aggiunta di dati agli indici (popolamento e tracking delle modifiche) può essere richiesta manualmente, tramite pianificazione, o in maniera automatica

Abilitare ed utilizzare la funzionalità

A questo punto analizziamo i passi da seguire per rendere disponibile e quindi utilizzare questa funzionalità ovvero, andando per ordine:

  • abilitare la ricerca per il database (sp_fulltext_database)

  • creare il catalogo (sp_fulltext_catalog)

  • marcare la tabella per l'indicizzazione fulltext (sp_fulltext_table)

  • aggiungere le colonne che si vogliono indicizzare (sp_fulltext_column)

  • attivare l'indice (sp_fulltext_table)

  • popolare l'indice (sp_fulltext_catalog)

Eventualmente si possono definire anche una serie di regole con cui mantenere allineato il catalogo di indicizzazione (operazioni di tracking): sempre, in maniera schedulata, …

Analizziamo allora, tramite un esempio pratico, la realizzazione di un catalogo full-text ed il suo utilizzo.

Per prima cosa costruiamo un database di test che utilizzeremo per fare i nostri ragionamenti.

Dal Management Studio, in una nuova query, andiamo a scrivere:

-- 0) Creo un database ed una tabella di test
USE master
GO

-- Drop the database if it already exists
IF  EXISTS (
SELECT name 
FROM sys.databases 
WHERE name = N'testFullText'
)
DROP DATABASE testFullText
GO

CREATE DATABASE testFullText
GO

Fatto questo andiamo a definire una tabella “ricette” che popoleremo con due record di esempio:

use testFullText
go

create table ricette
(
idRecord int identity(1,1),
titolo varchar(35),
descrizione varchar(1000),
constraint PK_ricette primary key clustered (idRecord)
)
go

-- Inserimento dati ----------------------------------------
declare @testo varchar(1000)
set @testo = '... Salare internamente l''anatra e porla in un tegame con l''olio d''oliva. 
Farla rosolare per qualche minuto su tutti i lati e poi aggiungere il vino e un po'' d''acqua.
Proseguire la cottura, con il coperchio e a fiamma bassa, per 45-60 minuti a seconda delle dimensioni.
Salarla a fine cottura.
Nel frattempo sbucciare
un''arancia e, con un coltellino affilato, ricavare dalla buccia sola la parte gialla. 
Tagliarla a striscioline; far bollire le striscioline per qualche minuto e scolarle molto bene....'

insert ricette (titolo, descrizione) values ('Anatra all''arancia', @testo)

set @testo = 'Far bollire per pochi minuti 250 g di frutti di bosco con un cucchiaio di zucchero 
e il succo dei mandaranci o delle arance.
Frullare il tutto e passarlo nel colino per eliminare eventuali semi.
Far rosolare il fagiano nell''olio con l''erba cipollina
in maniera che assuma un colore dorato su tutti i lati.
Bagnarlo con l''arancino e farlo evaporare solo parzialmente.
A questo punto aggiustare di sale,
aggiungere il frullato di frutti di bosco e 1/2 bicchiere d''acqua tiepida.
Proseguire la cottura per circa un''ora a pentola coperta e fuoco moderato.
Se necessario, utilizzare altra acqua tiepida ma poca alla volta.
Aggiungere il resto dei frutti di bosco solo un minuto o due prima di spegnere il fornello.'

insert ricette (titolo, descrizione) values ('Fagiano ai frutti di bosco', @testo)
-- Fine inserimento dati ----------------------------------------

A questo punto eseguiamo i passi necessari alla definizione del catalogo di indicizzazione, ovvero abilitiamo l’indicizzazione sul nostro database tramite la procedura sp_fulltext_database tramite il parametro “Enable”:

-- 1) Inizializzo l'indicizzazione full-text
exec sp_fulltext_database @action='Enable'

Creiamo il catalogo con un nome appropriato:

-- 2) Creo catalogo full-text
exec sp_fulltext_catalog 'CatalogoRicette', 'create'

Definiamo la tabella che vogliamo indicizzare e, di conseguenza, la colonna (o le colonne) che vogliamo vengano inserite nell’indice:

-- 3) Contrassegno la tabella per l'indicizzazione fulltext 
exec sp_fulltext_table 'ricette', 'create', 'CatalogoRicette', 'PK_ricette'
-- aggiungo una colonna al catalogo
exec sp_fulltext_column 'ricette','descrizione','add'
-- attivo l'indice
exec sp_fulltext_table 'ricette','activate'

Popoliamo l’indice, ovvero facciamo eseguire il processo di popolamento completo per il catalogo specificato:

-- 4) popolo l'indice 
exec sp_fulltext_catalog @ftcat='CatalogoRicette', @action= 'start_full'

-- 5) attivo e avvio la propagazione delle modifiche rilevate nell'indice full-text
EXEC sp_fulltext_table 'ricette', 'Start_change_tracking';
EXEC sp_fulltext_table 'ricette', 'Start_background_updateindex';

A questo punto il nostro catalogo è stato correttamente definito e l’indice popolato.Non resta che provarlo andando ad utilizzare, ad esempio, i predicati full-text CONTAINS e FREETEXT, come:

-- 6) provo l'indice

-- ricerche di parole o frasi specifiche
select * from ricette where CONTAINS (descrizione, 'oliva')
select * from ricette where CONTAINS (descrizione, '"bollire" and "olio"')

select * from ricette where FREETEXT (descrizione, 'vino olio arancia')

-- ricerche con prefisso
select * from ricette where CONTAINS (descrizione, ' "fuoco moderato*" ' );

Con le stesse stored procedure di sistema siamo in grado di eliminare indice e catalogo appena creati mandando ancora in esecuzione la sp_fulltext_table per eliminare (drop) i metadati dell’indice e la sp_fulltext_catalog per eliminare l’intero catalogo, ovvero:

-- 7) drop indice e drop del catalogo
exec sp_fulltext_table 'ricette', 'drop'
exec sp_fulltext_catalog @ftcat='CatalogoRicette', @action= 'drop'


-- 8) drop database
Use Master
go
drop database testFullText
go

Gli script che abbiamo mostrato sono sicuramente utili e validi per capire e conoscere nel dettaglio le operazioni che devono essere eseguite per realizzare una soluzione basata su questa tecnologia.

Bisogna però sottolineare anche come Microsoft, ed il team di sviluppo di SQL Server, si siano spesi nel realizzare strumenti più facilmente utilizzabili che possano, quindi, rendere più veloce il lavoro agli sviluppatori ed ai DBA.

Dal Management Studio, infatti, è possibile eseguire tutti i passi appena descritti tramite un comodo wizard che, con pochi step, è in grado di eseguire per noi tutte le istruzioni necessarie.

A scopo didattico e per vedere sempre quello che avviene dietro le quinte può essere molto interessante far partire una traccia di Profiler (SQL Profiler è il tool, compreso in SQL Server, in grado di profilare, ovvero registrare, l’utilizzo di una istanza) ed analizzare i comandi che effettivamente vengono inviati verso il nostro database.

Vediamo allora come potremmo replicare le stesse operazioni in maniera visuale proprio tramite questa procedura guidata.

Dal Management Studio ci colleghiamo alla nostra istanza, quindi al nostro database e selezioniamo la tabella sulla quale vogliamo costruire un indice di tipo FullText.

Dal menù contestuale (visualizzabile tramite tasto DX) siamo in grado di selezionare la voce “Full-Text Index” e quindi “Define Full-Text Index”.

*

Questa operazione consente l’avvio del wizard che ci guiderà passo passo, dopo la classica prima videata di benvenuto, nella definizione del nostro catalogo.

Come prima cosa andiamo a definire la colonna o le colonne delle quali vogliamo andare ad indicizzare il contenuto:

*

Selezioniamo la modalità di aggiornamento, ovvero come vogliamo che vengano tracciate le modifiche sul nostro indice (in maniera automatica, manuale o senza alcuna tracciatura):

*

Selezioniamo quindi il nome che vogliamo assegnare al nostro catalogo, quindi la sua posizione fisica (dopo la sua creazione potremmo andare a vedere sul file system che cosa è stato creato, il numero di file, la loro dimensione):

*

Compiuti tutti i passi precedenti non ci resta che far creare il catalogo ed il suo indice:

*

La creazione del catalogo, come abbiamo visto poco sopra scrivendo ed analizzando gli script in T-SQL, non è sufficiente per poter lavorare con esso.

Dobbiamo ancora abilitarlo ed infine popolarlo. Possiamo farlo selezionando le opportune voci, come mostrato nella figura sottostante:

*

A questo punto saremo in grado, avendo fatto il catalogo, avendolo abilitato e popolato, di effettuare interrogazioni sui nostri contenuti utilizzando parole, frasi, prefissi, …

Ancora a scopo di analisi e di studio, andiamo ad analizzare le istruzioni che utilizzano il Full-Text Search Engine, come ad esempio:

select * from ricette where CONTAINS (descrizione, 'oliva')

La analizziamo tramite la visualizzazione grafica dell’execution plan in grado di mostrarci come l’interrogazione richiesta venga risolta internamente dal motore di database, ovvero, come mostrato in figura:

*

Dalla rappresentazione grafica si può vedere come la query venga svolta tramite un’operazione di Remote Scan, ovvero tramite la una scansione di un oggetto remoto (come infatti è il catalogo di indicizzazione di Full-Text che risiede su file system e non all’interno del database interessato).

Sicuramente non è possibile coprire tutte le tematiche associate alle ricerche Full-Text in un solo articolo. Il consiglio è sicuramente quello di provare questa tecnologia che può, di fatto, migliorare di molto la vita sia degli sviluppatori, che dei DBA, che degli utenti che devono essere in grado di recuperare, nel minor tempo e con la maggior precisione possibile, le informazioni cercate.


Mostra: