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
I vantaggi di 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.
Funzionamento di Full-Text search con un esempio pratico
USE AdventureWorks2019; GO CREATE FULLTEXT CATALOG AdvWksProdDescFTCat;
Il catalogo appena creato è visibile in Databases>Adventureworks2019>Storage>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 e 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:
Invece nel caso con FREETEXTTABLE (vale anche per CONTAINSTABLE) si possono vedere le due colonne aggiuntive.
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.
5) Vediamo qualche esempio pratico.
-- Esempio con CONTAINS USE AdventureWorks2019 GO SELECT ProductDescriptionID ,Description FROM Production.ProductDescription WHERE CONTAINS ( Description ,'steel' ) GO
SELECT Description FROM Production.ProductDescription WHERE ProductDescriptionID <> 5 AND CONTAINS ( Description ,'aluminum AND spindle' ) GO
-- Esempio con FREETEXT USE AdventureWorks2019 GO SELECT ProductDescriptionID ,Description FROM Production.ProductDescription WHERE FREETEXT ( Description ,'bike' ) GO
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
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
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