Crear Catálogos e Indices de Texto Completo

La búsqueda de texto completo permite búsquedas aproximadas en bases de datos SQL Server 2012. Antes de empezar a usar predicados y funciones de texto completo, debe crear índices de texto completo dentro de catálogos de texto completo. Después de crear índices de texto completo sobre las columnas de caracteres en su base de datos, puede buscarlos por:

·         Término Simple, esto es, una o más palabras o frases específicas.

·         Término de Prefijo, los cuales son términos con que las palabras o frases empiezan.

·         Término de Generación, significando formas inflexionales de las palabras.

·         Término de Proximidad, o palabras o frases cercanas a otra palabra o frase.

·         Término Thesaurus, o sinónimos de una palabra.

·     Término Ponderado, que son palabras o frases que utilizan valores con su peso personalizado.

·         Búsqueda Semántica Estadística, o frases clave en un documento.

·         Documentos Similares, donde la similitud es definida por frases claves semánticas.

Componentes de Búsqueda de Texto Completo

Para empezar a utilizar la búsqueda de texto completo, tiene que entender los componentes de texto completo. Para empezar, puede comprobar si la Búsqueda de Texto Completo está instalada, por utilizar la siguiente consulta.

SELECT SERVERPROPERTY('IsFullTextInstalled');

Si Búsqueda de Texto Completo no está instalado, debe volver a ejecutar el setup.

Puede crear índices de texto completo sobre columnas de tipo CHAR, VARCHAR, NCHAR, NVARCHAR, TEXT, NTEXT, IMAGE, XML y VARBINARY(MAX). Además de usar índices de texto completo sobre datos carácter de SQL Server, puede almacenar documentos completos en columnas binarias o XML, y utilizar consultas de texto completo sobre estos documentos. Las columnas de tipo de datos VARBINARY(MAX), IMAGE, o XML requieren una columna de tipo adicional, en la cual almacena la extensión del archivo (por ejemplo, .docx, .pdf, o .xlsx) del documento en cada fila.

Necesita filtros apropiados para los documentos. Filtros, llamados ifilters en la terminología de texto completo, extraer la información textual y eliminar el formato de los documentos. Puede comprobar qué filtros están instalados en su instancia por utilizar la siguiente consulta.

EXEC sys.sp_help_fulltext_system_components 'filter';

Además de utilizar el procedimiento almacenado de sistema, también puede comprobar qué filtros están instalados en su instancia por consultar la vista de catálogo sys.fulltext_document_types, de la siguiente manera.

SELECT document_type, path

FROM sys.fulltext_document_types;

Muchos formatos populares son soportados por defecto. Puede instalar filtros adicionales, tales como los filtros para formatos de documentos Microsoft Office 2010. Puede descargar el paquete de filtros de Microsoft Office 2010 en http://www.microsoft.com/es-ES/download/details.aspx?id=17062.

Después de descargar los paquetes de filtros, se instalan en su ordenador con su instancia SQL Server, por utilizar las instrucciones proporcionadas con los paquetes de filtros. Para un paquete de filtros de Office 2010, por ejemplo, todo lo que necesita hacer es ejecutar el archivo descargado de extracción automática.

Después de instalar el paquete de filtros en su computadora, necesita registrar los filtros en SQL Server por utilizar el siguiente comando.

EXEC sys.sp_fulltext_service 'load_os_resources', 1;

Puede necesitar reiniciar el SQL Server. Después de reiniciarlo, compruebe si los filtros fueron instalados exitosamente por utilizar el procedimiento de sistema sys.sp_help_fulltext_components de nuevo.

Los separadores de palabras y lematizadores (stemmers) realizan análisis lingüístico en todos los datos de texto completo. Puesto que las reglas difieren de un idioma a otro, los separadores de palabras y lematizadores son específicos del idioma. Un separador de palabra identifica las palabras individuales (o tokens). Los tokens son insertados en un índice de texto completo en formato comprimido. El lematizador genera formas inflexionales de una palabra basada en las reglas de un idioma. Puede utilizar la siguiente consulta para comprobar que idiomas son soportados en SQL Server.

SELECT lcid, name

FROM sys.fulltext_languages

ORDER BY name;

Los lematizadores son específicos del idioma. Si utiliza una versión localizada de SQL Server, SQL Server Setup establece el idioma de texto completo por defecto al idioma de su instancia, si el idioma es soportado en su instancia. Si el idioma no es soportado, o si utiliza una versión no localizada de SQL Server, el idioma de texto completo por defecto es el inglés. Puede especificar un idioma diferente para cada columna indexada de texto completo. Puede cambiar el idioma predeterminado por usar el procedimiento de sistema sys.sp_configure.

Los separadores de palabras son específicos del idioma también. Si un separador de palabras no existe para el idioma de su instancia, un separador de palabras neutral es utilizado. El separador de palabras neutral utiliza sólo caracteres neutrales como espacios para la separación de texto en palabras individuales.

Imagine que tiene documentos acerca de SQL Server. La frase "SQL Server" probablemente aparece en todos los documentos. Tal frase no le ayuda con las búsquedas; sin embargo, satura un índice de texto completo. Puede evitar la indexación de tales palabras ruidosas por crear stoplists de stopwords. Puede comprobar los stopwords y stoplists actuales en su base de datos por utilizar las consultas siguientes.

SELECT stoplist_id, name

FROM sys.fulltext_stoplists;

SELECT stoplist_id, stopword, language

FROM sys.fulltext_stopwords;

Las consultas de texto completo pueden buscar no sólo por las palabras que suministre en una consulta; pueden buscar por sinónimos también. SQL Server encuentra sinónimos en archivos thesaurus. Cada idioma tiene un archivo thesaurus XML asociado. La ubicación de los archivos thesaurus para una instancia por defecto es SQL_Server_install_path\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\FTDATA\. Puede editar manualmente cada archivo thesaurus y configurar los siguientes elementos:

·      Diacritics_sensitive. Establecer el valor de este elemento a 0 si el idioma no es sensible al acento, o 1 si es sensible al acento.

·         Expansion. Utilice este elemento para agregar palabras de expansión a una palabra. Por ejemplo, puede agregar la palabra de expansión "autor" a la palabra "escritor" con el fin de buscar "autor", también, cuando un usuario final busca la palabra "escritor".

·         Replacement. Utilice este elemento para definir palabras o términos de reemplazo para una palabra o término específico. Por ejemplo, "Windows 2008" podría ser un reemplazo para "Win 2k8". En tal ejemplo, SQL Server podría buscar por "Windows 2008", aun cuando "Win 2k8" fue utilizado en un término de búsqueda.

Después de editar el archivo thesaurus para un idioma específico, debe cargarlo con la siguiente llamada al procedimiento de sistema.

EXEC sys.sp_fulltext_load_thesaurus_file 1033;

El parámetro del procedimiento denota el ID lenguaje; en este caso, (1033), que es el idioma US English.

Las consultas de texto completo pueden buscar también en las propiedades del documento. Cuales propiedades pueden ser buscadas, dependen del filtro del documento. Puede crear una lista de propiedades de búsqueda para definir las propiedades de búsqueda para sus documentos. Puede incluir propiedades que un filtro específico puede extraer de un documento.

Creando y Gestionando Catálogos e Indices de Texto Completo

Una vez que tenga toda la infraestructura de texto completo en su lugar, puede comenzar a usarla. Los índices de texto completo son almacenados en los catálogos de texto completo. Un catálogo de texto completo es un objeto virtual, un contenedor para índices de texto completo. Como un objeto virtual, no pertenece a algún filegroup.

La siguiente es la sintaxis para crear catálogos de texto completo.

CREATE FULLTEXT CATALOG nombre_catalogo

     [ON FILEGROUP filegroup ]

     [IN PATH 'rutaraiz']

     [WITH <opcion_catalogo>]

     [AS DEFAULT]

     [AUTHORIZATION nombre_propietario ]

<opcion_catalogo>::=

     ACCENT_SENSITIVITY = {ON|OFF}

Las opciones ON FILEGROUP e IN PATH se mantienen por compatibilidad con SQL Server 2008 y versiones anteriores y no tienen efecto en SQL Server 2012; debería evitar utilizarlas. La opción ACCENT_SENSITIVITY determina si los índices de texto completo en este catálogo son sensibles al acento o no. Si cambia esta opción más adelante, tendrá que reconstruir todos los índices de texto completo en el catálogo.

Modifica un catálogo de texto completo por utilizar la sentencia ALTER FULLTEXT CATALOG, y lo elimina con la sentencia DROP FULLTEXT CATALOG.

Después de que tiene un catálogo de texto completo, puede crear índices de texto completos apropiados. La sintaxis para crear un índice de texto completo es la siguiente.

CREATE FULLTEXT INDEX ON nombre_tabla

   [ ( { nombre_columna

            [ TYPE COLUMN nombre_tipo_columna ]

            [ LANGUAGE termino_languaje ]

            [ STATISTICAL_SEMANTICS ]

       } [ ,...n]

     ) ]

   KEY INDEX nombre_indice

   [ ON <opcion_filegroup_catalogo> ]

   [ WITH [ ( ] <with_option> [ ,...n] [ ) ] ]

[;]

< opcion_filegroup_catalogo >::=

 {

    nombre_catalogo_fulltext

 | (nombre_catalogo_fulltext, FILEGROUP nombre_filegroup)

 | ( FILEGROUP nombre_filegroup, nombre_catalogo_fulltext)

 | ( FILEGROUP nombre_filegroup)

 }

<with_option>::=

 {

   CHANGE_TRACKING [ = ] { MANUAL | AUTO | OFF [, NO POPULATION ] }

 | STOPLIST [ = ] { OFF | SYSTEM | nombre_stoplist }

 | SEARCH PROPERTY LIST [ = ] nombre_lista_property

 }

La mayoría de las opciones son auto-descriptivas. Se aprende acerca de ellos en la práctica en este post. Lo siguiente describe algunas opciones avanzadas:

·         KEY INDEX nombre_indice. Este es el nombre del índice de llave unique en una tabla. Tiene que usar una columna unique, llave simple, no nula. Los enteros son recomendados.

·         CHANGE_TRACKING [=] {MANUAL | AUTO | OFF [, NO POPULATION]}. Esta opción especifica si SQL Server actualiza un índice de texto completo automáticamente. SQL Server utiliza un mecanismo de seguimiento de cambios, para rastrear los cambios.

·     STATISTICAL_SEMANTICS. Esta opción crea frases de llaves adicionales y documentos similares de índices, que son parte de la indexación semántica estadística.

La última opción mencionada, la opción STATISTICAL_SEMANTICS, merece una explicación más profunda. La búsqueda semántica estadística le da una visión más profunda en los documentos, por la extracción e indexación de frases claves estadísticamente relevantes. La búsqueda de texto completo utiliza estas frases clave para identificar e indexar documentos que son similares o relacionados. Consultar estos índices semánticos por utilizar tres funciones rowset de T-SQL, para recuperar los resultados como datos estructurados. Utiliza estas funciones en las prácticas en este post. La búsqueda semántica extiende la funcionalidad de búsquedas de texto completo. Le permite consultar el significado de los documentos. Por ejemplo, puede consultar el índice de las frases claves, para construir la taxonomía de los documentos. Puede consultar el documento de similitud de índices para identificar resúmenes, que coinciden con una descripción del trabajo. La búsqueda semántica te da la posibilidad de crear tu propia solución de minería de texto. La búsqueda semántica podría ser especialmente interesante en conjunción con los componentes de minería de texto de SQL Server Integration Services (SSIS).

Para utilizar la característica Busqueda Semantica, tiene que tener el Full-Text Search instalado. Además, necesita instalar la Semantic Language Statistics Database. Lo instalará en la práctica de este post.

Práctica: Creando un Indice de Texto Completo

En esta práctica, crea una tabla, la puebla con algunos documentos y datos de texto (https://onedrive.live.com/redir?resid=21485C1E4BBA52A!722&authkey=!ALSPxEfmtMdZJ14&ithint=file%2crar), y crea un catálogo e índice de texto completo sobre esta tabla. Esta práctica asume que el idioma predeterminado de su instancia es US English.

Ejercicio 1: Crear una Tabla y Componentes de Texto Completo

En este ejercicio, creará una tabla de demostración, lo poblará con algún texto demo, y luego creará stopwords y una stoplist y propiedades de documento de búsqueda.

1.    Inicie el SSMS y conéctese a su instancia de SQL Server.

2.    Abra una nueva ventana de consulta haciendo clic en el botón Nueva Consulta.

3.    Cambie el contexto a la base de datos TSQL2012.

4.    Compruebe si Full-Text Search está instalado utilizando la siguiente consulta.

SELECT SERVERPROPERTY('IsFullTextInstalled');

5.    Si Full-Text Search no está instalado, ejecute el SQL Server Setup e instálelo. También instale el paquete de filtros Microsoft Office 2010.

6.    Crear una tabla que utilizará para búsquedas de texto completo. Crearlo en el esquema dbo y nombrarlo Documentos. Utilice la información de la Tabla para las columnas de su tabla dbo.Documentos.

Utilice el siguiente código para crear la tabla.

CREATE TABLE dbo.Documentos

(

  id INT IDENTITY(1,1) NOT NULL,

  titulo NVARCHAR(100) NOT NULL,

  tipodoc NCHAR(4) NOT NULL,

  extractodoc NVARCHAR(1000) NOT NULL,

  contenidodoc VARBINARY(MAX) NOT NULL,

  CONSTRAINT PK_Documentos

   PRIMARY KEY CLUSTERED(id)

);

7.    Importe los cuatro documentos incluidos en la carpeta para este post. Si la carpeta es C:\TSQL, entonces puede utilizar el siguiente código directamente; de lo contrario, cambie la carpeta en las funciones OPENROWSET adecuadamente.

INSERT INTO dbo.Documentos

(titulo, tipodoc, extractodoc, contenidodoc)

SELECT N'Columnstore Indices and Batch Processing',

 N'docx',

 N'You should use a columnstore index on your fact tables,

   putting all columns of a fact table in a columnstore index.

   In addition to fact tables, very large dimensions could benefit

   from columnstore indices as well.

   Do not use columnstore indices for small dimensions. ',

 bulkcolumn

FROM OPENROWSET(BULK

'C:\TSQL\ColumnstoreIndicesAndBatchProcessing.docx',

                SINGLE_BLOB) AS doc;

INSERT INTO dbo.Documentos

(titulo, tipodoc, extractodoc, contenidodoc)

SELECT N'Introduction to Data Mining',

N'docx',

N'Using Data Mining is becoming more a necessity for every company

and not an advantage of some rare companies anymore. ',

bulkcolumn

FROM OPENROWSET(BULK 'C:\TSQL\IntroductionToDataMining.docx',

                SINGLE_BLOB) AS doc;

INSERT INTO dbo.Documentos

(titulo, tipodoc, extractodoc, contenidodoc)

SELECT N'Why Is Bleeding Edge a Different Conference',

 N'docx',

 N'During high level presentations attendees encounter many questions.

   For the third year, we are continuing with the breakfast Q&A session.

   It is very popular, and for two years now,

   we could not accommodate enough time for all questions and discussions! ',

 bulkcolumn

FROM OPENROWSET(BULK 'C:\TSQL\WhyIsBleedingEdgeADifferentConference.docx',

                SINGLE_BLOB) AS doc;

INSERT INTO dbo.Documentos

(titulo, tipodoc, extractodoc, contenidodoc)

SELECT N'Additivity of Measures',

 N'docx',

 N'Additivity of measures is not exactly a data warehouse design problem.

   However, you have to realize which aggregate functions you will use

   in reports for which measure, and which aggregate functions

   you will use when aggregating over which dimension.',

 bulkcolumn

FROM OPENROWSET(BULK 'C:\TSQL\AdditivityOfMeasures.docx',

                SINGLE_BLOB) AS doc;

8.    Crear una lista de propiedad de búsqueda llamada ListaPropiedadBusquedaPalabra. Agregar la propiedad Authors a la lista. Las propiedades de documento tienen IDs enteros y GUIDs predefinidos. Para la propiedad Authors de los documentos Office, el GUID es F29F85E0-4FF9-1068-AB91-08002B27B3D9, y el ID entero es 4. Utilice el siguiente código.

CREATE SEARCH PROPERTY LIST ListaPropiedadBusquedaPalabra;

GO

ALTER SEARCH PROPERTY LIST ListaPropiedadBusquedaPalabra

 ADD 'Authors'

 WITH (PROPERTY_SET_GUID = 'F29F85E0-4FF9-1068-AB91-08002B27B3D9',

       PROPERTY_INT_ID = 4,

       PROPERTY_DESCRIPTION = 'System.Authors – autores de un articulo dado');

9.    Crear una lista stopwords llamada SQLStopList. Agregar la palabra SQL, utilizando inglés como el idioma. Utilice el siguiente código.

CREATE FULLTEXT STOPLIST SQLStopList;

GO

ALTER FULLTEXT STOPLIST SQLStopList

 ADD 'SQL' LANGUAGE 'English';

10.  Revisar la lista stopwords y recuerde el ID stoplist. Utilice la siguiente consulta.

SELECT w.stoplist_id,

 l.name,

 w.stopword,

 w.language

FROM sys.fulltext_stopwords AS w

 INNER JOIN sys.fulltext_stoplists AS l

  ON w.stoplist_id = l.stoplist_id;

11.  Utilice la vista de gestión dinámica sys.dm_fts_parser para comprobar cómo la búsqueda de texto completo está analizando cadenas de acuerdo a su stoplist, información thesaurus, separador de palabras en el idioma seleccionado, y lematizadores en el idioma seleccionado. Por ejemplo, las siguientes dos consultas verifican cómo una cadena es separada en palabras y que formas inflexionales de una búsqueda de texto completo de palabra puede utilizar. Note los parámetros de la vista de gestión dinámica: El primero es la cadena de caracteres a analizar, el segundo es el ID idioma (1033 para US English), el tercero es el ID stoplist que obtuvo de la consulta anterior, y el cuarto es una marca mostrando si el análisis debería ser sensible a acentos o no.

SELECT *

FROM sys.dm_fts_parser

(N'"Additivity of measures is not exactly a data warehouse design problem.

   However, you have to realize which aggregate functions you will use

   in reports for which measure, and which aggregate functions

   you will use when aggregating over which dimension."', 1033, 5, 0);

SELECT *

FROM sys.dm_fts_parser

('FORMSOF(INFLECTIONAL,'+ 'function' + ')', 1033, 5, 0);

Ejercicio 2: Instalar una Base de Datos Semántica y Crear un Indice de Texto Completo

En este ejercicio, se instala una base de datos semántica y luego crea un índice de texto completo.

1.    Compruebe si la Semantic Language Statistics Database está instalada. Si la siguiente consulta no retorna una fila, debe instalarlo.

SELECT *

FROM sys.fulltext_semantic_language_statistics_database;

Para instalar la Semantic Language Statistics Database, ejecute el paquete SemanticLanguageDatabase.msi desde la carpeta x64\Setup (si está usando una instancia de 64 bits) o x86\Setup (si la instancia es de 32 bits) de la unidad de SQL Server Setup.

2.    Compruebe si la cuenta de servicio de SQL Server tiene permisos Read y Write en la carpeta donde instaló los archivos Semantic Language Statistics Database. La carpeta predeterminada es C:\Archivos de programa\Microsoft Semantic Language Database. Si ha instalado la base de datos en la carpeta predeterminada, puede adjuntarlo con el comando siguiente.

CREATE DATABASE semanticsdb ON

(FILENAME = 'C:\Program Files\Microsoft Semantic Language Database\semanticsdb.mdf'),

(FILENAME = 'C:\Program Files\Microsoft Semantic Language Database\semanticsdb_log.ldf')

FOR ATTACH;

3.    Después de adjuntar la base de datos, registrarlo por utilizar el siguiente código.

EXEC sp_fulltext_semantic_register_language_statistics_db

 @dbname = N'semanticsdb';

4.    Compruebe si la Semantic Language Statistics Database fue instalada exitosamente por repetir la consulta del paso 1. Esta vez, la consulta debe retornar una fila.

5.    Por último, es el momento para crear un catálogo. Nómbrelo CatalogoTcDocumentos. Utilice el siguiente código.

CREATE FULLTEXT CATALOG CatalogoTcDocumentos;

6.    Ahora, crear un índice de texto completo. Debería indexar las columnas extractodoc y contenidodoc. Ajuste el seguimiento de cambio para poblar el índice a AUTO. Utilice el siguiente código.

CREATE FULLTEXT INDEX ON dbo.Documentos

(

extractodoc Language 1033,

contenidodoc TYPE COLUMN tipodoc

Language 1033

STATISTICAL_SEMANTICS

)

KEY INDEX PK_Documentos

ON CatalogoTcDocumentos

WITH STOPLIST = SQLStopList,

SEARCH PROPERTY LIST = ListaPropiedadBusquedaPalabra,

CHANGE_TRACKING AUTO;