SQL Server Full-Text Search 

Datamaze
18.07.24 12:01 PM Comment(s)

In questo articolo introdurremo un componente aggiuntivo di SQL Serverdenominato ‘Full-Text Search’, una tecnologia che ci permette di effettuare query su grandi quantità di dati testuali, strutturati e non, nel modo più veloce ed efficiente possibile.


Full-Text Search 

Generalmente per manipolare e ricercare del testo nelle nostre tabelle utilizziamo funzioni come SUBSTRING(), LEFT(),RIGHT() e/o il predicato LIKE. Questo tipo di ricerca è chiamato ‘Simple Text Searching’ e, su grandi volumi di dati, tende ad essere una metodologia  metodologia poco efficiente e poco performante. 

Da SQL Server 2000 è stata introdotta la funzionalità ‘Full-Text search’ che permette di effettuare ricerche libere in modo efficiente e scalabile su colonne con dati di tipo carattere. 

I dati supportati sono i seguenti:  char, varchar, nchar, nvarchar, text, ntext, image, xml o varbinary(max) e  FILESTREAM.

I vantaggi di Full-Text Search

L’attivazione di questa funzionalità prevede l’installazione di un ulteriore servizio oltre al servizio base di database engine. Tale servizio è denominato ‘SQL Full-text Filter Daemon Launcher’ 
Full Text Search
  • In aggiunta alla possibilità di eseguire query basate su condizioni esatte, come l'uguaglianza data dal predicato LIKE, questa nuova funzionalità permette di cercare parole chiave o frasi all'interno di testi lunghi, considerando anche aspetti come sinonimi, formeflesse e altre caratteristiche linguistiche

    Si può pensare alla ricerca ‘Full-Text’ un po’ come un motore di ricerca di un browser interno a SQL Server. 

    I metodi di ricerca più avanzata che introduce questa componente aggiuntiva sono i seguenti: 

    • Trovare una o più parole o frasi specifiche (termine semplice). 

    • Trovare parole o frasi in cui le parole iniziano con il testo specificato (termine di prefisso). 

    • Trovare sinonimi di una parola specifica (thesaurus). 

    • Trovare una parola o frase vicina a un'altra parola o frase (termine di prossimità). 

    • Trovare parole o frasi che usano valori ponderati (termine ponderato).

    • Trovare file in base alle loro proprietà. 

    • Influenzare la ricerca configurando parole e liste di stop. 

    • Trovare parole in base alla loro struttura flessa.  

    • Altre metodologie di ricerca.

Configurazione

Questa funzionalità va aggiunta in fase di installazione. Per verificare se la feature è presente o meno sull’istanza di interesse eseguiamo la seguente query: 

SELECT SERVERPROPERTY('IsFullTextInstalled') AS [Full Text Search Installed];
	-- 1 = installata. 
	-- 0 = non installata.

Nel caso non fosse installata, occorre utilizzare ‘SQL Server Installation Center’ per aggiungere la componente all'istanza. 

SQL Server setup

Funzionamento di Full-Text search con un esempio pratico 

Di seguito descriverò i passaggi necessari affinché si possa iniziare ad utilizzare la funzionalità in questione. Gli esempi pratici verranno effettuati sul database sample ’Adventureworks2019’ di Microsoft scaricabile gratuitamente a questo link

1) Abbiamo bisogno di un ‘full-text catalog’ per poter definire degli indici full-text sulle colonne in cui avremo interesse di fare le ricerche FT. Questo catalogo è un semplice raggruppamento logico degli indici FT e non appartiene a nessun filegroup. Eseguiamo il seguente comando: 
USE AdventureWorks2019;
GO

CREATE FULLTEXT CATALOG AdvWksProdDescFTCat;

Il catalogo appena creato è visibile in Databases>Adventureworks2019>Storage>Full Text Catalogs.

Full Text Catalogs

2) Una volta creato il catalogo possiamo iniziare a creare degli indici full-text sulle colonne che contengono il testo da ricercare. Prima di fare ciò dobbiamo assicurarci che la tabella interrogata (per esempio ProductDescription) abbia un indice unique, single-column, non-nullable. Non è sufficiente utilizzare una eventuale chiave primaria di una sola colonna già esistente (che per definizione è già univoca e non-nulla) va creato comunque un indice di tipo UNIQUE. 


CREATE UNIQUE INDEX ui_ukProdDesc ON Production.ProductDescription (ProductDescriptionID);

Ora possiamo creare l’indice sulla colonna (ProductDescriptionID) dove vogliamo poter effettuare delle ricerche tramite full text. Basta eseguire  il seguente comando:

CREATE FULLTEXT INDEX ON Production.ProductDescription (Description) KEY INDEX ui_ukProdDesc -- Indice univoco 
	ON AdvWksProdDescFTCat -- Catalogo full-text 

3) Nella creazione dell’indice FT su una specifica colonna, il testo contenuto viene analizzato e suddiviso in unità più piccole chiamate ‘token’. Oltre a questo processo di tokenization, vengono anche applicate funzioni di stemming per ridurre le parole alla forma radice (es. "parlare", "parlato“ diventano "parla“) e di Thesaurus per trovare sinonimi

4)  Ora possiamo eseguire delle ricerche full-text tramite la classica istruzione di SELECT ma potendo utilizzare dei nuovi predicati (CONTAINS e FREETEXT)e delle nuove funzioni (CONTAINSTABLE FREETEXTTABLE). 

Queste istruzioni sono divise in due categorie, da una parte CONTAINS e FREETEXT, dall’altra CONTAINSTABLE e FREETEXTTABLE. La caratteristica dei predicati con il suffisso TABLE risiede nel fatto che le funzioni applicate vengono valutate a livello di set di righe (gli esempi che vedremo a riguardo useranno una tabella che verrà messa in JOIN con sé stessa).

L’altra differenza è che quelli senza TABLE ritornano un risultato booleano e possono essere utilizzati dopo una clausola WHERE. Le funzioni con TABLE ritornano due colonne aggiuntive, una colonna che ha una chiave univoca definita nell’indice FT e una colonna in cui è definito un punteggio di rilevanza del risultato ottenuto. Nel caso invece dei predicati senza TABLE, non ci sono queste colonne aggiuntive. 

Ecco degli esempi: 

table set di righe

Invece nel caso con FREETEXTTABLE (vale anche per CONTAINSTABLE) si possono vedere le due colonne aggiuntive. 

Freetext Table
Tornando a parlare delle due categorie di istruzioni full-text, CONTAINS e FREETEXT, elencherò di seguito le principali differenze. Queste valgono anche per le funzioni con TABLE. 

Pur essendo la sintassi praticamente identica, tuttavia i due comandi hanno comportamenti piuttosto diversi: 
SELECT *
FROM Tabella
WHERE CONTAINS (
		Colonna1
		,'parola chiave'
		);

SELECT *
FROM Tabella
WHERE FREETEXT (
		(Colonna1)
		,'parola chiave'
		);

CONTAINS: Offre la possibilità di eseguire più forme di ricerca e trova i valori corrispondenti della parola/frase specificata. Supporta l’utilizzo di operatori logici (AND, OR, NOT) e caratteri jolly come l’asterisco (si può utilizzare solo alla fine) e di altri specifici costrutti più avanzati (che esulano da questo articolo introduttivo sul tema).


Di seguito elencherò alcune forme possibili con CONTAINS.


  • Termine semplice. Restituisce i record che contengono la parola ‘steel’ nella colonna ‘Description’ 
SELECT *
FROM Production.ProductDescription
WHERE CONTAINS (
		Description
		,'steel'
		)
  • Termine di prefisso. Con l’utilizzo dell’asterisco restituisce i valori che iniziano per ‘ste’ nella colonna ‘Description’. 
SELECT *
FROM Production.ProductDescription
WHERE CONTAINS (
		Description
		,'ste*'
		)
  • Termine di Prossimità. Con l’utilizzo di NEAR possiamo trovare due parole che sono all’interno di una distanza definita in termini di caratteri. La seguente query ritorna i record in cui le parole ‘steel’ e ‘aluminium’ sono ad un massimo di 10 lettere di distanza. 
SELECT *
FROM Production.ProductDescription
WHERE CONTAINS (
		Description
		,'NEAR ((steel, aluminium), 10)'
		)
  • Termine di Generazione. Per considerare forme flesse o sinonimi usando il predicato CONTAINS bisogna utilizzare FORMSOF specificando INFLECTIONAL se vogliamo ottenere forme flesse e THESAURUS per i sinonimi. Di seguito la sintassi per questa forma di query. 
--Forma flessa 
SELECT *
FROM Production.ProductDescription
WHERE CONTAINS (
		*
		,'FORMSOF (INFLECTIONAL, Steel)'
		)

-- Sinonimo 
SELECT *
FROM Production.ProductDescription
WHERE CONTAINS (
		*
		,'FORMSOF (THESAURUS, Steel)'
		)
  • FREETEXT: È più restrittivo di CONTAINS, non offre più forme di ricerca. Di default applica una ricerca della parola/frase specificata con forme flesse e sinonimi, più che la corrispondenza ricerca il significato. Non supporta l’utilizzo di operatori logici e caratteri jolly. 

Da un punto di vista dei piani di esecuzione generati da query che utilizzano la ricerca full text, possiamo notare come su tutti vi sia un’operatore Table Valued Function di tipo ‘FulltextMatch’. 

 

Valued Function

5) Vediamo qualche esempio pratico. 

-- Esempio con CONTAINS  
USE AdventureWorks2019
GO

SELECT ProductDescriptionID
	,Description
FROM Production.ProductDescription
WHERE CONTAINS (
		Description
		,'steel'
		)
GO


CONTAINS
SELECT Description
FROM Production.ProductDescription
WHERE ProductDescriptionID <> 5
	AND CONTAINS (
		Description
		,'aluminum AND spindle'
		)
GO


Description
-- Esempio con FREETEXT  
USE AdventureWorks2019
GO

SELECT ProductDescriptionID
	,Description
FROM Production.ProductDescription
WHERE FREETEXT (
		Description
		,'bike'
		)
GO


DESCRIPTION

Oltre al risultato possiamo farci restituire un punteggio di rilevanza (RANK) che indica quanto il testo trovato corrisponda alla query specificata. 

La seguente query utilizza il predicato CONTAINSTABLE e restituisce l'ID e la descrizione dei prodotti con la parola "alluminio" accanto(NEAR) a "light" o "lightweight", limitato alle righe con rango 2 o superiore. 

-- Esempio con CONTAINSTABLE   
USE AdventureWorks2019
GO

SELECT FT_TBL.ProductDescriptionID
	,FT_TBL.Description
	,KEY_TBL.RANK
FROM Production.ProductDescription AS FT_TBL
INNER JOIN CONTAINSTABLE(Production.ProductDescription, Description, '(light NEAR aluminum) OR   

      (lightweight NEAR aluminum)') AS KEY_TBL ON FT_TBL.ProductDescriptionID = KEY_TBL.[KEY]
WHERE KEY_TBL.RANK > 2
ORDER BY KEY_TBL.RANK DESC;
GO


CONTAINSTABLE  

Possiamo notare che il primo risultato in cui le due parole ricercate sono vicine ha un punteggio alto di 160 mentre nelle ultime righe le parole in questione sono distanti una dall’altra e hanno un punteggio molto più basso di 6. 

L’ultimo esempio che vedremo è con il predicato FREETEXTTABLE che restituirà le righe che corrispondono o che sono simili e ordinandole per punteggio di rilevanza. 

-- Esempio con FREETEXTTABLE 
USE AdventureWorks2019
GO

SELECT KEY_TBL.RANK
	,FT_TBL.Description
FROM Production.ProductDescription AS FT_TBL
INNER JOIN FREETEXTTABLE(Production.ProductDescription, Description, 'perfect all-around bike') AS KEY_TBL ON FT_TBL.ProductDescriptionID = KEY_TBL.[KEY]
ORDER BY KEY_TBL.RANK DESC
GO


C:\Users\AliceSella\Desktop\FTS_Article_9.png

Possiamo vedere che i predicati di tipo FREETEXT trovano anche corrispondenze meno precise. Il primo risultato ha un punteggio di 247 ed ha una corrispondenza esatta mentre gli ultimi risultati con punteggio più basso hanno una corrispondenza simile o parziale. 

Per maggiori approfondimenti sul tema e per usi più avanzati dello strumento full text rimando alla documentazione Microsoft: https://learn.microsoft.com/it-it/sql/relational-databases/search/full-text-search?view=sql-server-ver16 


Conclusioni

Come abbiamo visto in questo articolo introduttivo fare ricerca testuale su grossi volumi nel modo classico (Simple Text Searching) è limitato nelle sue possibilità e può essere poco performante. Implementare ed utilizzare invece la funzionalità Full-Text search è molto facile e diretto. Questa permetterà di non appesantire il motore del database e introdurrà nuove metodologie di ricerca che saranno molto più efficienti e scalabili. 


di Nabil El Merzouki, pubblicato il 18/07/2024