Entenda o FULL – Text Search!

Posted on outubro 26, 2011

5


Introdução

 

A necessidade de realizar pesquisas em campos textos semore foi uma grande realizade. Em algumas das vezes conseguimos utilizar o LIKE, um operador que realiza a busca de uma palavra ou letra dentro de um campo texto. Porém muito das vezes a consulta pode ser prejudicada, porque o índice pode não ser utilizado. Tendo essa necessidade em mente temos um produto integrado totalmente com a engine de busca do SQL Server. O Full-Text Search permite que seja realizado pesquisas avançadas além da capacidade tradicional de um SELECT com LIKE.

 

A consulta que utiliza esse mecanismos poderá retornar:

 

  • Tempos Verbais;
  • Resultados retornados de campos VARBINARY(MAX) podendo ser este dados armazenado como um arquivo .PDF.
  • Sinonimos da(s) palavra(s) informadas.

 

Sendo um recurso introduzido na versão 7.0 do SQL Server a mesma continua se aprimorando e otimizando os seus recursos. Na versão 2005 o produto não era totalmente integrado porque era necessário ser criado uma estrutura externa para que o serviço fosse habilitado.

 

Arquitetura

 

Os seguintes processos fazem parte da arquitetura para seu total funcionamento.

 

SQL Server Process (Sqlservr.exe) = Realiza o gerenciamento dos índices e consultas Ou seja esse serviço possui total integração com a Engine para que o Optimizer possa otimizar as consultas realizadas.

 

Filter Daemon Host Process (Fdhost.exe) = Todos os filtros são carregados neste processo, sendo assim a instância utiliza o processo de multithread para que os filtros sejam implementados, sendo assim idependente dos componentes, para que o SQL Server seja protegido de componentes de terceiros.

 

SQL Full-Text Filter Daemon Launcher (Fdlauncher.exe) = Realiza a incialização do processo quando requisitado.

 

 

full-text search architecture

 

Os Componentes para essa arquitetura são:

 

User Tables = Aonde ficam armazenados os dados para serem indexados.

 

FULL-Text Gatherer = Trabalha junto com o FULL-Text Crawl. Ele é responsável pelo agendamento e populamento dos FULL-Text Indexes e monitoramento dos catálogos.

 

Theasurus Files = Aonde ficam contidos os sinónimos.

 

Stop List = Local aonde fica armazenado lista de palavras em comum que não sejam válidas para pesquisa.

 

SQL Server Query Processor = Local aonde a consulta é compilada e executada. Se a consulta possuir FULL-Text a mesma é enviada para a Engine do FULL-Text Engine.

 

Index Writer (Indexer) = O escritor de índices que realiza a construção da estrutura que armazena os Tokens dos Índices.

 

Filter Daemon Manager = Responsável pelo monitoramento de Status do processo Daemon Host (Fdhost.exe).

 

 

Terminologia

 

O FULL-Text possui uma terminologia própria, entenderemos a seguir quais são:

 

Term = Uma palavra, frase ou caracter incluso na consulta.

 

Full-Text Catalog = Um objeto virtual que representa um grupo de índices. Quando é realizado a criação de um catálogo no SQL Server 2008 o mesno não pertence a nenhum filegroup.

 

Word Breaker = Processo que acha faixas/tokens ou seja uma padronização para a consulta baseada no Collation do Banco de Dados.

 

Token = Uma palavra ou caracter que define um Word Breaker.

 

Stemmer = Um processo que realiza a conjugação de verbos baseada na Collation do Banco de Dados.

 

Theasurus = Um XML que realiza a definição dos sinónimos.

 

StopWord = Uma palavra que é usada para adicionar um significado para a pesquisa dentro da estrutura, assim como [e, que]…

 

StopList = Objeto que realiza o gerenciamento das StopWords. No SQL Server 2008 você pode criar suas próprias StopWords.

 

Filter = Componente que realiza a extração das informações dos documentos armazenados em VARBANARY(MAX ou Image e assim manda as informações para os WordBrakers.

 

Population(Crawl) = Preocesso de adicionar os dados ou povoá-los dentro do índice durante a criação ou alteração do mesmo. Esse processo pode se realizar automaticamente ou manualmente.

 

Full-Text Engine = Um componente que realiza a administração de tarefas do processo do FULL-Text Search.

 

Configuração

 

Para a criação e gerenciamento dos full-text indexes, é necessário um entendimento básico em relação a configuração pelo SSMS. Na instalação do SQL Server o FULL-Text Search já é instalado. Para iniciar o processo de criação é necessário primeiramente que seja criado os FULL-Text Catalogs.

 

FULL-Text Catalogs

Os catálogos são criados para cada banco de dados porém eles não podem ser criados nos banco de dados de sistema como MODEL, MASTER, MSDB, TEMPDB.

A nomenclatura para a criação dos catálogos é:

CREATE FULLTEXT CATALOG catalog_name      [ON FILEGROUP filegroup ]      [IN PATH 'rootpath']      [WITH <catalog_option>]      [AS DEFAULT]      [AUTHORIZATION owner_name ]

<catalog_option>::=      ACCENT_SENSITIVITY = {ON|OFF}

 

CREATE DATABASE FullTextSearch

go

USE FullTextSearch

go

 

CREATE FULLTEXT CATALOG CatalogoDados AS DEFAULT

 

FULL-Text Indexes

Depois da criação do catálogo, o próximo passo é a criação do FULL-Text Indexe, para isso temos que:

  • É necessário que exista um FULL-Text Catalog neste banco de dados.
  • Só é permitido a criação de um FULL-Text Index por tabela.
  • É necessário que na tabela tenha uma UNIQUE que náo possa permitir nulos.
  • CREATE FULLTEXT INDEX ON table_name       [ ( { column_name              [ TYPE COLUMN type_column_name ]              [ LANGUAGE language_term ]         } [ ,...n]             ) ]     KEY INDEX index_name         [ ON <catalog_filegroup_option> ]         [ WITH [ ( ] <with_option> [ ,...n] [ ) ] ]
    [;]
    
    <catalog_filegroup_option>::=   {         fulltext_catalog_name   | ( fulltext_catalog_name, FILEGROUP filegroup_name )   | ( FILEGROUP filegroup_name, fulltext_catalog_name )   | ( FILEGROUP filegroup_name )   }
    
    <with_option>::=   {    CHANGE_TRACKING [ = ] { MANUAL | AUTO | OFF [, NO POPULATION ] }   | STOPLIST [ = ] { OFF | SYSTEM | stoplist_name }

 

Table_Name = Nome da tabela/View aonde a estrutura será criada.

 

Column_Name = Nome da coluna aonde será criado o FULL-Text Index.

 

Type_Column = Extensão para a criação (Extensão), podendo ser VARBINARY, VARBINARY(MAX) ou IMAGE . Se a opção for especificada mais não for informado um erro será gerado.

 

Key Index = Definição do nome do UNIQUE INDEX solicitado pelo FULL-Text Index.

 

FullText_Catalog_Name = O Nome lógico do catálogo.

 

FILEGROUP = Nome do filegroup existente aonde o FUL-Text Index é armazenado.

 

Change_Tracking = Povoamento do FULL-Text Index.

  • AUTO = O Povoamento será automático.
  • MANUAL = O Povoamento será manual, sendo assim o mesmo só será atualizado com um ALTER ou START UPDATE POPULATION.

OFF = O SQL Server não realizará o povoamento das informações.

 

STOPLIST = Especifica o StopList associado com o FULL-Text Index. Se a opção não for especificada, então ele pegará como padrão a do sistema.

 

SELECT *

INTO FullTextSearch.dbo.Produto

FROM AdventureWorks.Production.Product

–Criação Unique KEY

ALTER TABLE FullTextSearch.dbo.Produto

ADD CONSTRAINT PK_Produto_ProductID

PRIMARY KEY (ProductID)

 

–Criação FULL-Text Index

CREATE FULLTEXT INDEX ON FullTextSearch.dbo.Produto

      KEY INDEX PK_Produto_ProductID –Unique KEY

      ON CatalogoDados — FULL-Text Catalog

WITH CHANGE_TRACKING AUTO,

       STOPLIST SYSTEM; –STOPList do Sistema

 

 

Consultas

 

Depois de toda a estrutura criada, é hora de realizarmos as pequisas, para que esse mecanismo funcione, podemos escolher entre CONTAINS, FREETEXT ou predicados como CONTAINSTABLE, FREETEXTTABLE que são funções. Sendo asism temos que:

 

– CONTAINS = É um predicado que realiza a busca de um texto específico na coluna. O funcionamento desse recurso é similar ao predicado LIKE.

 

– FREETEXT = Neste predicado é analisado todas as palavras da frase e depois é devolvido os registros que contém a frase ou algum fragmento do que foi especificado.

 

– CONTAINSTABLE = Similar ao funcionamento do CONTAINS sendo que sua diferença é que a devolução das informações em duas colunas a [KEY] que contém o valor da palavra chave e a informação na qual buscamos.

 

– FREETEXTTABLE = Exatamente equivalente ao CONSTAINSTABLE, porém a pesquisa dentro do campo é retornado uma busca de todas as palavras.

 

 

Aplicação

 

Para visualizar se o povoamento do FULL-Text Index foi realizado podemos utilizar a função – FULLTEXTCATALOGPROPERTY – Se a população estiver em progresso então o valor mostrado será 1. Além disso podemos vizualizar um DMV  sys.dm_fts_index_population que retorna o status do povoamento dos dados e ainda para vizualizar as colunas aonde estão os FULL-Text Indexes podemos visualizar por outra DMV sys.dm_index_columns.

 

SELECT FULLTEXTCATALOGPROPERTY(‘CatalogoDados’, ‘PopulateStatus’) AS StatusPovoamento;

 

SELECT DB_NAME(database_id) AS ‘Database Name’

, database_id AS ‘DB_ID’

, OBJECT_NAME(table_id) AS ‘Table Name’

, table_id

, population_type_description AS ‘Population Desc.’

, status_description AS ‘Status Desc.’

, completion_type_description AS ‘Completion Desc.’

, start_time

FROM sys.dm_fts_index_population;

 

 

SELECT OBJECT_NAME (object_id) AS TableName

, object_id

, COL_NAME(object_id, column_id) AS ColumnName

, column_id

, COL_NAME(object_id, type_column_id) AS TypeColumn

, language_id

FROM sys.fulltext_index_columns;

 

Pesquisas

 

Tendo toda a estrutura criada, agora iremos fazer algumas pesquisas para analisar o comportamento do FULL-Text.

 

SELECT *

FROM FullTextSearch.dbo.Produto AS P

WHERE CONTAINS(P.Name ,‘LL’)

 

image

 

SELECT *

FROM FullTextSearch.dbo.Produto AS P

WHERE FREETEXT(P.Name ,‘LL’)

 

image

 

Consultas Utilizando Conjugação e Verbo e Sinónimos.

 

–Verbo

SELECT *

FROM FullTextSearch.dbo.Produto AS P

WHERE FREETEXT(P.Name ,‘FORMSOF(INFLECTIONAL,Chainring’)

 

image

–Sinónimos

SELECT *

FROM FullTextSearch.dbo.Produto AS P

WHERE FREETEXT(P.Name ,‘FORMSOF(THESAURUS,Race’)

 

image

 

Como podemos ver, conseguimos realizar várias tipos de pesquisas mais otimizadas e bem específicas com esse recurso.

 

Referências

http://technet.microsoft.com/en-us/library/ms142541.aspx

 

http://www.criarweb.com/artigos/introducao-freetex-e-contains-em-sql-server.html

Posted in: SQL Server 2008