Mise en œuvre de la recherche Full-Text dans SQL Server 2005

Par Jean-Pierre Riehl, posté le 05/08/2007

Profil : Développeur | Niveau : Intermédiaire (200)

Tags : SQL Server | Partager :

1 Introduction

La recherche Full-Text (recherche « plein-texte » en français) existe depuis la version 7 de SQL Server. Elle offre une recherche beaucoup plus puissante qu'un simple LIKE car les champs texte sont indexés comme des documents à part entière. On peut la rendre analogue aux moteurs de recherches devenus incontournables pour surfer sur le Web.

Pour avoir pratiqué la recherche Full-Text dans SQL Server 2000, la version 2005 a été améliorée même si elle n'offre pas de fonctionnalités supplémentaires. Elle a gagnée en pertinence et en performance mais surtout elle supporte nativement les différentes cultures comme FR-fr. Aussi, la mise en oeuvre et l'administration rentrent maintenant un peu plus dans les moules habituels de SQL Server.

La mise en ouvre de la recherche Full-Text (FTS) s'articule autour de 2 grandes actions :

· L'indexation : transformer les champs texte en informations structurées et indexées

· Le requêtage : rechercher les enregistrements les plus pertinents pour une requête exprimée

Bien sûr, il y a aussi la mise en place de la FTS et aussi son administration qu'il ne faut pas négliger lors d'une mise en oeuvre. Dans cet article, nous allons aborder tous ces points en détail.

Un script SQL d'exemple est disponible à cette adresse :

http://blog.djeepy1.net/public/labs/full-text-search/Mise_en_oeuvre_de_la_recherche_Full-Text_dans_SQL_Server_2005.sql.

2 Architecture

2.1 Big picture

Pour bien comprendre le fonctionnement de la recherche Full-Text (Full-Text Search, ie. FTS) nous allons entrer plus en profondeur dans l'architecture. La FTS s'articule autour de 3 process :

· SQL Server : le moteur de base de données pilote toutes les actions concernant la recherche

· Filter Daemon Process : en charge de la collecte des textes à indexer et de leur préparation pour l'indexation

· Search Process (Service Windows) : en charge du stockage de l'index Full-Text et de l'exécution des requêtes de recherche

Le schéma ci-dessous illustre l'architecture complète de la FTS et les interactions entre les différents composants. Les modules évoqués (Wordbreakers, Stemmers, etc.) sont explicités plus loin dans l'article.

2.2 Mise en place

La FTS est une option à sélectionner lors de l'installation d'une instance de SQL Server 2005. Cette fonctionnalité est disponible dans toutes les versions de SQL Server même s'il faut installer les Advanced Services pour la version Express.

On peut vérifier l'installation complète en exécutant une fonction T-SQL système.

   1:  SELECT FulltextServiceProperty('IsFulltextInstalled ')

Une fois le serveur préparé, il faut activer le Full-Text au niveau de la base de données. Par défaut toutes les bases de SQL Server 2005 ont le Full-Text activé. Pour le vérifier on utilise encore une fois une fonction système.

   1:  SELECT DatabasePropertyEx('AdventureWorks' , 'IsFulltextEnabled')

Dans le cas où il soit désactivé (restauration d'un backup par exemple), on exécute une procédure système.

   1:  Exec sp_fulltext_database 'enable'

Bien sûr, toute cette configuration peut être faite via Management Studio dans les propriétés de la base de données.

2.3 Catalogues

Toute la mise en place de la recherche dans une base de données se fait de façon déclarative. La première chose à mettre en place est de créer un catalogue Full-Text . On peut assimiler ce catalogue à un FILE dédié à recevoir les données d'indexation. On l'affecte à un FILEGROUP mais contrairement aux fichiers de données, on peut choisir son emplacement. D'ailleurs, si vous allez voir sur disque, vous verrez que ce n'est pas un simple fichier mais tout un répertoire.

L'instruction T-SQL pour créer un catalogue est la suivante :

   1:  CREATE FULLTEXT CATALOG NouveauCatalogue
   2:  WITH ACCENT_SENSITIVITY ON

Une remarque qui a été prise en compte par Microsoft pour la version 2005 est la prise en charge des accents en natif dans la recherche Full-Text. Ainsi on peut préciser la sensibilité aux accents dans la création du catalogue.

Encore une fois, toutes ces opérations sont faisables avec des assistants dans Management Studio.

Tout est maintenant prêt pour passer à l'indexation proprement dite des données.

3 Indexation

Il faut indiquer au moteur d'indexation les éléments à indexer de la base de données. Les éléments que l'on indexe sont des colonnes. Pour cela on place un INDEX de type Full-Text sur la table en indiquant les champs, comme on ferait pour un index classique.

Dans l'exemple ci-dessous, on indexe la colonne Description dans la table ProductDescription :

   1:  CREATE FULLTEXT INDEX ON dbo.ProductDescription
   2:       ( [Description] LANGUAGE 0x40c) --French(France)
   3:       KEY INDEX PK_ProductDescription
   4:       ON NouveauCatalogue

Bien que la syntaxe soit ressemblante, les options diffèrent. Nous allons les détailler dans les chapitres suivants.

Il est possible de créer les index Full-Text sur des tables ou des vues mais ces dernières devront être indexées pour disposer d'une clé unique (cf. 3.2).

3.1 Langage

   1:  [.]( [Description] LANGUAGE 0x40c)[.]

Pour chaque colonne indexée, on peut (et je dirais même on doit) préciser la langue de cette colonne. Ce paramètre va permettre une indexation optimum car le moteur de recherche Full-Text va prendre en considération les spécificités culturelles du langage choisi. Ces spécificités impactent l'indexation elle-même et le requêtage du moteur de recherche.

Les modules sensibles à la langue choisie sont :

· Le Word Breaker : ce module se charge du découpage des mots dans un texte. Il est évident que l'on ne traite pas le chinois traditionnel comme l'espagnol

· Le Stemmer : ce module se charge de décliner les mots (pluriels, conjugaisons, etc.). Par exemple, ride va être décliné en rider, riding, rides, etc. en langue anglaise et fondre va être décliné en fondante, fondues, fond, etc. en français.

· Les Noises : ce module se charge de nettoyer le texte à indexer dans mots « polluants ». Ainsi, des mots comme le ou la (the, of, etc. en anglais), les lettres isolées ne seront pas pris en compte par le moteur.

Vous pouvez voir la liste des langages supportés par l'indexation Full-Text dans la vue système sys.fulltext_languages.

3.2 Clé unique

   1:  [.]KEY INDEX PK_ProductDescription[.]

Pour créer un index Full-Text, on doit préciser une clé unique. Une clé primaire ou un index unique peuvent être utilisés. Cette clé unique permet à l'indexation de faire référence à l'enregistrement contenant le texte indexé.

La clé unique est nécessaire pour que le moteur de requêtage puisse retourner les enregistrements correspondants aux résultats de la recherche.

3.3 Indexation de fichiers

La recherche Full-Text permet d'indexer des champs de type texte mais elle peut aussi prendre en charge les champs de type XML ou varbinary.

Pour indexer un champ de type varbinary, il faut indiquer à la création de l'index le type de données que contient le champ. Pour cela, on doit ajouter un champ contenant l'extension du type de fichier que contient le champ binaire, par exemple .doc pour un document Word.

Dans l'exemple qui suit, on indexe une colonne DocData contenant un fichier et la colonne DocType contient l'extension correspondante.

   1:  CREATE FULLTEXT INDEX ON Production.ProductDescription
   2:       ( DocData TYPE COLUMN DocType LANGUAGE 0x40c)
   3:       KEY INDEX PK_ProductDescription_ProductDescriptionID
   4:       ON ProductsCatalog

Le moteur Full-Text contient de Filters qui prennent en charge l'indexation de ces fichiers. C'est ce filtre qui détermine l'extension qui doit être utilisée. Bien évidemment, seuls les types disposant d'un filtre installé seront pris en charge. Je vous invite à consulter l'aide en ligne pour connaître les types de fichiers supportés nativement, tout en sachant que des éditeurs tiers fournissent leurs propres filtres.

3.4 Assistants graphiques

Bien sûr toutes ces opérations sont disponibles via un formulaire graphique dans Management Studio.

4 Mise à jour de l'index

4.1 Modes de mises à jour

Une des problématiques rencontrées avec la recherche Full-Text est la mise à jour des index. En effet, on peut le voir dans l'architecture décrite plus haut, l'index Full-Text n'est pas stocké dans SQL Server, ce qui a pour conséquence qu'il ne soit pas mis à jour automatiquement quand on modifie ou que l'on insert des enregistrements dans la table.

Le Full-Text propose plusieurs façons de mettre à jour les index :

• Au fil de l'eau : c'est le CHANGE TRACKING
• Reconstruction complète : FULL POPULATION
• Incrémentale : INCREMENTAL POPULATION

Le premier choix peut sembler idéal car il permet d'avoir un index à jour en permanence. En effet, SQL Server se charge d'envoyer des requêtes de mise à jour au service d'indexation quand on modifie les champs indexés de la table. Mais ce mode comporte quelques contreparties, il entraine une consommation supplémentaire des ressources de votre serveur. Aussi, il peut y avoir une légère latence car l'envoi des requêtes se fait en asynchrone. Pour préserver les ressources, il est possible de ne répercuter les changements que manuellement par un job ou une requête.

Pour activer ou désactiver le change tracking, il suffit d'ajouter l'option suivante à la création de l'index :

   1:  WITH CHANGE_TRACKING { AUTO | MANUAL | OFF }

Le second choix est quant à lui est assez facile à comprendre, il reconstruit complètement l'index Full-Text à chaque mise à jour. Cette reconstruction est forcément appelée manuellement. Il y aura un moment ou l'index sera vide puis il se remplira petit à petit. Cela peut avoir des effets de bord sur la production.

Le dernier choix fonctionne exactement comme le précédent sauf qu'il reconstruit l'index de façon incrémentale, seuls les enregistrements modifiés depuis la dernière construction seront utilisés pour la mise à jour de l'index. Pour que ce type de mise à jour fonctionne, vous devez obligatoirement placer une colonne de type timestamp dans votre table.

Comme pour la reconstruction complète, il est nécessaire d'appeler manuellement avec un job par exemple. Cela permet de maîtriser la consommation de ressources pour mettre à jour l'index, pendant des périodes d'inactivité du serveur ou dans des plans de maintenance par exemple.

On lance les mises à jour en appelant l'instruction suivante, chaque mot-clé correspond à la méthode utilisée :

   1:  ALTER FULLTEXT INDEX ON dbo.ProductDescription
   2:  START { UPDATE | FULL | INCREMENTAL } POPULATION

On peut changer le mode de mise à jour à posteriori en modifiant l'index avec un ALTER FULLTEXT INDEX.

4.2 Première indexation

Lors de la création de l'index, son remplissage démarre tout de suite, à moins d'avoir précisé l'option NO POPULATION. Dans ce cas, pensez à lancer le remplissage avec un ALTER FULLTEXT INDEX.

Pour connaître le statut de l'index, vous pouvez consulter les vues systèmes :

   1:  select has_crawl_completed from sys.fulltext_indexes

4.3 Assistants graphiques

Comme pour toutes les opérations, on peut contrôler la mise à jour de l'index depuis l'interface de Management Studio.

5 Requêtage

Maintenant que le catalogue et les index Full-Text sont créés, on peut maintenant passer à la partie requêtage et tester le moteur de recherche de SQL Server 2005.

5.1 Types de requêtage

Il y a 2 types de requêtes que l'on peut envoyer au moteur de recherche, les requêtes de type FREETEXT et les requêtes de type CONTAINS.

· FREETEXT : effectue une recherche basée sur un texte libre, le moteur Full-Text se charge du découpage et de l'interprétation de la recherche. C'est le type de recherche le plus simple à mettre en ouvre car elle peut prendre directement la saisie d'un utilisateur.

   1:  Select *
   2:  From dbo.Products
   3:  Where Freetext(*, 'croissant au jambon')

· CONTAINS : effectue une recherche basée sur une liste de mots ou phrases « préparés ». La chaîne de recherche doit être construite avec des mots-clés et les différentes options utilisées. Chaque mot doit être séparé par AND ou OR et les phrases doivent être entourées de guillemets. Elle est plus complexe car elle nécessite un pré-mâchage de la saisie utilisateur mais forcément plus configurable.

   1:  Select *
   2:  From dbo.Products
   3:  Where Contains(*, 'croissant AND jambon')

Le paramètre * qui se trouve dans les fonctions FreeText et Contains indique que la recherche doit se faire sur tous les champs indexés de la table (ici Products). On peut bien évidemment ne rechercher que sur un seul champ. Par exemple, voici la requête pour ne rechercher que sur le champ Description :

   1:  Select *
   2:  From dbo.Products
   3:  Where Contains([Description], 'croissant AND jambon')

En plus d'avoir 2 types de requêtes, il existe 2 façons de les utiliser. Les exemples ci-dessus utilisent la recherche comme une condition dans la clause WHERE. Il est aussi possible d'utiliser les résultats d'une recherche comme source de données dans une clause FROM. Pour cela, on utilise une syntaxe qui ressemble à celle d'une fonction qui renvoie une table. Les mots-clés deviennent FREETEXTTABLE et CONTAINSTABLE.

   1:  Select *
   2:  From FreeTextTable(dbo.Articles , [Description], 'vélo femme')

A noter qu'il est possible de remplacer le nom du champ par le caractère * afin de rechercher sur l'ensemble des champs indexés de la table.

Si vous exécutez cette requête, vous remarquerez que seules 2 colonnes sont retournées : KEY et RANK ; les colonnes de la table requêtée ne sont pas remontées.
Key correspond à la clé unique de la table qui a été indiquée à la création de l'index Full-Text, cela va nous permettre de faire la jointure avec le reste des données.

Rank correspond à l'indice de pertinence accordé à l'enregistrement. Ce chiffre est compris entre 1 et 1000 et est donné à titre indicatif, il ne correspond en aucun cas à un pourcentage de pertinence.

   1:  Select Articles.*
   2:  From FreeTextTable(dbo.Articles,[Description],'vélo femme') SearchResults
   3:      Inner Join dbo.Articles On Articles.ArticleID = SearchResults.Key
   4:      Order By SearchResults.Rank Desc

La pertinence n'est récupérable qu'avec les méthodes CONTAINSTABLE et FREETEXTTABLE, les méthodes CONTAINS et FREETEXT ne vérifient que la correspondance aux mots-clés recherchés.

Les deux fonctions CONTAINSTABLE et FREETEXTTABLE prennent un paramètre optionnel supplémentaire qui permet de borner le nombre de résultats remontés par le moteur Full-Text.

   1:  From FreeTextTable(dbo.Articles , *, 'vélo femme', 100)

Ce paramètre est conseillé si le nombre de résultats potentiels est élevé. Cela permet de limiter l'utilisation en ressources du serveur, surtout si on peut déterminer le nombre d'enregistrements maximum à afficher.

5.2 Options de recherche

La recherche Full-Text de SQL Server 2005 recèle de nombreuses options pour affiner le degré de pertinence des résultats. Ces options sont utilisables uniquement avec les requêtes de type CONTAINS.

5.2.1 Noise words

Littéralement, on peut traduire le terme par « bruits ». On entend par Noise words les mots qui peuvent influencer une recherche car ils sont trop génériques et par conséquent polluent les résultats. Ces mots sont filtrés au moment où l'index est construit ou mis à jour. Ils sont aussi retirés de la chaîne de recherche avant qu'elle soit découpée et analysée.

On peut trouver la liste des bruits par langue dans le répertoire suivant :

%SQL Install%\ MSSQL\FTData\noise.txt

Vous pouvez bien entendu modifier vous-même la liste, rajouter vos propres mots inutiles en fonction de votre domaine. Cela nécessitera une reconstruction complète de l'index.
Attention vous risquez d'être surpris par les mots français que Microsoft considère comme étant des bruits : bernique, diantre, fichtre et autres jarnicoton vous ferons redécouvrir la langue de Molière. 

5.2.2 Thésaurus

Le thésaurus est une sorte de dictionnaire qu'utilise la recherche Full-Text pour qualifier les mots-clés qui lui sont passés. Le thésaurus permet d'influer sur les mots de 2 façons :

· Les remplacements : le mot est remplacé par un autre avant de lancer la recherche. Cela permet de corriger des erreurs d'orthographe ou de chercher sur des mots complets plutôt que des abréviations.
Ex : tuya à thuya ou VTT à vélo tout-terrain

· Les expansions : cela permet d'étendre la recherche à des mots-clés connexes ; par exemple, si un utilisateur tape coca, on peut lui remonter aussi les sodas au cola pour ne pas restreindre à une marque bien connue

Les fichiers de thésaurus se trouvent dans le répertoire suivant. Il y en a un par langage.

%SQL Install%\ MSSQL\FTData\ts.xml

Ce sont des fichiers XML. Pour que leur modification soit prise en compte, il faut redémarrer le service de recherche Full-Text. On ne peut donc pas les mettre à jour sans coupure de service.

Voici le format d'un fichier de thésaurus.

   1:  <XML ID="Microsoft Search Thesaurus">
   2:      <thesaurus>
   3:      <diacritics_sensitive>0diacritics_sensitive>
   4:          <expansion>
   5:              <sub>cocasub>
   6:              <sub>colasub>
   7:              <sub>cokesub>
   8:          expansion>       
   9:          <replacement>
  10:              <pat>tuyapat>
  11:              <pat>tuillapat>
  12:              <sub>thuyasub>
  13:          replacement>      
  14:      thesaurus>
  15:  XML>

Le thésaurus est utilisé de façon automatique dans une recherche de type FREETEXT. En revanche, dans une requête de type CONTAINS, il faut y recourir explicitement. La requête prend alors cette forme :

   1:  Where Contains(*, 'FORMSOF(Thesaurus, ')

Si le mot-clé est une phrase, il faudra l'encadrer de guillemets ("caractère").

5.2.3 Formes de mot

Cette option permet de décliner un mot sous toutes ses formes : conjugaisons, adverbiales, etc.). Les Stemmers décrits plus haut prennent en charge la déclinaison en fonction de la langue.

Comme pour le thésaurus, le FREETEXT bénéficie automatiquement de cette option alors qu'une directive est nécessaire pour son utilisation dans un CONTAINS :

   1:  Where Contains(*, 'FORMSOF(Inflectional, ')

5.2.4 Commençant par

Cette option permet de considérer que le ou les mots-clés fournis ne sont que le début des mots recherchés. Les mots-clés doivent obligatoirement être encadrés de guillemets.

   1:  Where Contains(*, '"*"')

Par exemple, avec "lard*", le Full-Text ramènera des lardons et du lard. Quand plusieurs mots sont fournis, chaque mot peut être complété. Par exemple, "lard fume*" cherchera des lardons fumés.
Attention, on parle bien de préfixe, une recherche sur "lards*" ne ramènera jamais des lardons.

5.2.5 Distance

La fonctionnalité de distance mesure l'écart entre les mots dans le texte qui le contient. Ainsi, si les deux mots ont une forte corrélation entre eux car ils ne sont séparés que par un ou deux autres mots, l'enregistrement remontera avec un meilleur indice.

   1:  Where Contains(*, ' NEAR ')

Par exemple, on peut tester la distance sur les mots confiture et myrtilles pour remonter principalement de la confiture de myrtilles.

5.2.6 Pondération

Il est possible de pondérer certains mots recherchés pour leur donner plus d'importance. On utilise la syntaxe suivante :

   1:  Where Contains(*, 'ISABOUT( Weight(.8),  Weight (.2))')

Dans cet exemple, on considère que le premier mot à une importance de 80% et le second seulement de 20%. La pondération se fait sur les mots-clés et non sur les colonnes. Pour pondérer sur les colonnes, on peut complexifier la requête de cette façon :

   1:  Select [key], Sum(RNG) as [Rank]
   2:  From (
   3:      Select [key], [rank] * 0.4 as RNG
   4:      From ContainsTable(dbo.Articles , [Description], '') 
   5:      Union
   6:      Select [key], [rank] * 0.6 as RNG
   7:      From ContainsTable(dbo.Articles , [Title], '') ) as SearchResults
   8:  Group By [key]
   9:  Order By [Rank] Desc

Dans cet exemple, on considère que le titre a plus de poids que la description (60%-40%). On lance 2 requêtes et on agrège les résultats avec un petit calcul implémentant la pondération.

Il est évident que les 2 requêtes envoyées au moteur de recherche dégradent les performances mais il n'y a pas d'autre solution puisque l'on ne peut pas indiquer de poids à une colonne lors de la création de l'index.

Note : on aurait pu faire la même requête avec une jointure entre les deux jeux de résultats de recherche mais les performances sont équivalentes.

5.2.7 Multi-option

Toutes ces options sont utilisables ensembles, jointes par les mots-clés AND, OR ou AND NOT.

   1:  Select *
   2:  From dbo.Products
   3:  Where Contains([Description], 
   4:     'FORMSOF(INFLECTIONAL fondre) AND "hiver*" AND pere NEAR noel')

6 Maintenance

Les opérations de maintenance ne sont pas le propos de cet article (surtout parce que ce n'est pas ma spécialité) mais je vais en dire tout de même quelques mots.

Comme pour tout ce qui touche à un système de production, il faut une politique de maintenance. On peut y inclure les mises à jour d'index manuelles (incrémentales ou complètes) décrites plus haut mais je pensais plutôt aux opérations de sauvegarde et, qui dit sauvegarde, dit restauration.

Sachez que depuis SQL 2005, l'index Full-Text est inclus dans le backup comme un fichier de la base de données à part entière. A la restauration, vous récupérerez donc le catalogue Full-Text et ses index comme ils ont été laissés, c'est-à-dire directement utilisables dans la plupart des cas.

7 Conclusion

SQL Server embarque un réel moteur de recherche performant et qui couvre un spectre fonctionnel important. La version 2005 a été grandement améliorée sur tous les points qui lui faisaient défaut : la maintenance, la gestion des cultures, la sensibilité aux accents, l'administration, etc.

Reste une donnée et non des moindres, c'est la pertinence de recherche des algorithmes de Microsoft. Personnellement, je les trouve satisfaisants mais ce n'est qu'une question de point de vue et j'ai vu quelques chefs produit déçus, bien souvent parce qu'ils s'attendaient à avoir un google embarqué. A cela je réponds que les vecteurs primordiaux pour améliorer une fonctionnalité de recherche sont :

· La bonne utilisation des options en adéquation avec le produit ou le domaine (et donc le développement associé pour prémâcher la saisie utilisateur)

· le processus humain à mettre en place pour améliorer la recherche, en alimentant le thésaurus avec les corrections orthographiques et les synonymes correspondant au domaine métier par exemple.

Et si ce n'est pas suffisant, il existe des produits tiers sur le marché, compatibles avec SQL Server 2005. J'ai eu l'occasion de jouer avec le moteur d'Exalead et les fonctionnalités offertes sont intéressantes. Mais le prix à payer par rapport à la gratuité de la recherche Full-Text, coupe bien souvent court à toute conversation.

8 Annexes

8.1 SQL Server 2000

Les commandes T-SQL n'étaient pas les mêmes avec la version 2000 de SQL Server. Voici un petit aide-mémoire pour faire la correspondance.

 

Version 2000	Equivalence 2005
sp_fulltext_catalog	CREATE/ALTER FULLTEXT CATALOG
sp_fulltext_table	CREATE/ALTER FULLTEXT INDEX
sp_fulltext_column	N/A
sp_help_fulltext_catalogs	sys.fulltext_catalogs
sp_help_fulltext_tables	sys.fulltext_indexes
sp_help_fulltext_columns	sys.fulltext_index_columns

 

Note : ces procédures stockées systèmes sont utilisables dans SQL Server 2005 mais ne seront pas conservées dans la prochaine version.

8.2 Soundex

Il existe une fonction dans SQL Server que certaines personnes associent aux problématiques de recherche. C'est la fonction Soundex() qui permet de calculer la ressemblance entre 2 mots. On parle ici de ressemblance phonétique, chose que ne sait pas gérer, le Full-Text.

Cela est bien gentil mais il faut faire cette comparaison mot à mot, ce qui, dans un contexte de recherche online est inenvisageable sur des gros jeux de données (encore moins avec des textes complets)

Commentaires